Using sqlite temp

[kinglestat]

Well, I am too lazy to post a full example, but the code snippets should get you going

Code: Select all

  Procedure             PragmaSet( updates.i = 512, setcommit = 0 )

   Protected            temp.s

   sql = "PRAGMA temp_store = MEMORY"
   DatabaseUpdate( gLocaldb, sql )

   sql = "PRAGMA page_size = 8192"
   DatabaseUpdate( gLocaldb, sql )
 
   If setcommit
      ;sql = "PRAGMA journal_mode = TRUNCATE"  ;MEMORY is faster but unsafe if crash occurs
      sql = "PRAGMA journal_mode = MEMORY"

      DatabaseQuery( gLocaldb, sql )
      FinishDatabaseQuery( gLocaldb )
   EndIf

   sql = "PRAGMA synchronous = OFF"
   DatabaseUpdate( gLocaldb, sql )
   
   sql = "cache_size=" + Str( updates )
   DatabaseUpdate( gLocaldb, sql )
   sql = "pragma default_cache_size=" + Str( updates )
   DatabaseUpdate( gLocaldb, sql )

EndProcedure

You will need this when you open/and or create a database. This will make sure that the temp databases stay in RAM

Code: Select all

Macro                dbUpdate( runsql )
   If Not DatabaseUpdate( gLocaldb, runsql )
      Debug "[" + #PB_Compiler_Procedure + "] sql=" + runsql + Chr(13) + DatabaseError()
   EndIf
EndMacro

A handy macro I use


Now lets say you have a table which you use often, and which you only add to (no deletes or modifies), and this table is called testdata

then you do the following

Code: Select all

sql = "create temp table tmptestdata( id INTEGER PRIMARY KEY AUTOINCREMENT,text TEXT, time time NOT NULL DEFAULT CURRENT_TIMESTAMP)"
  dbUpdate( sql )

 sql = "insert into temp.tmptestdata select * from testdata"
         dbUpdate( sql )

This copies all the rows into the newly created table. The question, why do go through this hassle?. The answer is simple, now its possible to use your stanradr database together with your memory tables in an efficjent way. Lets say we have another table which has a foreign key in the copied table

select * from mytable
INNER JOIN temp.tmptestdata id = mytable.link_id;

You want to add to the meory table for speed? No problem, do so and then at timely intervals

Code: Select all

Procedure               SyncronizeTable( table.s )

   Protected            sql.s
   Protected            id.i
   
   sql = "select id from " + table + " order by id desc limit 1"

   If DatabaseQuery( gLocaldb, sql )
      If NextDatabaseRow( gLocaldb )
         id = GetDatabaseLong( gLocaldb, 0 )
      EndIf
      
      FinishDatabaseQuery( gLocaldb )
   Else
      WriteLog( "[" + #PB_Compiler_Procedure + "] sql=" + sql + LF + DatabaseError() )
   EndIf
   
   sql = "insert into " + table + " select * from temp.tmp" + table + " where temp.tmp" + table + ".id > " + Str( id )
   dbUpdate( sql )    

EndProcedure

[srod]

Ah ya lazy bas*ard, post a proper example!

Interesting indeed. Thanks.

[kinglestat]

I dont want to mention any speakers or missing cables!