DROP VIEW IF EXISTS dokindex;

DROP TABLE IF EXISTS volltextindex;
DROP TABLE IF EXISTS attribut;
DROP TABLE IF EXISTS woerterbuch;
DROP TABLE IF EXISTS dokument;

CREATE TABLE dokument
(
  did    INTEGER     NOT NULL,
  autor  VARCHAR(50) NOT NULL,
  titel  TEXT        NOT NULL,
  inhalt TEXT,

  PRIMARY KEY (did)
);

CREATE TABLE attribut
(
  aid  INTEGER     NOT NULL,
  attr VARCHAR(20) NOT NULL,
  
  PRIMARY KEY (aid)
);

CREATE TABLE woerterbuch
(
  wid  INTEGER     NOT NULL,
  wort VARCHAR(50) NOT NULL,

  PRIMARY KEY (wort)
);

CREATE TABLE volltextindex
(
  did INTEGER NOT NULL,
  wid INTEGER NOT NULL,
  aid INTEGER NOT NULL,
  pos INTEGER NOT NULL,
  
  PRIMARY KEY (wid, aid, pos, did),

  FOREIGN KEY (did) REFERENCES dokument    (did),
  FOREIGN KEY (aid) REFERENCES attribut    (aid)
);


CREATE VIEW dokindex AS
SELECT did, aid, pos, wid, wort, attr, autor, titel, inhalt
FROM dokument JOIN volltextindex USING (did)
              JOIN woerterbuch   USING (wid)
              JOIN attribut      USING (aid)
;
