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