locked
LDDatabase SQLite In Memory Database RRS feed

  • Question

  • I was wondering if LDDatabase supports in memory databases and if so how would one set them up? 

    Friday, February 19, 2016 12:29 AM

Answers

  • A database is a good way to hold this sort of data and sorting, querying it will generally be be more efficient and easier than writing it separately, espeially as complexity increases.

    Only if the database requests are very simple and it is used in a fast moving action game where a simple calculation would do or a value easily got from a simple array will it probably be best to not use a database.

    It looks like the in memory doesn't work (I'm not sure why), so just use a file - delete it at the end if you want - there will be no significant downsides in terms of performance for example.

    • Marked as answer by a65001 Saturday, February 20, 2016 9:34 PM
    Saturday, February 20, 2016 8:45 AM

All replies

  • What have you tried?

    https://www.sqlite.org/inmemorydb.html

    LDDatabase.ConnectSQLite(":memory:")

    Why do you want to do this - perhaps there is another solution if the above doesn't work.

    Friday, February 19, 2016 10:17 AM
  • 'Example SQL taken from http://zetcode.com/db/sqlite/select/
    
    'Set a database file - it is created if it doesn't exist yet
    'datFile = Program.Directory+"\database.db"
    database = LDDataBase.ConnectSQLite(":memory:")
    
    Rest of standard code from LDDatabase Sample Code
      

    This results in the following errors for me:

    LDDataBase.Query : SQL logic error or missing database
    no such table: Cars
    LDDataBase.Query : Operation is not valid due to the current state of the object. (Times 14+) 
    

    "::memory:" , "./:memory:", "file::memory:?cache=sharedresults",

    in the following error:

    LDDataBase.Command : The path is not of a legal form.

    I am currently trying to make a game using tables as a way to hold and sort data. This is probably not the most efficient way of doing it.

    Here is the sample code below:

    TurtleFx = 2 
    TurtleFY = 2 
    BoxesToGo = 40
    
    DistanceDBCreateCmd = "Create Table Distance (ID Integer,X Integer,Y Integer,Distance Integer,Batch Integer);"
    DistanceDB = Program.Directory +"\Distance.db"
    DistanceDB =  LDDataBase.ConnectSQLite(DistanceDB)
    LDDataBase.Command(DistanceDB,DistanceDBCreateCmd) 
    XArray =  LDArray.Create(40)
    YArray = LDArray.Create(40)
    For i = 1 to 40 
      LDArray.SetValue(XArray,I,Math.GetRandomNumber(40) ) 
      LDArray.SetValue(YArray,I,Math.GetRandomNumber(40) ) 
    EndFor 
    
    Closest()
    Sub Closest
      TextWindow.Clear()
      Closest_StartTime = Clock.ElapsedMilliseconds
      BatchID = BatchID + 1 
      ' LDDataBase.Command(DistanceDB,DistanceDBCreateCmd) 
      DBCMD= "" 
      Start_Time[1] = Clock.ElapsedMilliseconds
      For ShortPath = 1 To LDArray.Count(XArray) 
        ShortPath_Y = LDArray.GetValue(XArray,ShortPath)
        ShortPath_X = LDArray.GetValue(YArray,ShortPath)
        If ShortPath_X <> 0 And ShortPath_Y <> 0 Then 
          Distance = Math.Power( ShortPath_X - TurtleFx,2) + Math.Power(ShortPath_Y-TurtleFY,2)
          Distance = Math.Ceiling( Math.SquareRoot( Distance ) )
          DBCMD = Text.Append( DBCMD , "INSERT OR REPLACE INTO Distance (ID,X,Y,Distance,Batch) VALUES('"+ShortPath+"','"+ShortPath_X+"','"+ShortPath_Y+"','"+Distance+"','"+BatchID+"');")
        EndIf 
      EndFor
      End_Time[1] = Clock.ElapsedMilliseconds
      TextWindow.Clear()
      TextWindow.Title = BatchID
      
      Start_Time[2] = Clock.ElapsedMilliseconds
      return =  LDDataBase.Command(DistanceDB,DBCMD)
      End_Time[2] = Clock.ElapsedMilliseconds
      
      Start_Time[3] = Clock.ElapsedMilliseconds
      Closest_Return =  LDDataBase.Query(DistanceDB,"SELECT X,Y,Distance FROM Distance WHERE Batch =" + BatchID+" ORDER BY Distance,X,Y ASC;","","True") 
      End_Time[3] = Clock.ElapsedMilliseconds
      
      Start_Time[4] = Clock.ElapsedMilliseconds
      For ShortPath = 1 To BoxesToGo 
        TextWindow.WriteLine(Closest_Return[ShortPath] ) 
      EndFor
      End_Time[4] = Clock.ElapsedMilliseconds
      
      Closest_EndTime = Clock.ElapsedMilliseconds
      
      OverallTime[0] = ((Closest_EndTime - Closest_StartTime)/1000)
      OverallTime[1] = ((End_Time[1]- Start_Time[1])/1000)
      OverallTime[2] = ((End_Time[2]- Start_Time[2])/1000)
      OverallTime[3] = ((End_Time[3]- Start_Time[3])/1000)
      OverallTime[4] = ((End_Time[4]- Start_Time[4])/1000)
      
      TextWindow.WriteLine("")
      TextWindow.Write("Overall Time: ")
      TextWindow.CursorLeft = 30
      TextWindow.WriteLine(OverallTime[0] +"(s)" ) 
      
      TextWindow.Write("Time Generation: ") 
      TextWindow.CursorLeft = 30
      TextWindow.Write( OverallTime[1]  +"(s)" ) 
      TextWindow.CursorLeft  = 50 
      TextWindow.WriteLine(Math.Round(  (OverallTime[1] / OverallTime[0])*100 ))
      
      TextWindow.Write("Time Writing: ")
      TextWindow.CursorLeft = 30 
      TextWindow.Write( OverallTime[2] +"(s)" )
      TextWindow.CursorLeft  = 50 
      TextWindow.WriteLine( Math.Round( (OverallTime[2] / OverallTime[0])*100 ))
      
      TextWindow.Write("Time Query: ")
      TextWindow.CursorLeft = 30 
      TextWindow.Write(OverallTime[3] +"(s)" ) 
      TextWindow.CursorLeft  = 50 
      TextWindow.WriteLine( Math.Round(  (OverallTime[3] / OverallTime[0])*100))
      
      TextWindow.Write("Time TextwindowWriteTime: ")
      TextWindow.CursorLeft = 30
      TextWindow.Write(OverallTime[4] +"(s)" )
      TextWindow.CursorLeft  = 50 
      TextWindow.WriteLine( Math.Round( (OverallTime[4] / OverallTime[0])*100))
      TextWindow.WriteLine("")
    EndSub



    • Edited by a65001 Saturday, February 20, 2016 8:36 PM Updated Source Code
    Saturday, February 20, 2016 12:31 AM
  • A database is a good way to hold this sort of data and sorting, querying it will generally be be more efficient and easier than writing it separately, espeially as complexity increases.

    Only if the database requests are very simple and it is used in a fast moving action game where a simple calculation would do or a value easily got from a simple array will it probably be best to not use a database.

    It looks like the in memory doesn't work (I'm not sure why), so just use a file - delete it at the end if you want - there will be no significant downsides in terms of performance for example.

    • Marked as answer by a65001 Saturday, February 20, 2016 9:34 PM
    Saturday, February 20, 2016 8:45 AM
  • I had a little trouble understanding what you were trying to convey. This is my understanding:

    Databases in this particular context is a good way to utilize them unless:

    1.You are developing a fast action game 

    2.A simple calculation would work better than a database

    3.You could use a simple array.

    Is my understanding of your statement correct?

    Saturday, February 20, 2016 8:07 PM
  • Absolutely correct - yes.

    The onlty other reason to use databases is to learn them - which is great too.


    • Edited by litdev Saturday, February 20, 2016 8:39 PM
    Saturday, February 20, 2016 8:39 PM
  • Thank you for your help. I will try to implement the data in some other way though you implementing memory databases would help a ton with improving the speed of some of my sorts :) . 
    Saturday, February 20, 2016 9:35 PM
  • Some sorting in LDSort and in LDList and LDArray, I will try to see why in memory db fails, but you could just use a temp file for the database (at minimal performance hit - databases are made to be efficient so they actually store stuff in memory if they can).

    • Edited by litdev Saturday, February 20, 2016 9:44 PM
    Saturday, February 20, 2016 9:40 PM