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