'dump ?TABLE? [TABLE...]: Dumps all or specified tables in SQL text format
The table name is treated as like pattern so you can use % as
a wildcard.  You can use dump to make a text based backup of
the database.  It is also useful for comparing differences or
making the data available to other databases.  Indices and
triggers for the table(s) are also dumped.  Finally views
matching the table pattern name are dumped (it isn\'t possible
to work out which views access which table and views can
access multiple tables anyway).
Note that if you are dumping virtual tables such as used by
the FTS3 module then they may use other tables to store
information.  For example if you create a FTS3 table named
*recipes* then it also creates *recipes_content*,
*recipes_segdir* etc.  Consequently to dump this example
correctly use::
.dump recipes recipes_%
If the database is empty or no tables/views match then there
is no output.'

def command_dump(self, cmd):

    self.process_sql('BEGIN    IMMEDIATE', internal=True)
    try:
        v = {'virtuals': False, 'foreigns': False}
        def check(name, sql):
            if name.lower().startswith('sqlite_'):
                return False
            sql = sql.lower()
            if re.match('^\\s*create\\s+virtual\\s+.*', sql):
                v['virtuals'] = True
            if re.match('.*\\b(foreign\\s*key|references)\\b.*', sql):
                v['foreigns'] = True
            return True
        if (len(cmd) == 0):
            cmd = ['%']
        tables = []
        for pattern in cmd:
            for (name, sql) in self.db.cursor().execute("SELECT    name,sql    FROM    sqlite_master    WHERE    sql    NOT    NULL    AND    type    IN    ('table','view')    AND    tbl_name    LIKE    ?1", (pattern,)):
                if (check(name, sql) and (name not in tables)):
                    tables.append(name)
        if (not tables):
            return
        analyze_needed = []
        for stat in self.db.cursor().execute("select    name    from    sqlite_master    where    sql    not    null    and    type='table'    and    tbl_name    like    'sqlite_stat%'"):
            for name in tables:
                if len(self.db.cursor().execute((('select    *    from    ' + self._fmt_sql_identifier(stat[0])) + '    WHERE    tbl=?'), (name,)).fetchall()):
                    if (name not in analyze_needed):
                        analyze_needed.append(name)
        analyze_needed.sort()
        def blank():
            self.write(self.stdout, '\n')
        def comment(s):
            if isinstance(s, bytes):
                s = s.decode('utf-8', 'replace')
            self.write(self.stdout, (textwrap.fill(s, 78, initial_indent='--    ', subsequent_indent='--    ') + '\n'))
        pats = ',    '.join([(x, '(All)')[(x == '%')] for x in cmd])
        comment(('SQLite    dump    (by    APSW    %s)' % (apsw.apswversion(),)))
        comment(('SQLite    version    ' + apsw.sqlitelibversion()))
        comment(('Date:    ' + time.ctime()))
        comment(('Tables    like:    ' + pats))
        comment(('Database:    ' + self.db.filename))
        try:
            import getpass
            import socket
            comment(('User:    %s    @    %s' % (getpass.getuser(), socket.gethostname())))
        except ImportError:
            pass
        blank()
        comment('The    values    of    various    per-database    settings')
        comment((('PRAGMA    page_size=' + str(self.db.cursor().execute('pragma    page_size').fetchall()[0][0])) + ';\n'))
        comment((("PRAGMA    encoding='" + self.db.cursor().execute('pragma    encoding').fetchall()[0][0]) + "';\n"))
        vac = {0: 'NONE', 1: 'FULL', 2: 'INCREMENTAL'}
        vacvalue = self.db.cursor().execute('pragma    auto_vacuum').fetchall()[0][0]
        comment((('PRAGMA    auto_vacuum=' + vac.get(vacvalue, str(vacvalue))) + ';\n'))
        comment((('PRAGMA    max_page_count=' + str(self.db.cursor().execute('pragma    max_page_count').fetchall()[0][0])) + ';\n'))
        blank()
        dectables = [(x.lower(), x) for x in tables]
        dectables.sort()
        tables = [y for (x, y) in dectables]
        virtuals = v['virtuals']
        foreigns = v['foreigns']
        if virtuals:
            comment('This    pragma    is    needed    to    restore    virtual    tables')
            self.write(self.stdout, 'PRAGMA    writable_schema=ON;\n')
        if foreigns:
            comment('This    pragma    turns    off    checking    of    foreign    keys    as    tables    would    be    inconsistent    while    restoring.        It    was    introduced    in    SQLite    3.6.19.')
            self.write(self.stdout, 'PRAGMA    foreign_keys=OFF;\n')
        if (virtuals or foreigns):
            blank()
        self.write(self.stdout, 'BEGIN    TRANSACTION;\n')
        blank()
        def sqldef(s):
            if ('--' in s.split('\n')[(-1)]):
                nl = '\n'
            else:
                nl = ''
            return ((s + nl) + ';\n')
        oldtable = self._output_table
        try:
            self.push_output()
            self.output = self.output_insert
            for table in tables:
                for sql in self.db.cursor().execute("SELECT    sql    FROM    sqlite_master    WHERE    name=?1    AND    type='table'", (table,)):
                    comment(('Table        ' + table))
                    if (sql[0].lower().split()[:3] == ['create', 'virtual', 'table']):
                        self.write(self.stdout, (('DELETE    FROM    sqlite_master    WHERE    name=' + apsw.format_sql_value(table)) + "    AND    type='table';\n"))
                        self.write(self.stdout, ("INSERT    INTO    sqlite_master(type,name,tbl_name,rootpage,sql)    VALUES('table',%s,%s,0,%s);\n" % (apsw.format_sql_value(table), apsw.format_sql_value(table), apsw.format_sql_value(sql[0]))))
                    else:
                        self.write(self.stdout, (('DROP    TABLE    IF    EXISTS    ' + self._fmt_sql_identifier(table)) + ';\n'))
                        self.write(self.stdout, sqldef(sql[0]))
                        self._output_table = self._fmt_sql_identifier(table)
                        self.process_sql(('select    *    from    ' + self._fmt_sql_identifier(table)), internal=True)
                    first = True
                    for (name, sql) in self.db.cursor().execute("SELECT    name,sql    FROM    sqlite_master    WHERE    sql    NOT    NULL    AND    type    IN    ('index',    'trigger')    AND    tbl_name=?1    AND    name    NOT    LIKE    'sqlite_%'    ORDER    BY    lower(name)", (table,)):
                        if first:
                            comment(('Triggers    and    indices    on        ' + table))
                            first = False
                        self.write(self.stdout, sqldef(sql))
                    blank()
            first = True
            for (name, sql) in self.db.cursor().execute((("SELECT    name,sql    FROM    sqlite_master    WHERE    sql    NOT    NULL    AND    type='view'    AND    name    IN    (    " + ','.join([apsw.format_sql_value(i) for i in tables])) + ')    ORDER    BY    _ROWID_')):
                if first:
                    comment('Views')
                    first = False
                self.write(self.stdout, ('DROP    VIEW    IF    EXISTS    %s;\n' % (self._fmt_sql_identifier(name),)))
                self.write(self.stdout, sqldef(sql))
            if (not first):
                blank()
            if len(self.db.cursor().execute("select    *    from    sqlite_master    where    name='sqlite_sequence'").fetchall()):
                first = True
                for t in tables:
                    v = self.db.cursor().execute('select    seq    from    main.sqlite_sequence    where    name=?1', (t,)).fetchall()
                    if len(v):
                        assert (len(v) == 1)
                        if first:
                            comment('For    primary    key    autoincrements    the    next    id    to    use    is    stored    in    sqlite_sequence')
                            first = False
                        self.write(self.stdout, ('DELETE    FROM    main.sqlite_sequence    WHERE    name=%s;\n' % (apsw.format_sql_value(t),)))
                        self.write(self.stdout, ('INSERT    INTO    main.sqlite_sequence    VALUES    (%s,    %s);\n' % (apsw.format_sql_value(t), v[0][0])))
                if (not first):
                    blank()
        finally:
            self.pop_output()
            self._output_table = oldtable
        if analyze_needed:
            comment('You    had    used    the    analyze    command    on    these    tables    before.        Rerun    for    this    new    data.')
            for n in analyze_needed:
                self.write(self.stdout, (('ANALYZE    ' + self._fmt_sql_identifier(n)) + ';\n'))
            blank()
        uv = self.db.cursor().execute('pragma    user_version').fetchall()[0][0]
        if uv:
            comment('Your    database    may    need    this.        It    is    sometimes    used    to    keep    track    of    the    schema    version    (eg    Firefox    does    this).')
            comment(('pragma    user_version=%d;' % (uv,)))
            blank()
        self.write(self.stdout, 'COMMIT    TRANSACTION;\n')
        if foreigns:
            blank()
            comment('Restoring    foreign    key    checking    back    on.        Note    that    SQLite    3.6.19    is    off    by    default')
            self.write(self.stdout, 'PRAGMA    foreign_keys=ON;\n')
        if virtuals:
            blank()
            comment('Restoring    writable    schema    back    to    default')
            self.write(self.stdout, 'PRAGMA    writable_schema=OFF;\n')
            blank()
            comment("We    need    to    force    SQLite    to    reread    the    schema    because    otherwise    it    doesn't    know    that    the    virtual    tables    we    inserted    directly    into    sqlite_master    exist.        See    last    comments    of    https://sqlite.org/cvstrac/tktview?tn=3425")
            self.write(self.stdout, 'BEGIN;\nCREATE    TABLE    no_such_table(x,y,z);\nROLLBACK;\n')
    finally:
        self.process_sql('END', internal=True)
