Changeset 2749

Show
Ignore:
Timestamp:
01/25/2007 02:11:45 AM (2 years ago)
Author:
johnf
Message:

I have changed the way the program retrieves the primary key. It was possible to get the PK from the wrong schema for the same table name. The code works with 7.4 - 8.x. Still not using information_schema views due to the continued support of version 7.4

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • trunk/dabo/db/dbPostgreSQL.py

    r2740 r2749  
    8181    def getFields(self, tableName): 
    8282        tempCursor = self._connection.cursor() 
     83        tableNameBreak=tableName.split('.',1) 
     84        localSchemaName = tableNameBreak[0] 
     85        localTableName = tableNameBreak[1] 
    8386        #jfcs 11/01/04 works great from psql (but does not work with the psycopg 
    8487        #module) and only with postgres 7.4.x and later.  Too bad, the statement 
    …… 
    113116where (b.schemaname || '.'|| c.relname)  = '%s' and a.attnum > 0 """ % tableName) 
    114117        rs = tempCursor.fetchall() 
    115         myoid=rs[0][0] 
    116         ## get the PK  
    117         tempCursor.execute("""select c2.relname, i.indisprimary, i.indisunique,  
    118                 pg_catalog.pg_get_indexdef(i.indexrelid)  
    119                 FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i  
    120                 WHERE c.oid = %s AND c.oid = i.indrelid AND i.indexrelid = c2.oid  
    121                 AND i.indisprimary =TRUE  
    122                 ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname""" % myoid)     
     118 
     119        ## get the PK the code should work well with 7.4 - 8.2 versions 
     120         
     121        sqlstr = """SELECT n.nspname AS schema_name, c.relname AS table_name, 
     122           c.oid AS table_oid, a.attname AS column_name, idx.n + 1 AS ordinal_position 
     123      FROM pg_class c, pg_attribute a, pg_index i, pg_namespace n, generate_series(0, 31) idx(n) 
     124     WHERE c.oid = a.attrelid AND c.oid = i.indrelid AND i.indisprimary AND a.attnum = i.indkey[idx.n] 
     125       AND NOT a.attisdropped 
     126       AND has_schema_privilege(n.oid, 'USAGE'::text) 
     127       AND n.nspname NOT LIKE 'pg!_%s' ESCAPE '!' 
     128       AND has_table_privilege(c.oid, 'SELECT'::text) 
     129       AND c.relnamespace = n.oid and c.relname = '%s' and n.nspname = '%s' """ % ('%',localTableName,localSchemaName) 
     130         
     131        tempCursor.execute(sqlstr) 
    123132        rs2=tempCursor.fetchall() 
    124133        if rs2==[]: 
    125134            thePKFieldName = None 
    126135        else: 
    127             thestr = rs2[0][3] 
    128             thePKFieldName = thestr[thestr.find("(") + 1: thestr.find(")")].split(", ") 
     136            #thestr = rs2[0][3] 
     137            #thePKFieldName = thestr[thestr.find("(") + 1: thestr.find(")")].split(", ") 
     138            thePKFieldName = rs2[0][3] 
    129139         
    130140        fields = []