| 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;') |