SQL Unique Index und zwei vertauschbare Werte

Yuuri · 13. Oktober 2014

Hallo zusammen,

ich steh grad vor ner kleinen Frage: Ich hab ne SQLite DB vor mir mit einer Tabelle und einem Unique Index. Ein Auszug aus der Tabelle:

Code:

Index:
CREATE UNIQUE INDEX "Comparisons_Hash1_Hash2" ON "Comparisons" ("Hash1", "Hash2");

Daten:
id	Hash1	Hash2	Similarity
1	1	2	56.25
2	2	1	56.25

(Wie) kann ich diese beiden Zeilen als eine zusammenfassen bzw. so modellieren, dass hier der Unique Index greift? Trifft ja nicht direkt auf den Index zu, da die Spalten vertauscht sind, aber sinngemäß entsprechen ja beide Zeilen einem Ergebnis.

LG

benneq · 13. Oktober 2014

Erstell eine dritte Spalte: Hash1_Hash2
und da packst du dann "Hash1 SEPARATOR Hash2" rein. Ist zwar denomalisiert (ich steh drauf

), aber dann hast du sie zusammengefasst und trotzdem noch die Einzelwerte in den anderen Spalten verfügbar.
Und SEPARATOR sollte natürlich nicht Teil des Zeichensatzes von Hash1 und/oder Hash2 sein. Versteht sich ja von selbst.

EDIT: I see, ich habe das Problem falsch interpretiert.
Du brauchst also für die dritte Spalte eine Funktion, die aus "Hash1 verknüpft mit Hash2" ein eindeutiges Ergebnis liefert, das dasselbe Ergebnis ist wie "Hash2 verknüpft mit Hash1". Höhere Mathematik...

Spontane Idee #1:
4 Spalten: Hash1, Hash2, Hash1_2 und Hash2_2 und jeweils UNIQUE (Hash1, Hash2) und UNIQUE (Hash2_1, Hash2_2) und die Werte entsprechend vertauscht speichern.

Spontane Idee #2.1:
Lös es in deiner Programmlogik:

Code:

SELECT count(*) FROM Table WHERE Hash1 = x AND Hash2 = y OR Hash1 = y AND Hash2 = x
if(result == 0) {
  INSERT ...
}

Spontane Idee #2.2:
Das geht natürlich auch in einem einzelnen Query:

Code:

INSERT INTO Table (Hash1, Hash2, Similarity)
  SELECT 1, 2, 42.00 FROM DUAL
    WHERE NOT EXISTS (SELECT * FROM Table WHERE Hash1 = 1 AND Hash2 = 2 OR Hash1 = 2 AND Hash2 = 1)

(keine Ahnung ob SQLite das beherrscht. DUAL ist ein Virtueller Table, den man benutzen kann, wenn eigentlich auf keinen Table zugegriffen wird, aber man wegen der Syntax einen angeben muss)

Spontane Idee #3:
Als Funktion, um die beiden Hashes in beliebiger Reihenfolge eindeutig zu verknüpfen und in einer dritten Spalte zu speichern könntest du einfach Folgendes tun:

Code:

SEPARATOR = "-";
hash1 = 3;
hash2 = 4;
if(hash1 < hash2) {
  combinedHash = hash1+SEPARATOR+hash2; // also in dem Fall: "3-4"
} else {
  combinedHash = hash2+SEPARATOR+hash1; // -> "4-3"
}
INSERT INTO Table (Hash1, Hash2, CombinedHash, Similarity) VALUES (hash1, hash2, combinedHash, 42.00)

Dann steht immer der kleinere Wert vorne. Und somit ist die Spalte eindeutig definiert. Der SEPARATOR "-" ist nicht im Alphabet von hash1 und hash2 enthalten (sind ja einfach nur Zahlen mit Alphabet 0123456789). Dasselbe geht natürlich auch für Strings, wobei man wieder ein Zeichen braucht, das in keinem String vorhanden ist. Hier könnte man die beiden Hashes vorher durch einen Base64 Encoder jagen, dann hat man noch einige unbenutzte Zeichen zur Verfügung, die man als SEPARATOR missbrauchen kann.

Es reicht nicht die zwei Zahlen einfach (ohne SEPARATOR) zu konkatenieren: 1 + 222 wäre sonst dasselbe wie 12 + 22.

Yuuri · 13. Oktober 2014

Hehe, viele schöne Ideen, ich hab mich nun für Variante 2.2 entschieden (da sich nur die DB drum kümmern soll und ich auf Anwendungsebene nichts groß machen will). Der Rattenschwanz "WHERE NOT EXISTS" ist zwar leider ein Übel, aber das find ich akzeptabel.

Der Performance wegen speicher ich mir aber alles in ner temporären Tabelle zwischen (SQLite beherrscht leider keine Variablen), da die Hashes nur über Joins (drei Tabellen - Filenames, Hashes, Filenames_Hashes) ermittelt werden. Vielleicht stell ich auch bald auf ne richtige DB um, das Script läuft momentan nur temporär in der PowerShell (hab momentan auch nur den SQLite Provider installiert).

Funktioniert so wunderbar:

Querys:

Code:

CREATE TEMP TABLE IF NOT EXISTS Vars (
	id INTEGER PRIMARY KEY,
	HashId INTEGER NOT NULL
);

INSERT OR IGNORE INTO Vars
SELECT
	0 id,
	h.id HashId
FROM Filenames_Hashes fh
	JOIN Filenames f
		ON fh.Filename = f.id
		AND f.Filename = "C:\temp1.jpg"
	JOIN Hashes h
		ON fh.Hash = h.id;
INSERT OR IGNORE INTO Vars
SELECT
	1 id,
	h.id HashId
FROM Filenames_Hashes fh
	JOIN Filenames f
		ON fh.Filename = f.id
		AND f.Filename = "C:\temp2.jpg"
	JOIN Hashes h
		ON fh.Hash = h.id;

DELETE FROM Comparisons;

-- Testeintrag 0-1
INSERT OR IGNORE INTO Comparisons
SELECT
	NULL id,
	(SELECT HashId FROM Vars WHERE id = 0) Hash1,
	(SELECT HashId FROM Vars WHERE id = 1) Hash2,
	50 Similarity
WHERE NOT EXISTS
	(
		SELECT id
		FROM Comparisons
		WHERE
			(
				Hash1 = (SELECT HashId FROM Vars WHERE id = 0)
				AND Hash2 = (SELECT HashId FROM Vars WHERE id = 1)
			)
			OR
			(
				Hash2 = (SELECT HashId FROM Vars WHERE id = 0)
				AND Hash1 = (SELECT HashId FROM Vars WHERE id = 1)
			)
	);

-- Testeintrag 1-0
INSERT OR IGNORE INTO Comparisons
SELECT
	NULL id,
	(SELECT HashId FROM Vars WHERE id = 1) Hash1,
	(SELECT HashId FROM Vars WHERE id = 0) Hash2,
	50
WHERE NOT EXISTS
	(
		SELECT id
		FROM Comparisons
		WHERE
			(
				Hash1 = (SELECT HashId FROM Vars WHERE id = 0)
				AND Hash2 = (SELECT HashId FROM Vars WHERE id = 1)
			)
			OR
			(
				Hash2 = (SELECT HashId FROM Vars WHERE id = 0)
				AND Hash1 = (SELECT HashId FROM Vars WHERE id = 1)
			)
	);

Resultat:

Code:

id	Hash1	Hash2	Similarity
14	1	2	50

Variante 3 würde in ner richtigen DB sicherlich funktionieren (und wohl die beste Variante sein, als Erweiterung von #1), klappt in SQLite aber leider nicht.

Danke!

benneq · 13. Oktober 2014

Wieso funktioniert #3 denn in SQLite nicht? Ist doch einfach nur 'ne zusätzliche Spalte, in der ein String gespeichert wird ... oder?

Yuuri · 13. Oktober 2014

Ja klar, aber wie gesagt will ich auf Anwendungsebene keine "wenn dann sonst, aber nicht wenn ..." einbauen, sondern alles DB-basiert laufen lassen. Und Variablen kann ich da nicht deklarieren (außer über ne temporäre Tabelle). Vielleicht wäre noch ein IF() o.ä. irgendwo drin denkbar, aber da weiß ich nicht, ob SQLite das unterstützt. Müsst ich erst nachschauen. Gegen die zusätzliche Spalte spricht natürlich nichts, ist ja prinzipiell nur eine Erweiterung von #1 (bzw. #0). Am liebsten wär mir natürlich, dass alles die DB selbst über Indizes regelt, aber das funktioniert wohl leider nicht.

ice-breaker · 14. Oktober 2014

Du könntest für #3 einfach einen Trigger definieren, der dir diese verknüpfte Spalte füllt

Yuuri · 14. Oktober 2014

Ach ja, manchmal sieht man den Wald vor lauter Bäumen nicht...

Code:

DELIMITER ;;
CREATE TRIGGER "Comparisons_Trigger_HashOrder" AFTER INSERT ON "Comparisons"
BEGIN

UPDATE test
SET
  Hash1= MIN(new.Hash1,new.Hash2),
  Hash2 = MAX(new.Hash1,new.Hash2)
WHERE
  id = new.id;

END;;

DELIMITER ;

Wäre wohl zu einfach gewesen... Danke.

benneq · 14. Oktober 2014

Langweilige Lösung

Allerdings hättest du es so auch in deinem Programmcode lösen können. PowerShell kann doch bestimmt Min und Max oder?

Yuuri · 16. Oktober 2014

Wie gesagt, momentan mach ich alles in PowerShell und ist nur ne Art Proof of Concept. Die Anwendung soll später mal in nem PHP-Script ablaufen, evtl. bleibt beides auch simultan bestehen. Deswegen will ich gern alles an Constraints in die DB auslagern und im Programmcode wirklich nur das machen, was wirklich notwendig ist. Für die Datenhaltung und -organisation ist die DB ja schließlich gemacht.

Suche

SQL Unique Index und zwei vertauschbare Werte

Yuuri

Fleet Admiral

benneq

Fleet Admiral

Yuuri

Fleet Admiral

benneq

Fleet Admiral

Yuuri

Fleet Admiral

ice-breaker

Commodore

Yuuri

Fleet Admiral

benneq

Fleet Admiral

Yuuri

Fleet Admiral

Ähnliche Themen