# Mimic the sqlite3 console shell's .dump command

[0] Fix | Delete

# Author: Paul Kippes <kippesp@gmail.com>

[1] Fix | Delete

[2] Fix | Delete

# Every identifier in sql is quoted based on a comment in sqlite

[3] Fix | Delete

# documentation "SQLite adds new keywords from time to time when it

[4] Fix | Delete

# takes on new features. So to prevent your code from being broken by

[5] Fix | Delete

# future enhancements, you should normally quote any identifier that

[6] Fix | Delete

# is an English language word, even if you do not have to."

[7] Fix | Delete

[8] Fix | Delete

def _iterdump(connection):

[9] Fix | Delete

"""

[10] Fix | Delete

Returns an iterator to the dump of the database in an SQL text format.

[11] Fix | Delete

[12] Fix | Delete

Used to produce an SQL dump of the database. Useful to save an in-memory

[13] Fix | Delete

database for later restoration. This function should not be called

[14] Fix | Delete

directly but instead called from the Connection method, iterdump().

[15] Fix | Delete

"""

[16] Fix | Delete

[17] Fix | Delete

cu = connection.cursor()

[18] Fix | Delete

yield('BEGIN TRANSACTION;')

[19] Fix | Delete

[20] Fix | Delete

# sqlite_master table contains the SQL CREATE statements for the database.

[21] Fix | Delete

q = """

[22] Fix | Delete

SELECT "name", "type", "sql"

[23] Fix | Delete

FROM "sqlite_master"

[24] Fix | Delete

WHERE "sql" NOT NULL AND

[25] Fix | Delete

"type" == 'table'

[26] Fix | Delete

ORDER BY "name"

[27] Fix | Delete

"""

[28] Fix | Delete

schema_res = cu.execute(q)

[29] Fix | Delete

for table_name, type, sql in schema_res.fetchall():

[30] Fix | Delete

if table_name == 'sqlite_sequence':

[31] Fix | Delete

yield('DELETE FROM "sqlite_sequence";')

[32] Fix | Delete

elif table_name == 'sqlite_stat1':

[33] Fix | Delete

yield('ANALYZE "sqlite_master";')

[34] Fix | Delete

elif table_name.startswith('sqlite_'):

[35] Fix | Delete

continue

[36] Fix | Delete

# NOTE: Virtual table support not implemented

[37] Fix | Delete

#elif sql.startswith('CREATE VIRTUAL TABLE'):

[38] Fix | Delete

# qtable = table_name.replace("'", "''")

[39] Fix | Delete

# yield("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"\

[40] Fix | Delete

# "VALUES('table','{0}','{0}',0,'{1}');".format(

[41] Fix | Delete

# qtable,

[42] Fix | Delete

# sql.replace("''")))

[43] Fix | Delete

else:

[44] Fix | Delete

yield('{0};'.format(sql))

[45] Fix | Delete

[46] Fix | Delete

# Build the insert statement for each row of the current table

[47] Fix | Delete

table_name_ident = table_name.replace('"', '""')

[48] Fix | Delete

res = cu.execute('PRAGMA table_info("{0}")'.format(table_name_ident))

[49] Fix | Delete

column_names = [str(table_info[1]) for table_info in res.fetchall()]

[50] Fix | Delete

q = """SELECT 'INSERT INTO "{0}" VALUES({1})' FROM "{0}";""".format(

[51] Fix | Delete

table_name_ident,

[52] Fix | Delete

",".join("""'||quote("{0}")||'""".format(col.replace('"', '""')) for col in column_names))

[53] Fix | Delete

query_res = cu.execute(q)

[54] Fix | Delete

for row in query_res:

[55] Fix | Delete

yield("{0};".format(row[0]))

[56] Fix | Delete

[57] Fix | Delete

# Now when the type is 'index', 'trigger', or 'view'

[58] Fix | Delete

q = """

[59] Fix | Delete

SELECT "name", "type", "sql"

[60] Fix | Delete

FROM "sqlite_master"

[61] Fix | Delete

WHERE "sql" NOT NULL AND

[62] Fix | Delete

"type" IN ('index', 'trigger', 'view')

[63] Fix | Delete

"""

[64] Fix | Delete

schema_res = cu.execute(q)

[65] Fix | Delete

for name, type, sql in schema_res.fetchall():

[66] Fix | Delete

yield('{0};'.format(sql))

[67] Fix | Delete

[68] Fix | Delete

yield('COMMIT;')

[69] Fix | Delete

[70] Fix | Delete