Sign in to follow this  
Followers 0
KaFu

_SQLite_Dump

20 posts in this topic

#1 ·  Posted (edited)

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

#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 by KaFu

Share this post


Link to post
Share on other sites



Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by KaFu

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

#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 by KaFu

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

n/m my previous comment. Nice function :)

Edited by wraithdu

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by laffo16

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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 by laffo16

Share this post


Link to post
Share on other sites

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

#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

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

@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 by Dschingis

Share this post


Link to post
Share on other sites

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" :D, never heard of that table before :D... guess this needs some testing and tweaking, try this:

#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

Share this post


Link to post
Share on other sites

@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).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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 :evil:...

Share this post


Link to post
Share on other sites

Looking forward for the backup API :D...

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

...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 :huggles:, 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 :D when (not if : ) it proves itself stable.

Share this post


Link to post
Share on other sites

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!

1 person likes this

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0