-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.38-Ubuntu_0ubuntu1-log


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema zfblog
--

CREATE DATABASE IF NOT EXISTS zfblog;
USE zfblog;

CREATE TABLE  `zfblog`.`blog` (
  `blog_id` int(11) NOT NULL auto_increment,
  `blog_uid` varchar(32) collate utf8_bin NOT NULL,
  `blog_created` datetime NOT NULL default '1970-01-01 00:00:00',
  `blog_updated` datetime NOT NULL default '1970-01-01 00:00:00',
  `blog_url` varchar(255) collate utf8_bin NOT NULL,
  `blog_name` varchar(255) collate utf8_bin NOT NULL,
  `blog_description` longtext collate utf8_bin,
  `blog_status` smallint(6) NOT NULL default '1',
  PRIMARY KEY  (`blog_id`),
  KEY `idx_blog_blog_updated` USING BTREE (`blog_updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `zfblog`.`blog` VALUES  (4,1,'2007-09-01 17:17:37','2007-09-01 17:17:37','My-Fird-Blog','My Fird Blog','Description 1',1);
CREATE TABLE  `zfblog`.`category` (
  `category_id` bigint(20) NOT NULL,
  `blog_id` int(11) NOT NULL,
  `category_title` varchar(255) collate utf8_bin NOT NULL,
  `category_url` varchar(255) collate utf8_bin NOT NULL,
  `category_desc` longtext collate utf8_bin,
  `category_position` int(11) default '0',
  PRIMARY KEY  (`category_id`),
  UNIQUE KEY `uk_category_title` (`category_title`,`blog_id`),
  UNIQUE KEY `uk_category_url` (`category_url`,`blog_id`),
  KEY `idx_category_blog_id` USING BTREE (`blog_id`),
  CONSTRAINT `fk_category_blog` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`blog_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`comment` (
  `comment_id` bigint(20) NOT NULL,
  `post_id` bigint(20) NOT NULL,
  `comment_created` datetime NOT NULL default '1970-01-01 00:00:00',
  `comment_updated` datetime NOT NULL default '1970-01-01 00:00:00',
  `comment_author` varchar(255) collate utf8_bin default NULL,
  `comment_email` varchar(255) collate utf8_bin default NULL,
  `comment_site` varchar(255) collate utf8_bin default NULL,
  `comment_content` longtext collate utf8_bin,
  `comment_words` longtext collate utf8_bin,
  `comment_ip` varchar(39) collate utf8_bin default NULL,
  `comment_status` smallint(6) default '0',
  `comment_spam_status` varchar(128) collate utf8_bin default '0',
  `comment_spam_filter` varchar(32) collate utf8_bin default NULL,
  `comment_trackback` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`comment_id`),
  KEY `idx_comment_post_id` USING BTREE (`post_id`),
  CONSTRAINT `fk_comment_post` FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`log` (
  `log_id` bigint(20) NOT NULL,
  `user_id` int(11) default NULL,
  `log_table` varchar(255) collate utf8_bin NOT NULL,
  `log_created` datetime NOT NULL default '1970-01-01 00:00:00',
  `log_ip` varchar(39) collate utf8_bin NOT NULL,
  `log_msg` varchar(255) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`log_id`),
  KEY `idx_log_user_id` USING BTREE (`user_id`),
  CONSTRAINT `fk_log_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`media` (
  `media_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `media_path` varchar(255) collate utf8_bin NOT NULL,
  `media_title` varchar(255) collate utf8_bin NOT NULL,
  `media_file` varchar(255) collate utf8_bin NOT NULL,
  `media_dir` varchar(255) collate utf8_bin NOT NULL default '.',
  `media_meta` longtext collate utf8_bin,
  `media_dt` datetime NOT NULL default '1970-01-01 00:00:00',
  `media_created` datetime NOT NULL default '1970-01-01 00:00:00',
  `media_updated` datetime NOT NULL default '1970-01-01 00:00:00',
  `media_private` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`media_id`),
  KEY `idx_media_user_id` USING BTREE (`user_id`),
  CONSTRAINT `fk_media_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`permissions` (
  `user_id` int(11) NOT NULL,
  `blog_id` int(11) NOT NULL,
  `permissions` longtext collate utf8_bin,
  PRIMARY KEY  (`user_id`,`blog_id`),
  KEY `idx_permissions_blog_id` USING BTREE (`blog_id`),
  CONSTRAINT `fk_permissions_blog` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`blog_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_permissions_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`ping` (
  `post_id` bigint(20) NOT NULL,
  `ping_url` varchar(255) collate utf8_bin NOT NULL,
  `ping_dt` datetime NOT NULL default '1970-01-01 00:00:00',
  PRIMARY KEY  (`post_id`,`ping_url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`post` (
  `post_id` bigint(20) NOT NULL,
  `blog_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `category_id` bigint(20) default NULL,
  `post_created` datetime NOT NULL default '1970-01-01 00:00:00',
  `post_updated` datetime NOT NULL default '1970-01-01 00:00:00',
  `post_password` varchar(32) collate utf8_bin default NULL,
  `post_type` varchar(32) collate utf8_bin NOT NULL default 'post',
  `post_format` varchar(32) collate utf8_bin NOT NULL default 'xhtml',
  `post_url` varchar(255) collate utf8_bin NOT NULL,
  `post_lang` varchar(5) collate utf8_bin default NULL,
  `post_title` varchar(255) collate utf8_bin default NULL,
  `post_excerpt` longtext collate utf8_bin,
  `post_excerpt_xhtml` longtext collate utf8_bin,
  `post_content` longtext collate utf8_bin,
  `post_content_xhtml` longtext collate utf8_bin NOT NULL,
  `post_notes` longtext collate utf8_bin,
  `post_words` longtext collate utf8_bin,
  `post_status` smallint(6) NOT NULL default '0',
  `post_selected` smallint(6) NOT NULL default '0',
  `post_open_comment` smallint(6) NOT NULL default '0',
  `post_open_tb` smallint(6) NOT NULL default '0',
  `nb_comment` int(11) NOT NULL default '0',
  `nb_trackback` int(11) NOT NULL default '0',
  PRIMARY KEY  (`post_id`),
  UNIQUE KEY `uk_post_url` (`post_url`,`post_type`,`blog_id`),
  KEY `idx_post_category_id` USING BTREE (`category_id`),
  KEY `idx_post_user_id` USING BTREE (`user_id`),
  KEY `idx_post_blog_id` USING BTREE (`blog_id`),
  KEY `idx_post_post_dt_post_id` USING BTREE (`post_id`),
  KEY `idx_blog_post_post_dt_post_id` USING BTREE (`blog_id`,`post_id`),
  CONSTRAINT `fk_post_blog` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`blog_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_post_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_post_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`post_media` (
  `media_id` bigint(20) NOT NULL,
  `post_id` bigint(20) NOT NULL,
  PRIMARY KEY  (`media_id`,`post_id`),
  KEY `idx_post_media_post_id` USING BTREE (`post_id`),
  CONSTRAINT `fk_media` FOREIGN KEY (`media_id`) REFERENCES `media` (`media_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_post` FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`session` (
  `ses_id` varchar(40) collate utf8_bin NOT NULL,
  `ses_time` int(11) NOT NULL default '0',
  `ses_start` int(11) NOT NULL default '0',
  `ses_value` longtext collate utf8_bin NOT NULL,
  PRIMARY KEY  (`ses_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`setting` (
  `setting_id` varchar(255) collate utf8_bin NOT NULL,
  `blog_id` int(11) default NULL,
  `setting_ns` varchar(32) collate utf8_bin NOT NULL default 'system',
  `setting_value` longtext collate utf8_bin,
  `setting_type` varchar(8) collate utf8_bin NOT NULL default 'string',
  `setting_label` longtext collate utf8_bin,
  UNIQUE KEY `uk_setting` (`setting_id`,`blog_id`),
  KEY `idx_setting_blog_id` USING BTREE (`blog_id`),
  CONSTRAINT `fk_setting_blog` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`blog_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`user` (
  `user_id` int(11) NOT NULL,
  `user_super` smallint(6) default NULL,
  `user_status` smallint(6) NOT NULL default '1',
  `user_pwd` varchar(40) collate utf8_bin NOT NULL,
  `user_recover_key` varchar(32) collate utf8_bin default NULL,
  `user_name` varchar(255) collate utf8_bin default NULL,
  `user_firstname` varchar(255) collate utf8_bin default NULL,
  `user_displayname` varchar(255) collate utf8_bin default NULL,
  `user_email` varchar(255) collate utf8_bin default NULL,
  `user_url` varchar(255) collate utf8_bin default NULL,
  `user_description` longtext collate utf8_bin,
  `user_default_blog` int(11) default NULL,
  `user_options` longtext collate utf8_bin,
  `user_lang` varchar(5) collate utf8_bin default NULL,
  `user_tz` varchar(128) collate utf8_bin NOT NULL default 'UTC',
  `user_post_status` smallint(6) NOT NULL default '-2',
  `user_created` datetime NOT NULL default '1970-01-01 00:00:00',
  `user_updated` datetime NOT NULL default '1970-01-01 00:00:00',
  PRIMARY KEY  (`user_id`),
  KEY `idx_user_user_default_blog` USING BTREE (`user_default_blog`),
  KEY `idx_user_user_super` USING BTREE (`user_super`),
  CONSTRAINT `fk_user_default_blog` FOREIGN KEY (`user_default_blog`) REFERENCES `blog` (`blog_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE  `zfblog`.`version` (
  `module` varchar(64) collate utf8_bin NOT NULL,
  `version` varchar(32) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;