+'&hmdZddlmZddlmZddlmZddlmZddlmZddlmZddlm Z dd lm Z dd l m Z dd l m Z dd l mZdd lmZddlmZddlmZddlmZddlmZddlmZddlmZddlmZddlmZddlmZddl m!Z!ddl"m#Z#ddl$m%Z%ddl$m&Z&ddl$m'Z'ddl(m)Z)ddl(m*Z*ddl(m+Z+ej,Z- e.Z/Gdd eZ0d!Z1e1d"Z2e2d#Z3d$Z4ej5d%ej5d%d&Gd'd(ej6Z7ej5d)ej5d)d*Gd+d,ej8Z9ej:e7d-Z;ej:e9d.Z<ee d/d0Z=eed/d1Z>ej?e7d2Z@ej?e9d3ZAe-Bej6d4ZCe-Bej8d5ZDej5d6Gd7d8ejEZFej5d9Gd:d;ejGZHej:eFd<ZIej:eHd=ZJej?eFd>ZKej?eHd?ZLe-BejEd@ZMe-BejGdAZNej5dBej5dBdCGdDdEejOZPej5dFej5dFdGGdHdIejQZRej:ePdJZSej:eRdKZTej?ePdLZUej?eRdMZVe-BejOdNZWe-BejQdOZXdPS)Qz0Some helpers to use with Alembic migration tool.) renderers)rewriter) _add_column) _add_index) _add_table) _drop_column) _drop_index) _drop_table) RenameTable)format_table_name)visit_rename_table) SQLiteImpl)BatchOperations) Operations)ops)Column)text) MySQLDialect) SQLiteDialect)compiles) DropTable)func) TypeDecorator) Geography)Geometry)Raster)_check_spatial_type) _get_gis_cols)_spatial_idx_nameceZdZdZdZdS)GeoPackageImplzCClass to copy the Alembic implementation from SQLite to GeoPackage. geopackageN)__name__ __module__ __qualname____doc__ __dialect__V/var/www/html/reinick/venv/lib/python3.11/site-packages/geoalchemy2/alembic_helpers.pyr!r!(sMMKKKr)r!cntj}dd}|j|_|t_|t_dS)8Monkey patch SQLAlchemy to fix spatial index reflection.Nc|j||fddi|}|jjdk} |d|rdnd}|s|Sn#t $r|cYSwxYw|r$td|}n"td|}||} | rtd |D} | D]f} | d } t|| } t| d r| | vr4| | | gd d did| | g|S)Nschemar"zPRAGMA main.table_info({})gpkg_geometry_columnsgeometry_columnsa@SELECT A.table_name, A.column_name, IFNULL(B.has_index, 0) AS has_index FROM "gpkg_geometry_columns" AS A LEFT JOIN ( SELECT table_name, column_name, COUNT(*) AS has_index FROM gpkg_extensions WHERE LOWER(table_name) = LOWER('{table_name}') AND extension_name = 'gpkg_rtree_index' ) AS B ON LOWER(A.table_name) = LOWER(B.table_name) WHERE LOWER(A.table_name) = LOWER('{table_name}'); ) table_namezSELECT * FROM geometry_columns WHERE f_table_name = '{}' ORDER BY f_table_name, f_geometry_column;cg|] }|d Snamer(.0is r* zR_monkey_patch_get_indexes_for_sqlite..spatial_behavior..b">">">1V9">">">r)r _column_flagTr4 column_namesuniquedialect_options)_get_indexes_normal_behaviordialectr4exec_driver_sqlformatfetchallAttributeErrorrexecutesetrboolappendadd)self connectionr1r.kwindexesis_gpkg is_spatial_dbspatial_index_queryspatial_indexesreflected_namesidxidx_colidx_names r*spatial_behaviorz>_monkey_patch_get_indexes_for_sqlite..spatial_behavior2s3$3J ^^SW^[]^^$)\9 &66077/6N++DV>> ## %,,-@AAJJLL  .!">">g">">">??O& . .a&,ZAACG}}O(C(C ()0 "#,:D+A  ##H----sAA&& A54A5N)r get_indexesr&rAnormal_behaviorrXs r*$_monkey_patch_get_indexes_for_sqliter].sG#/O@@@@D /6 0M1@M...r)cntj}dd}|j|_|t_|t_dS)r,Nc|j||fddi|}d|}||d|z }|t|}|rmt d|D}|D]Q} | d} t || } | |vr|| | gdddid|| R|S) Nr.zSELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = '{}' AND INDEX_TYPE = 'SPATIAL'z AND TABLE_SCHEMA = '{}'cg|] }|d Sr3r(r5s r*r8zQ_monkey_patch_get_indexes_for_mysql..spatial_behavior..r9r)rr<Tr=) rArDrGrrErHrrJrK) rLrMr1r.rNrOhas_index_queryrSrTrUrVrWs r*rXz=_monkey_patch_get_indexes_for_mysql..spatial_behaviors:3$3J ^^SW^[]^^BCI& C C     =DDVLL LO$,,T/-B-BCCLLNN  .!">">g">">">??O& . .a&,ZAA.. ()0 "#,:D+A  ##H----r)rY)rrZr&rAr[s r*#_monkey_patch_get_indexes_for_mysqlrb|sC".OB /6/L0?L---r)c|dkrPt|tttfr.|jj}|jd|d|zSdS)z%Add proper imports for spatial types.typezfrom geoalchemy2 import z%rF) isinstancerrr __class__r#importsrK)obj_typeobjautogen_context import_names r* render_itemrls^6jxF.KLLm, ##$L{$L$LMMMcz 5r)c|dkr|ds|ds|ds~|dsi|dsT|ds?|ds*|d s|d rd Sd S) aDo not include internal tables of spatial extensions. .. warning:: This function only checks the table names, so it might exclude tables that should not be. In such case, you should create your own function to handle your specific table names. tabler0spatial_ref_sysspatialite_historysqlite_sequenceviews_geometry_columnsvirts_geometry_columnsidx_gpkg_vgpkg_FT) startswith)rir4rh reflected compare_tos r*include_objectrzs7 *++ ??, - - ??/ 0 0 ??, - -  ??3 4 4  ??3 4 4  ??6 " " ??7 # # ??8 $ $u 4r)add_geospatial_columnbatch_add_geospatial_columncLeZdZdZeddZdZe ddZdS)AddGeospatialColumnOpzAdd a Geospatial Column in an Alembic migration context. This method originates from: https://alembic.sqlalchemy.org/en/latest/api/operations.html#operation-plugins NcH||||}||S)zNHandle the different situations arising from adding geospatial column to a DB.r.invoke)cls operationsr1columnr.ops r*r{z+AddGeospatialColumnOp.add_geospatial_columns.SVF 3 3 3  $$$r)cbt|j|j|jjS,Used to autogenerate the downgrade function.)DropGeospatialColumnOpfrom_column_and_tablenamer.r1rr4rLs r*reversezAddGeospatialColumnOp.reverses*%?? K$+*:   r)ci}|r||d<|r||d<||jj|fd|jji|}||S)zLIssue an "add column" instruction using the current batch migration context. insert_before insert_afterr.implr1r.r)rrrrrrNrs r*r|z1AddGeospatialColumnOp.batch_add_geospatial_columns}  0"/B   .!-B~  S O &   ?)       $$$r)rY)NN)r#r$r%r& classmethodr{rr|r(r)r*r~r~su %%%[%      %%%[%%%r)r~drop_geospatial_columnbatch_drop_geospatial_columncFeZdZdZeddZdZedZdS)rz9Drop a Geospatial Column in an Alembic migration context.Nc D|||fd|i|}||S)zRHandle the different situations arising from dropping geospatial column from a DB.r.r)rrr1 column_namer.rNrs r*rz-DropGeospatialColumnOp.drop_geospatial_columns6S[ > > >2 > >  $$$r)cXt|j|j|jSr)r~rr.r1rrs r*rzDropGeospatialColumnOp.reverses'$>> K$+   r)c l||jj|fd|jji|}||S)zLIssue a "drop column" instruction using the current batch migration context.r.r)rrrrNrs r*rz3DropGeospatialColumnOp.batch_drop_geospatial_column sTS O &   ?)       $$$r)rY)r#r$r%r&rrrrr(r)r*rrseDC%%%[%    %%[%%%r)rc |j}|jj}|j}|jdkrt |jt r |jj|}n |jj}| tj |||j |j |j|j dSd|jvr)|j||j|jdSdS)aBHandle the actual column addition according to the dialect backend. Args: operations: Operations object from alembic base, defining high level migration operations. operation: AddGeospatialColumnOp call, with attributes for table_name, column_name, column_type, and optional keywords. sqlite postgresqlrN)r1rr4get_bindrBrerrdload_dialect_implrGrAddGeometryColumnsrid geometry_type dimensionnullabler add_columnr.)r operationr1rrBgeospatial_core_types r*r{r{s%J"'K!!##+G|x i& 6 6 9$-#3#8#J#J7#S#S #,#3#8   "$)$2$.(11      % %""   # #      & %r)c|j}||j}|j}|jdkrt ||jj ||fd|j i|j dS)aAHandle the actual column removal according to the dialect backend. Args: operations: Operations object from alembic base, defining high level migration operations. operation: AddGeospatialColumnOp call, with attributes for table_name, column_name, column_type, and optional keywords. rr.N) r1 to_columnmigration_contextrrBr4_SPATIAL_TABLESrKr drop_columnr.rN)rrr1rrBs r*rr<s%J  != > >F!!##+G|xJ'''JO F\\9;K\y|\\\\\r)rc |jtv}|jtv}|s|r;dt||j|jdt||j|jdSt ||fi|S)zGSpecific compilation rule to rename spatial tables with SQLite dialect.zSELECT RenameTable(NULL, 'z', ''))r1rnew_table_namer r.r )elementcompilerrNtable_is_spatialnew_table_is_spatials r*visit_rename_geospatial_tablerOs)_<"1_D;/; h(:GN K K K K h(> O O O O  "'8::r:::r)c |jjtv}|rdt||jjddS|j|fi|S)zESpecific compilation rule to drop spatial tables with SQLite dialect.zSELECT DropTable(NULL, 'Nr)rr4rr visit_drop_table)rrrNrs r*visit_drop_geospatial_tabler`sd+>88 h(Replace the default DropColumnOp by a geospatial-specific one.r)rrdrerrrBrrrrrr1rr.rs r*drop_geo_columnrs||~~"H(M**7,&--g66(Xy&9::' r~biXXX Mr)create_geospatial_tablec\eZdZdZedZdZedfd Zdfd ZxZ S)CreateGeospatialTableOpzCreate a Geospatial Table in an Alembic migration context. This method originates from: https://alembic.sqlalchemy.org/en/latest/api/operations.html#operation-plugins c@|||fi|}||S)zOHandle the different situations arising from creating geospatial table to a DB.r)rrr1columnsrNrs r*rz/CreateGeospatialTableOp.create_geospatial_tables1SW + + + +  $$$r)cht||jSr)_namespace_metadata)r from_tableto_tablerrs r*rzCreateGeospatialTableOp.reverses0%00 MMOO $ 81   r)NcLt||}|SrYsuperrrrnrrirfs r*rz"CreateGeospatialTableOp.from_table"gg  (;<< r)ct|}|jD]+} |jjr d|j_#t $rY(wxYw|SNF)rrrrdrrF)rLrrncolrfs r*rz CreateGeospatialTableOp.to_tablesp  !233=  C 8)3-2CH*!     sA AArY) r#r$r%r&rrrrr __classcell__rfs@r*rrs %%[%    [          r)rdrop_geospatial_tablecZeZdZeddZdZedfd Zdfd ZxZS)DropGeospatialTableOpNc H||||}||S)zQHandle the different situations arising from dropping geospatial table from a DB.)r.table_kwr)rrr1r.rNrs r*rz+DropGeospatialTableOp.drop_geospatial_tables.SFR 8 8 8  $$$r)cht||jSr)rrrrrs r*rzDropGeospatialTableOp.reverses0&11 MMOO $ 82   r)cLt||}|SrYrrs r*rz DropGeospatialTableOp.from_tablerr)cJt|}|SrY)rr)rLrrnrfs r*rzDropGeospatialTableOp.to_tables   !233 r)rY) r#r$r%rrrrrrrs@r*rrs%%%[%    [r)rc|j}|}|j|g|jRd|ji|j|jjdkrt |dSdS)aCHandle the actual table creation according to the dialect backend. Args: operations: Operations object from alembic base, defining high level migration operations. operation: CreateGeospatialTableOp call, with attributes for table_name, column_name, column_type, and optional keywords. r.rN) r1r create_tablerr.rNrBr4rrK)rrr1rs r*rrs%J    DJJd):ddd9CSdW`Wcddd |H$$J'''''%$r)c|j}|}|j}|jdkrt||j|fd|ji|jdS)a@Handle the actual table removal according to the dialect backend. Args: operations: Operations object from alembic base, defining high level migration operations. operation: DropGeospatialTableOp call, with attributes for table_name, column_name, column_type, and optional keywords. rr.N) r1rrBr4rrK drop_tabler.r)rrr1rrBs r*rrsq%J    DlG|xJ'''J*TTY-=TASTTTTTr)cNt||}|ddS)zARender the create_geospatial_table operation in migration script.z.create_table(z.create_geospatial_table()rrrjr table_renders r*render_create_geo_tablers+or22L    02M N NNr)cNt||}|ddS)z?Render the drop_geospatial_table operation in migration script.z .drop_table(z.drop_geospatial_table()r rrs r*render_drop_geo_tablers*33L   0I J JJr)c|jj}t|ttt f|}|r1t |j|jf|j |j |j d|j }n|}|S)z?Replace the default CreateTableOp by a geospatial-specific one.)r.r_constraints_included) rrBrrrrrr1rr.rrrN)rrrrBgis_colsrs r*create_geo_tabler s~l"GR(Iv!>HHH ( M J 9 " 6"$":   e    Mr)c|jj}|}t|tt t f|}|rt|j|j }n|}|S)z=Replace the default DropTableOp by a geospatial-specific one.r) rrBrrrrrrr1r.)rrrrBrnrrs r*drop_geo_tabler"s]l"G KKMMEUXy&$A7KKH&r}RYGGG Mr)create_geospatial_indexbatch_create_geospatial_indexcFeZdZe ddZdZedZdS)CreateGeospatialIndexOpNFc H||||f||d|}||S)zQHandle the different situations arising from creating geospatial index into a DB.r.r?r) rr index_namer1rr.r?rNrs r*rz/CreateGeospatialIndexOp.create_geospatial_index4s;SZ U U URT U U  $$$r)cft|j|j|jdj|jS)rr)rr.)DropGeospatialIndexOprr1rr4r.rs r*rzCreateGeospatialIndexOp.reverseCs4$ O O Q,;     r)c n|||jj|fd|jji|}||S)zMIssue a "create index" instruction using the current batch migration context.r.r)rrrrrNrs r*rz5CreateGeospatialIndexOp.batch_create_geospatial_indexLsWS  O &   ?)       $$$r)r)r#r$r%rrrrr(r)r*rr1sf % % %[ %   %%[%%%r)rdrop_geospatial_indexbatch_drop_geospatial_indexcleZdZfdZe ddZdZedZedZxZ S) rcHtj|i|||_dSrY)r__init__r)rLrargskwargsrfs r*rzDropGeospatialIndexOp.__init__bs-$)&)))&r)NFc H||f||||d|}||S)zQHandle the different situations arising from dropping geospatial index from a DB.)r1rr.r?r) rrrr1rr.r?rNrs r*rz+DropGeospatialIndexOp.drop_geospatial_indexfsNS  !#        $$$r)cXt|j|jf|j|j|d|jS)rrr._reverse)rrr1rr.rNrs r*rzDropGeospatialIndexOp.reverse|sD& O O (;   g    r)c|jJt|jdks Jd||j|jjf|jdj|jjt |d|jS)Nr:.A spatial index must be set on one column onlyrr )rnlenrr4r.r from_indexr)rindexs r*rz DropGeospatialIndexOp.from_indexs{&&&5=!!Q&&&(X&&&s J K   a(-;%,77>>   l    r)c l||f|jj|jjd|}||S)zKIssue a "drop index" instruction using the current batch migration context.)r1r.r)rrrrNrs r*rz1DropGeospatialIndexOp.batch_drop_geospatial_indexsRS  !1?)        $$$r)r) r#r$r%rrrrrrrrs@r*rr_s'''''%%%[%*       [  %%[%%%%%r)rcr|}|j}|jdkr\t|jdks Jd|t j|j|jddS| |j }|j |dS)aCHandle the actual index creation according to the dialect backend. Args: operations: Operations object from alembic base, defining high level migration operations. operation: CreateGeospatialIndexOp call, with attributes for table_name, column_name, column_type, and optional keywords. rr:r rN) rrBr4rrrGrCreateSpatialIndexr1to_indexrr create_index)rrrrBrUs r*rrs    DlG|x9$%%***,\***4293GIZ[\I]^^_____  !=>>$$S)))))r)c|}|j}|jdkr4|t j|j|jdS|j | |j dS)a=Handle the actual index drop according to the dialect backend. Args: operations: Operations object from alembic base, defining high level migration operations. operation: DropGeospatialIndexOp call, with attributes for table_name, column_name, column_type, and optional keywords. rN) rrBr4rGrDisableSpatialIndexr1rr drop_indexrr)rrrrBs r*rrs    DlG|x43I4H)J_``aaaaa""9#5#5j6R#S#STTTTTr)cNt||}|ddS)zARender the create_geospatial_index operation in migration script.z.create_index(z.create_geospatial_index()rr)rjr idx_renders r*render_create_geo_indexrs+OR00J   .0K L LLr)c~t||}|dd}|ddd|jdz}|S)z?Render the drop_geospatial_index operation in migration script.z .drop_index(z.drop_geospatial_index(Nr;z, column_name='r)r rr)rjrrrs r*render_drop_geo_indexrsO_b11J   n.G H HD 99@ @D Kr)c|jj}t|jdkr|jd}t |t rt |jtttf|r|j dd|j d<|jj r |jdi}ni}|j d||j d<t|j|j|jf|j|jd|j S|S)z?Replace the default CreateIndexOp by a geospatial-specific one.r:rpostgresql_usinggistgist_geometry_ops_ndpostgresql_opsr)rrBrrrerrrdrrrrNget use_N_D_indexr4rrr1r.r?)rrrrBrr"s r*create_geo_indexr%s l"G 2:!jm c6 " " ': HxF3W( (  )+ 2Df(M(MBE$ %x% $"%(,B!C!#&(eii0@.&Q&QBE" #*   yy  %   Ir)c^|jj}|}t|jdkrt|jd}t |t rRt|jtttf|r*t|j f|j|j|jd|jS|S)z=Replace the default DropIndexOp by a geospatial-specific one.r:r)r1rr.)rrBrrrrerrrdrrrrrr1r4r.rN)rrrrBrUrs r*drop_geo_indexr'sl"G ++--C 3;1k!n c6 " " ': HxF3W( ( ) =Hy  %   Ir)N)Yr&alembic.autogeneraterralembic.autogenerate.renderrrrrr r alembic.ddl.baser r r alembic.ddl.sqliteralembic.operationsrrr sqlalchemyrrsqlalchemy.dialects.mysql.basersqlalchemy.dialects.sqlite.basersqlalchemy.ext.compilerrsqlalchemy.schemarsqlalchemy.sqlrsqlalchemy.typesr geoalchemy2rrr!geoalchemy2.admin.dialects.commonrrrRewriterwriterrHrr!r]rbrlrzregister_operation AddColumnOpr~ DropColumnOprimplementation_forr{rrr dispatch_forrrrewritesrr CreateTableOpr DropTableOprrrrrrr CreateIndexOpr DropIndexOprrrrrr%r'r(r)r*rBsU66******))))))333333222222222222444444333333333333((((((......//////))))))......))))))""""""777777999999,,,,,,''''''******!!!!!! AAAAAA;;;;;;??????    "#%%Z HAHAHAV%$&&&'@'@'@T$#%%%.677##$;=Z[[(%(%(%(%(%CO(%(%\[87(%V788##$<>\]]%%%%%S-%%^]98%6455# # 65# L566]]76]$ +x   ; ;!  ;  )X 8 8 8-..II/.I .//KK0/K !!  "! !""  #" 899#####c/##:9#L677CO870677((87($455UU65U"/00OO10O -..KK/.K "##$#(!!  "! 899##$=?^__)%)%)%)%)%c/)%)%`_:9)%X677##$;=Z[[<%<%<%<%<%CO<%<%\[87<%~677**87*&455UU65U"/00MM10M -..  /. "##$#:!!"!r)