--
-- SQL model description for MySQL 5.0.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
) ENGINE=InnoDB CHARSET=UTF8;

-- News item category.

CREATE TABLE news_categories
(
    news_category_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    -- Category name.
    name TEXT NOT NULL,
    -- Category description.
    description TEXT NOT NULL
) ENGINE=InnoDB CHARSET=UTF8;

-- Site news item.

CREATE TABLE news_items
(
    news_item_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    -- 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
) ENGINE=InnoDB CHARSET=UTF8;

-- Newsletter subscriber.

CREATE TABLE news_subscribers
(
    news_subscriber_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    -- 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
) ENGINE=InnoDB CHARSET=UTF8;

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

CREATE INDEX index_news_items_1
    ON news_items (category_news_category_id);

CREATE UNIQUE INDEX index_categories_news_subscriber_news_category_2
    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;

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

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

COMMIT;