1# Mimic the sqlite3 console shell's .dump command 2# Author: Paul Kippes <kippesp@gmail.com> 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 10def _quote_name(name): 11 return '"{0}"'.format(name.replace('"', '""')) 12 13 14def _quote_value(value): 15 return "'{0}'".format(value.replace("'", "''")) 16 17 18def _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;') 114