Sorting Arrays DB Way SQLite

[ErosOlmi]

Ciao primo,

thanks for your example here: http://www.thinbasic.com/community/showthread.php?12791-Sorting-Arrays
Yes you are right, actually it is not possible to sort more than one array at the same time and also not possible to sort array of UDT based on one element of the UDT.
Something to work on.

But thinking to the problem ... with the latest thinBasic beta 1.10.x http://www.thinbasic.com/community/showthread.php?12778-thinBasic-1-10-x
it is possible to use basic functionality of SQLite module to help in complex data structures where simple arrays cannot help.

The following is an example, based on your example, in which a SQLIte DB is created in memory (or on disk if needed) and use the SQL syntax to solve situations where simple arrays are a pain.
I've commented out PRINTL lines in order to see the speed. Un-comment them to see data but lower lMaxRows number of rows that are 100000



uses "Console"
uses "SQLIte"


'---Choose if create a DB on disk or in memory
'dim sDBName as string = app_sourcepath & "xxx.sqlite"
dim sDBName as string = ":memory:"

printl "Create DB" in %CCOLOR_FYELLOW
sqlite_Open sDBName, "C"

printl "Create Table and Index" in %CCOLOR_FYELLOW
sqlite_Exe "Drop Table If Exists MyTable"
sqlite_Exe "
Create Table MyTable (lNum INTEGER, sName TEXT);
Create Index idx1 on MyTable(lNum);
Create Index idx2 on MyTable(sName);
"
dim lMaxRows as long = 100000
dim lRow as Long


dim lNum as Long
dim sName as String

printl "Fill the table with ", lMaxRows, "records" in %CCOLOR_FYELLOW
for lRow = 1 to lMaxRows

lNum = Rnd(1, lMaxRows * 2)
sName = Chr$(rnd(48, 126)) . format$(lRow, "00000")
'PrintL sName . " -- " . lNum


SQLite_Exe "INSERT INTO MyTable (lNum, sName) VALUES (" . lNum . ", """ . sName . """);"


Next


printl "Read data sorted and print" in %CCOLOR_FYELLOW
sqlite_Select "Select * from MyTable order by lNum, sName"

lRow = 0
Do While sqlite_GetRow
Incr lRow
'printl strformat$("Rec {1}: {2}, {3}", lRow, SQLite_FieldValue("lNum"), SQLite_FieldValue("sName") )
Loop


SQLite_Close
printl "---All done---" in %CCOLOR_FLIGHTGREEN

WaitKey



It is possible to make also much more complex examples using multiple tables and making JOINs is SQL statements to connect heterogeneous data

Ciao
Eros

[primo]

thank you Eros very much for introducing SQLite, yes it works well and very suitable. but i need some time to adapt my procedural brain to the SQLite way of doing things. as an example it seems this line:
SQLite_Select "Select * from MyTable order by lNum, sName"
which sort the data, very compact and as if we instruct SQLite with a human like syntax.
whatever the situation it is very good addition
thanks

[ErosOlmi]

Making queries into a SQLite DB is done using standard SQL language.
There are many tutorial on the web, just search "SQLite SQL"
Example: https://sqlite.org/lang.html