Am 22.11.2017 um 01:29 schrieb Jens Alfke:
When I’ve run into this before, the requirement has been to support lists with
customizable ordering, like an outliner where the user can freely drag the rows
up and down.
Yep.
And therefore such cases should be handled at the App-Level IMO...
There's a lot of ways to approach that - one that comes to mind
(since JSON is in the meantime standard in App-development),
is to store such "orderable Groups" in their own JSON-Blob-DBFields
(as simple Text - serialized into JSON-Array-format for example).
E.g. when we assume that any given "fruit-salad" is stored as
a single record (a single Blob) in a table "recipes", then
this could look like the following VB-Code...
(which interested users could paste e.g. into an Excel-VBA-Module,
after installing and referencing the vbRichClient5-COM-wrapper
for SQLite):
Private Cnn As cMemDB, SQL As String
Sub Main()
Set Cnn = New_c.MemDB 'create an SQLite InMemory-DB-Instance
Cnn.Exec "Create Table Recipes(ID Integer Primary Key, R Text)"
InsertNewRecipe MakeRecipe("apple", "pear", "kiwi") 'insert 1st record
Dim R As cCollection 'at App-Level, a Recipe is a Collection
Set R = GetRecipeByID(1) 'retr. the above inserted Record by ID
R.Add "banana", Before:=1 'add banana before Index 1 (pear)
UpdateRecipe 1, R 'write the new content of R back into the DB (ID 1)
'check, whether the DB-update was successful, retr. a Collection by ID
Debug.Print GetRecipeByID(1).SerializeToJSONString
'search-queries against the JSON-content are possible per Like...
SQL = "Select R From Recipes Where R Like '%banana%'"
Debug.Print Cnn.GetRs(SQL)(0)
'or when the SQLite-JSONExtension is available, it will allow
'to query the contents of JSON-fields more specifically...
SQL = "Select R From Recipes Where json_extract(R,'$[1]')='banana'"
Debug.Print Cnn.GetRs(SQL)(0)
End Sub
The above prints out (the same thing from all 3 Debug-Statements):
["apple","banana","pear","kiwi"]
["apple","banana","pear","kiwi"]
["apple","banana","pear","kiwi"]
The critical line in the above main-code (which makes handling
the issue per SQL obsolete) is: -> R.Add "banana", Before:=1
(most Array-, List- or Collection-Objects allow such Inserts inbetween,
no matter which programming-language).
'------ the needed Helper-Functions for the above Main-Routine ------
Function MakeRecipe(ParamArray PA()) As cCollection
'returntype of a new Recipe is a JSON-Array-(in a cCollection)
Set MakeRecipe = New_c.JSONArray
Dim P: For Each P In PA: MakeRecipe.Add P: Next 'copy-over-loop
End Function
Sub InsertNewRecipe(R As cCollection)
Cnn.ExecCmd "Insert Into Recipes(R) Values(?)", _
R.SerializeToJSONString
End Sub
Function GetRecipeByID(ByVal ID As Long) As cCollection
Dim sJSON As String 'first retrieve the JSON-String by ID
sJSON = Cnn.GetSingleVal("Select R From Recipes Where ID=" & ID)
'deserialize sJSON into a cCollection
Set GetRecipeByID = New_c.JSONDecodeToCollection(sJSON)
End Function
Sub UpdateRecipe(ByVal ID As Long, R As cCollection)
Cnn.ExecCmd "Update Recipes Set R=? Where ID=?",_
R.SerializeToJSONString, ID
End Sub
Olaf
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
