Results 1 to 3 of 3

Thread: Sorting Arrays DB Way SQLite

Threaded View

  1. #1
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    50
    Posts
    8,187
    Blog Entries
    2
    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/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

Similar Threads

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

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
  •