| 1 | # Mimic the sqlite3 console shell's .dump command
|
|---|
| 2 | # Author: Paul Kippes <kippesp@gmail.com>
|
|---|
| 3 |
|
|---|
| 4 | def _iterdump(connection):
|
|---|
| 5 | """
|
|---|
| 6 | Returns an iterator to the dump of the database in an SQL text format.
|
|---|
| 7 |
|
|---|
| 8 | Used to produce an SQL dump of the database. Useful to save an in-memory
|
|---|
| 9 | database for later restoration. This function should not be called
|
|---|
| 10 | directly but instead called from the Connection method, iterdump().
|
|---|
| 11 | """
|
|---|
| 12 |
|
|---|
| 13 | cu = connection.cursor()
|
|---|
| 14 | yield('BEGIN TRANSACTION;')
|
|---|
| 15 |
|
|---|
| 16 | # sqlite_master table contains the SQL CREATE statements for the database.
|
|---|
| 17 | q = """
|
|---|
| 18 | SELECT name, type, sql
|
|---|
| 19 | FROM sqlite_master
|
|---|
| 20 | WHERE sql NOT NULL AND
|
|---|
| 21 | type == 'table'
|
|---|
| 22 | """
|
|---|
| 23 | schema_res = cu.execute(q)
|
|---|
| 24 | for table_name, type, sql in schema_res.fetchall():
|
|---|
| 25 | if table_name == 'sqlite_sequence':
|
|---|
| 26 | yield('DELETE FROM sqlite_sequence;')
|
|---|
| 27 | elif table_name == 'sqlite_stat1':
|
|---|
| 28 | yield('ANALYZE sqlite_master;')
|
|---|
| 29 | elif table_name.startswith('sqlite_'):
|
|---|
| 30 | continue
|
|---|
| 31 | # NOTE: Virtual table support not implemented
|
|---|
| 32 | #elif sql.startswith('CREATE VIRTUAL TABLE'):
|
|---|
| 33 | # qtable = table_name.replace("'", "''")
|
|---|
| 34 | # yield("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"\
|
|---|
| 35 | # "VALUES('table','%s','%s',0,'%s');" %
|
|---|
| 36 | # qtable,
|
|---|
| 37 | # qtable,
|
|---|
| 38 | # sql.replace("''"))
|
|---|
| 39 | else:
|
|---|
| 40 | yield('%s;' % sql)
|
|---|
| 41 |
|
|---|
| 42 | # Build the insert statement for each row of the current table
|
|---|
| 43 | res = cu.execute("PRAGMA table_info('%s')" % table_name)
|
|---|
| 44 | column_names = [str(table_info[1]) for table_info in res.fetchall()]
|
|---|
| 45 | q = "SELECT 'INSERT INTO \"%(tbl_name)s\" VALUES("
|
|---|
| 46 | q += ",".join(["'||quote(" + col + ")||'" for col in column_names])
|
|---|
| 47 | q += ")' FROM '%(tbl_name)s'"
|
|---|
| 48 | query_res = cu.execute(q % {'tbl_name': table_name})
|
|---|
| 49 | for row in query_res:
|
|---|
| 50 | yield("%s;" % row[0])
|
|---|
| 51 |
|
|---|
| 52 | # Now when the type is 'index', 'trigger', or 'view'
|
|---|
| 53 | q = """
|
|---|
| 54 | SELECT name, type, sql
|
|---|
| 55 | FROM sqlite_master
|
|---|
| 56 | WHERE sql NOT NULL AND
|
|---|
| 57 | type IN ('index', 'trigger', 'view')
|
|---|
| 58 | """
|
|---|
| 59 | schema_res = cu.execute(q)
|
|---|
| 60 | for name, type, sql in schema_res.fetchall():
|
|---|
| 61 | yield('%s;' % sql)
|
|---|
| 62 |
|
|---|
| 63 | yield('COMMIT;')
|
|---|
