1 # Mimic the sqlite3 console shell's .dump command 2 # Author: Paul Kippes <kippesp (at] 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 10 def _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 for table_name, type, sql in schema_res.fetchall(): 32 if table_name == 'sqlite_sequence': 33 yield('DELETE FROM "sqlite_sequence";') 34 elif table_name == 'sqlite_stat1': 35 yield('ANALYZE "sqlite_master";') 36 elif table_name.startswith('sqlite_'): 37 continue 38 # NOTE: Virtual table support not implemented 39 #elif sql.startswith('CREATE VIRTUAL TABLE'): 40 # qtable = table_name.replace("'", "''") 41 # yield("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"\ 42 # "VALUES('table','{0}','{0}',0,'{1}');".format( 43 # qtable, 44 # sql.replace("''"))) 45 else: 46 yield('%s;' % sql) 47 48 # Build the insert statement for each row of the current table 49 table_name_ident = table_name.replace('"', '""') 50 res = cu.execute('PRAGMA table_info("{0}")'.format(table_name_ident)) 51 column_names = [str(table_info[1]) for table_info in res.fetchall()] 52 q = """SELECT 'INSERT INTO "{0}" VALUES({1})' FROM "{0}";""".format( 53 table_name_ident, 54 ",".join("""'||quote("{0}")||'""".format(col.replace('"', '""')) for col in column_names)) 55 query_res = cu.execute(q) 56 for row in query_res: 57 yield("%s;" % row[0]) 58 59 # Now when the type is 'index', 'trigger', or 'view' 60 q = """ 61 SELECT "name", "type", "sql" 62 FROM "sqlite_master" 63 WHERE "sql" NOT NULL AND 64 "type" IN ('index', 'trigger', 'view') 65 """ 66 schema_res = cu.execute(q) 67 for name, type, sql in schema_res.fetchall(): 68 yield('%s;' % sql) 69 70 yield('COMMIT;') 71
