DROP DATABASE IF EXISTS odeum;

CREATE DATABASE odeum;

USE odeum;

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS role;
DROP TABLE IF EXISTS user_role;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS post;

CREATE TABLE user (
    username varchar(40) NOT NULL,
    password varchar(40) NOT NULL,
    email varchar(255) NOT NULL,
    enabled char(1) NOT NULL DEFAULT 'N',
    creation_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (username)
) ENGINE=MyISAM;

CREATE TABLE role (
    name varchar(20) NOT NULL,
    description varchar(255),
    PRIMARY KEY (name)
) ENGINE=MyISAM;

CREATE TABLE user_role (
    username varchar(40) NOT NULL,
    role_name varchar(20) NOT NULL,
    PRIMARY KEY (username, role_name)
) ENGINE=MyISAM;

CREATE TABLE category (
    id int(8) NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    description varchar(255),
    post_count int(8),
    PRIMARY KEY (id)
) ENGINE=MyISAM;

CREATE TABLE post (
    id int(8) NOT NULL AUTO_INCREMENT,
    author varchar(50) NOT NULL,
    category_id int(8) NOT NULL,
    title varchar(255) NOT NULL,
    content text NOT NULL,
    summary varchar(255) NOT NULL,
    status varchar(8) NOT NULL,
    permalink varchar(255) NOT NULL,
    post_date datetime,
    update_date datetime,
    PRIMARY KEY (id)
) ENGINE=MyISAM;