# Mimic the sqlite3 console shell's .dump command

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

def _iterdump(connection):

    """

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

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

    database for later restoration.  This function should not be called

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

    """

    cu = connection.cursor()

    yield('BEGIN TRANSACTION;')

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

    q = """

        SELECT name, type, sql

        FROM sqlite_master

            WHERE sql NOT NULL AND

            type == 'table'

        """

    schema_res = cu.execute(q)

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

        if table_name == 'sqlite_sequence':

            yield('DELETE FROM sqlite_sequence;')

        elif table_name == 'sqlite_stat1':

            yield('ANALYZE sqlite_master;')

        elif table_name.startswith('sqlite_'):

            continue

        # NOTE: Virtual table support not implemented

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

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

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

        #        "VALUES('table','%s','%s',0,'%s');" %

        #        qtable,

        #        qtable,

        #        sql.replace("''"))

        else:

            yield('%s;' % sql)

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

        res = cu.execute("PRAGMA table_info('%s')" % table_name)

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

        q = "SELECT 'INSERT INTO \"%(tbl_name)s\" VALUES("

        q += ",".join(["'||quote(" + col + ")||'" for col in column_names])

        q += ")' FROM '%(tbl_name)s'"

        query_res = cu.execute(q % {'tbl_name': table_name})

        for row in query_res:

            yield("%s;" % row[0])

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

    q = """

        SELECT name, type, sql

        FROM sqlite_master

            WHERE sql NOT NULL AND

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

        """

    schema_res = cu.execute(q)

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

        yield('%s;' % sql)

    yield('COMMIT;')