Mintplex-Labs · nicho2 · Jul 18, 2024 · Jul 19, 2024 · Jul 19, 2024
diff --git a/collector/package.json b/collector/package.json
@@ -11,8 +11,9 @@
   },
   "scripts": {
     "dev": "NODE_ENV=development nodemon --ignore hotdir --ignore storage --trace-warnings index.js",
-    "start": "NODE_ENV=production node index.js",
-    "lint": "yarn prettier --ignore-path ../.prettierignore --write ./processSingleFile ./processLink ./utils index.js"
+    "start": "set NODE_ENV=production& node index.js",
+    "lint": "yarn prettier --ignore-path ../.prettierignore --write ./processSingleFile ./processLink ./utils index.js",
+    "windev": "set NODE_ENV=development& nodemon --ignore hotdir --ignore storage --trace-warnings index.js"
   },
   "dependencies": {
     "@googleapis/youtube": "^9.0.0",

diff --git a/frontend/package.json b/frontend/package.json
@@ -8,7 +8,8 @@
     "dev": "NODE_ENV=development vite --debug --host=0.0.0.0",
     "build": "vite build",
     "lint": "yarn prettier --ignore-path ../.prettierignore --write ./src",
-    "preview": "vite preview"
+    "preview": "vite preview",
+    "windev": "set NODE_ENV=development& vite --debug --host=0.0.0.0"
   },
   "dependencies": {
     "@metamask/jazzicon": "^2.0.0",
@@ -68,4 +69,4 @@
     "tailwindcss": "^3.3.1",
     "vite": "^4.3.0"
   }
-}
+}
diff --git a/frontend/src/pages/Admin/Agents/SQLConnectorSelection/DBConnection.jsx b/frontend/src/pages/Admin/Agents/SQLConnectorSelection/DBConnection.jsx
@@ -1,12 +1,14 @@
 import PostgreSQLLogo from "./icons/postgresql.png";
 import MySQLLogo from "./icons/mysql.png";
 import MSSQLLogo from "./icons/mssql.png";
+import ODBCLogo from "./icons/odbc.png";
 import { X } from "@phosphor-icons/react";
 
 export const DB_LOGOS = {
   postgresql: PostgreSQLLogo,
   mysql: MySQLLogo,
   "sql-server": MSSQLLogo,
+  odbc: ODBCLogo,
 };
 
 export default function DBConnection({ connection, onRemove, setHasChanges }) {

diff --git a/frontend/src/pages/Admin/Agents/SQLConnectorSelection/NewConnectionModal.jsx b/frontend/src/pages/Admin/Agents/SQLConnectorSelection/NewConnectionModal.jsx
@@ -11,6 +11,7 @@ function assembleConnectionString({
   host = "",
   port = "",
   database = "",
+  driver = "",
 }) {
   if ([username, password, host, database].every((i) => !!i) === false)
     return `Please fill out all the fields above.`;
@@ -21,6 +22,9 @@ function assembleConnectionString({
       return `mysql://${username}:${password}@${host}:${port}/${database}`;
     case "sql-server":
       return `mssql://${username}:${password}@${host}:${port}/${database}`;
+    case "odbc":
+      if (!driver) return `Please fill out the driver field.`;
+      return `Driver={${driver}};Server=${host};Port=${port};Database=${database};UID=${username};PWD=${password}`;
     default:
       return null;
   }
@@ -33,6 +37,7 @@ const DEFAULT_CONFIG = {
   host: null,
   port: null,
   database: null,
+  driver: null,
 };
 
 export default function NewSQLConnection({ isOpen, closeModal, onSubmit }) {
@@ -48,12 +53,14 @@ export default function NewSQLConnection({ isOpen, closeModal, onSubmit }) {
 
   function onFormChange() {
     const form = new FormData(document.getElementById("sql-connection-form"));
+
     setConfig({
       username: form.get("username").trim(),
       password: form.get("password"),
       host: form.get("host").trim(),
       port: form.get("port").trim(),
       database: form.get("database").trim(),
+      driver: form.get("driver")?.trim(),
     });
   }
 
@@ -130,6 +137,11 @@ export default function NewSQLConnection({ isOpen, closeModal, onSubmit }) {
                     active={engine === "sql-server"}
                     onClick={() => setEngine("sql-server")}
                   />
+                  <DBEngine
+                    provider="odbc"
+                    active={engine === "odbc"}
+                    onClick={() => setEngine("odbc")}
+                  />
                 </div>
               </div>
 
@@ -224,6 +236,25 @@ export default function NewSQLConnection({ isOpen, closeModal, onSubmit }) {
                   spellCheck={false}
                 />
               </div>
+
+              {
+                engine === "odbc" &&
+                <div className="flex flex-col">
+                  <label
+                    className="text-white text-sm font-semibold block mb-3">
+                    Driver
+                  </label>
+                  <input
+                    type="text"
+                    name="driver"
+                    className="border-none bg-zinc-900 text-white placeholder:text-white/20 text-sm rounded-lg focus:outline-primary-button active:outline-primary-button outline-none block w-full p-2.5"
+                    placeholder="the driver to use : MongoDB ODBC 1.2.0 ANSI Driver"
+                    required={true}
+                    autoComplete="off"
+                    spellCheck={false}
+                  />
+                </div>
+              }
               <p className="text-white/40 text-sm">
                 {assembleConnectionString({ engine, ...config })}
               </p>

diff --git a/frontend/src/pages/Admin/Agents/SQLConnectorSelection/icons/odbc.png b/frontend/src/pages/Admin/Agents/SQLConnectorSelection/icons/odbc.png
diff --git a/package.json b/package.json
@@ -12,7 +12,7 @@
   "scripts": {
     "lint": "cd server && yarn lint && cd ../frontend && yarn lint && cd ../embed && yarn lint && cd ../collector && yarn lint",
     "setup": "cd server && yarn && cd ../collector && yarn && cd ../frontend && yarn && cd .. && yarn setup:envs && yarn prisma:setup && echo \"Please run yarn dev:server, yarn dev:collector, and yarn dev:frontend in separate terminal tabs.\"",
-    "setup:envs": "cp -n ./frontend/.env.example ./frontend/.env && cp -n ./server/.env.example ./server/.env.development && cp -n ./collector/.env.example ./collector/.env && cp -n ./docker/.env.example ./docker/.env && echo \"All ENV files copied!\n\"",
+    "setup:envs": "copy .\\frontend\\.env.example .\\frontend\\.env && copy .\\server\\.env.example .\\server\\.env.development && copy .\\collector\\.env.example .\\collector\\.env && copy .\\docker\\.env.example .\\docker\\.env && echo \"All ENV files copied!\n\"",
     "dev:server": "cd server && yarn dev",
     "dev:collector": "cd collector && yarn dev",
     "dev:frontend": "cd frontend && yarn dev",
@@ -25,7 +25,10 @@
     "prod:frontend": "cd frontend && yarn build",
     "generate:cloudformation": "node cloud-deployments/aws/cloudformation/generate.mjs",
     "generate::gcp_deployment": "node cloud-deployments/gcp/deployment/generate.mjs",
-    "verify:translations": "cd frontend/src/locales && node verifyTranslations.mjs"
+    "verify:translations": "cd frontend/src/locales && node verifyTranslations.mjs",
+    "windev:server": "cd server && yarn windev",
+    "windev:collector": "cd collector && yarn windev",
+    "windev:frontend": "cd frontend && yarn windev"
   },
   "private": false
-}
+}
diff --git a/server/package.json b/server/package.json
@@ -11,10 +11,11 @@
   },
   "scripts": {
     "dev": "NODE_ENV=development nodemon --ignore documents --ignore vector-cache --ignore storage --ignore swagger --trace-warnings index.js",
-    "start": "NODE_ENV=production node index.js",
+    "start": "set NODE_ENV=production& node index.js",
     "lint": "yarn prettier --ignore-path ../.prettierignore --write ./endpoints ./models ./utils index.js",
     "swagger": "node ./swagger/init.js",
-    "sqlite:migrate": "cd ./utils/prisma && node migrateFromSqlite.js"
+    "sqlite:migrate": "cd ./utils/prisma && node migrateFromSqlite.js",
+    "windev": "set NODE_ENV=development& nodemon --ignore documents --ignore vector-cache --ignore storage --ignore swagger --trace-warnings index.js"
   },
   "prisma": {
     "seed": "node prisma/seed.js"
@@ -66,6 +67,7 @@
     "mysql2": "^3.9.7",
     "node-html-markdown": "^1.3.0",
     "node-llama-cpp": "^2.8.0",
+    "odbc": "^2.4.8",
     "ollama": "^0.5.0",
     "openai": "4.38.5",
     "pg": "^8.11.5",

diff --git a/server/utils/agents/aibitat/plugins/sql-agent/SQLConnectors/ODBC.js b/server/utils/agents/aibitat/plugins/sql-agent/SQLConnectors/ODBC.js
@@ -0,0 +1,61 @@
+const odbc = require("odbc");
+const UrlPattern = require("url-pattern");
+
+class ODBCConnector {
+  #connected = false;
+  database_id = "";
+  constructor(
+    config = {
+      connectionString: null,
+    }
+  ) {
+    this.connectionString = config.connectionString;
+    this._client = null;
+    this.database_id = this.#parseDatabase();
+  }
+
+  #parseDatabase() {
+    const regex = /Database=([^;]+)/;
+    const match = this.connectionString.match(regex);
+    return match ? match[1] : null;
+  }
+
+  async connect() {
+    this._client = await odbc.connect(this.connectionString);
+    this.#connected = true;
+    return this._client;
+  }
+
+  /**
+   *
+   * @param {string} queryString the SQL query to be run
+   * @returns {import(".").QueryResult}
+   */
+  async runQuery(queryString = "") {
+    const result = { rows: [], count: 0, error: null };
+    try {
+      if (!this.#connected) await this.connect();
+      const query = await this._client.query(queryString);
+      result.rows = query;
+      result.count = query.length;
+    } catch (err) {
+      console.log(this.constructor.name, err);
+      result.error = err.message;
+    } finally {
+      await this._client.close();
+      this.#connected = false;
+    }
+    return result;
+  }
+
+  getTablesSql() {
+    return `SELECT table_name FROM information_schema.tables WHERE table_schema = '${this.database_id}'`;
+  }
+
+  getTableSchemaSql(table_name) {
+    return `SHOW COLUMNS FROM ${this.database_id}.${table_name};`;
+  }
+}
+
+module.exports.ODBCConnector = ODBCConnector;
+
diff --git a/server/utils/agents/aibitat/plugins/sql-agent/SQLConnectors/index.js b/server/utils/agents/aibitat/plugins/sql-agent/SQLConnectors/index.js
@@ -1,6 +1,7 @@
 const { SystemSettings } = require("../../../../../../models/systemSettings");
 const { safeJsonParse } = require("../../../../../http");
-
+const { MSSQLConnector } = require("./MSSQL");
+const { ODBCConnector } = require("./ODBC");
 /**
  * @typedef {('postgresql'|'mysql'|'sql-server')} SQLEngine
  */
@@ -36,6 +37,9 @@ function getDBClient(identifier = "", connectionConfig = {}) {
     case "sql-server":
       const { MSSQLConnector } = require("./MSSQL");
       return new MSSQLConnector(connectionConfig);
+    case "odbc":
+      const { ODBCConnector } = require("./ODBC");
+      return new ODBCConnector(connectionConfig);
     default:
       throw new Error(
         `There is no supported database connector for ${identifier}`

diff --git a/server/yarn.lock b/server/yarn.lock
@@ -673,7 +673,7 @@
     "@langchain/core" "~0.1"
     js-tiktoken "^1.0.11"
 
-"@mapbox/node-pre-gyp@^1.0.11":
+"@mapbox/node-pre-gyp@^1.0.11", "@mapbox/node-pre-gyp@^1.0.5":
   version "1.0.11"
   resolved "https://registry.yarnpkg.com/@mapbox/node-pre-gyp/-/node-pre-gyp-1.0.11.tgz#417db42b7f5323d79e93b34a6d7a2a12c0df43fa"
   integrity sha512-Yhlar6v9WQgUp/He7BdgzOz8lqMQ8sU+jkCq7Wx8Myc5YFJLbEe7lgui/V7G1qB1DJykHSGwreceSaD60Y0PUQ==
@@ -1581,7 +1581,7 @@ arrify@^2.0.0:
   resolved "https://registry.yarnpkg.com/arrify/-/arrify-2.0.1.tgz#c9655e9331e0abcd588d2a7cad7e9956f66701fa"
   integrity sha512-3duEwti880xqi4eAMN8AyR4a0ByT90zoYdLlevfrvU43vb0YZwZVfxOgxWrLXXXpyugL0hNZc9G6BiB5B3nUug==
 
-async@^3.2.3, async@^3.2.4:
+async@^3.0.1, async@^3.2.3, async@^3.2.4:
   version "3.2.5"
   resolved "https://registry.yarnpkg.com/async/-/async-3.2.5.tgz#ebd52a8fdaf7a2289a24df399f8d8485c8a46b66"
   integrity sha512-baNZyqaaLhyLVKm/DlvdW051MSgO6b8eVfIezl9E5PqWxFgzLm/wQntEW4zOytVburDEr0JlALEpdOFwvErLsg==
@@ -4807,6 +4807,11 @@ node-abort-controller@^3.1.1:
   resolved "https://registry.yarnpkg.com/node-abort-controller/-/node-abort-controller-3.1.1.tgz#a94377e964a9a37ac3976d848cb5c765833b8548"
   integrity sha512-AGK2yQKIjRuqnc6VkX2Xj5d+QW8xZ87pa1UK6yA6ouUyuxfHuMP6umE5QK7UmTeOAymo+Zx1Fxiuw9rVx8taHQ==
 
+node-addon-api@^3.0.2:
+  version "3.2.1"
+  resolved "https://registry.yarnpkg.com/node-addon-api/-/node-addon-api-3.2.1.tgz#81325e0a2117789c0128dab65e7e38f07ceba161"
+  integrity sha512-mmcei9JghVNDYydghQmeDX8KoAm0FAiYyIcUt/N4nhyAipB17pllZQDOJD2fotxABnt4Mdz+dKTO7eftLg4d0A==
+
 node-addon-api@^5.0.0:
   version "5.1.0"
   resolved "https://registry.yarnpkg.com/node-addon-api/-/node-addon-api-5.1.0.tgz#49da1ca055e109a23d537e9de43c09cca21eb762"
@@ -5059,6 +5064,15 @@ octokit@^3.1.0:
     "@octokit/request-error" "^5.0.0"
     "@octokit/types" "^12.0.0"
 
+odbc@^2.4.8:
+  version "2.4.8"
+  resolved "https://registry.yarnpkg.com/odbc/-/odbc-2.4.8.tgz#56e34a1cafbaf1c2c53eec229b3a7604f890e3bf"
+  integrity sha512-W4VkBcr8iSe8hqpp2GoFPybCAJefC7eK837XThJkYCW4tBzyQisqkciwt1UYidU1OpKy1589y9dMN0tStiVB1Q==
+  dependencies:
+    "@mapbox/node-pre-gyp" "^1.0.5"
+    async "^3.0.1"
+    node-addon-api "^3.0.2"
+
 ollama@^0.5.0:
   version "0.5.0"
   resolved "https://registry.yarnpkg.com/ollama/-/ollama-0.5.0.tgz#cb9bc709d4d3278c9f484f751b0d9b98b06f4859"