Entwickler-Ecke
Datenbanken - Vom User festgelegte Ordnung einer Tabelle
Narses - Sa 10.07.10 13:58
Titel: Vom User festgelegte Ordnung einer Tabelle
Moin!
Ich habe eine Tabelle, die auf´s Wesentliche reduziert zwei Spalten hat:
Items: ItemName, ItemPos
Durch ItemPos soll bei der Abfrage mit ORDER BY ItemPos die Ordnung der Eintäge hergestellt werden. Sinn ist es, dass der User diese Ordnung herstellen kann.
Neue Einträge mit ItemPos := SELECT MAX(ItemPos)+1 FROM Items anlegen (btw: bisher brauche ich dafür zwei SQL-Queries, eine für das neue ItemPos und eine für das INSERT, geht das nicht auch mit nur einem? :gruebel:)
Einträge löschen: einfach raushauen. ;)
Problematisch wird´s jetzt beim Ändern der Reihenfolge schon bestehender Einträge. Hier ist ja nun etwas mehr zu tun. Ich habe aktuell eine Lösung, die finde ich aber nicht "elegant" (ihr kennt das ja schon :zwinker:).
Also, wie würdet ihr sowas angehen (konzeptionell)? :nixweiss:
cu
Narses
BenBE - Sa 10.07.10 16:07
Für das Hinzufügen:
SQL-Anweisung
1:
| INSERT INTO foo (ItemName, ItemPos) SELECT 'DeinItemName', Max(ItemPos)+1 FROM foo |
Für's Verschieben:
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
| BEGIN TRANSACTION
-- Abfragen einer freien ItemID
SET @tmpPos := SELECT MIN(ItemPos) - 1 FROM foo;
-- Eintrag dorthin verschieben
UPDATE foo SET ItemPos = @tmpPos WHERE ItemPos = :ItemPos LIMIT 1;
-- Einträge zwischen altem und (einschließlich) neuem Eintrag verschieben
UPDATE foo SET ItemPos = ItemPos - 1 WHERE ...
-- Eintrag wieder an richtige Stelle
UPDATE foo SET ItemPos = NewPos WHERE ItemPos = @tmpPos;
COMMIT |
Da die ersten beiden Schritte nicht atomar sind, brauchst Du hier zwingend eine Transaktion. Das dürfte aber sowieso ja kein Thema sein.
Wenn Du Indexfehler komplett ausschließen möchtest, solltest Du nicht nur einen Eintrag, sondern alle mit der MIN-Bedingung verschieben, die geändert werden müssen.
Unter 3 UPDATE-Statements geht's AFAIK nicht.
Narses - So 11.07.10 00:42
Moin!
Hm, hat MySQL AFAIK nicht? :nixweiss: Es werden aber auch nur wenige Datensätze hinzugefügt, häufiger ist das Sortieren.
Tja, kann man alles machen, bringt mich aber der Lösung nicht näher. :?
Trotzdem Danke für deine Vorschläge. ;)
 BenBE hat folgendes geschrieben  : |
Für das Hinzufügen:
SQL-Anweisung 1:
| INSERT INTO foo (ItemName, ItemPos) SELECT 'DeinItemName', Max(ItemPos)+1 FROM foo | |
Jau, das hab ich gesucht! :autsch: Danke! :zustimm:
| BenBE hat folgendes geschrieben : |
Für's Verschieben:
SQL-Anweisung 1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
| BEGIN TRANSACTION
-- Abfragen einer freien ItemID
SET @tmpPos := SELECT MIN(ItemPos) - 1 FROM foo;
-- Eintrag dorthin verschieben
UPDATE foo SET ItemPos = @tmpPos WHERE ItemPos = :ItemPos LIMIT 1;
-- Einträge zwischen altem und (einschließlich) neuem Eintrag verschieben
UPDATE foo SET ItemPos = ItemPos - 1 WHERE ...
-- Eintrag wieder an richtige Stelle
UPDATE foo SET ItemPos = NewPos WHERE ItemPos = @tmpPos;
COMMIT |
Da die ersten beiden Schritte nicht atomar sind, brauchst Du hier zwingend eine Transaktion. Das dürfte aber sowieso ja kein Thema sein. |
Ich schau mir das mal an, sieht spannend aus. :)
| BenBE hat folgendes geschrieben : |
| Wenn Du Indexfehler komplett ausschließen möchtest, solltest Du nicht nur einen Eintrag, sondern alle mit der MIN-Bedingung verschieben, die geändert werden müssen. |
Das hab ich noch nicht so ganz verstanden... :oops:
| BenBE hat folgendes geschrieben : |
| Unter 3 UPDATE-Statements geht's AFAIK nicht. |
Aktuell sind´s bei mir mehr, das sieht doch gut aus. :)
cu
Narses
BenBE - So 11.07.10 01:11
| Narses hat folgendes geschrieben : |
Moin!
Hm, hat MySQL AFAIK nicht? :nixweiss: Es werden aber auch nur wenige Datensätze hinzugefügt, häufiger ist das Sortieren.
Tja, kann man alles machen, bringt mich aber der Lösung nicht näher. :?
Trotzdem Danke für deine Vorschläge. ;) |
MySQL hat auch keine Sequences; zumindest nicht in dem Sinne, wie man sie von Postgres her kennt. Den AutoIncrement kann man aber durchaus dafür missbrauchen, solange man dafür sorgt, dass beim setzen eines UNIQUE-Index bei jeder Operation auf der Tabelle die Sortierung eindeutig bleibt. Man muss dann allerdings damit leben können, dass die Sortierung Lücken aufweisen kann (z.B. nach dem Löschen von Einträgen).
NP ;-)
Eigentlich recht simpel ;-)
1. Das in Transaction sollte klar sein. Encapsulation muss soweit sein, dass nur Stable Reads für die Transaktion zählen. Oder anders gesagt: Die Transaktion stellt hier einen Lock auf die Tabelle dar.
2. Das SET-Statement ermittelt die untere Grenze für verwendete Index-Nummern. Diese sollte theoretisch IMMER konstant bleiben; und zwar 0 (beim verschieben eines Datensatzes) - Bzw. 1 - Anzahl für den allgemeinen Fall. Womit wir bei ner kurzen Korrektur wären: Wir speichern an dieser Stelle mal das aktuelle Minimum.
3. Verschiebe alle Datensätze, die durch das Verschieben betroffen sind UNTER das aktuelle Minimum. Damit wird der Wertebereich zwischen der oberen und unteren Grenze frei.
4. Verschiebe alle Datensätze wieder zurück, die nur um eine Zeile verschoben werden.
5. Verschiebe den Datensatz mit der größten Bewegungsdistanz an die finale Stelle
6. Speichere die Änderungen - ACHTUNG: Dieser Commitkönnte wegen der zahlreichen Index-Operationen etwas brauchen.
Korrigiert also (:new,:old = Params, :old<:new):
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
| BEGIN TRANSACTION
-- Abfragen einer freien ItemID
SET @tmpPos := SELECT MIN(ItemPos) FROM foo;
-- Eintrag dorthin verschieben
UPDATE foo SET ItemPos = ItemPos - (:new - @tmpPos + 1) WHERE ItemPos >= :old AND ItemPos <= :new LIMIT :new - :old + 1;
UPDATE foo SET ItemPos = ItemPos + (:new - @tmpPos) WHERE ItemPos >= @tmpPos - :new + :old AND ItemPos <= @tmpPos LIMIT :new - :old;
-- Eintrag wieder an richtige Stelle
UPDATE foo SET ItemPos = :new WHERE ItemPos = @tmpPos - :new + :old - 1 LIMIT 1;
-- Speichern
COMMIT |
| Narses hat folgendes geschrieben : |
| BenBE hat folgendes geschrieben : | | Wenn Du Indexfehler komplett ausschließen möchtest, solltest Du nicht nur einen Eintrag, sondern alle mit der MIN-Bedingung verschieben, die geändert werden müssen. |
Das hab ich noch nicht so ganz verstanden... :oops: |
MySQL hat sich manchmal beim aktualisieren des Index, wenn man auf einem Unique Key
UPDATE foo SET bar = bar - 1 für
UNIQUE KEY baz(bar) ausführt. Daher muss man da erstmal Platz schaffen ;-)
| Narses hat folgendes geschrieben : |
| BenBE hat folgendes geschrieben : | | Unter 3 UPDATE-Statements geht's AFAIK nicht. |
Aktuell sind´s bei mir mehr, das sieht doch gut aus. :)
cu
Narses |
Du meinst: jeden Datensatz einzeln? Ach ja: Ich hoffe, die Änderungen für :new < :old bzw. :new = :old dürften klar gehen?
Kha - So 11.07.10 01:56
| BenBE hat folgendes geschrieben : |
| Unter 3 UPDATE-Statements geht's AFAIK nicht. |
Wenn kein Paging stattfinden soll, die Daten also immer komplett abgefragt werden, und im Gegenzug zur Entlastung des Servers der Client ein bisschen Aufwand beim Rekonstruieren der Daten haben darf: Wie wäre es damit, sie als verkettete Liste abzulegen (Recursive Foreign Key)? Dann wären wir immer noch bei 3 UPDATEs, aber es müssen auch nur genau 3 Reihen überhaupt angefasst werden :) .
Narses - So 11.07.10 20:11
Moin!
| Kha hat folgendes geschrieben : |
| Wie wäre es damit, sie als verkettete Liste abzulegen (Recursive Foreign Key)? |
Geniale Idee :D hat aber auch ein paar Haken... :? Muss ich mir aber mal ernsthaft durch den Kopf gehen lassen! :think:
cu
Narses
Entwickler-Ecke.de based on phpBB
Copyright 2002 - 2011 by Tino Teuber, Copyright 2011 - 2026 by Christian Stelzmann Alle Rechte vorbehalten.
Alle Beiträge stammen von dritten Personen und dürfen geltendes Recht nicht verletzen.
Entwickler-Ecke und die zugehörigen Webseiten distanzieren sich ausdrücklich von Fremdinhalten jeglicher Art!