Multimedia Datenbank - Sinnvolle Indexe / Strukturen

Hallo liebe Community!

Da dieses mein erster Beitrag in diesem Forum ist, möchte ich mich kurz vorstellen.
Mein Name ist Norman und ich studieren Angewandte Informatik an der FH-Hannover im 5. Semester.
Teil dieses (und des nächsten) Semesters ist ein Praxis-Projekt. Ich und acht Kommilitonen habe nun dass Vergnügen, eine “Multimedia Datenbank” (multimedia beschreibt in diesem Fall Bild-Dateien) zu entwickeln.

Wir befinden uns zur Zeit in der Analyse-Phase, haben uns aber bereits für PostgreSQL entschieden.

Der nächste Schritt für das Datenbank-Team ist es nun, ein sinnvolles Datenbank Schema zu erarbeiten.
Der erste Prototyp unserer Anwendung soll nur das manuelle taggen und hochladen, sowie eine automatisierte Extraktion von relevanten Metadaten (EXIF-Daten) realisieren.

Wir haben uns bereits Gedanken gemacht, wie die Tabellen zum speichern dieser Daten aussehen könnten.

Table IMAGE_LINK
SERIAL id
VARCHAR external_link (pfad zur bilddatei auf fileserver)

Table TAGS
VARCHAR tag (PRIMARY KEY)
FK image_id[] (alle ids von Bilddateien, die dieses Tag tragen in form eines arrays)

Table METADATA
FK SERIAL image_id
SMALLINT image_width
SMALLINT image_high
… (weitere EXIF-Datent)

Nun brennen mir insbesondere diese Fragen auf der Seele:

  1. Ist unser Ansatz mit den Arrays in der TAG-Tabelle sinnvoll? Wir würden natürlich einen INDEX auf die TAG-Spalte legen um eine perfomante Suche nach TAGS möglich zu machen.

  2. Wie würdet ihr die Indexierung der METADATA Tabelle handhaben? Jeweils ein Index für jede Spalte? Macht das Sinn?

  3. Bis jetzt habe ich immer gelesen dass Binäre Dateien möglichst nicht direkt in der DB abgelegt werden sollen. Als Grund wurden immer Performance-Probleme angegeben.
    Ich würde dass gerne einfach so hinnehmen und mit Verweisen auf einen Fileserver arbeiten aber unsere Professorin besteht darauf, die Alternative mit der direkten Speicherung der Bilder in der DB zu testen. Was meint Ihr dazu?

Ich würde mich sehr über Feedback freuen! Im Gegenzug bin ich bereit unsere Fortschritte, Arbeitsergebnisse o.ä. bei Interesse für die Community offenzulegen und anderen somit die Möglichkeit zu geben, aus unseren Fehlern und Problemen zu lernen.

Freundliche Grüße
Norman

Nein, das mit den Arrays ist nicht gut. Speichere lieber in einer Tabelle die Tags mit einer ID und der Bezeichnung und in einer weiteren Tabelle die Zuordnung Tag-ID und Image-ID.

Zu 2.: Depends on eure Abfragen… Indexe auf die Spalten, die in WHERE-Bedingungen auftauchen UND auch sinnvoll indiziert werden können (Selektivität)

Zu 3.: teste es. Bilder in der DB haben halt den Nachteil, erst durch die DB gezogen werden zu müssen. Mit allen Konsequenzen wie z.B. Nutzung von Shared-Mem der DB…

Ansonsten: Willkommen hier und viel Erfolg! Und wenn ihr einen Hoster sucht, … SCNR.


Andreas

Hallo Andreas!

Vielen Dank für deinen Kommentar!

zu 1:

Unser Denkansatz war, Redundanz der Tags zu vermeiden.
Aber ich nehme an, dass die Arrays auch einen nicht unerheblichen Verlust an Performance mit sich bringen würden und du uns deshalb davon abrätst?

Zu 2:
Die Anfragen unseres 1. Prototypen werden wohl in etwa so aussehen:

Finde alle Bilder, die die Tags “Sportfest 2009” und “Fussball” tragen

oder

Finde alle Bilder, die die Tags “Sportfest 2009” und “Fussball” tragen und eine Auflösung von min. 1024 x 768 Pixel betragen

o.ä.

Die Abfragemöglichkeiten sollen an die erweiterte Bildersuche von Google angelehnt werden.

Ich denke mal dass man sinnvolle Indizes für diese Tabelle gut mit einem Tool ermittel könnte oder? Stichwort: Index-Wizards ?!

zu 3.

denke auch das wir da nicht drumrum kommen :wink:

gruß
norman

edit:
danke für das angebot mit dem hosting, aber bei 500 eur studiengebühren kann man ja wohl verlangen das für hochschulinterne projekte entsprechende infrastruktur zur verfügung gestellt wird :wink:

zu 1: ja. große arrays zu durchsuchen ist bäh.

zu 2: so ein tool kenne ich nicht, aber wie schon gesagt: spalten, die im where auftauchen, sind kandidaten für Indexe.

Andreas

zu 1.
naja, wir würden die arrays ja nicht durchsuchen. wenn ein tag für eine suchanfrage zutrifft, möchte man ja “nur” alle ergebnisse aus dem array auslesen. dass dürfte doch eigentlich kein problem sein? und das Einfügen am ende, müsste ja auch in konstanter zeit möglich sein, bzw. sogar O(1) ?!


Eine neue Frage ist aufgetaucht:

Wie kann man es realisieren, dass z.B. bei der Suchanfrage zu “Nagetier” wirklich alle Nagetiere (z.B nur als Maus oder Hamster getaggte bilder) ausgegeben werden?!
Gibt es da eine universelle Lösung für dieses Problem? :wink:

Gruß
Norman

Du willst möglicherweise wissen, welche Tags ein bestimmtes Bild hat.

Andreas

Da muss ich Andreas zustimmen, das macht keinen Sinn und wird auf Dauer die Sache nur komplizierter machen. Mach eine vernünftige normalisierte Tabelle daraus.


  1. Bis jetzt habe ich immer gelesen dass Binäre Dateien möglichst nicht direkt in der DB abgelegt werden sollen. Als Grund wurden immer Performance-Probleme angegeben. Ich würde dass gerne einfach so hinnehmen und mit Verweisen auf einen Fileserver arbeiten aber unsere Professorin besteht darauf, die Alternative mit der direkten Speicherung der Bilder in der DB zu testen. Was meint Ihr dazu?

Es hängt immer von der Umgebung ab. Beide Vorgehen haben ihre Vor- und Nachteile.
Eine interessante Untersuchung zu diesem Thema gibt es von Microsoft. Ist zwar auf Basis des SQL Server aber die Grundsätzliche Aussage ist, dass es hauptsächlich von der Größe der Bilder (Binärdateien) abhängt. Bei vielen kleinen Dateien ist es - zumindest in deren Testumgebung - schneller diese in der DB zu speichern.
Ich habe aber keine Ahnung ob und in wie weit sich diese Ergebnisse auf Postgres übertragen lassen (das Dateisystem spielt ja dann auch eine große Rolle).

Aber unterm Strich hilft nur: selber testen :wink:

Hier ist der Link zu dem Artikel
To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem - Microsoft Research

Hallo,

Zu der Sache mit den Arrays: das ist nicht sinnvoll, weil das nicht skaliert. Wie genau das mit dem von euch angedachten Index funktionieren soll, ist mir auch noch nicht klar. Also lieber eine 1:n Tabelle verwenden und die Daten (massiv) normalisieren. Redundanz vermeidet ihr übrigens am besten durch Normalisierung.

Zur Indizierung der Metadaten: ein Index macht nur dort Sinn, wo du auch Anfragen auf die entsprechende Spalte stellst. Speziell bei den Metadaten würde ich mir überlegen, ob ich das nicht in eine Key/Value Tabelle auslagere und die Keys selbst noch mal in eine weitere Tabelle, um das ganze zu normalisieren.

Zu Binärdaten in der Datenbank: das macht nur selten Sinn. Aber wenn eure Professorin das so haben möchte, würde ich zwei Wege implementieren und beide zum Schluß analysieren. Und zwar hinsichtlich Performance kleine, durchschnittlich großer und großer Dateien.

Zur Kontextsuche (Hamster versus Nagetiere): Das ist nicht Aufgabe der Datenbank. Woher soll die Datenbank wissen, was alles ein Nagetier ist? Und um deine Frage zu beantworten: nein, ich glaube nicht, dass es dafür eine universelle Lösung gibt. Ev. könntet ihr über bereits vorhandene Tags beim Einfügen neuer Bilder dem Anwender weitere Tags vorschlaten, ala: “Anwender die dieses Tag verwenden, haben auch dieses Tag …” und so weiter. Damit könnt ihr zumindest auf die Intelligenz der Masse setzen.

So, und warum kann man bei euch nicht als Dozent eine Vorlesung zum Thema PostgreSQL halten? :wink:

Es gibt auch noch den hstore Datentyp. Das ist so ein key->value Store. Bei 9.0 ist er glaube ich schon an Bord, ansonsten ist er bei den contribs zu finden. Damit lassen sich Tags m.M.n. ganz gut verwalten. Index kann man auch drauf legen, so dass das auch einigermaßen performant sein sollte. OpenStreetMap benutzt hstore für die Tags der Nodes und Ways z.B.

Hallo!

Vielen Dank erstmal für das hilfreiche Feedback!

So, und warum kann man bei euch nicht als Dozent eine Vorlesung zum Thema PostgreSQL halten? :wink:

Dazu muss ich sagen, dass wir natürlich Vorlesungen zum Thema Datenbanken hatten. Ich muss allerdings zugeben, dass ich in diesem Bereich nicht grade sehr bewandert bin und dieses Projekt nutzen will, um das zu Ändern :wink:

Hier ist der Link zu dem Artikel
To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem - Microsoft Research

Danke für den Link, so eine Studie ist doch ein sehr gutes Kontra-Argument :wink:

Wir haben nun ein erstes Schema auf die Beine gestellt, ohne die Möglichkeit der Key/Value Tabellen zu berücksichtigen. Das werden wir spätestens dan tun, wenn der Aktuelle Ansatz performance mäßig an seine Grenzen stößt oder ihn hier jemand völlig auseinander nimmt. Letzteres wäre mir natürlich lieber als ein BIG-BANG kurz vor Projekt-Schluss ^^


Erkläung zum Schema:


Erklärung

Die Zentrale Tabelle ist die image-Tabelle. Jedem Bild wird eine eindeutige ID, sowie der Pfad zum Fileserver, Dateiformat, Dateiname und optional eine Beschreibung zugeordnet.
Die extrahierten Meta-Daten (EXIF usw.) werden in der meta-Tabelle gespeichert und einer image-id zugeordnet. Es ergibt sich eine 1 zu 1 Beziehung. Sinnvolle Indizes für die Meta-Tabelle werden analytisch ermittelt.
Gleiches gilt für die meta_gps Tabelle.
Für jedes neue Tag wird in tag_ids ein neuer Eintrag erstellt. Jedes Tag erhält eine eindeutige ID.
In der Tabelle img_tags werden den Bildern, bzw. deren IDs, TAGs, bzw. deren IDs, zugeordnet. Somit ergibt sich eine Navigierbarkeit in beide Richtungen:

Welche Bilder haben das TAG „Mensch“?

SELECT img_id FROM tag_ids JOIN img_tags on (tag_ids.ID = img_tags.TAG_ID) WHERE tag_name = ‘mensch’

Welche TAGs hat Bild xyz?

SELECT tag_id FROM tag_ids JOIN img_tags on (tag_ids.ID = img_tags.TAG_ID) WHERE img_id =‘id(xyz)’

Die Tabellen user und img_owner erlauben, Bilder einem Benutzer zuzuordnen. Diese Relation soll es dem Benutzer auch ermöglichen, seine Dateien zu kommentieren.

Perspektive

Auf der Sitzung vom 17.11.2010 wurde diskutiert, ein besonderes „Event-Tag“ einzuführen. Dieses würde erlauben, sich alle Bilder eines bestimmten Events anzeigen zu lassen. Diese Funktion wäre vor allem Hochschulintern sinnvoll, da der Vorhandene Bildbestand i.d.R immer einem bestimmten Ereignis zugerdnet ist (Bossel2006, Absolventa2010 usw.)
Weitere Ideen des DB-Teams:

  1. Es sollte dem Benutzer möglich sein, die Sichtbarkeit der Bilder für bestimmte Benutzergruppen einzuschränken.
  2. Der Benutzer sollte entscheiden können, wer seine Bilder bearbeiten darf

Wünsche euch allen ein schönes Wochenende!!!

Gruß
Norman

Hallo Liebe Community,

nachdem nun etwas Zeit ins Land gegangen ist, habe ich mal wieder eine Frage.
Ich bin für den Persistenz Controler, also eine Fassaden-Klasse der Persistenz-Schicht zur Anwendungs-Schicht zuständig.
Nun fragen wir uns, was der eleganteste Weg ist, um entsprechnde Querys vorzubereiten.

Szenario:
Unser erster Prototyp soll die Suche nach “Meta Daten” eines Bildes ermöglichen. Diese Daten sind einige EXIF Daten, die von der Applikationsschicht aus dem Bild extrahiert worden sind.
Jemand kann also z.B. nach allen Bildern suchen die 1024 * 768 Pixel groß sind, ODER vom Autor xy sind. Oder alle Bilder die 1024 * 768 Pixel groß sind UND vom Autor xy.
Und genau da ist jetzt mein Problem:

Wir könnten für jedes Meta-Datum eine findBy()-Methode anbieten (findByHeight(int high), findByWidth(int width, findByAuthor(String author)).
Schön wäre es, wenn man die Ergebnisse bzw. die Querys, die durch diese Methoden ausgelöst werden, JOINEN könnte. Dann hätte man zumindest schonmal die UND verknüpfung wenn ejmand nach mehreren Meta Daten sucht.

Eine Alternative wäre, die Apllikationsschicht ruft eine Methode findByMeta(Meta metaObject) auf, in der uns ein Objekt übergen wird, welches die gesuchten Paramter als Instanz-Attribut trägt.
Dann müssten wir allerding irgendwie ganz umständlich daraus eine SQL-Query mit ‘n’ WHERE Bedingungen bauen, was meiner Meinung nach eine schlechte Lösung wäre.

Wäre schön wenn mir jemand einen Denkanstoß liefern könnte :wink:

MfG
Norman
Wir arbeiten übrigens mit JDBC!

Da waren sie wieder, die Probleme. Framework …

Nun, leztzten Endes muss das Framework die Anfragen so effizient wie möglich zusammenstellen und so wenig wie möglich Daten aus der DB lesen. Zuerst mal alle Daten holen und dann darin weiter suchen ist sehr ineffizient.

Wie genau ihr das löst … oommmhhhhh :wink:

So, habe heute den ganzen Tag dran gesessen und was benutzbares ist sogar bei rausgekommen.
Inwiefern das ganze perfomant ist, wird sich erst herausstellen, wenn wir genügend TestDatensätze in der DB haben.
Habe das Problem wie folgt “gelöst”:

        StringBuffer query = new StringBuffer("SELECT * FROM meta WHERE ");
        int whereclausecount = 0;
        
        //Wenn der Benutzer bei Artist eine Bedingung eingegeben hat
        if(meta.getArtist()!=null){
            query.append("artist LIKE '%"+meta.getArtist()+"%'");
            whereclausecount++;
        }

        //Wenn der Benutzer bei ImageHeight eine Bedingung eingegeben hat        
        if(meta.getImageHeight()>0.0){
            if(whereclausecount>0)
                query.append(" AND ");
            query.append("imageheight="+meta.getImageHeight());
            whereclausecount++;
        }

        //Wenn der Benutzer bei ImageWidth eine Bedingung eingegeben hat  
        if(meta.getImageWidth()>0.0){
            if(whereclausecount>0)
                query.append(" AND ");
            query.append("imagewidth="+meta.getImageWidth());
            whereclausecount++;

        }
        System.err.println(query);
        String sqlstmt = query.toString();
        resultSet = test.selectFromTable(sqlstmt);

Somit ergibt sich, dass man die DB zumindest schonmal nach “artist”, “imagewidth” und “imageheight” durchsuchen kann.
Und das ganze ist ja beliebig erweiterbar…


Würde euch das ganze gerne hier zeigen, habe nämlich eine JSP gebaut um das ganze zu testen. Leider bräuchtet ihr dazu einen SSH-Tunnel in unser FH Netzwerk, den ich euch leider nicht anbieten kann :stuck_out_tongue:
Aber wenn das ganze irgendwann mal richtig showreif & public ist, lassen wir euch gerne an unserem Ergebnis teilhaben!

Gruß
Norman

Eine Kleinigkeit: ich würde die einzelnen WHERE-Bedingungen in einem Array sammeln und das zum Schluß mittels " AND " joinen.

like this?!

        ArrayList<String> query = new ArrayList<String>();
        StringBuffer finalQuery = new StringBuffer("SELECT * FROM meta");

        if (meta.getArtist() != null) {
            query.add("artist LIKE '%" + meta.getArtist() + "%'");
        }

        if (meta.getImageHeight() > 0.0) {
            query.add("imageheight=" + meta.getImageHeight());
        }

        if (meta.getImageWidth() > 0.0) {
            query.add("imagewidth=" + meta.getImageWidth());
        }

        for (int i = 0; i < query.size(); i++) {
            if(i==0)
                finalQuery.append(" WHERE ");
            finalQuery.append(query.get(i));
            if(i<query.size()-1)
                finalQuery.append(" AND ");
        }

Schaut besser - und imho schlüssiger lesbar - aus.