SQLite semi Embedded database functionality in AutoIt

[randallc]

Hi,

Any developments here?

I have my sorting OK for limited group sorting for huge files. I will post later.

1. Is SQLite still the way to go?; I see there may be other SQLs which can do a "bulk insert" to get a csv file in quickly; seems to be an issue in SQLite of any wrapper?

2. Which SQLite version now?

3. Any thoughts further on including any in AutoIt?

4. @ptrex, have you tried to get ListView working in "snippets" according to latests sort, so the limited view is not just beginning or end? I don't look forward to it, and won't do it if you have already done so.

5. @ptrex , any further enhancements for @blink database?

Best, Randall

[piccaso]

i'm still working on it (doc's)

[Walkabout]

Been poking around as part of some preliminary work for a major piece of development work I'm going do for myself and I've come up with a major limitation (for this case) of SQLite.

Database level locking!

Means I can't have one script inserting data at the same time as I have another script selecting it. This obviously wouldn't matter for simple, single user applications, but anything more complex will need to use a database with row level locking.

Speaking of which, Firebird looks pretty good. Anyone done any work on connecting to a Firebird database using the client DLL (like the SQLite stuff here), rather than using ODBC?

Walkabout.

[piccaso]

AFAIK only table's get locked...

the '_SQlite_Query*' func's handle '%SQLITE_BUSY' stuff themself.

and sqlite is pretty fast so it shouldnt be locked for long...

i didnt know that firebird had a client dll

sounds interesting

[edit]

the upx'ed firebird dll is only 600k small

i didnt find doc's on apicalls (jet) but it looks like a good alternative.

Edited December 18, 2005 by piccaso

[randallc]

Hi,

@piccaso, I'd love to be able to "Split" a huge db so I can keep the sqlite3.exe speed; as it slows down disproportionately after about 100,000 rows in my example; (presumably a memory issue)

I can't get it to work, and don't understand why;; I can split it, but no quicker; I think because I don't understand the memory/ stout/ tempfiles/ filewriting of your command when it gets output.

Can you please help?

This is as close as i can come;

$f=FileOpen($sFile,1)

For $j = 0 To $TotalRandomRowsINT

$BaseStringLength=$MaxStringLength*$j

if ($TotalRandomRows>($MaxStringLength*($j -1))) then

;MsgBox(0,"","$MaxStringLength="&$MaxStringLength&@CRLF&"$BaseStringLength="&$BaseStringLength&@CRLF&"$TotalRandomRowsINT="&$TotalRandomRowsINT)

$s_Prepare=$s_Prepare1&$s_Prepare2& " LIMIT " & $MaxStringLength& " OFFSET " & $BaseStringLength&";"

If FileExists($SQLiteEXEScrDir) and $msg = $Export_exe Then

$sInput = ".mode tabs" & @CRLF

$sInput &= $s_Prepare & @CRLF

_SQLite_SQLiteExe($sDb, $sInput, $sOutput)

FileWriteLine($f,$sOutput)

ConsoleWrite('@@ Debug(193) : ' & "_SQLite_SQLiteExe" & "=" & Round(TimerDiff($nTimerQuery), 2) & " MSEC." & @LF & '>Error code: ' & @error & @LF) ;### Debug Console

Else

_SQLite_GetTextRowsOld(-1, $s_Prepare, $aResultRow, $iRows, $iColumns, $sErrorMsg, 64, False)

ConsoleWrite('@@ Debug(193) : ' & "_SQLite_GetTextOLDRows" & "=" & Round(TimerDiff($nTimerQuery), 2) & " MSEC." & @LF & '>Error code: ' & @error & @LF) ;### Debug Console

EndIf

EndIf

Next

FileClose($f)

Best, Randall

[piccaso]

@randallc

use sqlite.exe's inbuilt functions, they dont have memory problems

#include <sqlite.au3>
Local $sInp, $sOut
$sInp = ".mode tabs" & @CRLF
$sInp &= ".output 'out.cvs'" & @CRLF; The output File
$sInp &= "SELECT * FROM Orders;" & @CRLF; .dump always exports sql so we use select...
_SQLite_SQLiteExe("..\2Nwind.db",$sInp,$sOut)

exported 460800 rows x 14 Cols in about 11 seconds... Edited December 18, 2005 by piccaso

[ptrex]

@randallc

No major development has been done so far, working on other projects as well. But during Xmas Holidays me I hope to have some time available.

@Walkabout

Recordlocking is not supported as you can read on the website of SQLite. But this was not a problem, since SQLite and AutoIT are light products, with limited functionality compaired to other products. This is just the beauty of it all. Small but beautifull.

Taking a look a Firebird might be interesting though.

@Picasso

See Also here for FirebirdDB !! http://www.firebirdsql.org/

For distributing FB you need :

1 DLL (fbclient.dll) and 2 supporting files firebird.conf and firebird.msg and of course the database itself. Redistribution is simple, files just need to be copied.

[randallc]

@piccaso,

Thanks for your help... but I still have a problem.

if you can run the attached script, Listview sort column "e" (floating) (db col d), you will find the 500000 rows and above (100000) really takes 15-30 minutes.

I presume it has something to do with memory, machine speed, or something to do with the difficult sort (if sorted on listview column "b", my same machine tales 25seconds! - which is probably comparable to your 11secs on simple data with no sort??).

Let me know if you can do anything further; I cannot seem to make any difference to this by segmenting the output [which does help with your Table output]; I presume because the sort still has to occur on the total data.

Best, Randall

Edited December 19, 2005 by randallc

[piccaso]

@randallc

if you use 'ORDER BY' in my last posted example it takes 50 seconds.

if there is a index it takes about 16 seconds.

i'll take a look at your script...

@ptrex

I know, but i cant find an api reference...

[randallc]

Hi,

yes, I forgot to ask; i have struggled to get the index lines made for the other columns at creation (presumably...?)

I guess that is my problem; can you help with those lines?...

_SQLite_QueryNoResult (-1, "CREATE TABLE "&$sDbTable&"( a INTEGER PRIMARY KEY, b TEXT, c INTEGER, d FLOAT );")

_SQLite_QueryNoResult (-1, "CREATE INDEX b ON "&$sDbTable&"(b ASC);")

That should fix my problem, if I canmake it work!

I would think... If I just add more index lines, I don't get a table to sort using the previous commands!

eg

$s_Prepare = SELECT a , b, c , d FROM test ORDER BY a DESC, b ASC LIMIT 30;

no longer works; should it be "ORDER BY INDEX e DESC" if I have created such an index??

Thanks if you can explain it..

randall

Edited December 18, 2005 by randallc

[Mason]

I could be missing something, but is it possible to connect to other databases via network. For example, you have a hostname, database name, password, etc and you can manipulate that database rather than a local database in the examples. If it is not possible now, will it ever?

[piccaso]

@randallc

try

_SQLite_QueryNoResult (-1, "CREATE INDEX idxB ON " & $sDbTable & " ( b );")

or

_SQLite_QueryNoResult (-1, "CREATE INDEX idxB ON COLLATE " & $sDbTable & " ASC ( b );")

its just a syntax error.

check out _SQLite_ErrMsg().

Select Statement doesent change.

@Mason

there is a payed version of sqlite that can do such things

the free version we are using here isnt able to do so.

[randallc]

Thanks,

But I get "not an error" for this;

_SQLite_QueryNoResult (-1, "CREATE INDEX b ON "&$sDbTable&"(b ASC);")

MsgBox(0,"",_SQLite_ErrMsg())

and your table function works on the Select after

_SQLite_QueryNoResult (-1, "CREATE INDEX idxB ON " & $sDbTable & " ( b );")

and

$s_Prepare1 = "SELECT a,b,c,d FROM "&$sDbTable

$s_Prepare2 = " ORDER BY " & $s_OrderColumn & " " & $s_SortOrder & ", b ASC"

$s_Prepare=$s_Prepare1&$s_Prepare2&";"

, but _SQLite_SQLIteExe returns a blank Answer text file..

I'm going to take a while to learn all this, I fear!

Best, randall

[EDIT - PS I got it to work in the end; thanks; great for listview, and very fast for output (500000 in 9secs vs 5 mins best before!; I needed to include all columns except "primary integer key" in their individual indexes )]

(PS in example, cancel dialog to go to create random for this example script)

Edited December 19, 2005 by randallc

[jpm]

@petrex/@picasso

Just checking the latest sqlite.zip I notice a still missing return0 in totalchanges after seterror(1)

Continue the good work

[piccaso]

now i get it!

the Return Statement was missing completely

Fixed that...

SQLite.zip

Thank you jpm :">

[randallc]

hi,

I'm trying to import csv;

can anyone fix this code to work please?

FileDelete("test.db")

Local $sInput, $sOutput, $iRval

;$sInput = "create temporary table table1 (a,b,c); " & @CRLF

$sInput = "CREATE temporary TABLE table1 (a,b,c,d);" & @CRLF

$sInput &= ".separator tabs;" & @CRLF

$sInput &= ".import table2.csv table1;" & @CRLF

$sInput &= "CREATE TABLE table2 (keyfield INTEGER PRIMARY KEY, a,b,c,d);" & @CRLF

$sInput &= ".mode insert table2" & @CRLF

$sInput &= "begin; " & @CRLF

$sInput &= "SELECT null, * FROM table1;" & @CRLF

$sInput &= "commit;" & @CRLF

_SQLite_SQLiteExe("test.db", $sInput, $sOutput)

Thanks, Randall

[EDIT - btw, attached update to where I have gone...]

Edited December 21, 2005 by randallc

[piccaso]

'.import' always crashes (my) sqlite3.exe

and i dont know why (jet)...

[randallc]

Hi,

Since my PM I have got "import" to work at the command line; just having trouble calling it.

Best, Randall

[randallc]

OK, got one way; [for a csv with commas , 5 columns; no other testing]

;SQimportCsv.au3

#include <SQLite.au3>

#include <Array.au3>

$sDbTable="test"

global $Dbfile="test.db"

$DumpFile="table.txt"

$CSVFile="Input.csv"

$Success=FileDelete(@ScriptDir&"\"&$Dbfile)

if not $Success then MsgBox(0,"","not $Success")

Local $sInput, $sOutput, $iRval

$sInput = "CREATE TABLE temptable(a,b,c,d,e);" & @CRLF

MsgBox(0,"",'$sInput1 = ' & $sInput)

_SQLite_SQLiteExe($Dbfile, $sInput, $sOutput)

$sInput = '-separator , ' & $Dbfile& ' "'&".import "&$CSVFile&" temptable"&'"' & @CRLF

ConsoleWrite('$sInput2 = ' & $sInput)

MsgBox(0,"",'$sInput2 = ' & $sInput)

$SQLiteCommand="sqlite3.exe " & $sInput

$Command=@ComSpec & " /c "&$SQLiteCommand

RunWait($Command,@WorkingDir,@SW_HIDE)

Best, randall

[piccaso]

Added 'example_TsvImport.au3', Demonstrates Tab Seperated values Import

Updated bundled SQLite Version to 3.2.8 (Changelog)

SQLite.zip

Beware: SQLite3.exe crashes if you try to '.import' into a Table that does not exist...