In Memory SQLite DB (PB 4.20)

[pdwyer]

Actually I've never used PB's DB engine but now that 4.20 full version is out with SQLite I thought it's time to see if I can retire my include files and use native PB for all this.

Test 1 was Japanese in Unicode mode and PB SQLite was fine "out of the box" (but not much interesting to post in the tips area there). Next is InMemory Databases.

This works fine too

Next I guess will be blobs and X' sql syntax I hope! Thanks PB Team for the new tools

Code: Select all

    UseSQLiteDatabase()

    If OpenDatabase(0, ":memory:", "", "")
        Debug "Connected"
        
        DatabaseUpdate(0, "CREATE TABLE MyTable (test VARCHAR(255));")       
        Debug DatabaseError()
        
        For i = 1 To 15
            DatabaseUpdate(0, "insert into MyTable (test) values('Hello Row " + Str(i)+ "')")
        Next
        
        DatabaseQuery(0, "SELECT * FROM MyTable")
        While NextDatabaseRow(0)
            Debug GetDatabaseString(0, 0)
        Wend
    
    Else
        Debug DatabaseError()    
    EndIf

[srod]

Now that is good to know - that the in memory database carries over to the PB library.

Thanks.

[akj]

Please could the ":memory:" feature be documented within the help page for the UseSQLiteDatabase() command?
It would be helpful for newcomers to SQLite.

[srod]

It is an SQLite feature really rather than a PB one.

[pdwyer]

It's hard to find docs about it on the sqlite.org site too which is unlike them, I found it a while back only because they made a change and it popped up in their announcements about it. I don't see it in their API docs section, just in their wiki discussions and CVS blogs etc

[srod]

I originally found it in their api docs somewhere - but I couldn't tell you where right now!

**EDIT : actually, I tell a lie; the first time I encountered it was in another thread of yours! However I have since seen it in the SQLite docs.

[netmaestro]

Good tip, thanks!

[Karbon]

I wish the SQLite command line utility's ".dump" command worked through the API. That way we could save SQLite files to text and import them directly back into a memory database..

[superadnim]

So it's not possible to save anything that we made in memory?

On a side-note: I would love to have compression support, but its a feature the author of sqlite asks money for

[pdwyer]

I think I saw this get requested within the sqlite world but it doesn't look to be happening.

I think if you want data out of an in memory DB you need to select it out (which is very fast from memory) and write it to a disk db.

I haven't tried to open multiple DBs at the same time with SQLite (only multiple tables) so I'm not sure if you can use "Select Into" with DB.TABLE or not. That would be a nice easy way I guess.

I think (guessing) that since in memory dbs are not as safe (in that data is lost if the process dies) it's more of a feature for a different type of application (a way to do complex searches on complex memory structures easily) rather than a performance tweak for a DB that needs to be kept on disk.

For example, a little commandline app that loads a CSV file and a TSV to memory, does a select with a join and then outputs a quick query result and terminates. There would be a lot of code to saved using SQL syntax and little need for disk I/O overhead of normal DBs.

I admit, it would be nice to dump an in-memory db though

[Amnesty]

Its an amazing trick, this :memory: function, thank you for posting.

Its so fast, you can create your own OLAP System.

Its a pity that its so slowly writing, because I dont know how to use the ".import" SQLite command in PureBasic. Maybe somebody has an idea, because its the fastest way to write DB from memory back into file. As far as I know.

regards

Amnesty

Code: Select all

Structure table
  sql.s
  name.s
EndStructure

Procedure OpenSQLiteDatabase2Mem(database.l, file.s, user.s, pass.s)
  NewList createStatement.table()
  
  source = OpenDatabase(#PB_Any, File, User, Pass, #PB_Database_SQLite)
  If source
    DatabaseQuery(source, "Select sql, name FROM sqlite_master WHERE type='table'")
    While NextDatabaseRow(source)
      AddElement(createStatement())
      createStatement()\sql  = GetDatabaseString(source, 0)
      createStatement()\name = GetDatabaseString(source, 1)
    Wend
    If OpenDatabase(database, ":memory:", "", "", #PB_Database_SQLite)
      ForEach createStatement()
        DatabaseUpdate(database, createStatement()\sql)
        DatabaseQuery(source, "select * from " + createStatement()\name)  
        columns = DatabaseColumns(source)
        While NextDatabaseRow(source)
          values.s = ""
          For columnNr = 0 To columns - 1
            If DatabaseColumnType(source, columnNr) = #PB_Database_String Or DatabaseColumnType(source, columnNr) = 0 
              values+ "'" + GetDatabaseString(source,columnNr) + "'"
              Else
              values+ GetDatabaseString(source,columnNr)
            EndIf
            If columnNr < columns -1
              values+ ","
            EndIf
          Next
          DatabaseUpdate(database, "insert into " + createStatement()\name + " values(" + values + ")")
        Wend
      Next
      Else
        MessageRequester("Error","Can't open DB.",0) 
        ProcedureReturn
    EndIf
    CloseDatabase(source)
    Else
      MessageRequester("Error","Can't open DB.",0) 
      ProcedureReturn
  EndIf
EndProcedure

Procedure WriteSQLiteDatabase2Disk(source.l, File.s)
  NewList createStatement.table()

  destinationFile = CreateFile(#PB_Any, File.s)
  If file
    CloseFile(destinationFile)
    Else
      MessageRequester("Error","Can't write DB.",0) 
      ProcedureReturn
  EndIf
  
  destinationDB = OpenDatabase(#PB_Any, File, "", "", #PB_Database_SQLite)
  If destinationDB
    DatabaseQuery(source, "Select sql, name FROM sqlite_master WHERE type='table'")
    While NextDatabaseRow(source)
      AddElement(createStatement())
      createStatement()\sql  = GetDatabaseString(source, 0)
      createStatement()\name = GetDatabaseString(source, 1)
    Wend
    ForEach createStatement()
      DatabaseUpdate(destinationDB, createStatement()\sql)
      DatabaseQuery(source, "select * from " + createStatement()\name)  
      columns = DatabaseColumns(source)
      While NextDatabaseRow(source)
        values.s = ""
        For columnNr = 0 To columns - 1
          If DatabaseColumnType(source, columnNr) = #PB_Database_String Or DatabaseColumnType(source, columnNr) = 0 
            values+ "'" + GetDatabaseString(source,columnNr) + "'"
            Else
            values+ GetDatabaseString(source,columnNr)
          EndIf
          If columnNr < columns -1
            values+ ","
          EndIf
        Next
        DatabaseUpdate(destinationDB, "insert into " + createStatement()\name + " values(" + values + ")")
      Wend
    Next
    CloseDatabase(destinationDB)
    Else
      MessageRequester("Error","Can't open DB.",0) 
      ProcedureReturn
  EndIf
EndProcedure


UseSQLiteDatabase()

OpenSQLiteDatabase2Mem(0,"c:\PureBasic.sqlite","","")

WriteSQLiteDatabase2Disk(0, "c:\purebasic_new.sqlite")

[srod]

Nice example - thanks.

[Kiffi]

Maybe somebody has an idea

perhaps this is faster (by using Attach Database). Can you test it?

Code: Select all

EnableExplicit

Procedure.s GetTableNames(hDB.l)             ; Returns the tablenames of the specified database
  
  Protected TableName.s
  
  DatabaseQuery(hDB, "Select tbl_name From sqlite_master Where type='table' Order By tbl_name")
  
  While NextDatabaseRow(hDB)
    TableName + GetDatabaseString(hDB, 0) + ";"
  Wend
  
  If Right(TableName,1)=";"
    TableName = Left(TableName,Len(TableName)-1)
  EndIf
  
  ProcedureReturn TableName
  
EndProcedure

Procedure.s GetSchema(hDB.l, TableName.s)   ; Returns a schema of a table
  
  Protected SQL.s
  Protected Schema.s
  
  SQL + "SELECT sql FROM "
  SQL + "(SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) "
  SQL + "WHERE tbl_name = '" + TableName + "' AND type!='meta' "
  SQL + "ORDER BY type DESC, name "
  
  DatabaseQuery(hDB, SQL)
  
  While NextDatabaseRow(hDB)
    Schema + GetDatabaseString(hDB, 0)
  Wend
  
  ProcedureReturn Schema
  
EndProcedure

; MemoryDb to FileDb

Define hDB.l
Define Counter.l
Define FF.l
Define MemoryTablenames.s
Define MemoryTablename.s
Define Schema.s

UseSQLiteDatabase()

hDB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)
  
If hDB
  
  ; create a sample-table
  DatabaseUpdate(hDB, "Create Table TestTable (TestField)")
  
  ; insert some sample-data
  For Counter = 0 To 9
    DatabaseUpdate(hDB, "Insert Into TestTable (TestField) Values ('TestValue" + Str(Counter) + "')")
  Next
  
  ; create an empty sqlite-database
  FF = CreateFile(#PB_Any, "D:\FromMemoryToDisk.db")
  
  If FF = 0
    MessageRequester("", "Couldn't create D:\FromMemoryToDisk.db")
    End
  EndIf
  
  CloseFile(FF)
  
  DatabaseUpdate(hDB, "Attach Database 'D:\FromMemoryToDisk.db' As M2D_DB")
  
  ; Get all tablenames
  MemoryTablenames = GetTableNames(hDB)
  
  For Counter = 0 To CountString(MemoryTablenames, ";")
    
    ; get each Memorytablename from MemoryTablenames-'Collection'
    MemoryTablename = StringField(MemoryTablenames, Counter, ";")
    
    ; get schema from the current table
    Schema = GetSchema(hDB, MemoryTablename)
    
    ; create file-table
    Schema = ReplaceString(Schema, "CREATE TABLE " + MemoryTablename, "CREATE TABLE M2D_DB." + MemoryTablename)
    DatabaseUpdate(hDB, Schema)
    
    ; Insert content from memory-table to file-table
    DatabaseUpdate(hDB, "INSERT INTO M2D_DB." + MemoryTablename + " Select * FROM " + MemoryTablename)
    
  Next Counter
  
  CloseDatabase(hDB)
  
EndIf

Greetings ... Kiffi

// Edit: You can also try to encapsulate the Insert-Statements
into a Transaction.

[Edwin Knoppert]

So it's not possible to save anything that we made in memory?

On a side-note: I would love to have compression support, but its a feature the author of sqlite asks money for

Sure by using drop table and then save to the desired tablename using INSERT INTO it's a piece of cake..

Sqlite is versatile, espec due it's library size being so small.

PS,
Blobs through x'' notation will do fine as well.

[pdwyer]

Its a pity that its so slowly writing

SQLite is always slower to write (although faster with memory dbs of course) as it flushes caches etc after a transaction. This makes inserts in big loops very slow on disk DBs.

If, however you use a transaction to insert all your speed comes back .
http://www.sqlite.org/cvstrac/wiki?p=Pe ... iderations

In my tests, if I put about 500 inserts per transaction I get best performance but this may very aon the size of the insert statement text. If I go too high with like 5000 inserts then the string concaternation starts to slow it down again. Perhaps if my string building was better optimised that might not happen.