@Qc@sddlmZddlmZddlmZddlZeddddd d Zd efd YZej ej j d Z ej ej ejfdZej ejejdZej ejej jdZej ejej jejdZej ejej jdZej ejej jdZej ejdZej ej dZej ej jej jdZej ej ej jdZej ej ej jejdZej ej ej jejejdZej ej ej jejejdZej ej ej jejejdZ ej ej ej jejejdZ!ej ej ej jejejdZ"ej ej j#ej jejej ejfej$dZ%ej ejej jej j#dZ&ej ej j#ej jejd Z'ej ejej jej j(d!Z)ej ejej jej j(d"Z*ej ejej jej j(d#Z+ej ejej jej j(d$Z,ej ejej jej j(d%Z-dS(&i(tSql92ReverseEngineering(t DefineModule(tfind_object_with_nameNtnametDbPostgresqlREtauthors Oracle Corp.tversions1.0tPostgresqlReverseEngineeringcBseZedZedZedZedZedZedZedZ edZ RS(cCsdS(Nt Postgresql((tcls((s"..\modules\db_postgresql_re_grt.pytgetTargetDBMSNamescCs|j|jdS(NR(t _connectionst__id__(R t connection((s"..\modules\db_postgresql_re_grt.pyt serverVersion scCstt|j||}|r |j|djd}tjdd|j|fg|jdj djdD]}t |^q}ddg}tj j }|d \|_ |_|_|_||j|jd <|j d kr td |q n|S( Nsselect version()is PostgreSQL REsConnected to %s, %sit,t.iRisZPostgreSQL version %s is not a supported migration source. At least version 8 is required.(tsuperRtconnectt execute_querytfetchonetgrttlog_infoRtsplittrstriptinttclassest GrtVersiont majorNumbert minorNumbert releaseNumbert buildNumberR R t RuntimeError(R R tpasswordtrtvertnt ver_partsR((s"..\modules\db_postgresql_re_grt.pyR$sF"cCs>gtt|j||D]}|jdkr|^qS(s;Returns a list of schemata for the given connection object.tINFORMATION_SCHEMAt PG_CATALOG(R&R'(RRtgetSchemaNamestupper(R R t catalog_namet schema_name((s"..\modules\db_postgresql_re_grt.pyR(3s"cCs$|jjd|j}d}|j||j}x|D]\}|j|||j|fj\}}} } } } } tjj}||_||_ t ||_ t ||_ t | |_ t | |_t | |_t| |_t | |_|jj|q?WdS(NsSELECT c.relname FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) WHERE n.nspname = '%s' AND c.relkind in ('S', 's')sfSELECT min_value, max_value, start_value, increment_by, last_value, is_cycled, cache_value FROM %s.%s(t sequencest remove_allRRtfetchallRRRt db_SequencetownertstrtminValuetmaxValuet startValuet incrementByt lastNumberRt cycleFlagt cacheSizetappend(R R tschematseq_names_querytseq_details_querytsequence_namestseq_namet min_valuet max_valuet start_valuet increment_byt last_valuet is_cycledtncachetsequence((s"..\modules\db_postgresql_re_grt.pytreverseEngineerSequences=s"  :  c Cs|j}|j}t|jdkrTtjdd|j|j|jfdSd|j|jf}d}|j||j}x|D]\}} } } } tj j } || _d| _ | | _ | rdnd| _ g| jD]}t|^q}| t|krGtjd|jd |j|fqnx|j||||j|jfD]\}t|j|}|rtj j}|d ||_||_| jj|qmtjd|jd |j|||jfqmqmW|j| qWdS( Nis%s: reverseEngineerTableIndicessTReverse engineer of table %s.%s was attempted but the table has no columns attributeis`SELECT c2.relname, i.indisunique::int, i.indisclustered::int, i.indnatts, i.indkey FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_namespace n, pg_catalog.pg_index i WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND c.relnamespace = n.oid AND n.nspname = '%s' AND c.relname = '%s' AND i.indisprimary = False ORDER BY c2.relnamesSELECT a.attname FROM unnest(ARRAY%r) attrid JOIN pg_catalog.pg_attribute a ON attrid=a.attnum JOIN pg_catalog.pg_class c ON c.oid = a.attrelid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = '%s' AND c.relname = '%s'tUNIQUEtINDEXsReverse engineer of index %s.%s was attempted but the referenced columns count differs from the number of its referenced columns. Skipping index!RszReverse engineer of index %s.%s was attempted but the referenced column %s could not be found on table %s. Skipping index!(R0tlentcolumnsRt send_errorR RRR.Rtdb_Indext isPrimarytuniquet indexTypeRRt send_warningRtdb_IndexColumntreferencedColumnR9taddIndex(R R ttableR:tcatalogtall_indices_querytindex_columns_queryt index_rowst index_namet is_uniquet is_clusteredt column_countt column_refstindextcoltcolst column_nametcolumnt index_column((s"..\modules\db_postgresql_re_grt.pytreverseEngineerTableIndices[sD       %/ cCs||dkr]d|jj|j|jf}|j||j}|r]|dd}q]ntt|j||||S(Ns USER-DEFINEDsSELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid LEFT JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = '%s' AND c.relname = '%s' AND a.attname = '%s' AND NOT a.attisdropped; ii(R0RRR.RRtgetColumnDatatype(R R RURct type_nametquerytudtype((s"..\modules\db_postgresql_re_grt.pyRfs  c CsQ|j|}|jjd}d}|j||}x|D]\}}tjj} || _|| _d|kr||j d } n|} | j } x^|j j D]P} | jj | ks| g| j D]} | j ^qkr| | _PqqW|| _|jj| qAWd}d}d}|j||jdkrP|n|}d }g}g}xO|D]G\}}||kr|}g}|j||fn|j|qrWd }x|D]\}}tjj} || _dd jg|D]}d |jd d ^q| _|| _|| _|jj| qWd S(s There are several kinds of user datatypes in Postgres, including: - domains - tuples/composite (table like structure) - ranges (numeric ranges with fancy definition, only in 9.2+) - base types - enums - others As of now, we're only supporting domains and enums. Ranges can be migrated to their underlying type. Composite types should be migrated to StructuredTypes at some point. sSELECT t.typname, at.attname, pg_catalog.format_type(at.atttypid, at.atttypmod) FROM pg_type t JOIN pg_class on (reltype = t.oid) JOIN pg_attribute at on (at.attrelid = pg_class.oid) JOIN pg_type a on (at.atttypid = a.oid) JOIN pg_namespace n on n.oid = t.typnamespace WHERE n.nspname NOT IN ('information_schema', 'pg_catalog') AND pg_class.relkind = 'c' sSELECT t.typname, pg_catalog.format_type(t.typbasetype, t.typtypmod) FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace WHERE n.nspname NOT IN ('information_schema', 'pg_catalog') AND t.typtype = 'd' t(s sSELECT t.typname, e.enumlabel FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace LEFT JOIN pg_catalog.pg_enum e ON e.enumtypid = t.oid WHERE t.typrelid = 0 AND t.typtype = 'e' AND n.nspname NOT IN ('information_schema', 'pg_catalog') AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid) ORDER BY e.enumsortordersSELECT t.typname, e.enumlabel FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace LEFT JOIN pg_catalog.pg_enum e ON e.enumtypid = t.oid WHERE t.typrelid = 0 AND t.typtype = 'e' AND n.nspname NOT IN ('information_schema', 'pg_catalog') AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid) i senum(%s)s, s'%s't's''N(Rt userDatatypesR-RRRtdb_UserDatatypeRt sqlDefinitiontfindR)t_rdbmstsimpleDatatypestsynonymst actualTypeR0R9RtNonetjointreplace(R R RVRtquery_compositet query_domainst domain_typesRgttype_deftdatatypet base_typet up_type_nametstypetst query_rangest query_enumstquery_enums_80t enum_typestltypettypestvaluest enum_labeltenumTypet enum_labelstl((s"..\modules\db_postgresql_re_grt.pytreverseEngineerUserDatatypessP     =    '  9  ( t__name__t __module__t classmethodR RRR(RGReRfR(((s"..\modules\db_postgresql_re_grt.pyRs :cCs tjdS(Nspostgresql_rdbms_info.xml(RtinitializeDBMSInfo(((s"..\modules\db_postgresql_re_grt.pyRscCs tjS(N(RtgetDataSourceNames(((s"..\modules\db_postgresql_re_grt.pyR scCs tj|S(N(RtquoteIdentifier(R((s"..\modules\db_postgresql_re_grt.pyRscCs tj|S(N(RtfullyQualifiedObjectName(tobj((s"..\modules\db_postgresql_re_grt.pyRscCstj||S(N(RR(R R!((s"..\modules\db_postgresql_re_grt.pyRscCs tj|S(N(Rt disconnect(R ((s"..\modules\db_postgresql_re_grt.pyRscCs tj|S(N(Rt isConnected(R ((s"..\modules\db_postgresql_re_grt.pyR!scCs tjS(N(RR (((s"..\modules\db_postgresql_re_grt.pyR %scCs tjS(N(RtgetSupportedObjectTypes(((s"..\modules\db_postgresql_re_grt.pyR)scCs tj|S(N(RtgetServerVersion(R ((s"..\modules\db_postgresql_re_grt.pyR-scCs tj|S(N(RtgetCatalogNames(R ((s"..\modules\db_postgresql_re_grt.pyR1scCstj||S(N(RR((R R*((s"..\modules\db_postgresql_re_grt.pyR(5scCstj|||S(N(Rt getTableNames(R R*R+((s"..\modules\db_postgresql_re_grt.pyR9scCstj|||S(N(Rt getViewNames(R R*R+((s"..\modules\db_postgresql_re_grt.pyR=scCstj|||S(N(RtgetTriggerNames(R R*R+((s"..\modules\db_postgresql_re_grt.pyRAscCstj|||S(N(RtgetProcedureNames(R R*R+((s"..\modules\db_postgresql_re_grt.pyREscCstj|||S(N(RtgetFunctionNames(R R*R+((s"..\modules\db_postgresql_re_grt.pyRIscCstj||||S(N(RtreverseEngineer(R R*t schemata_listtcontext((s"..\modules\db_postgresql_re_grt.pyRMscCstj||S(N(RR(R RV((s"..\modules\db_postgresql_re_grt.pyRQscCstj||S(N(RtreverseEngineerCatalog(R R*((s"..\modules\db_postgresql_re_grt.pyRUscCstj||S(N(RtreverseEngineerTables(R R:((s"..\modules\db_postgresql_re_grt.pyRYscCstj||S(N(RtreverseEngineerViews(R R:((s"..\modules\db_postgresql_re_grt.pyR]scCstj||S(N(RtreverseEngineerProcedures(R R:((s"..\modules\db_postgresql_re_grt.pyRascCstj||S(N(RtreverseEngineerFunctions(R R:((s"..\modules\db_postgresql_re_grt.pyRescCstj||S(N(RtreverseEngineerTriggers(R R:((s"..\modules\db_postgresql_re_grt.pyRis(.tdb_sql92_re_grtRtwbRtworkbench.utilsRRt ModuleInfoRtexportRt db_mgmt_RdbmsRtLISTtSTRINGRRtGrtNamedObjectRtINTtdb_mgmt_ConnectionRRRR RRRRR(RRRRRt db_CatalogtDICTRRRt db_SchemaRRRRR(((s"..\modules\db_postgresql_re_grt.pyts< $!$*$$'$*00000B------