def get_add_column_sql(self, table, column):
# versions >= sqlite 3.2.0, see http://www.sqlite.org/lang_altertable.html
table_name = table.name
col_name = column.name
sql = SQL()
qn = self.connection.ops.quote_name
kw = self.style.SQL_KEYWORD
fct = self.style.SQL_COLTYPE
tqn = lambda s: self.style.SQL_TABLE(qn(s))
fqn = lambda s: self.style.SQL_FIELD(qn(s))
fqv = lambda s: self.style.SQL_FIELD(self.quote_value(s))
sql.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' ADD COLUMN ') + fqn(col_name) + ' ' + fct(column.dbtype) + ';')
if column.primary_key:
raise RebuildTableNeededException("we'd better rebuild all table instead of updating primary_key field")
if column.default is NotProvided and not column.allow_null:
details = 'column "%s" into table "%s"' % (col_name, table_name)
raise NotNullColumnNeedsDefaultException("when added " + details)
if not column.default is NotProvided and not column.allow_null:
sql.append(
kw('UPDATE ') + tqn(table_name) +
kw(' SET ') + fqn(col_name) + ' = ' + fqv(column.default) +
kw(' WHERE ') + fqn(col_name) + kw(' IS NULL;'))
if not column.allow_null:
sql.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' ALTER COLUMN ') + fqn(col_name) +
kw(' SET NOT NULL;'))
if column.unique:
sql.append(
kw('ALTER TABLE ') + tqn(table_name) + kw(' ADD CONSTRAINT ') +
table_name + '_' + col_name + '_unique_constraint'+
kw(' UNIQUE(') + fqn(col_name) + kw(')')+';')
return sql
def do_rename(self, change):
try:
sql = SQL()
sql.extend(self.ops.get_change_table_name_sql(change.left, change.right))
if change.nested:
commands = [(change, sql)]
commands.extend(self.do_try_update(change))
return commands
except RebuildTableNeededException:
return self.do_rebuild(change)
return sql
def get_create_index_sql(self, table, index):
"Returns the CREATE INDEX SQL statement for a single field"
from django.db import connection
qn = self.connection.ops.quote_name
kw = self.style.SQL_KEYWORD
output = SQL()
if not (index.pk or index.unique):
unique = index.unique and 'UNIQUE ' or ''
output.append(
kw('CREATE %sINDEX' % unique) + ' ' +
self.style.SQL_TABLE(qn('%s_%s' % (table.name, index.name))) + ' ' +
kw('ON') + ' ' +
self.style.SQL_TABLE(qn(table.name)) + ' ' +
"(%s)" % self.style.SQL_FIELD(qn(index.name)) +
";"
)
return output
def get_change_column_def_sql(self, table, left, right, updates):
table_name = table.name
col_name = right.name
qn = self.connection.ops.quote_name
kw = self.style.SQL_KEYWORD
fct = self.style.SQL_COLTYPE
tqn = lambda s: self.style.SQL_TABLE(qn(s))
fqn = lambda s: self.style.SQL_FIELD(qn(s))
fqv = lambda s: self.style.SQL_FIELD(self.quote_value(s))
sql = SQL()
if 'primary_key' in updates and right.coltype.attrs.get('auto_increment',False):
raise RebuildTableNeededException("mysql can't add auto_increment field")
if 'null' in updates or 'coltype' in updates:
if left.allow_null and not right.allow_null:
if right.default is NotProvided:
details = 'column "%s" of table "%s"' % (col_name, table_name)
raise NotNullColumnNeedsDefaultException("when modified " + details)
sql.append(
kw('UPDATE ') + tqn(table_name) +
kw(' SET ') + fqn(col_name) +
kw(' = ') + fqv(right.default) +
kw(' WHERE ') + fqn(col_name) + kw(' IS NULL;'))
col_def = fct(right.dbtype)
if not right.primary_key:
col_def += ' ' + kw(not right.allow_null and 'NOT NULL' or 'NULL')
if right.unique:
col_def += ' ' + kw('UNIQUE')
#if right.primary_key:
# col_def += ' ' + kw('PRIMARY KEY')
sql.append(
kw('ALTER TABLE ')+ tqn(table_name) +
kw(' MODIFY COLUMN ')+ fqn(col_name) + ' '+ col_def + ';')
return sql
def get_add_column_sql(self, table, column):
# versions >= sqlite 3.2.0, see http://www.sqlite.org/lang_altertable.html
table_name = table.name
col_name = column.name
default = column.default
sql = SQL()
qn = self.connection.ops.quote_name
kw = self.style.SQL_KEYWORD
fct = self.style.SQL_COLTYPE
tqn = lambda s: self.style.SQL_TABLE(qn(s))
fqn = lambda s: self.style.SQL_FIELD(qn(s))
fqv = lambda s: self.style.SQL_FIELD(self.quote_value(s))
field_output = []
field_output.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' ADD COLUMN ') + fqn(column.name) + ' ' + fct(column.dbtype))
if column.unique:
field_output.append(kw('UNIQUE'))
if column.primary_key and column.coltype.attrs.get('auto_increment',False):
raise RebuildTableNeededException("mysql can't add auto_increment field")
if column.primary_key:
field_output.append(kw('PRIMARY KEY'))
sql.append(' '.join(field_output) + ';')
if column.primary_key: return sql
#if default is NotProvided and not column.allow_null:
# details = 'column "%s" into table "%s"' % (col_name, table_name)
# raise NotNullColumnNeedsDefaultException("when added " + details)
if not default is NotProvided and not column.allow_null:
sql.append(
kw('UPDATE ') + tqn(table_name) +
kw(' SET ') + fqn(col_name) + ' = ' + fqv(default) +
kw(' WHERE ') + fqn(col_name) + kw(' IS NULL;'))
if not column.allow_null:
col_def = fct(column.dbtype) + kw(not column.allow_null and ' NOT NULL' or '')
#if column.unique:
# col_def += ' '+ kw('UNIQUE')
#if primary_key:
# col_def += ' '+ kw('PRIMARY KEY')
sql.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' MODIFY COLUMN ') + fqn(col_name) + ' '+
kw(col_def+';'))
#if column.unique:
# sql.append(
# kw('ALTER TABLE ') + tqn(table_name) + kw(' ADD CONSTRAINT ') +
# table_name + '_' + col_name + '_unique_constraint'+
# kw(' UNIQUE(') + fqn(col_name) + kw(')')+';')
return sql
def get_create_table_sql(self, table):
#known_models = {}
style = self.style
qn = self.connection.ops.quote_name
table_output = []
#pending_references = {}
for f in table.fields.values():
col_type = f.coltype.dbtype
tablespace = None # f.db_tablespace or table.db_tablespace
if col_type is None:
# Skip ManyToManyFields, because they're not represented as
# database columns in this table.
continue
# Make the definition (e.g. 'foo VARCHAR(30)') for this field.
field_output = [style.SQL_FIELD(qn(f.name)),
style.SQL_COLTYPE(col_type)]
field_output.append(style.SQL_KEYWORD('%sNULL' % (not f.allow_null and 'NOT ' or '')))
if f.primary_key:
field_output.append(style.SQL_KEYWORD('PRIMARY KEY'))
elif f.unique:
field_output.append(style.SQL_KEYWORD('UNIQUE'))
if tablespace and f.unique:
# We must specify the index tablespace inline, because we
# won't be generating a CREATE INDEX statement for this field.
field_output.append(self.connection.ops.tablespace_sql(tablespace, inline=True))
#ForeignKey support
#if f.rel:
# ref_output, pending = self.sql_for_inline_foreign_key_references(f, known_models, style)
# if pending:
# pass#pr = pending_references.setdefault(f.rel.to, []).append((model, f))
# else:
# field_output.extend(ref_output)
table_output.append(' '.join(field_output))
#if opts.order_with_respect_to:
# table_output.append(style.SQL_FIELD(qn('_order')) + ' ' + \
# style.SQL_COLTYPE(models.IntegerField().db_type()) + ' ' + \
# style.SQL_KEYWORD('NULL'))
#for field_constraints in opts.unique_together:
# table_output.append(style.SQL_KEYWORD('UNIQUE') + ' (%s)' % \
# ", ".join([style.SQL_FIELD(qn(opts.get_field(f).column)) for f in field_constraints]))
full_statement = [style.SQL_KEYWORD('CREATE TABLE') + ' ' + style.SQL_TABLE(qn(table.name)) + ' (']
final_output = SQL()
for i, line in enumerate(table_output): # Combine and add commas.
full_statement.append(' %s%s' % (line, i < len(table_output)-1 and ',' or ''))
full_statement.append(')')
#if table.db_tablespace:
# full_statement.append(self.connection.ops.tablespace_sql(opts.db_tablespace))
full_statement.append(';')
final_output.append('\n'.join(full_statement))
#currently for Oracle only
#if opts.has_auto_field:
# # Add any extra SQL needed to support auto-incrementing primary keys.
# auto_column = opts.auto_field.db_column or opts.auto_field.name
# autoinc_sql = self.connection.ops.autoinc_sql(opts.db_table, auto_column)
# if autoinc_sql:
# for stmt in autoinc_sql:
# final_output.append(stmt)
return final_output
def get_rebuild_table_sql(self, left, right, renames):
"""
Renames: right => left
"""
old_names = left.fields.keys()
#print "Renames:", renames
# used instead of column renames, additions and removals
qn = self.connection.ops.quote_name
kw = self.style.SQL_KEYWORD
fld = self.style.SQL_FIELD
tqn = lambda s: self.style.SQL_TABLE(qn(s))
fqn = lambda s: self.style.SQL_FIELD(qn(s))
fqv = lambda s: self.style.SQL_FIELD(self.quote_value(s))
sql = SQL('-- FYI: next few lines could take a while if you have a lot of data')
tmp_table_name = right.name + '_1337_TMP' # unlikely to produce a namespace conflict
temp = DBTable(tmp_table_name)
sql.extend(self.get_change_table_name_sql(left, temp))
sql.extend(self.get_create_table_sql(right))
updated = []
for f in right.fields.values():
if f.name in renames:
updated.append(fqn(renames[f.name])) # copy renamed column
elif f.name in old_names:
updated.append(fqn(f.name)) # copy column
else:
default = NotProvided
if f.allow_null:
default = None
elif f.primary_key:
default = '0'
elif f.coltype.coltype in ['int', 'bool', 'tinyint']:
default = '0'
elif f.coltype.coltype in ['varchar', 'text']:
default = ''
default = get_field_default(f, default) # add column with default value set
if default is None:
updated.append('NULL')
else:
updated.append(fqv(default))
sql.append(kw('INSERT INTO ') +
tqn(right.name) +
kw(' SELECT ') +
fld(','.join(updated)) +
kw(' FROM ') +
tqn(tmp_table_name) +';')
sql.extend(self.get_drop_table_sql(temp))
sql.extend(self.get_create_table_indexes_sql(right))
return sql
def get_create_table_indexes_sql(self, table):
sql = SQL()
for index in table.indexes.values():
sql.extend(self.get_create_index_sql(table, index))
return sql
def get_change_column_def_sql(self, table, left, right, updates):
table_name = table.name
col_name = right.name
sql = SQL()
qn = self.connection.ops.quote_name
kw = self.style.SQL_KEYWORD
fct = self.style.SQL_COLTYPE
tqn = lambda s: self.style.SQL_TABLE(qn(s))
fqn = lambda s: self.style.SQL_FIELD(qn(s))
fqv = lambda s: self.style.SQL_FIELD(self.quote_value(s))
#print table_name, ":\n%s ->\n%s" % (left.traits, right.traits)
if 'primary_key' in updates:
raise RebuildTableNeededException("we'd rebuild whole table instead of updating primary key field")
if 'coltype' in updates:
null = ' NULL'
if not right.allow_null:
null = ' NOT NULL'
unique = ""
if right.unique:
unique = " UNIQUE"
sql.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' ADD COLUMN ') + fqn(col_name+'_tmp_1337') + ' ' + fct(right.dbtype) + unique + null +';')
sql.append(
kw('UPDATE ') + tqn(table_name) +
kw(' SET ') + fqn(col_name+'_tmp_1337') +
' = ' + fqn(col_name) + ';')
sql.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' DROP COLUMN ') + fqn(col_name) + ';')
sql.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' RENAME COLUMN ') + fqn(col_name+'_tmp_1337') +
kw(' TO ') + fqn(col_name) + ';')
elif 'max_length' in updates:
sql.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' ALTER COLUMN ') + fqn(col_name) +
kw(' TYPE ')+ fct(right.dbtype) + ';')
if 'sequence' in updates:
seq_name = left.sequence
seq_name_correct = right.sequence
if seq_name != seq_name_correct:
sql.append(
kw('ALTER TABLE ') + tqn(seq_name) +
kw(' RENAME TO ') + tqn(seq_name_correct)+';')
sql.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' ALTER COLUMN ') + tqn(col_name) +
kw(' SET DEFAULT nextval(')+
fqv(seq_name_correct)+'::regclass);')
if 'allow_null' in updates:
if right.default is NotProvided and not right.allow_null:
details = 'column "%s" of table "%s"' % (col_name, table_name)
raise NotNullColumnNeedsDefaultException("when modified " + details)
if not right.allow_null:
sql.append(
kw('UPDATE ') + tqn(table_name) +
kw(' SET ') + fqn(col_name) + ' = ' + fqv(right.default) +
kw(' WHERE ') + fqn(col_name) + kw(' IS NULL;'))
sql.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' ALTER COLUMN ') + fqn(col_name) +
kw(' SET NOT NULL;'))
elif not 'coltype' in updates:
sql.append(
kw('ALTER TABLE ') + tqn(table_name) +
kw(' ALTER COLUMN ') + fqn(col_name) +
kw(' DROP NOT NULL;'))
if 'unique' in updates and right.unique:
sql.append(kw('ALTER TABLE ') + tqn(table_name) +
kw(' ADD CONSTRAINT ') +
table_name + '_' + col_name + '_unique_constraint'+
kw(' UNIQUE(') + fqn(col_name) + kw(')')+';')
return sql
