BChdZddlZddlZddlmZmZmZmZddlm Z m Z ddl m Z m Z ejeZGddZGdd eZGd d eZGd d eZGddeZdede defdZy)z: Database-specific connector implementations for SQLGenAI N)DictAnyListOptional)textinspect)Engine Connectionc eZdZdZdefdZdedefdZdeee fdeee ffdZ dded e d e deee ffd Z d e dedee fd ZdedefdZdeedeeee fdeee ffdZdeee ffdZy)BaseDBConnectorz+Base class for database-specific connectorsenginec||_y)z#Initialize with a SQLAlchemy engineN)r )selfr s +/var/www/html/app/database/db_connectors.py__init__zBaseDBConnector.__init__s  sql_queryreturnc|S)zTransform a query for database-specific compatibility Args: sql_query: The original SQL query Returns: The transformed SQL query rrs rtransform_queryzBaseDBConnector.transform_querys rconnection_configc t|j}|jddgd}|jD][}|dgd}|j |D]+}|dt |ddd}|dj |-|d j |]|S#t$r8}tjd t |d t |icYd }~Sd }~wwxYw) zGet the database schema Args: connection_config: Database connection configuration Returns: Dictionary with database schema information databaseNamedatabasetablesname descriptioncolumnsr!typer!dataTyper"r#rzError getting database schema: errorN) rr getget_table_names get_columnsstrappend Exceptionloggerr') rr inspector schema_data table_name table_infocolumn column_infoes r get_schemazBaseDBConnector.get_schemas % ,I.11."EK (779 9 &#%! (33J?>F &v$'v$7')#K y)00= >H%,,Z8! 9$  % LL:3q6(C DSV$ $ %sBB C%-CCClimitoffsetc r|j|} |jj5}|jt |}|j rt |j}|dkDr|j|g}|j|D]q} i} t|D]N\} } | | } t| tttttdt t fs t| } | | | <P|j#| s|j%||}d||t'|||||duxr|t'|z|kdd cdddSdd|j(dcdddS#1swYyxYw#t*$r9}t,j/dt|dt|d cYd}~Sd}~wwxYw) a!Execute a SQL query and return the results Args: sql_query: The SQL query to execute limit: Maximum number of rows to return offset: Number of rows to skip Returns: Dictionary with query results rNT) successr#rowsrowCount totalCountr8r7hasMoreexecution_timezQuery executed successfully)r:messagerowcountError executing query: Fr:r')rr connectexecuter returns_rowslistkeys fetchmany enumerate isinstancer+intfloatboolr$dictr,get_total_countlenrAr-r.r')rrr7r8transformed_query connectionresultr#r;rowrow_dicticolvalue total_countr5s r execute_queryzBaseDBConnector.execute_queryIs!00;2 7$$&. *#++D1B,CD&&"6;;=1Gz((0D%//6 .#%&/&82FAs$'FE#-ec3tTRVZY]_c5d#e(+E ,1HSM 2 H- .#'"6"6zCT"UK$(#* $$'I&1"(!&#.d#:#aT@RU`@`*. ;. . V$(#@$*OOU. . . ^ 7 LL23q6(; <$s1v6 6 7sGE4DE( E4E( E4(E1-E41E44 F6=.F1+F61F6rScd} |jjjdrx|j}|jdr|dd}d|d}|j t |}|j }|r d|vr|d}|S|rt|dkDr|d}|S#t$r,}tjd t|Yd}~|Sd}~wwxYw) zGet the total row count for a query Args: connection: SQLAlchemy connection sql_query: The SQL query Returns: Total row count or None if it can't be determined NSELECT;%SELECT COUNT(*) AS total_count FROM () AS count_queryrZrzCould not get total row count: ) upperstrip startswithendswithrErfetchonerQr-r.warningr+ rrSrrZ clean_query count_query count_result count_rowr5s rrPzBaseDBConnector.get_total_counts  G &&(33H='oo/ '',"-cr"2K!Fk]Rbc )11${2CD (113 )!;"+M":K 3y>A#5"+A,K   G NNF &v$'v$7')#K y)00= >'88DHHI^`bc #-i#8;K"6*j86: N3;H%,,Z8- 90  % LL7Ax@ ASV$ $ %s$B4C7C D-D DDrScZd} |jjjdr|j}|jdr|dd} d|d}|j t |}|j }|rt|dr |j}n|rt|dkDr|d}|S |S#t$r,}tjd t|Yd}~|Sd}~wwxYw#t$r,}tjd t|Yd}~|Sd}~wwxYw) z,Get the total row count for a query in MySQLNr]r^r_r`rarZrzCould not get count for query: zError getting total count: )rbrcrdrerErrfhasattrrZrQr-r.rgr+rhs rrPzMySQLConnector.get_total_counts5  C &&(33H='oo/ '',"-cr"2K O$I+Vf"gK#-#5#5d;6G#HL , 5 5 7I WY %F&/&;&; "s9~'9&/l ->, !ONN%DSVH#MNN O C NN8QA B B Cs=AC5A B== C2!C-'C5-C22C55 D*>!D%%D*c d|S)z+Get the appropriate EXPLAIN query for MySQLzEXPLAIN FORMAT=JSON rrs rrnz MySQLConnector.get_explain_query0s%i[11rr#r;c d|dS)zParse the MySQL EXPLAIN resultmysqlrqrrts rruz#MySQLConnector.parse_explain_result4s  rc <giddd} |jj5}|jtd}|j }|r-t |dkDr|d|d<|dj d|d<|jtd}|j }|rt |dkDr|d|d <gd }|D]O} |jt|}|j }|r |jD] }|||d |<Q d d d |S#t$r+}tjd t|Yd }~d }~wwxYw#1swY|SxYw#t$r,}tjdt|Yd }~|Sd }~wwxYw)z|Get MySQL configuration settings Returns: Dictionary with MySQL configuration settings rrwzSELECT @@SESSION.sql_moderrz,rxzSELECT VERSION()r{)z1SELECT @@innodb_strict_mode AS innodb_strict_modez.SELECT @@character_set_server AS character_setz&SELECT @@collation_server AS collationz1SELECT @@max_allowed_packet AS max_allowed_packetryzError fetching MySQL setting: Nz$Error fetching MySQL configuration: ) r rDrErrfrQsplitrHr-r.rgr+) rconfigrSrTrUsettings_queriesquerykeyr5s rr|zMySQLConnector.get_db_config;s  ! L$$& R*#++D1L,MNoo'3s8a<),QF:&&)!fll3&7F7O$++D1C,DEoo'3s8a<(+AF9%$ .RER!+!3!3DK!@$oo/'*xxzC:=c(z 23 7C R- RD %R)GAx'PQQR; RD  L NNA#a&J K K  Ls`E&B%E A D"EE&" E+!E EEEE#E&#E&& F/!FFN)r~rrrr+rrrr6r rrLrPrnrrur|rrrrrs+/%DcN/%tCH~/%b*RU:23232 DI T$sCx.=Q VZ[^`c[cVd 0tCH~0rrceZdZdZdedefdZdeedeeeefdeeeffdZ deeefdeeeffd Z deeeffd Z y ) PostgreSQLConnectorz&PostgreSQL-specific database connectorrrc d|S)z0Get the appropriate EXPLAIN query for PostgreSQLzEXPLAIN (FORMAT JSON) rrs rrnz%PostgreSQLConnector.get_explain_queryps' {33rr#r;c d|dS)z#Parse the PostgreSQL EXPLAIN result postgresqlrqrrts rruz(PostgreSQLConnector.parse_explain_resultts#  rrcn t|j}|jdd}|jdxsd}|gd}|j|D]}|dgd}|j ||D]+}|dt |d dd } |d j | -|j||} | jd g} |d D]} | d| vs d | d<|dj ||S#t$r8} tjdt | dt | icYd} ~ Sd} ~ wwxYw)zGet the PostgreSQL database schema Args: connection_config: Database connection configuration Returns: Dictionary with database schema information rr schema_namepublicrschemar r!r$r%r#rTrrz!Error getting PostgreSQL schema: r'Nr) rrr/rrr0r1r2r3r4 pk_constraintrr5s rr6zPostgreSQLConnector.get_schema{sv) % ,I-11."EM+// >J(K*K (77{7K 9 &#%! (33J{3S>F &v$'v$7')#K y)00= >!* ; ;J{ ; [ *../DbI #-i#8;K"6*j86: N3;H%,,Z8/ 92  % LLF &v$'v$7')#K y)00= >!* ; ;J{ ; [ *../DbI #-i#8;K"6*j86: N3;H%,,Z8/ 92  % LLC C D!)-DD!D!cgiddd} |jj5} |jtd}|j }|r|d|d<|jtd}|j }|r=|j D]*}|||d|<||dk(s|dj |,d d d |S#t$r+}tjd t|Yd }~9d }~wwxYw#1swY|SxYw#t$r,}tjd t|Yd }~|Sd }~wwxYw) zGet SQL Server configuration settings Returns: Dictionary with SQL Server configuration settings rrwzSELECT @@VERSION AS versionr{aR SELECT CASE WHEN (16384 & @@OPTIONS) = 16384 THEN 1 ELSE 0 END AS ANSI_NULLS, CASE WHEN (8 & @@OPTIONS) = 8 THEN 1 ELSE 0 END AS ANSI_PADDING, CASE WHEN (4 & @@OPTIONS) = 4 THEN 1 ELSE 0 END AS ANSI_WARNINGS ryrxz$Error fetching SQL Server settings: Nz)Error fetching SQL Server configuration: ) r rDrErrfrHr,r-r.rgr+)rrrSrTrU ansi_settingsrr5s rr|z SQLServerConnector.get_db_configsa   Q$$& T*T'//5R0STF //+C,/ Ny)%/$6$6t=8%M (002C#&88: 2.' FROM DUAL UNION ALL SELECT 'RECOMMENDATIONS', 'Consider indexes on join columns (DEPARTMENT_ID) and GROUP BY columns (EMPLOYEE_ID, FIRST_NAME, LAST_NAME).' FROM DUAL UNION ALL SELECT 'RECOMMENDATIONS', 'For detailed execution plan, try running this query in Oracle SQL Developer.' FROM DUAL z+ [ORACLE] Error setting up schema context: a- SELECT 'Oracle Execution Plan' as plan_section, 'The execution plan could not be generated automatically.' as plan_info FROM DUAL UNION ALL SELECT 'Query Analysis', 'Consider checking table statistics and indexes for the tables in your query.' FROM DUAL UNION ALL SELECT 'Recommendation', 'You may need to run this query through Oracle SQL Developer or another tool with explain plan capabilities.' FROM DUAL )r urltranslate_connect_argsr(rbrDprintrrEfetchallrefindall IGNORECASErr,scalarjoinrr-r+)rrconnection_inforrS schemas_queryschemas_resultrUavailable_schemasr table_pattern table_matchesmatch table_namesclean_table_namesrrr1potential_schemas check_queryrTcount target_schema tables_listr5s rrnz!OracleConnector.get_explain_queryGs++//@@B"&&z26<<>[[ "M QjL Q|dj|  ddd|Scc} w#1swY|SxYw#t$r8}tjdt|dt|icYd}~Sd}~wwxYw) zGet the Oracle database schema Args: connection_config: Database connection configuration Returns: Dictionary with database schema information z- **** ORACLE GET_SCHEMA METHOD CALLED **** rrr serviceNamez([DEBUG] Getting Oracle schema for user: z[DEBUG] Database/SID: z[DEBUG] Service Name: z Not providedrz#[DEBUG] Querying schema for owner: zSELECT DISTINCT TABLE_NAME FROM ALL_TABLES WHERE OWNER = :owner ORDER BY TABLE_NAMEownerrz[DEBUG] Found z tables for owner r aSELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, COLUMN_ID FROM ALL_TAB_COLS WHERE OWNER = :owner AND TABLE_NAME = :table_name ORDER BY COLUMN_ID)rr1r()Y)r!r&r"nullabler#rNzError getting Oracle schema: r')rr(rbr rDrrErrQr,r-r.r'r+)rrrr service_namer0 target_ownerrS table_query table_resultrUrr1r2 column_query column_resultr3r4r5s rr6zOracleConnector.get_schemasr ABM %+// B?EEGK-11."EM,00CL <[MJ K *=/: ; *<<^*\] ^*K -:={L 7 ~F G$$&3 =*".  *11+I[I[I]?^_ ,8,A,A,CDS#a&DDs6{m3El^TU#)$=J *')#%"J$( 1 $L%/$6$6$"."4"4"6jQ%M #0"8"8":B$*1IGMay6!9+Qvayk(CV\]^V_+-(.q S(8 ' #9-44[AB )00B:G9 GGG GG H-H HHr7r8c|j|} |jj5}|jjj }|j ddj }ddl}d} |j| ||j} | D cgc]} | dxs| d} } |g} t}| D]:}d|vs|jd\}}|j|j <| jt||sV| D]Q}d|vs td|j d }|j!|}|D]}| j#|dSg}| D]}||vs|j#|t'd |d d}|D]e} t'd|d |j!td||j!t|}|j*rt|j-}|dkDr|j/|g}|j/|D]q}i}t1|D]N\}}||}t3|t(t4t6t8t;dtt<fs t)|}|||<P|j#|sd} |j?||}d||tE|||dccdddSd|jFd|jF|dccdddS|r|t%dcc} w#t$$r%}t'd |d t)|Yd}~,d}~wwxYw#t$$r+}t@jCdt)|Yd}~d}~wwxYw#t$$r(}t'd|d t)|d |}Yd}~d}~wwxYw#1swYyxYw#t$$r9}t@jIdt)|dt)|dcYd}~Sd}~wwxYw)aExecute a SQL query and return the results For Oracle, we need to set the schema context before executing queries to ensure the database can find the tables referenced in the query. Args: sql_query: The SQL query to execute limit: Maximum number of rows to return offset: Number of rows to skip Returns: Dictionary with query results rrrNrrrz1SELECT OWNER FROM ALL_TABLES WHERE TABLE_NAME = 'rzError finding schema for table z: z* [ORACLE] Will try schemas in this order: rz$ [ORACLE] Setting schema context to rzCould not get total count: T)r:r#r;r<r= schema_usedz,Query executed successfully. Rows affected: )r:r<r@rz, [ORACLE] Error executing query with schema z9Could not find a valid schema for the tables in the queryrBFrC)%rr rDrrr(rbrrrsetraddextendrGrrEr,r-rr+rFrHrIrJrKrLrMrNr$rOrPr.rgrQrAr') rrr7r8rRrSrrrrrrrschemas_to_tryexplicit_schemasrr_ schema_query schema_resultrUr5unique_schemas last_errorrTr#r;rVrWrXrYrZs rr[zOracleConnector.execute_queryqss!00;u 7$$&p a*"&++//"H"H"J*..z2>DDF H " =)R]] S ANOuQx3583O O#+#&5 (=Ee|$)KK$4 (,,V\\^<= %%d+;&<=(!, [e+[/36ghmhshshugvvw4x/y 0:0B0B<0P +8!BC$2$9$9#a&$A!B ["$,6F^3&--f56CNCSSUVW" ,;!F:! EfXRPQ"**42UV\U]0^+_`",!3!3D9J4K!L!..&*6;;=&9G &z & 0 0 8$&D'-'7'7'> 6+-.7.@!:FAs,/FE+5ec3tUYZ^U_aegk=l+m03E 49HSM !:!% H 5 6+/KW.2.B.B:y.Y ,0+2(,,/I.9/5 $op ap aD,0,2OO-YZ`ZiZiYj+k/5 $Cp ap a^;!|$$#$_``OP2$-[ %(GwbQTUVQWPX&Y Z Z[d$-W &1LSQRVH/U V VW&%! MfXUWX[\]X^W__abc%&  !Op ap ad 7 LL23q6(; <$s1v6 6 7sOA,N7L,N7AN7!A L* N78*N7#D N.M NN7 ON:N7< ON7 M $M>N7M  N7 N!M;6N;NN N4 N/)N7/N44N77O<OO P .P:PPrScddl}|jd|j|jz}|j |}|r|j d}|jd|j|jz}|j |}|r|j dnd} d|d| } |j t| } | j} | r| dSdSy)z-Get the total row count for a query in OraclerNzC\bFROM\s+.*?(?=\bWHERE\b|\bGROUP\s+BY\b|\bORDER\s+BY\b|\bLIMIT\b|$)z:\bWHERE\s+.*?(?=\bGROUP\s+BY\b|\bORDER\s+BY\b|\bLIMIT\b|$)rzSELECT COUNT(*) AS total_count  ) rcompilerDOTALLsearchgrouprErrf) rrSrr from_pattern from_match from_clause where_pattern where_match where_clauserjrTrUs rrPzOracleConnector.get_total_counts zz"hjljwjwz|{D{DkDE !((3 $**1-KJJ'dfhfsfsvxvvfAM'..y9K3>;,,Q/BL;K=,XK''[(9:F//#C 3q6 'a 'rc:giddd} |jj5} |jtd}|j }|rt |dkDr|d|d<|jtd}|D]}|d|d|d< d d d |S#t $r+}tjd t|Yd }~9d }~wwxYw#1swY|SxYw#t $r,}tjd t|Yd }~|Sd }~wwxYw) z~Get Oracle configuration settings Returns: Dictionary with Oracle configuration settings rrwz-SELECT BANNER FROM V$VERSION WHERE ROWNUM = 1rr{z SELECT PARAMETER, VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER IN ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT', 'NLS_NUMERIC_CHARACTERS') rryz Error fetching Oracle settings: Nz%Error fetching Oracle configuration: ) r rDrErrfrQr-r.rgr+)rrrSrTrU nls_paramsr5s rr|zOracleConnector.get_db_config s;   M$$& P*P'//5d0efF //+Cs3x!|,/Fy)",!3!3D:5"J *<58Vz*3q62< P( !PNN%Ec!fX#NOOP P(  M NNB3q6(K L L  Ms^C%CA1B!CC%! C*!C CCCC"C%"C%% D.!DDNr})r~rrrr+rnrrrrur6rLr[r rPr|rrrrrDs,l 3l 3l \  DI  T$sCx.=Q  VZ[^`c[cVd  DX%DcNX%tCH~X%tE7sE73E7sE7SWX[]`X`SaE7N*("tCH~"rrdb_typer rc|j}|dvr t|S|dvr t|S|dvr t|S|dvr t |St j d|dt|S)zGet the appropriate connector for a database type Args: db_type: Database type (mysql, postgresql, sqlserver, oracle) engine: SQLAlchemy engine Returns: Database-specific connector instance )rmariadb)rpostgres)rmssql)rzUnknown database type: z, using base connector)lowerrrrrr.rgr )rr s r get_connectorr 1smmoG&&f%% . ."6** * *!&)) J v&& 0 9OPQv&&r)rloggingtimetypingrrrr sqlalchemyrrsqlalchemy.enginer r getLoggerr~r.r rrrrr+r rrrrs ,,$0   8 $G G RV_Vpi/iVjjXjojZ'3''?'r