XׯdVddlmZddlZddlmZddlmZedddZd Zd Z ia d Z d Z d Z dZdZeejejdZeejejjdZeejejjejdZeejejjdZeejejjdZeejdZeejjejjdZeejejjdZeejejjejdZ eejejjejejdZ!eejejjejejdZ"eejejjejejdZ#eejejjejejdZ$eejejjejejdZ%eejj&ejjejejejfej'dZ(eejejjejj)dZ*eejj)ejjejd Z+eejejjejj,d!Z-eejejjejj.d"Z/eejejjejj.d#Z0eejejjejj.d$Z1eejejjejj.d%Z2eejejjejj,d&Z3eejj4ejjejj,ejd'Z5eejejjejj,d(Z6eejejjejj,d)Z7eejejjejj,d*Z8eejj9d+Z:eejejfd,Z;eejejfd-Zeejejjd0Z?eejejjd1Z@dS)2) DefineModuleN) db_driver)NotConnectedError DbMssqlREz Oracle Corp.z1.0)nameauthorversioncRtjrtjdSN)grt query_status UserInterrupt..\modules\db_mssql_grt.pycheck_interruptionr"s, "!!!""rcdd}tjjjjD]}|jdkr|}n|SNMssql)r rootwb rdbmsMgmtrdbmsr)mssql_rdbms_instancers rget_mssql_rdbms_instancer&sF&, : #( E ! rc||jtvrt|jdStd|jz)N connectionNo open connection to %s__id__ _connectionsrhostIdentifierconnection_objects rget_connectionr%0s<<//-45lCC :=N=] ]^^^rc||jtvrt|jdStd|jz)Nr rrr#s rconnected_server_versionr'6s<<//-45i@@ :=N=] ]^^^rc tjdd|d|d|dt|j|g|Ri|S)zRetrieves a connection and executes the given query returning a cursor to iterate over results. The remaining positional and keyword arguments are passed with the query to the execute function zdb.mssqlzexecute   )r log_debug3r%cursorexecute)r$queryargskwargss r execute_queryr1<sc N::uuudddFFFKLLL =>+ , , 3 3 5 5 =e Ud U U Uf U UUrc.|D]}|j|kr|cSdS)zFinds an object with the given name within a collection of GRT objects (such as grt.List). Returns the found object or None if there was no object with the given name in the collection. Nr)r collectionobjs rfind_grt_objectr6Es3  8t  JJJ  4rcPgd}|d|vr |dd}t|S)N)'`"r)quoteIdentifier)rquotess rfixQuoteIdentifierr?Os7   F Aw&AbDz 4  rc d|zS)Nz[%s]rr3s rr=r=Ws D=rc|j}|rt|tjjru|jrmt|jtjjrIt |jjdzt |jzdzt |jzSnM|rKt|tjjr,t |jdzt |jzSt |jS)N.)owner isinstancer classes db_Schema db_Catalogr=r)r5rCs rfullyQualifiedObjectNamerH[s IE IE3;#899I ; s:ek3;3IJJ s"5;#344S89T9TTUXXYhiliqYrYrr r I:eS[%;<<Iuz**3.sx/H/HHH 38 $ $$rc d} t|} |dstdn=#t$r0}t jd|jztdd}~wwxYwn#t$r}|j}d}t jd|zddl} tj ||}|j j d kr|d  d}t|d d d}|d kr/|dd|ddn.|dd|ddnl#|j$r_}t%|jdkr=|jddvr.d|jd vrt j|jd Yd}~nd}~wwxYw|s#t jdt-|t jd|d|d|it.|j<t3|t.|jd<Yd}~nd}~wwxYwd S)aEstablishes a connection to the server and stores the connection object in the connections pool. It first looks for a connection with the given connection parameters in the connections pool to reuse existent connections. If such connection is found it queries the server to ensure that the connection is alive and reestablishes it if is dead. If no suitable connection is found in the connections pool, a new one is created and stored in the pool. Parameters: =========== connection: an object of the class db_mgmt_Connection storing the parameters for the connection. password: a string with the password to use for the connection. NzSELECT 1zconnection errorz1Connection to %s apparently lost, reconnecting...zConnection errorzConnecting to %s...rpyodbc8SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)rBr; ijc4||n|dSNzutf-16decodevalues rzconnect..s%-]b]i]ijr]s]src4||n|dSrOrPrRs rrTzconnect..su}eeZ_ZfZfgoZpZprc(||nt|Sr strrRs rrTzconnect..s%-]`af]g]grc(||nt|Sr rWrRs rrTzconnect..su}eeZ]^cZdZdr)2800042000z(18456)zConnection failedz Connected to z, rr )r%r,r- Exceptionr send_infor"rrKrconnectdriverdriverLibraryNamefetchoneintsplitadd_output_converterErrorlenr/ DBLoginError send_errorrXr!r getServerVersion) rpasswordconexchost_identifierr rK majorVersionodbc_errs rr_r_hs C&RZ(( 8::<<'' 33 4 2333 4 8 8 8 MMPZPii j j j#$677 7 8 4 RRR$3 +o=>>>  9#J99C 2h>>++&`aajjllmno"7==a#8#8#;<< 1$$,,T3s3sttt,,Q0p0pqqqq,,T3g3ghhh,,Q0d0deee| 9 9 98=!!Q&&8=+;?Q+Q+QV_ckcpqrcsVsVs&x}Q'7888 9   N.C 9 9 9  HIII+7#*? Z&'5Ej5Q5Q Z&' 222222=R> 1sgB 6A B B+BBB J$I<:CFI< H#AG=8I<=HA5I<<Jc<|jtvr t|j=dSNrr r!rs r disconnectrus L(( * + 1rc&|jtvrdndS)Nr;rrsrts r isConnectedrws!\1111q8rcdSrrrrrgetTargetDBMSNamerys 7rc$tj}t|dd}d|dDddgzz}|dd\|_|_|_|_ |S)zNReturns a GrtVersion instance containing information about the server version.rLrc,g|]}t|Sr)rc).0parts r z$getServerVersion..s???#d))???rrBN) r rE GrtVersionr1rbrd majorNumber minorNumber releaseNumber buildNumber)rr ver_string ver_partss rrjrjsk$$&&Gz+effooqqrstJ?? (8(8(=(=???!aE'II[degfgeg[hXG,g.CWEX Nrczd}d}t|}|jdkr|n|}dt||DS)z}Returns a list of the available catalogs. [NOTE] From MSDN: [A catalog] is equivalent to a databases in SQL Server. z(SELECT name FROM master.dbo.sysdatabaseszexec sys.sp_databasesrMcg|] }|d Srrr|rows rr~z#getCatalogNames.. A A ASV A A Ar)r'rr1)r query_pre_90 query_post_90 serverVersionr.s rgetCatalogNamesrsO >L+M,Z88M)599LL}E A A}Z?? A A AArcdt|z}t||d|z}d|z}t|}|jdkr|n|}dt||DS)z;Returns a list of schemata for the given connection object.USE %sa5SELECT TABLE_SCHEMA AS SCHEMANAME, max(TABLE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = '%s' GROUP BY TABLE_SCHEMA UNION SELECT ROUTINE_SCHEMA AS SCHEMANAME, max(ROUTINE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.ROUTINES GROUP BY ROUTINE_SCHEMA;zSELECT TABLE_SCHEMA AS SCHEMANAME, max(TABLE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = '%s' GROUP BY TABLE_SCHEMA;cg|] }|d Srrrs rr~z"getSchemaNames..rrr=r1r'r)r catalog_namer. query_post70 query_pre70rs rgetSchemaNamesrs |44 4E*e$$$ #//L))K -Z88M(499KK|E A A}Z?? A A AArcdt|z}t||d|z}d|z}t|}|jdkr|n|}dt||DS)Nrz=SELECT name FROM sys.tables WHERE schema_id = SCHEMA_ID('%s')zdSELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='%s'rMcg|] }|d Srr)r| table_names rr~z!getTableNames.. O O OzZ] O O Orrrr schema_namer.rrrs r getTableNamesrs |44 4E*e$$$%(35M79DEL-Z88M)599LL}E O O]:u-M-M O O OOrcdt|z}t||d|z}d|d|d}t|}|jdkrgS|jdkr|n|}dt||DS) Nrz?SELECT name FROM sys.views WHERE SCHEMA_NAME(schema_id) = '%s';zESELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_CATALOG='z' AND TABLE_SCHEMA='r8rMcg|] }|d Srr)r| views_infos rr~z getViewNames.. rrrrs r getViewNamesrs |44 4E*e$$$(*56MM 6B\\;;;PL-Z88M A%% )599LL}E O O]:u-M-M O O OOrcdt|z}t||d}d}t|}|jdkrgS|jdkr|n|}||z}dt||DS)NrzHSELECT name FROM sys.triggers WHERE OBJECT_SCHEMA_NAME(object_id) = '%s'zSELECT OBJECT_NAME(id) FROM sysobjects WHERE OBJECTPROPERTY(id, 'IsTrigger') = 1 AND uid = SCHEMA_ID('%s') GROUP BY OBJECT_NAME(id)rrMcg|] }|d Srr)r| trigger_infos rr~z#getTriggerNames.. s S S S\!_ S S Srrrs rgetTriggerNamesr s |44 4E*e$$$.ML-Z88M A%% )5::LL E K E S Sz51Q1Q S S SSrcdt|z}t||d}d}t|}|jdkrgS|jdkr|n|}||z}dt||DS)NrzASELECT name FROM sys.procedures WHERE schema_id = SCHEMA_ID('%s')zsSELECT ROUTINE_NAME as name FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_SCHEMA='%s' AND ROUTINE_TYPE='PROCEDURE'rrMcg|] }|d Srr)r| proc_infos rr~z%getProcedureNames..4s M M MiYq\ M M Mrrrs rgetProcedureNamesr#s |44 4E*e$$$%M:L-Z88M A%% )599LL}E K E M M=U+K+K M M MMrcdt|z}t||d}d}t|}|jdkrgS|jdkr|n|}dt|||DS)Nrz[SELECT name FROM sys.objects WHERE type_desc LIKE '%FUNCTION%' AND schema_id = SCHEMA_ID(?)zoSELECT ROUTINE_NAME as name FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_SCHEMA=? AND ROUTINE_TYPE='FUNCTION'rrMcg|] }|d Srr)r| func_infos rr~z$getFunctionNames..Hs Z Z ZiYq\ Z Z Zrrrs rgetFunctionNamesr7s |44 4E*e$$$BM6L-Z88M A%% )599LL}E Z Z=UK+X+X Z Z ZZrc xtjddtj}||_|j|j|jj jt|d| }|r|dpd|_ tjddtt||tjddii}i}i}i} |d d } |d d } |d d } |d d } d}d}d}|D]<}t| rt!t#|||nd|<| rt!t%|||nd||<t| r=t!t'|||t!t)|||znd||<| rt!t+|||nd||<|||z||z||zdz| |<|| |z }tj|d|t!|dzz zzd|z|dz }>d}tjtj||dzd}|D]w}| |d|z }tj|||zd}tj}||_ ||_|j |_ |j||| |dzz }| rhttjd||fztj|||zt9||tj||z }tj|d|z||| |dzz }| rhttjd|||fztj|||zt=||tj||z }tj|d|z||| |dzz }| rttjd|||fztj|||dz z|jtA||tjttj||dz z||ztC||tj||z }tj|d|z||| |dzz }| rhttjd|||fztj|||ztE||tj||z }tj|d|z||z }tjytjd}| rtGfd|jD}|jD]}td|j|dzz z}tjd|jztj|||zt9||tj||z }tj|d |ztjdd!|S)"Nrz'Reverse engineering catalog informationz)SELECT DATABASEPROPERTYEX(?, 'Collation')rJg?z&Reverse engineering User Data Types...g?z Preparing...reverseEngineerTablesTreverseEngineerTriggersreverseEngineerViewsreverseEngineerRoutines绽|=?zGathered stats for %sg?g333333?z%Reverse engineering %i tables from %sz@First pass of table reverse engineering for schema %s completed!z$Reverse engineering %i views from %sz5Reverse engineering of views for schema %s completed!z'Reverse engineering %i routines from %srZz8Reverse engineering of routines for schema %s completed!z'Reverse engineering %i triggers from %sz8Reverse engineering of triggers for schema %s completed!g?c32K|]}|jVdSr r3)r|schematable_count_per_schemas r z"reverseEngineer..s+^^61&+>^^^^^^rz8Reverse engineering foreign keys for tables in schema %szASecond pass of table reverse engineering for schema %s completed!zReverse engineering completed!)$r send_progressrEdb_mssql_CatalogrsimpleDatatypes remove_allextendr`rCr1rbdefaultCollationNamerreverseEngineerUserDatatypesgetrgrrrrrreset_progress_stepsbegin_progress_stepdb_mssql_Schemaschemataappendr^rend_progress_steprroutinesreverseEngineerProceduresreverseEngineerFunctionsrsum)rr schemata_listoptionscatalog collation_rowview_count_per_schemaroutine_count_per_schematrigger_count_per_schematotal_count_per_schema get_tables get_triggers get_views get_routinestotaliaccumulated_progressraccumulated_schema_progressschema_progress_sharethis_schema_progressrstep_progress_share total_tablesrs @rreverseEngineerrMsaBCCCk**,,GGL &&((( "":#4#:#JKKK!*.Y[ghhqqssM>'4Q'7'=2$dDEEE W555c>***!!4d;;J;;8$??L 2D99I;;8$??L E A$   ku.|c- LZe2f2f.g.g.g{|{+ir-ySj,Xc1d1d-e-e-exyk*q}1D4EjR^`k4l4l0m0mpsuEFPR^`kululqmqm1m1mCD -o{1COJP\^i4j4j0k0k0kBC -/Ek/RUjkvUw/w/G /T0UWop{W|0}@E0E{+ ' 44 .S=O=ORW=W8X1ZZ\swB]B C C C S02F2LMMM"%$C C 6 : :; L Lu T  ;=X[p=pqqq",,.. ! &-&B#'''5[AE[\gEhkpEpq  $  MAE[\gEhjuDvv w w w  #$8:NQd:d e e e !*f 5 5 5  ! # # # 33 .0rvA1A B B B4K@DZ[fDgjoDop  $  M@DYZeDfhsCtt u u u  #$8:NQd:d e e e V 4 4 4  ! # # # 33 .0gju0uvvv7{CG]^iGjmrGrs  $  MCG_`kGlnyFzz { { {  #$8:NQdefQf:f g g g O & & ( ( ( %j& 9 9 9  ! # # #   #$8;Nq;P$PRfi|R| } } } $Z 8 8 8  ! # # # 33 .0jmx0xyyy7{CG]^iGjmrGrs  $  MCG_`kGlnyFzz { { {  #$8:NQd:d e e e #J 7 7 7  ! # # # 33 .0jmx0xyyy#'<<#  G^^^^WM]^^^^^ & G GF  "%)? )LP\_dPd)e"f  MTW]Wbb c c c  #$8:NQd:d e e e !*f 5 5 5  ! # # # $7 7  24w{F5F G G G Gc;<<< Nrc dt|jz}t||d}t|}|jdkr t }|jt||D]\}}}}} } tj } | | _|| _ || _ | | _| | _d} |jD],} |r(| j| kr | | _d} n-| stjdd|d|d || _|j| d S) NrzzSELECT name, TYPE_NAME(system_type_id), max_length, precision, scale, is_nullable FROM sys.types WHERE is_user_defined = 1rMFTz"MSSQL reverseEngineerUserDatatypeszCould not find base type "z" for user defined type "r:r)r=rr1r'rr userDatatypesrr rEdb_mssql_UserDatatypeuppercharacterMaximumLengthnumericPrecision numericScale isNullabler actualType send_warningrCr)rrr.rrr base_typelength precisionscale is_nullabledatatypeactual_type_found simple_types rrrs w|44 4E*e$$$ E-Z88M A%%799((***FST^`eFfFf 3 3 BD)VY{{88::H JJLLHM.4H +(1H %$)H !"-H  % 3C   !1Y__5F5F!F!F*5H'(,%E$ X !E!EFOFOFOQUQUQUHVXXX$HN  ! ( ( 2 2 2 2 1rctj}||_|j|j|jjjt||t||}|j |D]H}tj }||_||_|j |I|Sr )r rErrrrrr`rCrrrrr)rrrschemata_namesrrs rreverseEngineerCatalogrsk**,,GGL &&((( "":#4#:#JKKK W555#J ==N !!!%(( ,,..!  '''' Nrc t|jdg}d|jz|v}|ro|j}dt |jz}t ||d}d}t|}|jdkr|n|}||jz}|j dt ||D} t| dz} d } | D]\} } tj | | z d |jd | d tj}| |_|j |||_| pd |_t%||t'||t)||| dz } |d|jznad} t|j dz} |j D]@}t+||tj | | z d|jd |jd| dz } AdS)N_rev_eng_progress_flagsz%s_tables_first_passrzSELECT t.name, p.value FROM sys.tables t LEFT JOIN sys.extended_properties p ON p.major_id = t.object_id AND p.minor_id = 0 AND p.name = 'MS_Description' AND p.class_desc = 'OBJECT_OR_COLUMN' WHERE schema_id = SCHEMA_ID('%s')zSELECT TABLE_NAME, '' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='%s'rMc.g|]}|d|dfS)rr;rrs rr~z)reverseEngineerTables..:s%UUUCAA'UUUrrrzRetrieving table rBz...rJrz-Reverse engineering of foreign keys in table z completedr)r!r setdefaultrrCr=r1r'rtablesrrgr rrEdb_mssql_TablercommentreverseEngineerTableColumnsreverseEngineerTablePKreverseEngineerTableIndicesreverseEngineerTableFKs)rrprogress_flags is_first_passrr.rrr table_namesrrr table_commenttables rrr"sa"*"34??@Y[]^^N/&+=.PM*,?7<888j%(((% Q 1<< - 9A = = = #   """UU=U3S3SUUU K  5( )4   %J  a%ii U_U_U_)` a a aK..00E#EJ M  ' ' ' EK)/REM ' E : : : ":u 5 5 5 ' E : : : HAA 4v{BCCCC FM""U*]  E #J 6 6 6  a%iikqkvkvkvx}yCyCyC*D E E E HAA 1rc 4|j}|j}dt|jz}t||d}d}t |}|jdkr|n|}t|||j|jf}t } d| jD} d|jD} d|j D} |D]]} tdt| | D}tj }|d pd |_|jdkr |d d kn|d  |_|d pd |_|dpd|_|dpd|_|dpd|_|dpd |_|d|_d} | |d}n#t0$r |j| |d}d}||_n#t0t4f$rld}| d}d|_d|dd|jd|jd|jd }tjd|YnwxYwYnwxYw|| j||_|jdkr$|j|jjdvr|jdz |_|d}|#fd |d!krd"|_n||_||_dS)#NraSELECT sys.columns.name AS COLUMN_NAME, sys.columns.is_nullable AS IS_NULLABLE, sys.types.name AS DATA_TYPE, sys.columns.max_length AS CHARACTER_MAXIMUM_LENGTH, sys.columns.precision AS NUMERIC_PRECISION, sys.columns.scale AS NUMERIC_SCALE, sys.columns.collation_name AS COLLATION_NAME, is_identity AS IS_IDENTITY_COLUMN, CAST (sys.default_constraints.definition as NVARCHAR(max)) as COLUMN_DEFAULT, sys.extended_properties.value as COLUMN_COMMENT FROM sys.columns JOIN sys.types ON sys.columns.user_type_id=sys.types.user_type_id JOIN sys.objects ON sys.columns.object_id = sys.objects.object_id LEFT JOIN sys.default_constraints ON (sys.columns.column_id=sys.default_constraints.parent_column_id AND sys.columns.object_id=sys.default_constraints.parent_object_id) LEFT JOIN sys.extended_properties ON sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id and sys.extended_properties.name = 'MS_Description' and sys.extended_properties.class_desc = 'OBJECT_OR_COLUMN' WHERE sys.objects.schema_id=SCHEMA_ID(?) AND sys.objects.name=? ORDER BY sys.columns.column_ida~SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME, (c.status & 128) / 128 AS IS_IDENTITY_COLUMN, '' AS COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS, sysobjects t, sysusers u, syscolumns c WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND u.name=TABLE_SCHEMA AND t.name=TABLE_NAME AND u.uid=t.uid AND c.id=t.id AND c.name=COLUMN_NAME ORDER BY ORDINAL_POSITIONrMcg|] }|j Srr3r|rs rr~z/reverseEngineerTableColumns..~s"h"h"hhHM"h"h"hrcg|] }|j Srr3r s rr~z/reverseEngineerTableColumns..sQQQhHMQQQrcg|] }|d Srr)r|col_descriptions rr~z/reverseEngineerTableColumns..sNNN/!$NNNrc3K|]}|VdSr r)r|namevals rrz.reverseEngineerTableColumns..s"GGw7GGGGGGr COLUMN_NAMErJ IS_NULLABLEYESCOLLATION_NAMECHARACTER_MAXIMUM_LENGTHr NUMERIC_SCALENUMERIC_PRECISIONCOLUMN_COMMENTIS_IDENTITY_COLUMN DATA_TYPEVARCHARzColumn datatype "z" for column "z " in table "rBz$" reverse engineered as VARCHAR(255)z!MSSQL reverseEngineerTableColumns)NCHARNVARCHARrZCOLUMN_DEFAULTc|dr(|dr|dd}|S)N()r;r<) startswithendswith)dataremove_parenthesiss rr'z7reverseEngineerTableColumns..remove_parenthesissG??3'':DMM#,>,>:--d1R4j99D rz(NULL)T) rCr=rr1r'rrrr descriptiondictzipr rEdb_mssql_Column isNotNull collationNamerrrridentityindexr ValueErroruserType TypeErrorr simpleTypedefaultValueIsNull defaultValue addColumn)rr rrr. query_post90 query_pre90rrowsrmssql_simple_datatypes_listuser_datatypes_list col_namesr row_valuescolumn user_datatypermsg default_valuer's @rrrXs [FlG w|44 4E*e$$$ "L K-Z88M(4q88KKlE UV[%*,E G GD355"h"hBVBf"h"h"hQQ':OQQQNNDC! !/27a %&9:?a#$45;$%9:  0288*[:Q:W:W:Y:Y[[HH 0 0 0 0 ' 56I6O6OQ[\gQhQnQnQpQp6r6r s  "/ * K K K $ 6<> J 8H  J A:JJJJ Jc|j}|j}dt|jz}t||dt |jdt |jd}t |jdkr(tjdd|jd|jd d Sd t||D}|D].}t||j}|r| |/dS) z9Reverse engineers the primary key(s) for the given table.rzexec sp_pkeys 'z', 'r8rz!Migration: reverseEngineerTablePKReverse engineer of table rB5 was attempted but the table has no columns attributer;cg|] }|d S)rrs rr~z*reverseEngineerTablePK..sIIISVIIIr) rCr=rr1r?rgcolumnsr rir6addPrimaryKeyColumn)rr rrr. pk_col_names pk_columnr>s rrrs:[FlG w|44 4E*e$$$$* 0 AND i.indid < 255 AND o.type = 'U' AND (i.status & 64)=0 AND (i.status & 8388608)=0 AND (i.status & 2048)=0 AND u.name='z' AND o.name='z)' ORDER BY i.name, k.keynoNUNIQUEINDEXaSELECT object_id, name, index_id, CAST (type_desc AS NVARCHAR) AS type_desc, is_unique, is_primary_key, is_disabled, has_filter, CAST (filter_definition AS NVARCHAR) AS filter_definition FROM sys.indexes WHERE sys.indexes.object_id = OBJECT_ID('')zSELECT c.name, ic.is_descending_key FROM sys.index_columns ic JOIN sys.columns c on (ic.column_id=c.column_id and ic.object_id=c.object_id) WHERE ic.object_id=%i and ic.index_id=%iSPATIAL CLUSTEREDrJ)rCrgrGr rirr'r=r1rr?fetchallrEdb_mssql_Index isPrimaryunique indexType clusteredignoreDuplicateRowsaddIndexr6db_mssql_IndexColumnreferencedColumnrrc hasFilterfilterDefinitiondescend)rr rrr r.get_indices_query_pre90 index_rowsr/ table_schemar index_name column_nameignore_dup_keys is_uniqueignore_dup_rows is_clusteredis_primary_keyr> index_columnget_indices_queryget_index_columns_querytable_idindex_id type_desc is_disabled has_filterfilter_definitionis_descending_keys rrrs[FlG 5=Q ??\b\g\g\ginisisisBt v v vq&z22G w|44 4E*e$$$a/A.M.M.M.MOabgblOmOmOmOm#o#:/FGGPPRR HR 7 7 EL*j+PY \>9! 7 * j 8 8K6688E!+EJ&4EO#,EL3<'Ixx'EO&2EO1@E-NN5))))emDD7#&;#C#C#E#EL(2S(8;(FL%4:L1M((666/ 7% ^H)P):):b&7DJPgksu}j~P~7A7A;;2K!2,[%-HHF;'*{'G'G'I'I ,6,<{,J )/@ ,8> 5 ,,\:::u%%% 1rcf |j}|j}dt|jz}t||t |}|jdkrFd}t||t |jt |jf}d}|D]\} } } } } }}| |j| kr|r|j |tj }| pd|_||_|pd|_ |pd|_d|_t!| |j}|rt!| |j}n/tjdd|jd |jd | d | d d S|s2tjdd|jd |jd| d | d | d d S||_t!| |j}|s/tjdd|jd |jd| d | d d St!| |j}|s4tjdd|jd |jd| d | d| d |  d S|j ||j ||r|j |ndt |jd t |jd}d}t/|jdkr(tjdd|jd |jdd St||}|j|D]\}} }}}|stj }| pd|_||_|pd|_ |pd|_d|_d}t|||D]c\} } } } |s]t!| |j}|rt!| |j}n0tjdd|jd |jd| d| d d S|s3tjdd|jd |jd| d| d | d d St!| |j}|s0tjdd|jd |jd| d | d d St!| |j}|s5tjdd|jd |jd| d | d| d |  d S|j ||j |e||_|j |dS)z7Reverse engineers the foreign keys for the given table.rrMa0SELECT fk.name AS CONSTRAINT_NAME, c.name AS COLUMN_NAME, ref_u.name AS REF_SCHEMA_NAME, ref_tbl.name AS REF_TABLE_NAME, ref_c.name AS REF_COLUMN_NAME, CASE WHEN (ObjectProperty(sfk.constid, 'CnstIsUpdateCascade')=1) THEN 'CASCADE' ELSE 'NO ACTION' END as UPDATE_RULE, CASE WHEN (ObjectProperty(sfk.constid, 'CnstIsDeleteCascade')=1) THEN 'CASCADE' ELSE 'NO ACTION' END as DELETE_RULE FROM sysusers u, sysobjects t, sysobjects fk, sysforeignkeys sfk, syscolumns c, sysobjects ref_tbl, sysusers ref_u, syscolumns ref_c WHERE u.name=? AND t.name=? AND t.uid=u.uid AND t.xtype='U' AND sfk.fkeyid=t.id AND fk.id=sfk.constid AND c.id=t.id AND c.colid=sfk.fkey AND ref_tbl.id=sfk.rkeyid AND ref_tbl.uid=ref_u.uid AND ref_c.id=ref_tbl.id AND ref_c.colid=sfk.rkey ORDER BY sfk.constid, sfk.keynoNrJrz"Migration: reverseEngineerTableFKsz While reverse engineering table rBz: Referenced schema z in foreign key z not found in catalog schematar;z: Referenced table z not foundz: Foreign key column z not found in this tablez not found in table zSELECT object_id as fk_id, name, delete_referential_action_desc, update_referential_action_desc, is_disabled FROM sys.foreign_keys WHERE parent_object_id=OBJECT_ID('rNaSELECT COL_NAME(fkc.parent_object_id, fkc.parent_column_id) as parent_column, OBJECT_SCHEMA_NAME(fkc.referenced_object_id) as referenced_schema, OBJECT_NAME(fkc.referenced_object_id) as referenced_table, COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) as referenced_column FROM sys.foreign_key_columns fkc JOIN sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id WHERE fk.object_id=?; rCrDz: The foreign key z references the schema z;, which was not found in the list of schemas to be migratedz references the table z*, which was not found in the source schema)rCr=rr1r'rr?rQ foreignKeysrr rEdb_mssql_ForeignKey deleteRule updateRule modelOnlyr6rrrireferencedTablerGreferencedColumnsrgr)rr rrr.r get_fks_query_pre9fk_rows foreign_keyfk_namerbreferenced_schema_namereferenced_table_namereferenced_column_name update_rule delete_rulereferenced_schemareferenced_tabler>referenced_column get_fks_queryget_fk_mapping_queryfk_iddelete_referential_actionupdate_referential_actionrns rrr:s [FlG w|44 4E*e$$$&z22Ga#" ,>ASTZT_A`A`btuzucAcAABCCLLNN FM" D" D BG["8:OQgitwB"k&6'&A&A:%,,[999!k==?? #*=b  $) !)4): &)4): &() %%44JGL\$]$]!$'67LN_Nf'g'g$$N#G#G{A{F{F{FHMHRHRHRTjTjTjlslslsJtvvv11'N#G#Gioitititv{v@v@v@BXBXBXZoZoZoqxqxqxJy{{{11.> +$[%-@@F CCrxr}r}r}DIIIKVKVKVX_X_X_F`bbbqq /0FHXH` a a $ CCsys~s~s~@E@J@J@JLbLbLbdkdkdkmCmCmCEZEZF[]]]qq   & &v . . .  ) 0 01B C C C C  2   $ $[ 1 1 1q x5Gv{4S4S4S4SUghmhrUsUsUsUsu   u}   " " N??\b\g\g\ginisisisBt v v v1 M::CCEE $$&&&ah) 6) 6 ]E757PR]( 6!k==?? #*=b  $) !)B)Hb &)B)Hb &() %$( jwyCEY[`kakaLLfK!79NPf+%,;?TVgVn/o/o,,N+O+Oekepepeprwr|r|r|~E~E~EG]G]G]R^```#$111+!'K'KRXR]R]R]_d_i_i_ikAkAkACJCJCJLaLaLaNbddd qqq,[%-HHF!!'K'Kz@zEzEzEGLGQGQGQS^S^S^`g`g`gNhjjj qqq(78NP`Ph(i(i%,!'K'K{A{F{F{FHMHRHRHRTjTjTjlslslsuKuKuKMbMbNceee qqq'..v6661889JKKKK.> +!((555 1rcFdt|jjz}t||d}d}t |}|jdkr|n|}|d|jiz}t||}|r|jd}t|D]\}\} } } |dkrd| d zz }tj ||zd |jd | tj } | pd | _|| _| | _ |j| dS) NrzSELECT COUNT(*) OVER () AS count, OBJECT_NAME(object_id) as name, definition FROM sys.sql_modules WHERE OBJECT_SCHEMA_NAME(object_id) = '%(schema)s'zSELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='%(schema)s'), TABLE_NAME as name, VIEW_DEFINITION AS definition FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='%(schema)s'rMrrrrrzReverse engineering view rBrJ)r=rCrr1r'rviewsr enumerater rrE db_mssql_View sqlDefinitionr) rrr.rrrr,stepidx view_count view_nameview_definitionviews rrrsK v|'899 9E*e$$$6M#L -Z88M)5::LL E Xfk* *E :u - -F & !!!=Fv=N=N & & 9C9*iaxxj501  cDjjfkkk[d[d*e f f f;,,..D!RDIDJ!0D  L   % % % % 1rc`dt|jjz}t||d}d}t |}|jdkr|n|}||j|fz}t||}|rAt j}|pd|_||_|dd|_ |S)aReverse engineers a view for the given schema. Parameters: =========== connection: an object of the class :class:`db_mgmt_Connection` storing the parameters for the connection. schema: a schema object (an instance of :class:`grt.classes.db_mssql_Schema`). This object must have its ``owner`` and ``name`` attributes properly set. view_name: the name of the view to reverse engineer. Return value: ============= view: an object of the class :class:`grt.classes.db_mssql_View` with the retrieved information. rzLSELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('%s.%s') znSELECT VIEW_DEFINITION AS definition FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'rMrJr) r=rCrr1r'rr rErr) rrrr.rrr resultsetrs rreverseEngineerViewrs$ v|'899 9E*e$$$M/L-Z88M)599LL}E V[), ,Ej%11I-{((**O  &q\!_ Krc\dt|jjz}t||d}d}t |}|jdkr|n|}|d|jiz}t||}d}|rt |D]\}\} } } |dkrd| d zz }tj||zd |jd | tj } | pd | _|| _d | _ | | _ |j | tjdd|jzdS)NrzSELECT COUNT(*) OVER () AS count, OBJECT_NAME(M.object_id) as name, definition FROM sys.sql_modules M JOIN sys.procedures P ON M.object_id=P.object_id WHERE OBJECT_SCHEMA_NAME(M.object_id) = '%(schema)s'aSELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='%(schema)s' AND ROUTINE_TYPE='PROCEDURE'), ROUTINE_NAME as name, ROUTINE_DEFINITION as definition FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='%(schema)s' AND ROUTINE_TYPE='PROCEDURE'rMrrrrrzReverse engineering procedure rBrJ PROCEDUREz=Finished reverse engineering of procedures for the %s schema.r=rCrr1r'rrr rrEdb_mssql_Routine routineTyperrr) rrr.rrrr,rr proc_count proc_nameproc_definitionprocs rrr sc v|'899 9E*e$$$8MBL -Z88M)5::LL E Xfk* *E :u - -F D )=Fv=N=N ) ) 9C9*iaxxj501  cDjjSYS^S^S^`i`i*j k k k;//11D!RDIDJ*D !0D  O " "4 ( ( ( (cZ]c]hhiii 1rc\dt|jjz}t||d}d}t |}|jdkr|n|}|d|jiz}t||}d}|rt |D]\}\} } } |dkrd| d zz }tj||zd |jd | tj } | pd | _|| _d | _ | | _ |j | tjdd|jzdS)NrzSELECT COUNT(*) OVER () AS count, OBJECT_NAME(M.object_id) as name, definition FROM sys.sql_modules M JOIN sys.objects O ON M.object_id=O.object_id WHERE type_desc LIKE '%%FUNCTION%%' AND schema_id = SCHEMA_ID('%(schema)s')aSELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='%(schema)s' AND ROUTINE_TYPE='FUNCTION'), ROUTINE_NAME as name, ROUTINE_DEFINITION as definition FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='%(schema)s' AND ROUTINE_TYPE='FUNCTION'rMrrrrrzReverse engineering function rBrJFUNCTIONz 1rc iadSrr)r!rrrcleanuprsL 1rcdS)Nwindowsrrts rgetOSrs 9rcRt|ddS)NzSELECT @@servicename r)r1rbrts rgetSourceInstancers$ %< = = F F H H KKr)Arrr workbenchrworkbench.exceptionsrrrrr!r%r'r1r6r?exportrr=rEGrtNamedObjectrHINTdb_mgmt_Connectionr_rurwryrrjLISTrrrrrrrrGDICTrrrrrrrrrrrrrrrrr db_mgmt_Rdbmsrrrrrrrrrrrs, 222222\ ^U S S S """    ___ ___ VVV!!! 3:sz**+* 3:s{9::%%;:% 37CK:CJGG6 6 HG6 r 37CK:;;  <;  37CK:;;99<;9  3: 3;)3;+IJJKJ 38S[;<< B B=< B 38S[;SZHHBBIHB2 38S[;SZTTPPUTP" 38S[;SZTTPPUTP$ 38S[;SZTTTTUTT, 38S[;SZTTNNUTN& 38S[;SZTT[[UT[* 3;)3;+I3:X[X`beblWmorowxxNNyxNb 37CK:CK>?> 3:s{=>>LL?>LLLr