
    BCh6                         d Z ddlZddlZddlmZmZmZmZmZ ddl	m
Z
 ddlmZmZmZ ddlmZmZ ddlmZ ddlmZmZmZmZmZmZ  ej6                  e      Z G d	 d
      Zy)zl
Database connector module for SQLGenAI
Provides direct database connections without requiring OpenMetadata
    N)DictAnyListOptionalTuple)
quote_plus)create_engineinspecttext)Engine
Connection)SQLAlchemyError)BaseDBConnectorMySQLConnectorPostgreSQLConnectorSQLServerConnectorOracleConnectorget_connectorc                      e Zd ZdZd Zdedeeef   defdZ	dedeeef   defdZ
d!ded	ed
edededededefdZdedefdZdedeeef   deeef   fdZdedeeef   deeef   fdZd"dedeeef   dededeeef   f
dZ	 d#dedeeef   dedededeeef   fdZdedeeef   deeef   fdZd$dedededeeef   fdZd Zd Zd Zd Zd Zd%d Zy)&DatabaseConnectorz"Base class for database connectorsc                     i | _         y)z!Initialize the database connectorN)engines)selfs    */var/www/html/app/database/db_connector.py__init__zDatabaseConnector.__init__   s	        db_typeconnection_configreturnc                     | d|j                  d       d|j                  d       }|| j                  v r| j                  |   S | j                  ||      }|| j                  |<   |S )zIGet a SQLAlchemy engine for the specified database type and configuration:hostPortdatabaseName)getr   _create_engine)r   r   r   connection_keyengines        r   
get_enginezDatabaseConnector.get_engine   st    #9A&7&;&;J&G%HJ[J_J_`nJoIpqT\\)<<//$$W.?@'-^$r   c           	      ^   |j                  d      }|j                  d      }|j                  dd      }|j                  dd      }|j                  dd      }d|v r|j                  d      \  }}	n|}| j                  |      }	t        d       t        d	|        t        d
|        t        d|	        t        d|        t        d|r|nd        t        d|        | j	                  |||||	||      }
|r|
j                  |d      n|
}t        d| d       t        |
      S )zLCreate a SQLAlchemy engine for the specified database type and configurationusernamepasswordr"    r#   serviceNamer!   z
[DEBUG] Connection Parameters:z  DB Type: z  Host: z  Port: z  Database Name: z  Service Name: zNot providedz  Username: z********z  Connection URL: 
)r$   split_get_default_portprint_get_connection_urlreplacer	   )r   r   r   r*   r+   	host_portdatabase_nameservice_namehostportconnection_url
masked_urls               r   r%   z DatabaseConnector._create_engine'   sJ   $((4$((4%))*b9	)--nbA(,,]B?)"-JD$D))'2D 	02G9%&  !-12 > RSTXJ'(11Xxt]L

 FN^++HjASa
":,b12^,,r   Nr*   r+   r7   r8   r5   r6   c                 h   |j                         }|dv rd| dt        |       d| d| d| 
S |dv rd| dt        |       d| d| d| 
S |dv rd	| dt        |       d| d| d| d
S |dv r6|rd| dt        |       d| d| d| 
S d| dt        |       d| d| d| 
S t        d|       )zFGet a connection URL for the specified database type and configurationmysqlmariadbzmysql+pymysql://r!   @/
postgresqlpostgreszpostgresql://	sqlservermssqlzmssql+pyodbc://z%?driver=ODBC+Driver+17+for+SQL+Serveroraclezoracle+cx_oracle://z/?service_name=zUnsupported database type: )lowerr   
ValueError)r   r   r*   r+   r7   r8   r5   r6   s           r   r2   z%DatabaseConnector._get_connection_urlH   sA   --/**%hZqH1E0FavQtfTUVcUdee22"8*Aj.B-C1TF!D6QRS`Rabb..$XJa
80D/EQtfAdVSTUbTc  dI  J  J
",XJa
88L7MQtfTUVZU[[jkwjxyy -XJa
88L7MQtfTUVZU[[\]j\kll:7)DEEr   c                 L    |j                         }|dv ry|dv ry|dv ry|dv ryy)	z4Get the default port for the specified database typer<   3306rA   5432rD   1433rG   1521)rI   )r   r   s     r   r0   z#DatabaseConnector._get_default_port]   s?    --/**22..
"r   c                    	 t        d| d       t        d|        | j                  ||      }|j                         }|j                          t        d       dddS # t        $ rL}t        |      }t        d       t        d	| d
       t        j                  d|        d|dcY d}~S d}~wt        $ rO}t        |      }t        d       t        d	| d
       t        j                  d|        dd| dcY d}~S d}~ww xY w)zTest the database connectionz 
[DEBUG] Testing connection for r!   z  Connection config: z"  Connection test result: SUCCESS
TzConnection successful)successmessagez2  Connection test result: FAILED (SQLAlchemyError)z	  Error: r.   zError testing connection: FNz3  Connection test result: FAILED (Unexpected error)z%Unexpected error testing connection: zUnexpected error: )	r1   r(   connectcloser   strloggererror	Exception)r   r   r   r'   
connectione	error_msgs          r   test_connectionz!DatabaseConnector.test_connectionl   s   	S5gYa@A)*;)<=>__W.?@F)J79#0GHH 	<AIFHIi[+,LL5i[AB$;; 	SAIGIIi[+,LL@LM$3Ei[1QRR	Ss2   AA! !	D*AB1+D1D=ADDDc                     t        d       	 | j                  ||      }t        ||      }|j                  |      S # t        $ r8}t
        j                  dt        |              dt        |      icY d}~S d}~ww xY w)zIGet the database schema using the appropriate database-specific connectorz4

**** MAIN GET_DATABASE_SCHEMA METHOD CALLED ****

zError getting database schema: rW   N)r1   r(   r   
get_schemarX   rV   rW   rU   )r   r   r   r'   db_connectorrZ   s         r   get_database_schemaz%DatabaseConnector.get_database_schema   s|     	HI	%__W.?@F )&9L  **+<== 	%LL:3q6(CDSV$$	%s   .< 	A=-A82A=8A=	sql_query
model_typec                    	 |j                         dk(  r6t        d| d       d|v r|d   rt        d|d    d       nt        d       | j                  ||      }t        ||      }|j	                         5 }|j                  |      }|j                  t        |            }	t        |	j                               }
g }|	j                         D ]q  }i }t        |
      D ]N  \  }}||   }t        |t        t        t        t         t#        d      t        t$        f      st        |      }|||<   P |j'                  |       s |j)                  |
|      }| j+                  ||
|||      }d|
|||d	cddd       S # 1 sw Y   yxY w# t,        $ r9}t.        j1                  d
t        |              dt        |      dcY d}~S d}~ww xY w)z&Get the execution plan for a SQL queryrH   z2
[EXPLAIN QUERY] Oracle connection config before: r.   r-   z,
[EXPLAIN QUERY] Using Oracle service_name: zI
[EXPLAIN QUERY] WARNING: No service_name provided for Oracle connection
NT)rQ   columnsrowsexplain_dataperformance_analysisz$Error getting query execution plan: FrQ   rW   )rI   r1   r(   r   rS   get_explain_queryexecuter   listkeysfetchall	enumerate
isinstancerU   intfloatbooltypedictappendparse_explain_result_analyze_explain_planrX   rV   rW   )r   r   r   ra   rb   r'   r_   rY   explain_queryresultrd   re   rowrow_dicticolvaluerf   analysisrZ   s                       r   rx   zDatabaseConnector.explain_query   s   5	7}}(*KL]K^^`ab $55:KM:ZIJ[\iJjIkkmnogi __W.?@F )&9L! "Z , > >y I $++D,?@ v{{}-!??, 
*C!H"+G"4 .3 #A)%#sE4dUY[_1`a$'JE(-. KK)
*  ,@@$O  55gwiYcd  $& $0,49" " "F  	7LL?AxHI$s1v66	7s=   A7F 9C9E<2	F <FF F 	G
.G?G
G
limitoffsetc                 x   	 | j                  ||      }t        ||      }t        j                         }|j                  |||      }	t        j                         }
|	j	                  dd      r|
|z
  |	d<   |	S # t
        $ r9}t        j                  dt        |              dt        |      dcY d}~S d}~ww xY w)z*Execute a SQL query and return the resultsrQ   Fexecution_timezError executing query: rh   N)	r(   r   timeexecute_queryr$   rX   rV   rW   rU   )r   r   r   ra   r   r   r'   r_   
start_timery   end_timerZ   s               r   r   zDatabaseConnector.execute_query   s    	7__W.?@F )&9L J!//	5&IFyy{H zz)U++3j+@'(M 	7LL23q6(;<$s1v66	7s   A4A7 7	B9 .B4.B94B9c           	         	 | j                  ||      }t        ||      }|j                         }||d<   d|dS # t        $ r@}t        j                  dt        |              dt        |      |g i ddddcY d	}~S d	}~ww xY w)
a=  Get database configuration settings including flags and SQL modes
        
        Args:
            db_type: Database type (mysql, postgresql, sqlserver, oracle)
            connection_config: Connection configuration
            
        Returns:
            Dictionary with database configuration settings
        r   T)rQ   configz&Error getting database configuration: Fr,   )r   flagssettingssql_modeversion)rQ   rW   r   N)r(   r   get_db_configrX   rV   rW   rU   )r   r   r   r'   r_   r   rZ   s          r   r   zDatabaseConnector.get_db_config   s    	__W.?@F )&9L "//1F !(F9      	LLA#a&JK Q& " "!
 
	s   7: 	B5A>8B>Bc           
      
   g }g }g }	 t        |      }	|j                         dv r| j                  |||||       nw|j                         dv r| j                  |||||       nO|j                         dv r| j	                  |||||       n'|j                         dv r| j                  |||||       | j                  |	|||       |r	 ddlm}
 ddl	m
} ddl}dd	lm} 	 ddl}d
}|xs |
j$                  j'                  dd      }t)        d| d       |j+                  |      }t-               }	 |j/                  d|j0                        }|j/                  d|j0                        }|j/                  d|j0                        }|j/                  d|j0                        }|j3                  |j5                  |             |j3                  |j5                  |             |j3                  |j5                  |             |j3                  |j5                  |             t         j7                  d|        g }	 |
j$                  j'                  di       }|r| j;                  ||      } ||      }|D ]  }|g d}	 |j=                  |      D ]  }|d   t        |d         |j'                  dd
      d}|j?                  |      } | rd| v r|d   | d   v |d<   g |d<   |jA                  |      D ]*  }!|d   |!d    v s|d   jC                  |!d   |!d!   d"       , |d#   jC                  |        |jC                  |        |||||d'}"|jE                  |"||      }#|#j'                  d(d      r[|#j'                  d)g       }$|$r|$}t         j7                  d*| d+       |#j'                  d,      }%|%rt         j7                  d-| d+       i }&|s|s
d/d0g g d1gd2}&n|r	d3d4|||d2}&nd5d6|||d2}&d,tG               v r%r|%|&d,<   |&S # t        $ r d}t         j#                  d       Y Ew xY w# t8        $ r,}t         j#                  dt        |              Y d}~&d}~ww xY w# t8        $ r/}t         j#                  d$| d%t        |              Y d}~$d}~ww xY w# t8        $ r,}t         j#                  d&t        |              Y d}~d}~ww xY w# t8        $ r,}t         j#                  d.t        |              Y d}~:d}~ww xY w# t8        $ rW}t         j#                  d7t        |              d8d9g d:t        |       gd;d<gd2}&d,tG               v r%r|%|&d,<   |&cY d}~S d}~ww xY w)=z9Analyze the explain plan for potential performance issuesr<   rA   rD   rG   r   )current_app)AIModelFactoryN)r
   TFz7sqlparse module not found. SQL parsing will be limited.AI_MODELdeepseekz[EXPLAIN] Using AI model type: z for explain plan analysisz\bFROM\s+[`"]?([\w\d_]+)[`"]?z\bJOIN\s+[`"]?([\w\d_]+)[`"]?z\bUPDATE\s+[`"]?([\w\d_]+)[`"]?z&\bINSERT\s+INTO\s+[`"]?([\w\d_]+)[`"]?z"Extracted table names from query: z)Error extracting table names from query: LAST_CONNECTION_CONFIG)namerd   r   rs   nullable)r   dataTyper   constrained_columnsisPrimaryKeyindexescolumn_namesunique)r   r   rd   zError getting schema for table z: zError getting table schemas: )rd   re   issues_detectedwarnings_detectedtable_schemasrQ   recommendationsz(Using AI-generated recommendations from z modeladditional_infoz&Additional information available from z"Error getting AI recommendations: goodz:No performance issues detected in the query execution planzPThe query appears to be using efficient access methods and execution strategies.)statusrR   issueswarningsr   poorz7Performance issues detected in the query execution planwarningzEPotential performance concerns identified in the query execution planzError analyzing explain plan: unknownz#Unable to analyze query performancezAnalysis error: z4Consider reviewing the query execution plan manuallyz-Check for missing indexes or table statistics)$rU   rI   _analyze_mysql_explain_analyze_postgresql_explain_analyze_sqlserver_explain_analyze_oracle_explain_analyze_general_patternsflaskr   
app.modelsr   re
sqlalchemyr
   sqlparseImportErrorrV   r   r   r$   r1   	get_modelsetcompile
IGNORECASEupdatefindallinforX   r(   get_columnsget_pk_constraintget_indexesru   analyze_explain_planlocals)'r   r   rd   re   ra   rb   r   r   r   plan_strr   r   r   r
   r   has_sqlparseai_model_typeai_modeltable_namesfrom_patternjoin_patternupdate_patterninsert_patternrZ   r   r   r'   	inspector
table_name
table_infocolumncolumn_infopk_constraintindexrf   	ai_resultai_recommendationsr   ry   s'                                          r   rw   z'DatabaseConnector._analyze_explain_plan  s   |	4yH }}"66++D'68_]$>>00wRab$:://gvxQ`aJ.,,T7FHo^ **8VXW sR192b''+ %/$`+2D2D2H2HU_2`M;M?Jdef  .77FH #&%K] (*zz2RTVTaTa'b')zz2RTVTaTa'b)+4VXZXeXe)f)+4]_a_l_l)m $**<+?+?	+JK#**<+?+?	+JK#**>+A+A)+LM#**>+A+A)+LM&H$VW
 %'M*Q,7,>,>,B,BC[]_,`),%)__W>O%PF(/I.9 !m
,6/1."

!m2;2G2G
2S %R4:6N8;F6N8K8>

:t8T7* 9B8S8ST^8_+8=RVc=cJPQW.\ij  ]A  KAK,G BDI(>5>5J5J:5V )3E/5f~~AV/V0;I0F0M0M<A&M>CHoO2 13)3 )39(=(D(D[(Q+%R. %2$8$8$D?!mN $+ $+1-5)6$L !) = =lGU^ _I }}Y6-6]];Lb-Q*-.@O"KK*RS`Raag(hi +4--8I*J*"KK*PQ^P__e(fg F($[  "(z'{ $X$ ('6 (f$ ('6 !FH,,;()MY ' b','`ab> % ])RSVWXSYRZ'[\\]X (1 !m$*NN5TU_T``bcfghcibj3k$l$l!m$ Q)Fs1vh'OPPQ6 ! RNN%GAx#PQQRL  	NN;CF8DE#@/Ax89$Z  ]L  $MF !FH,,;()M	s   CT 
S !P	 'A
S 2DP0 :S =AR#  B Q(AQ(R# 	BS 7T 	 P-)S ,P--S 0	Q%9!Q S  Q%%S (	R 1$RR# R  R# #	S,!SS SS 	T$!TT TT 	U3AU.(U3.U3c           	         t        |      }d|j                         v sd|j                         v rK|j                  d       | j                  |      }|r|j                  d| d       n|j                  d       d|j                         v r"|j                  d       |j                  d	       d
|j                         v sd|j                         v r"|j                  d       |j                  d       d|j                         v r"|j                  d       |j                  d       d|j                         v r	 |j                         j	                  d      d   j	                  d      }t        dj                  t        t         j                  |d                     }	|	dk  r:d|j                         v r'|j                  d|	 d       |j                  d       yyyy#  Y yxY w)z"Analyze MySQL/MariaDB explain plan
table scanz	full scanzSFull table scan detected - scanning all rows in the table instead of using an index@Create an index on the columns used in WHERE clauses for table ``CCreate indexes on columns used in WHERE clauses and JOIN conditionsfilesortzMFilesort operation detected - MySQL is performing an expensive sort operationz?Add an index that matches your ORDER BY clause to avoid sortingztemporary tablezusing temporaryzJQuery uses temporary tables - this requires additional memory and disk I/OzVSimplify complex GROUP BY clauses or subqueries that might be causing temporary tableszjoin bufferz9Join buffer used - indicates a less efficient join methodz:Ensure proper indexes exist on join columns on both tableskey_len   ,r,   r      wherez-Potentially inefficient index usage (key_len=)zDConsider creating a composite index that covers all filtered columnsN)	rU   rI   ru   _extract_table_name_from_planr/   rp   joinfilterisdigit)
r   re   rd   r   r   r   r   r   key_len_partsr   s
             r   r   z(DatabaseConnector._analyze_mysql_explain  s   t9 8>>++{hnn>N/NMMop ;;HEJ&&)ijtiuuv'wx&&'lm))MMij""#de 004EIY4YOOhi""#{| HNN,,OOWX""#_` (( ( 0 6 6y A! D J J3 ObggfS[[-:J&KLMQ;7hnn.>#>OO&ST[S\\]$^_#**+qr $?;	 )s   <B#G# #G'c           	         t        |      }d|j                         v ra| j                  |d      }|r+|j                  d| d       |j                  d| d       n"|j                  d       |j                  d       d	|j                         v r"|j                  d
       |j                  d       d|j                         v r	 |j                         j	                  d      d   j	                  d      }|d   j                         }	t        |	j	                  d      d         }
|
dkD  r&|j                  d|
        |j                  d       n*|
dkD  r%|j                  d|
        |j                  d       d|j                         v rd|j                         v r	 |j                         j	                  d      d   j	                  d      }t        dj                  t        t         j                  |d                     }|dk  r'|j                  d| d       |j                  d       yyyy#  Y xY w#  Y yxY w)zAnalyze PostgreSQL explain planzseq scanzseq scan onzSequential scan on table `z.` - reading all rows instead of using an indexr   r   zESequential scan detected - reading all rows instead of using an index/Create indexes on columns used in WHERE clausesz	hash joinz?Hash join detected - can be memory intensive for large datasetszCCreate indexes on join columns to enable more efficient merge joinszcost=r    r   z..'  z#Very high cost operation detected: z:Consider rewriting the query or adding appropriate indexes  zHigh cost operation detected: z7Review query complexity and consider additional indexesbitmaprows=r,   d   z'Bitmap scan used for small result set ( rows)z>Consider using an index-only scan by creating a covering indexN)rU   rI   r   ru   r/   striprq   rp   r   r   r   )r   re   rd   r   r   r   r   r   
cost_parts
cost_rangemax_cost
rows_parts	row_counts                r   r   z-DatabaseConnector._analyze_postgresql_explain  s%   t9 ));;HmTJ ::,Ftuv&&)ijtiuuv'wxef&&'XY (..**OO]^""#hi hnn&&%^^-33G<Q?EEcJ
']002
 !1!1$!7!:;e#MM$Gz"RS#**+gh_OO&DXJ$OP#**+de
 x~~''Gx~~7G,G%^^-33G<Q?EEcJ
s{{JqM(J KL	s?OO&Mi[X^$_`#**+kl #	 -H'	s   B6H6 !BH= 6H:=Ic                    t        |      }d|j                         v r"|j                  d       |j                  d       d|j                         v sd|j                         v r"|j                  d       |j                  d       d|j                         v r"|j                  d	       |j                  d
       d|j                         v r#|j                  d       |j                  d       yy)zAnalyze SQL Server explain planr   z@Table scan detected - reading all rows instead of using an indexr   z
key lookupzbookmark lookupzBKey lookup operation detected - requires additional I/O operationszECreate a covering index that includes all columns needed by the queryz
hash matchz2Hash match join detected - can be memory intensivezSCreate indexes on join columns to enable more efficient nested loops or merge joinssortz<Sort operation detected - consuming memory and CPU resourceszBCreate an index that matches your ORDER BY clause to avoid sortingN)rU   rI   ru   )r   re   rd   r   r   r   r   s          r   r   z,DatabaseConnector._analyze_sqlserver_explain+  s    t9 8>>++MM\]""#hi 8>>++/@HNNDT/TOO`a""#jk 8>>++OOPQ""#xy X^^%%OOZ[""#gh &r   c           	         t        |      }d|j                         v r4d|j                         v r"|j                  d       |j                  d       d|j                         v r"|j                  d       |j                  d       d|j                         v rd	|j                         v r	 |j                         j                  d	      d
   j                  d      }t	        dj                  t        t         j                  |d                     }|dkD  r'|j                  d| d       |j                  d       yyyy#  Y yxY w)zAnalyze Oracle explain planfullr   zEFull table scan detected - reading all rows instead of using an indexr   	cartesianz@Cartesian join detected - extremely inefficient for large tablesz9Add proper join conditions to eliminate cartesian productznested loopsr   r   r   r,   r   r   z(Nested loops join with large row count (r   zBConsider adding indexes on join columns or restructuring the queryN)rU   rI   ru   r/   rp   r   r   r   )	r   re   rd   r   r   r   r   r   r   s	            r   r   z)DatabaseConnector._analyze_oracle_explainC  s&   t9 X^^%%,(..:J*JMMab""#TU (..**MM\]""#^_ X^^--'X^^=M2M%^^-33G<Q?EEcJ
s{{JqM(J KL	u$OO&NykYZ$[\#**+op %	 3N-s   +BE   Ec           	         d|j                         v r	 |j                         j                  d      dd D cg c](  }|j                  d      d   j                  d      d   * }}|D ]a  }t        dj                  t	        t
        j                  |                  }|dkD  s;|j                  d| d	       |j                  d
        n |j                         j                  d      }	|	dkD  r&|j                  d|	 d       |j                  d       d|j                         v r#|j                  d       |j                  d       yyc c}w #  Y xY w)z-Analyze patterns common to all database typesr   r   Nr   r   r,   i zLarge result set detected (r   zHConsider adding LIMIT/TOP clause or pagination to reduce result set sizer      zComplex query with z joins detectedzBConsider simplifying the query or breaking it into smaller queriessubqueryz>Subquery detected - may cause performance issues if correlatedzCConsider rewriting using JOINs instead of subqueries where possible)	rI   r/   rp   r   r   rU   r   ru   count)
r   r   r   r   r   partr   row_partr   
join_counts
             r   r   z+DatabaseConnector._analyze_general_patterns\  sJ    hnn&&	OW~~O_OeOefmOnopoqOrstdjj1!4::3?Bs
s * H #BGGF3;;,I$J KI 6) *Ei[PV(WX'../yz ^^%++F3
>OO1*_MN""#gh ))OO\]""#hi *# ts"   $E -E%>E $)E E Ec                    	 |ra|j                         j                  |j                               d   j                  d      }|d   j                  d      j                         S g d}|D ]i  }||j                         v s|j                         j                  |      d   j                  d      }|d   j                  d      j                         c S  y#  Y yxY w)z+Extract table name from explain plan stringr   r   z
"`,\[](){})ztable=ztable:ztable zfrom r   N)rI   r/   r   )r   r   prefixpartstable_patternspatterns         r   r   z/DatabaseConnector._extract_table_name_from_planv  s    	 (..v||~>qAGGLQx~~m4::<< "I- EG(.."22 ( 0 6 6w ? B H H M$Qx~~m<BBDDE 	s   A"C %C AC C C)N)openai)r   r   )Nr  )r,   )__name__
__module____qualname____doc__r   rU   r   r   r   r(   r%   r2   r0   r\   r`   rx   rp   r   r   rw   r   r   r   r   r   r    r   r   r   r      sL   ,	# 	$sCx. 	V 	-c -d38n -QW -BF3 F# F FTW F_b Fsv F  GJ F  VY F*  Ss StCH~ SRVWZ\_W_R` S4%3 %4S> %VZ[^`c[cVd %$77S 77T#s(^ 77X[ 77il 77  }A  BE  GJ  BJ  }K 77t GH7S 7T#s(^ 7 #7,/7@C7LPQTVYQYN72'S 'T#s(^ 'PTUXZ]U]P^ 'RBS BC Bdg Bw{|  BE  }E  xF BH%N+Zi02j4r   r   )r  loggingr   typingr   r   r   r   r   urllib.parser   r   r	   r
   r   sqlalchemy.enginer   r   sqlalchemy.excr   app.database.db_connectorsr   r   r   r   r   r   	getLoggerr  rV   r   r  r   r   <module>r     sQ      3 3 # 3 3 0 * 
 
		8	$p	 p	r   