{"version":3,"file":"sql_utils.cjs","names":["SQL_POSTGRES_PROMPT","SQL_SQLITE_PROMPT","SQL_MYSQL_PROMPT","SQL_MSSQL_PROMPT","SQL_SAP_HANA_PROMPT","SQL_ORACLE_PROMPT","DEFAULT_SQL_DATABASE_PROMPT"],"sources":["../../src/util/sql_utils.ts"],"sourcesContent":["import type { DataSource, DataSourceOptions } from \"typeorm\";\nimport type { PromptTemplate } from \"@langchain/core/prompts\";\nimport {\n  DEFAULT_SQL_DATABASE_PROMPT,\n  SQL_SAP_HANA_PROMPT,\n  SQL_MSSQL_PROMPT,\n  SQL_MYSQL_PROMPT,\n  SQL_POSTGRES_PROMPT,\n  SQL_SQLITE_PROMPT,\n  SQL_ORACLE_PROMPT,\n} from \"../chains/sql_db/sql_db_prompt.js\";\n\ninterface RawResultTableAndColumn {\n  table_name: string;\n  column_name: string;\n  data_type: string | undefined;\n  is_nullable: string;\n}\n\nexport interface SqlDatabaseParams {\n  includesTables?: Array<string>;\n  ignoreTables?: Array<string>;\n  sampleRowsInTableInfo?: number;\n  customDescription?: Record<string, string>;\n}\n\nexport interface SqlDatabaseOptionsParams extends SqlDatabaseParams {\n  appDataSourceOptions: DataSourceOptions;\n}\n\nexport interface SqlDatabaseDataSourceParams extends SqlDatabaseParams {\n  appDataSource: DataSource;\n}\n\nexport type SerializedSqlDatabase = SqlDatabaseOptionsParams & {\n  _type: string;\n};\n\nexport interface SqlTable {\n  tableName: string;\n  columns: SqlColumn[];\n}\n\nexport interface SqlColumn {\n  columnName: string;\n  dataType?: string;\n  isNullable?: boolean;\n}\n\nexport const verifyListTablesExistInDatabase = (\n  tablesFromDatabase: Array<SqlTable>,\n  listTables: Array<string>,\n  errorPrefixMsg: string\n): void => {\n  const onlyTableNames: Array<string> = tablesFromDatabase.map(\n    (table: SqlTable) => table.tableName\n  );\n  if (listTables.length > 0) {\n    for (const tableName of listTables) {\n      if (!onlyTableNames.includes(tableName)) {\n        throw new Error(\n          `${errorPrefixMsg} the table ${tableName} was not found in the database`\n        );\n      }\n    }\n  }\n};\n\nexport const verifyIncludeTablesExistInDatabase = (\n  tablesFromDatabase: Array<SqlTable>,\n  includeTables: Array<string>\n): void => {\n  verifyListTablesExistInDatabase(\n    tablesFromDatabase,\n    includeTables,\n    \"Include tables not found in database:\"\n  );\n};\n\nexport const verifyIgnoreTablesExistInDatabase = (\n  tablesFromDatabase: Array<SqlTable>,\n  ignoreTables: Array<string>\n): void => {\n  verifyListTablesExistInDatabase(\n    tablesFromDatabase,\n    ignoreTables,\n    \"Ignore tables not found in database:\"\n  );\n};\n\nconst formatToSqlTable = (\n  rawResultsTableAndColumn: Array<RawResultTableAndColumn>\n): Array<SqlTable> => {\n  const sqlTable: Array<SqlTable> = [];\n  for (const oneResult of rawResultsTableAndColumn) {\n    const sqlColumn = {\n      columnName: oneResult.column_name,\n      dataType: oneResult.data_type,\n      isNullable: oneResult.is_nullable === \"YES\",\n    };\n    const currentTable = sqlTable.find(\n      (oneTable) => oneTable.tableName === oneResult.table_name\n    );\n    if (currentTable) {\n      currentTable.columns.push(sqlColumn);\n    } else {\n      const newTable = {\n        tableName: oneResult.table_name,\n        columns: [sqlColumn],\n      };\n      sqlTable.push(newTable);\n    }\n  }\n\n  return sqlTable;\n};\n\nexport const getTableAndColumnsName = async (\n  appDataSource: DataSource\n): Promise<Array<SqlTable>> => {\n  let sql;\n  if (\n    appDataSource.options.type === \"postgres\" ||\n    appDataSource.options.type === \"aurora-postgres\"\n  ) {\n    const schema =\n      appDataSource.options.type === \"postgres\"\n        ? (appDataSource.options?.schema ?? \"public\")\n        : \"public\";\n    sql = `SELECT \n            t.table_name, \n            c.* \n          FROM \n            information_schema.tables t \n              JOIN information_schema.columns c \n                ON t.table_name = c.table_name \n          WHERE \n            t.table_schema = '${schema}' \n              AND c.table_schema = '${schema}' \n          ORDER BY \n            t.table_name,\n            c.ordinal_position;`;\n    const rep = await appDataSource.query(sql);\n\n    return formatToSqlTable(rep);\n  }\n\n  if (\n    appDataSource.options.type === \"sqlite\" ||\n    appDataSource.options.type === \"better-sqlite3\" ||\n    appDataSource.options.type === \"sqljs\"\n  ) {\n    sql =\n      \"SELECT \\n\" +\n      \"   m.name AS table_name,\\n\" +\n      \"   p.name AS column_name,\\n\" +\n      \"   p.type AS data_type,\\n\" +\n      \"   CASE \\n\" +\n      \"      WHEN p.\\\"notnull\\\" = 0 THEN 'YES' \\n\" +\n      \"      ELSE 'NO' \\n\" +\n      \"   END AS is_nullable \\n\" +\n      \"FROM \\n\" +\n      \"   sqlite_master m \\n\" +\n      \"JOIN \\n\" +\n      \"   pragma_table_info(m.name) p \\n\" +\n      \"WHERE \\n\" +\n      \"   m.type = 'table' AND \\n\" +\n      \"   m.name NOT LIKE 'sqlite_%';\\n\";\n\n    const rep = await appDataSource.query(sql);\n\n    return formatToSqlTable(rep);\n  }\n\n  if (\n    appDataSource.options.type === \"mysql\" ||\n    appDataSource.options.type === \"aurora-mysql\"\n  ) {\n    sql =\n      \"SELECT \" +\n      \"TABLE_NAME AS table_name, \" +\n      \"COLUMN_NAME AS column_name, \" +\n      \"DATA_TYPE AS data_type, \" +\n      \"IS_NULLABLE AS is_nullable \" +\n      \"FROM INFORMATION_SCHEMA.COLUMNS \" +\n      `WHERE TABLE_SCHEMA = '${appDataSource.options.database}';`;\n\n    const rep = await appDataSource.query(sql);\n\n    return formatToSqlTable(rep);\n  }\n\n  if (appDataSource.options.type === \"mssql\") {\n    const schema = appDataSource.options?.schema;\n    const sql = `SELECT\n    TABLE_NAME AS table_name,\n    COLUMN_NAME AS column_name,\n    DATA_TYPE AS data_type,\n    IS_NULLABLE AS is_nullable\n    FROM INFORMATION_SCHEMA.COLUMNS\n    ${schema && `WHERE TABLE_SCHEMA = '${schema}'`} \nORDER BY TABLE_NAME, ORDINAL_POSITION;`;\n\n    const rep = await appDataSource.query(sql);\n    return formatToSqlTable(rep);\n  }\n\n  if (appDataSource.options.type === \"sap\") {\n    const schema = appDataSource.options?.schema ?? \"public\";\n    sql = `SELECT\n        TABLE_NAME,\n        COLUMN_NAME,\n        DATA_TYPE_NAME AS data_type,\n        CASE WHEN IS_NULLABLE='TRUE' THEN 'YES' ELSE 'NO' END AS is_nullable\n      FROM TABLE_COLUMNS\n      WHERE SCHEMA_NAME='${schema}'`;\n\n    const rep: Array<{ [key: string]: string }> =\n      await appDataSource.query(sql);\n\n    const repLowerCase: Array<RawResultTableAndColumn> = [];\n    rep.forEach((_rep) =>\n      repLowerCase.push({\n        table_name: _rep.TABLE_NAME,\n        column_name: _rep.COLUMN_NAME,\n        data_type: _rep.DATA_TYPE,\n        is_nullable: _rep.IS_NULLABLE,\n      })\n    );\n\n    return formatToSqlTable(repLowerCase);\n  }\n  if (appDataSource.options.type === \"oracle\") {\n    const schemaName = appDataSource.options.schema;\n    const sql = `  \n      SELECT\n          TABLE_NAME AS \"table_name\",\n          COLUMN_NAME AS \"column_name\",\n          DATA_TYPE AS \"data_type\",\n          NULLABLE AS \"is_nullable\"\n      FROM ALL_TAB_COLS\n      WHERE\n          OWNER = UPPER('${schemaName}')`;\n    const rep = await appDataSource.query(sql);\n    return formatToSqlTable(rep);\n  }\n  throw new Error(\"Database type not implemented yet\");\n};\n\nconst formatSqlResponseToSimpleTableString = (rawResult: unknown): string => {\n  if (!rawResult || !Array.isArray(rawResult) || rawResult.length === 0) {\n    return \"\";\n  }\n\n  let globalString = \"\";\n  for (const oneRow of rawResult) {\n    globalString += `${Object.values(oneRow).reduce(\n      (completeString, columnValue) => `${completeString} ${columnValue}`,\n      \"\"\n    )}\\n`;\n  }\n\n  return globalString;\n};\n\nexport const generateTableInfoFromTables = async (\n  tables: Array<SqlTable> | undefined,\n  appDataSource: DataSource,\n  nbSampleRow: number,\n  customDescription?: Record<string, string>\n): Promise<string> => {\n  if (!tables) {\n    return \"\";\n  }\n\n  let globalString = \"\";\n  for (const currentTable of tables) {\n    // Add the custom info of the table\n    const tableCustomDescription =\n      customDescription &&\n      Object.keys(customDescription).includes(currentTable.tableName)\n        ? `${customDescription[currentTable.tableName]}\\n`\n        : \"\";\n    // Add the creation of the table in SQL\n    let schema = null;\n    if (appDataSource.options.type === \"postgres\") {\n      schema = appDataSource.options?.schema ?? \"public\";\n    } else if (appDataSource.options.type === \"aurora-postgres\") {\n      schema = \"public\";\n    } else if (appDataSource.options.type === \"mssql\") {\n      schema = appDataSource.options?.schema;\n    } else if (appDataSource.options.type === \"sap\") {\n      schema =\n        appDataSource.options?.schema ??\n        appDataSource.options?.username ??\n        \"public\";\n    } else if (appDataSource.options.type === \"oracle\") {\n      schema = appDataSource.options.schema;\n    }\n    let sqlCreateTableQuery = schema\n      ? `CREATE TABLE \"${schema}\".\"${currentTable.tableName}\" (\\n`\n      : `CREATE TABLE ${currentTable.tableName} (\\n`;\n    for (const [key, currentColumn] of currentTable.columns.entries()) {\n      if (key > 0) {\n        sqlCreateTableQuery += \", \";\n      }\n      sqlCreateTableQuery += `${currentColumn.columnName} ${\n        currentColumn.dataType\n      } ${currentColumn.isNullable ? \"\" : \"NOT NULL\"}`;\n    }\n    sqlCreateTableQuery += \") \\n\";\n\n    let sqlSelectInfoQuery;\n    if (appDataSource.options.type === \"mysql\") {\n      // We use backticks to quote the table names and thus allow for example spaces in table names\n      sqlSelectInfoQuery = `SELECT * FROM \\`${currentTable.tableName}\\` LIMIT ${nbSampleRow};\\n`;\n    } else if (appDataSource.options.type === \"postgres\") {\n      const schema =\n        appDataSource.options.type === \"postgres\"\n          ? (appDataSource.options?.schema ?? \"public\")\n          : \"public\";\n      sqlSelectInfoQuery = `SELECT * FROM \"${schema}\".\"${currentTable.tableName}\" LIMIT ${nbSampleRow};\\n`;\n    } else if (appDataSource.options.type === \"mssql\") {\n      const schema = appDataSource.options?.schema;\n      sqlSelectInfoQuery = schema\n        ? `SELECT TOP ${nbSampleRow} * FROM ${schema}.[${currentTable.tableName}];\\n`\n        : `SELECT TOP ${nbSampleRow} * FROM [${currentTable.tableName}];\\n`;\n    } else if (appDataSource.options.type === \"sap\") {\n      const schema =\n        appDataSource.options?.schema ??\n        appDataSource.options?.username ??\n        \"public\";\n      sqlSelectInfoQuery = `SELECT * FROM \"${schema}\".\"${currentTable.tableName}\" LIMIT ${nbSampleRow};\\n`;\n    } else if (appDataSource.options.type === \"oracle\") {\n      sqlSelectInfoQuery = `SELECT * FROM \"${schema}\".\"${currentTable.tableName}\" WHERE ROWNUM <= '${nbSampleRow}'`;\n    } else {\n      sqlSelectInfoQuery = `SELECT * FROM \"${currentTable.tableName}\" LIMIT ${nbSampleRow};\\n`;\n    }\n\n    const columnNamesConcatString = `${currentTable.columns.reduce(\n      (completeString, column) => `${completeString} ${column.columnName}`,\n      \"\"\n    )}\\n`;\n\n    let sample = \"\";\n    try {\n      const infoObjectResult = nbSampleRow\n        ? await appDataSource.query(sqlSelectInfoQuery)\n        : null;\n      sample = formatSqlResponseToSimpleTableString(infoObjectResult);\n    } catch (error) {\n      // If the request fails we catch it and only display a log message\n      console.log(error);\n    }\n\n    globalString = globalString.concat(\n      tableCustomDescription +\n        sqlCreateTableQuery +\n        sqlSelectInfoQuery +\n        columnNamesConcatString +\n        sample\n    );\n  }\n\n  return globalString;\n};\n\nexport const getPromptTemplateFromDataSource = (\n  appDataSource: DataSource\n): PromptTemplate => {\n  if (\n    appDataSource.options.type === \"postgres\" ||\n    appDataSource.options.type === \"aurora-postgres\"\n  ) {\n    return SQL_POSTGRES_PROMPT;\n  }\n\n  if (appDataSource.options.type === \"sqlite\") {\n    return SQL_SQLITE_PROMPT;\n  }\n\n  if (appDataSource.options.type === \"mysql\") {\n    return SQL_MYSQL_PROMPT;\n  }\n\n  if (appDataSource.options.type === \"mssql\") {\n    return SQL_MSSQL_PROMPT;\n  }\n\n  if (appDataSource.options.type === \"sap\") {\n    return SQL_SAP_HANA_PROMPT;\n  }\n\n  if (appDataSource.options.type === \"oracle\") {\n    return SQL_ORACLE_PROMPT;\n  }\n\n  return DEFAULT_SQL_DATABASE_PROMPT;\n};\n"],"mappings":";;AAiDA,MAAa,mCACX,oBACA,YACA,mBACS;CACT,MAAM,iBAAgC,mBAAmB,KACtD,UAAoB,MAAM,SAC7B;CACA,IAAI,WAAW,SAAS,GACjB;OAAA,MAAM,aAAa,YACtB,IAAI,CAAC,eAAe,SAAS,SAAS,GACpC,MAAM,IAAI,MACR,GAAG,eAAe,aAAa,UAAU,+BAC3C;CAAA;AAIR;AAEA,MAAa,sCACX,oBACA,kBACS;CACT,gCACE,oBACA,eACA,uCACF;AACF;AAEA,MAAa,qCACX,oBACA,iBACS;CACT,gCACE,oBACA,cACA,sCACF;AACF;AAEA,MAAM,oBACJ,6BACoB;CACpB,MAAM,WAA4B,CAAC;CACnC,KAAK,MAAM,aAAa,0BAA0B;EAChD,MAAM,YAAY;GAChB,YAAY,UAAU;GACtB,UAAU,UAAU;GACpB,YAAY,UAAU,gBAAgB;EACxC;EACA,MAAM,eAAe,SAAS,MAC3B,aAAa,SAAS,cAAc,UAAU,UACjD;EACA,IAAI,cACF,aAAa,QAAQ,KAAK,SAAS;OAC9B;GACL,MAAM,WAAW;IACf,WAAW,UAAU;IACrB,SAAS,CAAC,SAAS;GACrB;GACA,SAAS,KAAK,QAAQ;EACxB;CACF;CAEA,OAAO;AACT;AAEA,MAAa,yBAAyB,OACpC,kBAC6B;CAC7B,IAAI;CACJ,IACE,cAAc,QAAQ,SAAS,cAC/B,cAAc,QAAQ,SAAS,mBAC/B;EACA,MAAM,SACJ,cAAc,QAAQ,SAAS,aAC1B,cAAc,SAAS,UAAU,WAClC;EACN,MAAM;;;;;;;;gCAQsB,OAAO;sCACD,OAAO;;;;EAIzC,MAAM,MAAM,MAAM,cAAc,MAAM,GAAG;EAEzC,OAAO,iBAAiB,GAAG;CAC7B;CAEA,IACE,cAAc,QAAQ,SAAS,YAC/B,cAAc,QAAQ,SAAS,oBAC/B,cAAc,QAAQ,SAAS,SAC/B;EACA,MACE;EAgBF,MAAM,MAAM,MAAM,cAAc,MAAM,GAAG;EAEzC,OAAO,iBAAiB,GAAG;CAC7B;CAEA,IACE,cAAc,QAAQ,SAAS,WAC/B,cAAc,QAAQ,SAAS,gBAC/B;EACA,MACE,yKAMyB,cAAc,QAAQ,SAAS;EAE1D,MAAM,MAAM,MAAM,cAAc,MAAM,GAAG;EAEzC,OAAO,iBAAiB,GAAG;CAC7B;CAEA,IAAI,cAAc,QAAQ,SAAS,SAAS;EAC1C,MAAM,SAAS,cAAc,SAAS;EACtC,MAAM,MAAM;;;;;;MAMV,UAAU,yBAAyB,OAAO,GAAG;;EAG/C,MAAM,MAAM,MAAM,cAAc,MAAM,GAAG;EACzC,OAAO,iBAAiB,GAAG;CAC7B;CAEA,IAAI,cAAc,QAAQ,SAAS,OAAO;EAExC,MAAM;;;;;;2BADS,cAAc,SAAS,UAAU,SAOlB;EAE9B,MAAM,MACJ,MAAM,cAAc,MAAM,GAAG;EAE/B,MAAM,eAA+C,CAAC;EACtD,IAAI,SAAS,SACX,aAAa,KAAK;GAChB,YAAY,KAAK;GACjB,aAAa,KAAK;GAClB,WAAW,KAAK;GAChB,aAAa,KAAK;EACpB,CAAC,CACH;EAEA,OAAO,iBAAiB,YAAY;CACtC;CACA,IAAI,cAAc,QAAQ,SAAS,UAAU;EAE3C,MAAM,MAAM;;;;;;;;2BADO,cAAc,QAAQ,OASP;EAClC,MAAM,MAAM,MAAM,cAAc,MAAM,GAAG;EACzC,OAAO,iBAAiB,GAAG;CAC7B;CACA,MAAM,IAAI,MAAM,mCAAmC;AACrD;AAEA,MAAM,wCAAwC,cAA+B;CAC3E,IAAI,CAAC,aAAa,CAAC,MAAM,QAAQ,SAAS,KAAK,UAAU,WAAW,GAClE,OAAO;CAGT,IAAI,eAAe;CACnB,KAAK,MAAM,UAAU,WACnB,gBAAgB,GAAG,OAAO,OAAO,MAAM,CAAC,CAAC,QACtC,gBAAgB,gBAAgB,GAAG,eAAe,GAAG,eACtD,EACF,EAAE;CAGJ,OAAO;AACT;AAEA,MAAa,8BAA8B,OACzC,QACA,eACA,aACA,sBACoB;CACpB,IAAI,CAAC,QACH,OAAO;CAGT,IAAI,eAAe;CACnB,KAAK,MAAM,gBAAgB,QAAQ;EAEjC,MAAM,yBACJ,qBACA,OAAO,KAAK,iBAAiB,CAAC,CAAC,SAAS,aAAa,SAAS,IAC1D,GAAG,kBAAkB,aAAa,WAAW,MAC7C;EAEN,IAAI,SAAS;EACb,IAAI,cAAc,QAAQ,SAAS,YACjC,SAAS,cAAc,SAAS,UAAU;OACrC,IAAI,cAAc,QAAQ,SAAS,mBACxC,SAAS;OACJ,IAAI,cAAc,QAAQ,SAAS,SACxC,SAAS,cAAc,SAAS;OAC3B,IAAI,cAAc,QAAQ,SAAS,OACxC,SACE,cAAc,SAAS,UACvB,cAAc,SAAS,YACvB;OACG,IAAI,cAAc,QAAQ,SAAS,UACxC,SAAS,cAAc,QAAQ;EAEjC,IAAI,sBAAsB,SACtB,iBAAiB,OAAO,KAAK,aAAa,UAAU,SACpD,gBAAgB,aAAa,UAAU;EAC3C,KAAK,MAAM,CAAC,KAAK,kBAAkB,aAAa,QAAQ,QAAQ,GAAG;GACjE,IAAI,MAAM,GACR,uBAAuB;GAEzB,uBAAuB,GAAG,cAAc,WAAW,GACjD,cAAc,SACf,GAAG,cAAc,aAAa,KAAK;EACtC;EACA,uBAAuB;EAEvB,IAAI;EACJ,IAAI,cAAc,QAAQ,SAAS,SAEjC,qBAAqB,mBAAmB,aAAa,UAAU,WAAW,YAAY;OACjF,IAAI,cAAc,QAAQ,SAAS,YAKxC,qBAAqB,kBAHnB,cAAc,QAAQ,SAAS,aAC1B,cAAc,SAAS,UAAU,WAClC,SACwC,KAAK,aAAa,UAAU,UAAU,YAAY;OAC3F,IAAI,cAAc,QAAQ,SAAS,SAAS;GACjD,MAAM,SAAS,cAAc,SAAS;GACtC,qBAAqB,SACjB,cAAc,YAAY,UAAU,OAAO,IAAI,aAAa,UAAU,QACtE,cAAc,YAAY,WAAW,aAAa,UAAU;EAClE,OAAO,IAAI,cAAc,QAAQ,SAAS,OAKxC,qBAAqB,kBAHnB,cAAc,SAAS,UACvB,cAAc,SAAS,YACvB,SAC4C,KAAK,aAAa,UAAU,UAAU,YAAY;OAC3F,IAAI,cAAc,QAAQ,SAAS,UACxC,qBAAqB,kBAAkB,OAAO,KAAK,aAAa,UAAU,qBAAqB,YAAY;OAE3G,qBAAqB,kBAAkB,aAAa,UAAU,UAAU,YAAY;EAGtF,MAAM,0BAA0B,GAAG,aAAa,QAAQ,QACrD,gBAAgB,WAAW,GAAG,eAAe,GAAG,OAAO,cACxD,EACF,EAAE;EAEF,IAAI,SAAS;EACb,IAAI;GACF,MAAM,mBAAmB,cACrB,MAAM,cAAc,MAAM,kBAAkB,IAC5C;GACJ,SAAS,qCAAqC,gBAAgB;EAChE,SAAS,OAAO;GAEd,QAAQ,IAAI,KAAK;EACnB;EAEA,eAAe,aAAa,OAC1B,yBACE,sBACA,qBACA,0BACA,MACJ;CACF;CAEA,OAAO;AACT;AAEA,MAAa,mCACX,kBACmB;CACnB,IACE,cAAc,QAAQ,SAAS,cAC/B,cAAc,QAAQ,SAAS,mBAE/B,OAAOA,sBAAAA;CAGT,IAAI,cAAc,QAAQ,SAAS,UACjC,OAAOC,sBAAAA;CAGT,IAAI,cAAc,QAAQ,SAAS,SACjC,OAAOC,sBAAAA;CAGT,IAAI,cAAc,QAAQ,SAAS,SACjC,OAAOC,sBAAAA;CAGT,IAAI,cAAc,QAAQ,SAAS,OACjC,OAAOC,sBAAAA;CAGT,IAAI,cAAc,QAAQ,SAAS,UACjC,OAAOC,sBAAAA;CAGT,OAAOC,sBAAAA;AACT"}