dump in-memory db to file in tcl

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

dump in-memory db to file in tcl

Ned Fleming


Is it possible to dump an in-memory sqlite database (or table?) to a
file from within Tcl?

I create it like so:

        sqlite3 dbFireData :memory:

and insert a bunch of records, and then commit.

I have tried the following (and variations) -- but no go, kokomo.
($fileOut3 is a handle to a command-line file name.)

        exec sqlite3 dump dbFireData $fileOut3

Something tells me I'm wildly off-base.

Ned

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: dump in-memory db to file in tcl

Simon Slavin-2

On 2 Oct 2009, at 5:20pm, Ned Fleming wrote:

> Is it possible to dump an in-memory sqlite database (or table?) to a
> file from within Tcl?

Not easily.  MySQL has a pseudo-command which does what .dump does:  
returns a long piece of text with all the commands needed to reproduce  
the table.  If I suggest adding this as a PRAGMA people will scream  
'bloat'.  In SQLite it's easy to find the command needed to create the  
TABLE and INDEXes, but what's hard is devising a short way to recreate  
the records.

> I have tried the following (and variations) -- but no go, kokomo.
> ($fileOut3 is a handle to a command-line file name.)
>
> exec sqlite3 dump dbFireData $fileOut3

 From a shell:

echo '.dump' | sqlite3 mydb.sql > mydb.dump

echo '.dump' | sqlite3 mydb.sql | gzip -c > mydb.dump.gz

I don't know enough about Tcl to know if you can do piping and routing  
in its 'exec' commands.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: dump in-memory db to file in tcl

Gerry Snyder-4
In reply to this post by Ned Fleming
Ned Fleming wrote:
> Is it possible to dump an in-memory sqlite database (or table?) to a
> file from within Tcl?
>
> I create it like so:
>
> sqlite3 dbFireData :memory:
>
> and insert a bunch of records, and then commit.

Is there reason not to attach a file (old or new), and either

create table realfile.newtablecopy as select * from main.originaltable

or else create the new table with ~ the same create table statement used
for the original table, and then

insert into realfile.newtablecopy select * from main.originaltable

?  The first method copies all the data, but will drop any special
thingies from the column definitions.


HTH,


Gerry
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: dump in-memory db to file in tcl

Alexey Pechnikov-2
In reply to this post by Ned Fleming
Hello!

1. Use tcl backup API

The "backup" method
The "backup" method makes a backup copy of a live database. The command syntax is like this:
dbcmd backup ?source-database? backup-filename
The optional source-database argument tells which database in the current connection should be backed up. The default value is main (or, in other words, the primary database file). To back up TEMP tables use temp. To backup an auxilary database added to the connection using the ATTACH command, use the name of that database as it was assigned in the ATTACH command.
The backup-filename is the name of a file into which the backup is written. Backup-filename does not have to exist ahead of time, but if it does, it must be a well-formed SQLite database.
The "restore" method
The "restore" method copies the content a separate database file into the current database connection, overwriting any preexisting content. The command syntax is like this:
dbcmd restore ?target-database? source-filename
The optional target-database argument tells which database in the current connection should be overwritten with new content. The default value is main (or, in other words, the primary database file). To repopulate the TEMP tables use temp. To overwrite an auxilary database added to the connection using the ATTACH command, use the name of that database as it was assigned in the ATTACH command.
The source-filename is the name of a existing well-formed SQLite database file from which the content is extracted.

2. Use sql commands to copy database structure from sqlite_master table and copy data

2006-02-20: A simple TCL-Implementation for loading a DB into memory:
proc loadDB {dbhandle filename} {
    if {$filename != ""} {
        #attach persistent DB to target DB
        $dbhandle eval "ATTACH DATABASE '$filename' AS loadfrom"
        #copy each table to the target DB
        foreach {tablename} [$dbhandle eval "SELECT name FROM loadfrom.sqlite_master WHERE type = 'table'"] {
            $dbhandle eval "CREATE TABLE '$tablename' AS SELECT * FROM loadfrom.'$tablename'"
        }
        #create indizes in loaded table
        foreach {sql_exp} [$dbhandle eval "SELECT sql FROM loadfrom.sqlite_master WHERE type = 'index'"] {
            $dbhandle eval $sql_exp
        }
        #detach the source DB
        $dbhandle eval {DETACH loadfrom}
    }
}


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users