-- -*- SQL -*-
-- Copyright (C) 2006, Christof Meerwald
-- http://cmeerw.org

-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; version 2 dated June, 1991.
--
-- This program is distributed in the hope that it will be useful, but
-- WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
-- General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
-- USA

CREATE TABLE category (
    id INTEGER PRIMARY KEY,
    pid INTEGER,
    name TEXT NOT NULL,
    priority INTEGER NOT NULL DEFAULT 0,
    lastupdate INTEGER,
    descr BLOB,
    UNIQUE (pid, name)
);

CREATE INDEX category_pid ON category (
    pid
);

CREATE TABLE related (
    id INTEGER PRIMARY KEY,
    cid INTEGER NOT NULL,
    rid INTEGER NOT NULL,
    UNIQUE (cid, rid)
);

CREATE INDEX related_cid ON related (
    cid
);

CREATE TABLE link (
    id INTEGER PRIMARY KEY,
    cid INTEGER NOT NULL,
    lid INTEGER NOT NULL,
    priority INTEGER NOT NULL DEFAULT 0,
    name TEXT NOT NULL,
    UNIQUE (cid, lid)
);

CREATE INDEX link_cid ON link (
    cid
);

CREATE TABLE page (
    id INTEGER PRIMARY KEY,
    cid INTEGER NOT NULL,
    priority INTEGER NOT NULL DEFAULT 0,
    mediadate VARCHAR(10) NOT NULL DEFAULT '',
    type INTEGER NOT NULL DEFAULT 0,
    url TEXT NOT NULL,
    title TEXT NOT NULL,
    descr TEXT NOT NULL
);

CREATE INDEX page_cid ON page (
    cid
);

CREATE TABLE pagelist (
    cid INTEGER PRIMARY KEY,
    data BLOB NOT NULL
);

CREATE INDEX pagelist_cid ON pagelist (
    cid
);

CREATE TABLE category_newsgroup (
    id INTEGER PRIMARY KEY,
    cid INTEGER NOT NULL,
    newsgroup TEXT NOT NULL,
    UNIQUE (cid, newsgroup)
);

CREATE INDEX category_newsgroup_cid ON category_newsgroup (
    cid
);

CREATE TABLE category_editor (
    id INTEGER PRIMARY KEY,
    cid INTEGER NOT NULL,
    editor TEXT NOT NULL,
    UNIQUE (cid, editor)
);

CREATE INDEX category_editor_cid ON category_editor (
    cid
);