Jump to content

SQLite Array Functions

SmOke_N

By SmOke_N,
in AutoIt Example Scripts

 Share

Recommended Posts

  • Moderators
SmOke_N Universalist

SmOke_N

Posted
  • Moderators
Posted (edited)

Recently I've been doing a lot of MySQL and SQLite db work.

In one of my apps I needed a faster method for unique arrays and sorting methods, so I utilized sqlite.

These are quick and dirty, not what I used in my apps, I remembered someone asking about it before so I thought I'd whip something up fast for ya'll to break or optimize.

You may enjoy the speed difference on the larger sized arrays.

There's no headers at the moment, I just don't have time at the moment to go into detail on on how to utilize the funcs, I made a few comments where someone might get confused.

I'll provide headers and examples at a later time, but for those of you that can understand the parameters, you'll get it working just fine.

Simple Example(s):

#include <Array.au3>
#include <mySQLite.au3> ; comment out if you're just going to paste on top of the code provided

Global $a_d[10001]

For $i = 0 To 10000
    $a_d[$i] = Chr(Random(101, 132, 1))
Next

MsgBox(64, "Example - 1", "Start")
Global $gn1 = TimerInit()
Global $a_u1 = _ArrayUnique($a_d)
ConsoleWrite(TimerDiff($gn1) / 1000 & @CRLF)
ConsoleWrite(UBound($a_u1) & @CRLF)

MsgBox(64, "Example - 2", "Start")
Global $gn2 = TimerInit()
Global $a_u2 = _mysqlite_ArrayUnique($a_d)
ConsoleWrite(TimerDiff($gn2) / 1000 & @CRLF)
ConsoleWrite(UBound($a_u2) & @CRLF)

MsgBox(64, "Example - 3", "Start")
Global $gn3 = TimerInit()
_ArraySort($a_d)
ConsoleWrite(TimerDiff($gn3) / 1000 & @CRLF)

MsgBox(64, "Example - 4", "Start")
Global $gn4 = TimerInit()
_mysqlite_ArraySort($a_d)
ConsoleWrite(TimerDiff($gn4) / 1000 & @CRLF)

As you can see, the arraysort function is much much slower than standard on smaller strings ( haven't tested with larger ).

The reason for most of it is the escaping of the strings.

Interested to see how it compares on larger strings, but eh...

#include-once

#include <Sqlite.au3>
#include <Sqlite.dll.au3>

Global $__gs_mysqlitedll = ""

Func _mysqlite_ArraySort(ByRef $a_args, $f_desc = False, $i_rowstart = 0, $i_rowend = 0, $i_colstart = 0, $f_sortnums = False)

    Local $i_dimensions = __mysqlite_AreDimensionsInRange($a_args)
    If @error Then Return SetError(@error, @extended, 0)

    ; get ubounds
    Local $a_ubs[$i_dimensions]
    For $iub = 0 To $i_dimensions - 1
        $a_ubs[$iub] = UBound($a_args, $iub + 1)
    Next

    __mysqlite_SetDefaultVals($f_desc, False)
    __mysqlite_SetDefaultVals($i_rowstart, 0)
    __mysqlite_SetDefaultVals($i_rowend, 0)
    __mysqlite_SetDefaultVals($f_sortnums, False)

    If $i_rowstart < 0 Or $i_rowstart > $a_ubs[0] - 1 Then Return SetError(3, 0, 0)
    If ($i_rowend > 0 And $i_rowend < $i_rowstart) Or $i_rowend > $a_ubs[0] - 1 Then
        Return SetError(4, 0, 0)
    EndIf
    If $i_rowend > 0 And $i_rowend < $a_ubs[0] - 1 Then $a_ubs[0] = $i_rowend + 1

    If $i_dimensions > 1 Then
        If $i_colstart = Default Or $i_colstart = -1 Then $i_colstart = 0
        If $i_colstart < 0 Or $i_colstart > $a_ubs[1] - 1 Then Return SetError(5, 0, 0)
    EndIf

    _SQLite_Startup(_mysqlite_GetDll())
    Local $h_db = _SQLite_Open(":memory:")
    If $h_db < 1 Then Return SetError(4, 0, 0)

    Local $s_create = "CREATE TEMP TABLE args (row_n,dat text);"
    If _SQLite_Exec($h_db, $s_create) <> $SQLITE_OK Then
        __mysqlite_CloseAndShutDownDB($h_db)
        Return SetError(5, 0, 0)
    EndIf

    _SQLite_Exec($h_db, "BEGIN;")
    Local $s_insert = ""
    Local $i_dimrow = ($a_ubs[0] - 1) - $i_rowstart
    For $irow = $i_rowstart To $a_ubs[0] - 1
        $s_insert = "INSERT INTO args VALUES(" & $irow & ","
        If $i_dimensions = 1 Then
            $s_insert &= _SQLite_Escape($a_args[$irow]) & ");"
        Else
            $s_insert &= _SQLite_Escape($a_args[$irow][$i_colstart]) & ");"
        EndIf
        _SQLite_Exec($h_db, $s_insert)
    Next
    _SQLite_Exec($h_db, "COMMIT;")

    Local $s_orderby = " ORDER BY dat "
    If $f_sortnums Then $s_orderby = " ORDER BY CAST(dat AS REAL)"
    Local $s_asc = $s_orderby & " ASC"
    If $f_desc Then $s_asc = $s_orderby & " DESC"

    ; query for distinct, return id's
    Local $s_distinct = "SELECT row_n FROM args" & $s_asc & ";"
    Local $h_query, $a_row
    If _SQLite_Query($h_db, $s_distinct, $h_query) <> $SQLITE_OK Then
        __mysqlite_CloseAndShutDownDB($h_db)
        Return SetError(6, 0, 0)
    EndIf

    Local $a_return = $a_args

    If $i_dimensions = 1 Then
        For $irow = $i_rowstart To $a_ubs[0] - 1
            If _SQLite_FetchData($h_query, $a_row) <> $SQLITE_OK Then ExitLoop
            $a_return[$irow] = $a_args[$a_row[0]]
        Next
    Else
        For $irow = $i_rowstart To $a_ubs[0] - 1
            If _SQLite_FetchData($h_query, $a_row) <> $SQLITE_OK Then ExitLoop
            For $icol = 0 To $a_ubs[1] - 1
                $a_return[$irow][$icol] = $a_args[$a_row[0]][$icol]
            Next
        Next
    EndIf
    _SQLite_QueryFinalize($h_query)
    __mysqlite_CloseAndShutDownDB($h_db)

    $a_args = $a_return
    Return 1
EndFunc

; the standard array unique had me confused on dimension and base
; these are the rows ->[n][n]<- these are the columns
; I switched them around I believe to match the above
; Also _ArrayUnique doesn't make sense with my version with "1" representing index "0" on row
Func _mysqlite_ArrayUnique($a_args, $i_row = 0, $i_column = 0, $f_case = False)

    Local $i_dimensions = __mysqlite_AreDimensionsInRange($a_args)
    If @error Then Return SetError(@error, @extended, 0)

    ; get ubounds
    Local $a_ubs[$i_dimensions]
    For $iub = 0 To $i_dimensions - 1
        $a_ubs[$iub] = UBound($a_args, $iub + 1)
    Next

    ; set defaults
    __mysqlite_SetDefaultVals($i_row, 0)
    __mysqlite_SetDefaultVals($f_case, False)

    ; is row out of bounds
    If $i_row < 0 Or $i_row > $a_ubs[0] - 1 Then Return SetError(3, 1, 0)

    ; is column out of bounds
    If $i_dimensions > 1 Then
        __mysqlite_SetDefaultVals($i_column, 0)
        If $i_column < 0 Or $i_column > $a_ubs[1] - 1 Then
            Return SetError(3, 2, 0)
        EndIf
    EndIf

    ; if it's the last row, let's make this short and quick
    Local $a_return[2] = [1]
    If $i_row = $a_ubs[0] - 1 Then
        If $i_dimensions = 1 Then
            $a_return[1] = $a_args[$i_row]
        Else
            $a_return[1] = $a_args[$i_row][$i_column]
        EndIf
        Return $a_return
    EndIf

    _SQLite_Startup(_mysqlite_GetDll())
    Local $h_db = _SQLite_Open(":memory:")
    If $h_db < 1 Then Return SetError(4, 0, 0)

    ; create table
    Local $s_nocase = " COLLATE NOCASE"
    If $f_case Then $s_nocase = ""

    ; using blob in case binary data is passed
    Local $s_create = "CREATE TEMP TABLE args (row_n,dat blob UNIQUE" & $s_nocase & ");"
    If _SQLite_Exec($h_db, $s_create) <> $SQLITE_OK Then
        __mysqlite_CloseAndShutDownDB($h_db)
        Return SetError(5, 0, 0)
    EndIf

    Local $i_dimcount = 0, $s_insert = ""

    _SQLite_Exec($h_db, "BEGIN;")
    For $irow = $i_row To $a_ubs[0] -1
        $i_dimcount += 1
        $s_insert = "INSERT OR IGNORE INTO args VALUES(" & $irow & ","
        If $i_dimensions = 1 Then
            $s_insert &= _SQLite_Escape($a_args[$irow]) & ")"
        Else
            $s_insert &= _SQLite_Escape($a_args[$irow][$i_column]) & ")"
        EndIf
        _SQLite_Exec($h_db, $s_insert)
    Next
    _SQLite_Exec($h_db, "COMMIT;")

    ; query for distinct, return id's
    Local $s_distinct = "SELECT row_n FROM args;"
    Local $h_query, $a_row
    If _SQLite_Query($h_db, $s_distinct, $h_query) <> $SQLITE_OK Then
        __mysqlite_CloseAndShutDownDB($h_db)
        Return SetError(6, 0, 0)
    EndIf

    ReDim $a_return[$i_dimcount + 1]
    Local $i_next = 0

    If $i_dimensions = 1 Then
        While _SQLite_FetchData($h_query, $a_row) = $SQLITE_OK
            $i_next += 1
            $a_return[$i_next] = $a_args[$a_row[0]]
        WEnd
    Else
        While _SQLite_FetchData($h_query, $a_row) = $SQLITE_OK
            $i_next += 1
            $a_return[$i_next] = $a_args[$a_row[0]][$i_column]
        WEnd
    EndIf
    _SQLite_QueryFinalize($h_query)
    __mysqlite_CloseAndShutDownDB($h_db)

    ReDim $a_return[$i_next + 1]
    $a_return[0] = $i_next

    Return $a_return
EndFunc

; simple being 1 dimension only
Func _mysqlite_ArrayUniqueSimple($a_args, $i_row = 0, $f_case = False)

    Local $i_ub = UBound($a_args)
    If Not $i_ub Then Return SetError(1, 0, 0)
    If UBound($a_args, 2) Then Return SetError(2, 0, 0) ; only 1 dimension allowed

    __mysqlite_SetDefaultVals($i_row, 0)
    If $i_row < 0 Or $i_row > $i_ub - 1 Then Return SetError(3, 0, 0)

    _SQLite_Startup(_mysqlite_GetDll())
    Local $h_db = _SQLite_Open(":memory:")
    If $h_db < 1 Then Return SetError(4, 0, 0)

    ; create table
    Local $s_nocase = " COLLATE NOCASE"
    __mysqlite_SetDefaultVals($f_case, False)
    If $f_case Then $s_nocase = ""

    ; using blob in case binary data is passed
    Local $s_create = "CREATE TEMP TABLE args (row_n,dat blob UNIQUE" & $s_nocase & ");"
    If _SQLite_Exec($h_db, $s_create) <> $SQLITE_OK Then
        __mysqlite_CloseAndShutDownDB($h_db)
        Return SetError(5, 0, 0)
    EndIf

    Local $i_dimcount = 0, $s_insert = ""
    _SQLite_Exec($h_db, "BEGIN;")
    For $irow = $i_row To $i_ub -1
        $i_dimcount += 1
        $s_insert = "INSERT OR IGNORE INTO args VALUES(" & $irow & ","
        $s_insert &= _SQLite_Escape($a_args[$irow]) & ")"
        _SQLite_Exec($h_db, $s_insert)
    Next
    _SQLite_Exec($h_db, "COMMIT;")

    ; query for distinct, return id's
    Local $s_distinct = "SELECT row_n FROM args;"
    Local $h_query, $a_row
    If _SQLite_Query($h_db, $s_distinct, $h_query) <> $SQLITE_OK Then
        __mysqlite_CloseAndShutDownDB($h_db)
        Return SetError(6, 0, 0)
    EndIf

    Local $a_return[$i_dimcount + 1], $i_next = 0

    While _SQLite_FetchData($h_query, $a_row) = $SQLITE_OK
        $i_next += 1
        $a_return[$i_next] = $a_args[$a_row[0]]
    WEnd
    _SQLite_QueryFinalize($h_query)
    __mysqlite_CloseAndShutDownDB($h_db)

    ReDim $a_return[$i_next + 1]
    $a_return[0] = $i_next

    Return $a_return
EndFunc

Func _mysqlite_SetDll($s_dll = "")
    $__gs_mysqlitedll = $s_dll
EndFunc

Func _mysqlite_GetDll()
    Return $__gs_mysqlitedll
EndFunc

Func __mysqlite_AreDimensionsInRange(ByRef $a_args, $i_maxdimensions = 2)

    Local $i_dimensions = 0

    ; check dimensions range
    For $i = 1 To 3
        If Not UBound($a_args, $i) Then ExitLoop
        $i_dimensions = $i
    Next

    ; is it an array
    If $i_dimensions = 0 Then Return SetError(1, 0, 0)

    ; only working with 1 or 2d arrays
    If $i_dimensions > 2 Then Return SetError(2, 0, 0)

    Return $i_dimensions
EndFunc

Func __mysqlite_SetDefaultVals(ByRef $v_var, $v_default)
    If $v_var = Default Or Int($v_var) = -1 Then $v_var = $v_default
EndFunc

Func __mysqlite_CloseAndShutDownDB(ByRef $h_db)
    _SQLite_Close($h_db)
    _SQLite_Shutdown()
    $h_db = 0
EndFunc
Edited by SmOke_N
Added a couple of examples so people could get the gist.

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.

Link to comment
Share on other sites
  • Moderators
SmOke_N Universalist

SmOke_N

Posted
  • Author
  • Moderators
Posted

I just did a large char/index array test.

I don't see the memory DB array sort even coming close to the standard udf version since it's created on the fly.

I'm sure if it were a disk db with the data already in it, it'd be much more proficient, a good chance I won't be using that one! lol

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.

Link to comment
Share on other sites
darkshark Wayfarer

darkshark

Posted
Posted (edited)

hi, thanks for share!

i'm using this test code:

#include-once
#include <Sqlite.au3>
#include <Sqlite.dll.au3>
#include <Array.au3>


Global $__gs_mysqlitedll = ""

Global $a_d[10001]
For $i = 0 To 10000
$a_d[$i] = Chr(Random(101, 132, 1))
Next
ConsoleWrite(@CRLF & "- First Ubound - " & UBound($a_d) & @CRLF & @CRLF)
Global $a_d2 = $a_d

ConsoleWrite("+ _ArrayUnique Test:" & @CRLF)
Global $gn1 = TimerInit()
Global $a_u1 = _ArrayUnique($a_d)
ConsoleWrite("_ArrayUnique time - " & StringFormat("%.2f", TimerDiff($gn1) / 1000) & @CRLF)
ConsoleWrite("_ArrayUnique ubound - " & UBound($a_u1) & @CRLF & @CRLF)

ConsoleWrite("+ _mysqlite_ArrayUnique Test:" & @CRLF)
Local $gn2 = TimerInit()
Global $a_u2 = _mysqlite_ArrayUnique($a_d)
Local $ubound = UBound($a_u2)
ConsoleWrite("_mysqlite_ArrayUnique time - " & StringFormat("%.2f", TimerDiff($gn2) / 1000) & @CRLF)
If $ubound = 0 Then
ConsoleWrite("- _mysqlite_ArrayUnique ubound - " & $ubound & @CRLF & @CRLF)
Else
ConsoleWrite("_mysqlite_ArrayUnique ubound - " & $ubound & @CRLF & @CRLF)
EndIf


ConsoleWrite("+ _ArraySort Test:" & @CRLF)
Global $gn3 = TimerInit()
_ArraySort($a_d)
ConsoleWrite("_ArraySort time - " & StringFormat("%.2f", TimerDiff($gn3) / 1000) & @CRLF & @CRLF)
_ArrayDisplay($a_d)

ConsoleWrite("+ _mysqlite_ArraySort Test:" & @CRLF)
Global $gn4 = TimerInit()
_mysqlite_ArraySort($a_d2)
ConsoleWrite("_mysqlite_ArraySort time - " & StringFormat("%.2f", TimerDiff($gn4) / 1000) & @CRLF & @CRLF)
_ArrayDisplay($a_d2)

MsgBox(0,'','end')


Func _mysqlite_ArraySort(ByRef $a_args, $f_desc = False, $i_rowstart = 0, $i_rowend = 0, $i_colstart = 0, $f_sortnums = False)

Local $i_dimensions = __mysqlite_AreDimensionsInRange($a_args)
If @error Then Return SetError(@error, @extended, 0)

; get ubounds
Local $a_ubs[$i_dimensions]
For $iub = 0 To $i_dimensions - 1
$a_ubs[$iub] = UBound($a_args, $iub + 1)
Next

__mysqlite_SetDefaultVals($f_desc, False)
__mysqlite_SetDefaultVals($i_rowstart, 0)
__mysqlite_SetDefaultVals($i_rowend, 0)
__mysqlite_SetDefaultVals($f_sortnums, False)

If $i_rowstart < 0 Or $i_rowstart > $a_ubs[0] - 1 Then Return SetError(3, 0, 0)
If ($i_rowend > 0 And $i_rowend < $i_rowstart) Or $i_rowend > $a_ubs[0] - 1 Then
Return SetError(4, 0, 0)
EndIf
If $i_rowend > 0 And $i_rowend < $a_ubs[0] - 1 Then $a_ubs[0] = $i_rowend + 1

If $i_dimensions > 1 Then
If $i_colstart = Default Or $i_colstart = -1 Then $i_colstart = 0
If $i_colstart < 0 Or $i_colstart > $a_ubs[1] - 1 Then Return SetError(5, 0, 0)
EndIf

_SQLite_Startup(_mysqlite_GetDll())
Local $h_db = _SQLite_Open(":memory:")
If $h_db < 1 Then Return SetError(4, 0, 0)

Local $s_create = "CREATE TEMP TABLE args (row_n,dat text);"
If _SQLite_Exec($h_db, $s_create) <> $SQLITE_OK Then
__mysqlite_CloseAndShutDownDB($h_db)
Return SetError(5, 0, 0)
EndIf

_SQLite_Exec($h_db, "BEGIN;")
Local $s_insert = ""
Local $i_dimrow = ($a_ubs[0] - 1) - $i_rowstart
For $irow = $i_rowstart To $a_ubs[0] - 1
$s_insert = "INSERT INTO args VALUES(" & $irow & ","
If $i_dimensions = 1 Then
$s_insert &= _SQLite_Escape($a_args[$irow]) & ");"
Else
$s_insert &= _SQLite_Escape($a_args[$irow][$i_colstart]) & ");"
EndIf
_SQLite_Exec($h_db, $s_insert)
Next
_SQLite_Exec($h_db, "COMMIT;")

Local $s_orderby = " ORDER BY dat "
If $f_sortnums Then $s_orderby = " ORDER BY CAST(dat AS REAL)"
Local $s_asc = $s_orderby & " ASC"
If $f_desc Then $s_asc = $s_orderby & " DESC"

; query for distinct, return id's
Local $s_distinct = "SELECT row_n FROM args" & $s_asc & ";"
Local $h_query, $a_row
If _SQLite_Query($h_db, $s_distinct, $h_query) <> $SQLITE_OK Then
__mysqlite_CloseAndShutDownDB($h_db)
Return SetError(6, 0, 0)
EndIf

Local $a_return = $a_args

If $i_dimensions = 1 Then
For $irow = $i_rowstart To $a_ubs[0] - 1
If _SQLite_FetchData($h_query, $a_row) <> $SQLITE_OK Then ExitLoop
$a_return[$irow] = $a_args[$a_row[0]]
Next
Else
For $irow = $i_rowstart To $a_ubs[0] - 1
If _SQLite_FetchData($h_query, $a_row) <> $SQLITE_OK Then ExitLoop
For $icol = 0 To $a_ubs[1] - 1
$a_return[$irow][$icol] = $a_args[$a_row[0]][$icol]
Next
Next
EndIf
_SQLite_QueryFinalize($h_query)
__mysqlite_CloseAndShutDownDB($h_db)

$a_args = $a_return
Return 1
EndFunc

; the standard array unique had me confused on dimension and base
; these are the rows ->[n][n]<- these are the columns
; I switched them around I believe to match the above
; Also _ArrayUnique doesn't make sense with my version with "1" representing index "0" on row
Func _mysqlite_ArrayUnique($a_args, $i_row = 0, $i_column = 0, $f_case = False)

Local $i_dimensions = __mysqlite_AreDimensionsInRange($a_args)
If @error Then Return SetError(@error, @extended, 0)

; get ubounds
Local $a_ubs[$i_dimensions]
For $iub = 0 To $i_dimensions - 1
$a_ubs[$iub] = UBound($a_args, $iub + 1)
Next

; set defaults
__mysqlite_SetDefaultVals($i_row, 0)
__mysqlite_SetDefaultVals($f_case, False)

; is row out of bounds
If $i_row < 0 Or $i_row > $a_ubs[0] - 1 Then Return SetError(3, 1, 0)

; is column out of bounds
If $i_dimensions > 1 Then
__mysqlite_SetDefaultVals($i_column, 0)
If $i_column < 0 Or $i_column > $a_ubs[1] - 1 Then
Return SetError(3, 2, 0)
EndIf
EndIf

; if it's the last row, let's make this short and quick
Local $a_return[2] = [1]
If $i_row = $a_ubs[0] - 1 Then
If $i_dimensions = 1 Then
$a_return[1] = $a_args[$i_row]
Else
$a_return[1] = $a_args[$i_row][$i_column]
EndIf
Return $a_return
EndIf

_SQLite_Startup(_mysqlite_GetDll())
Local $h_db = _SQLite_Open(":memory:")
If $h_db < 1 Then Return SetError(4, 0, 0)

; create table
Local $s_nocase = " COLLATE NOCASE"
If $f_case Then $s_nocase = ""

; using blob in case binary data is passed
Local $s_create = "CREATE TEMP TABLE args (row_n,dat blob UNIQUE" & $s_nocase & ");"
If _SQLite_Exec($h_db, $s_create) <> $SQLITE_OK Then
__mysqlite_CloseAndShutDownDB($h_db)
Return SetError(5, 0, 0)
EndIf

Local $i_dimcount = 0, $s_insert = ""

_SQLite_Exec($h_db, "BEGIN;")
For $irow = $i_row To $a_ubs[0] -1
$i_dimcount += 1
$s_insert = "INSERT OR IGNORE INTO args VALUES(" & $irow & ","
If $i_dimensions = 1 Then
$s_insert &= _SQLite_Escape($a_args[$irow]) & ")"
Else
$s_insert &= _SQLite_Escape($a_args[$irow][$i_column]) & ")"
EndIf
_SQLite_Exec($h_db, $s_insert)
Next
_SQLite_Exec($h_db, "COMMIT;")

; query for distinct, return id's
Local $s_distinct = "SELECT row_n FROM args;"
Local $h_query, $a_row
If _SQLite_Query($h_db, $s_distinct, $h_query) <> $SQLITE_OK Then
__mysqlite_CloseAndShutDownDB($h_db)
Return SetError(6, 0, 0)
EndIf

ReDim $a_return[$i_dimcount + 1]
Local $i_next = 0

If $i_dimensions = 1 Then
While _SQLite_FetchData($h_query, $a_row) = $SQLITE_OK
$i_next += 1
$a_return[$i_next] = $a_args[$a_row[0]]
WEnd
Else
While _SQLite_FetchData($h_query, $a_row) = $SQLITE_OK
$i_next += 1
$a_return[$i_next] = $a_args[$a_row[0]][$i_column]
WEnd
EndIf
_SQLite_QueryFinalize($h_query)
__mysqlite_CloseAndShutDownDB($h_db)

ReDim $a_return[$i_next + 1]
$a_return[0] = $i_next

Return $a_return
EndFunc

; simple being 1 dimension only
Func _mysqlite_ArrayUniqueSimple($a_args, $i_row = 0, $f_case = False)

Local $i_ub = UBound($a_args)
If Not $i_ub Then Return SetError(1, 0, 0)
If UBound($a_args, 2) Then Return SetError(2, 0, 0) ; only 1 dimension allowed

__mysqlite_SetDefaultVals($i_row, 0)
If $i_row < 0 Or $i_row > $i_ub - 1 Then Return SetError(3, 0, 0)

_SQLite_Startup(_mysqlite_GetDll())
Local $h_db = _SQLite_Open(":memory:")
If $h_db < 1 Then Return SetError(4, 0, 0)

; create table
Local $s_nocase = " COLLATE NOCASE"
__mysqlite_SetDefaultVals($f_case, False)
If $f_case Then $s_nocase = ""

; using blob in case binary data is passed
Local $s_create = "CREATE TEMP TABLE args (row_n,dat blob UNIQUE" & $s_nocase & ");"
If _SQLite_Exec($h_db, $s_create) <> $SQLITE_OK Then
__mysqlite_CloseAndShutDownDB($h_db)
Return SetError(5, 0, 0)
EndIf

Local $i_dimcount = 0, $s_insert = ""
_SQLite_Exec($h_db, "BEGIN;")
For $irow = $i_row To $i_ub -1
$i_dimcount += 1
$s_insert = "INSERT OR IGNORE INTO args VALUES(" & $irow & ","
$s_insert &= _SQLite_Escape($a_args[$irow]) & ")"
_SQLite_Exec($h_db, $s_insert)
Next
_SQLite_Exec($h_db, "COMMIT;")

; query for distinct, return id's
Local $s_distinct = "SELECT row_n FROM args;"
Local $h_query, $a_row
If _SQLite_Query($h_db, $s_distinct, $h_query) <> $SQLITE_OK Then
__mysqlite_CloseAndShutDownDB($h_db)
Return SetError(6, 0, 0)
EndIf

Local $a_return[$i_dimcount + 1], $i_next = 0

While _SQLite_FetchData($h_query, $a_row) = $SQLITE_OK
$i_next += 1
$a_return[$i_next] = $a_args[$a_row[0]]
WEnd
_SQLite_QueryFinalize($h_query)
__mysqlite_CloseAndShutDownDB($h_db)

ReDim $a_return[$i_next + 1]
$a_return[0] = $i_next

Return $a_return
EndFunc

Func _mysqlite_SetDll($s_dll = "")
$__gs_mysqlitedll = $s_dll
EndFunc

Func _mysqlite_GetDll()
Return $__gs_mysqlitedll
EndFunc

Func __mysqlite_AreDimensionsInRange(ByRef $a_args, $i_maxdimensions = 2)

Local $i_dimensions = 0

; check dimensions range
For $i = 1 To 3
If Not UBound($a_args, $i) Then ExitLoop
$i_dimensions = $i
Next

; is it an array
If $i_dimensions = 0 Then Return SetError(1, 0, 0)

; only working with 1 or 2d arrays
If $i_dimensions > 2 Then Return SetError(2, 0, 0)

Return $i_dimensions
EndFunc

Func __mysqlite_SetDefaultVals(ByRef $v_var, $v_default)
If $v_var = Default Or Int($v_var) = -1 Then $v_var = $v_default
EndFunc

Func __mysqlite_CloseAndShutDownDB(ByRef $h_db)
_SQLite_Close($h_db)
_SQLite_Shutdown()
$h_db = 0
EndFunc

and in my console i have this:

- First Ubound - 10001

+ _ArrayUnique Test:

_ArrayUnique time - 63.89

_ArrayUnique ubound - 33

+ _mysqlite_ArrayUnique Test:

_mysqlite_ArrayUnique time - 9.42

_mysqlite_ArrayUnique ubound - 33

+ _ArraySort Test:

_ArraySort time - 1.21

+ _mysqlite_ArraySort Test:

_mysqlite_ArraySort time - 12.69

the "_mysqlite_ArrayUnique" is really more fast than "_ArrayUnique", but the "_ArraySort" is more fast than "_mysqlite_ArraySort"

i'm using Win 7, 32 bits!

Edited by darkshark
Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...