Debian Codegraph @

Lib/sqlite3/dump.py

raw
1 # Mimic the sqlite3 console shell's .dump command
2 # Author: Paul Kippes <[email protected]>
3
4 # Every identifier in sql is quoted based on a comment in sqlite
5 # documentation "SQLite adds new keywords from time to time when it
6 # takes on new features. So to prevent your code from being broken by
7 # future enhancements, you should normally quote any identifier that
8 # is an English language word, even if you do not have to."
9
10 def _quote_name(name):
11 return '"{0}"'.format(name.replace('"', '""'))
12
13
14 def _quote_value(value):
15 return "'{0}'".format(value.replace("'", "''"))
16
17
18 def _iterdump(connection, *, filter=None):
19 """
20 Returns an iterator to the dump of the database in an SQL text format.
21
22 Used to produce an SQL dump of the database. Useful to save an in-memory
23 database for later restoration. This function should not be called
24 directly but instead called from the Connection method, iterdump().
25 """
26
27 writeable_schema = False
28 cu = connection.cursor()
29 cu.row_factory = None # Make sure we get predictable results.
30 # Disable foreign key constraints, if there is any foreign key violation.
31 violations = cu.execute("PRAGMA foreign_key_check").fetchall()
32 if violations:
33 yield('PRAGMA foreign_keys=OFF;')
34 yield('BEGIN TRANSACTION;')
35
36 if filter:
37 # Return database objects which match the filter pattern.
38 filter_name_clause = 'AND "name" LIKE ?'
39 params = [filter]
40 else:
41 filter_name_clause = ""
42 params = []
43 # sqlite_master table contains the SQL CREATE statements for the database.
44 q = f"""
45 SELECT "name", "type", "sql"
46 FROM "sqlite_master"
47 WHERE "sql" NOT NULL AND
48 "type" == 'table'
49 {filter_name_clause}
50 ORDER BY "name"
51 """
52 schema_res = cu.execute(q, params)
53 sqlite_sequence = []
54 for table_name, type, sql in schema_res.fetchall():
55 if table_name == 'sqlite_sequence':
56 rows = cu.execute('SELECT * FROM "sqlite_sequence";')
57 sqlite_sequence = ['DELETE FROM "sqlite_sequence"']
58 sqlite_sequence += [
59 f'INSERT INTO "sqlite_sequence" VALUES({_quote_value(table_name)},{seq_value})'
60 for table_name, seq_value in rows.fetchall()
61 ]
62 continue
63 elif table_name == 'sqlite_stat1':
64 yield('ANALYZE "sqlite_master";')
65 elif table_name.startswith('sqlite_'):
66 continue
67 elif sql.startswith('CREATE VIRTUAL TABLE'):
68 if not writeable_schema:
69 writeable_schema = True
70 yield('PRAGMA writable_schema=ON;')
71 yield("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
72 "VALUES('table',{0},{0},0,{1});".format(
73 _quote_value(table_name),
74 _quote_value(sql),
75 ))
76 else:
77 yield('{0};'.format(sql))
78
79 # Build the insert statement for each row of the current table
80 table_name_ident = _quote_name(table_name)
81 res = cu.execute(f'PRAGMA table_info({table_name_ident})')
82 column_names = [str(table_info[1]) for table_info in res.fetchall()]
83 q = "SELECT 'INSERT INTO {0} VALUES('{1}')' FROM {0};".format(
84 table_name_ident,
85 "','".join(
86 "||quote({0})||".format(_quote_name(col)) for col in column_names
87 )
88 )
89 query_res = cu.execute(q)
90 for row in query_res:
91 yield("{0};".format(row[0]))
92
93 # Now when the type is 'index', 'trigger', or 'view'
94 q = f"""
95 SELECT "name", "type", "sql"
96 FROM "sqlite_master"
97 WHERE "sql" NOT NULL AND
98 "type" IN ('index', 'trigger', 'view')
99 {filter_name_clause}
100 """
101 schema_res = cu.execute(q, params)
102 for name, type, sql in schema_res.fetchall():
103 yield('{0};'.format(sql))
104
105 if writeable_schema:
106 yield('PRAGMA writable_schema=OFF;')
107
108 # gh-79009: Yield statements concerning the sqlite_sequence table at the
109 # end of the transaction.
110 for row in sqlite_sequence:
111 yield('{0};'.format(row))
112
113 yield('COMMIT;')