Debian Codegraph @

django/db/backends/sqlite3/introspection.py

raw
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