Chimp

Manage arrays by means of SQL

14 posts in this topic

#1 ·  Posted (edited)

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)

; 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

 

; 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 by Chimp
3 people like this

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites



Very interesting approach to handle arrays using SQL.  :thumbsup:

 

Br,

UEZ


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

I played with a similar/more hard coded concept:

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

1 person likes this

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites

Very interesting approach to handle arrays using SQL.  :thumbsup:

 

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


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

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.


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

#6 ·  Posted (edited)

.....

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

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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.

; 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)

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

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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

; 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.

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

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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).
; 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 :think:

could you please elaborate on this

thanks

Edited by Chimp

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

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


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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

small minds discuss people average minds discuss events great minds discuss ideas.... and use 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

  • Similar Content

    • czardas
      By czardas
      Haven't had much time to code recently. However the following thread inspired me.
      The debate about linear, parallel and binary search methods was rather interesting and, in an attempt to be diplomatic, I decided to combine @jchd's suggestion with @LarsJ's binary search example. I decided that the binary search algorithm required modification to make it more linear. As usual, 'if you invent something, it probably already exists and if it already exists, it exists for a reason'. My first attempt was not all that good. The code worked but was really a mess. I blame peer pressure (to post an example of a parallel search method).  I will delete that old code in due course.
      With a little memory jogging and a glance at the help file, the solution turned out to be quite easy: I just needed a better understanding of Euler. Further modification will be needed to work with more complicated unicode strings. The output could be returned as an array or a delimitered string. I'm not so interested in those details. I'm just going to post the algorithm for now and anyone, who wants to, can modify it to suit their needs. Both arrays must contain at least 1 element.
      Local $aFoo = [0,1,2,3,4,5,6,7,9,10,11,12,13,14,15,16,19,20,23,24,26,30,35,39,40,41] Local $aBar = [0,1,5,6,7,8,9,10,11,12,13,14,17,18,19,21,24,25,26,27,34,35,38,40] ParallelExponetialSearch($aFoo, $aBar) ; Compares two lists - returning positive matches. Each input array must be unique (individually) and in alphabetical order. Func ParallelExponetialSearch($aFoo, $aBar) Local $sFind, _ $iMin_F = -1, $iMax_F = UBound($aFoo) -1, $Lo_F = $iMin_F, $Hi_F, _ $iMin_B = -1, $iMax_B = UBound($aBar) -1, $Lo_B = $iMin_B, $Hi_B While $iMin_F < $iMax_F And $iMin_B < $iMax_B ; Toggle Arrays - Which array has most untested elements? This is the one we want to search next, ; so we can bypass more comparisons because (in theory) mismatches have a greater chance of being skipped. If $iMax_F - $iMin_F >= $iMax_B - $iMin_B Then ; $aFoo has more (or an equal number of) untested elements $Hi_F = $iMax_F $iMin_B += 1 $sFind = $aBar[$iMin_B] While $Lo_F < $Hi_F ; search $aFoo For $i = 0 To Floor(Log($Hi_F - $Lo_F) / Log(2)) $Lo_F = $iMin_F + 2^$i If $aFoo[$Lo_F] = $sFind Then $iMin_F = $Lo_F ; each match should be added to the output [perhaps an array] ConsoleWrite($sFind & " found at $aFoo[" & $Lo_F & "] = $aBar[" & $iMin_B & "]" & @LF) ExitLoop 2 ElseIf $aFoo[$Lo_F] > $sFind Then $Hi_F = $Lo_F -1 $iMin_F += Floor(2^($i -1)) $Lo_F = $iMin_F ContinueLoop 2 EndIf Next $iMin_F = $Lo_F ; minimum increment is one WEnd Else ; $aBar has more untested elements $Hi_B = $iMax_B $iMin_F += 1 $sFind = $aFoo[$iMin_F] While $Lo_B < $Hi_B ; search $aBar For $i = 0 To Floor(Log($Hi_B - $Lo_B) / Log(2)) $Lo_B = $iMin_B + 2^$i If $aBar[$Lo_B] = $sFind Then $iMin_B = $Lo_B ; each match should be added to the output [perhaps an array] ConsoleWrite($sFind & " found at $aFoo[" & $iMin_F & "] = $aBar[" & $Lo_B & "]" & @LF) ExitLoop 2 ElseIf $aBar[$Lo_B] > $sFind Then $Hi_B = $Lo_B -1 $iMin_B += Floor(2^($i -1)) $Lo_B = $iMin_B ContinueLoop 2 EndIf Next $iMin_B = $Lo_B ; minimum increment is one WEnd EndIf WEnd EndFunc ;==> ParallelExponetialSearch I hope this will be useful to someone. I believe it deserved a thread of its own!
    • RC86
      By RC86
      Afternoon!
      I have a requirement to return both a string and an array from a function so as a result I put them both into an array and returned that.  I can access them in their entirety after returning them but then I can't seem to access the array elements after this.  Should I be able to or is there a prettier way?
      #include <Array.au3> ;Memory info returned as a string and an array $memoryInfo = _getMemoryInfo() msgbox(0,"Memory Info",$memoryInfo[0]) _ArrayDisplay($memoryInfo[1],"Memory as an Array") Local $newArray[7] $memoryInfo[1] = $newArray msgbox(0,"Test element",$newArray[0]) _ArrayDisplay($newArray) Func _getMemoryInfo() Local $newArray[7] Local $array = MemGetStats() $newArray[0] = $array[0] ;% of memory in use $newArray[1] = Round($array[1]/1024 * 0.001,2) ;Total physical RAM $newArray[2] = Round($array[2]/1024 * 0.001,2) ;Availaible physical RAM $newArray[3] = Round($array[3]/1024 * 0.001,2) ;Total pagefile $newArray[4] = Round($array[4]/1024 * 0.001,2) ;Available pagefile $newArray[5] = Round($array[5]/1024 * 0.001,2) ;Total virtual $newArray[6] = Round($array[6]/1024 * 0.001,2) ;Available virtual $memoryUsage = $newArray[1] - $newarray[2] $pagefileUsage = $newArray[3] - $newarray[4] ;Output/Return Local $returnArray[2] $returnArray[0] = "Memory: " & $memoryUsage & " GB/" & $newArray[1] & " GB " & @CRLF & "Pagefile: " & $pagefileUsage & " GB/" & $newArray[3] & " GB " $returnArray[1] = $newArray return $returnArray EndFunc A bit messy but hopefully it's understandable what I'm trying to achieve.
      Thanks!
    • Jibberish
      By Jibberish
      I am having difficulties getting updated results from GUICtrlCreateInput. I wrote a small script to demonstrate what I am trying to do.
      This script reads a text file into an array and displays the Name and Number from the text file.
      The user can check a name and change the number, and the results are displayed in _ArrayDisplay.
      Only the Checked names are displayed in their original position. This is necessary for future
      plumbing.
      The problem with this script is the original number is returned, not the changed number. I cannot
      figure out how to get the updated number to be displayed.
      ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; This script reads a text file into an array and displays the Name and Number from the text file. ; The user can check a name and change the number, and the results are displayed in _ArrayDisplay. ; Only the Checked names are displayed in their original position. This is necessary for future ; plumbing. ; ; The problem with this script is the original number is returned, not the changed number. I cannot ; figure out how to get the updated number to be displayed. ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #include-once #include <MsgBoxConstants.au3> #include <array.au3> #include <Date.au3> #include <WinAPIFiles.au3> #include <AutoItConstants.au3> #include <Misc.au3> #include <File.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> Local $iMax = 6 ;Default number of Videos - 1 for the array counter Dim $aTextFile[$iMax][2] ; [start with 5 entries][Name, Number] Don't get confused! [Row][Column] Local $i = 0 Local $iLeft = 30 Local $iTop = 30 Local $sName Local $sNumber Local $aArray Local $iMaxCol = 5 Local $iRow = 0 Local $iCol = 0 Local $aNumberCount[$iMaxCol][2] $aArray1 = ReadFile() $aNamesNumbers = DisplayNames($aArray1) _ArrayDisplay($aNamesNumbers) ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; ReadFile() places the contents of the NameNumber.txt file in an array ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Func ReadFile() Local $sFileData = @ScriptDir & "\NameNumber.txt" Local $iStrReturn = 0 Local $aArrayFile Local $aTextFile[$iMaxCol][2] Local $aName Local $sCheckBox1 Const $iL = 10 ; Count from Left for GUI so all buttons line up Local $sFill Local $iA = 0, $iN = 0 ; Put the Names into an Array _FileReadToArray($sFileData, $aArrayFile) For $iA = 0 To $aArrayFile[0] ; Step through the array looking for Names If StringInStr($aArrayFile[$iA], "Name:") Then $aName = StringSplit($aArrayFile[$iA],":") $sFill = $aName[2] If UBound($aTextFile) <= $iRow Then ; Resize the array when $iRow is equal to the element count in the array to prevent subscript error ReDim $aTextFile[UBound($aTextFile) + 1][$iMaxCol] EndIf $aTextFile[$iRow][$iCol] = $sFill $iRow += 1 EndIf Next $iCol = 1 $iRow = 0 $sFill = "" For $iA = 1 To $aArrayFile[0] ; Step through the array looking for Numbers If StringInStr($aArrayFile[$iA], "Number:") Then $aName = StringSplit($aArrayFile[$iA],":") $sFill = $aName[2] If UBound($aTextFile) <= $iRow Then ; Resize the array when $iRow is equal to the element count in the array to prevent subscript error ReDim $aTextFile[UBound($aTextFile) + 1][$iMaxCol] EndIf $aTextFile[$iRow][$iCol] = $sFill $iRow += 1 EndIf Next Return $aTextFile EndFunc ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; Display the Name and Number ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Func DisplayNames($aArray1) Local $aName[$iMax] Local $aNumbers[$iMax] $iMMCount = UBound($aArray1) $iMMCount -=1 Local $iWidth = 300 Local $iLength = 300;$iMMCount * 30 GUISetFont(12) $hGUI = GUICreate("Edit / Update Number Test", $iWidth, $iLength, -1, -1) GUICtrlCreateLabel(" Name Number",1,5) ;$iTop += 30 ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; Display Name with a Checkbox. Only Checked Names should be saved. ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ For $i = 0 to $iMMCount Step 1 $sNameText = $aArray1[$i][0] $iNameLength = StringLen($sNameText) $aName[$i] = GUICtrlCreateCheckbox($sNameText,$iLeft, $iTop) $iTop += 30 Next ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; Display the number from the text file below each name. Allow the user to change the number and display the ; new number. ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ $iLeft = 60 GUIStartGroup() $iTop = 28 For $i = 0 to $iMMCount Step 1 $sNumberText = $aArray1[$i][1] $aNumberCount[$i][1] = $sNumberText $aNumberCount[$i][0] = GUICtrlCreateInput($sNumberText,$iLeft, $iTop, 50,18, $GUI_DOCKAUTO) GUICtrlSetPos($aNumberCount[$i][0],200) $iTop += 30 Next ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; Read the checked names and (possibly updated) number ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ $iMMCount = UBound($aNumberCount) $iMMNewCount = $iMMCount - 1 Local $aGUICheckbox[$iMMCount] Local $aCheckedNameNumber[$iMMCount][2] $iLeft = 30 Local $idCloseGUI = GUICtrlCreateButton("Close",$iLeft, $iTop) GUISetState(@SW_SHOW) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $idCloseGUI For $i = 0 To $iMMNewCount Step 1 Switch GUICtrlRead($aName[$i]) Case $GUI_CHECKED $aCheckedNameNumber[$i][0] = $aArray1[$i][0] Case $GUI_UNCHECKED EndSwitch Switch GUICtrlRead($aNumberCount[$i][0]) Case $aCheckedNameNumber[$i][1] = GUICtrlRead($aNumberCount[$i][1]) EndSwitch Next ExitLoop EndSwitch WEnd For $i = 0 to $iMMNewCount step 1 GUICtrlRead($aNumberCount[$i][0]) If $aCheckedNameNumber[$i][0] <> "" Then $aCheckedNameNumber[$i][1] = $aNumberCount[$i][1] EndIf Next GUIDelete($hGUI) Return $aCheckedNameNumber EndFunc This is the text file I am reading. If you want to try this out put the NameNumber.txt file in your script directory. It's attached to the post.
      * This is a dummy file with a Name and Number * The only purpose of this file is to read the updated Number. Name:Taggart Number:916 Name:Mongo Number:90 Name:Hedley Lamarr Number:22 Name:Bart Number:9999 Name:The Waco Kid Number:2244 If I change the number, the original number is displayed at the end, not the updated/modified number. I need the modified number to be displayed.
      Thanks in advance for any assistance!
      Jibberish
      NameNumber.txt
    • Jibberish
      By Jibberish
      I am working on a Video Player Test Script. I am reading a text file into a 3d array to be displayed in a GUI.
      Array Content:
      [x][0]FileName.mp4
      [x][1]UsageCount -> Up to a 4 digit number
      [x][2]EnableUsageCount (True/False)
      In the GUI the user can check the filename box, edit the number of usages and check EnableUsageCount to turn on the UsageCount control using the UsageCount number for the maximum number of plays.
      To make this easier to deal with I have removed the EnableUsageCount section, and am just concentrating on getting a method to put the edited (or unchanged) UsageCount in the array. Currently nothing is read into the array, due to my using the wrong method in the Case statement.
      Here are code snippets of what I am trying to do:
      ; Snippets from script ; I read the text file at the bottom and put the .mp4 filenames in $aManifest[x][0] and UsageCount in $aManifest[x][1] ; Then I create a GUI to display the .mp4 filenames with checkboxes and the UsageCount to the right. UsageCount is editable by the user. ; If the filename is checked, I want to read the filename into $aCheckedVideos[x][0] and the updated UsageCount in $aCheckedVideos[x][1] ; The GUICtrlRead($aVideoName[$i]) with Case $GUI_CHECKED & UNCHECKED works for the checkboxes ; This section puts the filenames in the GUI with a checkbox For $i = 0 to $iMMCount Step 1 $sMP4Text = $aManifest[$i][0] $iMP4Length = StringLen($sMP4Text) $aVideoName[$i] = GUICtrlCreateCheckbox($sMP4Text,$iLeft, $iTop) $iTop += 30 Next ; This section reads numbers from the Manifest array, and I want to be able to change the number and have them saved. ; So the Case $GUI_CHECKED & UNCHECKED won't work here, and I can't figure out what I should be doing here. For $i = 0 to $iMMCount Step 1 $sUsageText = $aManifest[$i][1] $aUsageCount[$i] = GUICtrlCreateInput($sUsageText,$iLeft, $iTop, 50,18, $GUI_DOCKAUTO) GUICtrlSetPos($aUsageCount[$i],200) $iTop += 30 Next While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $idCloseGUI For $i = 0 To $iMMNewCount Step 1 Switch GUICtrlRead($aVideoName[$i]) Case $GUI_CHECKED $aCheckedVideos[$i][0] = $aManifest[$i][0] ; If checked, I put the results into a two D array Case $GUI_UNCHECKED ; where Video Name is $aCheckedVideos[x][0] EndSwitch Switch GUICtrlRead($aUsageCounter[$i]) Case $GUI_CHECKED $aCheckedVideos[$i][1] = $aUsageCount[$i] ;I want to put the text (numbers) in $aCheckedVideos[x][1] Case $GUI_UNCHECKED ;but what is returned is blank, probably due to EndSwitch ; $GUI_CHECKED being the wrong thing. Next ExitLoop EndSwitch WEnd ;The txt file I'm reading has the following: ;~ /** Title #1: Big Buck Bunny 1080p **/ ;~ "Name": "Big Buck Bunny", ;~ "URI": "..\\MediaFiles\\bbb_1080_60s.mp4", ;~ "UsageCount": 9999, ;~ "URI": "..\\MediaFiles\\bbb_1080_60s_enc1.mp4", ;~ "UsageCount": 45, ;~ "URI": "..\\MediaFiles\\bbb_1080_60s_enc1.mp4", ;~ "UsageCount": 2, ;~ /** Title #2: Tears of Steel 4K **/ ;~ "Name": "Tears of Steel 4K", ;~ "URI": "..\\MediaFiles\\tos_4K_60s_HEVC.mp4", ;~ "UsageCount": 9876, ;~ "URI": "..\\MediaFiles\\tos_4K_60s_HEVC_enc2.mp4", ;~ "UsageCount": 0, ;~ "URI": "..\\MediaFiles\\tos_4K_60s_HEVC_enc2.mp4", ;~ "UsageCount": 5, I am certain that the section
                          Switch GUICtrlRead($aUsageCounter[$i])
                              Case $GUI_CHECKED
                                  $aCheckedVideos[$i][1] = $aUsageCount[$i]    ;I want to put the text (numbers) in $aCheckedVideos[x][1]
                              Case $GUI_UNCHECKED                                ;but what is returned is blank, probably due to
                          EndSwitch                                            ; $GUI_CHECKED being the wrong thing.

      is wrong, and this is what I am looking for help with. Instead of $GUI_CHECKED what should I be looking for?
      The worst part of this is I had this working late last night, and then lost my changes and cannot for the life of me remember how I had this working.
      Help is truly appreciated!
      Jibberish
       
    • sc4ry
      By sc4ry
      Dear all,
      I already have a formula which will select doublicates, but sometimes there might be entries with additional information starting after "-", which I now want to trim away.
      Current formula is:
      Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (a.title like b.title and a.oid <> b.oid)" & $ActiveDisks_Filter & " AND (a.comment <> 'Delete' AND b.comment <> 'Delete') Order by a.title ASC, a.size DESC; testwise I want to add a simple solution like left, but afterwards I get an error but I do not know why.
      Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (left(a.title,15) like b.title and a.oid <> b.oid) Order by a.title ASC, a.size DESC; Error: near "(": syntax error: Finally I would like to insert intstr to search for "-" but currently I am not able to extend my current code =/
      Thanks a lot for your help.
      EDIT: hey, I managed it, left was not working also it was somehow shown that the tool knowed the comment. I did it now with substr+instr
      (substr(a.title, 1, instr(a.title, ' - '))