PureBasic Forums - English

Posted: **Sun Jun 15, 2008 9:40 am**

@pdwyer

Damn, it works, and its great.

Tested with 5000 records / 43 columns on my poor celereon Notebook.

Result for writing: 1344 ms

I didnt know that SQLite offers Transact in this way.

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)
        DatabaseUpdate(database, "Begin;")
        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
        DatabaseUpdate(database, "Commit;")
      Next

; -- Creating Indices and Views

      DatabaseUpdate(database, "Begin;")
      DatabaseQuery(source, "Select sql FROM sqlite_master WHERE type in('view','index')")
      While NextDatabaseRow(source)
        DatabaseUpdate(database, GetDatabaseString(source,0))
      Wend
      DatabaseUpdate(database, "commit;")

      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 destinationFile
    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)
      DatabaseUpdate(destinationDB, "Begin;")
      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
      DatabaseUpdate(destinationDB, "commit;")
    Next

; -- Creating Indices and Views

    DatabaseUpdate(destinationDB, "Begin;")
    DatabaseQuery(source, "Select sql FROM sqlite_master WHERE type in('view','index')")
    While NextDatabaseRow(source)
      DatabaseUpdate(destinationDB, GetDatabaseString(source,0))
    Wend
    DatabaseUpdate(destinationDB, "commit;")

    CloseDatabase(destinationDB)
    Else
      MessageRequester("Error","Can't open DB.",0) 
      ProcedureReturn
  EndIf
EndProcedure


UseSQLiteDatabase()

x = ElapsedMilliseconds()             
OpenSQLiteDatabase2Mem(0,"c:\PureBasic.sqlite","","")
Debug ElapsedMilliseconds() - x 

x = ElapsedMilliseconds()             
WriteSQLiteDatabase2Disk(0, "c:\purebasic_new2.sqlite")
Debug ElapsedMilliseconds() - x 

CloseDatabase(0)

Posted: **Sun Jun 15, 2008 10:17 am**

I have a database with 274.537 records that needs a full update every 6 months...

Using BEGIN TRANSACTION; SQLCODE; COMMIT; it takes about 2 minutes to import and crunch the data into it...

If i use just SQLCODE; it takes about 20 minutes

Posted: **Sun Jun 15, 2008 12:03 pm**

I ve added Indices and views...

regards

Amnesty

Posted: **Sun Jun 15, 2008 11:36 pm**

To tweak SQLite's performance parameters take a look at the PRAGMA commands..

http://www.sqlite.org/pragma.html

PureBasic Forums - English

In Memory SQLite DB (PB 4.20)