Datenbankstruktur fuer Usenet-Archiv

[Stefan Froehlich]

Nachdem mir Google-Groups immer unsympathischer wird, ich aber auf
sinnvolle Suche im Usenet nicht verzichten will, bin ich dabei, ein
eigenes Archiv ins Leben zu rufen. Fuer wen es letztendlich zugaenglich
sein wird, ist noch offen - haengt u.a. von rechtlichen und finanziellen
Aspekten ab.

Zur Zeit beschaeftige ich mich gerade damit, ein moeglichst langfristig
stabiles Datenbanklayout zu entwickeln und haette dazu gerne Feedback
jedweder Art (sowohl, was funktionelle Aspekte betrifft, als auch
datenbanktechnische Belange). Die derzeitige Struktur ist binnen weniger
Stunden entwickelt worden und insofern wohl noch verbesserungsfaehig - in
einem Prototyp bewaehrt sie sich jedoch schon einigermassen
(<http://www.froehlich.priv.at/reusenet/groups.php>, wobei das nicht fuer
Produktivbetrieb bestimmt ist; die "Oberflaeche" ist rudimentaerst und von
den dargestellten Gruppen sind nur de.comp.lang.php und de.sci.physik mit
nennenswerte Artikeln befuellt).

Was jedenfalls noch fehlt, ist ein Volltextindex - wie macht man so etwas
in PostgreSQL sinnvollerweise, wenn die Postings unterschiedliche (und
abseits von einer groben Zuordnung via Hierarchie noch nicht einmal eine
bekannte) Sprache haben? Hier fehlt mir die Erfahrung, mit MySQL/InnoDB
ist das zwar weniger leistungsfaehig, dafuer aber einfacher...

Servus,
Stefan


DROP TABLE IF EXISTS newsgroup CASCADE;
CREATE TABLE newsgroup (
IDNewsgroup SERIAL,

FKIDNewsgroupParent INTEGER
REFERENCES newsgroup(IDNewsgroup)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE,

NameComponent VARCHAR(128) NOT NULL,
NameFull VARCHAR(1024) NOT NULL,
Tagline VARCHAR(160)
Charta TEXT,

PRIMARY KEY (IDNewsgroup),
UNIQUE(NameFull),
UNIQUE(FKIDNewsgroupParent, NameComponent)
);

DROP TABLE IF EXISTS posting CASCADE;
CREATE TABLE posting (

FKIDPostingParent INTEGER
REFERENCES posting(IDPosting)
ON UPDATE CASCADE
ON DELETE SET NULL
DEFERRABLE,

FKIDPostingThread INTEGER
REFERENCES posting(IDPosting)
ON UPDATE CASCADE
ON DELETE SET NULL
DEFERRABLE,

MissingParent VARCHAR(128) DEFAULT NULL,

MessageId VARCHAR(128) NOT NULL,
XNoArchive BOOLEAN NOT NULL DEFAULT 'f',
Cancelled BOOLEAN NOT NULL DEFAULT 'f',
Superseded BOOLEAN NOT NULL DEFAULT 'f',
Expires TIMESTAMP,

FKIDPostingCancel INTEGER
REFERENCES posting(IDPosting)
ON UPDATE CASCADE
ON DELETE SET NULL
DEFERRABLE,

FKIDPostingSupersede INTEGER
REFERENCES posting(IDPosting)
ON UPDATE CASCADE
ON DELETE SET NULL
DEFERRABLE,

FromName VARCHAR(128),
FromEmail VARCHAR(128),
SubjectHeader VARCHAR(512) NOT NULL,
DateHeader TIMESTAMP NOT NULL,

Posting TEXT NOT NULL,

PRIMARY KEY (IDPosting)
);

CREATE INDEX posting_date ON posting(DateHeader);
CREATE INDEX posting_messageid ON posting(MessageId);
CREATE INDEX posting_missingparent ON posting(MissingParent);
CREATE INDEX posting_fkidpostingthread ON posting(FKIDPostingThread);
CREATE INDEX posting_fkidpostingparent ON posting(FKIDPostingParent);
CREATE INDEX posting_fkidpostingcancel ON posting(FKIDPostingCancel);
CREATE INDEX posting_fkidpostingsupersede ON posting(FKIDPostingSupersede);

DROP TABLE IF EXISTS posted_in CASCADE;
CREATE TABLE posted_in (
FKIDNewsgroup INTEGER NOT NULL
REFERENCES newsgroup(IDNewsgroup)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE,

FKIDPosting INTEGER NOT NULL
REFERENCES posting(IDPosting)
ON UPDATE CASCADE
ON DELETE CASCADE
DEFERRABLE,

PRIMARY KEY (FKIDNewsgroup, FKIDPosting)
);

CREATE INDEX posted_in_fkidposting ON posted_in(FKIDPosting);

DROP TABLE IF EXISTS header CASCADE;
CREATE TABLE header (
FKIDPosting INTEGER NOT NULL
REFERENCES posting(IDPosting)
ON UPDATE CASCADE
ON DELETE CASCADE
DEFERRABLE,

Header VARCHAR(64),
Content VARCHAR(1024),

PRIMARY KEY (FKIDPosting, Header)
);

CREATE INDEX header_Header_Content ON header(Header, Content);

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Stefan - mit der mächtigen Verführung hinterlistiger Liegen.
(Sloganizer)

[Stefan Froehlich]

On Tue, 31 Jul 2012 19:28:51 Stefan Ram wrote:
> >XNoArchive BOOLEAN NOT NULL DEFAULT 'f',

> Das erscheint mir als etwas zu fragil; was ist, wenn ein Post
>
> X-No-Archive: Alpha

> enthält? Das sollte zwar nicht vorkommen, der Server sollte
> es zurückweisen, aber was ist, wenn so etwas doch auftaucht?

Dann wird es halt nicht erkannt.

Die Grundidee hinter dem Schema ist: alle Header eines Postings
werden in der entsprechenden Tabelle gefuehrt, die aber eher
beilaeufigen Charakter hat (evt. fuer statistische Auswertungen) und
ansonsten kaum benoetigt wird. Das Posting als solches ist im
TEXT-Feld samt allen Headern gespeichert, die fuer Suche oder
Spezialfunktionen wesentlichen Header sind noch einmal extra
herausgezogen (From, Subject, Date, etc.).

Die Flags XNoArchive, Cancelled und Superseded sind also lediglich
dazu da, die Anzeige eines Postings zu unterdruecken, wenn z.B. ein
User nicht ausreichend Berechtigungen hat. Im konkreten Fall wuerde
"X-No-Archive: Alpha" also als Header abgespeichert, fuer das Flag
jedoch ignoriert werden.

> Schau Dir mal die Header meines Postings an, könntest Du die
> alle unverfälscht unterbringen?

Ich kann. Testhalber habe ich nun diese Gruppe und damit auch diesen
Thread in die Datenbank mit aufgenommen:
<http://www.froehlich.priv.at/reusenet/posting.php?msgid=%3CUsenet-Archiv-20120731192515%40ram.dialup.fu-berlin.de%3E>
(Ich gebe bzgl. XNAY nur einen Hinweis aus - die Oberflaeche ist ja
eher zum Debuggen gedacht, als fuer Produktivbetrieb)

> Ich würde erst einmal ein Rohspeicherformat vorsehen (das
> muß keine relationale Datenbank benutzen), das alles
> möglichst uninterpretiert als Rohtext speichert, [...]

> Man kriegt die Tabellenstruktur vielleicht nicht
> gleich perfekt hin, aber dann importiert man es nach einer
> Änderung eben gegebenenfalls erneut, muß es aber nicht
> alles erneut vom Server holen.

Ich habe mir rund 10k Artikel zum Spielen heruntergeladen, die im
originalen Format vorliegen. Die sind nach Aenderungen in ein paar
Minuten importiert - aber die Frage ist inzwischen eben, ob noch
Aenderungen notwendig sind, oder ich mich schon, auf dem jetzigen
Layout aufbauend, mit den naechsthoeheren Dingen beschaeftige.

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Der unglaubliche Politiker will Stefan. Warum wohl?
(Sloganizer)