I have very small experience working with databases. Could someone tell me how to do it better ? The code below will show an approximate table structure. There is a column that stores the value of the Date() command. I store a number, not text. This is done for speed. Will this be enough to make a filter in the future ? For example, I want to retrieve records only for a specific time period: a selection from the entire table, but only for specific hours or a date range. Or both. That is, I want to show only evening records from a specific date range. I would also like to be able to further filter the results by a specific type (this field will have values: 1, 2, 3, etc.). I know these are complex queries, so any help would be appreciated. I admit that it will take several steps.
Also the table has a column with the type. When the table is created, its type is specified as INTEGER, but in the rest of the code the variable is of type LONG (see structure 'table'). This is done because there is only Get(Set)Database Long(), but not INTEGER. Is this normal ?
Also I adhere to the concept of working with an in-memory database. To save the results of program work, I have a separate db-file.
Here's what I have at the moment:
Code: Select all
Structure table
sometime.q
FileType.l
event$
Compressed$
EndStructure
Procedure LoadDB(dbf$, *str.string)
Protected Result = #False
Protected dbm = OpenDatabase(#PB_Any, ":memory:", #Empty$, #Empty$, #PB_Database_SQLite)
If dbm
Debug "Open database in memory - success."
If FileSize(dbf$) > 0
Debug "A previously saved database was found."
If DatabaseUpdate(dbm, "ATTACH DATABASE '" + dbf$ + "' AS disk")
Debug "Attach database file - success."
If DatabaseUpdate(dbm, "CREATE TABLE main.notes AS SELECT * FROM disk.notes")
Debug "Data loaded from file into RAM."
If DatabaseUpdate(dbm, "DETACH DATABASE disk")
Debug "Detach database file - success."
*str\s = "read table"
Result = dbm
Else
*str\s = "Detach database file error."
EndIf
Else
*str\s = "Copy database into memory error."
EndIf
Else
*str\s = "Unable to attach database file."
EndIf
Else
If DatabaseUpdate(dbm, "CREATE TABLE notes (id INTEGER PRIMARY KEY ASC, dt INTEGER, type INTEGER, event TEXT, msg TEXT)")
Debug "A clean database has been created in RAM."
Result = dbm
Else
*str\s = "Сan't create a new database."
EndIf
EndIf
Else
*str\s = "Can't open database in mem !"
Debug DatabaseError()
EndIf
ProcedureReturn Result
EndProcedure
Procedure SaveDB(dbm, dbf$, *str.string)
Protected Result = #False
If IsDatabase(dbm)
If FileSize(dbf$) >= 0 : DeleteFile(dbf$) : EndIf
If DatabaseUpdate(dbm, "VACUUM INTO '" + dbf$ + "'")
Result = #True
Debug "Backup completed successfully !"
Else
*str\s = "Backup error !"
EndIf
EndIf
ProcedureReturn Result
EndProcedure
Procedure Read_table(dbm, Array MyArray.s(1))
Protected Result = #False
Protected tmp$, n.i
If DatabaseQuery(dbm, "SELECT COUNT(*) FROM notes")
If NextDatabaseRow(dbm)
tmp$ = GetDatabaseString(dbm, 0)
Debug "Records in DB: " + tmp$
n = Val(tmp$)
EndIf
FinishDatabaseQuery(dbm)
EndIf
If n > 0
ReDim MyArray(n-1) : n=0
If DatabaseQuery(dbm,"SELECT dt, event, msg FROM notes;"); LIMIT 1;")
While NextDatabaseRow(dbm)
sometime = GetDatabaseLong(dbm, 0)
tmp$ = FormatDate("%dd.%mm.%yyyy %hh:%ii:%ss", sometime) + #TAB$
tmp$ = tmp$ + GetDatabaseString(dbm, 1) + #TAB$
tmp$ = tmp$ + GetDatabaseString(dbm, 2)
Debug tmp$
MyArray(n) = GetDatabaseString(dbm, 2)
n+1
Wend
FinishDatabaseQuery(dbm)
Result = #True
EndIf
EndIf
ProcedureReturn Result
EndProcedure
Procedure AddRecord(dbm, *data.table)
Protected Result = #False
With *data
SetDatabaseQuad(dbm, 0, \sometime)
SetDatabaseLong(dbm, 1, \FileType)
SetDatabaseString(dbm, 2, \event$)
SetDatabaseString(dbm, 3, \Compressed$)
EndWith
If DatabaseUpdate(dbm,"INSERT INTO notes (dt, type, event, msg) VALUES (?, ?, ?, ?);")
Result = #True
EndIf
ProcedureReturn Result
EndProcedure
UseSQLiteDatabase()
Define DataBase.i, DB_File$ = "d:\data.db"
Define error.string, *err = @error
Define table.table, *data = @table
Dim msg.s(0)
Define.i i,n
DataBase = LoadDB(DB_File$, *err)
If DataBase = #False
MessageRequester("Error", error\s)
End
EndIf
If error\s = "read table"
Debug ""
Debug "Read records from a table..."
If Read_table(DataBase, msg()) = #True
n = ArraySize(msg())
Debug "ArraySize: n = " + Str(n)
For i = 0 To n
Debug msg(i)
Next
Else
MessageRequester("Error", "Can't read the table.")
End
EndIf
Debug ""
EndIf
; Adding a new record To a table
table\sometime = Date()
table\FileType = 1
table\event$ = "some event..."
table\Compressed$ = "some data..."
If AddRecord(DataBase, *data) = #True
Debug "New record was successfully added."
Else
MessageRequester("Error", "Can't execute the query: " + DatabaseError())
EndIf
If SaveDB(DataBase, DB_File$, *err) = #False
MessageRequester("Error", error\s)
End
EndIf
CloseDatabase(DataBase)Can someone help me with implementing a complex filter query ?
Thank you in advance !
Added:
How to hide this piece of code in a procedure ?
Code: Select all
DataBase = LoadDB(DB_File$, *err)
If DataBase = #False
MessageRequester("Error", error\s)
End
EndIf
If error\s = "read table"
Debug ""
Debug "Read records from a table..."
If Read_table(DataBase, msg()) = #True
n = ArraySize(msg())
Debug "ArraySize: n = " + Str(n)
For i = 0 To n
Debug msg(i)
Next
Else
MessageRequester("Error", "Can't read the table.")
End
EndIf
Debug ""
EndIfCode: Select all
DataBase = InitDB(DB_File$)
