Results 1 to 3 of 3

Thread: Sorting Arrays DB Way SQLite

  1. #1
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10

    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

    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
    Last edited by ErosOlmi; 30-08-2017 at 20:33.
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  2. #2
    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

  3. #3
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10
    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
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

Similar Threads

  1. Sorting arrays with built-in sorting functions
    By ErosOlmi in forum Execution speed tests
    Replies: 6
    Last Post: 21-02-2024, 21:36
  2. sorting the Linked List
    By primo in forum thinBasic General
    Replies: 18
    Last Post: 25-09-2017, 20:41
  3. Sorting Arrays
    By primo in forum thinBasic General
    Replies: 0
    Last Post: 30-08-2017, 18:20
  4. 3d arrays?
    By JosephE in forum thinDebug
    Replies: 2
    Last Post: 08-08-2010, 23:21
  5. Arrays
    By Charles Pegge in forum O2h Compiler
    Replies: 0
    Last Post: 17-03-2009, 16:09

Members who have read this thread: 0

There are no members to list at the moment.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •