Sign in to follow this  
Followers 0
Marlo

Question regarding _SQLite UDF.

3 posts in this topic

So i started using the SQLite UDF which is now shipped with AutoIT along with the a sqlite dll i found on the net and all is working fine but i have a slight issue when it comes to saving a database.

I have 60,000 or so entries which i need to very quickly write to a new SQLite table which can be done within seconds when using a memory database like so

_SQLite_Startup()
If @error Then
MsgBox(0, @error, "Failed to start SQLite")
Return
EndIf

$hDB = _SQLite_Open()
If @error Then
MsgBox(0, @error, "Failed to create memory database.")
Return
EndIf

$Q = _SQLite_Exec($hDB, "Create table '" & $sName & "' (`auc` INT ( 11 ) NOT NULL , `item` INT ( 11 ) NOT NULL , `owner` VARCHAR ( 13 ) NOT NULL , " & _
"`bid` INT ( 11 ) NOT NULL , `buyout` INT ( 11 ) NOT NULL , `quantity` INT ( 11 ) NOT NULL , `timeleft` VARCHAR ( 11 ) NOT NULL)")

If @error Then
MsgBox(0, @error, "Failed to create table")
Return
EndIf



While True

$iItt += 1
$Line = FileReadLine($hFile)
If @error = -1 Then ExitLoop
$Info = StringRegExp($Line, '^.*?{.?auc":(\d*).*?"item":(\d*),"owner":"([\w]+)","bid":(\d*),"buyout":(\d*),"quantity":(\d*),"timeLeft":"([a-zA-Z_]+)"}', 1)
If $Info = 0 Then ContinueLoop

$Q = _SQLite_Exec($hDB, "INSERT INTO '" & $sName & "' VALUES ("&$Info[0]&", "&$info[1]&", '"&$info[2]&"', "&$info[3]&", "&$info[4]&", "&$info[5]&", '"&$info[6]&"');")
If @error Then
MsgBox(0, @error, $Q)
ContinueLoop
EndIf

If Mod($iItt, 100) = 0 Then
_Log($iItt & " itterations so far.")
EndIf

WEnd

But if i want to save the database to disc by using a permanent database like so

$hDB = _SQLite_Open(@scriptdir & "\database.aql")

Then the script takes about half an hour to write all 60,000~ entries to disc.

Is there a way i can use the memory database whilst it processes the information and THEN write the database to disc? I can see no functions related to this in the UDF :(


Click here for the best AutoIt help possible.Currently Working on: Autoit RAT

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

You have not less than three clean ways to do what you want.

A.) use the (the most efficient and simplest way)

B.) ATTACH your (or open a new) disk DB and use a CREATE ... AS statement:

CREATE table diskdb.tablename as SELECT * from memdb.sourcetablename;

(this won't copy other parts of the schema).

C.) use your code base but enclose your inserts in a transaction (which you should always choose to do even for a memory DB as soon as you have block inserts/updates).

Don't use single quotes around schema names (e.g. tables, columns, ...). Singles quotes are only used to delimit literal strings in SQL. Single quotes within literal strings MUST be escaped by doubling them (_SQLiteFastEscape).

Don't use back-quotes around schema names: prefer double quotes or square brackets for reserved names or names with spaces.

Edited by jchd

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

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
Sign in to follow this  
Followers 0