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 10def _iterdump(connection): 11 """ 12 Returns an iterator to the dump of the database in an SQL text format. 13 14 Used to produce an SQL dump of the database. Useful to save an in-memory 15 database for later restoration. This function should not be called 16 directly but instead called from the Connection method, iterdump(). 17 """ 18 19 cu = connection.cursor() 20 yield('BEGIN TRANSACTION;') 21 22 # sqlite_master table contains the SQL CREATE statements for the database. 23 q = """ 24 SELECT "name", "type", "sql" 25 FROM "sqlite_master" 26 WHERE "sql" NOT NULL AND 27 "type" == 'table' 28 ORDER BY "name" 29 """ 30 schema_res = cu.execute(q) 31 sqlite_sequence = [] 32 for table_name, type, sql in schema_res.fetchall(): 33 if table_name == 'sqlite_sequence': 34 rows = cu.execute('SELECT * FROM "sqlite_sequence";').fetchall() 35 sqlite_sequence = ['DELETE FROM "sqlite_sequence"'] 36 sqlite_sequence += [ 37 f'INSERT INTO "sqlite_sequence" VALUES(\'{row[0]}\',{row[1]})' 38 for row in rows 39 ] 40 continue 41 elif table_name == 'sqlite_stat1': 42 yield('ANALYZE "sqlite_master";') 43 elif table_name.startswith('sqlite_'): 44 continue 45 # NOTE: Virtual table support not implemented 46 #elif sql.startswith('CREATE VIRTUAL TABLE'): 47 # qtable = table_name.replace("'", "''") 48 # yield("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"\ 49 # "VALUES('table','{0}','{0}',0,'{1}');".format( 50 # qtable, 51 # sql.replace("''"))) 52 else: 53 yield('{0};'.format(sql)) 54 55 # Build the insert statement for each row of the current table 56 table_name_ident = table_name.replace('"', '""') 57 res = cu.execute('PRAGMA table_info("{0}")'.format(table_name_ident)) 58 column_names = [str(table_info[1]) for table_info in res.fetchall()] 59 q = """SELECT 'INSERT INTO "{0}" VALUES({1})' FROM "{0}";""".format( 60 table_name_ident, 61 ",".join("""'||quote("{0}")||'""".format(col.replace('"', '""')) for col in column_names)) 62 query_res = cu.execute(q) 63 for row in query_res: 64 yield("{0};".format(row[0])) 65 66 # Now when the type is 'index', 'trigger', or 'view' 67 q = """ 68 SELECT "name", "type", "sql" 69 FROM "sqlite_master" 70 WHERE "sql" NOT NULL AND 71 "type" IN ('index', 'trigger', 'view') 72 """ 73 schema_res = cu.execute(q) 74 for name, type, sql in schema_res.fetchall(): 75 yield('{0};'.format(sql)) 76 77 # gh-79009: Yield statements concerning the sqlite_sequence table at the 78 # end of the transaction. 79 for row in sqlite_sequence: 80 yield('{0};'.format(row)) 81 82 yield('COMMIT;') 83