Manage arrays by means of SQL

[Gianni]

March 22, 2015 (NEW udf version in >post #12 . slight  enhancement on index and multindex creation)

March 21, 2015 (NEW udf version in >post #12 . slight  enhancement on index creation)

March 15, 2015 (NEW version in >post #9 below. It also allows creation of simple indexes)

March 03, 2015 (NEW improved version in >post #7 below)

This is a simple udf to manage 1D or 2D arrays by means of SQL query.
I hope it can be useful both for simple educational and testing purposes about SQL that also for the management of arrays in general.

New version (it makes use of the new "chain insertion" functionality of SQLite as suggested by jchd in post #5)

expandcollapse popup

; save this as ArraySQL.au3
; V2
#include-once
#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
;
Global Static $g__sSQliteDll = _SQLite_Startup()
Global Static $g__hMemDb = _SQLite_Open()
Global $g__sSQLiteError = "" ; will contains SQL error messages

; #FUNCTION# ====================================================================================================================
; Name...........: _ArraySQL
; Description ...: Allows to execute SQL queryes against 1D or 2D arrays
; Syntax.........: _ArraySQL( ByRef $avArray, $sSQL_Query )
; Parameters ....: $avArray - an 1D or 2D Array to be manage
;                  $sSQL_Query - a string containing the SQL query to execute against the array
;                                the passed array is referred as array as table name in the query
;                                the fields (column(s)) of the array are referred as column0, column1, column2 .... and so on
;
; Return values .: Success: An 1D or 2D array containing data resulting from the execution of the SQL query.
;
;                  Fillure: an empty string and the @error value is set as following
;                           1 - First argument is not an array
;                           2 - not a 1D or 2D array
;                           3 - SQLite error. In this case the $g__sSQLiteError global variable contains the error description.
; ===============================================================================================================================
Func _ArraySQL(ByRef $avArray, $sSQL_Query)
    Local $aResult, $iRows, $iColumns, $iRval, $iError
    $g__sSQLiteError = ""
    __ArrayToSqlTable($avArray) ; clone $avArray to a temporary sql db table (memory resident)
    ;                             name of the temp table will be array
    ;                             name of the filed(s) will be column0 column1 column2 columnN and so on ....
    ;
    If @error Then Return SetError(@error, 0, "") ; something went wrong
    ; now execute the SQL query
    $iRval = _SQLite_GetTable2d(-1, $sSQL_Query, $aResult, $iRows, $iColumns)
    If Not $iRval = $SQLITE_OK Then ; an SQLite error occurred
        $g__sSQLiteError = _SQLite_ErrMsg()
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, 0, "")
    Else
        $g__sSQLiteError = ""
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(0, 0, $aResult)
    EndIf
EndFunc   ;==>_ArraySQL
;
; #INTERNAL USE# ================================================================================================================
; Name...........: __ArrayToSqlTable
; Description ...: clone $avArray to a temporary SQLite db table (memory resident)
; Syntax.........: __ArrayToSqlTable ( ByRef $avArray)
; Parameters ....: $avArray  - Array to clone to the SQLite temporary table
;
; Remarks .......: For Internal Use Only
; ===============================================================================================================================
Func __ArrayToSqlTable(ByRef $avArray) ; Pass an array to a temporary SQL table
    If Not IsArray($avArray) Then Return SetError(1, 0, 0) ; must be an array
    Local $iDimensions = UBound($avArray, 0)
    If $iDimensions > 2 Then Return SetError(2, 0, 0) ; must be an 1D or 2D array
    Local $aAutoItV = StringSplit(@AutoItVersion, ".", 2), $nAutoItVersion = StringFormat("%03i%03i%03i%03i", $aAutoItV[0], $aAutoItV[1], $aAutoItV[2], $aAutoItV[3])
    Local $iStep = 10 ; the number of records we want to load in each chain <---- the chain length
    If $nAutoItVersion < 3003012000 Then $iStep = 1 ; if an older version of AutoIt then do not use the new SQLite "chain insertion"
    Local $sDBfields = "" ; will hold the names of the fields (column0, column1, column2......) (zero based)
    Local $vDBvalues = "" ; will hold the values of a field (1, 'aaa', 456)
    Local $vDBvaluesChain = "" ; will hold the chain of n values (1, 'aaa', 456), (2, 'bbb', 147), ... , (235, 'xyz', 999)
    Local $iRecords = UBound($avArray, 1) ; total number of rows in the array
    Local $iLastRecord = $iRecords - 1
    Local $iChains = Ceiling($iRecords / $iStep) ; how many chains we will fill?
    Local $nRemainders = Mod($iRecords, $iStep) ; are there rmainders? (if so the last chain will be only partly full)
    Local $iFields = 0 ; we suppose an 1D array
    If $iDimensions = 2 Then ; if is a 2D array instead
        $iFields = UBound($avArray, 2) - 1 ; number of fields (columns) in the 2D array (zero based)
    EndIf
    For $x = 0 To $iFields
        $sDBfields &= "column" & String($x) & ","
    Next
    $sDBfields = StringTrimRight($sDBfields, 1) ; remove the last comma

    If Not _SQLite_Exec(-1, "CREATE TEMP TABLE array (" & $sDBfields & ");") = $SQLITE_OK Then
        $g__sSQLiteError = _SQLite_ErrMsg()
        ; _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, 0, "")
    Else

        #cs suggestion by jchd -> http://www.autoitscript.com/forum/topic/166536-manage-arrays-by-means-of-sql/?p=1216694
            For maximizing efficiency of table population using INSERT, you can use the "chain insertion" new syntax:
            insert into mytable (a,b,c) values (1, 'aaa', 456), (2, 'bbb', 147), (3, 'ccc', 258), ... , (235, 'xyz', 999)
            You need to keep the query string under the size limit (depends on compile-time options)
            but you can get N-fold (for some value N) improvement on insert speed with that simple trick.
        #ce

        If Not $nRemainders Then ; there are no remainder records.
            ;                      then we can load all chains (with group of records)
            ;                      and we will have no residual records to manage
            For $x = 0 To $iLastRecord Step $iStep ; we read records in groups of $iStep
                $vDBvaluesChain = ""
                For $iRecord = $x To $x + $iStep - 1
                    $vDBvalues = ""
                    ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                    $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
                Next
                $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
                ; insert chain to table
                ___InsertChain($sDBfields, $vDBvaluesChain)
            Next
            ;
        Else ; if we are here is because there are remainders, so:
            If $iChains - 1 Then ; if there are more than 1 chain (otherwise if records are less than $istep read only remainders)
                For $x = 0 To $iLastRecord - $nRemainders Step $iStep
                    $vDBvaluesChain = ""
                    For $iRecord = $x To $x + $iStep - 1
                        $vDBvalues = ""
                        ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                        $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
                    Next
                    $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
                    ___InsertChain($sDBfields, $vDBvaluesChain)
                Next
            EndIf
            ; -- now read remainders -----
            $vDBvaluesChain = ""
            For $iRecord = $iLastRecord - $nRemainders + 1 To $iLastRecord ; Step $iStep
                $vDBvalues = ""
                ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
            Next
            $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
            ___InsertChain($sDBfields, $vDBvaluesChain)
        EndIf
    EndIf
EndFunc   ;==>__ArrayToSqlTable

Func ___BuildRecord(ByRef $avArray, ByRef $iFields, ByRef $vDBvalues, ByRef $x)
    For $y = 0 To $iFields
        Switch $iFields
            Case 0 ; just 1 field (1D Array)
                If IsNumber($avArray[$x]) Then
                    $vDBvalues &= $avArray[$x] & ","
                Else
                    $vDBvalues &= _SQLite_FastEscape($avArray[$x]) & ","
                EndIf
            Case Else ; multi fields (2D Array)
                If IsNumber($avArray[$x][$y]) Then
                    $vDBvalues &= $avArray[$x][$y] & ","
                Else
                    $vDBvalues &= _SQLite_FastEscape($avArray[$x][$y]) & ","
                EndIf
        EndSwitch
    Next
    $vDBvalues = StringTrimRight($vDBvalues, 1) ; remove last comma
EndFunc   ;==>___BuildRecord

Func ___InsertChain(ByRef $sDBfields, ByRef $vDBvaluesChain)
    If Not _SQLite_Exec(-1, "INSERT INTO array (" & $sDBfields & ") VALUES (" & $vDBvaluesChain & ");") = $SQLITE_OK Then
        $g__sSQLiteError = _SQLite_ErrMsg()
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, 0, "")
    EndIf
EndFunc   ;==>___InsertChain

previous version is in the spoiler

 

expandcollapse popup

; save this as ArraySQL.au3
#include-once
#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
;
Global Static $g__sSQliteDll = _SQLite_Startup()
Global Static $g__hMemDb = _SQLite_Open()
Global $g__sSQLiteError = ""

; #FUNCTION# ====================================================================================================================
; Name...........: _ArraySQL
; Description ...: Allows to execute SQL queryes against 1D or 2D arrays
; Syntax.........: _ArraySQL( ByRef $avArray, $sSQL_Query )
; Parameters ....: $avArray - an 1D or 2D Array to be manage
;                  $sSQL_Query - a string containing the SQL query to execute against the array
;                                the passed array is referred as array as table name in the query
;                                the fields (column(s)) of the array are referred as column0, column1, column2 .... and so on
;
; Return values .: Success: An 1D or 2D array containing data resulting from the execution of the SQL query.
;
;                  Fillure: an empty string and the @error value is set as following
;                           1 - First argument is not an array
;                           2 - not a 1D or 2D array
;                           3 - SQLite error. In this case the $g__sSQLiteError global variable contains the error description.
; ===============================================================================================================================
Func _ArraySQL(ByRef $avArray, $sSQL_Query)
    Local $aResult, $iRows, $iColumns, $iRval, $iError
    $g__sSQLiteError = ""
    __ArrayToSqlTable($avArray) ; clone $avArray to a temporary sql db table (memory resident)
    ;                            name of the temp table will be array
    ;                            name of the filed(s) will be column0 column1 column2 columnN and so on ....
    ;
    If @error Then Return SetError(@error, 0, "") ; something went wrong
    ; now execute the SQL query
    $iRval = _SQLite_GetTable2d(-1, $sSQL_Query, $aResult, $iRows, $iColumns)
    If Not $iRval = $SQLITE_OK Then ; an SQLite error occurred
        $g__sSQLiteError = _SQLite_ErrMsg()
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, 0, "")
    Else
        $g__sSQLiteError = ""
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(0, 0, $aResult)
    EndIf
EndFunc   ;==>_ArraySQL

; #INTERNAL USE# ================================================================================================================
; Name...........: __ArrayToSqlTable
; Description ...: clone $avArray to a temporary SQLite db table (memory resident)
; Syntax.........: __ArrayToSqlTable ( ByRef $avArray)
; Parameters ....: $avArray  - Array to clone to the SQLite temporary table
;
; Remarks .......: For Internal Use Only
; ===============================================================================================================================
Func __ArrayToSqlTable(ByRef $avArray) ; Pass an array to a temporary SQL table
    If Not IsArray($avArray) Then Return SetError(1, 0, 0) ; must be an array
    Local $Dimensions = UBound($avArray, 0)
    If $Dimensions > 2 Then Return SetError(2, 0, 0) ; must be an 1D or 2D array
    Local $sDBfields = ""
    Local $vDBvalues = ""
    Local $iRecords = UBound($avArray, 1) - 1
    Local $iFields = 0
    If $Dimensions = 2 Then
        $iFields = UBound($avArray, 2) - 1
    EndIf
    For $x = 0 To $iFields
        $sDBfields &= "column" & String($x) & ","
    Next
    $sDBfields = StringTrimRight($sDBfields, 1) ; remove the last comma

    If Not _SQLite_Exec(-1, "CREATE TEMP TABLE array (" & $sDBfields & ");") = $SQLITE_OK Then
        $g__sSQLiteError = _SQLite_ErrMsg()
        ; _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, 0, "")
    Else
        For $x = 0 To $iRecords
            For $y = 0 To $iFields
                Switch $iFields
                    Case 0 ; just 1 field (1D Array)
                        If IsNumber($avArray[$x]) Then
                            $vDBvalues &= $avArray[$x] & ","
                        Else
                            $vDBvalues &= _SQLite_FastEscape($avArray[$x]) & ","
                        EndIf
                    Case Else ; multi fields (2D Array)
                        If IsNumber($avArray[$x][$y]) Then
                            $vDBvalues &= $avArray[$x][$y] & ","
                        Else
                            $vDBvalues &= _SQLite_FastEscape($avArray[$x][$y]) & ","
                        EndIf
                EndSwitch
            Next
            $vDBvalues = StringTrimRight($vDBvalues, 1) ; remove last comma
            ; insert row to table
            If Not _SQLite_Exec(-1, "INSERT INTO array VALUES (" & $vDBvalues & ");") = $SQLITE_OK Then
                $g__sSQLiteError = _SQLite_ErrMsg()
                _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
                Return SetError(3, 0, "")
            EndIf
            $vDBvalues = ""
        Next
    EndIf
EndFunc   ;==>__ArrayToSqlTable

 

 

A simple example of use:

#include <ArraySQL.au3>
Local $x, $y, $sQuery, $aResult
; ------------------------------------------------------------
; populate an array with random data to be managed for example
Local $Array[1000][5]
For $x = 0 To 999
    For $y = 0 To 4
        $Array[$x][$y] = Random(0, 100, 1)
    Next
Next
; ------------------------------------------------------------
; returns only rows where first column (column0) contains a value < 10
$sQuery = "SELECT * FROM array WHERE column0 < 10;"
$aResult = _ArraySQL($Array, $sQuery)
If Not @error Then
    _ArrayDisplay($aResult, "only data < 10 in column 0")
Else
    MsgBox(0, "error", $g__sSQLiteError)
EndIf
;
; returns all rows ordered by first column (column0) and then fourth by column (column3)
$sQuery = "SELECT * FROM array ORDER BY column0,column3;"
$aResult = _ArraySQL($Array, $sQuery)
If Not @error Then
    _ArrayDisplay($aResult, "Ordered by column 0 and 3")
Else
    MsgBox(0, "error", $g__sSQLiteError)
EndIf

P.S.

improvements, corrections and suggestions are welcome

Edited July 25, 2015 by Chimp

[UEZ]

Very interesting approach to handle arrays using SQL. 

 

Br,

UEZ

[SmOke_N]

I played with a similar/more hard coded concept:

'?do=embed' frameborder='0' data-embedContent>>

[Gianni]

Very interesting approach to handle arrays using SQL. 

 

Br,

UEZ

 

Thank You!

 

I played with a similar/more hard coded concept:

'?do=embed' frameborder='0' data-embedContent>>

I see, you were simulating the _ArrayUnique and _ArraySort using SQLite to check the speed performances of both ways

well, you can easly achieve the same result here.

just pass this query to simulate _ArrayUnique

"SELECT DISTINCT column0 FROM array;"

and this other query to simulate _ArraySort

"SELECT * FROM array ORDER BY column0"

anyway I think that the interesting thing here is not about the execution speed, but the enormous versatility of management on the array's data given by SQL.

With it you can not only simulate many of the _Array* functions, but even perform many others kind of powerfull managements allowed easly by SQL

[jchd]

All,

I can't be overconvinced that SQLite is a superior application data container. In some 95% of use cases it offers a number of advantages over an ad-hoc file format, yet opens the power of SQL + user functions to routine uses, including versatility, reliability and real-time safe shareability to several processes.

Let me just add that with recent SQLite versions you can use CTEs (Common Table Expressions) possibly recursively. This for instance can let you solve hard sudokus fast and with only one query, something you would have some hard time coding in applicative language. CTEs can access tree-type structures efficiently, like genealogy trees or the like.

 

Chimp,

For maximizing efficiency of table population using INSERT, you can use the "chain insertion" new syntax:

insert into mytable (a,b,c) values (1, 'aaa', 456), (2, 'bbb', 147), (3, 'ccc', 258), ... , (235, 'xyz', 999)

You need to keep the query string under the size limit (depends on compile-time options) but you can get N-fold (for some value N) improvement on insert speed with that simple trick.

[Gianni]

.....

Chimp,

For maximizing efficiency of table population using INSERT, you can use the "chain insertion" new syntax:

insert into mytable (a,b,c) values (1, 'aaa', 456), (2, 'bbb', 147), (3, 'ccc', 258), ... , (235, 'xyz', 999)

You need to keep the query string under the size limit (depends on compile-time options) but you can get N-fold (for some value N) improvement on insert speed with that simple trick.

 

Thanks jchd for this good suggestion.

I tryed this new way of insertion and, as you said, it gives better performance.

I modified my script to make use of this new syntax.

Since the new syntax only works in new version of AutoIt, and since I would like that this script could be used also on previous versions of AutoIt, I inserted code in the script that allow automatically to make use of the new functionality only if it's run on a new version of AutoIt, (I've setted to insert 10 records at a time). Otherwise, the "classic" way of INSERT one record at time will be used.

the new code has been added to the first post and is compatible with the example already present (just use the new udf instead the previous)

Edited January 8, 2015 by Chimp

[Gianni]

Here a new version of this toy with a little improvement on previous (however yet compatible with the previous version).

Previous version is intended to be used mainly with the _ArraySql() function.
Simply and easily passing an array and an sql query is enough to quickly perform the sql query against the array. It returns an array containing the data resulting from the sql query.
The needed temp table is implicitly created in the db and automatically deleted when the function execution is completed.
Although this is the easiest way of use, it limits the ways you can use the function, that is, it allows you to manage only one table per function call.
Also, if you need to perform more queries against the same array, the temp table is generated and deleted for every function call. This causes a waste of time in that cases.

This new version has a slight enhancement that allows you to run sql queries versus multiple tables instead of against a single one table.

In short:
two new functions have been introduced:  _ArrayToDbTable and _ArrayFromSQL
The  _ArrayToDbTable function simply allows you to create a "permanent" table in the memory db from any 1D or 2D array. This table remains in the db untill you explicitly will delete it for example by a command like this  _SQLite_Exec($g__hMemDb, "DROP TABLE yourtablename") or, otherwise,  untill the end of the main program.
In this way you can "prepare" all the tables needed to be used in multi-tables queries. Anyway, it can also be usefull for creation of just a single table to be reused more times.

The _ArrayFromSQL() function allows you to easly perform SQL queries against tables that are stored in the memory db. Data resulting from the execution of the query are returned into an array.

expandcollapse popup

; save this as ArraySQL.au3
;
; this version allows the creation of "permanent" tables in the memory db
; allowing sql queries against more tables instead just one table at time
; this by using the 2 new functions:  _ArrayToDbTable and _ArrayFromSQL
; (see functions infos for details)
;
#include-once
#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
;
Global Static $g__sSQliteDll = _SQLite_Startup()
Global Static $g__hMemDb = _SQLite_Open()
Global $g__sSQLiteError = "" ; will contains SQL error messages

; #FUNCTION# ====================================================================================================================
; Name...........: _ArraySQL
; Description ...: Allows to execute SQL queryes against 1D or 2D arrays
; Syntax.........: _ArraySQL( ByRef $avArray, $sSQL_Query )
; Parameters ....: $avArray - an 1D or 2D Array to be manage
;                  $sSQL_Query - a string containing the SQL query to execute against the array
;                                the passed array is referred as array as table name in the query
;                                the fields (column(s)) of the array are referred as column0, column1, column2 .... and so on
;
; Return values .: Success: An 1D or 2D array containing data resulting from the execution of the SQL query.
;
;                  Fillure: an empty string and the @error value is set as following
;                           1 - First argument is not an array
;                           2 - not a 1D or 2D array
;                           3 - SQLite error. In this case the $g__sSQLiteError global variable contains the error description.
; ===============================================================================================================================
Func _ArraySQL(ByRef $avArray, $sSQL_Query)
    Local $aResult, $iRows, $iColumns, $iRval, $iError, $hTimer = TimerInit()
    $g__sSQLiteError = ""
    __ArrayToSqlTable($avArray) ; clone $avArray to a temporary sql db table (memory resident)
    ;                             name of the temp table will be array
    ;                             name of the filed(s) will be column0 column1 column2 columnN and so on ....
    ;
    If @error Then
        $iError = @error
        Return SetError($iError, TimerDiff($hTimer), "") ; something went wrong
    EndIf
    ; now execute the SQL query
    $iRval = _SQLite_GetTable2d(-1, $sSQL_Query, $aResult, $iRows, $iColumns)
    If Not $iRval = $SQLITE_OK Then ; an SQLite error occurred
        $g__sSQLiteError = _SQLite_ErrMsg()
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, TimerDiff($hTimer), "")
    Else
        $g__sSQLiteError = ""
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(0, TimerDiff($hTimer), $aResult)
    EndIf
EndFunc   ;==>_ArraySQL

; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayToDbTable
; Description ...: clones $avArray to a "permanent" SQLite db table (memory resident)
; Syntax.........: _ArrayToDbTable ( ByRef $avArray, $sTableName [,$iStep = 100] )
; Parameters ....: $avArray    -  Array to clone to the SQLite table
;
;                  $sTableName -  Is the name of the table that will be created in the memory db
;                                 note:
;                                 The name "array" is not allowed because is reserved to the default table used by _ArraySQL()
;
;                  $iStep      -  Optional. Default is 100. This is the number of records inserted in the table for every SQLite write
;                                 operation, that is the so called "chain insertion". This parameter is used only on recent versions
;                                 of AutoIt while is ignored on older versions for compatibility.
;                                 (Only one record at time is written in this second case)
;
; Return values .: Success:    -  @error = 0
;                                 @extended contains the milliseconds spent by the table creation
;
;                  Fillure:    -  Sets @error as following:
;                                 1 - First argument is not an array
;                                 2 - Array is not an 1D or 2D array
;                                 3 - SQLite error (the $g__sSQLiteError variable contains error description)
;                                 4 - The table name "array" is a reserved name not allowed with this function
; ===============================================================================================================================
Func _ArrayToDbTable(ByRef $avArray, $sTableName, $iStep = 100)
    If $sTableName = "array" Then Return SetError(4, 0, "")
    __ArrayToSqlTable($avArray, $sTableName, $iStep)
    Return SetError(@error, @extended, "")
EndFunc   ;==>_ArrayToDbTable

; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayFromSQL
; Description ...: Allows to execute SQL queryes against table(s) in the Temp db and get the resulting table returned in an array
; Syntax.........: _ArrayFromSQL( $sSQL_Query )
; Parameters ....: $sSQL_Query - a string containing the SQL query to execute against the temp db
;                                it's up to you to pass a correct query with existing temp tablenames.
;                                P.S. (Related info)
;                                You can previously create required temp tables by the _ArrayToDbTable() function
;                                passing an array and a tablename to be created in the temp db (see function info for details)
;
; Return values .: Success: An 1D or 2D array containing data resulting from the execution of the SQL query.
;
;                  Fillure: an empty string and the @error value is set as following
;                           3 - SQLite error. In this case the $g__sSQLiteError global variable contains the error description.
; ===============================================================================================================================
Func _ArrayFromSQL($sSQL_Query)
    Local $aResult, $iRows, $iColumns, $iRval, $iError, $hTimer = TimerInit()
    $g__sSQLiteError = ""
    ; now execute the SQL query
    If _SQLite_GetTable2d(-1, $sSQL_Query, $aResult, $iRows, $iColumns) = $SQLITE_OK Then ; execution of query is OK
        Return SetError(0, TimerDiff($hTimer), $aResult)
    Else ; an SQLite error occurred
        $g__sSQLiteError = _SQLite_ErrMsg()
        Return SetError(3, TimerDiff($hTimer), "")
    EndIf
EndFunc   ;==>_ArrayFromSQL
;
; #INTERNAL USE# ================================================================================================================
; Name...........: __ArrayToSqlTable
; Description ...: clone $avArray to a temporary SQLite db table (memory resident)
; Syntax.........: __ArrayToSqlTable ( ByRef $avArray [$sTableName [,$iStep]])
; Parameters ....: $avArray    -  Array to clone to the SQLite temporary table
;                  $sTableName -  Optional. Default is "array". Is the name of the table that will be created in the temp db
;                                 This function is used mainly by the _ArraySQL() function, and the "array" table created by default
;                                 will be automatically deleted after the execution of the query passed by _ArraySQL().
;                                 Optionally, if you create a table with this parameter instead, assigning a different name to the table
;                                 it will remain in the temp db till end of program execution or till you explicitly delete it using the
;                                 _SQLite_Exec($g__hMemDb, "DROP TABLE yourtablename") for example.
;                  $iStep      -  Optional. Default is 100. This is the number of records inserted in the table for every SQLite write
;                                 operation, that is the so called "chain insertion". This parameter is used only on recent versions
;                                  of AutoIt while is ignored on older versions for compatibility problems.
;                                 (Only one record at time is written in this second case)
;
; Return values .: Success:    -  @error = 0
;                                 @extended contains the milliseconds spent by the table creation
;
;                  Fillure:    -  Sets @error as following:
;                                 1 - First argument is not an array
;                                 2 - Array is not an 1D or 2D array
;                                 3 - SQLite error (the $g__sSQLiteError variable contains error description)
;
; Remarks .......: Mainly for Internal use (but not only)
; ===============================================================================================================================
Func __ArrayToSqlTable(ByRef $avArray, $sTableName = "array", $iStep = 100) ; Pass an array to a temporary SQL table in memory db
    Local $hTimer = TimerInit()
    If Not IsArray($avArray) Then Return SetError(1, TimerDiff($hTimer), 0) ; must be an array
    Local $iDimensions = UBound($avArray, 0)
    If $iDimensions > 2 Then Return SetError(2, TimerDiff($hTimer), 0) ; must be an 1D or 2D array
    Local $aAutoItV = StringSplit(@AutoItVersion, ".", 2), $nAutoItVersion = StringFormat("%03i%03i%03i%03i", $aAutoItV[0], $aAutoItV[1], $aAutoItV[2], $aAutoItV[3])
    ; Local $iStep = 10 ; the number of records we want to load in each chain <---- the chain length
    If $nAutoItVersion < 3003012000 Then $iStep = 1 ; if an older version of AutoIt then do not use the new SQLite "chain insertion"
    Local $sDBfields = "" ; will hold the names of the fields (column0, column1, column2......) (zero based)
    Local $vDBvalues = "" ; will hold the values of a field (1, 'aaa', 456)
    Local $vDBvaluesChain = "" ; will hold the chain of n values (1, 'aaa', 456), (2, 'bbb', 147), ... , (235, 'xyz', 999)
    Local $iRecords = UBound($avArray, 1) ; total number of rows in the array
    Local $iLastRecord = $iRecords - 1
    Local $iChains = Ceiling($iRecords / $iStep) ; how many chains we will fill?
    Local $nRemainders = Mod($iRecords, $iStep) ; are there rmainders? (if so the last chain will be only partly full)
    Local $iFields = 0 ; we suppose an 1D array
    If $iDimensions = 2 Then ; if is a 2D array instead
        $iFields = UBound($avArray, 2) - 1 ; number of fields (columns) in the 2D array (zero based)
    EndIf
    For $x = 0 To $iFields
        $sDBfields &= "column" & String($x) & ","
    Next
    $sDBfields = StringTrimRight($sDBfields, 1) ; remove the last comma

    If Not _SQLite_Exec(-1, "CREATE TEMP TABLE " & $sTableName & " (" & $sDBfields & ");") = $SQLITE_OK Then
        $g__sSQLiteError = _SQLite_ErrMsg()
        ; _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, TimerDiff($hTimer), "")
    Else

        #cs suggestion by jchd -> http://www.autoitscript.com/forum/topic/166536-manage-arrays-by-means-of-sql/?p=1216694
            For maximizing efficiency of table population using INSERT, you can use the "chain insertion" new syntax:
            insert into mytable (a,b,c) values (1, 'aaa', 456), (2, 'bbb', 147), (3, 'ccc', 258), ... , (235, 'xyz', 999)
            You need to keep the query string under the size limit (depends on compile-time options)
            but you can get N-fold (for some value N) improvement on insert speed with that simple trick.
        #ce

        If Not $nRemainders Then ; there are no remainder records.
            ;                      then we can load all chains (with group of records)
            ;                      and we will have no residual records to manage
            For $x = 0 To $iLastRecord Step $iStep ; we read records in groups of $iStep
                $vDBvaluesChain = ""
                For $iRecord = $x To $x + $iStep - 1
                    $vDBvalues = ""
                    ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                    $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
                Next
                $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
                ; insert chain to table
                ___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
                If @error Then Return SetError(3, TimerDiff($hTimer), "")
            Next
            ;
        Else ; if we are here is because there are remainders, so:
            If $iChains - 1 Then ; if there are more than 1 chain (otherwise if records are less than $istep read only remainders)
                For $x = 0 To $iLastRecord - $nRemainders Step $iStep
                    $vDBvaluesChain = ""
                    For $iRecord = $x To $x + $iStep - 1
                        $vDBvalues = ""
                        ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                        $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
                    Next
                    $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
                    ___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
                    If @error Then Return SetError(3, TimerDiff($hTimer), "")
                Next
            EndIf
            ; -- now read remainders -----
            $vDBvaluesChain = ""
            For $iRecord = $iLastRecord - $nRemainders + 1 To $iLastRecord ; Step $iStep
                $vDBvalues = ""
                ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
            Next
            $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
            ___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
            If @error Then Return SetError(3, TimerDiff($hTimer), "")
        EndIf
    EndIf
    Return SetError(0, TimerDiff($hTimer), "")
EndFunc   ;==>__ArrayToSqlTable

Func ___BuildRecord(ByRef $avArray, ByRef $iFields, ByRef $vDBvalues, ByRef $x)
    For $y = 0 To $iFields
        Switch $iFields
            Case 0 ; just 1 field (1D Array)
                If IsNumber($avArray[$x]) Then
                    $vDBvalues &= $avArray[$x] & ","
                Else
                    $vDBvalues &= _SQLite_FastEscape($avArray[$x]) & ","
                EndIf
            Case Else ; multi fields (2D Array)
                If IsNumber($avArray[$x][$y]) Then
                    $vDBvalues &= $avArray[$x][$y] & ","
                Else
                    $vDBvalues &= _SQLite_FastEscape($avArray[$x][$y]) & ","
                EndIf
        EndSwitch
    Next
    $vDBvalues = StringTrimRight($vDBvalues, 1) ; remove last comma
EndFunc   ;==>___BuildRecord

Func ___InsertChain(ByRef $sDBfields, ByRef $vDBvaluesChain, ByRef $sTableName)
    If Not _SQLite_Exec(-1, "INSERT INTO " & $sTableName & " (" & $sDBfields & ") VALUES (" & $vDBvaluesChain & ");") = $SQLITE_OK Then
        $g__sSQLiteError = _SQLite_ErrMsg()
        _SQLite_Exec($g__hMemDb, "DROP TABLE " & $sTableName) ; delete the temporary table
        Return SetError(3, 0, "")
    EndIf
EndFunc   ;==>___InsertChain

p.s.
Of course the target of this "toy" is for a quick and easy management of data in arrays, as well for testing of sql queries and/or didactical purpose.
needless to say that there is no claim to handle very large amounts of data.

As a simple example of use on multi tables (just 2 for this example), I will use a possible solution to a problem posted few days ago in the forum "General Help and Support" in >this topic.

1) Generate 2 "permanent" tables in the memory db slightly different each other, the first containing a list of filenames in the window directory excluding all the *.exe;  the second the same list excluding all the *.ico instead
2) Run an SQL query that uses both tables to find the differences of the first on the second and viceversa

the most waste of time is due to the retrieval of filenames by the _FileListToArrayRec() function (nothing to do with the quicker management of data performed by the sql query on next steps)

expandcollapse popup

#include <File.au3>
#include <ArraySQL.au3> ; this also include array.au3

Example()

Func Example()
    Local $sWinDir = @WindowsDir, $hTimer = TimerInit()
    ; A retrieve list of all files in the windows directory and subdirs but the *.exe
    ConsoleWrite("1) start of retrieving first list of filenames from disk with the '_FileListToArrayRec()' function ")
    Local $aArray = _FileListToArrayRec($sWinDir, "*|*.exe", $FLTAR_FILES, $FLTAR_RECUR)
    ConsoleWrite(@CRLF & "   time spent to retrieve first list of " & UBound($aArray) & " file names, ms: " & Int(TimerDiff($hTimer)) & @CRLF & @CRLF)
    ConsoleWrite("2) start of writing filenames to the table 'array1' in the SQLite memory db" & @CRLF)
    _ArrayToDbTable($aArray, "array1") ; <----- write array to the SQLite memory db in tablename array1 **********
    ConsoleWrite("   time spent to write first list of files to the table in db (array1), ms: " & @extended & @CRLF & @CRLF)
    ;
    ; B retrieve list of all files in the windows directory and subdirs but the *.ico
    $hTimer = TimerInit()
    ConsoleWrite("3) start of retrieving second list of filenames from disk with the '_FileListToArrayRec()' function ")
    $aArray = _FileListToArrayRec($sWinDir, "*|*.ico", $FLTAR_FILES, $FLTAR_RECUR)
    ConsoleWrite(@CRLF & "   time spent to retrieve second list of " & UBound($aArray) & " file names, ms: " & Int(TimerDiff($hTimer)) & @CRLF & @CRLF)
    ConsoleWrite("4) start of writing filenames to the table 'array2' in the SQLite memory db" & @CRLF)
    _ArrayToDbTable($aArray, "array2") ; <----- write array to the SQLite memory db in tablename array2 **********
    ConsoleWrite("   time spent to write second list of files to the table in db (array2), ms: " & @extended & @CRLF & @CRLF)
    ;
    ConsoleWrite("5) start of execution of the first sql query using both of above tables" & @CRLF)
    ; --------------------------------------------------------------------------------------
    ; following query will find only filenames that are in first list but not in the second
    ; by jchd from here --> http://www.autoitscript.com/forum/topic/161868-compare-array-to-sqlite-db-contents/?p=1175483
    $aArray = _ArrayFromSQL("Select * from array1 except select * from array2;") ; <--- execute sql query and retrieve result in $aArray
    ; --------------------------------------------------------------------------------------
    ConsoleWrite("   time spent to execute first sql query, ms: " & @extended & @CRLF & @CRLF)
    If @error Then
        MsgBox(0, @error, $g__sSQLiteError)
    Else
        _ArrayDisplay($aArray, "Files that are in the first list but not in the second")
    EndIf
    ;
    ConsoleWrite("6) start of execution of the second sql query using both of above tables" & @CRLF)
    ; --------------------------------------------------------------------------------------
    ; following query will find filenames that are in second list but not in the first
    ; by jchd from here --> http://www.autoitscript.com/forum/topic/161868-compare-array-to-sqlite-db-contents/?p=1175483
    $aArray = _ArrayFromSQL("Select * from array2 except select * from array1;") ; <--- execute sql query and retrieve result in $aArray
    ; --------------------------------------------------------------------------------------
    ConsoleWrite("   Time spent to execute second sql query, ms: " & @extended & @CRLF)
    If @error Then
        MsgBox(0, @error, $g__sSQLiteError)
    Else
        _ArrayDisplay($aArray, "Files that are in the second list but not in the first")
    EndIf
EndFunc   ;==>Example

on my system (cpu i5  2.9GHz) i have following results:
- generate of 2 list of filenames from disk directory about 100.000 names per list in 18 seconds for each list (most waste of time)
- writing of the above list to the memory SQLite db about 5 second per list
- execution of the sql query against above 2 tables about 5 seconds

hope you have fun on experimenting SQL queries.

Edited March 10, 2015 by Chimp

[Zedna]

Nice

Advice for improvement:

- add optional parameter "index" to _ArrayToDbTable()

Edited March 10, 2015 by Zedna

[Gianni]

Nice 

.....

 

Thanks

....

Advice for improvement:

- add optional parameter "index" to _ArrayToDbTable()

 

Thanks for this advice....

although if creation of indexes with the _SQLite_Exec() native command in a way like this:

_SQLite_Exec($g__hMemDb, "CREATE INDEX .......;"), is the master way to build articulated indexes,

it can also be convenient add an optional parameter to the _ArrayToDbTable() function to allow the creation of

simple basic indexes on one or more columns during that function call. I think that a way as the following may be fine:

set the optional $sIndex parameter in this way:

just one, or more comma separated numbers (zero based), that indicate on which columns you want to create the index.

example: $sIndex = "2,5,7" will create 3 Single-Column Indexes on the table that is being created. Index names are as follows:

tablename_ndx2 tablename_ndx5 Tablename_ndx7.

Here is the modified udf

expandcollapse popup

; save this as ArraySQL.au3
;
; this version allows the creation of "permanent" tables in the memory db
; allowing sql queries against more tables instead just one table at time
; this by using the 2 new functions:  _ArrayToDbTable and _ArrayFromSQL
; (see functions infos for details)
;
; March 15, 2015
; added a new optional parameter in the _ArrayToDbTable function that allows
; the creation of one or more simple single-column indexes
;
#include-once
#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
;
Global Static $g__sSQliteDll = _SQLite_Startup()
Global Static $g__hMemDb = _SQLite_Open()
Global $g__sSQLiteError = "" ; will contains SQL error messages

; #FUNCTION# ====================================================================================================================
; Name...........: _ArraySQL
; Description ...: Allows to execute SQL queryes against 1D or 2D arrays
; Syntax.........: _ArraySQL( ByRef $avArray, $sSQL_Query )
; Parameters ....: $avArray - an 1D or 2D Array to be manage
;                  $sSQL_Query - a string containing the SQL query to execute against the array
;                                the passed array is referred as array as table name in the query
;                                the fields (column(s)) of the array are referred as column0, column1, column2 .... and so on
;
; Return values .: Success: An 1D or 2D array containing data resulting from the execution of the SQL query.
;
;                  Fillure: an empty string and the @error value is set as following
;                           1 - First argument is not an array
;                           2 - not a 1D or 2D array
;                           3 - SQLite error. In this case the $g__sSQLiteError global variable contains the error description.
; ===============================================================================================================================
Func _ArraySQL(ByRef $avArray, $sSQL_Query)
    Local $aResult, $iRows, $iColumns, $iRval, $iError, $hTimer = TimerInit()
    $g__sSQLiteError = ""
    __ArrayToSqlTable($avArray) ; clone $avArray to a temporary sql db table (memory resident)
    ;                             name of the temp table will be array
    ;                             name of the filed(s) will be column0 column1 column2 columnN and so on ....
    ;
    If @error Then
        $iError = @error
        Return SetError($iError, TimerDiff($hTimer), "") ; something went wrong
    EndIf
    ; now execute the SQL query
    $iRval = _SQLite_GetTable2d(-1, $sSQL_Query, $aResult, $iRows, $iColumns)
    If Not $iRval = $SQLITE_OK Then ; an SQLite error occurred
        $g__sSQLiteError = _SQLite_ErrMsg()
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, TimerDiff($hTimer), "")
    Else
        $g__sSQLiteError = ""
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(0, TimerDiff($hTimer), $aResult)
    EndIf
EndFunc   ;==>_ArraySQL

; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayToDbTable
; Description ...: clones $avArray to a "permanent" SQLite db table (memory resident)
; Syntax.........: _ArrayToDbTable ( ByRef $avArray, $sTableName [$sIndex = "" [,$iStep = 100]] )
; Parameters ....: $avArray    -  Array to clone to the SQLite table
;
;                  $sTableName -  Is the name of the table that will be created in the memory db
;                                 note:
;                                 The name "array" is not allowed because is reserved to the default table used by _ArraySQL()
;                  $iIndex     -  Optional. A string containing one, or more comma separated numbers, that indicate on which columns
;                                 you want to create a "basic" index. Example: $sIndex = "2,5,7" will create 3 Single-Column Indexes
;                                 on the table that is being created. index names are as follows: tablename_ndx2 tablename_ndx5 Tablename_ndx7.
;                  $iStep      -  Optional. Default is 100. This is the number of records inserted in the table for every SQLite write
;                                 operation, that is the so called "chain insertion". This parameter is used only on recent versions
;                                 of AutoIt while is ignored on older versions for compatibility.
;                                 (Only one record at time is written in this second case)
;
; Return values .: Success:    -  @error = 0
;                                 @extended contains the milliseconds spent by the table creation
;
;                  Fillure:    -  Sets @error as following:
;                                 1 - First argument is not an array
;                                 2 - Array is not an 1D or 2D array
;                                 3 - SQLite error (the $g__sSQLiteError variable contains error description)
;                                 4 - The table name "array" is a reserved name not allowed with this function
; ===============================================================================================================================
Func _ArrayToDbTable(ByRef $avArray, $sTableName, $sIndex = "", $iStep = 100)
    If $sTableName = "array" Then Return SetError(4, 0, "")
    __ArrayToSqlTable($avArray, $sTableName, $sIndex, $iStep)
    Return SetError(@error, @extended, "")
EndFunc   ;==>_ArrayToDbTable

; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayFromSQL
; Description ...: Allows to execute SQL queryes against table(s) in the Temp db and get the resulting table returned in an array
; Syntax.........: _ArrayFromSQL( $sSQL_Query )
; Parameters ....: $sSQL_Query - a string containing the SQL query to execute against the temp db
;                                it's up to you to pass a correct query with existing temp tablenames.
;                                P.S. (Related info)
;                                You can previously create required temp tables by the _ArrayToDbTable() function
;                                passing an array and a tablename to be created in the temp db (see function info for details)
;
; Return values .: Success: An 1D or 2D array containing data resulting from the execution of the SQL query.
;
;                  Fillure: an empty string and the @error value is set as following
;                           3 - SQLite error. In this case the $g__sSQLiteError global variable contains the error description.
; ===============================================================================================================================
Func _ArrayFromSQL($sSQL_Query)
    Local $aResult, $iRows, $iColumns, $iRval, $iError, $hTimer = TimerInit()
    $g__sSQLiteError = ""
    ; now execute the SQL query
    If _SQLite_GetTable2d(-1, $sSQL_Query, $aResult, $iRows, $iColumns) = $SQLITE_OK Then ; execution of query is OK
        Return SetError(0, TimerDiff($hTimer), $aResult)
    Else ; an SQLite error occurred
        $g__sSQLiteError = _SQLite_ErrMsg()
        Return SetError(3, TimerDiff($hTimer), "")
    EndIf
EndFunc   ;==>_ArrayFromSQL
;
; #INTERNAL USE# ================================================================================================================
; Name...........: __ArrayToSqlTable
; Description ...: clone $avArray to a temporary SQLite db table (memory resident)
; Syntax.........: __ArrayToSqlTable ( ByRef $avArray [$sTableName [,$iStep]])
; Parameters ....: $avArray    -  Array to clone to the SQLite temporary table
;                  $sTableName -  Optional. Default is "array". Is the name of the table that will be created in the temp db
;                                 This function is used mainly by the _ArraySQL() function, and the "array" table created by default
;                                 will be automatically deleted after the execution of the query passed by _ArraySQL().
;                                 Optionally, if you create a table with this parameter instead, assigning a different name to the table
;                                 it will remain in the temp db till end of program execution or till you explicitly delete it using the
;                                 _SQLite_Exec($g__hMemDb, "DROP TABLE yourtablename") for example.
;                  $iIndex     -  Optional. A string containing one, or more comma separated numbers, that indicate on which columns
;                                 you want to create a "basic" index. Example: $sIndex = "2,5,7" will create 3 Single-Column Indexes
;                                 on the table that is being created. index names are as follows: tablename_ndx2 tablename_ndx5 Tablename_ndx7.
;                  $iStep      -  Optional. Default is 100. This is the number of records inserted in the table for every SQLite write
;                                 operation, that is the so called "chain insertion". This parameter is used only on recent versions
;                                  of AutoIt while is ignored on older versions for compatibility problems.
;                                 (Only one record at time is written in this second case)
;
; Return values .: Success:    -  @error = 0
;                                 @extended contains the milliseconds spent by the table creation
;
;                  Fillure:    -  Sets @error as following:
;                                 1 - First argument is not an array
;                                 2 - Array is not an 1D or 2D array
;                                 3 - SQLite error (the $g__sSQLiteError variable contains error description)
;
; Remarks .......: Mainly for Internal use (but not only)
; ===============================================================================================================================
Func __ArrayToSqlTable(ByRef $avArray, $sTableName = "array", $sIndex = "", $iStep = 100) ; Pass an array to a temporary SQL table in memory db
    Local $hTimer = TimerInit()
    If Not IsArray($avArray) Then Return SetError(1, TimerDiff($hTimer), 0) ; must be an array
    Local $iDimensions = UBound($avArray, 0)
    If $iDimensions > 2 Then Return SetError(2, TimerDiff($hTimer), 0) ; must be an 1D or 2D array
    Local $aAutoItV = StringSplit(@AutoItVersion, ".", 2), $nAutoItVersion = StringFormat("%03i%03i%03i%03i", $aAutoItV[0], $aAutoItV[1], $aAutoItV[2], $aAutoItV[3])
    ; Local $iStep = 10 ; the number of records we want to load in each chain <---- the chain length
    If $nAutoItVersion < 3003012000 Then $iStep = 1 ; if an older version of AutoIt then do not use the new SQLite "chain insertion"
    Local $sDBfields = "" ; will hold the names of the fields (column0, column1, column2......) (zero based)
    Local $vDBvalues = "" ; will hold the values of a field (1, 'aaa', 456)
    Local $vDBvaluesChain = "" ; will hold the chain of n values (1, 'aaa', 456), (2, 'bbb', 147), ... , (235, 'xyz', 999)
    Local $iRecords = UBound($avArray, 1) ; total number of rows in the array
    Local $iLastRecord = $iRecords - 1
    Local $iChains = Ceiling($iRecords / $iStep) ; how many chains we will fill?
    Local $nRemainders = Mod($iRecords, $iStep) ; are there rmainders? (if so the last chain will be only partly full)
    Local $iFields = 0 ; we suppose an 1D array
    If $iDimensions = 2 Then ; if is a 2D array instead
        $iFields = UBound($avArray, 2) - 1 ; number of fields (columns) in the 2D array (zero based)
    EndIf
    For $x = 0 To $iFields
        $sDBfields &= "column" & String($x) & ","
    Next
    $sDBfields = StringTrimRight($sDBfields, 1) ; remove the last comma

    If Not _SQLite_Exec(-1, "CREATE TEMP TABLE " & $sTableName & " (" & $sDBfields & ");") = $SQLITE_OK Then
        $g__sSQLiteError = _SQLite_ErrMsg()
        ; _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, TimerDiff($hTimer), "")
    Else

        #cs suggestion by jchd -> http://www.autoitscript.com/forum/topic/166536-manage-arrays-by-means-of-sql/?p=1216694
            For maximizing efficiency of table population using INSERT, you can use the "chain insertion" new syntax:
            insert into mytable (a,b,c) values (1, 'aaa', 456), (2, 'bbb', 147), (3, 'ccc', 258), ... , (235, 'xyz', 999)
            You need to keep the query string under the size limit (depends on compile-time options)
            but you can get N-fold (for some value N) improvement on insert speed with that simple trick.
        #ce

        If Not $nRemainders Then ; there are no remainder records.
            ;                      then we can load all chains (with group of records)
            ;                      and we will have no residual records to manage
            For $x = 0 To $iLastRecord Step $iStep ; we read records in groups of $iStep
                $vDBvaluesChain = ""
                For $iRecord = $x To $x + $iStep - 1
                    $vDBvalues = ""
                    ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                    $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
                Next
                $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
                ; insert chain to table
                ___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
                If @error Then Return SetError(3, TimerDiff($hTimer), "")
            Next
            ;
        Else ; if we are here is because there are remainders, so:
            If $iChains - 1 Then ; if there are more than 1 chain (otherwise if records are less than $istep read only remainders)
                For $x = 0 To $iLastRecord - $nRemainders Step $iStep
                    $vDBvaluesChain = ""
                    For $iRecord = $x To $x + $iStep - 1
                        $vDBvalues = ""
                        ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                        $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
                    Next
                    $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
                    ___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
                    If @error Then Return SetError(3, TimerDiff($hTimer), "")
                Next
            EndIf
            ; -- now read remainders -----
            $vDBvaluesChain = ""
            For $iRecord = $iLastRecord - $nRemainders + 1 To $iLastRecord ; Step $iStep
                $vDBvalues = ""
                ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
            Next
            $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
            ___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
            If @error Then Return SetError(3, TimerDiff($hTimer), "")
        EndIf
    EndIf

    If $sIndex <> "" Then ; create basic indexes
        ; - some infos abount indexes -
        ; http://stackoverflow.com/questions/796359/does-a-multi-column-index-work-for-single-column-selects-too
        ; http://www.sqlite.org/optoverview.html
        ; http://www.sqlite.org/lang_createindex.html
        ;
        Local $aNdxs = StringSplit($sIndex, ","), $sNdxs = ""
        ;
        #cs ; --- single index with multi columns --------------------------------------------------------------------
            For $i = 1 To $aNdxs[0]
            $sNdxs &= "column" & $aNdxs[$i] & ","
            Next
            $sNdxs = StringTrimRight($sNdxs, 1) ; remove the last comma
            _SQLite_Exec($g__hMemDb, "CREATE INDEX " & $sTableName & "_ndx ON " & $sTableName & " (" & $sNdxs & ");")
        #ce ; --------------------------------------------------------------------------------------------------------
        ;
        ;     --- a different index for each column ------------------------------------------------------------------
        For $i = 1 To $aNdxs[0]
            _SQLite_Exec($g__hMemDb, "CREATE INDEX " & $sTableName & "_ndx" & $aNdxs[$i] & " ON " & $sTableName & " (column" & $aNdxs[$i] & ");")
        Next
                If @error Then Return SetError(3, TimerDiff($hTimer), "")
    EndIf

    Return SetError(0, TimerDiff($hTimer), "")
EndFunc   ;==>__ArrayToSqlTable

Func ___BuildRecord(ByRef $avArray, ByRef $iFields, ByRef $vDBvalues, ByRef $x)
    For $y = 0 To $iFields
        Switch $iFields
            Case 0 ; just 1 field (1D Array)
                If IsNumber($avArray[$x]) Then
                    $vDBvalues &= $avArray[$x] & ","
                Else
                    $vDBvalues &= _SQLite_FastEscape($avArray[$x]) & ","
                EndIf
            Case Else ; multi fields (2D Array)
                If IsNumber($avArray[$x][$y]) Then
                    $vDBvalues &= $avArray[$x][$y] & ","
                Else
                    $vDBvalues &= _SQLite_FastEscape($avArray[$x][$y]) & ","
                EndIf
        EndSwitch
    Next
    $vDBvalues = StringTrimRight($vDBvalues, 1) ; remove last comma
EndFunc   ;==>___BuildRecord

Func ___InsertChain(ByRef $sDBfields, ByRef $vDBvaluesChain, ByRef $sTableName)
    If Not _SQLite_Exec(-1, "INSERT INTO " & $sTableName & " (" & $sDBfields & ") VALUES (" & $vDBvaluesChain & ");") = $SQLITE_OK Then
        $g__sSQLiteError = _SQLite_ErrMsg()
        _SQLite_Exec($g__hMemDb, "DROP TABLE " & $sTableName) ; delete the temporary table
        Return SetError(3, 0, "")
    EndIf
EndFunc   ;==>___InsertChain

Here an example on the creation of indexes within the function _ArrayToDbTable().

This simple script also provides a comparison of performances using a query on columns with index and on other without index.

expandcollapse popup

Local $iTimer = TimerInit()
ConsoleWrite("Starting SQLite engine" & @CRLF)
#include <ArraySQL.au3> ; This also includes array.au3
ConsoleWrite("Time taken to start SQLite: " & Int(TimerDiff($iTimer)) / 1000 & " sec." & @CRLF & @CRLF)

Local $x, $y, $sQuery, $aResult
$iTimer = TimerInit()

; populate an array with random data to be managed in the example
; ---------------------------------------------------------------
ConsoleWrite("Starting of loading Array with random numbers" & @CRLF)
Local $Array[100000][5]
For $x = 0 To 99999
    For $y = 0 To 4
        $Array[$x][$y] = Random(1000, 10000, 1)
    Next
Next
ConsoleWrite("Time taken to fill Array: " & Int(TimerDiff($iTimer)) / 1000 & " sec." & @CRLF & @CRLF)
;
; create table named Array1 and 2 indexes, one on column 0 and one on column 3
; ----------------------------------------------------------------------------
_ArrayToDbTable($Array, "Array1", "0,3")
ConsoleWrite("Time taken to create table fill it and then create 2 indexes: " & @extended / 1000 & " sec." & @CRLF & @CRLF)
ConsoleWrite('now this query: "SELECT * FROM array1 WHERE column0 = 1000;" will be executed on indexed and not indexed columns:' & @CRLF & @CRLF)

; sql query to be executed on the indexed column, column0
$sQuery = "SELECT * FROM array1 WHERE column0 = 1000;"
$aResult = _ArrayFromSQL($sQuery) ; <- execute query and get result in the $aResult array
ConsoleWrite("Time taken to execute query on the indexed column0: " & @extended & " milliseconds." & @CRLF)

; same query executed on same column without using index
$sQuery = "SELECT * FROM array1 NOT INDEXED WHERE column0 = 1000;"
$aResult = _ArrayFromSQL($sQuery) ; <- execute query and get result in the $aResult array
ConsoleWrite("Time taken to execute the same query on the same column0 but WITHOUT using the index: " & @extended & " milliseconds." & @CRLF)

$sQuery = "SELECT * FROM array1 WHERE column3 = 1000;"
$aResult = _ArrayFromSQL($sQuery) ; <- execute query and get result in the $aResult array
ConsoleWrite("Time taken to execute query on the INDEXED column3 : " & @extended & " milliseconds." & @CRLF)

$sQuery = "SELECT * FROM array1 WHERE column4 = 1000;"
$aResult = _ArrayFromSQL($sQuery) ; <- execute query and get result in the $aResult array
ConsoleWrite("Time taken to execute query on the NOT indexed column4: " & @extended & " milliseconds." & @CRLF & @CRLF)

edit:

added explanation of the $sIndex parameter in the header of the  _ArrayToDbTable() function

Edited March 15, 2015 by Chimp

[Zedna]

Indexes on single columns are not sufficient.

In many cases there is need for indexes over more table columns,

for example table with columns id, name, date --> index "name+date"

I recommend to add new optional parameter $UserSQL (instead of $index) where user can add anything what could be executed after creating of table. It could be more SQL commands separated by ; and you will just execute this passed SQL command.

Another thing is using hardcoded clausule TEMP in CREATE TABLE command.

I don't think it's good approach.

I'm not sure but I think that on TEMP tables indexes are not working (at all or the same way?).

Edited March 15, 2015 by Zedna

[UEZ]

It would be good to provide the SQLite.dll within your script to avoid downloading the DLL everytime. Or check whether SQLite.dll exists in script dir and call it with 

Global Static $g__hMemDb = _SQLite_Open(True)

Edited March 15, 2015 by UEZ

[Gianni]

Indexes on single columns are not sufficient.

In many cases there is need for indexes over more table columns,

for example table with columns id, name, date --> index "name+date"

I recommend to add new optional parameter $UserSQL (instead of $index) where user can add anything what could be executed after creating of table. It could be more SQL commands separated by ; and you will just execute this passed SQL command.

Another thing is using hardcoded clausule TEMP in CREATE TABLE command.

I don't think it's good approach.

I'm not sure but I think that on TEMP tables indexes are not working (at all or the same way?).

 

thanks

well, I renamed the $iIndex parameter to $sUserSQL (leaving unchanged the facility of the easy single column indexes creation as is),

and giving in addition the possibility to pass an entire SQL Query instead, that will be executed right after table creation.

In this way both possibilities are available, that is, (see edit of march 22 below)

if you pass a comma separated list of numbers then simple single column indexes are created, (one index for each column number in the list)

if you pass an entire SQL Query instead (for complex indexes creation) then that query will be executed as is right after the table creation.

About the TEMP parameter, I do not know if it causes penalities on indexes performances, I think that it only forces the creation of the table in memory, anyway since the whole db is already a memory db, then that parameter, in this case, is simply superfluous, so I can remove it.

edit of March 22 2015:

slightly enhanced the $sUserSQL usage by allowing now the passing of one or more SQL queries separated by ";" within the $sUserSQL variable (do not forget to place also the last ";" at the end of the string):

example of multi query string:

"CREATE INDEX Array1_ndx0 ON Array1 (column0); CREATE INDEX Array1_ndx3 ON Array1 (column3); CREATE INDEX Array1_ndx4 ON Array1 (column4);"

so now:

1. if you pass a comma separated list of numbers then simple single column indexes are created, (one index for each column number in the list)
2. if you pass an entire SQL Query or even more than one separated by ";" (for complex indexes creation) then this query (or those queries if more than one) will be executed right after the table creation (one after another if more than one are passed).

expandcollapse popup

; save this as ArraySQL.au3
;
; this version allows the creation of "permanent" tables in the memory db
; allowing sql queries against more tables instead just one table at time
; this by using the 2 new functions:  _ArrayToDbTable and _ArrayFromSQL
; (see functions infos for details)
;
; March 15, 2015
; added a new optional parameter in the _ArrayToDbTable function that allows
; the creation of one or more simple single-column indexes
;
; March 21, 2015
; slightly  modified the above parameter ($sUserSQL) allowing 2 ways of use
; (see function info for details)
; Also removed the TEMP parameter from CREATE TABLE
;
; March 22, 2015
; slightly enhanced the $sUserSQL usage by allowing now
; the passing of one or more SQL queries separated by ";"
; (see function info for details)
;
#include-once
#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
;
Global Static $g__sSQliteDll = _SQLite_Startup()
; Global Static $g__sSQliteDll = _SQLite_Startup(@ScriptDir & "\sqlite3.dll", False, True)
Global Static $g__hMemDb = _SQLite_Open()
Global $g__sSQLiteError = "" ; will contains SQL error messages

; #FUNCTION# ====================================================================================================================
; Name...........: _ArraySQL
; Description ...: Allows to execute SQL queryes against 1D or 2D arrays
; Syntax.........: _ArraySQL( ByRef $avArray, $sSQL_Query )
; Parameters ....: $avArray - an 1D or 2D Array to be manage
;                  $sSQL_Query - a string containing the SQL query to execute against the array
;                                the passed array is referred as array as table name in the query
;                                the fields (column(s)) of the array are referred as column0, column1, column2 .... and so on
;
; Return values .: Success: An 1D or 2D array containing data resulting from the execution of the SQL query.
;
;                  Fillure: an empty string and the @error value is set as following
;                           1 - First argument is not an array
;                           2 - not a 1D or 2D array
;                           3 - SQLite error. In this case the $g__sSQLiteError global variable contains the error description.
; ===============================================================================================================================
Func _ArraySQL(ByRef $avArray, $sSQL_Query)
    Local $aResult, $iRows, $iColumns, $iRval, $iError, $hTimer = TimerInit()
    $g__sSQLiteError = ""
    __ArrayToSqlTable($avArray) ; clone $avArray to a temporary sql db table (memory resident)
    ;                             name of the temp table will be array
    ;                             name of the filed(s) will be column0 column1 column2 columnN and so on ....
    ;
    If @error Then
        $iError = @error
        Return SetError($iError, TimerDiff($hTimer), "") ; something went wrong
    EndIf
    ; now execute the SQL query
    $iRval = _SQLite_GetTable2d(-1, $sSQL_Query, $aResult, $iRows, $iColumns)
    If Not $iRval = $SQLITE_OK Then ; an SQLite error occurred
        $g__sSQLiteError = _SQLite_ErrMsg()
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, TimerDiff($hTimer), "")
    Else
        $g__sSQLiteError = ""
        _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(0, TimerDiff($hTimer), $aResult)
    EndIf
EndFunc   ;==>_ArraySQL

; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayToDbTable
; Description ...: clones $avArray to a "permanent" SQLite db table (memory resident)
; Syntax.........: _ArrayToDbTable ( ByRef $avArray, $sTableName [$sUserSQL  = "" [,$iStep = 100]] )
; Parameters ....: $avArray    -  Array to clone to the SQLite table
;
;                  $sTableName -  Is the name of the table that will be created in the memory db
;                                 note:
;                                 The name "array" is not allowed because is reserved to the default table used by _ArraySQL()
;                  $sUserSQL   -  Optional. This can be used in 2 ways and can be one of the following:
;                                 1) A string containing one, or more comma separated numbers, that indicate on which columns
;                                    you want to create a "basic" index. Example: $sUserSQL  = "2,5,7" will create 3 Single-Column Indexes
;                                    on the table that is being created. index names are as follows: tablename_ndx2 tablename_ndx5 Tablename_ndx7.
;                                 or
;                                 2) You can pass an entire SQL Query or even more than one separated by ";" (for complex indexes creation)
;                                    this query (or those queries if more than one) will be executed right after the table creation.
;                                    If more than one query are passed, those are executed one after another
;                                    example of a string containing 3 queries:
;                                    "CREATE INDEX Array1_ndx0 ON Array1 (column0); CREATE INDEX Array1_ndx3 ON Array1 (column3); CREATE INDEX Array1_ndx4 ON Array1 (column4);"
;                                    IMPORTANT! do not forget to place also the last ; at the end of the string.
;
;                  $iStep      -  Optional. Default is 100. This is the number of records inserted in the table for every SQLite write
;                                 operation, that is the so called "chain insertion". This parameter is used only on recent versions
;                                 of AutoIt while is ignored on older versions for compatibility.
;                                 (Only one record at time is written in this second case)
;
; Return values .: Success:    -  @error = 0
;                                 @extended contains the milliseconds spent by the table creation
;
;                  Fillure:    -  Sets @error as following:
;                                 1 - First argument is not an array
;                                 2 - Array is not an 1D or 2D array
;                                 3 - SQLite error (the $g__sSQLiteError variable contains error description)
;                                 4 - The table name "array" is a reserved name not allowed with this function
; ===============================================================================================================================
Func _ArrayToDbTable(ByRef $avArray, $sTableName, $sUserSQL = "", $iStep = 100)
    If $sTableName = "array" Then Return SetError(4, 0, "")
    __ArrayToSqlTable($avArray, $sTableName, $sUserSQL, $iStep)
    Return SetError(@error, @extended, "")
EndFunc   ;==>_ArrayToDbTable

; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayFromSQL
; Description ...: Allows to execute SQL queryes against table(s) in the Temp db and get the resulting table returned in an array
; Syntax.........: _ArrayFromSQL( $sSQL_Query )
; Parameters ....: $sSQL_Query - a string containing the SQL query to execute against the temp db
;                                it's up to you to pass a correct query with existing temp tablenames.
;                                P.S. (Related info)
;                                You can previously create required temp tables by the _ArrayToDbTable() function
;                                passing an array and a tablename to be created in the temp db (see function info for details)
;
; Return values .: Success: An 1D or 2D array containing data resulting from the execution of the SQL query.
;
;                  Fillure: an empty string and the @error value is set as following
;                           3 - SQLite error. In this case the $g__sSQLiteError global variable contains the error description.
; ===============================================================================================================================
Func _ArrayFromSQL($sSQL_Query)
    Local $aResult, $iRows, $iColumns, $iRval, $iError, $hTimer = TimerInit()
    $g__sSQLiteError = ""
    ; now execute the SQL query
    If _SQLite_GetTable2d(-1, $sSQL_Query, $aResult, $iRows, $iColumns) = $SQLITE_OK Then ; execution of query is OK
        Return SetError(0, TimerDiff($hTimer), $aResult)
    Else ; an SQLite error occurred
        $g__sSQLiteError = _SQLite_ErrMsg()
        Return SetError(3, TimerDiff($hTimer), "")
    EndIf
EndFunc   ;==>_ArrayFromSQL
;
; #INTERNAL USE# ================================================================================================================
; Name...........: __ArrayToSqlTable
; Description ...: clone $avArray to a temporary SQLite db table (memory resident)
; Syntax.........: __ArrayToSqlTable ( ByRef $avArray [$sTableName = "array" [,$sUserSQL = "" [,$iStep = 100]]])
; Parameters ....: $avArray    -  Array to clone to the SQLite temporary table
;                  $sTableName -  Optional. Default is "array". Is the name of the table that will be created in the temp db
;                                 This function is used mainly by the _ArraySQL() function, and the "array" table created by default
;                                 will be automatically deleted after the execution of the query passed by _ArraySQL().
;                                 Optionally, if you create a table with this parameter instead, assigning a different name to the table
;                                 it will remain in the temp db till end of program execution or till you explicitly delete it using the
;                                 _SQLite_Exec($g__hMemDb, "DROP TABLE yourtablename") for example.
;                  $sUserSQL   -  Optional. This can be used in 2 ways and can be one of the following:
;                                 1) A string containing one, or more comma separated numbers, that indicate on which columns
;                                    you want to create a "basic" index. Example: $sUserSQL  = "2,5,7" will create 3 Single-Column Indexes
;                                    on the table that is being created. index names are as follows: tablename_ndx2 tablename_ndx5 Tablename_ndx7.
;                                 or
;                                 2) You can pass an entire SQL Query or even more than one separated by ";" (for complex indexes creation)
;                                    this query (or those queries if more than one) will be executed right after the table creation.
;                                    If more than one query are passed, those are executed one after another
;                                    example of a string containing 3 queries:
;                                    "CREATE INDEX Array1_ndx0 ON Array1 (column0); CREATE INDEX Array1_ndx3 ON Array1 (column3); CREATE INDEX Array1_ndx4 ON Array1 (column4);"
;                                    IMPORTANT! do not forget to place also the last ; at the end of the string.
;
;                  $iStep      -  Optional. Default is 100. This is the number of records inserted in the table for every SQLite write
;                                 operation, that is the so called "chain insertion". This parameter is used only on recent versions
;                                  of AutoIt while is ignored on older versions for compatibility problems.
;                                 (Only one record at time is written in this second case)
;
; Return values .: Success:    -  @error = 0
;                                 @extended contains the milliseconds spent by the table creation
;
;                  Fillure:    -  Sets @error as following:
;                                 1 - First argument is not an array
;                                 2 - Array is not an 1D or 2D array
;                                 3 - SQLite error (the $g__sSQLiteError variable contains error description)
;
; Remarks .......: Mainly for Internal use (but not only)
; ===============================================================================================================================
Func __ArrayToSqlTable(ByRef $avArray, $sTableName = "array", $sUserSQL = "", $iStep = 100) ; Pass an array to a temporary SQL table in memory db
    Local $hTimer = TimerInit()
    If Not IsArray($avArray) Then Return SetError(1, TimerDiff($hTimer), 0) ; must be an array
    Local $iDimensions = UBound($avArray, 0)
    If $iDimensions > 2 Then Return SetError(2, TimerDiff($hTimer), 0) ; must be an 1D or 2D array
    Local $aAutoItV = StringSplit(@AutoItVersion, ".", 2), $nAutoItVersion = StringFormat("%03i%03i%03i%03i", $aAutoItV[0], $aAutoItV[1], $aAutoItV[2], $aAutoItV[3])
    ; Local $iStep = 10 ; the number of records we want to load in each chain <---- the chain length
    If $nAutoItVersion < 3003012000 Then $iStep = 1 ; if an older version of AutoIt then do not use the new SQLite "chain insertion"
    Local $sDBfields = "" ; will hold the names of the fields (column0, column1, column2......) (zero based)
    Local $vDBvalues = "" ; will hold the values of a field (1, 'aaa', 456)
    Local $vDBvaluesChain = "" ; will hold the chain of n values (1, 'aaa', 456), (2, 'bbb', 147), ... , (235, 'xyz', 999)
    Local $iRecords = UBound($avArray, 1) ; total number of rows in the array
    Local $iLastRecord = $iRecords - 1
    Local $iChains = Ceiling($iRecords / $iStep) ; how many chains we will fill?
    Local $nRemainders = Mod($iRecords, $iStep) ; are there rmainders? (if so the last chain will be only partly full)
    Local $iFields = 0 ; we suppose an 1D array
    If $iDimensions = 2 Then ; if is a 2D array instead
        $iFields = UBound($avArray, 2) - 1 ; number of fields (columns) in the 2D array (zero based)
    EndIf
    For $x = 0 To $iFields
        $sDBfields &= "column" & String($x) & ","
    Next
    $sDBfields = StringTrimRight($sDBfields, 1) ; remove the last comma

    If Not _SQLite_Exec(-1, "CREATE TABLE " & $sTableName & " (" & $sDBfields & ");") = $SQLITE_OK Then
        $g__sSQLiteError = _SQLite_ErrMsg()
        ; _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
        Return SetError(3, TimerDiff($hTimer), "")
    Else

        #cs suggestion by jchd -> http://www.autoitscript.com/forum/topic/166536-manage-arrays-by-means-of-sql/?p=1216694
            For maximizing efficiency of table population using INSERT, you can use the "chain insertion" new syntax:
            insert into mytable (a,b,c) values (1, 'aaa', 456), (2, 'bbb', 147), (3, 'ccc', 258), ... , (235, 'xyz', 999)
            You need to keep the query string under the size limit (depends on compile-time options)
            but you can get N-fold (for some value N) improvement on insert speed with that simple trick.
        #ce

        If Not $nRemainders Then ; there are no remainder records.
            ;                      then we can load all chains (with group of records)
            ;                      and we will have no residual records to manage
            For $x = 0 To $iLastRecord Step $iStep ; we read records in groups of $iStep
                $vDBvaluesChain = ""
                For $iRecord = $x To $x + $iStep - 1
                    $vDBvalues = ""
                    ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                    $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
                Next
                $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
                ; insert chain to table
                ___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
                If @error Then Return SetError(3, TimerDiff($hTimer), "")
            Next
            ;
        Else ; if we are here is because there are remainders, so:
            If $iChains - 1 Then ; if there are more than 1 chain (otherwise if records are less than $istep read only remainders)
                For $x = 0 To $iLastRecord - $nRemainders Step $iStep
                    $vDBvaluesChain = ""
                    For $iRecord = $x To $x + $iStep - 1
                        $vDBvalues = ""
                        ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                        $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
                    Next
                    $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
                    ___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
                    If @error Then Return SetError(3, TimerDiff($hTimer), "")
                Next
            EndIf
            ; -- now read remainders -----
            $vDBvaluesChain = ""
            For $iRecord = $iLastRecord - $nRemainders + 1 To $iLastRecord ; Step $iStep
                $vDBvalues = ""
                ___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
                $vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
            Next
            $vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
            ___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
            If @error Then Return SetError(3, TimerDiff($hTimer), "")
        EndIf
    EndIf

    If $sUserSQL <> "" Then ; check for indexes requests
        ; - some infos abount indexes -
        ; http://stackoverflow.com/questions/796359/does-a-multi-column-index-work-for-single-column-selects-too
        ; http://www.sqlite.org/optoverview.html
        ; http://www.sqlite.org/lang_createindex.html
        ;
        If StringIsDigit(StringReplace($sUserSQL, ",", "")) Then ; if only digits in the request then
            ;                                                    create simple single columns indexes
            Local $aNdxs = StringSplit($sUserSQL, ","), $sNdxs = ""
            ;
            #cs ; --- single index with multi columns --------------------------------------------------------------------
                For $i = 1 To $aNdxs[0]
                $sNdxs &= "column" & $aNdxs[$i] & ","
                Next
                $sNdxs = StringTrimRight($sNdxs, 1) ; remove the last comma
                _SQLite_Exec($g__hMemDb, "CREATE INDEX " & $sTableName & "_ndx ON " & $sTableName & " (" & $sNdxs & ");")
            #ce ; --------------------------------------------------------------------------------------------------------
            ;
            ;     --- a different index for each column ------------------------------------------------------------------
            For $i = 1 To $aNdxs[0]
                _SQLite_Exec($g__hMemDb, "CREATE INDEX " & $sTableName & "_ndx" & $aNdxs[$i] & " ON " & $sTableName & " (column" & $aNdxs[$i] & ");")
            Next
            If @error Then Return SetError(3, TimerDiff($hTimer), "")
        Else ; execute the query/queries as is
            Local $sUserQueries = StringSplit($sUserSQL, ";")
            For $i = 1 To $sUserQueries[0] - 1
                _SQLite_Exec($g__hMemDb, $sUserQueries[$i] & ";")
                If @error Then Return SetError(3, TimerDiff($hTimer), "")
            Next
        EndIf
    EndIf
    Return SetError(0, TimerDiff($hTimer), "")
EndFunc   ;==>__ArrayToSqlTable

Func ___BuildRecord(ByRef $avArray, ByRef $iFields, ByRef $vDBvalues, ByRef $x)
    For $y = 0 To $iFields
        Switch $iFields
            Case 0 ; just 1 field (1D Array)
                If IsNumber($avArray[$x]) Then
                    $vDBvalues &= $avArray[$x] & ","
                Else
                    $vDBvalues &= _SQLite_FastEscape($avArray[$x]) & ","
                EndIf
            Case Else ; multi fields (2D Array)
                If IsNumber($avArray[$x][$y]) Then
                    $vDBvalues &= $avArray[$x][$y] & ","
                Else
                    $vDBvalues &= _SQLite_FastEscape($avArray[$x][$y]) & ","
                EndIf
        EndSwitch
    Next
    $vDBvalues = StringTrimRight($vDBvalues, 1) ; remove last comma
EndFunc   ;==>___BuildRecord

Func ___InsertChain(ByRef $sDBfields, ByRef $vDBvaluesChain, ByRef $sTableName)
    If Not _SQLite_Exec(-1, "INSERT INTO " & $sTableName & " (" & $sDBfields & ") VALUES (" & $vDBvaluesChain & ");") = $SQLITE_OK Then
        $g__sSQLiteError = _SQLite_ErrMsg()
        _SQLite_Exec($g__hMemDb, "DROP TABLE " & $sTableName) ; delete the temporary table
        Return SetError(3, 0, "")
    EndIf
EndFunc   ;==>___InsertChain

.

.

.

 

It would be good to provide the SQLite.dll within your script to avoid downloading the DLL everytime. Or check whether SQLite.dll exists in script dir and call it with 

Global Static $g__hMemDb = _SQLite_Open(True)

 

I don't get it

could you please elaborate on this

thanks

Edited March 22, 2015 by Chimp

[UEZ]

@Chimp: sorry for the confusion but I mean

_SQLite_Startup(@ScriptDir & "\sqlite3.dll", False, True)

When you embed the DLL into your script and extract it once _SQLite_Startup() will not download it everytime.

I wrote a tool for the company and wondered why the script took so long to start.

[Gianni]

@Chimp: sorry for the confusion but I mean

_SQLite_Startup(@ScriptDir & "\sqlite3.dll", False, True)

When you embed the DLL into your script and extract it once _SQLite_Startup() will not download it everytime.

I wrote a tool for the company and wondered why the script took so long to start.

 

Ah, ok got the meaning,

well I've seen that simply copying the sqlite3.dll in the same path of the script it's enough to speed-up the SQLite startup from few seconds down to just few milliseconds... (I've downloaded the dll from here)

Thanks for the advise!

I do not know how the #include <SQLite.dll.au3> works, but if just placing the sqlite3.dll within the same directory of the script makes the good job, I think that doing this (by hand) it's enough, without the need to embed about 650 kb of bytes within this general purpose udf. (in this way it's also easier keeping the dll updated)

So, placing the sqlite3.dll together, within the script's directory, It's anyway a "mandatory" recommendation for better startup performances

Thanks UEZ

Edited March 22, 2015 by Chimp