[Solved] Fast Real-Time File Read Write

[jchd]

3 minutes ago, Nine said:

Maybe SQLite does not react as Oracle do when manipulating ROWID

Yes it does, but since the login is the primary key here and it's expected to be a short string, that doesn't eat more cycles that storing + indexing + manipulating the rowid. In practice, I believe the OP will want to display the login in the chat app; retrieving, storing the rowid along the login didn't seem useful here. I don't expect non-unique login names in such context.

But imagine you want to log messages exchanged in a chat. Then you probably won't store actual login names in the msg table, but instead an integer primary key. Then the DDL of Users would rather be:

CREATE TABLE [Users](
  [Id] INTEGER PRIMARY KEY NOT NULL,
  [Login] CHAR NOT NULL CONSTRAINT [ckUsersLoginUniq] UNIQUE,
  [CredHash] BLOB NOT NULL,
  [Logged] INT NOT NULL DEFAULT 0,
  [Level] INT NOT NULL DEFAULT 1,
  [Guild] CHAR NOT NULL DEFAULT '');

That will create two indices: Id as primary key and ckUsersLoginUniq for login uniqueness

Remember than in SQLite, a rowid alias has to be declared as integer primary key, not int primary key.

[TheSaint]

9 hours ago, Ascer said:

If you can't post simple examples is ok just tell not everyones are masters of Autoit.

 

3 hours ago, Ascer said:

This is black magic for me without any Autoit source example i could say ehm..

Generally how it works here, is we teach you how to fish, not do the fishing for you.

We can advise on the right equipment and usage and even tutor you, but you must make an attempt yourself, which means in this instance, having a go with what you have been told and providing that code.

It doesn't matter if you fail or make seemingly silly mistakes or you think they will look that way to the rest of us, what matters is you made the attempt. We all started with the basics at some point and looked none too smart in the beginning.

jchd has kindly suggested you try SQL. There is plenty of information about SQL in the AutoIt Help file and many more examples etc at the forum can be found using the Search.

Have a go, and I promise you will learn more and appreciate more the help given for the hard bits, than if we did it all for you. 

In  short, it is a collaborative effort, between you and helper(s).

P.S. The helpers and example providers at this forum, are unpaid volunteers and doing so out of the goodness of their heart, while also showing appreciation for the gift of AutoIt.

[Nine]

Tested on 1k, 10k, 30k rows within memory DB attached to disk DB with a well balanced primary key.  It stays around 0.32 ms per access to a single row fetch.  I think with @jchdapproach, you should get the performance you are expecting.  Now, you will have to show us that you have made some effort to understand SQLite, before we give you the code "tout cuit dans le bec".

[jchd]

Well said @Nine [Pan sur le bec!] and thank you for investing time and effort to check that my assumptions roughly spot on. Three times more rows won't make any significant difference as index access in a B-tree of order x is O(logₓ N).

[LarsJ]

9 hours ago, TheSaint said:

Generally how it works here, is we teach you how to fish, not do the fishing for you.

This is also a very convenient opinion if you are too fat, old, lazy or simply don't have the necessary coding skills. The active supporters today are a bunch of chit-chatting guys and not really coders. It would be desirable if we could get some more coders and a little less chit-chatting guys.

[Jos]

2 hours ago, LarsJ said:

This is also a very convenient opinion if you are too fat, old, lazy or simply don't have the necessary coding skills. The active supporters today are a bunch of chit-chatting guys and not really coders. It would be desirable if we could get some more coders and a little less chit-chatting guys.

Did you wet your bed or something to trigger this unneeded response? 
Maybe next time just take a cold shower to clean/cool off and refrain from posting here is a better option?

Jos

Edited February 23, 2020 by Jos

[Ascer]

I have used dictionary to speed-up xml reading.

• Load database xml on program start.
• Transfer it to dictionary.
• Manage users via dictionary.
• Save users every some time.

Speed results for 100,000 users:

• Transfer xml to dictionary database take around 30s
• Finding user typing name as key take 0.1ms
• Saving database to file take 3s.

expandcollapse popup

;----------------------------------------------------------
; Config for script.
;----------------------------------------------------------
Global $TIME = 0
Global $FILE = "users.xml"                  ; Name of file with users.
Global $PATH = @ScriptDir & "\" & $FILE     ; Path where we create users file xml
Global $TOTAL = 100000                      ; Amount of users to create.

ObjEvent("AutoIt.Error", "error")           ; Register COM event handler.

;----------------------------------------------------------
; Create satabase file.
;----------------------------------------------------------
setTime("Creating database xml with " & $TOTAL & " users..")
createXMLDatabase($PATH, $TOTAL)
print("Database has been created.")
getTimeDiff()

;----------------------------------------------------------
; Transfer satabase from xml file to dictionary.
;----------------------------------------------------------
setTime("Transfering Database from xml file to dictionary it take some time don't close script..")
Dim $base = transferXMLToDictionary($FILE)
Dim $xmlbase = $base[0]
Dim $database = $base[1]
print("Transfer has end.")
getTimeDiff()

;----------------------------------------------------------
; Search database for user.
;----------------------------------------------------------
setTime("Serching dictionary for user nr " & $TOTAL & "..")
Dim $user = getDatabaseUser("User" & $TOTAL, $database)
If $user <> -1 Then
    print("User has been found.")
    print("name: " & $user.item("name"))
    print("passwd: " & $user.item("passwd"))
    print("range: " & $user.item("range"))
    print("group: " & $user.item("group"))
Else
    print("User not found.")
EndIf
getTimeDiff()

;----------------------------------------------------------
; Change user name for Ascer.
;----------------------------------------------------------
setTime("Change user name for Ascer..")
$user.item("name") = "Ascer"
print("Changed user name for Ascer")
getTimeDiff()

;----------------------------------------------------------
; Save database to file.
;----------------------------------------------------------
setTime("Save database to file xml..")
saveDatabaseToXML($path, $xmlbase, $database)
print("Saved database do file.")
getTimeDiff()

#Region 1.1, Functions.

Func saveDatabaseToXML($path, ByRef $xmlbase, ByRef $database)
    Dim $xml
    $xml &= '<?xml version="1.0" encoding="UTF-8" standalone="no"?>' & @CRLF
    $xml &= '<users>' & @CRLF
    For $user in $database.keys
        Dim $row = $database.item($user)
        $xml &= @TAB & '<user name="' & $row.item("name") & '" passwd="' & $row.item("passwd") & '" range="' & $row.item("range") & '" group="' & $row.item("group") & '"/>' & @CRLF
    Next
    $xml &= "</users>"
    FileDelete($path)
    FileWrite($path, $xml)
EndFunc ;==> Save database do file xml.

Func getDatabaseUser($name, ByRef $handle)
    Dim $user = $handle.item($name)
    If IsObj($user) Then Return $user
    Return -1
EndFunc ;==> Return info about user in database or -1 if not found.

Func transferXMLToDictionary($file)
    Dim $msxml = ObjCreate('MSXML2.DOMDocument.6.0')
    $msxml.load($file)
    Dim $users = $msxml.SelectNodes("//users/user")
    Dim $database = ObjCreate("Scripting.Dictionary")

    For $user In $users
        Dim $row = ObjCreate("Scripting.Dictionary")
        Dim $name = $user.getAttribute("name")
        $row.add("name", $name)
        $row.add("passwd", $user.getAttribute("passwd"))
        $row.add("range", $user.getAttribute("range"))
        $row.add("group", $user.getAttribute("group"))

        $database.add($name, $row)
    Next
    Dim $table = [$msxml, $database]
    Return $table
EndFunc ;==> Read path for file and export it to dictionary. Return array with [0] - xml handle, [1] - dictionary handle.

Func createXMLDatabase($path, $amount)
    Dim $xml
    $xml &= '<?xml version="1.0" encoding="UTF-8" standalone="no"?>' & @CRLF
    $xml &= '<users>' & @CRLF
    For $i = 1 To $amount
        $xml &= @TAB & '<user name="User' & $i & '" passwd="123456" range="6" group="none"/>' & @CRLF
    Next
    $xml &= "</users>"
    FileDelete($path)
    FileWrite($path, $xml)
EndFunc ;==> Create database xml with users

Func error()
    Return -1
EndFunc

Func setTime($msg="")
    print("setTime: " & $msg)
    $TIME = TimerInit()
EndFunc

Func getTimeDiff()
    print("getTimeDiff: " & Int(TimerDiff($TIME) * 100) / 100 & "ms")
EndFunc

Func print($data)
    Return ConsoleWrite($data & @CRLF)
EndFunc ;==> Wpisuje wartość + znak nowej linji do SCITE.print()

#EndRegion

 

[Nine]

FYI, creating the memory db takes 25ms.

Reading directly from disk instead of memory takes 0.65ms instead of 0.32ms.

Those values are for 32k rows.

Edited February 23, 2020 by Nine

[Ascer]

29 minutes ago, Nine said:

FYI, creating the memory db takes 25ms.

Reading directly from disk instead of memory takes 0.65ms instead of 0.32ms.

Those values are for 32k rows.

Ok mate could you post an example just like i do?

[condoman]

I would create a front end to drive a Robocopy call. Command line utility wicked fast.

[jchd]

To help you get a rough idea, here's a short sample of code with both disk and memory DBs:

expandcollapse popup

#include <SQLite.au3>


_SQLite_Startup()

Local $hDB = _SQLite_Open(@ScriptDir & "\test.sq3")

Local $login = 'natation'

Local $aRow
Local $t = TimerInit()
_SQLite_QuerySingleRow($hDB, "select * from users where login = '" & $login & "'", $aRow)
_ArrayDisplay($aRow, $login & " " & TimerDiff($t) & " ms (disk)")

_SQLite_Exec($hDB, "update users set logged = 1, guild = 'AutoIt programmers' where login = '" & $login & "'")

$t = TimerInit()
_SQLite_QuerySingleRow($hDB, "select * from users where login = '" & $login & "'", $aRow)
_ArrayDisplay($aRow, $login & " " & TimerDiff($t) & " ms (disk)")

; update a particular row on disk
$t = TimerInit()
_SQLite_Exec($hDB, "update users set logged = 1, guild = datetime('now') where login = '" & $login & "'")
ConsoleWrite("Update of row natation took " & TimerDiff($t) & " ms (dik)" & @LF)

; update a range of rows on disk
$t = TimerInit()
_SQLite_Exec($hDB, "update users set logged = 1, guild = datetime('now') where login like '%seq%'")
ConsoleWrite("Update of all rows where login start with 'seq' took " & TimerDiff($t) & " ms (disk)" & @LF)



_SQLite_Exec($hDB, "attach '' as mem")      ; memory temp DB

$t = TimerInit()
; copy Users table DDL from disk DB
_SQLite_QuerySingleRow($hDB, "select sql from sqlite_master where type = 'table' and name like 'users'", $aRow)
_SQLite_Exec($hDB, StringReplace($aRow[0], "create table ", "create table mem."))
_SQLite_Exec($hDB, "insert into mem.users select * from users")
_SQLite_Exec($hDB, "create table mem.changes (login char primary key not null) without rowid")
ConsoleWrite("Copy users table (109k rows + index) to memory took " & TimerDiff($t) & " ms" & @LF)

$t = TimerInit()
_SQLite_QuerySingleRow($hDB, "select * from mem.users where login = '" & $login & "'", $aRow)
_ArrayDisplay($aRow, $login & " " & TimerDiff($t) & " ms (memory)")

; update a particular row in memory
$t = TimerInit()
_SQLite_Exec($hDB, "update mem.users set logged = 1, guild = datetime('now') where login = '" & $login & "'")
_SQLite_Exec($hDB, "insert or ignore into mem.changes values ('" & $login & "')")
ConsoleWrite("Update of row natation took " & TimerDiff($t) & " ms (memory)" & @LF)

; update a range of rows in memory
$t = TimerInit()
_SQLite_Exec($hDB, "update mem.users set logged = 1, guild = datetime('now') where login like '%seq%'")
_SQLite_Exec($hDB, "insert or ignore into mem.changes values ('" & $login & "')")
ConsoleWrite("Update of all rows where login start with 'seq' took " & TimerDiff($t) & " ms (memory)" & @LF)


$t = TimerInit()
_SQLite_QuerySingleRow($hDB, "select * from mem.users where login = '" & $login & "'", $aRow)
_ArrayDisplay($aRow, $login & " " & TimerDiff($t) & " ms (memory)")


; copy changes back to disk
$t = TimerInit()
_SQLite_Exec($hDB, "replace into users select * from mem.users where login in (select login from mem.changes)")
ConsoleWrite("Copy back changes to disk took " & TimerDiff($t) & " ms" & @LF)

_SQLite_Close($hDB)

_SQLite_Shutdown()

 

[argumentum]

8 hours ago, Jos said:

or something to trigger this unneeded response?

The post may have been a bit harsh ( because I like TheSaint ), but I find the response justified, and would be eventually brought up at some random topic.
There are posts to just bash, not contributing to the topic at hand, based on the OP.
Those involved in aiding the OP can defend themselves without a new participant to "put the OP in it's place" and moving the attention from the issue at hand ( that of coding ).

As a non-programmer, I understand the limitations of the OP and requesting a functional example to start from.

As a veteran AutoIt forum member, I too would like to see what the OP has done. I too, don't wanna "mouth feed" code but see the OP becomes self sufficient.
And at times "mouth feed" code is what is needed.
I've done it myself with a good success rate in cranking up the OP brain   

[Jos]

I am fine with an well formulated opinion, not so much a response like that... hence my response without any nuance. 
There are some using many words and there are some (like me) that try to get away with messages as short as possible, but that response was uncalled for.

* Now back on topic please!

Jos

 

[Ascer]

Thanks for your sample @jchd but every single line i got error.

!   SQLite.au3 Error
--> Function: _SQLite_Query
--> Query:    select * from users where login = 'natation'
--> Error:    no such table: users

I play a little bit with ADODB and this allow to real-time read/write data to file.

• Time for create database.mdb with 100,000 users take 230s
• Time for get/set data user table take ~1ms
• I have no idea how to create table with more than 3 elements so far.

In example below i used 1000 users cuz creation of 100,000 take to long and required 1,12GB pc space

expandcollapse popup

Global $TIME = 0
Global $objConnection = ObjCreate("ADODB.Connection")
Global $objRecordSet = ObjCreate("ADODB.Recordset")
Global $errADODB = ObjEvent("AutoIt.Error","error")
Global $path = @ScriptDir & "\database.mdb"


;================================================
; Create database.
;================================================
databaseCreate($path)
databaseOpen($path)
setTime()
For $i = 1 To 1000
    databaseCreateTable($objConnection, "user" & $i, "name TEXT(50), passwd TEXT(50), range TEXT(1)")
    databaseInsertToTable("user" & $i, '(name, passwd, range) VALUES ("Ascer' & $i & '", "12234", "1");')
Next
getTimeDiff()

;================================================
; Get Databsae users.
;================================================
Dim $users = [1, 234, 678, 445, 277, 696, 978, 865]
For $i = 1 To UBound($users) - 1
    setTime()
    Dim $table = databaseGetTable("user" & $users[$i])
    getTimeDiff()
    For $j = 0 To UBound($table, 2) - 1
        print($table[0][$j])
    Next
Next

Func databaseCreate($path)
    FileDelete($path)
    Dim $newMdb = ObjCreate("ADOX.Catalog")
    $newMdb.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $path)
EndFunc

Func databaseOpen($path)
    $objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $path)
EndFunc

Func databaseCreateTable(ByRef $handle, $name, $fields)
    $objConnection.Execute("CREATE TABLE " & $name & "(" & $fields & ")")
EndFunc ;==> Create table inside database.

Func databaseInsertToTable($name, $fields)
    $objConnection.Execute("INSERT INTO " & $name & " " & $fields)
EndFunc ;==> Insert values to table database.

Func databaseGetTable($name)
    $objRecordSet.Open("SELECT " & "*" & " FROM " & $name, $objConnection)
    Dim $table = $objRecordSet.GetRows()
    $objRecordSet.Close
    Return $table
EndFunc ;==> Return table from database.

Func setTime($msg="")
    print("setTime: " & $msg)
    $TIME = TimerInit()
EndFunc

Func getTimeDiff()
    print("getTimeDiff: " & Int(TimerDiff($TIME) * 100) / 100 & "ms")
EndFunc

Func print($data)
    Return ConsoleWrite($data & @CRLF)
EndFunc ;==> Wpisuje wartość + znak nowej linji do SCITE.print()

Func error()
    Return 1
EndFunc

#EndRegion

 

[argumentum]

36 minutes ago, Ascer said:

but every single line i got error.

I had to change Local $hDB = _SQLite_Open(@ScriptDir & "\test_.sq3") to make it work. Other than that, the example is functional.

+>Setting Hotkeys...--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
Update of row natation took 27.6003616140532 ms (dik)
Update of all rows where login start with 'seq' took 29.4788042133634 ms (disk)
Copy users table (109k rows + index) to memory took 41.5781355930795 ms
Update of row natation took 0.273731753409803 ms (memory)
Update of all rows where login start with 'seq' took 13.7591764553012 ms (memory)
Copy back changes to disk took 0.238545586726559 ms
+>14:09:21 AutoIt3.exe ended.rc:0

Edit: ..to be clear, you are not using the database expected by the example.

Edited February 23, 2020 by argumentum
added link to DB ( .sq3 file )

[Ascer]

16 minutes ago, argumentum said:

I had to change Local $hDB = _SQLite_Open(@ScriptDir & "\test_.sq3") to make it work. Other than that, the example is functional.

+>Setting Hotkeys...--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
Update of row natation took 27.6003616140532 ms (dik)
Update of all rows where login start with 'seq' took 29.4788042133634 ms (disk)
Copy users table (109k rows + index) to memory took 41.5781355930795 ms
Update of row natation took 0.273731753409803 ms (memory)
Update of all rows where login start with 'seq' took 13.7591764553012 ms (memory)
Copy back changes to disk took 0.238545586726559 ms
+>14:09:21 AutoIt3.exe ended.rc:0

 

I have test.sq3 and I set valid path this same error.

[argumentum]

2 minutes ago, Ascer said:

I have test.sq3 and I set valid path this same error

would you:
1) add #AutoIt3Wrapper_UseX64=n to the top of the example.
2) download the example DB and overwrite your current one.
3) add ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)  below  _SQLite_Startup() and share that info to know your DLL version is ok.

The thing is that the error you have, makes no sense, so, lets troubleshoot.

 

[Ascer]

Done.

_SQLite_LibVersion=3.6.7

[argumentum]

1 minute ago, Ascer said:

_SQLite_LibVersion=3.6.7

That is from 2008, get an updated one from https://www.sqlite.org/download.html and try again  

[Ascer]

8 minutes ago, argumentum said:

That is from 2008, get an updated one from https://www.sqlite.org/download.html and try again  

I donload sqlite3.dll

Where should i put this one since my Windows can't find anyone other.