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