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;')