--
-- SQL model description for PostgreSQL 8.1.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 SERIAL PRIMARY KEY,
    -- Category name.
    name TEXT NOT NULL,
    -- Category description.
    description TEXT NOT NULL
);

-- Site news item.

CREATE TABLE news_items
(
    news_item_id SERIAL PRIMARY KEY,
    -- 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 SERIAL PRIMARY KEY,
    -- 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);

--------------------------------------------------------------------
-- Foreign keys                                                   --
--------------------------------------------------------------------

ALTER TABLE categories_news_subscriber_news_category
    ADD FOREIGN KEY (news_subscriber_id)
        REFERENCES news_subscribers (news_subscriber_id)
        ON DELETE CASCADE
        DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE categories_news_subscriber_news_category
    ADD FOREIGN KEY (news_category_id)
        REFERENCES news_categories (news_category_id)
        ON DELETE CASCADE
        DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE news_items
    ADD FOREIGN KEY (category_news_category_id)
        REFERENCES news_categories (news_category_id)
        ON DELETE CASCADE
        DEFERRABLE INITIALLY DEFERRED;

--------------------------------------------------------------------
-- Comments                                                       --
--------------------------------------------------------------------

COMMENT ON TABLE categories_news_subscriber_news_category IS 'relation N:M news_subscriber.categories --> news_category';
COMMENT ON TABLE news_categories IS 'News item category.';
COMMENT ON COLUMN news_categories.name IS 'Category name.';
COMMENT ON COLUMN news_categories.description IS 'Category description.';
COMMENT ON TABLE news_items IS 'Site news item.';
COMMENT ON COLUMN news_items.title IS 'Mail subject, rss feed title, ...';
COMMENT ON COLUMN news_items.content IS 'Repeats info from the title with more detail.';
COMMENT ON COLUMN news_items.category_news_category_id IS 'relation N:1 news_item.category --> news_category';
COMMENT ON COLUMN news_items._insert_time IS 'row insertion timestamp';
COMMENT ON COLUMN news_items._update_time IS 'row update timestamp';
COMMENT ON TABLE news_subscribers IS 'Newsletter subscriber.';
COMMENT ON COLUMN news_subscribers.email IS 'Subscriber''s e-mail.';
COMMENT ON COLUMN news_subscribers.realname IS 'Subscriber''s real name.';
COMMENT ON COLUMN news_subscribers._insert_time IS 'row insertion timestamp';
COMMENT ON COLUMN news_subscribers._update_time IS 'row update timestamp';

COMMIT;