_SQLite_Dump

[KaFu]

A function to dump SQLite DB tables to another file DB (with examples). Any comments are welcome.

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3> ; for example output only

Local $aResult, $iRows, $iColumns, $g_hDB_SQLite_save

Opt("MustDeclareVars", 1)

_SQLite_Startup()
_SQLite_Open () ; open :memory: Database, func works fine for file DBs too
_SQLite_Exec (-1, "CREATE TABLE aTest (a,b,c);") ; CREATE a Table
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") ; INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") ; INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") ; INSERT Data
_SQLite_Exec (-1, "CREATE TABLE bTest (a,b,c);") ; CREATE a Table
_SQLite_Exec (-1, "INSERT INTO bTest(a,b,c) VALUES ('x','9','Hello');") ; INSERT Data
_SQLite_Exec (-1, "INSERT INTO bTest(a,b,c) VALUES ('y','8',' ');") ; INSERT Data
_SQLite_Exec (-1, "INSERT INTO bTest(a,b,c) VALUES ('z','7','World');") ; INSERT Data
_SQLite_Exec (-1, "CREATE INDEX test ON aTest(a,b,c);") ; Create Test-Index
_SQLite_GetTable2d(-1,"SELECT * FROM sqlite_master",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"Tables+Indices in Memory")

; Usage 1, dump all tables
_SQLite_Dump("dump.sqlite") ; dump all tables to file

; DB handle reset by function to memory-db handle, so you just can continue to work on it with -1

$g_hDB_SQLite_save = $g_hDB_SQLite ; save handle to memory DB for second test

; Example to check dump all to file
_SQLite_Open("dump.sqlite")
_SQLite_GetTable2d(-1,"SELECT * FROM sqlite_master;",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"File dump all tables, Tables+Indices in File")
_SQLite_GetTable2d(-1,"Select * From aTest",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"File dump all tables, aTest")
_SQLite_GetTable2d(-1,"Select * From bTest",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"File dump all tables, bTest")
_SQLite_Close()

$g_hDB_SQLite = $g_hDB_SQLite_save

; Usage 2, dump one table
_SQLite_Dump("dump2.sqlite","aTest") ; dump one table to file

; Example to check dump to file
_SQLite_Open("dump2.sqlite")
_SQLite_GetTable2d(-1,"SELECT * FROM sqlite_master;",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"File dump one tables, Tables+Indices in File")
_SQLite_GetTable2d(-1,"Select * From aTest",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"File dump one table")
_SQLite_Close()

_SQLite_Shutdown()


;===============================================================================
;
; Function Name:    _SQLite_Dump($sSQLite_File, $sSQLite_Table = false)
;               AutoIt Version: 3.2.12.1
; Description:    Dumps a SQLite table or Database to a SQLite Database File
; Parameter(s):  $sSQLite_File - Filename of SQLite Database to dump to
;               $sSQLite_Table = false (default) = all tables dumped; "Tablename" to dump a single table
; Author(s):        KaFu
; URL:        http://www.autoitscript.com/forum/index.php?showtopic=81346
; Note(s):   2008-Sep-25 - Created Func
;            2008-Sep-26 - Added code to transfer indices too
;
;===============================================================================
func _SQLite_Dump($sSQLite_File, $sSQLite_Table = false)
    Local $hSQLite_File, $hQuery, $aRow, $g_hDB_SQLite_save
    FileDelete($sSQLite_File)
    $g_hDB_SQLite_save = $g_hDB_SQLite
    $hSQLite_File = _SQLite_Open($sSQLite_File)
    _SQLite_Exec($g_hDB_SQLite_save, "ATTACH DATABASE '" & $sSQLite_File & "' as SQLite_File;")
    if $sSQLite_Table Then 
        _SQLite_Exec($g_hDB_SQLite_save, "CREATE TABLE SQLite_File." & $sSQLite_Table & " AS SELECT * FROM " & $sSQLite_Table & ";")
        _SQlite_Query ($g_hDB_SQLite_save, "SELECT sql FROM sqlite_master WHERE tbl_name = '" & $sSQLite_Table & "' AND type = 'index';", $hQuery)
        While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
            _SQLite_Exec($hSQLite_File, $aRow[0] & ";")
        WEnd
    Else
        _SQlite_Query ($g_hDB_SQLite_save, "SELECT name FROM sqlite_master WHERE type = 'table';", $hQuery)
        While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
            _SQLite_Exec($g_hDB_SQLite_save, "CREATE TABLE SQLite_File." & $aRow[0] & " AS SELECT * FROM " & $aRow[0] & ";")
        WEnd
        _SQlite_Query ($g_hDB_SQLite_save, "SELECT sql FROM sqlite_master WHERE type = 'index';", $hQuery)
        While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
            _SQLite_Exec($hSQLite_File, $aRow[0] & ";")
        WEnd
    EndIf
    _SQLite_Exec($g_hDB_SQLite_save, "DETACH DATABASE SQLite_File;")
    _SQLite_Close($hSQLite_File)
    $g_hDB_SQLite = $g_hDB_SQLite_save
EndFunc

Edit: Renamed Func, Added dump of indices

Best Regards

Edited September 26, 2008 by KaFu

[ptrex]

@KaFu

I see that you finished what someone else started

Dump DB

Room for improvement :

Only the DB structure is dumped in a file and NOT the data.

Good job so far.

Regards,

ptrex

[KaFu]

I see that you finished what someone else started

Didnt see that post , mine is completely independent code. Derived it from VB

Load DB from Disk to 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}
    }
    }oÝ÷ Ú«¨µéÚ

,or did I miss something important?

Best Regards

Edited September 26, 2008 by KaFu

[KaFu]

And if I look at the VB code above, the indices might be useful too ...

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3> ; for example output only

Local $aResult, $iRows, $iColumns, $g_hDB_SQLite_save

Opt("MustDeclareVars", 1)

_SQLite_Startup()
_SQLite_Open () ; open :memory: Database, func works fine for file DBs too
_SQLite_Exec (-1, "CREATE TABLE aTest (a,b,c);") ; CREATE a Table
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") ; INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") ; INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") ; INSERT Data
_SQLite_Exec (-1, "CREATE TABLE bTest (a,b,c);") ; CREATE a Table
_SQLite_Exec (-1, "INSERT INTO bTest(a,b,c) VALUES ('x','9','Hello');") ; INSERT Data
_SQLite_Exec (-1, "INSERT INTO bTest(a,b,c) VALUES ('y','8',' ');") ; INSERT Data
_SQLite_Exec (-1, "INSERT INTO bTest(a,b,c) VALUES ('z','7','World');") ; INSERT Data
_SQLite_Exec (-1, "CREATE INDEX test ON aTest(a,b,c);") ; Create Test-Index
_SQLite_GetTable2d(-1,"SELECT * FROM sqlite_master",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"Tables+Indices in Memory")

; Usage 1, dump all tables
_SQLite_Dump("dump.sqlite") ; dump all tables to file

; DB handle reset by function to memory-db handle, so you just can continue to work on it with -1

$g_hDB_SQLite_save = $g_hDB_SQLite ; save handle to memory DB for second test

; Example to check dump all to file
_SQLite_Open("dump.sqlite")
_SQLite_GetTable2d(-1,"SELECT * FROM sqlite_master;",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"File dump all tables, Tables+Indices in File")
_SQLite_GetTable2d(-1,"Select * From aTest",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"File dump all tables, aTest")
_SQLite_GetTable2d(-1,"Select * From bTest",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"File dump all tables, bTest")
_SQLite_Close()

$g_hDB_SQLite = $g_hDB_SQLite_save

; Usage 2, dump one table
_SQLite_Dump("dump2.sqlite","aTest") ; dump one table to file

; Example to check dump to file
_SQLite_Open("dump2.sqlite")
_SQLite_GetTable2d(-1,"SELECT * FROM sqlite_master;",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"File dump one tables, Tables+Indices in File")
_SQLite_GetTable2d(-1,"Select * From aTest",$aResult,$iRows,$iColumns)
_ArrayDisplay($aResult,"File dump one table")
_SQLite_Close()

_SQLite_Shutdown()


;===============================================================================
;
; Function Name:    _SQLite_Dump($sSQLite_File, $sSQLite_Table = false)
;               AutoIt Version: 3.2.12.1
; Description:    Dumps a SQLite table or Database to a SQLite Database File
; Parameter(s):  $sSQLite_File - Filename of SQLite Database to dump to
;               $sSQLite_Table = false (default) = all tables dumped; "Tablename" to dump a single table
; Author(s):        KaFu
; URL:        http://www.autoitscript.com/forum/index.php?showtopic=81346
; Note(s):      2008-Sep-25 - Created Func
;               2008-Sep-26 - Added code to transfer indices too
;
;===============================================================================
func _SQLite_Dump($sSQLite_File, $sSQLite_Table = false)
    Local $hSQLite_File, $hQuery, $aRow, $g_hDB_SQLite_save
    FileDelete($sSQLite_File)
    $g_hDB_SQLite_save = $g_hDB_SQLite
    $hSQLite_File = _SQLite_Open($sSQLite_File)
    _SQLite_Exec($g_hDB_SQLite_save, "ATTACH DATABASE '" & $sSQLite_File & "' as SQLite_File;")
    if $sSQLite_Table Then 
        _SQLite_Exec($g_hDB_SQLite_save, "CREATE TABLE SQLite_File." & $sSQLite_Table & " AS SELECT * FROM " & $sSQLite_Table & ";")
        _SQlite_Query ($g_hDB_SQLite_save, "SELECT sql FROM sqlite_master WHERE tbl_name = '" & $sSQLite_Table & "' AND type = 'index';", $hQuery)
        While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
            _SQLite_Exec($hSQLite_File, $aRow[0] & ";")
        WEnd
    Else
        _SQlite_Query ($g_hDB_SQLite_save, "SELECT name FROM sqlite_master WHERE type = 'table';", $hQuery)
        While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
            _SQLite_Exec($g_hDB_SQLite_save, "CREATE TABLE SQLite_File." & $aRow[0] & " AS SELECT * FROM " & $aRow[0] & ";")
        WEnd
        _SQlite_Query ($g_hDB_SQLite_save, "SELECT sql FROM sqlite_master WHERE type = 'index';", $hQuery)
        While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
            _SQLite_Exec($hSQLite_File, $aRow[0] & ";")
        WEnd
    EndIf
    _SQLite_Exec($g_hDB_SQLite_save, "DETACH DATABASE SQLite_File;")
    _SQLite_Close($hSQLite_File)
    $g_hDB_SQLite = $g_hDB_SQLite_save
EndFuncoÝ÷ Ø(^rH§Ø^vé©y×Zµ¦®¶­sb6æ6ÇVFRfÇCµ5ÆFRæS2fwC°¢6æ6ÇVFRfÇCµ5ÆFRæFÆÂæS2fwC°¢6æ6ÇVFRfÇC´'&æS2fwC²²f÷"W×ÆR÷WGWBöæǤÆö6Âb33c¶&W7VÇBÂb33c¶&÷w2Âb33c¶6öÇVÖç2Âb33c¶VW'Âb33c¶&÷p¤÷BgV÷C´×W7DFV6Æ&Uf'2gV÷C²Â¥õ5ÆFUõ7F'GW¥õ5ÆFUô÷VâgV÷C¶GV×ç7ÆFRgV÷C²¥õ5ÆFUôvWEF&ÆS&BÓÂgV÷Cµ4TÄT5B¢e$ôÒ7ÆFUöÖ7FW"gV÷C²Âb33c¶&W7VÇBÂb33c¶&÷w2Âb33c¶6öÇVÖç2¥ô'&F7Æb33c¶&W7VÇBÂgV÷CµF&ÆW2´æF6W2âfÆRgV÷C²¥õ5ÆFUõVW'ÓÂgV÷Cµ4TÄT5BæÖRe$ôÒ7ÆFUöÖ7FW"tU$RGRÒb33·F&ÆRb33³²gV÷C²Âb33c¶VW'¥vÆRõ5ÆFUôfWF6FFb33c¶VW'Âb33c¶&÷rÒb33cµ5ÄDUôô° õ5ÆFUôvWEF&ÆS&BÓÂgV÷Cµ4TÄT5B¢e$ôÒgV÷C²fײb33c¶&÷u³ÒÂb33c¶&W7VÇBÂb33c¶&÷w2Âb33c¶6öÇVÖç2 ô'&F7Æb33c¶&W7VÇBÂgV÷CµF&ÆW2´æF6W2âfÆRgV÷C²¥tVæ@¥õ5ÆFUô6Æ÷6R¥õ5ÆFUõ6WFF÷vâ

Edited September 26, 2008 by KaFu

[wraithdu]

n/m my previous comment. Nice function

Edited September 26, 2008 by wraithdu

[ptrex]

@KaFu

Indead the data is dumped as well.

After checking something did not work with my SQL Browser.

Good job.

Regards,

ptrex

[ptrex]

@KaFu

Indead the data is dumped as well.

After checking something did not work with my SQL Browser.

Good job.

Regards,

ptrex

[laffo16]

excellent! thank you KaFu, will this be added to the UDF?

would it be possible to do this in reverse, to dump a saved db back into the memory.

Edited November 12, 2008 by laffo16

[laffo16]

i sent you a pm KaFu but it didnt appear in my sentbox so i post here just incase

"i'm having a problem with _SQLite_Load & _SQLite_Dump, i havent used these functions for a while and must have missed the problem last time around. basicly the problem i have is that auto_increment is forgotten (or removed) by both functions.

with a little testing i see that their is an extra table when using the regular _SQLite_Open() called "sqlite_sequence", this stores the sequential record. this table is not saved when either using _load or _dump functions, also i noticed my "INTEGER PRIMARY KEY" becomes just "INTEGER". I did have a try to fix the problem but my.. mysql is not very good. if you get the time, could you please have a look and let me know."

i may aswell post this code here which was writen by KaFu, its used to load a database file into memory

Func _SQLite_Load($sSQLite_File)
    Local $hSQLite_File, $hQuery, $aRow, $g_hDB_SQLite_save, $aNames
    _SQLite_Close() ; close and reopen Memory Database to Flush it
    _SQLite_Open()
    $g_hDB_SQLite_save = $g_hDB_SQLite
    $hSQLite_File = _SQLite_Open($sSQLite_File)
    _SQLite_Exec($g_hDB_SQLite_save, "ATTACH DATABASE '" & $sSQLite_File & "' as SQLite_File;")
        _SQLite_Query($g_hDB_SQLite_save, "SELECT name FROM SQLite_File.sqlite_master WHERE type = 'table';", $hQuery)
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            $s_sqlite_query = "CREATE TABLE " & $aRow[0] & " AS SELECT * FROM SQLite_File." & $aRow[0] & ";"
            _SQLite_Exec($g_hDB_SQLite_save, $s_sqlite_query)
        WEnd
        _SQLite_Query($g_hDB_SQLite_save, "SELECT sql FROM SQLite_File.sqlite_master WHERE type = 'index';", $hQuery)
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            _SQLite_Exec($hSQLite_File, $aRow[0] & ";")
        WEnd
    _SQLite_Exec($g_hDB_SQLite_save, "DETACH DATABASE SQLite_File;")
    _SQLite_Close($hSQLite_File)
    $g_hDB_SQLite = $g_hDB_SQLite_save
EndFunc   ;==>_SQLite_Load

Edited April 7, 2009 by laffo16

[KaFu]

Laffo, thanks for pointing me to the "sqlite_sequence" , never heard of that table before ... guess this needs some testing and tweaking, try this:

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3> ; for example output only

Local $aResult, $iRows, $iColumns, $g_hDB_SQLite_save

Opt("MustDeclareVars", 1)

_SQLite_Startup()
_SQLite_Open() ; open :memory: Database, func works fine for file DBs too
_SQLite_Exec(-1, "CREATE TABLE aTest (a,b,c,d INTEGER PRIMARY KEY AUTOINCREMENT);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") ; INSERT Data
_SQLite_Exec(-1, "CREATE TABLE bTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO bTest(a,b,c) VALUES ('x','9','Hello');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO bTest(a,b,c) VALUES ('y','8',' ');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO bTest(a,b,c) VALUES ('z','7','World');") ; INSERT Data
_SQLite_Exec(-1, "CREATE INDEX test ON aTest(a,b,c);") ; Create Test-Index
_SQLite_GetTable2d(-1, "SELECT * FROM sqlite_master", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "Tables+Indices in Memory")

; Usage 1, dump all tables
_SQLite_Dump("dump.sqlite") ; dump all tables to file

; DB handle reset by function to memory-db handle, so you just can continue to work on it with -1

$g_hDB_SQLite_save = $g_hDB_SQLite ; save handle to memory DB for second test

; Example to check dump all to file
_SQLite_Open("dump.sqlite")
_SQLite_GetTable2d(-1, "SELECT * FROM sqlite_master;", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "File dump all tables, Tables+Indices in File")
_SQLite_GetTable2d(-1, "Select * From aTest", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "File dump all tables, aTest")
_SQLite_GetTable2d(-1, "Select * From bTest", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "File dump all tables, bTest")
_SQLite_Close()

$g_hDB_SQLite = $g_hDB_SQLite_save

; Usage 2, dump one table
_SQLite_Dump("dump2.sqlite", "aTest") ; dump one table to file

; Example to check dump to file
_SQLite_Open("dump2.sqlite")
_SQLite_GetTable2d(-1, "SELECT * FROM sqlite_master;", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "File dump one tables, Tables+Indices in File")
_SQLite_GetTable2d(-1, "Select rowid,a,b,c,d From aTest", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "File dump one table")
_SQLite_Close()

_SQLite_Shutdown()


;===============================================================================
;
; Function Name:    _SQLite_Dump($sSQLite_File, $sSQLite_Table = false)
;               AutoIt Version: 3.3.0.0
; Description:    Dumps a SQLite table or Database to a SQLite Database File
; Parameter(s):  $sSQLite_File - Filename of SQLite Database to dump to
;               $sSQLite_Table = false (default) = all tables dumped; "Tablename" to dump a single table
; Author(s):        KaFu
; URL:        http://www.autoitscript.com/forum/index.php?showtopic=81346
; Note(s):   2008-Sep-25 - Created Func
;            2008-Sep-26 - Added code to transfer indices too
;            2009-Apr-07 - Changed code to consider INTEGER PRIMARY KEY AUTOINCREMENT + "sqlite_sequence" problem
;
;===============================================================================
Func _SQLite_Dump($sSQLite_File, $sSQLite_Table = False)
    Local $hSQLite_File, $hQuery, $aRow, $g_hDB_SQLite_save
    Local $create_dump_table
    FileDelete($sSQLite_File)
    $g_hDB_SQLite_save = $g_hDB_SQLite
    $hSQLite_File = _SQLite_Open($sSQLite_File)
    _SQLite_Exec($g_hDB_SQLite_save, "ATTACH DATABASE '" & $sSQLite_File & "' as SQLite_File;")

    _SQLite_Query($g_hDB_SQLite_save, "SELECT name,tbl_name, sql FROM sqlite_master WHERE type = 'table';", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        If $sSQLite_Table And $sSQLite_Table <> $aRow[1] Then ContinueLoop
        If $aRow[0] <> "sqlite_sequence" Then
            $create_dump_table = StringReplace($aRow[2], $aRow[0], "SQLite_File." & $aRow[0])
            _SQLite_Exec($g_hDB_SQLite_save, $create_dump_table & ";")
            _SQLite_Exec($g_hDB_SQLite_save, "INSERT INTO SQLite_File." & $aRow[0] & " SELECT * from " & $aRow[0] & ";")
        EndIf
    WEnd
    
    _SQLite_Query($g_hDB_SQLite_save, "SELECT tbl_name, sql FROM sqlite_master WHERE type = 'index';", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        If $sSQLite_Table And $sSQLite_Table <> $aRow[0] Then ContinueLoop
        _SQLite_Exec($hSQLite_File, $aRow[1] & ";")
    WEnd

    _SQLite_Exec($g_hDB_SQLite_save, "DETACH DATABASE SQLite_File;")
    _SQLite_Close($hSQLite_File)
    $g_hDB_SQLite = $g_hDB_SQLite_save
EndFunc   ;==>_SQLite_Dump

[Dschingis]

@ptrex, KaFu, laffo16:

I would like to thank you very much for this thread. I have learned a bunch for my own projects and i am very impressed with the knowledge you share. Especially the added functionality for loading and dumping a whole database saved my day.

Dschingis

Edited August 12, 2009 by Dschingis

[gcue]

your _SQLiteDump has been such a wonderful function for me =)

it has saved me so much time and headaches.

many many thanks!

Laffo, thanks for pointing me to the "sqlite_sequence" , never heard of that table before ... guess this needs some testing and tweaking, try this:

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3> ; for example output only

Local $aResult, $iRows, $iColumns, $g_hDB_SQLite_save

Opt("MustDeclareVars", 1)

_SQLite_Startup()
_SQLite_Open() ; open :memory: Database, func works fine for file DBs too
_SQLite_Exec(-1, "CREATE TABLE aTest (a,b,c,d INTEGER PRIMARY KEY AUTOINCREMENT);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") ; INSERT Data
_SQLite_Exec(-1, "CREATE TABLE bTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO bTest(a,b,c) VALUES ('x','9','Hello');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO bTest(a,b,c) VALUES ('y','8',' ');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO bTest(a,b,c) VALUES ('z','7','World');") ; INSERT Data
_SQLite_Exec(-1, "CREATE INDEX test ON aTest(a,b,c);") ; Create Test-Index
_SQLite_GetTable2d(-1, "SELECT * FROM sqlite_master", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "Tables+Indices in Memory")

; Usage 1, dump all tables
_SQLite_Dump("dump.sqlite") ; dump all tables to file

; DB handle reset by function to memory-db handle, so you just can continue to work on it with -1

$g_hDB_SQLite_save = $g_hDB_SQLite ; save handle to memory DB for second test

; Example to check dump all to file
_SQLite_Open("dump.sqlite")
_SQLite_GetTable2d(-1, "SELECT * FROM sqlite_master;", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "File dump all tables, Tables+Indices in File")
_SQLite_GetTable2d(-1, "Select * From aTest", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "File dump all tables, aTest")
_SQLite_GetTable2d(-1, "Select * From bTest", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "File dump all tables, bTest")
_SQLite_Close()

$g_hDB_SQLite = $g_hDB_SQLite_save

; Usage 2, dump one table
_SQLite_Dump("dump2.sqlite", "aTest") ; dump one table to file

; Example to check dump to file
_SQLite_Open("dump2.sqlite")
_SQLite_GetTable2d(-1, "SELECT * FROM sqlite_master;", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "File dump one tables, Tables+Indices in File")
_SQLite_GetTable2d(-1, "Select rowid,a,b,c,d From aTest", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "File dump one table")
_SQLite_Close()

_SQLite_Shutdown()


;===============================================================================
;
; Function Name:    _SQLite_Dump($sSQLite_File, $sSQLite_Table = false)
;               AutoIt Version: 3.3.0.0
; Description:    Dumps a SQLite table or Database to a SQLite Database File
; Parameter(s):  $sSQLite_File - Filename of SQLite Database to dump to
;               $sSQLite_Table = false (default) = all tables dumped; "Tablename" to dump a single table
; Author(s):        KaFu
; URL:        http://www.autoitscript.com/forum/index.php?showtopic=81346
; Note(s):   2008-Sep-25 - Created Func
;            2008-Sep-26 - Added code to transfer indices too
;            2009-Apr-07 - Changed code to consider INTEGER PRIMARY KEY AUTOINCREMENT + "sqlite_sequence" problem
;
;===============================================================================
Func _SQLite_Dump($sSQLite_File, $sSQLite_Table = False)
    Local $hSQLite_File, $hQuery, $aRow, $g_hDB_SQLite_save
    Local $create_dump_table
    FileDelete($sSQLite_File)
    $g_hDB_SQLite_save = $g_hDB_SQLite
    $hSQLite_File = _SQLite_Open($sSQLite_File)
    _SQLite_Exec($g_hDB_SQLite_save, "ATTACH DATABASE '" & $sSQLite_File & "' as SQLite_File;")

    _SQLite_Query($g_hDB_SQLite_save, "SELECT name,tbl_name, sql FROM sqlite_master WHERE type = 'table';", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        If $sSQLite_Table And $sSQLite_Table <> $aRow[1] Then ContinueLoop
        If $aRow[0] <> "sqlite_sequence" Then
            $create_dump_table = StringReplace($aRow[2], $aRow[0], "SQLite_File." & $aRow[0])
            _SQLite_Exec($g_hDB_SQLite_save, $create_dump_table & ";")
            _SQLite_Exec($g_hDB_SQLite_save, "INSERT INTO SQLite_File." & $aRow[0] & " SELECT * from " & $aRow[0] & ";")
        EndIf
    WEnd
    
    _SQLite_Query($g_hDB_SQLite_save, "SELECT tbl_name, sql FROM sqlite_master WHERE type = 'index';", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        If $sSQLite_Table And $sSQLite_Table <> $aRow[0] Then ContinueLoop
        _SQLite_Exec($hSQLite_File, $aRow[1] & ";")
    WEnd

    _SQLite_Exec($g_hDB_SQLite_save, "DETACH DATABASE SQLite_File;")
    _SQLite_Close($hSQLite_File)
    $g_hDB_SQLite = $g_hDB_SQLite_save
EndFunc   ;==>_SQLite_Dump

[jchd]

@KaFu,

Nice work but I'm afraid there are common cases where serious problems could arise. Virtual tables, foreign keys, triggers or constraints, all of them may dictate a specific order for loading tables. triggers, sqlite_stat*, views, are left out and possibly other things that escape me right now.

Wouldn't it be much safer to use the now available backup API. I know it's still "experimental" but as I see it there are enough people using it in professional context that I'd be extremely surprised if it would disappear in the future. This API even works while other processes are accessing the DB, read and/or write!

This way, you're not responsible for messing around with a difficult parsing of all kind of constraints to try to infer how re-loading should go to avoid problems.

I was thinking about making a first version of this API wrapper for the latest (3.3.3.3) UDF, but this needed more time than was available. I'll make it probably soon (I need it for my apps).

[Splash]

Good to make backups! ;D

Nice work! ^^

[KaFu]

Wouldn't it be much safer to use the now available backup API.

Yep, fully acknowledged . This was just a hack to have something, worked for my application as I know what type of tables/objects I created. Looking forward for the backup API ...

[jchd]

Looking forward for the backup API ...

I released the first beta here.

Please Kafu, don't believe I'm trying to minimize your work in any way but, as I said, I needed something more robust for my own applications. I only hope it can be improved and serve others as well.

[KaFu]

...don't believe I'm trying to minimize your work in any way but, as I said...

Na, it was just a hack for the time being , will definitely check out the API method cause it should be much, much more reliable and accurate... and implement it on the fly into SMF when (not if ) it proves itself stable.

[gcue]

thanks KaFu for the awesome function - I use it quite often. Recently I wanted to append to a sql database not replace all the contents =)

i added this - do you forsee any issues with it?

Func _SQLite_Dump($sSQLite_File, $sSQLite_Table = False, $sAppend = False)
    Local $hSQLite_File, $hQuery, $aRow, $g_hDB_SQLite_save
    Local $create_dump_table

    If $sAppend = False Then FileDelete($sSQLite_File)

thanks again!

[KaFu]

Hiho, sorry for the late reply, was out of town.

In general this should work fine, just be careful about the "Data Constraints" you use when creating the original tables.

http://www.sqlite.org/lang_createtable.html

I think having duplicates for the PRIMARY KEY or UNIQUE defined columns might result in data not appended correctly.

Also maybe its advisable to replace

_SQLite_Exec($g_hDB_SQLite_save, $create_dump_table & ";")

with

If not $sAppend then _SQLite_Exec($g_hDB_SQLite_save, $create_dump_table & ";")

Regards

[jchd]

I just now wake up to this thread. Dumping an SQLite DB with all schema detail is easily done thanks to the CLI (command line utility) sqlite3.exe via the .dump command.

Experiment with all capabilities of this executable for fast loading a .csv-style file, dumping a DB or restoring one.