stephencelis / SQLite.swift

A type-safe, Swift-language layer over SQLite3.
MIT License
9.73k stars 1.57k forks source link

Support UPSERT using update in addition to replace #842

Closed moshegottlieb closed 3 years ago

moshegottlieb commented 6 years ago

Currently, the ONCONFLICT support for UPSERT is replace.
While this works in most cases - it's not very useful when using cascading foreign keys because replace means the old record is deleted before a new record is added, causing dependent records via foreign keys to be deleted as well.
For instance:

// Turn on foreign keys
try connection.execute("PRAGMA foreign_keys = ON;")
// Create table user
let id = Expression<String>("id")
let name = Expression<String>("name")
try connection.run(Table("user").create(){
    t in
        t.column(id, primaryKey: true)
        t.column(name)
    })

// create table message
let user_id_ref = Expression<String>("id")
let id = Expression<String>("id")
let user_id = Expression<String>("user_id")
let body = Expression<String>("body")
try connection.run(Table("message").create(){
    t in
        t.column(id, primaryKey: true)
        t.column(user_id)
        t.column(body)
        t.foreignKey(user_id, references:Table("user"), user_id_ref,delete:.cascade)
    })

If you add a user, and then messages, and then do something like: try connection.run(table.insert(or: .replace, .... - all the existing messages will be deleted for the inserted user.
Instead, a new onConflict support should be added for .update, as discussed here.

jberkel commented 3 years ago

Added in #882