[SQL-CVS] r446 - home/phd/SQLObject/postgres-7.2/sqlobject/postgres

[<sub...@co...>]

Author: phd
Date: 2004-12-06 13:31:56 +0000 (Mon, 06 Dec 2004)
New Revision: 446

Modified:
   home/phd/SQLObject/postgres-7.2/sqlobject/postgres/pgconnection.py
Log:
Implemeted columnsFromSchemaOld() for postgres 7.2.
Fixed dropTable() - drop primary key (id) sequence after the table.


Modified: home/phd/SQLObject/postgres-7.2/sqlobject/postgres/pgconnection.py
===================================================================
--- home/phd/SQLObject/postgres-7.2/sqlobject/postgres/pgconnection.py	2004-12-06 13:30:03 UTC (rev 445)
+++ home/phd/SQLObject/postgres-7.2/sqlobject/postgres/pgconnection.py	2004-12-06 13:31:56 UTC (rev 446)
@@ -98,8 +98,14 @@
     def dropTable(self, tableName, cascade=False):
         if self.server_version[:3] <= "7.2":
             cascade=False
+            primaryKey = self._getPrimaryKey(tableName)
+        print "DROP TABLE and SEQUENCE..."
         self.query("DROP TABLE %s %s" % (tableName,
                                          cascade and 'CASCADE' or ''))
+        if self.server_version[:3] <= "7.2":
+            if len(tableName) + len("_%s_seq" % primaryKey) > 31: # max postgres name length
+                tableNem = tableName[:len("_%s_seq" % primaryKey)]
+            self.query("DROP SEQUENCE %s_%s_seq" % (tableName, primaryKey))
 
     def joinSQLType(self, join):
         return 'INT NOT NULL'
@@ -120,8 +126,69 @@
                    (tableName,
                     column.dbName))
 
+    def _getPrimaryKey(self, tableName):
+
+        primaryKeyQuery = """
+        SELECT a.attname FROM pg_attribute a, pg_index i, pg_class c
+        WHERE a.attnum = i.indkey[0] AND i.indisprimary = TRUE
+            AND a.attrelid = c.oid AND i.indrelid = c.oid AND c.relname = %s """
+
+        primaryData = self.queryOne(primaryKeyQuery % self.sqlrepr(tableName))
+        primaryKey = None
+        if primaryData:
+            primaryKey = primaryData[0]
+        return primaryKey
+
+    def columnsFromSchemaOld(self, tableName, soClass):
+
+        # This query doesn't work, at least for me, because
+        # pg_relcheck table is empty.
+        keyQuery = """
+        SELECT r.rcsrc
+        FROM pg_relcheck r, pg_class c
+        WHERE r.rcrelid = c.oid AND c.relname = %s"""
+
+        colQuery = """
+        SELECT a.attname, type.typname, a.attnotnull, a.atthasdef, def.adsrc
+        FROM pg_attribute a
+           JOIN pg_class c ON a.attrelid = c.oid
+           JOIN pg_type type ON a.atttypid = type.oid
+           LEFT JOIN pg_attrdef def ON def.adrelid = c.oid AND def.adnum = a.attnum
+        WHERE c.relname = %s AND type.typname NOT IN ('oid', 'cid', 'tid', 'xid')
+        ORDER BY a.attnum"""
+
+        keyData = self.queryAll(keyQuery % self.sqlrepr(tableName))
+        keyRE = re.compile(r"\((.+)\) REFERENCES (.+)\(")
+        keymap = {}
+
+        for (condef,) in keyData:
+            match = keyRE.search(condef)
+            if match:
+                field, reftable = match.groups()
+                keymap[field] = reftable.capitalize()
+
+        primaryKey = self._getPrimaryKey(tableName)
+
+        colData = self.queryAll(colQuery % self.sqlrepr(tableName))
+        results = []
+        for field, t, notnull, has_default, defaultstr in colData:
+            if field == primaryKey:
+                continue
+            colClass, kw = self.guessClass(t)
+            kw['name'] = soClass._style.dbColumnToPythonAttr(field)
+            kw['notNone'] = notnull
+            if has_default and defaultstr is not None:
+                kw['default'] = getattr(sqlbuilder.const, defaultstr)
+            if keymap.has_key(field):
+                kw['foreignKey'] = keymap[field]
+            results.append(colClass(**kw))
+        return results
+
     def columnsFromSchema(self, tableName, soClass):
 
+        if self.server_version[:3] <= "7.2":
+            return self.columnsFromSchemaOld(tableName, soClass)
+
         keyQuery = """
         SELECT pg_catalog.pg_get_constraintdef(oid) as condef
         FROM pg_catalog.pg_constraint r
@@ -129,12 +196,12 @@
 
         colQuery = """
         SELECT a.attname,
-        pg_catalog.format_type(a.atttypid, a.atttypmod), a.attnotnull,
-        (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
-        WHERE d.adrelid=a.attrelid AND d.adnum = a.attnum)
+            pg_catalog.format_type(a.atttypid, a.atttypmod), a.attnotnull,
+            (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
+            WHERE d.adrelid=a.attrelid AND d.adnum = a.attnum)
         FROM pg_catalog.pg_attribute a
         WHERE a.attrelid =%s::regclass
-        AND a.attnum > 0 AND NOT a.attisdropped
+            AND a.attnum > 0 AND NOT a.attisdropped
         ORDER BY a.attnum"""
 
         primaryKeyQuery = """
@@ -145,13 +212,12 @@
         WHERE c.relname = %s
             AND c.oid = pg_index.indrelid
             AND pg_index.indexrelid = c2.oid
-            AND pg_index.indisprimary
-        """
+            AND pg_index.indisprimary"""
 
         keyData = self.queryAll(keyQuery % self.sqlrepr(tableName))
         keyRE = re.compile(r"\((.+)\) REFERENCES (.+)\(")
         keymap = {}
-        
+
         for (condef,) in keyData:
             match = keyRE.search(condef)
             if match: