Author: danderson
Date: 2007-08-01 22:28:52 -0500 (Wed, 01 Aug 2007)
New Revision: 5785
Modified:
django/branches/schema-evolution/django/core/management.py
django/branches/schema-evolution/django/db/backends/ado_mssql/base.py
django/branches/schema-evolution/django/db/backends/mysql/base.py
django/branches/schema-evolution/django/db/backends/mysql_old/base.py
django/branches/schema-evolution/django/db/backends/postgresql/base.py
django/branches/schema-evolution/django/db/backends/postgresql/introspection.py
django/branches/schema-evolution/django/db/backends/postgresql_psycopg2/base.py
django/branches/schema-evolution/django/db/backends/sqlite3/base.py
django/branches/schema-evolution/django/db/backends/sqlite3/introspection.py
django/branches/schema-evolution/django/test/simple.py
django/branches/schema-evolution/tests/modeltests/schema_evolution/models.py
Log:
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
Modified: django/branches/schema-evolution/django/core/management.py
===================================================================
--- django/branches/schema-evolution/django/core/management.py 2007-08-01
17:51:59 UTC (rev 5784)
+++ django/branches/schema-evolution/django/core/management.py 2007-08-02
03:28:52 UTC (rev 5785)
@@ -170,7 +170,7 @@
field_output = [style.SQL_FIELD(backend.quote_name(f.column)),
style.SQL_COLTYPE(col_type)]
field_output.append(style.SQL_KEYWORD('%sNULL' % (not f.null and 'NOT
' or '')))
- if f.unique and (not f.primary_key or backend.allows_unique_and_pk):
+ if (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:
field_output.append(style.SQL_KEYWORD('PRIMARY KEY'))
@@ -569,18 +569,6 @@
data_type = f.get_internal_type()
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
@@ -664,7 +652,7 @@
( column_flags['unique']!=f.unique and (
settings.DATABASE_ENGINE!='postgresql' or not f.primary_key ) ) or \
column_flags['primary_key']!=f.primary_key:
#column_flags['foreign_key']!=f.foreign_key:
-# print
+# print 'need to change'
# print db_table, f.column, column_flags
# print "column_flags['allow_null']!=f.null",
column_flags['allow_null']!=f.null
# print "not f.primary_key and isinstance(f, CharField) and
column_flags['maxlength']!=str(f.maxlength)", not f.primary_key and
isinstance(f, CharField) and column_flags['maxlength']!=str(f.maxlength)
@@ -703,9 +691,9 @@
suspect_fields.discard(f.aka)
if f.aka: suspect_fields.difference_update(f.aka)
if len(suspect_fields)>0:
- output.append( '-- warning: as the following may cause data loss,
it/they must be run manually' )
+ output.append( '-- warning: the following may cause data loss' )
for suspect_field in suspect_fields:
- output.append( '-- '+ backend.get_drop_column_sql( db_table,
suspect_field ) )
+ output.extend( backend.get_drop_column_sql( db_table,
suspect_field ) )
output.append( '-- end warning' )
return output
Modified: django/branches/schema-evolution/django/db/backends/ado_mssql/base.py
===================================================================
--- django/branches/schema-evolution/django/db/backends/ado_mssql/base.py
2007-08-01 17:51:59 UTC (rev 5784)
+++ django/branches/schema-evolution/django/db/backends/ado_mssql/base.py
2007-08-02 03:28:52 UTC (rev 5785)
@@ -91,6 +91,7 @@
allows_group_by_ordinal = True
allows_unique_and_pk = True
+pk_requires_unique = False
autoindexes_primary_keys = True
needs_datetime_string_cast = True
needs_upper_for_iops = False
Modified: django/branches/schema-evolution/django/db/backends/mysql/base.py
===================================================================
--- django/branches/schema-evolution/django/db/backends/mysql/base.py
2007-08-01 17:51:59 UTC (rev 5784)
+++ django/branches/schema-evolution/django/db/backends/mysql/base.py
2007-08-02 03:28:52 UTC (rev 5785)
@@ -136,6 +136,7 @@
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
needs_upper_for_iops = False
@@ -284,7 +285,7 @@
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 '\n'.join(output)
+ return output
OPERATOR_MAPPING = {
Modified: django/branches/schema-evolution/django/db/backends/mysql_old/base.py
===================================================================
--- django/branches/schema-evolution/django/db/backends/mysql_old/base.py
2007-08-01 17:51:59 UTC (rev 5784)
+++ django/branches/schema-evolution/django/db/backends/mysql_old/base.py
2007-08-02 03:28:52 UTC (rev 5785)
@@ -151,6 +151,7 @@
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
needs_upper_for_iops = False
Modified: django/branches/schema-evolution/django/db/backends/postgresql/base.py
===================================================================
--- django/branches/schema-evolution/django/db/backends/postgresql/base.py
2007-08-01 17:51:59 UTC (rev 5784)
+++ django/branches/schema-evolution/django/db/backends/postgresql/base.py
2007-08-02 03:28:52 UTC (rev 5785)
@@ -117,6 +117,7 @@
allows_group_by_ordinal = True
allows_unique_and_pk = True
+pk_requires_unique = False
autoindexes_primary_keys = True
needs_datetime_string_cast = True
needs_upper_for_iops = False
@@ -321,7 +322,7 @@
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 '\n'.join(output)
+ return output
# Register these custom typecasts, because Django expects dates/times to be
# in Python's native (standard-library) datetime/time format, whereas psycopg
Modified:
django/branches/schema-evolution/django/db/backends/postgresql/introspection.py
===================================================================
---
django/branches/schema-evolution/django/db/backends/postgresql/introspection.py
2007-08-01 17:51:59 UTC (rev 5784)
+++
django/branches/schema-evolution/django/db/backends/postgresql/introspection.py
2007-08-02 03:28:52 UTC (rev 5785)
@@ -81,8 +81,8 @@
dict['foreign_key'] = False
dict['unique'] = False
dict['default'] = ''
-
-# dict['allow_null'] = False
+ dict['allow_null'] = False
+
for row in cursor.fetchall():
if row[0] == column_name:
Modified:
django/branches/schema-evolution/django/db/backends/postgresql_psycopg2/base.py
===================================================================
---
django/branches/schema-evolution/django/db/backends/postgresql_psycopg2/base.py
2007-08-01 17:51:59 UTC (rev 5784)
+++
django/branches/schema-evolution/django/db/backends/postgresql_psycopg2/base.py
2007-08-02 03:28:52 UTC (rev 5785)
@@ -79,6 +79,7 @@
allows_group_by_ordinal = True
allows_unique_and_pk = True
+pk_requires_unique = False
autoindexes_primary_keys = True
needs_datetime_string_cast = False
needs_upper_for_iops = False
Modified: django/branches/schema-evolution/django/db/backends/sqlite3/base.py
===================================================================
--- django/branches/schema-evolution/django/db/backends/sqlite3/base.py
2007-08-01 17:51:59 UTC (rev 5784)
+++ django/branches/schema-evolution/django/db/backends/sqlite3/base.py
2007-08-02 03:28:52 UTC (rev 5785)
@@ -2,6 +2,7 @@
SQLite3 backend for django. Requires pysqlite2 (http://pysqlite.org/).
"""
+from django.core import management
from django.db.backends import util
try:
try:
@@ -102,6 +103,7 @@
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
needs_upper_for_iops = False
@@ -227,13 +229,16 @@
output.append( 'ALTER TABLE '+ quote_name(table_name) +' ADD COLUMN '+
quote_name(new_col_name) +' '+ col_def + ';' )
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 get_change_column_def_sql( table_name, col_name, col_def ):
+def get_change_column_def_sql( table_name, col_name, col_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)
return '\n'.join(output)
@@ -247,7 +252,7 @@
field_output.append(quote_name(col_name))
field_output.append(col_type)
field_output.append(('%sNULL' % (not null and 'NOT ' or '')))
- if unique:
+ if unique or primary_key:
field_output.append(('UNIQUE'))
if primary_key:
field_output.append(('PRIMARY KEY'))
@@ -255,11 +260,28 @@
return '\n'.join(output)
def get_drop_column_sql( table_name, col_name ):
+ model = get_model_from_table_name(table_name)
output = []
- 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(col_name) )
- return '\n'.join(output)
+ output.append( '-- FYI: sqlite does not support deleting columns, so we
create a new '+ quote_name(col_name) +' and delete the old (ie, this could
take a while)' )
+ tmp_table_name = table_name + '_1337_TMP' # unlikely to produce a
namespace conflict
+ output.append( get_change_table_name_sql( tmp_table_name, table_name ) )
+ output.extend( management._get_sql_model_create(model, set())[0] )
+ new_cols = []
+ for f in model._meta.fields:
+ new_cols.append( quote_name(f.column) )
+ output.append( 'INSERT INTO '+ quote_name(table_name) +' SELECT '+
','.join(new_cols) +' FROM '+ quote_name(tmp_table_name) +';' )
+ output.append( 'DROP TABLE '+ quote_name(tmp_table_name) +';' )
+ return output
+
+def get_model_from_table_name(table_name):
+ from django.db import models
+ for app in models.get_apps():
+ app_name = app.__name__.split('.')[-2]
+ if app_name == table_name.split('_')[0] or app_name ==
'_'.join(table_name.split('_')[0:1]) or app_name ==
'_'.join(table_name.split('_')[0:2]):
+ for model in models.get_models(app):
+ if model._meta.db_table == table_name:
+ return model
+ return None
# SQLite requires LIKE statements to include an ESCAPE clause if the value
Modified:
django/branches/schema-evolution/django/db/backends/sqlite3/introspection.py
===================================================================
---
django/branches/schema-evolution/django/db/backends/sqlite3/introspection.py
2007-08-01 17:51:59 UTC (rev 5784)
+++
django/branches/schema-evolution/django/db/backends/sqlite3/introspection.py
2007-08-02 03:28:52 UTC (rev 5785)
@@ -53,17 +53,38 @@
def get_known_column_flags( cursor, table_name, column_name ):
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
if row[2][0:7]=='varchar':
dict['maxlength'] = row[2][8:len(row[2])-1]
# default flag check goes here
- #if row[2]=='YES': dict['allow_null'] = True
- #else: dict['allow_null'] = False
+ dict['allow_null'] = row[3]==0
+ # 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
#else: dict['primary_key'] = False
@@ -72,12 +93,8 @@
#if row[3]=='UNI': dict['unique'] = True
#else: dict['unique'] = False
- # default value check goes here
- # if row[4]=='NULL': dict['default'] = None
- # else: dict['default'] = row[4]
- #dict['default'] = row[4]
-
- print table_name, column_name, dict
+
+# print dict
return dict
def _table_info(cursor, name):
Modified: django/branches/schema-evolution/django/test/simple.py
===================================================================
--- django/branches/schema-evolution/django/test/simple.py 2007-08-01
17:51:59 UTC (rev 5784)
+++ django/branches/schema-evolution/django/test/simple.py 2007-08-02
03:28:52 UTC (rev 5785)
@@ -98,4 +98,4 @@
teardown_test_environment()
return len(result.failures) + len(result.errors)
-
\ No newline at end of file
+
Modified:
django/branches/schema-evolution/tests/modeltests/schema_evolution/models.py
===================================================================
---
django/branches/schema-evolution/tests/modeltests/schema_evolution/models.py
2007-08-01 17:51:59 UTC (rev 5784)
+++
django/branches/schema-evolution/tests/modeltests/schema_evolution/models.py
2007-08-02 03:28:52 UTC (rev 5785)
@@ -141,3 +141,69 @@
"""
+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;']
+
+"""
+
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
