SQLite Upsert (Insert Or Replace)

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.


UPSERT syntax was added to SQLite with version 3.24.0 (2018-06-04), so it is brand new.

I have seen that page, as stated in my OP. I assume I just use UPDATE instead of REPLACE…

That’s what I get for skimming your original post instead of reading it entirely. :confused:

Yeah, I noticed. We just have to be patient and wait until Xojo is going to use the new SQLite plugin.

Haha… no problem… I do that all the time.

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.

[quote=396846:@Kem Tekinay]

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.

FWIW, We typically like to wait for a sqlite release to be stable for at least 6 weeks before including it in a release. It’s just past that point so it’ll be probably be considered during the next pre-alpha cycle.

Thank you Greg.

I know what you mean, just looked in release history and several times they do a point version a week or two after a release, but not this time.

It is a bit unusual to do that manually in a trigger. I think if you just set up your FKs the normal way like

Alter Table myRelatedTable add column myFK integer references MainTable ON DELETE CASCADE

you shouldn’t run into the problem you describe at all. Haven’t tested this though…

You can always get a newer version in MBS SQL Plugin.

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.

Except for the part about manually escaping the data instead of using a prepared statement (!), I like it!


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?

It was not typically by exception ! :wink:

What does that mean?

You can always use MBS Xojo SQL Plugin for a more recent SQLite version.