@Qc@sNddlmZddlZddlmZddlmZddlmZedddd d d Z d Z d Z iZ dZ dZdZddZe jejejdZe jejejjdZe jejejjejdZe jejejjdZe jejejjdZe jejdZe jejjejjdZe jejejjdZ e jejejjejdZ!e jejejjejejdZ"e jejejjejejdZ#e jejejjejejdZ$e jejejjejejdZ%e jejejjejejd Z&e jejj'ejjejejejfej(d!Z)e jejejjejj*d"Z+e jejejjejj,d#Z-e jejejjejj.d$Z/e jejejjejj.d%Z0e jejejjejj.d&Z1e jejejjejj.d'Z2e jejejjejj,d(Z3e jejejjejj,d)Z4e jejejjejj,d*Z5e jejejjejj,d+Z6e jejj7d,Z8e jejejfd-Z9e jejejfd.Z:dS(/i(t DefineModuleN(t db_driver(tNotConnectedError(tfind_object_with_nametnamet DbSybaseREtauthors Oracle Corp.tversions1.0cCstjrtjndS(sChecks if the user is requesting to cancel an operation in progress. Call this from time to time so that the actual cancel requests can be handled N(tgrtt query_statust UserInterrupt(((s..\modules\db_sybase_re_grt.pytcheck_interruptions cCs@d}x3tjjjjD]}|jdkr|}PqqW|S(NtSybase(tNoneRtroottwbt rdbmsMgmttrdbmsR(tsybase_rdbms_instanceR((s..\modules\db_sybase_re_grt.pytget_sybase_rdbms_instance(s cCs5|jtkrt|jdStd|jdS(Nt connectionsNo open connection to %s(t__id__t _connectionsRthostIdentifier(tconnection_object((s..\modules\db_sybase_re_grt.pytget_connection4scOs<tjdd|||ft|jj|||S(sRetrieves 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 s db.sybasesexecute %s %s %s (Rt log_debug3Rtcursortexecute(Rtquerytargstkwargs((s..\modules\db_sybase_re_grt.pyt execute_query;scsfd}|S(s(Decorator). Needed because a Sybase issue with DDL statements inside transactions. More info here: http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/53037 csMd}x-|D]%}t|tjjr |}Pq q W|sPtjddSt|j}y|jdj d}Wnt k rd}nX|jd|jd|jd|dkr|jd |n~||}t|j}|jd|jd |jd|dkrI|jd |n|S( Ns db.sybasesBCannot find a connection object to apply the allow-ddl-in-tran fixsSELECT db_name()itmasters USE masters&sp_dboption tempdb,"ddl in tran", truesCHECKPOINT tempdbsUSE ?s'sp_dboption tempdb,"ddl in tran", false( R t isinstanceRtclassestdb_mgmt_Connectiont log_errorRRRtfetchonet Exception(RRtconntargRt current_dbtres(tfunc(s..\modules\db_sybase_re_grt.pytwrapper_functionJs6           ((R,R-((R,s..\modules\db_sybase_re_grt.pytallow_ddl_in_tranEscCsdS(N(R (t_((s..\modules\db_sybase_re_grt.pytksc csd}d}g}tdt|jD}x|D]}||||||} } | sn| |kr|||r||dj|fVn| }| g}|d7}q8n|j| q8W|r||dj|fVndS(sGenerator to join definition columns that are split across several rows In Sybase, object definitions in syscomments can be split into several rows. For example, a stored procedure can have several row entries in syscomments each having the same object name and containing the chunks of the procedure's code in the "text" column. The order of the rows is determined by the "colid" value (a sequence of integers: 1, 2, 3, etc.). Arguments: name_column -- string with the name of the column that has the object name split_column -- string with the name of the column that has the fragments cursor -- the resultset to iterate through callback -- an optional callable that will be called with the row corresponding to the first row of each distinct object Returns: idx, name, definition where idx tracks the count of different objects as they are found, name is the name of the current object and definition is the joint definition for the object. Note: This functions assumes that the rows are ordered properly, i.e. sorted by name, colid. icss%|]\}}|d|fVqdS(iN((t.0tpostcol_description((s..\modules\db_sybase_re_grt.pys stiN(R tdictt enumeratet descriptiontjointappend( t name_columnt split_columnRtcallbacktidxtcurrent_objecttcurrent_object_chunkst column_indextrowt object_nametobject_definition((s..\modules\db_sybase_re_grt.pytjoin_multiline_contentks"    cCsd|S(Ns[%s]((R((s..\modules\db_sybase_re_grt.pytquoteIdentifierscCs|j}|rzt|tjjrz|jrt|jtjjrt|jjdt|jdt|jSn9|rt|tjjrt|jdt|jSt|jS(Nt.(townerR"RR#t db_Schemat db_CatalogRER(tobjRG((s..\modules\db_sybase_re_grt.pytfullyQualifiedObjectNames !5cCsd}ynt|}y(|jjds<tdnWn3tk rr}tjd|jtdnXWntk r}tjd|jddl }yt j ||}t |_ Wnj|jk r1}t|jdkr2|jd dkr2d |jd kr2tj|jd q2nX|sTtjdt|ntjdi|d6t|jst sp_tables(R REtsortedtset(Rt catalog_name((s..\modules\db_sybase_re_grt.pytgetSchemaNames scCsKt|dt|gt|dD] }|d|kr'|d^q'S(NsUSE %sssp_tables @table_type="'TABLE'"ii(R RE(RRrt schema_nameRA((s..\modules\db_sybase_re_grt.pyt getTableNamesscCsKt|dt|gt|dD] }|d|kr'|d^q'S(NsUSE %sssp_tables @table_type="'VIEW'"ii(R RE(RRrRtRA((s..\modules\db_sybase_re_grt.pyt getViewNamesscCsDt|dt|d}gt|||D]}|d^q0S(NsUSE %ss@SELECT name FROM sysobjects WHERE type="TR" AND uid = user_id(?)i(R RE(RRrRtRRA((s..\modules\db_sybase_re_grt.pytgetTriggerNamesscCsDt|dt|d}gt|||D]}|d^q0S(NsUSE %ss?SELECT name FROM sysobjects WHERE type='P' AND uid = user_id(?)i(R RE(RRrRtRRA((s..\modules\db_sybase_re_grt.pytgetProcedureNames(scCsgS(N((RRrRt((s..\modules\db_sybase_re_grt.pytgetFunctionNames1scs}tjddtjj}||_|jj|jj|jj jd|_ tjddt t ||tjddii}i}i}i}|j dt} |j d t} |j d t} |j d t} d } d }d}x>|D]6}t | r5tt|||nd|<| r]tt|||nd||ss8Reverse engineering foreign keys for tables in schema %ssASecond pass of table reverse engineering for schema %s completed!sReverse engineering completed!((Rt send_progressR#tdb_sybase_CatalogRtsimpleDatatypest remove_alltextendtdriverRGtdefaultCollationNameR treverseEngineerUserDatatypestgetRRRURuRvRxRyRwtreset_progress_stepstbegin_progress_steptdb_sybase_SchematschemataR9RORRt setdefaultRqtdiscardRztend_progress_stepR|troutinestreverseEngineerProcedurestreverseEngineerFunctionsR{tsum(RRrt schemata_listtoptionstcatalogtview_count_per_schematroutine_count_per_schemattrigger_count_per_schemattotal_count_per_schemat get_tablest get_triggerst get_viewst get_routinesttotaltitaccumulated_progressRttaccumulated_schema_progresstschema_progress_sharetthis_schema_progressRtstep_progress_sharetprogress_flagst total_tables((Rs..\modules\db_sybase_re_grt.pytreverseEngineer8s      ((>(**                         c .Cstd^d_d`dadbdcdddedfdgdhdidjdkdldmdndodpdqdrdsdtdudvdwdxdydzd{d|d}d~dddddddddddf,}dY}t|dZt|jt}|jjxt||D]\}}}}} } tjj } |j | _|| _ || _ || _ | | _t|j|| } | rX| | _ntjd[d\| tf|jj| qWd]S(Ni"tIMAGEi#tTEXTi$s EXTENDED TYPEi%t TIMESTAMPi&tINTNi'tVARCHARi-tBINARYi/tCHARi0tTINYINTi1tDATEi2tBITi3tTIMEi4tSMALLINTi7tDECIMALi8tINTi:t SMALLDATETIMEi;tREALi<tMONEYi=tDATETIMEi>tFLOATi?tNUMERICiAt USMALLINTiBtUINTiCtUBIGINTiDtUINTNijtDECIMALNiltNUMERICNimtFLOATNintMONEYNiotDATETIMNizt SMALLMONEYi{tDATENitUNICHARitTIMENit UNIVARCHARit TEXT_LOCATORit IMAGE_LOCATORitUNITEXT_LOCATORitUNITEXTit BIGDATETIMENitBIGTIMENit BIGDATETIMEitBIGTIMEitBIGINTsYSELECT name, length, prec, scale, allownulls, type FROM systypes WHERE accessrule != NULLsUSE %ss#Sybase reverseEngineerUserDatatypess9Could not found base type "%s" for user defined type "%s"i(i"R(i#R(i$s EXTENDED TYPE(i%R(i&R(i'R(i-R(i/R(i0R(i1R(i2R(i3R(i4R(i7R(i8sINT(i:R(i;R(i<R(i=R(i>R(i?R(iAR(iBR(iCR(iDR(ijR(ilR(imR(inR(ioR(izR(i{R(iR(iR(iR(iR(iR(iR(iR(iR(iR(iR(iR(iR(R5R RERRt userDatatypesRRR#tdb_sybase_UserDatatypetuppertcharacterMaximumLengthtnumericPrecisiont numericScalet isNullableRRt actualTypet send_warningtuser_type_nameR9( RRt base_typesRRRtlengtht precisiontscalet is_nullablet base_typetdatatypet simple_type((s..\modules\db_sybase_re_grt.pyRs|   (     c Cst|jjdt}d|j|k}|rl|j}t|dt|j|jj gt ||j|jD]}|df^q~}t |d}d}x|D]\}} t j ||d|j|ft jj} || _|jj| || _| pd| _t|| t|| t|| |d7}qW|jd|jnhd}t |jd}xL|jD]A} t|| t j ||d |j| jf|d7}qWd S( NR~s%s_tables_first_passsUSE %sR4g|=gsRetrieving table %s.%s...g?s<Reverse engineering of foreign keys in table %s.%s completedi(RRRRqRRGR REttablesRRuRURRR#tdb_sybase_TableR9tcommenttreverseEngineerTableColumnstreverseEngineerTablePKtreverseEngineerTableIndicestaddtreverseEngineerTableFKs( RRRt is_first_passRt table_namet table_namesRRt table_commentttable((s..\modules\db_sybase_re_grt.pyRzs8  1!      $c Cs|j}|j}t|dt|jd}t|||j|j}t}g|jD]}|j^q`}g|jD]}|j^q} g|jD]} | d^q} x/|D]'} tdt | | D} t j j }| dpd|_| d |_ | jdd|_| d p2d|_| d dk rR| d nd |_| d dk ru| d nd |_| jd d|_| dpd|_d}y|j| dj}Wntk ry$|j| j| dj}Wnnttfk rmd}|jd}d|_d| dj|j|j|jf}t jd|qXd}||_nX|dk r|j||_n| d}|dk r|jdr|d|_n|j|qWdS(NsUSE %ssSELECT ISNULL(C.name, '') AS COLUMN_NAME, T.name AS DATA_TYPE, C.length AS CHARACTER_MAXIMUM_LENGTH, C.prec AS NUMERIC_PRECISION, C.scale AS NUMERIC_SCALE, CONVERT(BIT, (C.status & 0x08)) AS IS_NULLABLE, CONVERT(BIT, (C.status & 0x80)) AS IS_IDENTITY_COLUMN, K.text AS COLUMN_DEFAULT FROM syscolumns C, systypes T, sysobjects A, syscomments K WHERE USER_NAME(A.uid) = ? AND A.id = C.id AND C.id = OBJECT_ID(?) AND C.usertype *= T.usertype AND C.cdefault *= K.id ORDER BY C.colidicss|] }|VqdS(N((R1tnameval((s..\modules\db_sybase_re_grt.pys Yst COLUMN_NAMER4t IS_NULLABLEtCOLLATION_NAMEtCHARACTER_MAXIMUM_LENGTHtNUMERIC_PRECISIONit NUMERIC_SCALEtCOLUMN_COMMENTtIS_IDENTITY_COLUMNt DATA_TYPERisXColumn datatype "%s" for column "%s" in table "%s.%s" reverse engineered as VARCHAR(255)s"Sybase reverseEngineerTableColumnstCOLUMN_DEFAULTsDEFAULT i( RGR RERRRRR7R5tzipRR#tdb_sybase_Columnt isNotNullRt collationNameRR RRRtidentitytindexRt ValueErrort TypeErrorRtuserTypet simpleTypet startswitht defaultValuet addColumn(RRRRRtrowsRRtsybase_simple_datatypes_listtuser_datatypes_listR3t col_namesRAt row_valuestcolumnt user_datatypetmsgt default_value((s..\modules\db_sybase_re_grt.pyRAsR      ## $ )   c Cs|j}|j}t|d|jd}t|jdkretjdd|j|jfdSgt|||j|jD]}|d^q}x6|D].}t|j|}|r|j|qqWdS(s9Reverse engineers the primary key(s) for the given table.sUSE %ssSELECT sc.name FROM sysobjects so JOIN syskeys sk ON so.id=sk.id JOIN syscolumns sc ON sc.id=sk.id AND sc.colid IN (sk.key1, sk.key2, sk.key3, sk.key4, sk.key5, sk.key6, sk.key7, sk.key8) WHERE so.uid=USER_ID(?) AND sk.id=OBJECT_ID(?) AND sk.type=1isSybase reverseEngineerTablePKsbReverse engineer of table's %s.%s primary key was attempted but the table has no columns attributei( RGR RRUtcolumnsRRWRtaddPrimaryKeyColumn( RRRRRRAt pk_col_namest pk_columnR((s..\modules\db_sybase_re_grt.pyRs   2 c Cs|j}|j}t|jdkrKtjdd|j|jfdSt|d|jd}x)t|||j|jD] }tjj}|d|_|d|_ |d|_ |d|_ |j rd nd |_ |d |_ |j|g|d D]}|dk r|^q}xe|D]]} t|j| } | r,tjj} |jd | | _| | _|jj| q,q,WqWdS(s2Reverse engineers the indices for the given table.is"Sybase reverseEngineerTableIndicessTReverse engineer of table %s.%s was attempted but the table has no columns attributeisUSE %ss; SELECT INDEX_NAME = A.name, IS_CLUSTERED = CASE WHEN ((A.status&16) = 16 OR (A.status2&512) = 512) THEN 1 ELSE 0 END, IS_PRIMARY = CASE WHEN ((A.status&0x800) = 0x800) THEN 1 ELSE 0 END, IS_UNIQUE = CASE WHEN ((A.status&2) = 2) THEN 1 ELSE 0 END, IGNORE_DUP = CASE WHEN ((A.status&4) = 4) THEN 1 ELSE 0 END, INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 1), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 2), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 3), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 4), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 5), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 6), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 7), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 8), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 9), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 10), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 11), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 12), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 13), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 14), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 15), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 16), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 17), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 18), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 19), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 20), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 21), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 22), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 23), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 24), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 25), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 26), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 27), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 28), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 29), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 30), INDEX_COL(USER_NAME(B.uid) + '.' + B.name, indid, 31) FROM sysindexes A, sysobjects B WHERE A.indid > 0 AND A.indid < 255 AND A.status2 & 2 != 2 AND B.id = A.id AND B.type = 'U' AND USER_NAME(B.uid) = ? AND B.name=? ORDER BY 1, 2, 3iitUNIQUEtINDEXiiRFN(RGRURRRWRR R#tdb_sybase_Indext clusteredt isPrimarytuniquet indexTypetignoreDuplicateRowstaddIndexR Rtdb_sybase_IndexColumntreferencedColumnR9( RRRRRt index_rowRtcolnametindex_column_namest column_nameRt index_column((s..\modules\db_sybase_re_grt.pyRs2   5"      )  cCs|j}|j}t|d|jd|j|jf}t|jdkrutjdd|j|jfdS|jjxt||D]}g|dd!D]}|r|^q}g|dD]}|r|^q}tj j } d |j|jd j |f| _|| _d | _ | _ d| _t|j|d} | swtjdd |j|jfdSt| j|d| _| jstjdd |d|jfdSxt||D]\} } t|j| } | stjdd| |j|jfdSt| jj| }|s^tjdd| | j| jjfdS| jj| | jj|qW|jj| qWdS(s7Reverse engineers the foreign keys for the given table.sUSE %ss)SELECT so.name, USER_NAME(so.uid), COL_NAME(sk.id, key1), COL_NAME(sk.id, key2), COL_NAME(sk.id, key3), COL_NAME(sk.id, key4), COL_NAME(sk.id, key5), COL_NAME(sk.id, key6), COL_NAME(sk.id, key7), COL_NAME(sk.id, key8), COL_NAME(sk.depid, depkey1), COL_NAME(sk.depid, depkey2), COL_NAME(sk.depid, depkey3), COL_NAME(sk.depid, depkey4), COL_NAME(sk.depid, depkey5), COL_NAME(sk.depid, depkey6), COL_NAME(sk.depid, depkey7), COL_NAME(sk.depid, depkey8) FROM syskeys sk JOIN sysobjects so ON sk.depid = so.id WHERE sk.type = 2 AND sk.id = OBJECT_ID('%s.%s')isSybase reverseEngineerTableFKsseReverse engineer of foreign keys for table %s.%s was attempted but the table has no columns attributeiii s %s_%s_%s_fkR/tRESTRICTs*Could not find schema "%s" in catalog "%s"s3Could not find referenced table "%s" in schema "%s"s+Could not find column "%s" in table "%s.%s"(RGR RRURRRWt foreignKeysRR#tdb_sybase_ForeignKeyR8t deleteRulet updateRulet modelOnlyRRRtreferencedTableRR9treferencedColumns(RRRRRRAtcol_namet fk_columnstfk_ref_columnst foreign_keytreferenced_schemaR'treferenced_column_nameRtreferenced_column((s..\modules\db_sybase_re_grt.pyRsR    &#%       c Cst|d|jjd}|jjdtt||jj|jd}t|||j}|rxtdd|D]p\}}}tj ||d|j|ftj j }||_|pd|_||_ |jj |qWnd S( NsUSE %ssSELECT so.name AS view_name, sc.text AS view_definition FROM sysobjects so JOIN syscomments sc on so.id=sc.id WHERE so.uid=USER_ID(?) AND so.type='V' ORDER BY so.name, sc.colidg?g|=t view_nametview_definitionsReverse engineering view %s.%sR4i(R RGRtviewsRRURvRDRRR#tdb_sybase_Viewt sqlDefinitionR9( RRRtstepRR=R8R9tview((s..\modules\db_sybase_re_grt.pyR|;s )"!  c Cst|d|jjd}dtt||jj|jd}t|||j}|rxtdd|D]y\}}}tj||d|j|ftjj }||_|pd|_d |_ ||_ |j j |qtWntjdd |jd S( NsUSE %ssSELECT so.name AS procedure_name, sc.text as procedure_definition FROM sysobjects so INNER JOIN syscomments sc ON so.id=sc.id WHERE so.uid = USER_ID(?) AND so.type = 'P' ORDER BY so.name, sc.colidg?g|=tprocedure_nametprocedure_definitions#Reverse engineering procedure %s.%sR4t PROCEDUREs=Finished reverse engineering of procedures for the %s schema.i(R RGRRURxRDRRR#tdb_sybase_Routinet routineTypeR<RR9( RRRR=RR=R?R@t procedure((s..\modules\db_sybase_re_grt.pyRRs)"!   cCstjdd|jdS(Ng?s<Finished reverse engineering of functions for the %s schema.i(RRR(RR((s..\modules\db_sybase_re_grt.pyRjscst|d|jjd}d}i}xt|||jD]}|ddkr|j|d|ddgdcd7scs|d|dsP    &)!$ *<$$'$ *000 0 0B-H-)-A--X-H----3$