DROP TABLE IF EXISTS dc_blog;
CREATE TABLE "dc_blog"
(
blog_id VARCHAR(32) NOT NULL ,
blog_uid VARCHAR(32) NOT NULL ,
blog_creadt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
blog_upddt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
blog_url VARCHAR(255) NOT NULL ,
blog_name VARCHAR(255) NOT NULL ,
blog_desc TEXT NULL ,
blog_status INTEGER NOT NULL DEFAULT 1,
CONSTRAINT pk_blog PRIMARY KEY (blog_id) 
);


DROP TABLE IF EXISTS dc_category;
CREATE TABLE "dc_category"
(
cat_id INTEGER NOT NULL ,
blog_id VARCHAR(32) NOT NULL ,
cat_title VARCHAR(255) NOT NULL ,
cat_url VARCHAR(255) NOT NULL ,
cat_desc TEXT NULL ,
cat_position INTEGER NULL DEFAULT 0 ,
cat_lft INTEGER NULL ,
cat_rgt INTEGER NULL ,
CONSTRAINT pk_category PRIMARY KEY (cat_id) 
CONSTRAINT uk_cat_url UNIQUE (cat_url,blog_id) 
);

DROP TABLE IF EXISTS dc_session;
CREATE TABLE "dc_session"
(
ses_id VARCHAR(40) NOT NULL ,
ses_time INTEGER NOT NULL DEFAULT 0 ,
ses_start INTEGER NOT NULL DEFAULT 0 ,
ses_value TEXT NOT NULL ,
CONSTRAINT pk_session PRIMARY KEY (ses_id) 
);

DROP TABLE IF EXISTS dc_setting;
CREATE TABLE "dc_setting"
(
setting_id VARCHAR(255) NOT NULL ,
blog_id VARCHAR(32) NULL ,
setting_ns VARCHAR(32) NOT NULL DEFAULT 'system' ,
setting_value TEXT NULL DEFAULT NULL,
setting_type VARCHAR(8) NOT NULL DEFAULT 'string' ,
setting_label TEXT NULL ,
CONSTRAINT uk_setting UNIQUE (setting_ns,setting_id,blog_id) 
);

DROP TABLE IF EXISTS dc_spamrule;
CREATE TABLE dc_spamrule (
rule_id integer NOT NULL ,
blog_id varchar(32) NULL ,
rule_type varchar(16) NOT NULL DEFAULT 'word' ,
rule_content varchar(128) NOT NULL ,
CONSTRAINT dc_pk_spamrule PRIMARY KEY (rule_id) 
);

DROP TABLE IF EXISTS dc_user;
CREATE TABLE "dc_user"
(
user_id VARCHAR(32) NOT NULL ,
user_super INTEGER NULL ,
user_status INTEGER NOT NULL DEFAULT 1 ,
user_pwd VARCHAR(40) NOT NULL ,
user_change_pwd INTEGER NOT NULL DEFAULT 0 ,
user_recover_key VARCHAR(32) NULL DEFAULT NULL,
user_name VARCHAR(255) NULL DEFAULT NULL,
user_firstname VARCHAR(255) NULL DEFAULT NULL,
user_displayname VARCHAR(255) NULL DEFAULT NULL,
user_email VARCHAR(255) NULL DEFAULT NULL,
user_url VARCHAR(255) NULL DEFAULT NULL,
user_desc TEXT NULL ,
user_default_blog VARCHAR(32) NULL DEFAULT NULL,
user_options TEXT NULL ,
user_lang VARCHAR(5) NULL DEFAULT NULL,
user_tz VARCHAR(128) NOT NULL DEFAULT 'UTC' ,
user_post_status INTEGER NOT NULL DEFAULT -2 ,
user_creadt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
user_upddt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
CONSTRAINT pk_user PRIMARY KEY (user_id)
);

DROP TABLE IF EXISTS dc_permissions;
CREATE TABLE "dc_permissions"
(
user_id VARCHAR(32) NOT NULL ,
blog_id VARCHAR(32) NOT NULL ,
permissions TEXT NULL ,
CONSTRAINT pk_permissions PRIMARY KEY (user_id,blog_id) 
);


DROP TABLE IF EXISTS dc_post;
CREATE TABLE "dc_post"
(
post_id INTEGER NOT NULL ,
blog_id VARCHAR(32) NOT NULL ,
user_id VARCHAR(32) NOT NULL ,
cat_id INTEGER NULL ,
post_dt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
post_tz VARCHAR(128) NOT NULL DEFAULT 'UTC' ,
post_creadt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
post_upddt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
post_password VARCHAR(32) NULL DEFAULT NULL,
post_type VARCHAR(32) NOT NULL DEFAULT 'post' ,
post_format VARCHAR(32) NOT NULL DEFAULT 'xhtml' ,
post_url VARCHAR(255) NOT NULL ,
post_lang VARCHAR(5) NULL DEFAULT NULL,
post_title VARCHAR(255) NULL DEFAULT NULL,
post_excerpt TEXT NULL DEFAULT NULL,
post_excerpt_xhtml TEXT NULL DEFAULT NULL,
post_content TEXT NULL DEFAULT NULL,
post_content_xhtml TEXT NOT NULL ,
post_notes TEXT NULL DEFAULT NULL,
post_meta TEXT NULL DEFAULT NULL,
post_words TEXT NULL DEFAULT NULL,
post_status INTEGER NOT NULL DEFAULT 0 ,
post_selected INTEGER NOT NULL DEFAULT 0 ,
post_position INTEGER NOT NULL DEFAULT 0 ,
post_open_comment INTEGER NOT NULL DEFAULT 0 ,
post_open_tb INTEGER NOT NULL DEFAULT 0 ,
nb_comment INTEGER NOT NULL DEFAULT 0 ,
nb_trackback INTEGER NOT NULL DEFAULT 0 ,
CONSTRAINT pk_post PRIMARY KEY (post_id) ,
CONSTRAINT uk_post_url UNIQUE (post_url,post_type,blog_id) 
);

DROP TABLE IF EXISTS dc_media;
CREATE TABLE "dc_media"
(
media_id INTEGER NOT NULL ,
user_id VARCHAR(32) NOT NULL ,
media_path VARCHAR(255) NOT NULL ,
media_title VARCHAR(255) NOT NULL ,
media_file VARCHAR(255) NOT NULL ,
media_dir VARCHAR(255) NOT NULL DEFAULT '.' ,
media_meta TEXT NULL DEFAULT NULL,
media_dt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
media_creadt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
media_upddt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
media_private INTEGER NOT NULL DEFAULT 0 ,
CONSTRAINT pk_media PRIMARY KEY (media_id) 
);

DROP TABLE IF EXISTS dc_post_media;
CREATE TABLE "dc_post_media"
(
media_id INTEGER NOT NULL ,
post_id INTEGER NOT NULL ,
link_type VARCHAR(32) NOT NULL DEFAULT 'attachment' ,
CONSTRAINT pk_post_media PRIMARY KEY (media_id,post_id,link_type) 
);

DROP TABLE IF EXISTS dc_pref;
CREATE TABLE dc_pref (
pref_id varchar(255) NOT NULL ,
user_id varchar(32) NULL ,
pref_ws varchar(32) NOT NULL DEFAULT 'system' ,
pref_value text NULL DEFAULT NULL,
pref_type varchar(8) NOT NULL DEFAULT 'string' ,
pref_label text NULL ,
CONSTRAINT dc_uk_pref UNIQUE (pref_ws,pref_id,user_id) 
);

DROP TABLE IF EXISTS dc_log;
CREATE TABLE "dc_log"
(
log_id INTEGER NOT NULL ,
user_id VARCHAR(32) NULL ,
blog_id VARCHAR(32) NULL ,
log_table VARCHAR(255) NOT NULL ,
log_dt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
log_ip VARCHAR(39) NOT NULL ,
log_msg TEXT NULL ,
CONSTRAINT pk_log PRIMARY KEY (log_id) 
);

DROP TABLE IF EXISTS dc_version;
CREATE TABLE "dc_version"
(
module VARCHAR(64) NOT NULL ,
version VARCHAR(32) NOT NULL ,
CONSTRAINT pk_version PRIMARY KEY (module) 
);

DROP TABLE IF EXISTS dc_ping;
CREATE TABLE "dc_ping"
(
post_id INTEGER NOT NULL ,
ping_url VARCHAR(255) NOT NULL ,
ping_dt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
CONSTRAINT pk_ping PRIMARY KEY (post_id,ping_url) 
);

DROP TABLE IF EXISTS dc_comment;
CREATE TABLE "dc_comment"
(
comment_id INTEGER NOT NULL ,
post_id INTEGER NOT NULL ,
comment_dt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
comment_tz VARCHAR(128) NOT NULL DEFAULT 'UTC' ,
comment_upddt TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00' ,
comment_author VARCHAR(255) NULL DEFAULT NULL,
comment_email VARCHAR(255) NULL DEFAULT NULL,
comment_site VARCHAR(255) NULL DEFAULT NULL,
comment_content TEXT NULL ,
comment_words TEXT NULL DEFAULT NULL,
comment_ip VARCHAR(39) NULL DEFAULT NULL,
comment_status INTEGER NULL DEFAULT 0 ,
comment_spam_status VARCHAR(128) NULL DEFAULT 0 ,
comment_spam_filter VARCHAR(32) NULL DEFAULT NULL,
comment_trackback INTEGER NOT NULL DEFAULT 0 ,
CONSTRAINT pk_comment PRIMARY KEY (comment_id) 
);

DROP TABLE IF EXISTS dc_meta;
CREATE TABLE "dc_meta"
(
meta_id VARCHAR(255) NOT NULL ,
meta_type VARCHAR(64) NOT NULL ,
post_id INTEGER NOT NULL ,
CONSTRAINT pk_meta PRIMARY KEY (meta_id,meta_type,post_id) 
);

CREATE INDEX idx_blog_blog_upddt ON dc_blog (blog_upddt) ;
CREATE INDEX idx_category_blog_id ON dc_category (blog_id) ; 
CREATE INDEX idx_category_cat_lft_blog_id ON dc_category (blog_id,cat_lft) ; 
CREATE INDEX idx_category_cat_rgt_blog_id ON dc_category (blog_id,cat_rgt) ; 
CREATE INDEX idx_setting_blog_id ON dc_setting (blog_id) ; 
CREATE INDEX idx_user_user_default_blog ON dc_user (user_default_blog) ; 
CREATE INDEX idx_user_user_super ON dc_user (user_super) ; 
CREATE INDEX idx_permissions_blog_id ON dc_permissions (blog_id) ; 
CREATE INDEX idx_post_cat_id ON dc_post (cat_id) ; 
CREATE INDEX idx_post_user_id ON dc_post (user_id) ; 
CREATE INDEX idx_post_blog_id ON dc_post (blog_id) ; 
CREATE INDEX idx_post_post_dt ON dc_post (post_dt) ; 
CREATE INDEX idx_post_post_dt_post_id ON dc_post (post_dt,post_id) ; 
CREATE INDEX idx_blog_post_post_dt_post_id ON dc_post (blog_id,post_dt,post_id) ; 
CREATE INDEX idx_blog_post_post_status ON dc_post (blog_id,post_status) ; 
CREATE INDEX idx_media_user_id ON dc_media (user_id) ; 
CREATE INDEX idx_post_media_post_id ON dc_post_media (post_id) ; 
CREATE INDEX idx_log_user_id ON dc_log (user_id) ; 
CREATE INDEX idx_comment_post_id ON dc_comment (post_id) ; 
CREATE INDEX idx_comment_post_id_dt_status ON dc_comment (post_id,comment_dt,comment_status) ; 
CREATE INDEX idx_meta_post_id ON dc_meta (post_id) ; 
CREATE INDEX idx_meta_meta_type ON dc_meta (meta_type) ; 
