SQLite - UPSERT *nicht* INSERT oder REPLACE

Question

http://en.wikipedia.org/wiki/Upsert

Stored Proc einfügen aktualisieren auf SQL Server

Gibt es eine clevere Möglichkeit, dies in SQLite zu tun, an die ich noch nicht gedacht habe?

Im Grunde möchte ich drei von vier Spalten aktualisieren, wenn der Datensatz vorhanden ist, Wenn er nicht vorhanden ist, möchte ich den Datensatz mit dem Standardwert (NUL) für die vierte Spalte einfügen.

Die ID ist ein Primärschlüssel, so dass es immer nur einen Datensatz für UPSERT geben wird.

(Ich versuche, den Overhead von SELECT zu vermeiden, um festzustellen, ob ich UPDATE oder INSERT durchführen muss)

Vorschläge?

---

Ich kann die Syntax auf der SQLite-Website für TABLE CREATE nicht bestätigen. Ich habe keine Demo erstellt, um sie zu testen, aber sie scheint nicht unterstützt zu werden.

Wenn es so wäre, hätte ich drei Spalten, so dass es tatsächlich so aussehen würde:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

aber die ersten beiden Blobs verursachen keinen Konflikt, nur die ID würde Ich nehme also an, dass Blob1 und Blob2 nicht ersetzt werden (wie gewünscht).

---

UPDATEs in SQLite beim Binden von Daten sind eine vollständige Transaktion, d.h. Jede zu aktualisierende gesendete Zeile erfordert: Prepare/Bind/Step/Finalize-Anweisungen im Gegensatz zu INSERT, das die Verwendung der Reset-Funktion erlaubt

Der Lebenslauf eines Anweisungsobjekts sieht in etwa so aus:

1. Erstellen Sie das Objekt mit sqlite3_prepare_v2()
2. Binden Sie Werte an Host-Parameter mit sqlite3_bind_ interfaces.
3. Führen Sie das SQL durch Aufruf von sqlite3_step() aus.
4. Setzen Sie die Anweisung mit sqlite3_reset() zurück, gehen Sie zurück zu Schritt 2 und wiederholen Sie ihn.
5. Zerstören Sie das Statement-Objekt mit sqlite3_finalize().

UPDATE ich schätze, ist langsam im Vergleich zu INSERT, aber wie verhält es sich im Vergleich zu SELECT mit dem Primärschlüssel?

Vielleicht sollte ich mit select die vierte Spalte (Blob3) lesen und dann mit REPLACE einen neuen Datensatz schreiben, der die ursprüngliche vierte Spalte mit den neuen Daten für die ersten drei Spalten vermischt?

Answer 1

Angenommen, es gibt drei Spalten in der Tabelle: ID, NAME, ROLLE

---

BAD: Dadurch werden alle Spalten mit neuen Werten für ID=1 eingefügt oder ersetzt:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

---

BAD: Dadurch werden 2 der Spalten eingefügt oder ersetzt... die Spalte NAME wird auf NULL oder den Standardwert gesetzt:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

---

GUT : SQLite On-Konflikt-Klausel verwenden UPSERT-Unterstützung in SQLite! Die UPSERT-Syntax wurde mit der Version 3.24.0 zu SQLite hinzugefügt!

UPSERT ist ein spezieller Syntaxzusatz zu INSERT, der bewirkt, dass sich INSERT wie ein UPDATE oder ein no-op verhält, wenn das INSERT eine Eindeutigkeitsbeschränkung verletzen würde. UPSERT ist kein Standard-SQL. UPSERT in SQLite folgt der von PostgreSQL festgelegten Syntax.

GUT, aber mühsam: Dadurch werden 2 der Spalten aktualisiert. Wenn ID=1 vorhanden ist, bleibt der NAME unberührt. Wenn ID=1 nicht vorhanden ist, ist der Name der Standardwert (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

Dadurch werden 2 der Spalten aktualisiert. Wenn ID=1 vorhanden ist, bleibt die ROLE unberührt. Wenn ID=1 nicht vorhanden ist, wird die Rolle auf "Benchwarmer" anstelle des Standardwerts gesetzt.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Answer 2

INSERT OR REPLACE ist NICHT Äquivalent zu "UPSERT".

Angenommen, ich habe die Tabelle Employee mit den Feldern id, name und role:

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

Bumm, Sie haben den Namen des Mitarbeiters Nummer 1 verloren. SQLite hat ihn durch einen Standardwert ersetzt.

Die erwartete Ausgabe eines UPSERT wäre, die Rolle zu ändern und den Namen beizubehalten.

Answer 3

Eric B's Antwort ist in Ordnung, wenn Sie nur eine oder vielleicht zwei Spalten der bestehenden Zeile beibehalten wollen. Wenn Sie viele Spalten beibehalten wollen, wird es schnell zu umständlich.

Hier ist ein Ansatz, der sich gut für jede Anzahl von Spalten auf beiden Seiten eignet. Zur Veranschaulichung gehe ich von dem folgenden Schema aus:

 CREATE TABLE page (
     id      INTEGER PRIMARY KEY,
     name    TEXT UNIQUE,
     title   TEXT,
     content TEXT,
     author  INTEGER NOT NULL REFERENCES user (id),
     ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

Beachten Sie insbesondere, dass name ist der natürliche Schlüssel der Zeile - id wird nur für Fremdschlüssel verwendet, so dass SQLite beim Einfügen einer neuen Zeile den ID-Wert selbst auswählen soll. Wenn jedoch eine bestehende Zeile auf der Grundlage ihrer name Ich möchte, dass sie den alten ID-Wert beibehält (natürlich!).

Ich erreiche eine echte UPSERT mit dem folgenden Konstrukt:

 WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) )
 INSERT OR REPLACE INTO page (id, name, title, content, author)
 SELECT old.id, new.name, new.title, old.content, new.author
 FROM new LEFT JOIN page AS old ON new.name = old.name;

Die genaue Form dieser Abfrage kann ein wenig variieren. Der Schlüssel ist die Verwendung von INSERT SELECT mit einer linken äußeren Verknüpfung, um eine vorhandene Zeile mit den neuen Werten zu verbinden.

Hier, wenn eine Zeile vorher nicht vorhanden war, old.id wird sein NULL und SQLite wird dann automatisch eine ID zuweisen, aber nur, wenn es bereits eine solche Zeile gibt, old.id hat einen aktuellen Wert und wird wiederverwendet. Das ist genau das, was ich wollte.

Dies ist in der Tat sehr flexibel. Beachten Sie, wie die ts Säule auf allen Seiten komplett fehlt - denn sie hat eine DEFAULT Wert ist, wird SQLite in jedem Fall das Richtige tun, so dass ich mich nicht selbst darum kümmern muss.

Sie können auch eine Spalte auf beiden Seiten der new y old Seiten und verwenden Sie dann z. B. COALESCE(new.content, old.content) in der äußeren SELECT um zu sagen: "Fügen Sie den neuen Inhalt ein, falls vorhanden, andernfalls behalten Sie den alten Inhalt" - z. B. wenn Sie eine feste Abfrage verwenden und die neuen Werte mit Platzhaltern verbinden.

Answer 4

Diese Antwort wurde aktualisiert, so dass die unten stehenden Kommentare nicht mehr gelten.

2018-05-18 STOP PRESS.

UPSERT-Unterstützung in SQLite! Die UPSERT-Syntax wurde mit der Version 3.24.0 (pending) zu SQLite hinzugefügt!

UPSERT ist ein spezieller Syntaxzusatz zu INSERT, der bewirkt, dass sich das INSERT wie ein UPDATE oder ein no-op verhält, wenn das INSERT eine Einzigartigkeitsbeschränkung verletzen würde. UPSERT ist kein Standard-SQL. UPSERT in SQLite folgt der von PostgreSQL festgelegten Syntax.

alternativ:

Eine andere, völlig andere Art, dies zu tun, ist: In meiner Anwendung setze ich die Zeilen-ID im Speicher auf long.MaxValue, wenn ich die Zeile im Speicher erstelle. (MaxValue wird niemals als ID verwendet, da sie nicht lange genug leben wird.... Wenn rowID dann nicht dieser Wert ist, muss die Zeile bereits in der Datenbank sein und braucht ein UPDATE, wenn sie MaxValue ist, muss sie eingefügt werden. Dies ist nur sinnvoll, wenn Sie die rowIDs in Ihrer Anwendung verfolgen können.

Answer 5

Wenn Sie generell Aktualisierungen vornehmen, würde ich

1. Beginnen Sie eine Transaktion
2. Aktualisieren Sie
3. Prüfen Sie die Zeilenzahl
4. Wenn er 0 ist, fügen Sie ein
5. Commit

Wenn Sie generell Einsätze machen, würde ich

1. Beginnen Sie eine Transaktion
2. Versuchen Sie einen Einsatz
3. Prüfung auf Primärschlüsselverletzungsfehler
4. Wenn ein Fehler auftritt, wird die Aktualisierung durchgeführt.
5. Commit

Auf diese Weise vermeiden Sie das Select und sind transaktional auf Sqlite sicher.