(self, cmd)
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.
dump ?TABLE? [TABLE...]: Dumps all or specified tables in SQL text format
dump ?TABLE? [ TABLE ... ] : Dumps all or specified tables in SQL text format
def command_dump(self, cmd):
        """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.
        """
        # Simple tables are easy to dump.  More complicated is dealing
        # with virtual tables, foreign keys etc.

        # Lock the database while doing the dump so nothing changes
        # under our feet
        self.process_sql("BEGIN IMMEDIATE", internal=True)

        # Used in comment() - see issue 142
        outputstrtype = str
        if sys.version_info < (3, 0):
            outputstrtype = unicode

        # Python 2.3 can end up with nonsense like "en_us" so we fall
        # back to ascii in that case
        outputstrencoding = getattr(self.stdout, "encoding", "ascii")
        try:
            codecs.lookup(outputstrencoding)
        except:
            outputstrencoding = "ascii"

        def unicodify(s):
            if not isinstance(s, outputstrtype):
                # See issue 142 - it may not be in an expected encoding
                return s.decode(outputstrencoding, "replace")
            return s

        try:
            # first pass -see if virtual tables or foreign keys are in
            # use.  If they are we emit pragmas to deal with them, but
            # prefer not to emit them
            v = {"virtuals": False, "foreigns": False}

            def check(name, sql):
                if name.lower().startswith("sqlite_"):
                    return False
                sql = sql.lower()
                if re.match(r"^\s*create\s+virtual\s+.*", sql):
                    v["virtuals"] = True
                # pragma table_info doesn't tell us if foreign keys
                # are involved so we guess if any the various strings are
                # in the sql somewhere
                if re.match(r".*\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

            # will we need to analyze anything later?
            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):
                s = unicodify(s)
                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: " + unicodify(time.strftime("%c")))
            comment("Tables like: " + pats)
            comment("Database: " + self.db.filename)
            try:
                import getpass
                import socket
                comment("User: %s @ %s" % (unicodify(getpass.getuser()), unicodify(socket.gethostname())))
            except ImportError:
                pass
            blank()

            comment("The values of various per-database settings")
            self.write(self.stdout,
                       "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()

            # different python versions have different requirements
            # about specifying cmp to sort routine so we use this
            # portable workaround with a decorated list instead
            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):
                # return formatted sql watching out for embedded
                # comments at the end forcing trailing ; onto next
                # line https://sqlite.org/src/info/c04a8b8a4f
                if "--" in s.split("\n")[-1]:
                    nl = "\n"
                else:
                    nl = ""
                return s + nl + ";\n"

            # do the table dumping loops
            oldtable = self._output_table
            try:
                self.push_output()
                self.output = self.output_insert
                # Dump the table
                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)
                        # Special treatment for virtual tables - they
                        # get called back on drops and creates and
                        # could thwart us so we have to manipulate
                        # sqlite_master directly
                        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)
                        # Now any indices or triggers
                        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()
                # Views done last.  They have to be done in the same order as they are in sqlite_master
                # as they could refer to each other
                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()

                # sqlite sequence
                # does it exist
                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

            # analyze
            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()

            # user version pragma
            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)."
                )
                self.write(self.stdout, "pragma user_version=%d;" % (uv, ))
                blank()

            # Save it all
            self.write(self.stdout, "COMMIT TRANSACTION;\n")

            # cleanup pragmas
            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")
                # schema reread
                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)
def command_dump ( self , cmd ) : # Simple tables are easy to dump.  More complicated is dealing # with virtual tables, foreign keys etc. # Lock the database while doing the dump so nothing changes # under our feet self . process_sql ( "BEGIN IMMEDIATE" , internal = True ) # Used in comment() - see issue 142 outputstrtype = str if sys . version_info < ( 3 , 0 ) : outputstrtype = unicode # Python 2.3 can end up with nonsense like "en_us" so we fall # back to ascii in that case outputstrencoding = getattr ( self . stdout , "encoding" , "ascii" ) try : codecs . lookup ( outputstrencoding ) except : outputstrencoding = "ascii" def unicodify ( s ) : if not isinstance ( s , outputstrtype ) : # See issue 142 - it may not be in an expected encoding return s . decode ( outputstrencoding , "replace" ) return s try : # first pass -see if virtual tables or foreign keys are in # use.  If they are we emit pragmas to deal with them, but # prefer not to emit them v = { "virtuals" : False , "foreigns" : False } def check ( name , sql ) : if name . lower ( ) . startswith ( "sqlite_" ) : return False sql = sql . lower ( ) if re . match ( r"^\s*create\s+virtual\s+.*" , sql ) : v [ "virtuals" ] = True # pragma table_info doesn't tell us if foreign keys # are involved so we guess if any the various strings are # in the sql somewhere if re . match ( r".*\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 # will we need to analyze anything later? 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 ) : s = unicodify ( s ) 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: " + unicodify ( time . strftime ( "%c" ) ) ) comment ( "Tables like: " + pats ) comment ( "Database: " + self . db . filename ) try : import getpass import socket comment ( "User: %s @ %s" % ( unicodify ( getpass . getuser ( ) ) , unicodify ( socket . gethostname ( ) ) ) ) except ImportError : pass blank ( ) comment ( "The values of various per-database settings" ) self . write ( self . stdout , "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 ( ) # different python versions have different requirements # about specifying cmp to sort routine so we use this # portable workaround with a decorated list instead 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 ) : # return formatted sql watching out for embedded # comments at the end forcing trailing ; onto next # line https://sqlite.org/src/info/c04a8b8a4f if "--" in s . split ( "\n" ) [ - 1 ] : nl = "\n" else : nl = "" return s + nl + ";\n" # do the table dumping loops oldtable = self . _output_table try : self . push_output ( ) self . output = self . output_insert # Dump the table 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 ) # Special treatment for virtual tables - they # get called back on drops and creates and # could thwart us so we have to manipulate # sqlite_master directly 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 ) # Now any indices or triggers 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 ( ) # Views done last.  They have to be done in the same order as they are in sqlite_master # as they could refer to each other 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 ( ) # sqlite sequence # does it exist 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 # analyze 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 ( ) # user version pragma 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)." ) self . write ( self . stdout , "pragma user_version=%d;" % ( uv , ) ) blank ( ) # Save it all self . write ( self . stdout , "COMMIT TRANSACTION;\n" ) # cleanup pragmas 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" ) # schema reread 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 )