Code : Tout sélectionner
#AutoIt3Wrapper_Run_AU3Check=n
#include <Array.au3>
#include <SQLite.au3>
;~ #include <SQLite.dll.au3> ; use only if you don't have the sqlite3.dll file by itself
#include "..\include\SQLiteExtLoad.au3"
; if you need to backup or restore database to/from memory/disk in any direction
;~ #include "..\include\SQLiteBackup.au3"
Main()
Global $hMemDB ; memory DB handle
;; file location may need to be changed to suit your particular setup
;; best demoed using a decent font with some Unicode coverage
Func Main()
;~ _SQLite_Startup(Default, 1) ; use .dll from include file and allow for Unicode console output
_SQLite_Startup("..\bin\sqlite3.dll", 1) ; use .dll already in script directory and allow for Unicode console output
If @error Then
MsgBox(0, "Fatal", "SQLite3.dll can't be found.")
Return
EndIf
;~ OnAutoItExitRegister("_SQLite_ShutDown")
; load auto-extension Unifuzz
_SQLite_LoadAutoExtension("..\bin\unifuzz.dll")
If @error Then
_ConsoleWrite("@error = " & @error & ', @extended = ' & @extended & @LF)
MsgBox(0, "Fatal", "Error loading extension Unifuzz.")
Return
EndIf
; open (create) memory database
$hMemDB = _SQLite_Open()
OnAutoItExitRegister("_MemDbClose")
_SQLite_Exec($hMemDB, "PRAGMA foreign_keys = 1;PRAGMA recursive_triggers = 1;") ; example use of some common pragmas
; let's use Sl3Answer which is a simple function (defined below) returning a single string result from an SQL query
_ConsoleWrite(_Sl3Answer($hMemDB, "select 'SQLite3 v' || sqlite_version() || ' - ' || unifuzz();") & @LF) ; show which versions are running
; some basic casing demo with on the fly data
Local $str = 'cRisTiñà ǢȘȤḹǒṰẄạỸӑ'
_ConsoleWrite(_Sl3Answer($hMemDB, "select upper("&X($str)&");") & @LF)
_ConsoleWrite(_Sl3Answer($hMemDB, "select lower("&X($str)&");") & @LF)
_ConsoleWrite(_Sl3Answer($hMemDB, "select proper("&X($str)&");") & @LF)
_ConsoleWrite(_Sl3Answer($hMemDB, "select unaccent("&X($str)&");") & @LF)
_ConsoleWrite(_Sl3Answer($hMemDB, "select proper(unaccent("&X($str)&"));") & @LF)
$str = 'weißig am raschütz'
_ConsoleWrite(_Sl3Answer($hMemDB, "select upper("&X($str)&");") & @LF)
_ConsoleWrite(_Sl3Answer($hMemDB, "select unaccent("&X($str)&");") & @LF)
_ConsoleWrite(_Sl3Answer($hMemDB, "select unaccent(upper("&X($str)&"));") & @LF)
$str = 'АК-ДОВУРАК 1'
_ConsoleWrite(_Sl3Answer($hMemDB, "select lower("&X($str)&");") & @LF)
_ConsoleWrite(_Sl3Answer($hMemDB, "select flip("&X($str)&");") & @LF)
; not only letters work:
_ConsoleWrite(_Sl3Answer($hMemDB, "select unaccent('₀₁₂₃₄₅₆₇₈₉ ⁰¹²³⁴⁵⁶⁷⁸⁹');") & @LF)
_ConsoleWrite(_Sl3Answer($hMemDB, "select unaccent('⅛ < ⅙ < ⅕ < ¼ < ⅓ < ⅜ < ⅖ < ½ < ⅗ < ⅝ < ⅔ < ¾ < ⅘ < ⅚ < ⅞');") & @LF)
_ConsoleWrite("strdup test start" & @LF)
_ConsoleWrite(_Sl3Answer($hMemDB, "select hexw(4488151);") & @LF)
_ConsoleWrite(_Sl3Answer($hMemDB, "select length(strdup('1', 4488151));") & @LF) ; limit = 2147483647
_ConsoleWrite("strdup test end" & @LF)
_ConsoleWrite("fuzzy search test start" & @LF)
_SQLite_Exec($hMemDB, "create table test (countrycode text, zip text, city text collate nocase);")
_SQLite_Exec($hMemDB, "insert into test values ('AD', 'AD600', 'Sant Julià de Lòria'), ('AT', '7540', 'Großmürbisch'), " & _
"('AU', '2602', 'O''Connor'), ('BD', '1830', 'Lechhraganj'), ('BE', '1820', 'Steenokkerzeel Melsbroek'), " & _
"('CZ', '341 42', 'Braníčkov'), ('CZ', '664 71', 'Braníškov'), ('CZ', '468 21', 'Bratříkov'), " & _
"('HR', '2222', 'Bratiškovci'), ('GL', '3980', 'Ittoqqortoormiit'), ('RU', '152600', 'МАЙМЕРЫ'), " & _
"('RU', '427400', 'БОЛГУРЫ'), ('RU', '612613', 'ЗАЙЦЕВЫ'), ('RU', '673051', 'БАЙХОР');")
Local $aRows, $iRows, $iCols
; LIKE is now overloaded
_SQLite_GetTable2d($hMemDB, "select * from test where city like '%julia %lori%';", $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)
; search for match up to 2 typos
_SQLite_GetTable2d($hMemDB, "select *, typos(city, 'grôssmurb%') as Errors from test where errors < 3;", $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)
_SQLite_GetTable2d($hMemDB, "select *, typos(city, 'o connor') as Errors from test where errors < 3;", $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)
_SQLite_GetTable2d($hMemDB, "select *, typos(city, 'Lechraganj') as Errors from test where errors < 3;", $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)
_SQLite_GetTable2d($hMemDB, "select *, typos(city, 'Stennokherzeel Mel%') as Errors from test where errors < 3;", $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)
; propose choice of misspellings
_SQLite_GetTable2d($hMemDB, "select *, typos(city, 'BranIszkov%') as Errors from test where errors < 5 order by errors;", $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)
_SQLite_GetTable2d($hMemDB, "select *, typos(city, 'Б_ЙЦ_РЫ') as Errors from test where errors < 3 order by errors;", $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)
_ConsoleWrite("fuzzy search test end" & @LF)
EndFunc
Func _MemDbClose()
_SQLite_Close($hMemDB)
EndFunc
Func _Sl3Answer($hDB, $sql)
Local $row
Local $rtn = _SQLite_QuerySingleRow($hDB, $sql, $row)
If $rtn = $SQLITE_OK Then
Return ($row[0])
Else
Return (SetError(@error, $rtn, ''))
EndIf
EndFunc ;==>_Sl3Answer
Func _ConsoleWrite($sString)
ConsoleWrite(BinaryToString(StringToBinary($sString, 4), 1))
EndFunc
Func X($s)
Return ("'" & StringReplace($s, "'", "''", 0, 1) & "'")
EndFunc ;==>X
Func XX($s)
Return (",'" & StringReplace($s, "'", "''", 0, 1) & "'")
EndFunc ;==>XX
Func Y($s)
Return ("X'" & Hex($s) & "'")
EndFunc ;==>Y
Func YY($s)
Return (",X'" & Hex($s) & "'")
EndFunc ;==>YY
Je suis preneur ^^