Relationales Datenmodell mit SQL

[JBlond]

Ich habe die Folgen pro Autor gezählt bis 224 inklusive der Spezial-Folgen (per Hand). Eine solche Statisik wäre in der nett zu haben, wenn die mit von Programm erzeugt werden könnte.

44  André Marx
36  Marco Sonnleitner
25  Ben Nevis
17  Kari Erlhoff
16  Brigitte-Johanna Henkel-Waidhofer
15  M. V. Carey
14  William Arden
14  Hendrik Buchna
13  Christoph Dittert
13  André Minninger
10  Robert Arthur
10  Astrid Vollenbruch
4   Megan & H. William Stine
4   Marc Brandel
3   G. H. Stone
2   Nick West
2   Katharina Fischer
2   Peter Lerangis
1   Rose Estes
1   William McCay
1   Megan Stine
1   Ivar Leon Menger, John Beckmann

[YourMJK]

Danke, solche "queries" mit interessanten Statistiken werde ich auf die Liste der Features setzen, die ich für die neuen (linux-kompatiblen) command-line tools plane! Gute Idee.

[JBlond]

Für den Anfang vielleicht SQL? https://gist.github.com/JBlond/49bffa1626c229af3e0b96aeac7ddfa5

[YourMJK]

Das wäre natürlich am flexibelsten.
Überhaupt bin ich mit mir am Hadern das Ganze auf SQL umzustellen und dann folglich das JSON daraus abzuleiten. Oder umgekehrt.

Muss mir da noch Gedanken machen, will aber auf jeden Fall das Beitragen und Nutzen der Daten zugänglicher gestalten.

[YourMJK]

Im Branch v2.0 gibt es jetzt ein SQL-Dump der neuen Datenbank, kannst dich ja gerne mal testweise damit spielen :)

Hier ist das Schema:

CREATE TABLE "hörspiel"(
    "hörspielID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "titel" TEXT NOT NULL,
    "kurzbeschreibung" TEXT,
    "beschreibung" TEXT,
    "metabeschreibung" TEXT,
    "veröffentlichungsdatum" DATE,
    "unvollständig" BOOLEAN NOT NULL,
    "cover" BOOLEAN NOT NULL,
    "urlCoverApple" TEXT,
    "urlCoverKosmos" TEXT
);
CREATE TABLE "hörspielTeil"(
    "teil" INTEGER PRIMARY KEY NOT NULL REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE,
    "hörspiel" INTEGER NOT NULL REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE,
    "position" INTEGER NOT NULL CHECK("position" > 0),
    "buchstabe" TEXT CHECK(LENGTH("buchstabe") = 1),
    UNIQUE("hörspiel", "position"),
    UNIQUE("hörspiel", "buchstabe")
);

CREATE TABLE "serie"(
    "nummer" INTEGER PRIMARY KEY NOT NULL,
    "hörspielID" INTEGER NOT NULL UNIQUE REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "spezial"(
    "hörspielID" INTEGER PRIMARY KEY NOT NULL REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "kurzgeschichten"(
    "hörspielID" INTEGER PRIMARY KEY NOT NULL REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "dieDr3i"(
    "nummer" INTEGER PRIMARY KEY NOT NULL,
    "hörspielID" INTEGER NOT NULL UNIQUE REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE "medium"(
    "mediumID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "hörspielID" INTEGER NOT NULL REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE,
    "position" INTEGER NOT NULL CHECK("position" > 0),
    "xldLog" BOOLEAN NOT NULL,
    UNIQUE("hörspielID", "position")
);
CREATE TABLE "track"(
    "trackID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "mediumID" INTEGER NOT NULL REFERENCES "medium"("mediumID") ON DELETE CASCADE ON UPDATE CASCADE,
    "position" INTEGER NOT NULL CHECK("position" > 0),
    "titel" TEXT NOT NULL,
    "dauer" INTEGER NOT NULL CHECK("dauer" > 0),
    UNIQUE("mediumID", "position")
);
CREATE TABLE "kapitel"(
    "trackID" INTEGER PRIMARY KEY NOT NULL REFERENCES "track"("trackID") ON DELETE CASCADE ON UPDATE CASCADE,
    "hörspielID" INTEGER NOT NULL REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE,
    "position" INTEGER NOT NULL CHECK("position" > 0),
    "abweichenderTitel" TEXT,
    UNIQUE("hörspielID", "position")
);

CREATE TABLE "person"(
    "personID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "name" TEXT NOT NULL UNIQUE
);
CREATE TABLE "pseudonym"(
    "pseudonymID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "name" TEXT NOT NULL UNIQUE
);
CREATE TABLE "rolle"(
    "rolleID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "name" TEXT NOT NULL UNIQUE
);
CREATE TABLE "sprechrolle"(
    "sprechrolleID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "hörspielID" INTEGER NOT NULL REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE,
    "rolleID" INTEGER NOT NULL REFERENCES "rolle"("rolleID") ON DELETE CASCADE ON UPDATE CASCADE,
    "position" INTEGER NOT NULL CHECK("position" > 0),
    UNIQUE("hörspielID", "rolleID"),
    UNIQUE("hörspielID", "position")
);
CREATE TABLE "spricht"(
    "sprechrolleID" INTEGER NOT NULL REFERENCES "sprechrolle"("sprechrolleID") ON DELETE CASCADE ON UPDATE CASCADE,
    "personID" INTEGER NOT NULL REFERENCES "person"("personID") ON DELETE CASCADE ON UPDATE CASCADE,
    "pseudonymID" INTEGER REFERENCES "pseudonym"("pseudonymID") ON DELETE SET NULL ON UPDATE CASCADE,
    PRIMARY KEY("sprechrolleID", "personID")
);

CREATE TABLE "hörspielBuchautor"(
    "hörspielID" INTEGER NOT NULL REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE,
    "personID" INTEGER NOT NULL REFERENCES "person"("personID") ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY("hörspielID", "personID")
);
CREATE TABLE "hörspielSkriptautor"(
    "hörspielID" INTEGER NOT NULL REFERENCES "hörspiel"("hörspielID") ON DELETE CASCADE ON UPDATE CASCADE,
    "personID" INTEGER NOT NULL REFERENCES "person"("personID") ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY("hörspielID", "personID")
);

---

SELECT COUNT(*) AS anzahl, p.name FROM hörspiel NATURAL JOIN hörspielBuchautor NATURAL JOIN person p GROUP BY p.name ORDER BY anzahl DESC;

Mit dieser Query komme ich auf das folgende Ergebnis:

43  Marco Sonnleitner
41  André Marx
28  Ben Nevis
24  Kari Erlhoff
23  Hendrik Buchna
21  André Minninger
17  Christoph Dittert
16  Brigitte Johanna Henkel-Waidhofer
15  William Arden
15  M. V. Carey
11  Robert Arthur
7   Astrid Vollenbruch
4   Megan & H. William Stine
4   Marc Brandel
4   G. H. Stone
3   Tim Wenderoth
3   Ivar Leon Menger
2   Peter Lerangis
2   Nick West
2   Katharina Fischer
1   William McCay
1   Rose Estes
1   Megan Stine
1   Markus Winter
1   John Beckmann

Worauf die Diskrepanzen mit deinem Ergebnis genau zurückzuführen sind, weiß ich auf die Schnelle nicht. Hat vielleicht etwas mit den Teilen und Folge 200 zu tun (welche bei mir als nur ein Hörspiel modelliert ist, weil die Teile keinen eigenen Titel haben, und somit auch nur ein Credit André Marx angerechnet wird).

Der Fairness halber müsste man die Query wahrscheinlich so anpassen, das pro Teil ein Credit angerechnet wird, wenn die Teile keine eigenen Autoren haben (wie es bei 175 der Fall ist). Dann würde Marx vier Credits statt nur einen für 200 bekommen – ist ja auch ein 4h+ Hörbuch.

Oder man passt den Datenbestand noch an, was die "Erbung" von Daten bei Mehrteiligen Hörspielen angeht. Keine Ahnung, was hier die sinnvollste Lösung ist.

[YourMJK]

FYI, mit PR #54 ist diese Statistik jetzt auf der Seite: https://v2.dreimetadaten.de/statistik.html

[JBlond]

Worauf die Diskrepanzen mit deinem Ergebnis genau zurückzuführen sind, weiß ich auf die Schnelle nicht.

ich schon. Das liegt daran, dass ich alle Sonder/Spezialfolgen mit drin habe und auch ein paar Sachen außerhalb der normalen Reihe und auch wie schon von dir erwähnt die Zählung der Folgen die einzelnen Teile aus "und die Geisterlampe" A bis L, Das Rätsel der Sieben A - G

[JBlond]

Was für eine Datenbank ist gewählt worden? in MySQL kann ich das so leider nicht importieren.

[YourMJK]

Das liegt daran, dass ich alle Sonder/Spezialfolgen mit drin habe und auch ein paar Sachen außerhalb der normalen Reihe

Hm, ich aber eigentlich auch …

Was für eine Datenbank ist gewählt worden?

SQLite!