NLP_commit_message_categorize.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6ba26274",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "from matplotlib import pyplot as plt\n",
    "import mysql.connector\n",
    "from datetime import datetime\n",
    "import warnings\n",
    "import spacy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7ac2d298",
   "metadata": {},
   "outputs": [],
   "source": [
    "host = 'localhost'\n",
    "user = 'root'\n",
    "password = 'Test@1234'\n",
    "database = 'github'\n",
    "\n",
    "# Establish connection\n",
    "connection = mysql.connector.connect(\n",
    "    host=host,\n",
    "    user=user,\n",
    "    password=password,\n",
    "    database=database\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1d64ba16",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/jw/rknyb_hs2b50h1tpl5kq9_280000gp/T/ipykernel_33521/2010822267.py:13: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df_english_message = pd.read_sql(commit_message_english_query, connection)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(91558, 3)\n"
     ]
    }
   ],
   "source": [
    "commit_message_english_query = \"\"\"\n",
    "                                SELECT r.repo_id, r.langauge, c.messages \n",
    "                                FROM repositories r\n",
    "                                INNER JOIN commit_messages c \n",
    "                                    ON r.repo_id = c.repo_id\n",
    "                                LEFT JOIN commit_message_category ch\n",
    "                                        ON ch.repo_id = c.repo_id\n",
    "                                        AND ch.messages = c.messages\n",
    "                                WHERE\n",
    "                                    c.messages REGEXP '^[A-Za-z0-9[:space:][:punct:]]*$'\n",
    "                                    AND ch.messages IS NULL;\n",
    "\"\"\"\n",
    "df_english_message = pd.read_sql(commit_message_english_query, connection)\n",
    "\n",
    "print(df_english_message.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "20428d62",
   "metadata": {},
   "outputs": [],
   "source": [
    "# repeated error categories\n",
    "\n",
    "rep_error_cat = {\n",
    "    \"Integration Errors\": [\"merge\", \"conflict\", \"integration\", \"merge conflict\"],\n",
    "    \"Compilation Errors\": [\"compile\", \"build\", \"syntax\", \"error\"],\n",
    "    \"Testing Errors\": [\"test\", \"failure\", \"update test\", \"test case\"],\n",
    "    \"Deployment Errors\": [\"deploy\", \"deployment\", \"deployment error\", \"deploy issue\"],\n",
    "    \"Code Quality Issues\": [\"code quality\", \"refactor\", \"code smell\", \"performance\", \"security vulnerability\"],\n",
    "    \"Environment Inconsistencies\": [\"environment\", \"configuration\", \"mismatch\"],\n",
    "    \"Manual Configuration Errors\": [\"manual configuration\", \"config error\", \"server settings\"],\n",
    "    \"Rollback Errors\": [\"rollback\", \"revert\", \"undo\", \"fix rollback\"]\n",
    "}\n",
    "\n",
    "\n",
    "# overall commit categories\n",
    "all_error_cat = {\n",
    "    \"Bug Fixes\": [\"fix\", \"bug\", \"issue\"],\n",
    "    \"New Features\": [\"add\", \"feature\"],\n",
    "    \"Performance Improvements\": [\"refactor\", \"optimize\", \"performance\"],\n",
    "    \"Security Updates\": [\"security\", \"update\"],\n",
    "    \"UI/UX Changes\": [\"ui\", \"ux\", \"design\"]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d04f6c64",
   "metadata": {},
   "outputs": [],
   "source": [
    "nlp = spacy.load(\"en_core_web_sm\")\n",
    "\n",
    "def categorize_message(messages):\n",
    "    doc = nlp(messages.lower())\n",
    "    for category, keywords in all_error_cat.items():\n",
    "        for keyword in keywords:\n",
    "            if keyword in [token.text for token in doc]:\n",
    "                return category\n",
    "            \n",
    "    return \"Others\"\n",
    "\n",
    "\n",
    "\n",
    "def categorize_repeated_message(messages):\n",
    "    doc = nlp(messages.lower())\n",
    "    for category, keywords in rep_error_cat.items():\n",
    "        for keyword in keywords:\n",
    "            if keyword in [token.text for token in doc]:\n",
    "                return category\n",
    "            \n",
    "    return \"Others\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5eee9655",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_english_message['all_category'] = df_english_message['messages'].apply(categorize_message)\n",
    "\n",
    "df_english_message['repeated_category'] = df_english_message['messages'].apply(categorize_repeated_message)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2193da6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connection.cursor()\n",
    "\n",
    "# # Create table schema\n",
    "# create_table_query = '''\n",
    "# CREATE TABLE IF NOT EXISTS commit_message_category (\n",
    "#     repo_id INT,\n",
    "#     language VARCHAR(255),\n",
    "#     messages TEXT,\n",
    "#     all_category VARCHAR(255),\n",
    "#     repeated_category VARCHAR(255)\n",
    "# )\n",
    "# '''\n",
    "\n",
    "# # Execute table creation query\n",
    "# cursor.execute(create_table_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f3f8cfe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in df_english_message.iterrows():\n",
    "    insert_query = '''\n",
    "    INSERT INTO commit_message_category (repo_id, messages, all_category, repeated_category)\n",
    "    VALUES (%s, %s, %s, %s)\n",
    "    '''\n",
    "    cursor.execute(insert_query, (row['repo_id'], row['messages'], row['all_category'], row['repeated_category']))\n",
    "\n",
    "# Commit changes\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b2e551f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}