From 6cbe59cbe5982bd335bca29bee2de74b9ea74117 Mon Sep 17 00:00:00 2001 From: suchaudn Date: Thu, 18 Jul 2024 16:25:16 +0200 Subject: [PATCH 1/2] add possibility to connect to SQL Base by ODBC choice: Driver name, server, port, login, password, database --- collector/package.json | 5 +- frontend/package.json | 5 +- .../SQLConnectorSelection/DBConnection.jsx | 2 + .../NewConnectionModal.jsx | 31 ++++++++++ package.json | 9 ++- server/package.json | 6 +- .../plugins/sql-agent/SQLConnectors/ODBC.js | 61 +++++++++++++++++++ .../plugins/sql-agent/SQLConnectors/index.js | 6 +- server/yarn.lock | 18 +++++- 9 files changed, 131 insertions(+), 12 deletions(-) create mode 100644 server/utils/agents/aibitat/plugins/sql-agent/SQLConnectors/ODBC.js diff --git a/collector/package.json b/collector/package.json index 5e3873d1eb7..cf28b6abab8 100644 --- 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 index 3aa23b20cbd..dae6a1e3e46 100644 --- 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" } -} \ No newline at end of file +} diff --git a/frontend/src/pages/Admin/Agents/SQLConnectorSelection/DBConnection.jsx b/frontend/src/pages/Admin/Agents/SQLConnectorSelection/DBConnection.jsx index 9d7b35b0a38..d7361baea0d 100644 --- 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 index e5f4c301627..fc13ac5c879 100644 --- 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")} /> + setEngine("odbc")} + /> @@ -224,6 +236,25 @@ export default function NewSQLConnection({ isOpen, closeModal, onSubmit }) { spellCheck={false} /> + + { + engine === "odbc" && +
+ + +
+ }

{assembleConnectionString({ engine, ...config })}

diff --git a/package.json b/package.json index 43fc24cfb51..b8c32df16e2 100644 --- 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 -} \ No newline at end of file +} diff --git a/server/package.json b/server/package.json index be1749620b9..5d0b8d59388 100644 --- 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 new file mode 100644 index 00000000000..a0358741184 --- /dev/null +++ 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 index 9cf1e1ff4d7..260e3002087 100644 --- 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 index 56d2c58a1a6..1d231160b84 100644 --- 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" From fa21d527e4359d06ea7eb10c95df6684520649e5 Mon Sep 17 00:00:00 2001 From: suchaudn Date: Fri, 19 Jul 2024 15:08:30 +0200 Subject: [PATCH 2/2] add png icon tried with my mongodb base OK (connection , request) --- .../Agents/SQLConnectorSelection/icons/odbc.png | Bin 0 -> 3776 bytes 1 file changed, 0 insertions(+), 0 deletions(-) create mode 100644 frontend/src/pages/Admin/Agents/SQLConnectorSelection/icons/odbc.png diff --git a/frontend/src/pages/Admin/Agents/SQLConnectorSelection/icons/odbc.png b/frontend/src/pages/Admin/Agents/SQLConnectorSelection/icons/odbc.png new file mode 100644 index 0000000000000000000000000000000000000000..0f9dd509ccd49ad32b54d96da57823780ffa9c4a GIT binary patch literal 3776 zcmZ{ncQ71`*2dS`Wp%^*5-npjfDYS82lWhM^ zKJweUzGHnR;uv?Cf9SmJ5Nr{x!tXywPBIlh)!inM-YCOf_?B{y{0iCtB*c2M$V(1v z&m{MZ`v!)p!ha-HfUSaD_L-|`IToH5dnPmUKaZQe(qrnTqDFif_9Mis9Vlvtx-&EcmuJZ@rM)}<%eyXeo zUbzzV=W#PaUea;$p_EkWgQ3qbXw>fXAKIv0vO4_z42)OH_Sv52JO1gK?61F7inRF? zWWJxNqLJ%=nYtJXD@M(*>>$}66A`ThfEG)aC!~j|>pW;Lrs{)n0NhZR&SR_pi4OXAYD8Q_S+@7Ok0G*p1=+21hErtag2>$-*=^=Qkf~bl5UqwJ& zhl-nEUp!+<$o+Xy?uM%Q-&y#Jnt$a}r0phcmJC5bE=eZAaeZD6HrvMC26nNNkXv#0 z;^ryEy~7q$U8Z&>qL+xz`APA=fc|uSK-KD7NfA2(ypa{J@8{vJ0694DO#ex!9DO5_ z{$dT(2f0%d4_|ncu_Sal6;YIaz%Oqn%xFML9#ju<7u^aST!haq2Gh;Zg^CZMzKEpw zeF&2b0a6uP9Qdeq`oZG|+f}2JOEiHfMLI-HhP`w}E$iLO=YTqR*~G(e-oxm%F(eD~ zevJYb%22l5xhQ2QNX4EgDb#MS!RAEhDXoijv7JWtf5>^pw!YL9%ELbhL9Z!yU`O8^ zyHMRdb1h;uB$zqbDy)>I;!OPY#D!iTYvm0!e>#Yq=(iV&RS1Z+lL_qWy}`M*5ntv^ z$J1CAnG$R$J?$YAMa5*zk3sq}GKDMWtmQe;b?G8|T6w2ies9Ynp`n*UORpzC##mFn z3t=fMzE%kmd(Roy2TDJp8St2y+LV?ND=V=;?VX7pr&EdrsIpsZ;F#Xn+OOX)b@u*& z#ltX}8)`2E*@-u&f{+64*r3JSa_P51uQddOO<{BSRn{-hBD|~D6o(ADlT8fb89)z| z+XX+0tE%Dw!m)3_9Qt^G;t&PV2$ZEE2i94DB`^XG!;$vu3Ql;&tMO3R?Fq1ig})8O z%!9&eeHA2u`LZDOe50EC4P-(rfh-j?e%U??*|c+``k%eX9E_87PLEL?%*PjFG&kz~ zT#JLE$3%s0-0C^pgKmzJhWweFG?55)PL5Xg|AV>F@kI?pYcMNZ4!X>m+bOl?CLx4- zga`p1i;^T{<#mu8PqHsh-GivlXS`sEhCcwHJ?wV*dyGtOe21AmjrfuKtDJ$$52UNXkY!PdQ&2j%)z~-K?!S^iaAj6)UUIVQ?D!b|5`ATQmu7fBqIV#meL`n4Lto+@n28^I!Lx zd@L?oW$N5kqz4Mk`Tlpr7(wzCW@YPAmo-|Umwcxl%q@PuN=Y*K+glOkQ*pHp40gAK z)FY!@dT7koM0!0A>XwIbfhxCn3hh;@GLrAzwb+t)J=uR!I?7ID6>KJ1#SMNDRR+%9 zw_Y?}Rywx;0S}#LrI8z@8crKEN)u|5$dJjj6bygNXWh@f(uT)NyH(eAb~)xOk~1*y zdMiGG_{KmtAdMUAxc)f^3|#hkaIZsQh5~nG1QpfwcZ8V-=tNax09UiBnRYb0A~^X5 zfKY=kv#G>qH?@xdTR^DWx@9wJ+HXSRIr4laSH{s!cA+Za7E%odZJpi51WlKt@|U|@Gqnx@c29f0b=)(} z{oyA$@u)U#MC&aRHb>@`={_&nYHOMty6>cLwC|*0Rlg@QX zvAq%VTTj-j*r-P$kmb~X_%M0P+%Q>2a820@r-ZkUDqL0Dnv$%zF1_j<_HZVS53Fao zJhh!5zV+dhnBlPJWQtv=C*e)x<+7g=?U~oX5s#4C{EM-MnRJ=C(MkeQmDDhb&BHTA z;!Y8)5%x-#jyY*#ke14msA4>I9=e*QHfEHG`!SAn3*oJ+-@1#SKKQP=dc5Dc?fok? z^{Mw!l07akQ+Pb?9Z5npZ?oYPVuh0EIp%GA^;c+ThXU7TSf;N_o5XDf^>9dWs?`03 zR(2jmr>`*dPZ%$&V69oYQtBC_0OGgsSpr>E9{ZPpa74D)`QFENg^&?CI~U@s$Rr&| z+mdqoH3$|ZT-JQ*eK5JK>aMW%VaU676uoZH-s5A-SAMwb!R396pb_L>LWJ&Y#*mSPV z=s9Cqo5hH8_~f5`JXB7>qxHU8wpICbcME+E*XH7xr%hZ|tnNs?rP>$+=^1L2R8|Y zREL0zlwx;yFn;yjrEJC2-fOv@UgkS6d?{92zuypi38=nOMDvO(ZYCPP-GKUvn~CNO zEMWb+0o-Tu?F4G46eqN$9L|R;RkoxaHUxj|ZlW_LlZmqt zJtg4yvGj>SE6e$lda;m7z9>U$JO-7+Wp)p33J$*mzb}4B+~UCJ1@Uwlh-A9;rOqTIDtN3j-?AQ9K@wx4J}U}Cl$Sj=ay_%Sfu7E||l zhG{7x&|E`9^g|_hmLv?EA~dKS5419EcjY7=-WKagsvz)8E)Xeo{S?u1wW{!h4|X1s zTymQWtE-2pzKG!%*fo9pk?1}l1ob_UxvhyQ3Gh?XCyYbXPsYL`E_j}R;?dqI|M70- z{MMp%)rvGd?XL0KXcL2XlbT}NtCT6Vz^^pt)aKE*8b|W7xO&F3N|9k$O;VMl#ZjGW zKefJVJBr#DVdoRnD`{hKZBI?kEwx!5{Jp<@ig?40U~6@6ZpFR(INSN=kjvgs7QG0n zD%AQGKtfPYx2={Fb_apG3@)Ko(wWnNU?#!8(sDpdkwGDgnrgmKU*s?TN}bc#4vCG^Y6~vsm#-_