Datenbankstruktur fuer Usenet-Archiv

[Stefan+ (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);

[Stefan+ (Stefan Froehlich)]

Nachdem ich dankenswerterweise in den letzten 2 Wochen noch einige
Ideen per Mail bekommen habe, ist mein Usenet-Archiv nun so weit,
dass ich zum Testen zur Verfuegung stellen moechte:
<http://reusenet.froehlich.priv.at/search.php>

Archiviert werden momentan de/at/ch mit einem Altbestand, der ca. 1
Jahr zurueckreicht. Das Interface ist immer noch sehr rudimentaer
und wird in den naechsten paar Wochen auch nicht geaendert werden -
primaer haette ich gerne Feedback ueber die Datenqualitaet, d.h.
verstuemmelt angezeigte, fehlerhafte oder fehlende Threads u/o
Artikel. Erst wenn ich mir absolut sicher bezueglich Datenqualitaet
bin, werde ich mich verstaerkt um das Interface kuemmern (Input
hierzu ist natuerlich trotzdem auch willkommen).

Der direkte Aufruf eines Artikels ist uebrigens mit
http://reusenet.froehlich.priv.at/posting.php?msgid=<***@example>
moeglich, dazu braucht man nicht ueber die Suchfunktion zu gehen.

Servus,
Stefan