Changeset 5785

Timestamp:

08/01/07 22:28:52 (1 year ago)

Author:

danderson

Message:

schema-evolution:
added new "pk_requires_unique" option to the backend, because sqlite3 requires "UNIQUE" when creating PKs in order to
_actually_ create the constraint.
fixed "get_known_column_flags" introspection for sqlite3
implemented "get_drop_column_sql" for sqlite3 to work around sqlite's lack of DROP COLUMN support
added partial of the sqlite3 unit tests

Files:

• django/branches/schema-evolution/django/core/management.py (modified) (4 diffs)
• django/branches/schema-evolution/django/db/backends/ado_mssql/base.py (modified) (1 diff)
• django/branches/schema-evolution/django/db/backends/mysql/base.py (modified) (2 diffs)
• django/branches/schema-evolution/django/db/backends/mysql_old/base.py (modified) (1 diff)
• django/branches/schema-evolution/django/db/backends/postgresql/base.py (modified) (2 diffs)
• django/branches/schema-evolution/django/db/backends/postgresql/introspection.py (modified) (1 diff)
• django/branches/schema-evolution/django/db/backends/postgresql_psycopg2/base.py (modified) (1 diff)
• django/branches/schema-evolution/django/db/backends/sqlite3/base.py (modified) (5 diffs)
• django/branches/schema-evolution/django/db/backends/sqlite3/introspection.py (modified) (3 diffs)
• django/branches/schema-evolution/tests/modeltests/schema_evolution/models.py (modified) (1 diff)

django/branches/schema-evolution/django/core/management.py

@@ -171 +171 @@
-            style.SQL_COLTYPE(col_type)]
-        field_output.append(style.SQL_KEYWORD('%sNULL' % (not f.null and 'NOT ' or '')))
-f.unique and (not f.primary_key or backend.allows_unique_and_pk
+(f.unique and (not f.primary_key or backend.allows_unique_and_pk)) or (f.primary_key and backend.pk_requires_unique
-            field_output.append(style.SQL_KEYWORD('UNIQUE'))
-        if f.primary_key:
@@ -570 +570 @@
-            col_type = data_types[data_type]
-            if col_type is not None:
-#                field_output = []
-#                field_output.append('ALTER TABLE')
-#                field_output.append(db_table)
-#                field_output.append('ADD COLUMN')
-#                field_output.append(backend.quote_name(f.column))
-#                field_output.append(style.SQL_COLTYPE(col_type % rel_field.__dict__))
-#                field_output.append(style.SQL_KEYWORD('%sNULL' % (not f.null and 'NOT ' or '')))
-#                if f.unique:
-#                    field_output.append(style.SQL_KEYWORD('UNIQUE'))
-#                if f.primary_key:
-#                    field_output.append(style.SQL_KEYWORD('PRIMARY KEY'))
-#                output.append(' '.join(field_output) + ';')
-                output.append( backend.get_add_column_sql( db_table, f.column, style.SQL_COLTYPE(col_type % rel_field.__dict__), f.null, f.unique, f.primary_key ) )
-    return output
@@ -665 +653 @@
-                    column_flags['primary_key']!=f.primary_key:
-                    #column_flags['foreign_key']!=f.foreign_key:
-
+'need to change'
-#                print db_table, f.column, column_flags
-#                print "column_flags['allow_null']!=f.null", column_flags['allow_null']!=f.null
@@ -704 +692 @@
-        if f.aka: suspect_fields.difference_update(f.aka)
-    if len(suspect_fields)>0:
-as the following may cause data loss, it/they must be run manually
+the following may cause data loss
-        for suspect_field in suspect_fields:
-append( '-- '+
+extend(
-        output.append( '-- end warning' )
-    return output

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

@@ -92 +92 @@
-allows_group_by_ordinal = True
-allows_unique_and_pk = True
+pk_requires_unique = False
-autoindexes_primary_keys = True
-needs_datetime_string_cast = True

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

@@ -137 +137 @@
-allows_group_by_ordinal = True
-allows_unique_and_pk = True
+pk_requires_unique = False
-autoindexes_primary_keys = False
-needs_datetime_string_cast = True     # MySQLdb requires a typecast for dates
@@ -285 +286 @@
-    output = []
-    output.append( 'ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(col_name) + ';' )
-'\n'.join(output)
+output
-    
-    

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

@@ -152 +152 @@
-allows_group_by_ordinal = True
-allows_unique_and_pk = True
+pk_requires_unique = False
-autoindexes_primary_keys = False
-needs_datetime_string_cast = True     # MySQLdb requires a typecast for dates

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

@@ -118 +118 @@
-allows_group_by_ordinal = True
-allows_unique_and_pk = True
+pk_requires_unique = False
-autoindexes_primary_keys = True
-needs_datetime_string_cast = True
@@ -322 +323 @@
-    output = []
-    output.append( 'ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(col_name) + ';' )
-'\n'.join(output)
+output
-
-# Register these custom typecasts, because Django expects dates/times to be

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

@@ -82 +82 @@
-    dict['unique'] = False
-    dict['default'] = ''
-        
-
+dict['allow_null'] = False
+
-    for row in cursor.fetchall():
-        if row[0] == column_name:

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

@@ -80 +80 @@
-allows_group_by_ordinal = True
-allows_unique_and_pk = True
+pk_requires_unique = False
-autoindexes_primary_keys = True
-needs_datetime_string_cast = False

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

@@ -3 +3 @@
-"""
-
+from django.core import management
-from django.db.backends import util
-try:
@@ -103 +104 @@
-allows_group_by_ordinal = True
-allows_unique_and_pk = True
+pk_requires_unique = True # or else the constraint is never created
-autoindexes_primary_keys = True
-needs_datetime_string_cast = True
@@ -228 +230 @@
-    output.append( 'UPDATE '+ quote_name(table_name) +' SET '+ new_col_name +' = '+ old_col_name +' WHERE '+ pk_name +'=(select '+ pk_name +' from '+ table_name +');' )
-    output.append( '-- FYI: sqlite does not support deleting columns, so  '+ quote_name(old_col_name) +' remains as cruft' )
-    # use the following when sqlite gets drop support
-    #output.append( 'ALTER TABLE '+ quote_name(table_name) +' DROP COLUMN '+ quote_name(old_col_name) )
-    return '\n'.join(output)
-
-def
+type, null, unique, primary_key
-    # sqlite doesn't support column modifications, so we fake it
-    output = []
+    col_def = col_type +' '+ ('%sNULL' % (not null and 'NOT ' or ''))
+    if unique or primary_key:
+        col_def += ' '+ 'UNIQUE'
+    if primary_key:
+        col_def += ' '+ 'PRIMARY KEY'
-    # TODO: fake via renaming the table, building a new one and deleting the old
-    output.append('-- sqlite does not support column modifications '+ quote_name(table_name) +'.'+ quote_name(col_name) +' to '+ col_def)
@@ -248 +253 @@
-    field_output.append(col_type)
-    field_output.append(('%sNULL' % (not null and 'NOT ' or '')))
-
+ or primary_key
-        field_output.append(('UNIQUE'))
-    if primary_key:
@@ -256 +261 @@
-
-def get_drop_column_sql( table_name, col_name ):
+    model = get_model_from_table_name(table_name)
-    output = []
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-    
-

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

@@ -54 +54 @@
-    cursor.execute("PRAGMA table_info(%s)" % quote_name(table_name))
-    dict = {}
+    dict['primary_key'] = False
+    dict['foreign_key'] = False
+    dict['unique'] = False
+    dict['default'] = ''
+    dict['allow_null'] = True
+
-    for row in cursor.fetchall():
+#        print row
-        if row[1] == column_name:
+            col_type = row[2]
-
-            # maxlength check goes here
@@ -62 +70 @@
-            
-            # default flag check goes here
-
-
+
-            
+            # default value check goes here
+            dict['default'] = row[4]
+
+    cursor.execute("PRAGMA index_list(%s)" % quote_name(table_name))
+    index_names = []
+    for row in cursor.fetchall():
+        index_names.append(row[1])
+    for index_name in index_names:
+        cursor.execute("PRAGMA index_info(%s)" % quote_name(index_name))
+        for row in cursor.fetchall():
+            if row[2]==column_name:
+                if col_type=='integer': dict['primary_key'] = True  # sqlite3 does not distinguish between unique and pk; all 
+                else: dict['unique'] = True                         # unique integer columns are treated as part of the pk.
+
-            # primary/foreign/unique key flag check goes here
-            #if row[3]=='PRI': dict['primary_key'] = True
@@ -73 +94 @@
-            #else: dict['unique'] = False
-            
-
-
-
-
-
-
+
+
-    return dict
-    

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

@@ -142 +142 @@
-"""
-
+if settings.DATABASE_ENGINE == 'sqlite3':
+    __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" 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
+>>> management.get_sql_evolution(app)
+[]
+
+# delete a column, so it looks like we've recently added a field
+>>> cursor.execute( 'DROP TABLE "schema_evolution_person";' ).__class__
+<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'>
+>>> cursor.execute( 'CREATE TABLE "schema_evolution_person" ( "id" integer NOT NULL UNIQUE PRIMARY KEY, "name" varchar(20) NOT NULL, "gender" varchar(1) NOT NULL );' ).__class__
+<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'>
+>>> management.get_sql_evolution(app)
+['ALTER TABLE "schema_evolution_person" ADD COLUMN "gender2" varchar(1) NOT NULL;']
+
+# reset the db
+>>> cursor.execute('DROP TABLE schema_evolution_person;').__class__
+<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'>
+>>> cursor.execute(create_table_sql[0]).__class__
+<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'>
+
+# add a column, so it looks like we've recently deleted a field
+>>> cursor.execute( 'DROP TABLE "schema_evolution_person";' ).__class__
+<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'>
+>>> cursor.execute( 'CREATE TABLE "schema_evolution_person" ( "id" integer NOT NULL UNIQUE PRIMARY KEY, "name" varchar(20) NOT NULL, "gender" varchar(1) NOT NULL, "gender2" varchar(1) NOT NULL, "gender_new" varchar(1) NOT NULL );' ).__class__
+<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'>
+>>> management.get_sql_evolution(app)
+['-- warning: the following may cause data loss', u'-- FYI: sqlite does not support deleting columns, so we create a new "gender_new" 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";', '-- end warning']
+
+# reset the db
+>>> cursor.execute('DROP TABLE schema_evolution_person;').__class__
+<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'>
+>>> cursor.execute(create_table_sql[0]).__class__
+<class 'django.db.backends.sqlite3.base.SQLiteCursorWrapper'>
+
+"""
+
+crap = """
+
+# rename column, so it looks like we've recently renamed a field
+>>> cursor.execute( backend.get_change_column_name_sql( 'schema_evolution_person', {}, 'gender2', 'gender_old', 'varchar(1)' ) )
+>>> management.get_sql_evolution(app)
+['ALTER TABLE "schema_evolution_person" RENAME COLUMN "gender_old" TO "gender2";']
+
+# reset the db
+>>> cursor.execute('DROP TABLE schema_evolution_person;'); cursor.execute(create_table_sql[0])
+
+# rename table, so it looks like we've recently renamed a model
+>>> cursor.execute( backend.get_change_table_name_sql( 'schema_evolution_personold', 'schema_evolution_person' ) )
+>>> management.get_sql_evolution(app)
+['ALTER TABLE "schema_evolution_personold" RENAME TO "schema_evolution_person";']
+
+# reset the db
+>>> cursor.execute(create_table_sql[0])
+
+# change column flags, so it looks like we've recently changed a column flag
+>>> cursor.execute( backend.get_change_column_def_sql( 'schema_evolution_person', 'name', 'varchar(10)', True, False, False ) )
+>>> management.get_sql_evolution(app)
+['ALTER TABLE "schema_evolution_person" ADD COLUMN "name_tmp" varchar(20);\\nUPDATE "schema_evolution_person" SET "name_tmp" = "name";\\nALTER TABLE "schema_evolution_person" DROP COLUMN "name";\\nALTER TABLE "schema_evolution_person" RENAME COLUMN "name_tmp" TO "name";\\nALTER TABLE "schema_evolution_person" ALTER COLUMN "name" SET NOT NULL;']
+
+"""
+