Changeset 5792

Timestamp:

08/03/07 15:49:19 (1 year ago)

Author:

danderson

Message:

schema-evolution:
added the postgresql_psycopg2 backend
added more unit tests

Files:

• django/branches/schema-evolution/django/db/backends/postgresql_psycopg2/base.py (modified) (1 diff)
• django/branches/schema-evolution/django/db/backends/postgresql_psycopg2/introspection.py (modified) (1 diff)
• django/branches/schema-evolution/tests/modeltests/schema_evolution/models.py (modified) (6 diffs)

django/branches/schema-evolution/django/db/backends/postgresql_psycopg2/base.py

@@ -226 +226 @@
-    return output
-
+def get_change_table_name_sql( table_name, old_table_name ):
+    output = []
+    output.append('ALTER TABLE '+ quote_name(old_table_name) +' RENAME TO '+ quote_name(table_name) + ';')
+    return output
+
+def get_change_column_name_sql( table_name, indexes, old_col_name, new_col_name, col_def ):
+    # TODO: only supports a single primary key so far
+    pk_name = None
+    for key in indexes.keys():
+        if indexes[key]['primary_key']: pk_name = key
+    output = []
+    output.append( 'ALTER TABLE '+ quote_name(table_name) +' RENAME COLUMN '+ quote_name(old_col_name) +' TO '+ quote_name(new_col_name) +';' )
+    return output
+
+def get_change_column_def_sql( table_name, col_name, col_type, null, unique, primary_key ):
+    output = []
+    output.append( 'ALTER TABLE '+ quote_name(table_name) +' ADD COLUMN '+ quote_name(col_name+'_tmp') +' '+ col_type + ';' )
+    output.append( 'UPDATE '+ quote_name(table_name) +' SET '+ quote_name(col_name+'_tmp') +' = '+ quote_name(col_name) + ';' )
+    output.append( 'ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(col_name) +';' )
+    output.append( 'ALTER TABLE '+ quote_name(table_name) +' RENAME COLUMN '+ quote_name(col_name+'_tmp') +' TO '+ quote_name(col_name) + ';' )
+    if not null:
+        output.append( 'ALTER TABLE '+ quote_name(table_name) +' ALTER COLUMN '+ quote_name(col_name) +' SET NOT NULL;' )
+    if unique:
+        output.append( 'ALTER TABLE '+ quote_name(table_name) +' ADD CONSTRAINT '+ table_name +'_'+ col_name +'_unique_constraint UNIQUE('+ col_name +');' )
+    
+    return output
+
+def get_add_column_sql( table_name, col_name, col_type, null, unique, primary_key  ):
+    output = []
+    output.append( 'ALTER TABLE '+ quote_name(table_name) +' ADD COLUMN '+ quote_name(col_name) +' '+ col_type + ';' )
+    if not null:
+        output.append( 'ALTER TABLE '+ quote_name(table_name) +' ALTER COLUMN '+ quote_name(col_name) +' SET NOT NULL;' )
+    if unique:
+        output.append( 'ALTER TABLE '+ quote_name(table_name) +' ADD CONSTRAINT '+ table_name +'_'+ col_name +'_unique_constraint UNIQUE('+ col_name +');' )
+    return output
+    
+def get_drop_column_sql( table_name, col_name ):
+    output = []
+    output.append( 'ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(col_name) + ';' )
+    return output
+
-OPERATOR_MAPPING = {
-    'exact': '= %s',

django/branches/schema-evolution/django/db/backends/postgresql_psycopg2/introspection.py

@@ -64 +64 @@
-    return indexes
-
+def get_columns(cursor, table_name):
+    try:
+        cursor.execute("SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum, pg_catalog.col_description(a.attrelid, a.attnum) FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid from pg_catalog.pg_class c where c.relname ~ '^%s$') AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum" % table_name)
+        return [row[0] for row in cursor.fetchall()]
+    except:
+        return []
+    
+def get_known_column_flags( cursor, table_name, column_name ):
+#    print "SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum, pg_catalog.col_description(a.attrelid, a.attnum) FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid from pg_catalog.pg_class c where c.relname ~ '^%s$') AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum" % table_name
+    cursor.execute("SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum, pg_catalog.col_description(a.attrelid, a.attnum) FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT c.oid from pg_catalog.pg_class c where c.relname ~ '^%s$') AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum" % table_name)
+    dict = {}
+    dict['primary_key'] = False
+    dict['foreign_key'] = False
+    dict['unique'] = False
+    dict['default'] = ''
+    dict['allow_null'] = False
+
+    for row in cursor.fetchall():
+        if row[0] == column_name:
+
+            # maxlength check goes here
+            if row[1][0:17]=='character varying':
+                dict['maxlength'] = row[1][18:len(row[1])-1]
+            
+            # null flag check goes here
+            dict['allow_null'] = not row[3]
+
+    # pk, fk and unique checks go here
+#    print "select pg_constraint.conname, pg_constraint.contype, pg_attribute.attname from pg_constraint, pg_attribute where pg_constraint.conrelid=pg_attribute.attrelid and pg_attribute.attnum=any(pg_constraint.conkey) and pg_constraint.conname~'^%s'" % table_name 
+    unique_conname = None
+    shared_unique_connames = set()
+    cursor.execute("select pg_constraint.conname, pg_constraint.contype, pg_attribute.attname from pg_constraint, pg_attribute, pg_class where pg_constraint.conrelid=pg_class.oid and pg_constraint.conrelid=pg_attribute.attrelid and pg_attribute.attnum=any(pg_constraint.conkey) and pg_class.relname='%s'" % table_name )
+    for row in cursor.fetchall():
+#        print row
+        if row[2] == column_name:
+            if row[1]=='p': dict['primary_key'] = True
+            if row[1]=='f': dict['foreign_key'] = True
+            if row[1]=='u': unique_conname = row[0]
+        else:
+            if row[1]=='u': shared_unique_connames.add( row[0] )
+    if unique_conname and unique_conname not in shared_unique_connames:
+        dict['unique'] = True
+        
+            # default value check goes here
+    cursor.execute("select pg_attribute.attname, adsrc from pg_attrdef, pg_attribute WHERE pg_attrdef.adrelid=pg_attribute.attrelid and pg_attribute.attnum=pg_attrdef.adnum and pg_attrdef.adrelid = (SELECT c.oid from pg_catalog.pg_class c where c.relname ~ '^%s$')" % table_name )
+    for row in cursor.fetchall():
+        if row[0] == column_name:
+            if row[1][0:7] == 'nextval': continue
+            dict['default'] = row[1][1:row[1].index("'",1)]
+            
+#    print table_name, column_name, dict
+    return dict
+
-# Maps type codes to Django Field types.
-DATA_TYPES_REVERSE = {

django/branches/schema-evolution/tests/modeltests/schema_evolution/models.py

@@ -21 +21 @@
-    class Meta:
-        aka = ('PersonOld', 'OtherBadName')
+
+class Muebles(models.Model):
+    tipo = models.CharField(maxlength=40)
+    # new fields
+    fecha_publicacion = models.DateTimeField('date published')
-
-__test__ = {'API_TESTS':"""
@@ -35 +40 @@
-# the table as it is supposed to be
->>> create_table_sql = management.get_sql_all(app)
->>> print create_table_sql
-['CREATE TABLE `schema_evolution_person` (\\n    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,\\n    `name` varchar(20) NOT NULL,\\n    `gender` varchar(1) NOT NULL,\\n    `gender2` varchar(1) NOT NULL\\n)\\n;']
-
-# make sure we don't evolve an unedited table
@@ -90 +93 @@
->>> management.get_sql_evolution(app)
-['ALTER TABLE `schema_evolution_person` MODIFY COLUMN `name` varchar(20) NOT NULL;']
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-    __test__['API_TESTS'] += """
-# the table as it is supposed to be
->>> create_table_sql = management.get_sql_all(app)
->>> print create_table_sql
-['CREATE TABLE "schema_evolution_person" (\\n    "id" serial NOT NULL PRIMARY KEY,\\n    "name" varchar(20) NOT NULL,\\n    "gender" varchar(1) NOT NULL,\\n    "gender2" varchar(1) NOT NULL\\n)\\n;']
-
-# make sure we don't evolve an unedited table
@@ -140 +153 @@
-['ALTER TABLE "schema_evolution_person" ADD COLUMN "name_tmp" varchar(20);', 'UPDATE "schema_evolution_person" SET "name_tmp" = "name";', 'ALTER TABLE "schema_evolution_person" DROP COLUMN "name";', 'ALTER TABLE "schema_evolution_person" RENAME COLUMN "name_tmp" TO "name";', 'ALTER TABLE "schema_evolution_person" ALTER COLUMN "name" SET NOT NULL;']
-
+# reset the db
+>>> cursor.execute('DROP TABLE schema_evolution_person;'); cursor.execute(create_table_sql[0])
+
+# delete a datetime column pair, so it looks like we've recently added a datetime field
+>>> for sql in backend.get_drop_column_sql( 'schema_evolution_muebles', 'fecha_publicacion' ): print sql; cursor.execute(sql)
+ALTER TABLE "schema_evolution_muebles" DROP COLUMN "fecha_publicacion";
+>>> management.get_sql_evolution(app)
+['ALTER TABLE "schema_evolution_muebles" ADD COLUMN "fecha_publicacion" timestamp with time zone;', 'ALTER TABLE "schema_evolution_muebles" ALTER COLUMN "fecha_publicacion" SET NOT NULL;']
-"""
-
@@ -146 +167 @@
-# the table as it is supposed to be
->>> create_table_sql = management.get_sql_all(app)
->>> print create_table_sql
-['CREATE TABLE "schema_evolution_person" (\\n    "id" integer NOT NULL UNIQUE PRIMARY KEY,\\n    "name" varchar(20) NOT NULL,\\n    "gender" varchar(1) NOT NULL,\\n    "gender2" varchar(1) NOT NULL\\n)\\n;']
-
-# make sure we don't evolve an unedited table
@@ -244 +263 @@
->>> management.get_sql_evolution(app)
-['-- FYI: sqlite does not support changing columns, so we create a new "schema_evolution_person" and delete the old  (ie, this could take a while)', 'ALTER TABLE "schema_evolution_person" RENAME TO "schema_evolution_person_1337_TMP";', 'CREATE TABLE "schema_evolution_person" (\\n    "id" integer NOT NULL UNIQUE PRIMARY KEY,\\n    "name" varchar(20) NOT NULL,\\n    "gender" varchar(1) NOT NULL,\\n    "gender2" varchar(1) NOT NULL\\n)\\n;', 'INSERT INTO "schema_evolution_person" SELECT "id","name","gender","gender2" FROM "schema_evolution_person_1337_TMP";', 'DROP TABLE "schema_evolution_person_1337_TMP";']
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+