Sorting Arrays DB Way SQLite

Ciao primo,

thanks for your example here: http://www.thinbasic.com/community/s...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/s...inBasic-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

Code:

---

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

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

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