-- -----------------------------------------------------
-- table `permission`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `permission` ;

CREATE  TABLE IF NOT EXISTS `permission` (
  `id` tinyint NOT NULL ,
  `name` VARCHAR(50) NOT NULL ,
  `detail` VARCHAR(255) null ,
  `update_date` TIMESTAMP NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = MYISAM
COMMENT = 'system permission master data'
row_format = default;


-- -----------------------------------------------------
-- data for table `permission`
-- -----------------------------------------------------
set autocommit=0;
insert into `permission` (`id`, `name`, `detail`, `update_date`) values (0, 'permission', 'the default permission in system.', CURRENT_TIMESTAMP);
insert into `permission` (`id`, `name`, `detail`, `update_date`) values (-1, 'no permission', 'the default of no access permission in system.', CURRENT_TIMESTAMP);
insert into `permission` (`id`, `name`, `detail`, `update_date`) values (10, 'member permission', 'the default regist members permission in system.', CURRENT_TIMESTAMP);
insert into `permission` (`id`, `name`, `detail`, `update_date`) values (100, 'administrator permission', 'the default administrator members permission in system.', CURRENT_TIMESTAMP);

commit;

-- -----------------------------------------------------
-- table `administrator`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `administrator` ;

CREATE  TABLE IF NOT EXISTS `administrator` (
  `id` int unsigned NOT NULL auto_increment ,
  `name` VARCHAR(20) NOT NULL COMMENT 'login name' ,
  `passwd` VARCHAR(32) NOT NULL ,
  `permission` tinyint NOT NULL default 0 ,
  `updator` int unsigned null ,
  `update_date` TIMESTAMP NOT NULL ,
  `creator` int unsigned null ,
  `create_date` TIMESTAMP null ,
  `front_id` int unsigned null ,
  `group_id` int unsigned NOT NULL default 0 ,
  `delete_flg` char(1) NOT NULL default '0' COMMENT '0:false(life),1:true(die)' ,
  PRIMARY KEY (`id`, `name`) ,
  index main (`id` ASC, `name` ASC, `front_id` ASC, `creator` ASC, `updator` ASC) ,
  index fk_to_permission (`permission` ASC) ,
  unique index name_unq (`name` ASC) ,
  constraint `fk_to_permission`
    foreign key (`permission` )
    references `permission` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
COMMENT = 'administrator data'
row_format = default;


-- -----------------------------------------------------
-- table `model_mst`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `model_mst` ;

CREATE  TABLE IF NOT EXISTS `model_mst` (
  `id` tinyint unsigned NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `detail` TEXT null ,
  PRIMARY KEY (`id`) )
ENGINE = MYISAM
COMMENT = 'member/contents(category) access area\'s model.example:jp/cn';


-- -----------------------------------------------------
-- data for table `model_mst`
-- -----------------------------------------------------
set autocommit=0;
insert into `model_mst` (`id`, `name`, `detail`) values (0, 'default', 'default model for member/contents(category) default values');

commit;

-- -----------------------------------------------------
-- table `member`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `member` ;

CREATE  TABLE IF NOT EXISTS `member` (
  `id` int unsigned NOT NULL auto_increment ,
  `mail` VARCHAR(250) NOT NULL COMMENT 'login id' ,
  `unic_key` VARCHAR(255) NOT NULL COMMENT 'be encrypted string by <mail>$<passwd>' ,
  `nick_name` VARCHAR(45) null COMMENT 'this name can be use 2byte character code' ,
  `passwd` VARCHAR(32) NOT NULL ,
  `msn` VARCHAR(250) null ,
  `qq_no` VARCHAR(30) null ,
  `permission` tinyint NOT NULL default 0 ,
  `login_date` TIMESTAMP null ,
  `register_date` TIMESTAMP null ,
  `update_date` TIMESTAMP NOT NULL ,
  `updator` int null ,
  `delete_flg` char(1) NOT NULL default '0' COMMENT '0:false(life),1:true(die)' ,
  `creator_id` int null ,
  `model_id` tinyint unsigned NOT NULL default 0 ,
  PRIMARY KEY (`id`, `mail`) ,
  index fk_to_permission (`permission` ASC) ,
  index main (`id` ASC, `nick_name` ASC, `mail` ASC, `permission` ASC, `delete_flg` ASC, `model_id` ASC) ,
  unique index nick_name_uniq (`nick_name` ASC) ,
  index fk_to_model_mst (`model_id` ASC) ,
  constraint `fk_member_administrator`
    foreign key (`updator` , `creator_id` )
    references `administrator` (`id` , `id` )
    on delete no action
    on update no action,
  constraint `fk_to_permission`
    foreign key (`permission` )
    references `permission` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_model_mst`
    foreign key (`model_id` )
    references `model_mst` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
COMMENT = 'member infometion';


-- -----------------------------------------------------
-- table `admingstrator_group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `admingstrator_group` ;

CREATE  TABLE IF NOT EXISTS `admingstrator_group` (
  `id` int unsigned NOT NULL auto_increment ,
  `name` VARCHAR(40) NOT NULL ,
  `detail` VARCHAR(255) null ,
  `creator` int NOT NULL default 0 ,
  `create_date` TIMESTAMP null ,
  `updator` int unsigned null ,
  `update_date` TIMESTAMP NOT NULL ,
  `permission` tinyint NOT NULL default 0 ,
  `delete_flg` char(1) NOT NULL default '0' COMMENT '0:false(life),1:true(die)' ,
  PRIMARY KEY (`id`) ,
  index main (`id` ASC, `name` ASC, `updator` ASC, `creator` ASC) ,
  index fk_to_permission (`permission` ASC) ,
  constraint `fk_to_permission`
    foreign key (`permission` )
    references `permission` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
row_format = default;


-- -----------------------------------------------------
-- table `service_mst`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `service_mst` ;

CREATE  TABLE IF NOT EXISTS `service_mst` (
  `id` int unsigned NOT NULL auto_increment ,
  `name` VARCHAR(50) NOT NULL ,
  `passwd` VARCHAR(50) null ,
  `detail` VARCHAR(255) null ,
  `mail` VARCHAR(250) null ,
  `attribute1` VARCHAR(255) null ,
  `attribute_name1` VARCHAR(50) null ,
  `attribute2` VARCHAR(255) null ,
  `attribute_name2` VARCHAR(50) null ,
  `attribute3` VARCHAR(255) null ,
  `attribute_name3` VARCHAR(50) null ,
  `update_date` TIMESTAMP NOT NULL ,
  `updator` int NOT NULL ,
  `delete_flg` char(1) NOT NULL default '0' COMMENT '0:false(life),1:true(die)' ,
  `owner` int unsigned NOT NULL ,
  `order` tinyint NOT NULL default 0 ,
  PRIMARY KEY (`id`, `name`, `owner`) ,
  index fk_to_member (`owner` ASC, `updator` ASC) ,
  index main (`id` ASC, `name` ASC, `owner` ASC, `updator` ASC) ,
  constraint `fk_to_member`
    foreign key (`owner` , `updator` )
    references `member` (`id` , `id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `template`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `template` ;

CREATE  TABLE IF NOT EXISTS `template` (
  `id` int unsigned NOT NULL auto_increment ,
  `order` tinyint NOT NULL default 0 ,
  `name` VARCHAR(100) NOT NULL ,
  `body` TEXT NOT NULL ,
  `delete_flg` char(1) NOT NULL default '0' ,
  `updator` int unsigned NOT NULL ,
  `update_date` TIMESTAMP NOT NULL ,
  `permission` tinyint NOT NULL default 0 ,
  `type` char(1) NOT NULL default 0 COMMENT 'template type.0:all,1:category,2:contents,3:keywords' ,
  PRIMARY KEY (`id`) ,
  index fk_to_administrator (`updator` ASC) ,
  index main (`id` ASC, `name` ASC, `type` ASC, `order` ASC, `delete_flg` ASC, `permission` ASC) ,
  index fk_to_permission (`permission` ASC) ,
  constraint `fk_to_administrator`
    foreign key (`updator` )
    references `administrator` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_permission`
    foreign key (`permission` )
    references `permission` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `contents`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `contents` ;

CREATE  TABLE IF NOT EXISTS `contents` (
  `id` int unsigned NOT NULL auto_increment ,
  `title` VARCHAR(100) NOT NULL ,
  `access_key` VARCHAR(100) NOT NULL ,
  `owner` int NOT NULL ,
  `delete_flg` char(1) NOT NULL default '0' COMMENT '0:false(life),1:true(die)' ,
  `permission` tinyint NOT NULL default 0 ,
  `template_id` int unsigned null default null ,
  `model_id` tinyint unsigned NOT NULL default 0 ,
  `per_contents_id` int unsigned null ,
  PRIMARY KEY (`id`) ,
  index fk_to_member (`owner` ASC) ,
  index main (`id` ASC, `access_key` ASC, `title` ASC, `owner` ASC, `permission` ASC, `model_id` ASC) ,
  index fk_to_permission (`permission` ASC) ,
  index fk_to_template (`template_id` ASC) ,
  index fk_to_model_mst (`model_id` ASC) ,
  constraint `fk_to_member`
    foreign key (`owner` )
    references `member` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_permission`
    foreign key (`permission` )
    references `permission` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_template`
    foreign key (`template_id` )
    references `template` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_model_mst`
    foreign key (`model_id` )
    references `model_mst` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `contents_body`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `contents_body` ;

CREATE  TABLE IF NOT EXISTS `contents_body` (
  `id` int unsigned NOT NULL auto_increment ,
  `contents_id` int unsigned NOT NULL ,
  `body` TEXT NOT NULL ,
  `update_date` TIMESTAMP NOT NULL ,
  `pager` tinyint NOT NULL default 0 ,
  `delete_flg` char(1) NOT NULL default '0' COMMENT '0:false(life),1:true(die)' ,
  `tags` VARCHAR(200) null COMMENT 'contents tags/keywords punctuate a comma and can use tags data for meta for html/xhtml' ,
  PRIMARY KEY (`id`, `contents_id`) ,
  index fk_to_contents (`contents_id` ASC) ,
  index main (`id` ASC, `contents_id` ASC) ,
  constraint `fk_to_contents`
    foreign key (`contents_id` )
    references `contents` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `category_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `category_type` ;

CREATE  TABLE IF NOT EXISTS `category_type` (
  `id` int unsigned NOT NULL auto_increment ,
  `order` tinyint NOT NULL default 0 ,
  `name` VARCHAR(100) NOT NULL ,
  `detail` VARCHAR(255) null ,
  `updator` int unsigned NOT NULL ,
  `update_date` TIMESTAMP NOT NULL ,
  PRIMARY KEY (`id`) ,
  index main (`id` ASC, `order` ASC, `name` ASC) )
ENGINE = MYISAM
COMMENT = 'category type, example:forum/news/blog etc.'
row_format = default;


-- -----------------------------------------------------
-- data for table `category_type`
-- -----------------------------------------------------
set autocommit=0;

commit;

-- -----------------------------------------------------
-- table `category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `category` ;

CREATE  TABLE IF NOT EXISTS `category` (
  `id` int unsigned NOT NULL auto_increment ,
  `title` VARCHAR(100) NOT NULL ,
  `detail` VARCHAR(255) null ,
  `default_path` VARCHAR(100) NOT NULL default '0' ,
  `delete_flg` char(1) NOT NULL default '0' COMMENT '0:false(life),1:true(die)' ,
  `type_id` int unsigned NOT NULL default 0 ,
  `template_id` int unsigned NOT NULL default 0 ,
  `owner` int unsigned NOT NULL ,
  `updator` int null ,
  `update_date` TIMESTAMP null ,
  `permission` tinyint NOT NULL default 0 ,
  `model_id` tinyint unsigned NOT NULL default 0 ,
  PRIMARY KEY (`id`, `owner`) ,
  index fk_to_member (`owner` ASC, `updator` ASC) ,
  index main (`id` ASC, `title` ASC, `owner` ASC, `model_id` ASC) ,
  index fk_to_permission (`permission` ASC) ,
  index fk_to_template (`template_id` ASC) ,
  index fk_to_model_mst (`model_id` ASC) ,
  index fk_to_category_type (`type_id` ASC) ,
  constraint `fk_to_member`
    foreign key (`owner` , `updator` )
    references `member` (`id` , `id` )
    on delete no action
    on update no action,
  constraint `fk_to_permission`
    foreign key (`permission` )
    references `permission` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_template`
    foreign key (`template_id` )
    references `template` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_model_mst`
    foreign key (`model_id` )
    references `model_mst` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_category_type`
    foreign key (`type_id` )
    references `category_type` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
row_format = default;


-- -----------------------------------------------------
-- table `category_contents_mapping`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `category_contents_mapping` ;

CREATE  TABLE IF NOT EXISTS `category_contents_mapping` (
  `id` int unsigned NOT NULL auto_increment ,
  `contents_id` int unsigned NOT NULL ,
  `category_id` int unsigned NOT NULL ,
  `update` TIMESTAMP NOT NULL ,
  `order` tinyint unsigned NOT NULL default 0 ,
  index fk_to_category (`category_id` ASC) ,
  index fk_to_contents (`contents_id` ASC) ,
  index main (`id` ASC, `category_id` ASC, `contents_id` ASC, `order` ASC) ,
  unique index unique_order (`order` ASC) ,
  PRIMARY KEY (`id`) ,
  constraint `fk_to_category`
    foreign key (`category_id` )
    references `category` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_contents`
    foreign key (`contents_id` )
    references `contents` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
row_format = default;


-- -----------------------------------------------------
-- table `category_mapping`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `category_mapping` ;

CREATE  TABLE IF NOT EXISTS `category_mapping` (
  `id` int unsigned NOT NULL auto_increment ,
  `super_id` int unsigned NOT NULL ,
  `sub_id` int unsigned NOT NULL ,
  PRIMARY KEY (`id`) ,
  index fk_to_targets (`super_id` ASC, `sub_id` ASC) ,
  index main (`id` ASC, `super_id` ASC, `sub_id` ASC) ,
  constraint `fk_to_targets`
    foreign key (`super_id` , `sub_id` )
    references `category` (`id` , `id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
row_format = default;


-- -----------------------------------------------------
-- table `html_tag_mst`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `html_tag_mst` ;

CREATE  TABLE IF NOT EXISTS `html_tag_mst` (
  `id` smallint unsigned NOT NULL auto_increment ,
  `name` VARCHAR(20) NOT NULL ,
  `updator` int unsigned NOT NULL ,
  `update_date` TIMESTAMP NOT NULL ,
  `status` tinyint NOT NULL default 0 ,
  `detail` VARCHAR(255) null ,
  `options` VARCHAR(200) null ,
  PRIMARY KEY (`id`) ,
  unique index uniqe_name (`name` ASC) ,
  index fk_to_adminsitrator (`updator` ASC) ,
  index main (`name` ASC, `status` ASC, `id` ASC) ,
  constraint `fk_to_adminsitrator`
    foreign key (`updator` )
    references `administrator` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
row_format = default;


-- -----------------------------------------------------
-- table `custom_tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `custom_tag` ;

CREATE  TABLE IF NOT EXISTS `custom_tag` (
  `id` int unsigned NOT NULL auto_increment ,
  `name` VARCHAR(200) NOT NULL ,
  `detail` VARCHAR(255) NOT NULL ,
  `node` VARCHAR(200) NOT NULL ,
  `script` TEXT NOT NULL ,
  `delete_flg` char(1) NOT NULL default '0' COMMENT '0:false(life),1:true(die)' ,
  `updator` int unsigned null ,
  `update` TIMESTAMP null ,
  `permission` tinyint NOT NULL default 0 ,
  PRIMARY KEY (`id`) ,
  index fk_to_administrator (`updator` ASC) ,
  index main (`id` ASC, `node` ASC) ,
  index fk_to_permission (`permission` ASC) ,
  constraint `fk_to_administrator`
    foreign key (`updator` )
    references `administrator` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_permission`
    foreign key (`permission` )
    references `permission` (`update_date` )
    on delete no action
    on update no action)
ENGINE = MYISAM
row_format = default;


-- -----------------------------------------------------
-- table `file_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `file_type` ;

CREATE  TABLE IF NOT EXISTS `file_type` (
  `id` smallint unsigned NOT NULL auto_increment ,
  `extension` VARCHAR(10) NOT NULL ,
  `detail` VARCHAR(255) null ,
  `delete_flg` char(1) NOT NULL default '0' ,
  `updator` int unsigned null ,
  `update_date` TIMESTAMP null ,
  PRIMARY KEY (`id`) ,
  index fk_to_administrator (`updator` ASC) ,
  index main (`id` ASC, `extension` ASC) ,
  constraint `fk_to_administrator`
    foreign key (`updator` )
    references `administrator` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `file`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `file` ;

CREATE  TABLE IF NOT EXISTS `file` (
  `id` int unsigned NOT NULL auto_increment ,
  `name` VARCHAR(200) NOT NULL ,
  `detail` VARCHAR(255) null ,
  `delete_flg` char(1) NOT NULL default '0' ,
  `owner` int unsigned NOT NULL ,
  `updator` int unsigned NOT NULL ,
  `update_date` TIMESTAMP NOT NULL ,
  `path` VARCHAR(255) NOT NULL ,
  `type_id` smallint unsigned NOT NULL ,
  `contents_id` int unsigned NOT NULL ,
  `service_id` int unsigned NOT NULL ,
  `permission` tinyint NOT NULL default 0 ,
  PRIMARY KEY (`id`) ,
  index fk_to_contents (`contents_id` ASC) ,
  index fk_to_owner (`updator` ASC, `owner` ASC) ,
  index main (`id` ASC, `name` ASC, `contents_id` ASC) ,
  index fk_to_file_type (`type_id` ASC) ,
  index fk_to_service (`service_id` ASC) ,
  index fk_to_permission (`permission` ASC) ,
  constraint `fk_to_contents`
    foreign key (`contents_id` )
    references `contents` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_owner`
    foreign key (`updator` , `owner` )
    references `member` (`id` , `id` )
    on delete no action
    on update no action,
  constraint `fk_to_file_type`
    foreign key (`type_id` )
    references `file_type` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_service`
    foreign key (`service_id` )
    references `service_mst` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_permission`
    foreign key (`permission` )
    references `permission` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `member_group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `member_group` ;

CREATE  TABLE IF NOT EXISTS `member_group` (
  `id` int unsigned NOT NULL auto_increment ,
  `name` VARCHAR(40) NOT NULL ,
  `detail` VARCHAR(255) null ,
  `updator` int unsigned NOT NULL ,
  `update_date` TIMESTAMP NOT NULL ,
  `permission` tinyint unsigned NOT NULL ,
  `delete_flg` char(1) NOT NULL default '0' ,
  PRIMARY KEY (`id`) ,
  index maing (`id` ASC, `name` ASC) ,
  index fk_to_permission (`permission` ASC) ,
  constraint `fk_to_permission`
    foreign key (`permission` )
    references `permission` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `member_group_mapping`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `member_group_mapping` ;

CREATE  TABLE IF NOT EXISTS `member_group_mapping` (
  `member_id` int unsigned NOT NULL ,
  `group_id` int unsigned NOT NULL ,
  PRIMARY KEY (`member_id`) ,
  index fk_to_member (`member_id` ASC) ,
  index fk_to_member_group (`group_id` ASC) ,
  index main (`member_id` ASC, `group_id` ASC) ,
  constraint `fk_to_member`
    foreign key (`member_id` )
    references `member` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_member_group`
    foreign key (`group_id` )
    references `member_group` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `plugin_registory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `plugin_registory` ;

CREATE  TABLE IF NOT EXISTS `plugin_registory` (
  `id` int unsigned NOT NULL auto_increment ,
  `name` VARCHAR(50) NOT NULL ,
  `title` VARCHAR(100) NOT NULL ,
  `detail` VARCHAR(255) NOT NULL ,
  `updator` int unsigned NOT NULL ,
  `update_date` TIMESTAMP NOT NULL ,
  `owner` int unsigned NOT NULL ,
  `status` char(1) NOT NULL COMMENT '0:life,1:died,2:wait release' ,
  `permission` tinyint NOT NULL default 0 ,
  `version` double(5,3) unsigned NOT NULL default 0.0 ,
  PRIMARY KEY (`id`, `name`) ,
  index fk_to_administrator (`updator` ASC, `owner` ASC) ,
  index fk_to_permission (`permission` ASC) ,
  index main (`id` ASC, `name` ASC, `permission` ASC) ,
  constraint `fk_to_administrator`
    foreign key (`updator` , `owner` )
    references `administrator` (`id` , `id` )
    on delete no action
    on update no action,
  constraint `fk_to_permission`
    foreign key (`permission` )
    references `permission` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
COMMENT = 'plugin registory.'
row_format = default;


-- -----------------------------------------------------
-- table `module_list`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `module_list` ;

CREATE  TABLE IF NOT EXISTS `module_list` (
  `id` int unsigned NOT NULL auto_increment ,
  `plugin_id` int unsigned NOT NULL ,
  `file_id` int unsigned NOT NULL ,
  PRIMARY KEY (`id`) ,
  index fk_to_plugin_registory (`plugin_id` ASC) ,
  index fk_to_file (`file_id` ASC) ,
  index main (`id` ASC, `plugin_id` ASC) ,
  constraint `fk_to_plugin_registory`
    foreign key (`plugin_id` )
    references `plugin_registory` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_file`
    foreign key (`file_id` )
    references `file` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
row_format = default;


-- -----------------------------------------------------
-- table `keyword`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keyword` ;

CREATE  TABLE IF NOT EXISTS `keyword` (
  `id` int unsigned NOT NULL auto_increment ,
  `name` VARCHAR(20) NOT NULL ,
  `owner` int unsigned NOT NULL ,
  PRIMARY KEY (`id`) ,
  index fk_to_member (`owner` ASC) ,
  index main (`id` ASC, `name` ASC, `owner` ASC) ,
  unique index name_uniq (`name` ASC) ,
  constraint `fk_to_member`
    foreign key (`owner` )
    references `member` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
COMMENT = 'contents tag/keywork.';


-- -----------------------------------------------------
-- table `member_attribute_master`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `member_attribute_master` ;

CREATE  TABLE IF NOT EXISTS `member_attribute_master` (
  `id` int unsigned NOT NULL auto_increment ,
  `name` VARCHAR(100) NOT NULL ,
  `type` char(1) NOT NULL COMMENT 'attribute type.0:text filed,1:text area,2:select box,3:radio,4:check box' ,
  `detail` VARCHAR(255) null ,
  PRIMARY KEY (`id`) )
ENGINE = MYISAM
COMMENT = 'other member attribute items.'
row_format = default;


-- -----------------------------------------------------
-- table `member_attribute_item_master`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `member_attribute_item_master` ;

CREATE  TABLE IF NOT EXISTS `member_attribute_item_master` (
  `id` int unsigned NOT NULL auto_increment ,
  `attribute_id` int unsigned NOT NULL ,
  `item` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`id`) ,
  index fk_to_member_attribute_master (`attribute_id` ASC) ,
  index main (`id` ASC, `attribute_id` ASC) ,
  constraint `fk_to_member_attribute_master`
    foreign key (`attribute_id` )
    references `member_attribute_master` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
row_format = default;


-- -----------------------------------------------------
-- table `member_attribute_value`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `member_attribute_value` ;

CREATE  TABLE IF NOT EXISTS `member_attribute_value` (
  `member_id` int unsigned NOT NULL ,
  `attribute_id` int unsigned NOT NULL ,
  `value` VARCHAR(255) null ,
  `attribute_item_id` int unsigned NOT NULL ,
  index fk_to_member (`member_id` ASC) ,
  index fk_to_attribute (`attribute_id` ASC) ,
  PRIMARY KEY (`attribute_item_id`, `member_id`, `attribute_id`) ,
  index fk_to_attribute_item (`attribute_item_id` ASC) ,
  constraint `fk_to_member`
    foreign key (`member_id` )
    references `member` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_attribute`
    foreign key (`attribute_id` )
    references `member_attribute_master` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_attribute_item`
    foreign key (`attribute_item_id` )
    references `member_attribute_item_master` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
row_format = default;


-- -----------------------------------------------------
-- table `plugin_list`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `plugin_list` ;

CREATE  TABLE IF NOT EXISTS `plugin_list` (
  `id` int unsigned NOT NULL auto_increment ,
  `plugin_id` int unsigned NOT NULL ,
  `member_id` int unsigned NOT NULL ,
  `status` char(1) NOT NULL COMMENT '0:using,1:unuse,2:unstalled,3:installed/download,4:checked(be interst plugin/no download)' ,
  `update_date` TIMESTAMP NOT NULL ,
  `version` double(5,3) unsigned NOT NULL ,
  PRIMARY KEY (`id`) ,
  index fk_to_plugin (`plugin_id` ASC, `version` ASC) ,
  index fk_to_member (`member_id` ASC) ,
  constraint `fk_to_plugin`
    foreign key (`plugin_id` , `version` )
    references `plugin_registory` (`id` , `version` )
    on delete no action
    on update no action,
  constraint `fk_to_member`
    foreign key (`member_id` )
    references `member` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
COMMENT = 'member is using plugin status list ';


-- -----------------------------------------------------
-- table `contents_comment`
-- -----------------------------------------------------
drop table if exists `contents_comment` ;

create  table if not exists `contents_comment` (
  `id` int unsigned not null auto_increment ,
  `comment_body` text not null ,
  `comment_body_id` int unsigned not null ,
  `update_date` timestamp not null ,
  `creator` int unsigned not null ,
  PRIMARY KEY (`id`) ,
  index fk_to_contents_body (`COMMENT_body_id` ASC) ,
  index fk_to_member (`creator` ASC) ,
  index main (`COMMENT_body_id` ASC, `id` ASC) ,
  constraint `fk_to_contents_body`
    foreign key (`COMMENT_body_id` )
    references `contents_body` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_member`
    foreign key (`creator` )
    references `member` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `profile`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `profile` ;

CREATE  TABLE IF NOT EXISTS `profile` (
  `id` int NOT NULL ,
  `member_id` int unsigned NOT NULL ,
  `family_name` VARCHAR(50) null ,
  `first_name` VARCHAR(50) null ,
  `middle_name` VARCHAR(50) null ,
  `address_area` VARCHAR(100) null ,
  `address_city` VARCHAR(100) null ,
  `address_other1` VARCHAR(100) null ,
  `address_other2` VARCHAR(100) null ,
  `zip_code` VARCHAR(8) null ,
  `sign` VARCHAR(255) null ,
  `no_point` int unsigned null COMMENT 'normal point count' ,
  `sp_point` int unsigned null COMMENT 'special  point' ,
  PRIMARY KEY (`id`) ,
  index fk_to_member (`member_id` ASC) ,
  index main (`id` ASC, `member_id` ASC, `address_area` ASC, `address_city` ASC, `address_other1` ASC) ,
  constraint `fk_to_member`
    foreign key (`member_id` )
    references `member` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
COMMENT = 'member\'s profile';


-- -----------------------------------------------------
-- table `liking`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `liking` ;

CREATE  TABLE IF NOT EXISTS `liking` (
  `id` int unsigned NOT NULL ,
  `name` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`id`) ,
  index main (`id` ASC, `name` ASC) )
ENGINE = MYISAM
COMMENT = 'liking list in profile';


-- -----------------------------------------------------
-- table `liking_book`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `liking_book` ;

CREATE  TABLE IF NOT EXISTS `liking_book` (
  `id` int unsigned NOT NULL ,
  `name` VARCHAR(100) NOT NULL ,
  `isbn` VARCHAR(13) NOT NULL ,
  PRIMARY KEY (`id`) ,
  index main (`id` ASC, `name` ASC, `isbn` ASC) )
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `liking_movies`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `liking_movies` ;

CREATE  TABLE IF NOT EXISTS `liking_movies` (
  `id` int unsigned NOT NULL ,
  `name` VARCHAR(100) NOT NULL ,
  `you_tube_id` VARCHAR(255) null ,
  PRIMARY KEY (`id`) ,
  index main (`id` ASC, `name` ASC) )
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `liking_book_mapping`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `liking_book_mapping` ;

CREATE  TABLE IF NOT EXISTS `liking_book_mapping` (
  `liking_book_id` int unsigned NOT NULL ,
  `profile_id` int unsigned NOT NULL ,
  PRIMARY KEY (`liking_book_id`, `profile_id`) ,
  index fk_to_profile (`profile_id` ASC) ,
  index fk_to_liking_book (`liking_book_id` ASC) ,
  index main (`profile_id` ASC, `liking_book_id` ASC) ,
  constraint `fk_to_profile`
    foreign key (`profile_id` )
    references `profile` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_liking_book`
    foreign key (`liking_book_id` )
    references `liking_book` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `liking_mapping`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `liking_mapping` ;

CREATE  TABLE IF NOT EXISTS `liking_mapping` (
  `liking_id` int unsigned NOT NULL ,
  `profile_id` int unsigned NOT NULL ,
  PRIMARY KEY (`liking_id`, `profile_id`) ,
  index fk_to_profile (`profile_id` ASC) ,
  index fk_to_liking (`liking_id` ASC) ,
  index main (`profile_id` ASC, `liking_id` ASC) ,
  constraint `fk_to_profile`
    foreign key (`profile_id` )
    references `profile` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_liking`
    foreign key (`liking_id` )
    references `liking` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `liking_moves_mapping`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `liking_moves_mapping` ;

CREATE  TABLE IF NOT EXISTS `liking_moves_mapping` (
  `profile_id` int unsigned NOT NULL ,
  `liking_movies_id` int unsigned NOT NULL ,
  PRIMARY KEY (`profile_id`, `liking_movies_id`) ,
  index fk_to_profile (`profile_id` ASC) ,
  index fk_to_liking_movies (`liking_movies_id` ASC) ,
  index main (`profile_id` ASC, `liking_movies_id` ASC) ,
  constraint `fk_to_profile`
    foreign key (`profile_id` )
    references `profile` (`id` )
    on delete no action
    on update no action,
  constraint `fk_to_liking_movies`
    foreign key (`liking_movies_id` )
    references `liking_movies` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `site_config`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `site_config` ;

CREATE  TABLE IF NOT EXISTS `site_config` (
  `member_id` int unsigned NOT NULL ,
  `name` VARCHAR(100) NOT NULL ,
  `detail` TEXT null ,
  `local_lang` VARCHAR(2) null ,
  `local_area` VARCHAR(2) null ,
  `time_zone` char(2) null ,
  `local_document_root` VARCHAR(255) NOT NULL ,
  `remote_document_root` VARCHAR(255) NOT NULL ,
  `remote_host` VARCHAR(255) NOT NULL ,
  `connection_scheme` VARCHAR(10) NOT NULL ,
  `contents_index_path` VARCHAR(255) NOT NULL ,
  `public_key_file_path` VARCHAR(255) null ,
  PRIMARY KEY (`member_id`, `name`) ,
  index fk_to_member (`member_id` ASC) ,
  index main (`member_id` ASC, `name` ASC) ,
  constraint `fk_to_member`
    foreign key (`member_id` )
    references `member` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;


-- -----------------------------------------------------
-- table `message`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `message` ;

CREATE  TABLE IF NOT EXISTS `message` (
  `id` int unsigned NOT NULL auto_increment ,
  `from_group_id` int unsigned NOT NULL ,
  `to_group_id` int unsigned NOT NULL ,
  `from_member_id` int unsigned NOT NULL ,
  `to_member_id` int unsigned NOT NULL ,
  `title` VARCHAR(200) NOT NULL ,
  `body` TEXT null ,
  `order` tinyint NOT NULL default 0 COMMENT 'normal message or special report message.normal(>0);special report(<0);' ,
  PRIMARY KEY (`id`) ,
  index fk_to_member (`from_member_id` ASC, `to_member_id` ASC) ,
  index fk_to_group (`from_group_id` ASC, `to_group_id` ASC) ,
  index main (`id` ASC, `to_member_id` ASC, `from_member_id` ASC, `to_group_id` ASC, `from_group_id` ASC) ,
  constraint `fk_to_member`
    foreign key (`from_member_id` , `to_member_id` )
    references `member` (`id` , `id` )
    on delete no action
    on update no action,
  constraint `fk_to_group`
    foreign key (`from_group_id` , `to_group_id` )
    references `member_group` (`id` , `id` )
    on delete no action
    on update no action)
ENGINE = MYISAM
COMMENT = 'member<->group or gourp<->member message table';


-- -----------------------------------------------------
-- table `cache_mst`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `cache_mst` ;

CREATE  TABLE IF NOT EXISTS `cache_mst` (
  `table_name` VARCHAR(32) NOT NULL COMMENT 'table name' ,
  `last_modified` TIMESTAMP NOT NULL COMMENT 'last modified date.' ,
  `update_mod_name` VARCHAR(50) null ,
  PRIMARY KEY (`table_name`) ,
  index main (`table_name` ASC, `last_modified` ASC, `update_mod_name` ASC) )
ENGINE = MYISAM
COMMENT = 'cache info';


-- -----------------------------------------------------
-- table `template_script_list`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `template_script_list` ;

CREATE  TABLE IF NOT EXISTS `template_script_list` (
  `template_id` int NOT NULL ,
  `file_name` VARCHAR(255) NOT NULL COMMENT 'file path: $doc_root/template/$template_id/$script_type/$file_name' ,
  `type` char(1) NOT NULL COMMENT '0:css,1:javASCript' ,
  `detail` VARCHAR(255) null ,
  PRIMARY KEY (`template_id`, `file_name`, `type`) ,
  index fk_to_template (`template_id` ASC) ,
  constraint `fk_to_template`
    foreign key (`template_id` )
    references `template` (`id` )
    on delete no action
    on update no action)
ENGINE = MYISAM;