SQLite semi Embedded database functionality in AutoIt

[ptrex]

@Randallc

[piccaso]

Changed _SQLite_QuerySingleResult() now uses 'sqlite3_get_table' API

(only indirect, a direct/faster version is on its way...)

Improved example_browser.au3

SQLite.zip

@randallc

_SQLite_QuerySingleColumn() would make no sence.

Just 'SELECT' only one column

#include "sqlite.au3"
Local $aResult, $iRow,$iCol,$sErr
_SQLite_Startup()
_SQLite_Open()
_SQLite_QueryNoResult(-1,"CREATE TABLE test (a,b,c)")
_SQLite_QueryNoResult(-1,"INSERT INTO test VALUES ('1','2','3')")
_SQLite_QueryNoResult(-1,"INSERT INTO test VALUES ('1a','2a','3a')")
_SQLite_QueryNoResult(-1,"INSERT INTO test VALUES ('1b','2b','3b')")
_SQLite_GetTable2d (-1,"SELECT a FROM test",$aResult, $iRow,$iCol,$sErr)
MsgBox (0,"Column 'a'",_SQLite_Display2DResult($aResult,-1,True))
_SQLite_Close()
_SQLite_Shutdown()

@ptrex

sorry, i Couldnt re/compile litex.

maybe you'll ask one of the dev's

[randallc]

Hi @piccaso,

_SQLite_QuerySingleColumn() would make no sence.

Perhaps I mean rows;

If I have 100,000 rows and I only want 1000 in my listview,

Fetchdata too slow.

Gettable too big and slow for a small task.; or can I specify start and end rows?

I need GetRow to be faster than FetchData?

Thanks, Randall

[piccaso]

@randallc

of course you can

#include "SQLite.au3"

Local $aResult, $iRow,$iCol,$sErr,$sSql

_SQLite_Startup()

_SQLite_Open(); Load :memory: database

_SQLite_QueryNoResult(-1,"CREATE TABLE test (a,b,c);"); Create a Table

for $i = 1 to 100

$sSql = $sSql & "INSERT INTO test VALUES (random(*),random(*),random(*));"

Next

_SQLite_QueryNoResult(-1,$sSql); INSERTing 100 Radndom Rows.

_SQLite_GetTable2d (-1,"SELECT ROWID,* FROM test LIMIT 6 OFFSET 4;",$aResult, $iRow,$iCol,$sErr)

MsgBox (0,"Using LIMIT & OFFSET",_SQLite_Display2DResult($aResult,0,True))

_SQLite_GetTable2d (-1,"SELECT ROWID,* FROM test WHERE ROWID >= '5' AND ROWID <= '10'; ",$aResult, $iRow,$iCol,$sErr)

MsgBox (0,"Using WHERE & ROWID",_SQLite_Display2DResult($aResult,0,True))

_SQLite_Close()

_SQLite_Shutdown()

note that:

ROWID is always present ( integer primary key )

and you dont have to 'SELECT' it to use it in 'WHERE' Clause

I did it just for the example

and if you want to 'SELECT' it you have to specify it ( * doesent match that one )

check doc's on SELECT for LIMIT, OFFSET..

Edited December 10, 2005 by piccaso

[randallc]

Great!

Sorry I don't understand the SQL syntax yet; I keep hoping I'll get familiar with it the more I use it.

This should solve most of my speed issues; Except for the total table write to text or transfer to Excel, which will remain at about 1/3 speed, I think; if that is the only dosadvantage of the DLL method and we can change version easily, it certainly looks the most attractive depite some speed issues.

Best, Randall

[piccaso]

@randallc

its faster now

Using LIMIT and removing ' If $iRowCnt > GUICtrlRead($Tab1combo1) Then ExitLoop '...

and some little things i dont remeber... (use windiff if you want to know )

sorry, i'm having a hard time reading your code, still working on it...

[edit] uploaded new one

Edited December 10, 2005 by piccaso

[randallc]

Fantastic!

Sorts listviews of limited numbers at least as fast as the litex wrapper one.

It's only going to be a problem if you try to open all of a huge database instead of a portion (eg blink's database with OWC 270,000 x 700!)

I will manage to avoid that except when re-saving a sorted large file, for instance [still 30% the speed of wrapper for that]

btw to get the correct column sorting in listview (the one to the left of that clicked sorts now)

$i_OrderNumber&=1

at start of each getdata.

I will post again later.

Thanks again,

Randall

[EDIT - I guess we can apply the same principles to the gettable calls for getting the whole file.....

More to come...?]

Edited December 10, 2005 by randallc

[randallc]

Hi @ piccaso,

No - the only reason the DLL sqlite can catch up in listview is because of the GUIListView Create Item slowing down the wrapper;

All functions other than listview and insertion will be 3-4x faster with the wrapper sqlite if we have to get one item at a time and loop through them in AutoIt..

oh well... I'd be interested to hear if you come up with anything else.

Best, Randall

[piccaso]

com is faster, i cant change that

if you look at other sqlite browsers that use the dll you'll see that they are even faster, but keep in mind that we are using a interpreter here...

so you can only choose between fast com and an old version with bugs

or a latest version

dependig on what you want to do.

i have some ideas but i dont think it will beat the com wrapper in speed...

[randallc]

Hi,

I agree;

1. Gettable needs to do its reading in chunks of, say, 10000 (or even 100,000) as I can jam memory with 10^6 rows at present.

2. Similarly the string concat for a pointer on "insert table" I have had to limit the chunks to 100,000 in my script.

3. I can emulate the speed (nearly) of com for a "total" gettable by checking if "sqlite.exe" is in appropriate directory, and using that instead (If present, fileinstall..) (do a dump and modify the text file produced); if not present, accept getable speed. This seems to give flexibility to use current sqlite by dll and option of speed for people with a big task.

Let me know if you have other thoughts...

Best, Randall

Edited December 11, 2005 by randallc

[randallc]

@piccaso;

here's the example dump and process; [assumes "sqlite.exe" in include directory]

10000 rows wrapper (litex COM) 5800;// dump (sqlite.exe) 6900 // GetTable (Autoit DLL call) 22500 //on a slowish machine

best, randall

Edited December 12, 2005 by randallc

[piccaso]

@randallc

could you post getTable example too...

thanks

btw:

i think wrapping sqlite.exe is better than using the com wrapper (no offense ptrex )

because you can the latest version...

trids started something like this

http://www.autoitscript.com/forum/index.ph...indpost&p=61614

Edited December 11, 2005 by piccaso

[piccaso]

@randallc

you dont have to process it...

.mode MODE ?TABLE? Set output mode where MODE is one of:

csv Comma-separated values

column Left-aligned columns. (See .width)

html HTML <table> code

insert SQL insert statements for TABLE

line One value per line

list Values delimited by .separator string

tabs Tab-separated values

tcl TCL list elements

Try this: (dumped 10000 rows in 150msec)

Local $sDumpFile = "dump.csv"
Local $sDbFile = "sqlite3.db"
Local $sDbTable = "test"
Local $sInstructionsFile = "sqlite.inst"

Local $nTimer,$hFp
if FileExists($sDumpFile) Then FileDelete($sDumpFile)
if FileExists($sInstructionsFile) Then FileDelete($sInstructionsFile)   

$hFp = FileOpen($sInstructionsFile,2)
if $hFp = -1 Then Exit -1
FileWriteLine($hFp,".output " & $sDumpFile)
FileWriteLine($hFp,".mode tabs " & $sDbTable)
FileWriteLine($hFp,"SELECT * FROM " & $sDbTable & ";")
FileClose($hFp)

$nTimer = TimerInit()
RunWait(@ComSpec & " /c sqlite3.exe " & $sDbFile & " < " & $sInstructionsFile,@WorkingDir,@SW_HIDE)
MsgBox (0,"Timer",TimerDiff($nTimer))

[randallc]

@piccaso

Very fast!

Can you get the rows in lines by this method? -All seems to be tabs. [EDIT; no - opens OK in Word, not notepad; tabs and lines!]

I will post GetTable; but I have not yet fixed the grouping there (I have done in make table)

Best, randall

[EDIT; Ignore query- opens OK in Word, not notepad; tabs and lines!] - fantastic; I'll explore more for my purposes

Edited December 12, 2005 by randallc

[randallc]

Hi,

Old time and new time write to console after "export to Text button"

DLL /Com wrapper / sqliteExe

11800 / 3300 / 700!

Best , Randall

PS @piccaso, are you thinking you can use this for all the functions, or mixing DLL calls with sqlite.exe?

Edited December 12, 2005 by randallc

[piccaso]

@randallc

i'm allready working on a wrapping function.

but updateing a listview will be slower that way (i think)

it generates html too, maybe using an ie obj. is faster here...

time wil tell...

but you wont be able to mix it, just use it on the same db...

Edited December 12, 2005 by piccaso

[piccaso]

Added _SQLite_SQLiteExe() Wrapper for SQLite3.exe

(the name may change, it sounds silly... any sugestions?)

... and example (example_SQLiteExe.au3)

SQLite.zip

[randallc]

Hi,

I am watching, but not sure where you are going still; you are in fact using a mixture now within one script?

I am trying to avoid filling memory with arrays or large strings;

how will you decide if a file is too big for a fileread?;

$sOutput = FileRead($sOutputFile,FileGetSize($sOutputFile))

and what will you do instead?

See also my switch in "Write" function below depending whether sqlite.exe is present or not,

and the loop for over 100,000 lines (should I calculate memory size using columns as well... oh well...)

Edited December 19, 2005 by randallc

[piccaso]

why should i do something ?

the coder decides how many rows are SELECT'ed and if they are read back out or dumped (.output & .dump ...)

btw. specifying another .output <filename> will override .output stout...

[ptrex]

@picasso

the name may change, it sounds silly... any sugestions?

SQLite.exe is a commandline tool of SQLite. Why not call it _SQL_Cmd or SQL_CmdTool,

at least call the beast the name it already has.

I haven' t tested it yet will do for sure this week.

Thanks