I have made my own SQLite framework a long time ago. Now I want to make a similar one for iOS. It just makes it just so much easier without having to write repetitive code all the time.
Now, for saving records I used to use three methods…
1 - Save (public)
2 - Insert (private)
3 - Update (private)
I just simply call the save method. It checks if the PK exists. If it doesn’t, it will redirect the call to the private Insert method. If it does exist, it calls the Update method.
I have read somewhere about the Upsert Clause in SQLite.
Looks better. It will attempt to insert a record. If there is a PK collision, it will replace the record with new data.
The problem is, is that I use a bunch of triggers to create relationships. If a “parent record” is deleted, it will delete all associated records in other tables.
When the SQLite performs the Replace, it actually deletes the record and inserts a new one, with the new data.
The problem is that I will lose all the data in the associated tables since Replace actually deletes a record.
Are there any folks out there having experience with this kind of stuff? If so, do you have any suggestions? Or should I just stick with my old code?
The newer versions of SQLite support an ON CONFLICT clause to INSERT that will do what you want. If there is a conflict, e.g., a field or fields that are supposed to unique, the statement will execute the instructions in ON CONFLICT instead.
The current SQLite plugin does not yet support this version, at least on the desktop, but once it does, this will do what you want.
BTW, we handle the problem by assigning a large negative constant to the object’s ID. When saving, it checks to see if the ID = that constant and, if so, does an insert and fills in the ID. Otherwise, it does an update using that ID.
BTW, we handle the problem by assigning a large negative constant to the object’s ID. [/quote]
Actually… what if I would change my triggers.
What if, in case the record is being replaced, I first prefix the PK with an underscore character. My PKs are all GUIDs, so, an underscore is ok.
At the end I will remove the underscore. No harm will be done to the records that have my PK as FK… right?
FWIW, We typically like to wait for a sqlite release to be stable for at least 6 weeks before including it in a release. Its just past that point so itll be probably be considered during the next pre-alpha cycle.
I use the following code to do update or insert (with SQLite) in my LunaAPIDocGen program:
Dim db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.ApplicationData.Child(APIDocDBName)
if db.Connect Then
Dim strSQL As String
Dim strAPIDocsBranch As String=tfJsonDoc.Text
//https://stackoverflow.com/questions/15277373/sqlite-upsert-update-or-insert
strSQL="UPDATE apidocs SET apidocsbranchjson='" + EscapeSQLData(taJsonDoc.Text) + _
"' WHERE apidocsbranch='" + EscapeSQLData(strAPIDocsBranch) + "';"
strSQL=strSQL + "INSERT INTO apidocs (apidocsbranch, apidocsbranchjson) SELECT '" + _
EscapeSQLData(strAPIDocsBranch) + "', '" + EscapeSQLData(taJsonDoc.Text) + "' " + _
"WHERE (SELECT changes() = 0);"
//How It Works
//The 'magic' here is you use the Where (Select Changes() = 0) clause
//to determine if there are any rows for the insert,
//and since that is based on your own Where clause,
//it can be for anything you define, not just key violations.
//In the above example, if there are no changes from the update
///(i.e. the record doesn't exist) then Changes() = 0
//so the Where clause in the Insert statement returns true
//and a new row is inserted with the specified data.
//If the Update did update an existing row, then Changes() = 1,
//so the 'Where' clause in the Insert will now be false
//and thus no insert will take place.
//No brute-force needed.
db.SQLExecute(strSQL)
if db.Error Then
//handle error here
MsgBox("Could not save the branch to apidocs.")
End If
else
System.DebugLog "The database could not be opened. Error: " + db.ErrorMessage
end if
This works with the current plugin btw. No need to wait for changes.
Looks like SQLite 3.24.0 didn’t make it into 2018r2. No complaints. Just letting people know we don’t have UPSERT quite yet.
Once that finally happens, would I have to wait until CubeSQLServer updates its SQLite to 3.24.0 to be able to use UPSERT, if I run my SQLite databases under CubeSQLServer?
