###########################################
#### teckotooling database schema #########
###########################################
set names 'gbk';
drop database if exists teckotooling;
create database teckotooling
character set utf8
collate utf8_general_ci;
use teckotooling;
#頁面表
create table page
(
page_id int(11) not null primary key auto_increment,
page_name varchar(12) not null unique key,
title_en varchar(128) default '',
title_cn varchar(128) default '',
title_hk varchar(128) default '',
keyword_en varchar(128) default '',
keyword_cn varchar(128) default '',
keyword_hk varchar(128) default '',
title_content_en varchar(64) default '',
title_content_cn varchar(64) default '',
title_content_hk varchar(64) default '',
content_en text default '',
content_cn text default '',
content_hk text default '',
readTimes int(11) default 0
)engine=innodb default charset=utf8;
#類別系列類
create table category
(
category_id int(11) not null primary key auto_increment,
categoryName_en varchar(64) not null unique key,
categoryName_cn varchar(64) not null unique key,
categoryName_hk varchar(64) not null unique key,
description_en text default '',
description_cn text default '',
description_hk text default '',
img varchar(32)
)engine=innodb default charset=utf8;
#項目表
create table item
(
item_id int(11) not null primary key auto_increment,
item_no varchar(6) not null unique key,
category_id int(11) not null ,
itemName_en varchar(64) not null unique key,
itemName_cn varchar(64) not null unique key,
itemName_hk varchar(64) not null unique key,
img varchar(64),
publishedDt timestamp,
lastOne char(1) default 'n',
visible char(1) default 'y',
readTimes int(11)
)engine=innodb default charset=utf8;
#基本文本屬性表
create table basicInfoText
(
id int(11) not null primary key auto_increment,
item_id int(11) not null,
propertyName_en varchar(128) not null,
propertyName_cn varchar(128) not null,
propertyName_hk varchar(128) not null,
propertyValue_en varchar(256) default '',
propertyValue_cn varchar(256) default '',
propertyValue_hk varchar(256) default '',
unit_en varchar(32) default '',
unit_cn varchar(32) default '',
unit_hk varchar(32) default '',
visible char(1) default 'y'
)engine=innodb default charset=utf8;
#基本圖片屬性表
create table basicInfoImg
(
id int(11) not null primary key auto_increment,
item_id int(11) not null ,
imgName_en varchar(64),
imgName_cn varchar(64),
imgName_hk varchar(64),
imgUrl varchar(64),
visible char(1) default 'y'
)engine=innodb default charset=utf8;
#基本圖片屬性表
create table basicInfoDoc
(
id int(11) not null primary key auto_increment,
item_id int(11) not null ,
docName_en varchar(128),
docName_cn varchar(128),
docName_hk varchar(128),
docUrl varchar(64),
size varchar(16) default '',
contentType varchar(32) default '',
readTimes int(11),
enable char(1) default 'y',
password varchar(32),
visible char(1) default 'y'
)engine=innodb default charset=utf8;
#詳細屬性表
create table advancedInfo
(
id int(11) not null primary key auto_increment,
item_id int(11) not null ,
content_en longText,
content_cn longText,
content_hk longText,
visible char(1) default 'y'
)engine=innodb default charset=utf8;
#評論表
create table comment
(
id int(11) not null primary key auto_increment,
item_id int(11) not null ,
commenter varchar(32) not null,
dateTime timestamp,
content longText default '',
visible char(1) default 'y'
)engine=innodb default charset=utf8;
#留言表
create table leadWord
(
id int(11) not null primary key auto_increment,
leadWorder varchar(32) not null,
dateTime timestamp,
content longText default '',
visible char(1) default 'y'
)engine=innodb default charset=utf8;
#創建其它設置表
create table otherConfig
(
id int(11) not null primary key,
enablePress char(1) default 'y',
isImagePress char(1) default 'y',
textPress varchar(32) default '',
imagePress varchar(32) default '',
userFaceStyle varchar(32) default '',
afficheEn longText default '',
afficheCn longText default '',
afficheHk longText default ''
);
#用戶表
create table user
(
user_id int(11) not null primary key auto_increment,
user_name varchar(32) not null,
password varchar(32),
ENABLED tinyint(1) not null default 0
)engine=innodb default charset=utf8;
#用戶權限表連接表
create table user_auth
(
user_id int(11) not null,
auth_id int(11) not null,
primary key(user_id,auth_id)
)engine=innodb default charset=utf8;
#創建權限表
create table authority
(
auth_id int(11) not null primary key auto_increment,
authority varchar(255) not null,
auth_type varchar(32) not null,
protected_res varchar(128) not null,
display varchar(64) not null,
note varchar(64) default null
) engine=innodb default charset=utf8;
#參照完整性
#項目參照類別
alter table item
add index index_cat(category_id),
add constraint fk_item_category foreign key (category_id) references category(category_id) on update cascade on delete cascade ;
#項目文本屬性參照項目
alter table basicInfoText
add index index_bI(item_id),
add constraint fk_basicInfoText_item foreign key (item_id) references item(item_id) on update cascade on delete cascade ;
#項目圖片屬性參照項目
alter table basicInfoImg
add index index_bI(item_id),
add constraint fk_basicInfoImg_item foreign key (item_id) references item(item_id) on update cascade on delete cascade ;
#項目文檔屬性參照項目
alter table basicInfoDoc
add index index_bI(item_id),
add constraint fk_basicInfoDoc_item foreign key (item_id) references item(item_id) on update cascade on delete cascade ;
#項目詳細文本參照項目
alter table advancedInfo
add index index_bI(item_id),
add constraint fk_advancedInfo_item foreign key (item_id) references item(item_id) on update cascade on delete cascade ;
#項目詳細文本參照項目
alter table comment
add index index_cI(item_id),
add constraint fk_comment_item foreign key (item_id) references item(item_id) on update cascade on delete cascade ;
#權限連接表參照用戶表
alter table user_auth
add index index_user(user_id),
add constraint fk_user_auth_user foreign key (user_id) references user(user_id) on update cascade on delete cascade ;
#初始化表
#用戶表
insert into user values(1,'admin','21232f297a57a5a743894a0e4a801fc3',1);
#頁面表
insert into page
values(1,'home','home','home','home','home','home','home','home','home','home','home','home','home',0),
(2,'about','about','about','about','about','about','about','about','about','about','about','about','about',0),
(3,'product','product','product','product','product','product','product','product','product','product','product','product','product',0),
(4,'services','services','services','services','services','services','services','services','services','services','services','services','services',0),
(5,'contactUs','contactUs','contactUs','contactUs','contactUs','contactUs','contactUs','contactUs','contactUs','contactUs','contactUs','contactUs','contactUs',0);
#其它設置
insert into otherConfig values(1,'y','y','semovy@gmail.com','logo.gif','blue.css','affiche here...','公告在此……','公告在此……');
#初始資源鑒定表
INSERT INTO `authority` (`AUTH_ID`, `AUTHORITY`, `AUTH_TYPE`, `PROTECTED_RES`, `DISPLAY`, `NOTE`) VALUES
#項目資源保護鑒定
(1,'AUTH_FUNC_ItemManager.saveItem','FUNCTION','com.semovy.service.IItemService.saveItem','創建項目',NULL),
(2,'AUTH_FUNC_ItemManager.updateItem','FUNCTION','com.semovy.service.IItemService.updateItem','更新項目',NULL),
(3,'AUTH_FUNC_ItemManager.deleteItemById','FUNCTION','com.semovy.service.IItemService.deleteItemById','刪除項目',NULL),
(4,'AUTH_FUNC_ItemManager.outPutXMLItem','FUNCTION','com.semovy.service.IItemService.outPutXMLItem','訪問項目管理',NULL),
#頁面資源
(5,'AUTH_FUNC_PageManager.updatePage','FUNCTION','com.semovy.service.IPageService.updatePage','修改頁面',NULL),
(6,'AUTH_FUNC_PageManager.outPutPageXML','FUNCTION','com.semovy.service.IPageService.outPutPageXML','訪問管理頁面',NULL),
#其它管理
(7,'AUTH_FUNC_OtherconfigManager.updateOtherconfig','FUNCTION','com.semovy.service.IOtherconfigService.updateOtherconfig','修改其它管理',NULL),
(8,'AUTH_FUNC_OtherconfigManager.outPutOtherconfigXML','FUNCTION','com.semovy.service.IOtherconfigService.outPutOtherconfigXML','訪問其它管理',NULL),
#用戶管理
(9,'AUTH_FUNC_UserManager.updateUser','FUNCTION','com.semovy.service.IUserService.updateUser','修改用戶',NULL),
(10,'AUTH_FUNC_UserManager.outPutUsersListXML','FUNCTION','com.semovy.service.IUserService.outPutUsersListXML','訪問用戶管理',NULL),
#留言管理
(11,'AUTH_FUNC_LeadwordManager.updateUser','FUNCTION','com.semovy.service.ILeadwordService.updateLeadword','修改留言',NULL),
(12,'AUTH_FUNC_LeadwordManager.outPutUsersListXML','FUNCTION','com.semovy.service.ILeadwordService.deleteLeadwordById','刪除一條留言',NULL),
(13,'AUTH_FUNC_LeadwordManager.getLeadWordsOfPageByCriteria','FUNCTION','com.semovy.service.ILeadwordService.getLeadWordsOfPageByCriteria','獲取分頁留言',NULL),
(14,'AUTH_FUNC_LeadwordManager.outPutXMLLeadword','FUNCTION','com.semovy.service.ILeadwordService.outPutXMLLeadword','訪問留言管理',NULL),
#評論管理
(15,'AUTH_FUNC_CommentManager.updateComment','FUNCTION','com.semovy.service.ICommentService.updateComment','修改評論',NULL),
(16,'AUTH_FUNC_CommentManager.deleteCommentById','FUNCTION','com.semovy.service.ICommentService.deleteCommentById','刪除一條評論',NULL),
(17,'AUTH_FUNC_CommentManager.outPutXMLComment','FUNCTION','com.semovy.service.ICommentService.outPutXMLComment','訪問評論管理',NULL),
#項目基本文本管理
(18,'AUTH_FUNC_BasicinfotextManager.getBasicinfotextById','FUNCTION','com.semovy.service.IBasicinfotextService.getBasicinfotextById','獲取一個項目基本文本屬性',NULL),
(19,'AUTH_FUNC_BasicinfotextManager.saveBasicinfotext','FUNCTION','com.semovy.service.IBasicinfotextService.saveBasicinfotext','保存項目基本文本屬性',NULL),
(20,'AUTH_FUNC_BasicinfotextManager.deleteBasicinfotextById','FUNCTION','com.semovy.service.IBasicinfotextService.deleteBasicinfotextById','刪除項目基本文本屬性',NULL),
#(21,'AUTH_FUNC_BasicinfotextManager.outPutLocaleUnitXML','FUNCTION','com.semovy.service.IBasicinfotextService.outPutLocaleUnitXML','訪問基本文本屬性單位',NULL),
(22,'AUTH_FUNC_BasicinfotextManager.outPutBasicinfotextXMLOfItem','FUNCTION','com.semovy.service.IBasicinfotextService.outPutBasicinfotextXMLOfItem','訪問基本文本屬性管理',NULL),
#項目基本圖片管理
(23,'AUTH_FUNC_BasicinfoimgManager.getBasicinfoimgById','FUNCTION','com.semovy.service.IBasicinfoimgService.getBasicinfoimgById','獲取一個項目基本圖片屬性',NULL),
(24,'AUTH_FUNC_BasicinfoimgManager.saveBasicinfoimg','FUNCTION','com.semovy.service.IBasicinfoimgService.saveBasicinfoimg','保存項目基本圖片屬性',NULL),
(25,'AUTH_FUNC_BasicinfoimgManager.updateBasicinfoimg','FUNCTION','com.semovy.service.IBasicinfoimgService.updateBasicinfoimg','修改項目基本圖片屬性',NULL),
(26,'AUTH_FUNC_BasicinfoimgManager.deleteBasicinfoimgById','FUNCTION','com.semovy.service.IBasicinfoimgService.deleteBasicinfoimgById','刪除基本圖片屬性',NULL),
(27,'AUTH_FUNC_BasicinfoimgManager.outputBasicinfoimgXML','FUNCTION','com.semovy.service.IBasicinfoimgService.outputBasicinfoimgXML','訪問基本圖片屬性管理',NULL),
#項目基本文檔管理
#(28,'AUTH_FUNC_BasicinfodocManager.getBasicinfodocById','FUNCTION','com.semovy.service.IBasicinfodocService.getBasicinfodocById','獲取一個項目基本文檔屬性',NULL),
(29,'AUTH_FUNC_BasicinfodocManager.saveBasicinfodoc','FUNCTION','com.semovy.service.IBasicinfodocService.saveBasicinfodoc','保存項目基本文檔屬性',NULL),
(30,'AUTH_FUNC_BasicinfodocManager.updateBasicinfodoc','FUNCTION','com.semovy.service.IBasicinfodocService.updateBasicinfodoc','修改項目基本文檔屬性',NULL),
(31,'AUTH_FUNC_BasicinfodocManager.deleteBasicinfodocById','FUNCTION','com.semovy.service.IBasicinfodocService.deleteBasicinfodocById','刪除基本文檔屬性',NULL),
(32,'AUTH_FUNC_BasicinfodocManager.outputBasicinfodocXML','FUNCTION','com.semovy.service.IBasicinfodocService.outputBasicinfodocXML','訪問基本圖片文檔管理',NULL),
#項目高級文本管理
(33,'AUTH_FUNC_AdvancedinfoManager.getAdvancedinfoById','FUNCTION','com.semovy.service.IAdvancedinfoService.getAdvancedinfoById','獲取一個項目高級文本屬性',NULL),
(34,'AUTH_FUNC_AdvancedinfoManager.saveAdvancedinfo','FUNCTION','com.semovy.service.IAdvancedinfoService.saveAdvancedinfo','保存項目項目高級文本',NULL),
(35,'AUTH_FUNC_AdvancedinfoManager.updateAdvancedinfo','FUNCTION','com.semovy.service.IAdvancedinfoService.updateAdvancedinfo','修改項目項目高級文本',NULL),
(36,'AUTH_FUNC_AdvancedinfoManager.deleteAdvancedinfoById','FUNCTION','com.semovy.service.IAdvancedinfoService.deleteAdvancedinfoById','刪除項目高級文本',NULL),
#項目類別管理
(37,'AUTH_FUNC_CategoryManager.saveCategory','FUNCTION','com.semovy.service.ICategoryService.saveCategory','保存項目類別',NULL),
(38,'AUTH_FUNC_CategoryManager.updateCategory','FUNCTION','com.semovy.service.ICategoryService.updateCategory','修改項目類別',NULL),
(39,'AUTH_FUNC_CategoryManager.deleteCategoryById','FUNCTION','com.semovy.service.ICategoryService.deleteCategoryById','刪除項目類別',NULL),
(40,'AUTH_FUNC_CategoryManager.outputCategoriesXML','FUNCTION','com.semovy.service.ICategoryService.outputCategoriesXML','訪問管理項目類別',NULL);
#初始化user_auth表
insert into user_auth values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(1,7),
(1,8),
(1,9),
(1,10),
(1,11),
(1,12),
(1,13),
(1,14),
(1,15),
(1,16),
(1,17),
(1,18),
(1,19),
(1,20),
(1,21),
(1,22),
(1,23),
(1,24),
(1,25),
(1,26),
(1,27),
(1,28),
(1,29),
(1,30),
(1,31),
(1,32),
(1,33),
(1,34),
(1,35),
(1,36),
(1,37),
(1,38),
(1,39),
(1,40);