PBOSL - SQLITE

dmoc · Post by **dmoc** » Thu Jan 05, 2006 2:45 pm

A request to the PBOSL people, posted here so others may comment. First, thanks for the lib, very useful. I'm using the sqlite part so my first request is can the help file be updated? I noticed one of the demos use SQLite3_Prepare() for which there is no help doc (makes me wonder what else may be undocumented). I would also like to use sqlite with a db in memory, hopefully to improve performance. Idea would be to periodically write to disk but only when convenient. I know PBOSL is only wrapping sqlite but wondered if someone would be kind enough to see if they could apply the patch mentioned >here<

Update: Using ":memory:" for db name works and in my little test shows 4x speed-up when writing to the db! Just need a convenient way to load an existing one from disk and write it back when finished.

Another: ATTACH and CREATE TABLE can be used to populate the mem db from a disk db but it's clunky - you have to do it for each table. Still need an efficient method for updating disk db.

Post by **Kiffi** » Thu Jan 05, 2006 3:57 pm

Hello dmoc,

Another: ATTACH and CREATE TABLE can be used to populate the mem db
from a disk db but it's clunky - you have to do it for each table. Still need
an efficient method for updating disk db.

do you mean something like this?:

Code: Select all

Procedure.s GetTableNames(lDataBaseHandle.l)             ; Returns the tablenames of the specified database
  
  DefType.SQLite3_Recordset myRS
  DefType.s sSQL, sTablename, LastMessage
  
  If lDataBaseHandle = 0
    LastMessage = "Wrong Database-Handle"
    ProcedureReturn ""
  EndIf
  
  sSQL = "Select tbl_name From sqlite_master Where type='table' Order By tbl_name;"
  
  If SQLite3_GetRecordset(sSQL, lDataBaseHandle, @myRS )
    If myRS\Handle 
      While myRS\EOF=0
        If SQLite3_GetRecordsetValueByIndex(0, @myRS)
          sTablename + myRS\sValue + ";"
        EndIf
        SQLite3_RecordsetMoveNext(@myRS)
      Wend
    EndIf
  EndIf
  
  SQLite3_ReleaseRecordset(@myRS)
  
  If Right(sTablename,1)=";"
    sTablename = Left(sTablename,Len(sTablename)-1)
  EndIf
  
  ProcedureReturn sTablename
  
EndProcedure

Procedure.s GetSchema(sTablename.s, lDataBaseHandle.l)   ; Returns a schema of a table
  
  DefType.SQLite3_Recordset myRS
  DefType.s sSQL, sSchema, LastMessage
  
  If lDataBaseHandle = 0
    LastMessage = "Wrong Database-Handle"
    ProcedureReturn ""
  EndIf
  
  If sTablename = ""
    LastMessage = "Tablename is empty"
    ProcedureReturn ""
  EndIf
  
  sSQL + "SELECT sql FROM "
  sSQL + "(SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) "
  sSQL + "WHERE tbl_name = '" + sTablename + "' AND type!='meta' "
  sSQL + "ORDER BY type DESC, name "
  
  If SQLite3_GetRecordset(sSQL, lDataBaseHandle, @myRS )
    If myRS\Handle 
      If SQLite3_GetRecordsetValueByIndex(0, @myRS)
        sSchema + myRS\sValue 
      EndIf
    EndIf
  EndIf
  
  SQLite3_ReleaseRecordset(@myRS)
  
  ProcedureReturn sSchema
  
EndProcedure

If SQLite3_InitLib("C:\winnt\system32\sqlite3.dll")
  
  dbHandler = SQLite3_CreateDatabase(":memory:",1)
  
  If dbHandler
    
    SQL$="Create Table TestTable (Test1, Test2, Test3)"
    
    If SQLite3_Execute(SQL$, dbHandler)
      
      For lCounter = 0 To 9
        SQL$="Insert Into TestTable (Test1, Test2, Test3) Values ('T1V" + Str(lCounter) + "', 'T2V" + Str(lCounter) + "', 'T3V" + Str(lCounter) + "')"
        If SQLite3_Execute(SQL$, dbHandler)
        Else
          Debug SQLite3_GetLastMessage()
        EndIf
      Next
      
      SQL$ = "Attach Database 'D:\FromMemoryToDisk.db' As M2D_DB"
      
      If SQLite3_Execute(SQL$, dbHandler)
      
        ; Get all tablenames
        MemoryTablenames$ = GetTableNames(dbHandler)
        
        For lCounter = 0 To CountString(MemoryTablenames$, ";")
          
          MemoryTable$ = StringField(MemoryTablenames$, lCounter, ";")
          
          ; Get schema from table
          Schema$ = GetSchema(MemoryTable$, dbHandler)
          
          Schema$ = ReplaceString(Schema$, "CREATE TABLE " + MemoryTable$, "CREATE TABLE M2D_DB." + MemoryTable$)
          
          Debug Schema$
          
          SQLite3_Execute(Schema$, dbHandler)
          Debug SQLite3_GetLastMessage()
          
          SQLite3_Execute("INSERT INTO M2D_DB." + MemoryTable$ + " Select * FROM " + MemoryTable$, dbHandler)
          Debug SQLite3_GetLastMessage()
          
        Next lCounter
        
      Else
        Debug SQLite3_GetLastMessage()
      EndIf
      
    Else
      Debug SQLite3_GetLastMessage()
    EndIf
    
    SQLite3_CloseDatabase(dbHandler)
    SQLite3_End()
    
  Else
    Debug SQLite3_GetLastMessage()
  EndIf
  
EndIf

Greetings ... Kiffi

dmoc · Post by **dmoc** » Thu Jan 05, 2006 4:17 pm

Hello Kiffi and thanks for the reply. Yes from a quick glance this is good to populate the memory db and I have seen a technique using "dump" which could be used to (re)create the disk db. But these seem awfully inefficient (or maybe I'm just lazy/ perfectionist). I'd like something like an option to open a mem db, populated immediately from disk (with no knowledge of internals (eg, don't want to be changing code if db design changes)), eg, SQLite3_CreateDatabase(dbname.s, overwriteflag.l, inmemoryflag.l). Also SQLite3_WriteToDisk(dbHandle.l, optionalfilename.s).

(<<< very LISP-like, LOL)

PS: I'm reading something about setting cache size, which also may be a way to work with disk db's efficiently, ie, large cache means db effectively in mem (I think) but maybe cache setting not accessible via PBOSL?

PPS: Support for SQLite callbacks would be just WONDERFUL!

Lazy people ask a lot I know