root / env / lib / python2.7 / site-packages / south / db / sql_server / pyodbc.py @ d1a4905f
History | View | Annotate | Download (18.5 KB)
1 |
from datetime import date, datetime, time |
---|---|
2 |
from warnings import warn |
3 |
from django.db import models |
4 |
from django.db.models import fields |
5 |
from south.db import generic |
6 |
from south.db.generic import delete_column_constraints, invalidate_table_constraints, copy_column_constraints |
7 |
from south.exceptions import ConstraintDropped |
8 |
from django.utils.encoding import smart_unicode |
9 |
from django.core.management.color import no_style |
10 |
|
11 |
class DatabaseOperations(generic.DatabaseOperations): |
12 |
"""
|
13 |
django-pyodbc (sql_server.pyodbc) implementation of database operations.
|
14 |
"""
|
15 |
|
16 |
backend_name = "pyodbc"
|
17 |
|
18 |
add_column_string = 'ALTER TABLE %s ADD %s;'
|
19 |
alter_string_set_type = 'ALTER COLUMN %(column)s %(type)s'
|
20 |
alter_string_set_null = 'ALTER COLUMN %(column)s %(type)s NULL'
|
21 |
alter_string_drop_null = 'ALTER COLUMN %(column)s %(type)s NOT NULL'
|
22 |
|
23 |
allows_combined_alters = False
|
24 |
|
25 |
drop_index_string = 'DROP INDEX %(index_name)s ON %(table_name)s'
|
26 |
drop_constraint_string = 'ALTER TABLE %(table_name)s DROP CONSTRAINT %(constraint_name)s'
|
27 |
delete_column_string = 'ALTER TABLE %s DROP COLUMN %s'
|
28 |
|
29 |
#create_check_constraint_sql = "ALTER TABLE %(table)s " + \
|
30 |
# generic.DatabaseOperations.add_check_constraint_fragment
|
31 |
create_foreign_key_sql = "ALTER TABLE %(table)s ADD CONSTRAINT %(constraint)s " + \
|
32 |
"FOREIGN KEY (%(column)s) REFERENCES %(target)s"
|
33 |
create_unique_sql = "ALTER TABLE %(table)s ADD CONSTRAINT %(constraint)s UNIQUE (%(columns)s)"
|
34 |
|
35 |
|
36 |
default_schema_name = "dbo"
|
37 |
|
38 |
has_booleans = False
|
39 |
|
40 |
|
41 |
@delete_column_constraints
|
42 |
def delete_column(self, table_name, name): |
43 |
q_table_name, q_name = (self.quote_name(table_name), self.quote_name(name)) |
44 |
|
45 |
# Zap the indexes
|
46 |
for ind in self._find_indexes_for_column(table_name,name): |
47 |
params = {'table_name':q_table_name, 'index_name': ind} |
48 |
sql = self.drop_index_string % params
|
49 |
self.execute(sql, [])
|
50 |
|
51 |
# Zap the constraints
|
52 |
for const in self._find_constraints_for_column(table_name,name): |
53 |
params = {'table_name':q_table_name, 'constraint_name': const} |
54 |
sql = self.drop_constraint_string % params
|
55 |
self.execute(sql, [])
|
56 |
|
57 |
# Zap default if exists
|
58 |
drop_default = self.drop_column_default_sql(table_name, name)
|
59 |
if drop_default:
|
60 |
sql = "ALTER TABLE [%s] %s" % (table_name, drop_default)
|
61 |
self.execute(sql, [])
|
62 |
|
63 |
# Finally zap the column itself
|
64 |
self.execute(self.delete_column_string % (q_table_name, q_name), []) |
65 |
|
66 |
def _find_indexes_for_column(self, table_name, name): |
67 |
"Find the indexes that apply to a column, needed when deleting"
|
68 |
|
69 |
sql = """
|
70 |
SELECT si.name, si.id, sik.colid, sc.name
|
71 |
FROM dbo.sysindexes SI WITH (NOLOCK)
|
72 |
INNER JOIN dbo.sysindexkeys SIK WITH (NOLOCK)
|
73 |
ON SIK.id = Si.id
|
74 |
AND SIK.indid = SI.indid
|
75 |
INNER JOIN dbo.syscolumns SC WITH (NOLOCK)
|
76 |
ON SI.id = SC.id
|
77 |
AND SIK.colid = SC.colid
|
78 |
WHERE SI.indid !=0
|
79 |
AND Si.id = OBJECT_ID('%s')
|
80 |
AND SC.name = '%s'
|
81 |
"""
|
82 |
idx = self.execute(sql % (table_name, name), [])
|
83 |
return [i[0] for i in idx] |
84 |
|
85 |
|
86 |
def _find_constraints_for_column(self, table_name, name, just_names=True): |
87 |
"""
|
88 |
Find the constraints that apply to a column, needed when deleting. Defaults not included.
|
89 |
This is more general than the parent _constraints_affecting_columns, as on MSSQL this
|
90 |
includes PK and FK constraints.
|
91 |
"""
|
92 |
|
93 |
sql = """
|
94 |
SELECT CC.[CONSTRAINT_NAME]
|
95 |
,TC.[CONSTRAINT_TYPE]
|
96 |
,CHK.[CHECK_CLAUSE]
|
97 |
,RFD.TABLE_SCHEMA
|
98 |
,RFD.TABLE_NAME
|
99 |
,RFD.COLUMN_NAME
|
100 |
-- used for normalized names
|
101 |
,CC.TABLE_NAME
|
102 |
,CC.COLUMN_NAME
|
103 |
FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] TC
|
104 |
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
|
105 |
ON TC.CONSTRAINT_CATALOG = CC.CONSTRAINT_CATALOG
|
106 |
AND TC.CONSTRAINT_SCHEMA = CC.CONSTRAINT_SCHEMA
|
107 |
AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
|
108 |
LEFT JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS CHK
|
109 |
ON CHK.CONSTRAINT_CATALOG = CC.CONSTRAINT_CATALOG
|
110 |
AND CHK.CONSTRAINT_SCHEMA = CC.CONSTRAINT_SCHEMA
|
111 |
AND CHK.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
|
112 |
AND 'CHECK' = TC.CONSTRAINT_TYPE
|
113 |
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF
|
114 |
ON REF.CONSTRAINT_CATALOG = CC.CONSTRAINT_CATALOG
|
115 |
AND REF.CONSTRAINT_SCHEMA = CC.CONSTRAINT_SCHEMA
|
116 |
AND REF.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
|
117 |
AND 'FOREIGN KEY' = TC.CONSTRAINT_TYPE
|
118 |
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE RFD
|
119 |
ON RFD.CONSTRAINT_CATALOG = REF.UNIQUE_CONSTRAINT_CATALOG
|
120 |
AND RFD.CONSTRAINT_SCHEMA = REF.UNIQUE_CONSTRAINT_SCHEMA
|
121 |
AND RFD.CONSTRAINT_NAME = REF.UNIQUE_CONSTRAINT_NAME
|
122 |
WHERE CC.CONSTRAINT_CATALOG = CC.TABLE_CATALOG
|
123 |
AND CC.CONSTRAINT_SCHEMA = CC.TABLE_SCHEMA
|
124 |
AND CC.TABLE_CATALOG = %s
|
125 |
AND CC.TABLE_SCHEMA = %s
|
126 |
AND CC.TABLE_NAME = %s
|
127 |
AND CC.COLUMN_NAME = %s
|
128 |
"""
|
129 |
db_name = self._get_setting('name') |
130 |
schema_name = self._get_schema_name()
|
131 |
table = self.execute(sql, [db_name, schema_name, table_name, name])
|
132 |
|
133 |
if just_names:
|
134 |
return [r[0] for r in table] |
135 |
|
136 |
all = {} |
137 |
for r in table: |
138 |
cons_name, type = r[:2]
|
139 |
if type=='PRIMARY KEY' or type=='UNIQUE': |
140 |
cons = all.setdefault(cons_name, (type,[])) |
141 |
cons[1].append(r[7]) |
142 |
elif type=='CHECK': |
143 |
cons = (type, r[2]) |
144 |
elif type=='FOREIGN KEY': |
145 |
if cons_name in all: |
146 |
raise NotImplementedError("Multiple-column foreign keys are not supported") |
147 |
else:
|
148 |
cons = (type, r[3:6]) |
149 |
else:
|
150 |
raise NotImplementedError("Don't know how to handle constraints of type "+ type) |
151 |
all[cons_name] = cons
|
152 |
return all |
153 |
|
154 |
@invalidate_table_constraints
|
155 |
def alter_column(self, table_name, name, field, explicit_name=True, ignore_constraints=False): |
156 |
"""
|
157 |
Alters the given column name so it will match the given field.
|
158 |
Note that conversion between the two by the database must be possible.
|
159 |
Will not automatically add _id by default; to have this behavour, pass
|
160 |
explicit_name=False.
|
161 |
|
162 |
@param table_name: The name of the table to add the column to
|
163 |
@param name: The name of the column to alter
|
164 |
@param field: The new field definition to use
|
165 |
"""
|
166 |
self._fix_field_definition(field)
|
167 |
|
168 |
if not ignore_constraints: |
169 |
qn = self.quote_name
|
170 |
sch = qn(self._get_schema_name())
|
171 |
tab = qn(table_name) |
172 |
table = ".".join([sch, tab])
|
173 |
try:
|
174 |
self.delete_foreign_key(table_name, name)
|
175 |
except ValueError: |
176 |
# no FK constraint on this field. That's OK.
|
177 |
pass
|
178 |
constraints = self._find_constraints_for_column(table_name, name, False) |
179 |
for constraint in constraints.keys(): |
180 |
params = dict(table_name = table,
|
181 |
constraint_name = qn(constraint)) |
182 |
sql = self.drop_constraint_string % params
|
183 |
self.execute(sql, [])
|
184 |
|
185 |
ret_val = super(DatabaseOperations, self).alter_column(table_name, name, field, explicit_name, ignore_constraints=True) |
186 |
|
187 |
if not ignore_constraints: |
188 |
for cname, (ctype,args) in constraints.items(): |
189 |
params = dict(table = table,
|
190 |
constraint = qn(cname)) |
191 |
if ctype=='UNIQUE': |
192 |
params['columns'] = ", ".join(map(qn,args)) |
193 |
sql = self.create_unique_sql % params
|
194 |
elif ctype=='PRIMARY KEY': |
195 |
params['columns'] = ", ".join(map(qn,args)) |
196 |
sql = self.create_primary_key_string % params
|
197 |
elif ctype=='FOREIGN KEY': |
198 |
continue
|
199 |
# Foreign keys taken care of below
|
200 |
#target = "%s.%s(%s)" % tuple(map(qn,args))
|
201 |
#params.update(column = qn(name), target = target)
|
202 |
#sql = self.create_foreign_key_sql % params
|
203 |
elif ctype=='CHECK': |
204 |
warn(ConstraintDropped("CHECK "+ args, table_name, name))
|
205 |
continue
|
206 |
#TODO: Some check constraints should be restored; but not before the generic
|
207 |
# backend restores them.
|
208 |
#params['check'] = args
|
209 |
#sql = self.create_check_constraint_sql % params
|
210 |
else:
|
211 |
raise NotImplementedError("Don't know how to handle constraints of type "+ type) |
212 |
self.execute(sql, [])
|
213 |
# Create foreign key if necessary
|
214 |
if field.rel and self.supports_foreign_keys: |
215 |
self.execute(
|
216 |
self.foreign_key_sql(
|
217 |
table_name, |
218 |
field.column, |
219 |
field.rel.to._meta.db_table, |
220 |
field.rel.to._meta.get_field(field.rel.field_name).column |
221 |
) |
222 |
) |
223 |
model = self.mock_model("FakeModelForIndexCreation", table_name) |
224 |
for stmt in self._get_connection().creation.sql_indexes_for_field(model, field, no_style()): |
225 |
self.execute(stmt)
|
226 |
|
227 |
|
228 |
return ret_val
|
229 |
|
230 |
def _alter_set_defaults(self, field, name, params, sqls): |
231 |
"Subcommand of alter_column that sets default values (overrideable)"
|
232 |
# First drop the current default if one exists
|
233 |
table_name = self.quote_name(params['table_name']) |
234 |
drop_default = self.drop_column_default_sql(table_name, name)
|
235 |
if drop_default:
|
236 |
sqls.append((drop_default, [])) |
237 |
|
238 |
# Next, set any default
|
239 |
|
240 |
if field.has_default():
|
241 |
default = field.get_default() |
242 |
literal = self._value_to_unquoted_literal(field, default)
|
243 |
sqls.append(('ADD DEFAULT %s for %s' % (self._quote_string(literal), self.quote_name(name),), [])) |
244 |
|
245 |
def _value_to_unquoted_literal(self, field, value): |
246 |
# Start with the field's own translation
|
247 |
conn = self._get_connection()
|
248 |
value = field.get_db_prep_save(value, connection=conn) |
249 |
# This is still a Python object -- nobody expects to need a literal.
|
250 |
if isinstance(value, basestring): |
251 |
return smart_unicode(value)
|
252 |
elif isinstance(value, (date,time,datetime)): |
253 |
return value.isoformat()
|
254 |
else:
|
255 |
#TODO: Anybody else needs special translations?
|
256 |
return str(value) |
257 |
def _default_value_workaround(self, value): |
258 |
if isinstance(value, (date,time,datetime)): |
259 |
return value.isoformat()
|
260 |
else:
|
261 |
return super(DatabaseOperations, self)._default_value_workaround(value) |
262 |
|
263 |
def _quote_string(self, s): |
264 |
return "'" + s.replace("'","''") + "'" |
265 |
|
266 |
|
267 |
def drop_column_default_sql(self, table_name, name, q_name=None): |
268 |
"MSSQL specific drop default, which is a pain"
|
269 |
|
270 |
sql = """
|
271 |
SELECT object_name(cdefault)
|
272 |
FROM syscolumns
|
273 |
WHERE id = object_id('%s')
|
274 |
AND name = '%s'
|
275 |
"""
|
276 |
cons = self.execute(sql % (table_name, name), [])
|
277 |
if cons and cons[0] and cons[0][0]: |
278 |
return "DROP CONSTRAINT %s" % cons[0][0] |
279 |
return None |
280 |
|
281 |
def _fix_field_definition(self, field): |
282 |
if isinstance(field, (fields.BooleanField, fields.NullBooleanField)): |
283 |
if field.default == True: |
284 |
field.default = 1
|
285 |
if field.default == False: |
286 |
field.default = 0
|
287 |
|
288 |
# This is copied from South's generic add_column, with two modifications:
|
289 |
# 1) The sql-server-specific call to _fix_field_definition
|
290 |
# 2) Removing a default, when needed, by calling drop_default and not the more general alter_column
|
291 |
@invalidate_table_constraints
|
292 |
def add_column(self, table_name, name, field, keep_default=True): |
293 |
"""
|
294 |
Adds the column 'name' to the table 'table_name'.
|
295 |
Uses the 'field' paramater, a django.db.models.fields.Field instance,
|
296 |
to generate the necessary sql
|
297 |
|
298 |
@param table_name: The name of the table to add the column to
|
299 |
@param name: The name of the column to add
|
300 |
@param field: The field to use
|
301 |
"""
|
302 |
self._fix_field_definition(field)
|
303 |
sql = self.column_sql(table_name, name, field)
|
304 |
if sql:
|
305 |
params = ( |
306 |
self.quote_name(table_name),
|
307 |
sql, |
308 |
) |
309 |
sql = self.add_column_string % params
|
310 |
self.execute(sql)
|
311 |
|
312 |
# Now, drop the default if we need to
|
313 |
if not keep_default and field.default is not None: |
314 |
field.default = fields.NOT_PROVIDED |
315 |
#self.alter_column(table_name, name, field, explicit_name=False, ignore_constraints=True)
|
316 |
self.drop_default(table_name, name, field)
|
317 |
|
318 |
@invalidate_table_constraints
|
319 |
def drop_default(self, table_name, name, field): |
320 |
fragment = self.drop_column_default_sql(table_name, name)
|
321 |
if fragment:
|
322 |
table_name = self.quote_name(table_name)
|
323 |
sql = " ".join(["ALTER TABLE", table_name, fragment]) |
324 |
self.execute(sql)
|
325 |
|
326 |
|
327 |
@invalidate_table_constraints
|
328 |
def create_table(self, table_name, field_defs): |
329 |
# Tweak stuff as needed
|
330 |
for _, f in field_defs: |
331 |
self._fix_field_definition(f)
|
332 |
|
333 |
# Run
|
334 |
generic.DatabaseOperations.create_table(self, table_name, field_defs)
|
335 |
|
336 |
def _find_referencing_fks(self, table_name): |
337 |
"MSSQL does not support cascading FKs when dropping tables, we need to implement."
|
338 |
|
339 |
# FK -- Foreign Keys
|
340 |
# UCTU -- Unique Constraints Table Usage
|
341 |
# FKTU -- Foreign Key Table Usage
|
342 |
# (last two are both really CONSTRAINT_TABLE_USAGE, different join conditions)
|
343 |
sql = """
|
344 |
SELECT FKTU.TABLE_SCHEMA as REFING_TABLE_SCHEMA,
|
345 |
FKTU.TABLE_NAME as REFING_TABLE_NAME,
|
346 |
FK.[CONSTRAINT_NAME] as FK_NAME
|
347 |
FROM [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] FK
|
348 |
JOIN [INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE] UCTU
|
349 |
ON FK.UNIQUE_CONSTRAINT_CATALOG = UCTU.CONSTRAINT_CATALOG and
|
350 |
FK.UNIQUE_CONSTRAINT_NAME = UCTU.CONSTRAINT_NAME and
|
351 |
FK.UNIQUE_CONSTRAINT_SCHEMA = UCTU.CONSTRAINT_SCHEMA
|
352 |
JOIN [INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE] FKTU
|
353 |
ON FK.CONSTRAINT_CATALOG = FKTU.CONSTRAINT_CATALOG and
|
354 |
FK.CONSTRAINT_NAME = FKTU.CONSTRAINT_NAME and
|
355 |
FK.CONSTRAINT_SCHEMA = FKTU.CONSTRAINT_SCHEMA
|
356 |
WHERE FK.CONSTRAINT_CATALOG = %s
|
357 |
AND UCTU.TABLE_SCHEMA = %s -- REFD_TABLE_SCHEMA
|
358 |
AND UCTU.TABLE_NAME = %s -- REFD_TABLE_NAME
|
359 |
"""
|
360 |
db_name = self._get_setting('name') |
361 |
schema_name = self._get_schema_name()
|
362 |
return self.execute(sql, [db_name, schema_name, table_name]) |
363 |
|
364 |
@invalidate_table_constraints
|
365 |
def delete_table(self, table_name, cascade=True): |
366 |
"""
|
367 |
Deletes the table 'table_name'.
|
368 |
"""
|
369 |
if cascade:
|
370 |
refing = self._find_referencing_fks(table_name)
|
371 |
for schmea, table, constraint in refing: |
372 |
table = ".".join(map (self.quote_name, [schmea, table])) |
373 |
params = dict(table_name = table,
|
374 |
constraint_name = self.quote_name(constraint))
|
375 |
sql = self.drop_constraint_string % params
|
376 |
self.execute(sql, [])
|
377 |
cascade = False
|
378 |
super(DatabaseOperations, self).delete_table(table_name, cascade) |
379 |
|
380 |
@copy_column_constraints
|
381 |
@delete_column_constraints
|
382 |
def rename_column(self, table_name, old, new): |
383 |
"""
|
384 |
Renames the column of 'table_name' from 'old' to 'new'.
|
385 |
WARNING - This isn't transactional on MSSQL!
|
386 |
"""
|
387 |
if old == new:
|
388 |
# No Operation
|
389 |
return
|
390 |
# Examples on the MS site show the table name not being quoted...
|
391 |
params = (table_name, self.quote_name(old), self.quote_name(new)) |
392 |
self.execute("EXEC sp_rename '%s.%s', %s, 'COLUMN'" % params) |
393 |
|
394 |
@invalidate_table_constraints
|
395 |
def rename_table(self, old_table_name, table_name): |
396 |
"""
|
397 |
Renames the table 'old_table_name' to 'table_name'.
|
398 |
WARNING - This isn't transactional on MSSQL!
|
399 |
"""
|
400 |
if old_table_name == table_name:
|
401 |
# No Operation
|
402 |
return
|
403 |
params = (self.quote_name(old_table_name), self.quote_name(table_name)) |
404 |
self.execute('EXEC sp_rename %s, %s' % params) |
405 |
|
406 |
def _db_type_for_alter_column(self, field): |
407 |
return self._db_positive_type_for_alter_column(DatabaseOperations, field) |
408 |
|
409 |
def _alter_add_column_mods(self, field, name, params, sqls): |
410 |
return self._alter_add_positive_check(DatabaseOperations, field, name, params, sqls) |
411 |
|
412 |
@invalidate_table_constraints
|
413 |
def delete_foreign_key(self, table_name, column): |
414 |
super(DatabaseOperations, self).delete_foreign_key(table_name, column) |
415 |
# A FK also implies a non-unique index
|
416 |
find_index_sql = """
|
417 |
SELECT i.name -- s.name, t.name, c.name
|
418 |
FROM sys.tables t
|
419 |
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
|
420 |
INNER JOIN sys.indexes i ON i.object_id = t.object_id
|
421 |
INNER JOIN sys.index_columns ic ON ic.object_id = t.object_id
|
422 |
INNER JOIN sys.columns c ON c.object_id = t.object_id
|
423 |
AND ic.column_id = c.column_id
|
424 |
WHERE i.is_unique=0 AND i.is_primary_key=0 AND i.is_unique_constraint=0
|
425 |
AND s.name = %s
|
426 |
AND t.name = %s
|
427 |
AND c.name = %s
|
428 |
"""
|
429 |
schema = self._get_schema_name()
|
430 |
indexes = self.execute(find_index_sql, [schema, table_name, column])
|
431 |
qn = self.quote_name
|
432 |
for index in (i[0] for i in indexes if i[0]): # "if i[0]" added because an empty name may return |
433 |
self.execute("DROP INDEX %s on %s.%s" % (qn(index), qn(schema), qn(table_name) )) |
434 |
|