
    W;j#                         S SK Jr  S SKJr  S SKJr  S SKJr  S SKJ	r	  SS jr
S rS	\S
\4S jr            SS jrg)    )	inspector)get_cached_columns)
best_paths)rename_table)get_master_mappingNc                    U S:X  a  US:X  a  U(       a  US4$ 0 SS_SS_SS_SS	_S
S_SS_SS_SS_SS_SS_SS_SS_SS_SS_SS_S S_S!S_S"S#S$.EnX4U;   a  X0U4   $ X4U;   a  X1U 4   u  pEXT4$ [         R                  " U 5      nU H  nUS%   U:X  d  M  US&   S'   US(   S'   4s  $    [        5       n[        [	        X   5      [	        X   5      -  5      n	/ S)Qn
U
 H  nX;   d  M
  X4s  $    U	(       a  g*g*)+Nagri_crop_user_relsagri_crop_relatedid)
agri_usersagri_user_address)user_idr   )r   agri_crop_history)r   r	   )r   agri_districts)districtr   )r   agri_subdivisions)r   district_id)r   
agri_block)r   subdivision_id)r   
agri_mouza)r   block_id)agri_activity_pipesagri_land_user_rels)plot_fidrel_id)r   r   )user_fidr   )r   r	   )
cu_rel_fid	cu_rel_id)r	   r
   )crop_type_idr   )r	   agri_crop_name_growth_stage)crop_name_idr!   )r    r
   )crop_growth_stage_idr   )r   agri_season_dates)season_date_fidsd_id)r	   r   )land_rel_idr   )r	   r   )r	   r#   )
season_fidr   )crop_seasonr   ))r#   r
   )r   r
   referred_tableconstrained_columnsr   referred_columns)r   	unique_idfidr   g_id)NN)r   get_foreign_keysr   listset)t1t2hintJOIN_PRIORITY	right_colleft_colfksfkcolumnssharedpreferred_keyskeys               1/var/www/html/database-metadata/join_generator.pyfind_join_keyr?      s    ''))  :': (: *: ,: ,:" ( %:( !+:. 31:4 *7:: 3=:@ 1C:F ;$I:L 9&O:R 1$U:X 3![:^ *a:d 1$g:l 	q:Mx 	x= "X&&
x= +H5	"" 
$
$R
(C2% ()!,%&q) 	  !"GGKGK	F
N =8O	      c                     / nU  HD  n[        [        U5      S-
  5       H&  nX#   X#S-      4nXA;  d  M  UR                  U5        M(     MF     U$ )N   )rangelenappend)pathsedgespathiedges        r>   merge_pathsrK      sS    Es4y1}%AGTA#Y'D T" & 
 Lr@   configreturnc                 @    S[        S0 U D6R                  5       -   S-   $ )N() )generate_multi_join_sqlstrip)rL   s    r>   generate_subquery_sqlrT      s$    (26288::S@@r@   c                   ^^0^1^2^3 U(       a]  U Vs1 s H  nUS   iM
     nnU Vs/ s H  nUU;  d  M  UPM     nn[        U5      nU(       a  UR                  [        U U5      5        O[        U U5      n[        U5      n[	        S5        [	        U5        [	        S5        [	        S5        [	        U5        [	        S5        U=(       d    0 nT=(       d    0 mU
=(       d    0 n
U=(       d    0 n/ n0 nU
R                  5        H  u  nn[        U5      nUUU'   M     0 m2UR                  5        H6  u  nnUR                  5        H  u  nu  nnn[        UUU5      T2UU4'   M     M8     U24S jm1[        [        R                  U /U-   [        UR                  5       5      -   5      5      nU H  n[	        SU5        [        U5      n UR                  U/ 5      n!U!(       d  M5  U! Hm  nUT;   aN  UTU   ;   aE  TU   U   R                  5       n"UR                  U" SU  SU S	U"R                  5        S
U 3	5        MW  UR                  U  SU 35        Mo     M     [	        SU5        SSR!                  U5      -   n#U U;   a  U# SUU     SU  3n$OU# S[        U 5       3n$U H  u  n%n&S n'U(       a  UR                  U%U&45      n'[#        U%U&U'5      u  n(n)U((       a  U)(       d
  SU% SU& 3s  $ UR                  U%[        U%5      5      n*UR                  U&[        U&5      5      n+U$SU+ SU* SU( SU+ SU) 3
-  n$M     S UU14S jjm3S!U0U34S jjm0U(       a  U$ST0" U5      -   -  n$U(       a<  U V,Vs/ s H  u  n,n[        U,5       SU 3PM     n-n,nU$SSR!                  U-5      -   -  n$U	(       a  U$ST0" U	SS9-   -  n$U(       aP  U V,VV.s/ s H(  u  n,nn.[        U,5       SU SU.R                  5        3PM*     n/nn,n.U$SSR!                  U/5      -   -  n$U(       a  U$SU 3-  n$U(       a  U$SU 3-  n$U$$ s  snf s  snf s  snn,f s  sn.nn,f )"Nz
PATHS:
z
EDGES:c                 v   > [        U[        5      (       a  U$ TR                  X40 5      R                  X"5      $ N)
isinstancer0   get)tablecolvalmaster_lookups      r>   enrich_value-generate_multi_join_sql.<locals>.enrich_value   s;    c4    L
 #c-	r@   zSELECT TABLE:rO   .z) AS _zSELECTED_COLS:zSELECT , z
FROM z AS z%-- Cannot determine join key between z and z
JOIN z ON z = c                 B  > UR                  SS5      R                  5       nUR                  S5      nT	" XU5      nU(       a+  U T;   a%  UTU    ;   a  TU    U   R                  5        SU 3nO[        U 5       SU S3nUS:X  a4  [	        U[
        5      (       a  SS	R                  S
 U 5       5      -   S-   nO^US:X  a2  [	        U[
        5      (       a  [        U5      S:X  a  US    SUS    3nO&[	        U[        5      (       a  SU S3O
[        U5      nU SU SU 3$ )Nop=valuerc   z.""INrO   rd   c              3   n   #    U  H+  n[        U[        5      (       a  S U S 3O
[        U5      v   M-     g7f)'N)rZ   str).0vs     r>   	<genexpr>Cgenerate_multi_join_sql.<locals>.parse_condition.<locals>.<genexpr>  s0     %^Z]UV*Q2D2D!Ah#a&&PZ]s   35rP   BETWEEN   r   z AND rB   rl    )	r[   upperlowerr   rZ   r0   joinrD   rm   )
r\   r]   rule	use_aliasrf   r^   col_exprval_straggregationsr`   s
           r>   parse_condition0generate_multi_join_sql.<locals>.parse_condition  s&   XXdC &&(hhw5s+,.3,u:M3M&u-c288:;1SEBH&u-.bQ7H:*S$//DII%^Z]%^^^addG9_C!6!63s8q=Qc!fX.G$.sC$8$8#ajc#hG1RD'++r@   c           
      l  > / nU R                  5        H  u  pEUR                  5        Hp  u  pg[        U[        5      (       a   SU;   a  UR                  T
" XFXr5      5        M:  [        U[        5      (       d  MQ  T	" XG0X5      nUR                  SU S35        Mr     M     SU S3R	                  U5      $ )Nrf   rO   rP   rt   )itemsrZ   dictrE   rw   )filterslogicry   clausesr\   rulesr]   rx   nestedbuild_conditionsr}   s            r>   r   1generate_multi_join_sql.<locals>.build_conditions&  s    #MMOLE"[[]	dD))ddlNN?5t#OPd++-umUNFNNQvha=1 + , 5'|  ))r@   z
WHERE z

GROUP BY z
HAVING T)ry   rt   z

ORDER BY z
LIMIT z
OFFSET )F)ANDF)r0   extendr   rK   printr   rT   r   r   fromkeyskeysr   r[   ru   rE   rv   rw   r?   )4sourcetargetscolumn_filterswhere_filtersorder_bylimitoffsetgroup_byr|   having_filters
subqueriesforced_paths
join_hintsmaster_mappingsrH   covered_targetstremaining_targetsrF   rG   selected_colssubquery_aliasesaliasrL   subquery_sqlr\   col_mapr]   m_tablekey_col	value_col
all_tablesreadable_tablecolsfuncselect_clausesqlr2   r3   r4   col1col2leftrighttbl
group_cols	directionorder_clausesr   r`   r_   r}   s4           `                                       @@@@r>   rR   rR      s   &  %
$ H$ 	 
 
'  	 
 \"LL% 

 E	*	%L	$K	*	%L	$K#)rN%2L!rJ%+OM#))+v,V4". ,
 M)//1w29--/.C.'7I*<Wgy*YM5#,' 3B 2 H>&&()*	
J ou%%e,!!%,C$U0C)C#E*3/557$$vQ~.>auE$**,WXY\X]%^_ $$'7q%>?   

M*		- 88M!!w'7'?&@VHMw|F';&<=B>>2r( 
D #2b.
d4:2$eB4HH##BR(89 $$Rb)9:tD64&E7!D6BB , , 	* 	* z,];;;CKL8xsCc*+1SE28
L}tyy444{-nMMMemnemNacSVXaL-.auAioo6G5HIemn}tyy777%!!6(##Ju


L M os   P,
P1P1*P6/P<rY   )NNNNNNNNNNNN)dbr   schema_cacher   mlr   table_renamer   	db_masterr   r?   rK   r   rm   rT   rR   rQ   r@   r>   <module>r      sb     +  % (@DA$ A3 A 
or@   