--
-- SQL model description for SQLite 3.2.x
--
-- Model:   Newsletter
-- Author:  Ondrej Jirman
-- Date:    2008-11-11 07:54
--
-- Generated by GENiE 0.3 (build date: 2008-09-06 10:47) (http://megous.com)
--

BEGIN;

--------------------------------------------------------------------
-- Tables                                                         --
--------------------------------------------------------------------

-- relation N:M news_subscriber.categories --> news_category

CREATE TABLE categories_news_subscriber_news_category
(
    news_subscriber_id INTEGER NOT NULL,
    news_category_id INTEGER NOT NULL
);

-- News item category.

CREATE TABLE news_categories
(
    news_category_id INTEGER PRIMARY KEY AUTOINCREMENT,
    -- Category name.
    name TEXT NOT NULL,
    -- Category description.
    description TEXT NOT NULL
);

-- Site news item.

CREATE TABLE news_items
(
    news_item_id INTEGER PRIMARY KEY AUTOINCREMENT,
    -- Mail subject, rss feed title, ...
    title TEXT NOT NULL,
    -- Repeats info from the title with more detail.
    content TEXT NOT NULL,
    -- relation N:1 news_item.category --> news_category
    category_news_category_id INTEGER,
    -- row insertion timestamp
    _insert_time TIMESTAMP DEFAULT current_timestamp NOT NULL,
    -- row update timestamp
    _update_time TIMESTAMP
);

-- Newsletter subscriber.

CREATE TABLE news_subscribers
(
    news_subscriber_id INTEGER PRIMARY KEY AUTOINCREMENT,
    -- Subscriber's e-mail.
    email TEXT NOT NULL,
    -- Subscriber's real name.
    realname TEXT,
    -- row insertion timestamp
    _insert_time TIMESTAMP DEFAULT current_timestamp NOT NULL,
    -- row update timestamp
    _update_time TIMESTAMP
);

--------------------------------------------------------------------
-- Indices                                                        --
--------------------------------------------------------------------

CREATE INDEX news_items_category_news_category_id_idx
    ON news_items (category_news_category_id);

CREATE UNIQUE INDEX categories_news_subscriber_news_category_news_subscriber_id_news_category_id_idx
    ON categories_news_subscriber_news_category (news_subscriber_id, news_category_id);

COMMIT;