Changeset 23

Timestamp:

03/08/07 18:52:55

Author:

fumanchu

Message:

New Table.add_primary, drop_primary methods. Also made a new 'Bijection' superclass for Schema, Table, and IndexSet?. Also fixed a bug in SQLite temp tables (indices were not being copied).

Files:

• trunk/geniusql/objects.py (modified) (8 diffs)
• trunk/geniusql/providers/ado.py (modified) (1 diff)
• trunk/geniusql/providers/firebird.py (modified) (1 diff)
• trunk/geniusql/providers/mysql.py (modified) (1 diff)
• trunk/geniusql/providers/postgres.py (modified) (1 diff)
• trunk/geniusql/providers/sqlite.py (modified) (10 diffs)
• trunk/geniusql/test/zoo_fixture.py (modified) (1 diff)

trunk/geniusql/objects.py

@@ -19 +19 @@
-                return key
-        raise ValueError("The given value could not be found: %r" % value)
+    
+    def alias(self, oldname, newname):
+        """Move the object at the given, existing key to the new key.
+        
+        Consumer code should call this method when user-supplied object
+        names do not match the names in the database.
+        """
+        if oldname == newname:
+            return
+        
+        obj = self[oldname]
+        if newname in self:
+            dict.__delitem__(self, newname)
+        dict.__delitem__(self, oldname)
+        dict.__setitem__(self, newname, obj)
-
-
@@ -57 +72 @@
-    def __setstate__(self, state):
-        self.update(state)
-    
-    def alias(self, oldname, newname):
-        """Move the Index at the given, existing key to the new key.
-        
-        Consumer code should call this method when user-supplied index
-        names do not match the names in the database.
-        """
-        if oldname == newname:
-            return
-        obj = self[oldname]
-        if newname in self:
-            dict.__delitem__(self, newname)
-        dict.__delitem__(self, oldname)
-        dict.__setitem__(self, newname, obj)
-    
-    def __setitem__(self, key, index):
@@ -90 +91 @@
-
-
-
+(object)
-    """A column in a table in a database.
-    
@@ -169 +170 @@
-        Table, or calls methods like 'rename'.
-    indices: a dict-like IndexSet of Index objects.
-
+
+
-    """
-    
@@ -210 +212 @@
-        self.indices.table = self
-    
-    def alias(self, oldname, newname):
-        """Move the Column at the given, existing key to the new key.
-        
-        Consumer code should call this method when user-supplied column
-        names do not match the names in the database.
-        """
-        if oldname == newname:
-            return
-        
-        obj = self[oldname]
-        if newname in self:
-            dict.__delitem__(self, newname)
-        dict.__delitem__(self, oldname)
-        dict.__setitem__(self, newname, obj)
-    
-    def _add_column(self, column):
-        """Internal function to add the column to the database."""
@@ -309 +296 @@
-        return i
-    
+    def add_primary(self):
+        """Set the PRIMARY KEY for this Table, using its Column.key values."""
+        pk = [column.qname for column in self.itervalues() if column.key]
+        if pk:
+            self.schema.db.execute("ALTER TABLE %s ADD PRIMARY KEY (%s);" %
+                                   (self.qname, ", ".join(pk)))
+    
+    def drop_primary(self):
+        """Remove any PRIMARY KEY for this Table."""
+        raise NotImplementedError
-    
-    # ---------------------------- OLTP/CRUD ---------------------------- #
@@ -332 +329 @@
-            if self[key].key:
-                ids[key] = value
-
+
+
+
+
+
-    
-    def insert(self, **inputs):
@@ -597 +598 @@
-        finally:
-            self._discover_lock.release()
-    
-    def alias(self, oldname, newname):
-        """Move the Table at the given, existing key to the new key.
-        
-        Consumer code should call this method when user-supplied table
-        names do not match the names in the database.
-        """
-        if oldname == newname:
-            return
-        
-        obj = self[oldname]
-        if newname in self:
-            dict.__delitem__(self, newname)
-        dict.__delitem__(self, oldname)
-        dict.__setitem__(self, newname, obj)
-    
-    def _column_name(self, tablename, columnkey):

trunk/geniusql/providers/ado.py

@@ -378 +378 @@
-            conn = None
-            cat = None
+    
+    def drop_primary(self):
+        """Remove any PRIMARY KEY for this Table."""
+        db = self.schema.db
+        
+        data, _ = db.fetch(adSchemaIndexes, schema=True)
+        pknames = [row[5] for row in data
+                   if (self.name == row[2]) and row[6]]
+        for name in pknames:
+            db.execute('ALTER TABLE %s DROP CONSTRAINT %s;'
+                       % (self.qname, name))
-
-

trunk/geniusql/providers/firebird.py

@@ -301 +301 @@
-        
-        return newids
+    
+    def drop_primary(self):
+        """Remove any PRIMARY KEY for this Table."""
+        db = self.schema.db
+        
+        data, _ = db.fetch(
+            "SELECT RDB$CONSTRAINT_NAME FROM RDB$RELATION_CONSTRAINTS "
+            "WHERE (RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') "
+            "AND (RDB$RELATION_NAME = '%s')" % self.name)
+        for row in data:
+            db.execute('ALTER TABLE %s DROP CONSTRAINT %s;'
+                       % (self.qname, row[0].rstrip()))
-
-

trunk/geniusql/providers/mysql.py

@@ -181 +181 @@
-    def _grab_new_ids(self, idkeys, conn):
-        return {idkeys[0]: conn.insert_id()}
+    
+    def drop_primary(self):
+        """Remove any PRIMARY KEY for this Table."""
+        self.schema.db.execute('ALTER TABLE %s DROP PRIMARY KEY;' % self.qname)
-
-

trunk/geniusql/providers/postgres.py

@@ -131 +131 @@
-            newids[idkey] = data[0][0]
-        return newids
+    
+    def drop_primary(self):
+        """Remove any PRIMARY KEY for this Table."""
+        db = self.schema.db
+        
+        # Get the OID of the table
+        data, _ = db.fetch("SELECT oid FROM pg_class WHERE "
+                           "relname = '%s'" % self.name)
+        table_OID = data[0][0]
+        
+        data, _ = db.fetch("SELECT conname, * FROM pg_constraint WHERE conrelid "
+                           "= %s AND contype = 'p'" % table_OID)
+        for row in data:
+            constraint_name = row[0]
+            db.execute('ALTER TABLE %s DROP CONSTRAINT "%s";'
+                       % (self.qname, constraint_name))
-
-

trunk/geniusql/providers/sqlite.py

@@ -285 +285 @@
-
-class SQLiteTable(geniusql.Table):
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-    
-    def _temp_copy(self):
-
+
+
-        temptable = self.copy()
-
-
-
-
+
+
+
+
+
+
+
+
-        return tempkey, temptable
-    
-hiskey, t
+
-        """Copy data from a temp table to a new table for self."""
+        schema = self.schema
-        
-        # Drop the old table and create the new, final table.
@@ -307 +337 @@
-        newtable.name = self.name
-        newtable.qname = self.qname
-
+
+
+
+
+
+
-        
-        # Copy data from the temp table to the final table.
@@ -313 +348 @@
-        # mixes up the fields (during rename, at least).
-        selfields = ", ".join([c.qname for c in temptable.values()])
-elf.s
-     
-     
+
+
+
-        
-        # Drop the intermediate table.
-elf.s
+
-    
-    if not _add_column_support:
@@ -337 +372 @@
-            # Add the new column to the copy.
-            dict.__setitem__(temptable, key, column)
-
+ (this will call CREATE TABLE)
-            self.schema[tempkey] = temptable
-            
@@ -353 +388 @@
-            
-            # Copy data from the temp table to a new table for self.
-self._parent_key(), 
+
-    
-    def __delitem__(self, key):
@@ -369 +404 @@
-        # Drop the column from the copy.
-        dict.__delitem__(temptable, key)
-
+ (this will call CREATE TABLE)
-        self.schema[tempkey] = temptable
-        
-        # Copy data from the old table to the temp table.
-
-
-
-
+
-        self.schema.db.execute_ddl("INSERT INTO %s SELECT %s FROM %s;" %
-                                   (temptable.qname, ", ".join(selfields),
-                                    self.qname))
-        
-self._parent_key(), 
+
-        
-        if column.autoincrement:
@@ -401 +433 @@
-            # Make a temporary copy.
-            tempkey, temptable = self._temp_copy()
-
+ (this will call CREATE TABLE)
-            self.schema[tempkey] = temptable
-            
@@ -415 +447 @@
-                                        self.qname))
-            
-self._parent_key(), 
+
-    
-    def _grab_new_ids(self, idkeys, conn):
@@ -423 +455 @@
-            new_id = conn.sqlite_last_insert_rowid()
-        return {idkeys[0]: new_id}
+    
+    def add_primary(self):
+        """Set the PRIMARY KEY for this Table, using its Column.key values."""
+        pk = [column.qname for column in self.itervalues() if column.key]
+        if pk:
+            # Make a temporary copy.
+            tempkey, temptable = self._temp_copy()
+            # Bind the temp table to the DB.
+            self.schema[tempkey] = temptable
+            # Copy data from the old table to the temp table.
+            selfields = [c.qname for c in temptable.itervalues()]
+            self.schema.db.execute_ddl("INSERT INTO %s SELECT %s FROM %s;" %
+                                       (temptable.qname, ", ".join(selfields),
+                                        self.qname))
+            # Create the new table and copy data from the temp table to it.
+            self._copy_from_temp(temptable, tempkey)
+    
+    def drop_primary(self):
+        """Remove any PRIMARY KEY for this Table."""
+        # Make a temporary copy.
+        tempkey, temptable = self._temp_copy()
+        # Drop all .key's from the copy.
+        for col in temptable.itervalues():
+            col.key = False
+        # Bind the temp table to the DB.
+        self.schema[tempkey] = temptable
+        
+        # Copy data from the old table to the temp table.
+        selfields = [c.qname for c in temptable.itervalues()]
+        self.schema.db.execute_ddl("INSERT INTO %s SELECT %s FROM %s;" %
+                                   (temptable.qname, ", ".join(selfields),
+                                    self.qname))
+        # Create the new table and copy data from the temp table to it.
+        self._copy_from_temp(temptable, tempkey)
+
-
-
@@ -605 +672 @@
-        cols = []
-        for row in data:
-
-
-
-
+
+
+
+
+
-            
-            # "A single row can hold up to 2 ** 30 bytes of data

trunk/geniusql/test/zoo_fixture.py

@@ -603 +603 @@
-        ostrich = schema['Animal'].select(Species='Ostrich')
-        self.assertEqual(ostrich, None)
+    
+    def test_primary_key_support(self):
+        # Drop and re-add the PK on, oh, how about the Animal table?
+        schema['Animal'].drop_primary()
+        schema['Animal'].add_primary()
-    
-    def test_insert_into(self):