| 1 |
from collections import namedtuple |
| 2 |
|
| 3 |
import sqlparse |
| 4 |
|
| 5 |
from django.db import DatabaseError |
| 6 |
from django.db.backends.base.introspection import BaseDatabaseIntrospection |
| 7 |
from django.db.backends.base.introspection import FieldInfo as BaseFieldInfo |
| 8 |
from django.db.backends.base.introspection import TableInfo |
| 9 |
from django.db.models import Index |
| 10 |
from django.utils.regex_helper import _lazy_re_compile |
| 11 |
|
| 12 |
FieldInfo = namedtuple( |
| 13 |
"FieldInfo", BaseFieldInfo._fields + ("pk", "has_json_constraint") |
| 14 |
) |
| 15 |
|
| 16 |
field_size_re = _lazy_re_compile(r"^\s*(?:var)?char\s*\(\s*(\d+)\s*\)\s*$") |
| 17 |
|
| 18 |
|
| 19 |
def get_field_size(name): |
| 20 |
"""Extract the size number from a "varchar(11)" type name""" |
| 21 |
m = field_size_re.search(name) |
| 22 |
return int(m[1]) if m else None |
| 23 |
|
| 24 |
|
| 25 |
# This light wrapper "fakes" a dictionary interface, because some SQLite data |
| 26 |
# types include variables in them -- e.g. "varchar(30)" -- and can't be matched |
| 27 |
# as a simple dictionary lookup. |
| 28 |
class FlexibleFieldLookupDict: |
| 29 |
# Maps SQL types to Django Field types. Some of the SQL types have multiple |
| 30 |
# entries here because SQLite allows for anything and doesn't normalize the |
| 31 |
# field type; it uses whatever was given. |
| 32 |
base_data_types_reverse = { |
| 33 |
"bool": "BooleanField", |
| 34 |
"boolean": "BooleanField", |
| 35 |
"smallint": "SmallIntegerField", |
| 36 |
"smallint unsigned": "PositiveSmallIntegerField", |
| 37 |
"smallinteger": "SmallIntegerField", |
| 38 |
"int": "IntegerField", |
| 39 |
"integer": "IntegerField", |
| 40 |
"bigint": "BigIntegerField", |
| 41 |
"integer unsigned": "PositiveIntegerField", |
| 42 |
"bigint unsigned": "PositiveBigIntegerField", |
| 43 |
"decimal": "DecimalField", |
| 44 |
"real": "FloatField", |
| 45 |
"text": "TextField", |
| 46 |
"char": "CharField", |
| 47 |
"varchar": "CharField", |
| 48 |
"blob": "BinaryField", |
| 49 |
"date": "DateField", |
| 50 |
"datetime": "DateTimeField", |
| 51 |
"time": "TimeField", |
| 52 |
} |
| 53 |
|
| 54 |
def __getitem__(self, key): |
| 55 |
key = key.lower().split("(", 1)[0].strip() |
| 56 |
return self.base_data_types_reverse[key] |
| 57 |
|
| 58 |
|
| 59 |
class DatabaseIntrospection(BaseDatabaseIntrospection): |
| 60 |
data_types_reverse = FlexibleFieldLookupDict() |
| 61 |
|
| 62 |
def get_field_type(self, data_type, description): |
| 63 |
field_type = super().get_field_type(data_type, description) |
| 64 |
if description.pk and field_type in { |
| 65 |
"BigIntegerField", |
| 66 |
"IntegerField", |
| 67 |
"SmallIntegerField", |
| 68 |
}: |
| 69 |
# No support for BigAutoField or SmallAutoField as SQLite treats |
| 70 |
# all integer primary keys as signed 64-bit integers. |
| 71 |
return "AutoField" |
| 72 |
if description.has_json_constraint: |
| 73 |
return "JSONField" |
| 74 |
return field_type |
| 75 |
|
| 76 |
def get_table_list(self, cursor): |
| 77 |
"""Return a list of table and view names in the current database.""" |
| 78 |
# Skip the sqlite_sequence system table used for autoincrement key |
| 79 |
# generation. |
| 80 |
cursor.execute(""" |
| 81 |
SELECT name, type FROM sqlite_master |
| 82 |
WHERE type in ('table', 'view') AND NOT name='sqlite_sequence' |
| 83 |
ORDER BY name""") |
| 84 |
return [TableInfo(row[0], row[1][0]) for row in cursor.fetchall()] |
| 85 |
|
| 86 |
def get_table_description(self, cursor, table_name): |
| 87 |
""" |
| 88 |
Return a description of the table with the DB-API cursor.description |
| 89 |
interface. |
| 90 |
""" |
| 91 |
cursor.execute( |
| 92 |
"PRAGMA table_xinfo(%s)" % self.connection.ops.quote_name(table_name) |
| 93 |
) |
| 94 |
table_info = cursor.fetchall() |
| 95 |
if not table_info: |
| 96 |
raise DatabaseError(f"Table {table_name} does not exist (empty pragma).") |
| 97 |
collations = self._get_column_collations(cursor, table_name) |
| 98 |
json_columns = set() |
| 99 |
if self.connection.features.can_introspect_json_field: |
| 100 |
for line in table_info: |
| 101 |
column = line[1] |
| 102 |
json_constraint_sql = '%%json_valid("%s")%%' % column |
| 103 |
has_json_constraint = cursor.execute( |
| 104 |
""" |
| 105 |
SELECT sql |
| 106 |
FROM sqlite_master |
| 107 |
WHERE |
| 108 |
type = 'table' AND |
| 109 |
name = %s AND |
| 110 |
sql LIKE %s |
| 111 |
""", |
| 112 |
[table_name, json_constraint_sql], |
| 113 |
).fetchone() |
| 114 |
if has_json_constraint: |
| 115 |
json_columns.add(column) |
| 116 |
table_description = [ |
| 117 |
FieldInfo( |
| 118 |
name, |
| 119 |
data_type, |
| 120 |
get_field_size(data_type), |
| 121 |
None, |
| 122 |
None, |
| 123 |
None, |
| 124 |
not notnull, |
| 125 |
default, |
| 126 |
collations.get(name), |
| 127 |
bool(pk), |
| 128 |
name in json_columns, |
| 129 |
) |
| 130 |
for cid, name, data_type, notnull, default, pk, hidden in table_info |
| 131 |
if hidden |
| 132 |
in [ |
| 133 |
0, # Normal column. |
| 134 |
2, # Virtual generated column. |
| 135 |
3, # Stored generated column. |
| 136 |
] |
| 137 |
] |
| 138 |
# If the primary key is composed of multiple columns they should not |
| 139 |
# be individually marked as pk. |
| 140 |
primary_key = [ |
| 141 |
index for index, field_info in enumerate(table_description) if field_info.pk |
| 142 |
] |
| 143 |
if len(primary_key) > 1: |
| 144 |
for index in primary_key: |
| 145 |
table_description[index] = table_description[index]._replace(pk=False) |
| 146 |
return table_description |
| 147 |
|
| 148 |
def get_sequences(self, cursor, table_name, table_fields=()): |
| 149 |
pk_col = self.get_primary_key_column(cursor, table_name) |
| 150 |
return [{"table": table_name, "column": pk_col}] |
| 151 |
|
| 152 |
def get_relations(self, cursor, table_name): |
| 153 |
""" |
| 154 |
Return a dictionary of {column_name: (ref_column_name, ref_table_name)} |
| 155 |
representing all foreign keys in the given table. |
| 156 |
""" |
| 157 |
cursor.execute( |
| 158 |
"PRAGMA foreign_key_list(%s)" % self.connection.ops.quote_name(table_name) |
| 159 |
) |
| 160 |
return { |
| 161 |
column_name: (ref_column_name, ref_table_name) |
| 162 |
for ( |
| 163 |
_, |
| 164 |
_, |
| 165 |
ref_table_name, |
| 166 |
column_name, |
| 167 |
ref_column_name, |
| 168 |
*_, |
| 169 |
) in cursor.fetchall() |
| 170 |
} |
| 171 |
|
| 172 |
def get_primary_key_columns(self, cursor, table_name): |
| 173 |
cursor.execute( |
| 174 |
"PRAGMA table_info(%s)" % self.connection.ops.quote_name(table_name) |
| 175 |
) |
| 176 |
return [name for _, name, *_, pk in cursor.fetchall() if pk] |
| 177 |
|
| 178 |
def _parse_column_or_constraint_definition(self, tokens, columns): |
| 179 |
token = None |
| 180 |
is_constraint_definition = None |
| 181 |
field_name = None |
| 182 |
constraint_name = None |
| 183 |
unique = False |
| 184 |
unique_columns = [] |
| 185 |
check = False |
| 186 |
check_columns = [] |
| 187 |
braces_deep = 0 |
| 188 |
for token in tokens: |
| 189 |
if token.match(sqlparse.tokens.Punctuation, "("): |
| 190 |
braces_deep += 1 |
| 191 |
elif token.match(sqlparse.tokens.Punctuation, ")"): |
| 192 |
braces_deep -= 1 |
| 193 |
if braces_deep < 0: |
| 194 |
# End of columns and constraints for table definition. |
| 195 |
break |
| 196 |
elif braces_deep == 0 and token.match(sqlparse.tokens.Punctuation, ","): |
| 197 |
# End of current column or constraint definition. |
| 198 |
break |
| 199 |
# Detect column or constraint definition by first token. |
| 200 |
if is_constraint_definition is None: |
| 201 |
is_constraint_definition = token.match( |
| 202 |
sqlparse.tokens.Keyword, "CONSTRAINT" |
| 203 |
) |
| 204 |
if is_constraint_definition: |
| 205 |
continue |
| 206 |
if is_constraint_definition: |
| 207 |
# Detect constraint name by second token. |
| 208 |
if constraint_name is None: |
| 209 |
if token.ttype in (sqlparse.tokens.Name, sqlparse.tokens.Keyword): |
| 210 |
constraint_name = token.value |
| 211 |
elif token.ttype == sqlparse.tokens.Literal.String.Symbol: |
| 212 |
constraint_name = token.value[1:-1] |
| 213 |
# Start constraint columns parsing after UNIQUE keyword. |
| 214 |
if token.match(sqlparse.tokens.Keyword, "UNIQUE"): |
| 215 |
unique = True |
| 216 |
unique_braces_deep = braces_deep |
| 217 |
elif unique: |
| 218 |
if unique_braces_deep == braces_deep: |
| 219 |
if unique_columns: |
| 220 |
# Stop constraint parsing. |
| 221 |
unique = False |
| 222 |
continue |
| 223 |
if token.ttype in (sqlparse.tokens.Name, sqlparse.tokens.Keyword): |
| 224 |
unique_columns.append(token.value) |
| 225 |
elif token.ttype == sqlparse.tokens.Literal.String.Symbol: |
| 226 |
unique_columns.append(token.value[1:-1]) |
| 227 |
else: |
| 228 |
# Detect field name by first token. |
| 229 |
if field_name is None: |
| 230 |
if token.ttype in (sqlparse.tokens.Name, sqlparse.tokens.Keyword): |
| 231 |
field_name = token.value |
| 232 |
elif token.ttype == sqlparse.tokens.Literal.String.Symbol: |
| 233 |
field_name = token.value[1:-1] |
| 234 |
if token.match(sqlparse.tokens.Keyword, "UNIQUE"): |
| 235 |
unique_columns = [field_name] |
| 236 |
# Start constraint columns parsing after CHECK keyword. |
| 237 |
if token.match(sqlparse.tokens.Keyword, "CHECK"): |
| 238 |
check = True |
| 239 |
check_braces_deep = braces_deep |
| 240 |
elif check: |
| 241 |
if check_braces_deep == braces_deep: |
| 242 |
if check_columns: |
| 243 |
# Stop constraint parsing. |
| 244 |
check = False |
| 245 |
continue |
| 246 |
if token.ttype in (sqlparse.tokens.Name, sqlparse.tokens.Keyword): |
| 247 |
if token.value in columns: |
| 248 |
check_columns.append(token.value) |
| 249 |
elif token.ttype == sqlparse.tokens.Literal.String.Symbol: |
| 250 |
if token.value[1:-1] in columns: |
| 251 |
check_columns.append(token.value[1:-1]) |
| 252 |
unique_constraint = ( |
| 253 |
{ |
| 254 |
"unique": True, |
| 255 |
"columns": unique_columns, |
| 256 |
"primary_key": False, |
| 257 |
"foreign_key": None, |
| 258 |
"check": False, |
| 259 |
"index": False, |
| 260 |
} |
| 261 |
if unique_columns |
| 262 |
else None |
| 263 |
) |
| 264 |
check_constraint = ( |
| 265 |
{ |
| 266 |
"check": True, |
| 267 |
"columns": check_columns, |
| 268 |
"primary_key": False, |
| 269 |
"unique": False, |
| 270 |
"foreign_key": None, |
| 271 |
"index": False, |
| 272 |
} |
| 273 |
if check_columns |
| 274 |
else None |
| 275 |
) |
| 276 |
return constraint_name, unique_constraint, check_constraint, token |
| 277 |
|
| 278 |
def _parse_table_constraints(self, sql, columns): |
| 279 |
# Check constraint parsing is based of SQLite syntax diagram. |
| 280 |
# https://www.sqlite.org/syntaxdiagrams.html#table-constraint |
| 281 |
statement = sqlparse.parse(sql)[0] |
| 282 |
constraints = {} |
| 283 |
unnamed_constrains_index = 0 |
| 284 |
tokens = (token for token in statement.flatten() if not token.is_whitespace) |
| 285 |
# Go to columns and constraint definition |
| 286 |
for token in tokens: |
| 287 |
if token.match(sqlparse.tokens.Punctuation, "("): |
| 288 |
break |
| 289 |
# Parse columns and constraint definition |
| 290 |
while True: |
| 291 |
( |
| 292 |
constraint_name, |
| 293 |
unique, |
| 294 |
check, |
| 295 |
end_token, |
| 296 |
) = self._parse_column_or_constraint_definition(tokens, columns) |
| 297 |
if unique: |
| 298 |
if constraint_name: |
| 299 |
constraints[constraint_name] = unique |
| 300 |
else: |
| 301 |
unnamed_constrains_index += 1 |
| 302 |
constraints[ |
| 303 |
"__unnamed_constraint_%s__" % unnamed_constrains_index |
| 304 |
] = unique |
| 305 |
if check: |
| 306 |
if constraint_name: |
| 307 |
constraints[constraint_name] = check |
| 308 |
else: |
| 309 |
unnamed_constrains_index += 1 |
| 310 |
constraints[ |
| 311 |
"__unnamed_constraint_%s__" % unnamed_constrains_index |
| 312 |
] = check |
| 313 |
if end_token.match(sqlparse.tokens.Punctuation, ")"): |
| 314 |
break |
| 315 |
return constraints |
| 316 |
|
| 317 |
def get_constraints(self, cursor, table_name): |
| 318 |
""" |
| 319 |
Retrieve any constraints or keys (unique, pk, fk, check, index) across |
| 320 |
one or more columns. |
| 321 |
""" |
| 322 |
constraints = {} |
| 323 |
# Find inline check constraints. |
| 324 |
try: |
| 325 |
table_schema = cursor.execute( |
| 326 |
"SELECT sql FROM sqlite_master WHERE type='table' and name=%s", |
| 327 |
[table_name], |
| 328 |
).fetchone()[0] |
| 329 |
except TypeError: |
| 330 |
# table_name is a view. |
| 331 |
pass |
| 332 |
else: |
| 333 |
columns = { |
| 334 |
info.name for info in self.get_table_description(cursor, table_name) |
| 335 |
} |
| 336 |
constraints.update(self._parse_table_constraints(table_schema, columns)) |
| 337 |
|
| 338 |
# Get the index info |
| 339 |
cursor.execute( |
| 340 |
"PRAGMA index_list(%s)" % self.connection.ops.quote_name(table_name) |
| 341 |
) |
| 342 |
for row in cursor.fetchall(): |
| 343 |
# SQLite 3.8.9+ has 5 columns, however older versions only give 3 |
| 344 |
# columns. Discard last 2 columns if there. |
| 345 |
number, index, unique = row[:3] |
| 346 |
cursor.execute( |
| 347 |
"SELECT sql FROM sqlite_master WHERE type='index' AND name=%s", |
| 348 |
[index], |
| 349 |
) |
| 350 |
# There's at most one row. |
| 351 |
(sql,) = cursor.fetchone() or (None,) |
| 352 |
# Inline constraints are already detected in |
| 353 |
# _parse_table_constraints(). The reasons to avoid fetching inline |
| 354 |
# constraints from `PRAGMA index_list` are: |
| 355 |
# - Inline constraints can have a different name and information |
| 356 |
# than what `PRAGMA index_list` gives. |
| 357 |
# - Not all inline constraints may appear in `PRAGMA index_list`. |
| 358 |
if not sql: |
| 359 |
# An inline constraint |
| 360 |
continue |
| 361 |
# Get the index info for that index |
| 362 |
cursor.execute( |
| 363 |
"PRAGMA index_info(%s)" % self.connection.ops.quote_name(index) |
| 364 |
) |
| 365 |
for index_rank, column_rank, column in cursor.fetchall(): |
| 366 |
if index not in constraints: |
| 367 |
constraints[index] = { |
| 368 |
"columns": [], |
| 369 |
"primary_key": False, |
| 370 |
"unique": bool(unique), |
| 371 |
"foreign_key": None, |
| 372 |
"check": False, |
| 373 |
"index": True, |
| 374 |
} |
| 375 |
constraints[index]["columns"].append(column) |
| 376 |
# Add type and column orders for indexes |
| 377 |
if constraints[index]["index"]: |
| 378 |
# SQLite doesn't support any index type other than b-tree |
| 379 |
constraints[index]["type"] = Index.suffix |
| 380 |
orders = self._get_index_columns_orders(sql) |
| 381 |
if orders is not None: |
| 382 |
constraints[index]["orders"] = orders |
| 383 |
# Get the PK |
| 384 |
pk_columns = self.get_primary_key_columns(cursor, table_name) |
| 385 |
if pk_columns: |
| 386 |
# SQLite doesn't actually give a name to the PK constraint, |
| 387 |
# so we invent one. This is fine, as the SQLite backend never |
| 388 |
# deletes PK constraints by name, as you can't delete constraints |
| 389 |
# in SQLite; we remake the table with a new PK instead. |
| 390 |
constraints["__primary__"] = { |
| 391 |
"columns": pk_columns, |
| 392 |
"primary_key": True, |
| 393 |
"unique": False, # It's not actually a unique constraint. |
| 394 |
"foreign_key": None, |
| 395 |
"check": False, |
| 396 |
"index": False, |
| 397 |
} |
| 398 |
relations = enumerate(self.get_relations(cursor, table_name).items()) |
| 399 |
constraints.update( |
| 400 |
{ |
| 401 |
f"fk_{index}": { |
| 402 |
"columns": [column_name], |
| 403 |
"primary_key": False, |
| 404 |
"unique": False, |
| 405 |
"foreign_key": (ref_table_name, ref_column_name), |
| 406 |
"check": False, |
| 407 |
"index": False, |
| 408 |
} |
| 409 |
for index, (column_name, (ref_column_name, ref_table_name)) in relations |
| 410 |
} |
| 411 |
) |
| 412 |
return constraints |
| 413 |
|
| 414 |
def _get_index_columns_orders(self, sql): |
| 415 |
tokens = sqlparse.parse(sql)[0] |
| 416 |
for token in tokens: |
| 417 |
if isinstance(token, sqlparse.sql.Parenthesis): |
| 418 |
columns = str(token).strip("()").split(", ") |
| 419 |
return ["DESC" if info.endswith("DESC") else "ASC" for info in columns] |
| 420 |
return None |
| 421 |
|
| 422 |
def _get_column_collations(self, cursor, table_name): |
| 423 |
row = cursor.execute( |
| 424 |
""" |
| 425 |
SELECT sql |
| 426 |
FROM sqlite_master |
| 427 |
WHERE type = 'table' AND name = %s |
| 428 |
""", |
| 429 |
[table_name], |
| 430 |
).fetchone() |
| 431 |
if not row: |
| 432 |
return {} |
| 433 |
|
| 434 |
sql = row[0] |
| 435 |
columns = str(sqlparse.parse(sql)[0][-1]).strip("()").split(", ") |
| 436 |
collations = {} |
| 437 |
for column in columns: |
| 438 |
tokens = column[1:].split() |
| 439 |
column_name = tokens[0].strip('"') |
| 440 |
for index, token in enumerate(tokens): |
| 441 |
if token == "COLLATE": |
| 442 |
collation = tokens[index + 1] |
| 443 |
break |
| 444 |
else: |
| 445 |
collation = None |
| 446 |
collations[column_name] = collation |
| 447 |
return collations |