Sqlite: Update-Problem mit Positions-Verschiebung

[cppbert3 (Gast)]

Ich habe eine Sqlite Datenbank vorliegen, deren Schema ich nicht 
verändern darf und es ist mir wegen Foreign Keys nicht erlaubt Sätze 
einfach zu löschen und neu einzufügen, ist ein Legacy Projekt an dem man 
nicht mehr so viel ändern soll - oder falls euch das hilft (oder euch 
das heißer macht) - eine Hausaufgabe mit komischen Beschränkungen :)

Es gibt einen PK der u.a. die Position in einer Liste enthält.
Jetzt will der Kunden nach Jahren auch mal Lösch, Einfüge und Verschiebe 
Operationen ermöglichen - bisher war das alles ganz starr

Da ich nicht einfach alles löschen und neu erstellen darf habe ich mir
gedacht das ich einfach kurz "berechne" welche Pos in der Tabelle bei 
Einfügen,Löschen oder Verschieben (von x Items) verschoben werden müssen 
und führe dann damit ein
Update Statement aus das eben die Bereiche entsprechend verschiebt

Problem ist nur das ich scheinbar die PK contraints verletzte wenn
ich einfach 0,1,2 auf 0+1,1+1,2+2 update weil dann teilweise Sätze in 
dem Update die gleiche Position aufweisen - ist es wirklich so das die 
Contraints wärend des Updates und nicht erst am Ende des Updates geprüft 
werden?

meine Lösung ist jetzt das ich die Bereiche die verschoben werden müssen 
erst mal komplett in sichere Bereiche verschiebe, die komplett außerhalb 
der Liste liegen und dann erst an die eigentliche Position

Ist das (mal unabhängig davon wie dämlich das alles ist) eine gute 
Lösung oder geht das irgendwie noch einfacher/schneller/eleganter?

online testen unter: https://sqliteonline.com/
Zum Spielen muss man den Code da noch rein kopieren weil man nur mit 
Account sharen kann :(

DROP TABLE IF EXISTS items;
CREATE TABLE items (
  pos INTEGER PRIMARY KEY,
    name TEXT 
);
INSERT INTO items (pos,name) VALUES
(0,"Erster"),
(1,"Zweiter"),
(2,"Dritter"),
(3,"Vierter"),
(4,"Fuenfter");

-- verschieben von (2,3) vor 1
-- 0 bleibt so
-- 1 muss um +2 nach unten
-- (2,3) muessen um -1 nach oben
-- 4 bleibt so

-- die noetigen verschiebungen fuer einfuegen,loeschen,schieben berechne ich ausserhalb mit einem 5-Zeiler
-- die 100 und 200 sind nur "sichere" offsets fuer die position damit ich meine constraints nicht verletze
-- normalerweise ergeben die sich aus einem vielfachen der Item-Anzahl
-- sicher-offset fuer update1 = 1 * Anzahl-Items -> hier einfach 100
-- sicher-offset fuer update2 = 2 * Anzahl-Items -> hier einfach 200

-- fuer ein verschieben brauche ich jeweils 2 updates, fuer loeschen oder einfuegen nur 1 mal 2 updates

-- bloecke in "sichere" Bereiche schieben
UPDATE items set pos = 100+pos where (pos >= 1 and pos < 2);
UPDATE items set pos = 200+pos where (pos >= 2 and pos < 4);
--SELECT * from items order by pos;

-- ...und dann an die richtige Position
UPDATE items set pos = -100+pos+2 where (pos >= 100+1 and pos < 100+2);
UPDATE items set pos = -200+pos-1 where (pos >= 200+2 and pos < 200+4);

SELECT * from items order by pos;

[cppbert3 (Gast)]

und die Update-Blöcke setzen im Normalfall ein paar hundert/tausend 
Datensätze - also nicht super viele aber auch nicht 20

[Jim M. (turboj)]

Der Kunde will ein neues Feature! Und das geht nur vernünftig wenn man 
das DB Schema ändert. Punkt.

Also sieh zu das Du das OK für eine Schema Änderung bekommst. Mit dem 
neuen Feature kann man das prima begründen.

Alles Andere ist sinnlose Frickelei, die einem eher früher als später 
auf die Füße fällt.

[Jan H. (j_hansen)]

cppbert3 schrieb:
> Ist das (mal unabhängig davon wie dämlich das alles ist) eine gute
> Lösung oder geht das irgendwie noch einfacher/schneller/eleganter?

Nein, das ist natürlich keine gute Lösung. Das Grundkonzept ist halt 
Schrott. PK für die Reihenfolge ist ja grundsätzlich schon ein Hack, 
aber jetzt änderst du ja den Key eines Datensatzes, was ist wenn das 
drauf wer referenziert was bei einer relationalen DB ja durchaus 
vorkommen soll?

Wenn du deine "Lösung" so behalten willst und nicht in Schema eingreifen 
möchtest, dann bleib bei deiner Methode mit dem Verschieben in sichere 
Bereiche. Aber vermeide jeden Hinweis auf die Urheberschaft wenn möglich 
;)

[cppbert3 (Gast)]

Jim M. schrieb:
> Alles Andere ist sinnlose Frickelei, die einem eher früher als später
> auf die Füße fällt.

Jan H. schrieb:
> Nein, das ist natürlich keine gute Lösung. Das Grundkonzept ist halt
> Schrott. PK für die Reihenfolge ist ja grundsätzlich schon ein Hack,

Der PK hat noch eine Id vom Parent drin und es wird nie auf die Kinder 
(Items) direkt referenziert, nur auf den Parent, Items werden gelöscht 
wenn Parent gelöscht wird

bei welchem Schema würde es denn keine Constrain Probleme geben wenn die 
Pos in den Items UNIQUE sein muss? oder würdet ihr dann einen irgendwie 
gearteten Ordering Table komplett fuer diesen Parent leeren und frisch 
mit den Item Positionen füllen? wie gesagt, sind ein paar tausend Items

[Experte (Gast)]

cppbert3 schrieb:
> ist es wirklich so das die
> Contraints wärend des Updates und nicht erst am Ende des Updates geprüft
> werden?

Ja, wenn Du nichts anders angibst, ist das genau so. Man kann 
Constraints auch DEFERRABLE machen.

Allerdings kann Sqlite das halt nur für Foreign-Keys. Ist halt keine 
richtige Datenbank, sondern nur Spielzeug...

In PostgreSql geht das problemlos:

CREATE TABLE items (
  pos INTEGER PRIMARY KEY DEFERRABLE INITIALLY DEFERRED,
  name TEXT 
);

INSERT INTO items (pos,name) VALUES
(0,'Erster'),
(1,'Zweiter'),
(2,'Dritter'),
(3,'Vierter'),
(4,'Fuenfter');

BEGIN;
UPDATE items SET pos = 2 WHERE "name" = 'Zweiter';
UPDATE items SET pos = 1 WHERE "name" = 'Dritter';
COMMIT;

Nur, ob man sowas machen will, ist höchst fraglich! Ein Constraint 
"PRIMARY KEY" hat ja seinen Sinn!

Und in den Primary Key noch irgendwelche anderen Dinge zu kodieren, 
naja, das ist halt Murks hoch siebzehn.

Irgendwie musst Du die Zeilen während der Umsortierung ja eindeutig 
benennen. Und genau dazu ist der Primary Key da.


> bei welchem Schema würde es denn keine Constrain Probleme geben wenn die
> Pos in den Items UNIQUE sein muss?

Naja, man hat eine separate Spalte mit der Ordnung 
(Sortierreihenfolge)...


> würdet ihr dann einen irgendwie
> gearteten Ordering Table komplett fuer diesen Parent leeren und frisch
> mit den Item Positionen füllen?

Nein. Bei Sqlite bist Du eingeschränkt. Also lässt Du in der 
Sortier-Spalte NULL zu oder hast halt gar kein Constraint auf die 
Sortierspalte.

Im ersten Fall (mit NULL), rechnest Du die zu ändernden Zeilen aus, 
setzt ihre "order" auf "NULL" und danach auf die korrekt Sortierung. 
Wenn Du keine Constraints hast, dann kannst Du das auf "NULL" setzen 
sparen. Hast Du eine richtige Datenbank (PostgreSQL), dann eben 
DEFERRED.

[Experte (Gast)]

Achso, im einem Update kannst Du das so machen:

UPDATE items
  SET pos = map.new
  FROM (VALUES (1, 2) , (2, 1)) AS "map" ("old", "new")
  WHERE pos = map.old;

Aber auch hier, Constraints...

[Appelkorn (Gast)]

Musst du halt abwägen was schneller ist, das mit dem verschieben oder 
man schreibt in eine neue Tabelle. Bei Massenupdates ist Letzteres wohl 
schneller, ansonsten deine Verschiebelösung.
Da hilft nur viel Testen und Druck ausüben ob du nicht doch das Schema 
ändern darfst. Streng genommen reicht wohl eine Ergänzung so dass die 
alte Version immer noch funktioniert, die kommt gar nicht mit den 
Ergänzungen in Kontakt, ist evt. als Argumentationshilfe nützlich.

[cppbert3 (Gast)]

Experte schrieb:
> Ja, wenn Du nichts anders angibst, ist das genau so. Man kann
> Constraints auch DEFERRABLE machen.
> Allerdings kann Sqlite das halt nur für Foreign-Keys. Ist halt keine
> richtige Datenbank, sondern nur Spielzeug...

Keine Entscheidung von mir, sollte scheinbar einfach herumtragbar sein
bin als Externer in dem Projekt und eher zufällig über die noch offene 
Anforderung gestolpert

Experte schrieb:
> Nein. Bei Sqlite bist Du eingeschränkt. Also lässt Du in der
> Sortier-Spalte NULL zu oder hast halt gar kein Constraint auf die
> Sortierspalte.

Die wollen unbedingt ein Constraint weil es da schon ganz üble Fehler 
gegeben haben soll (was auch einer meiner vielen Vorgänger da verbockt 
haben mag)

Danke für deine Tips, ich schaue mal was ich daraus mache

[Clemens L. (c_l)]

cppbert3 schrieb:
> es ist mir wegen Foreign Keys nicht erlaubt Sätze einfach zu löschen und
> neu einzufügen

PRAGMA foreign_keys = off;