-------------------------------------------
----- teckotooling database schema --------
----- hsqldb-------------------------------
-------------------------------------------
drop table page if exists ;
--页面表
create table page
(
page_id int not null identity primary key ,
page_name varchar(12) not null,
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 longvarchar default '',
content_cn longvarchar default '',
content_hk longvarchar default '',
readTimes int default 0,
unique(page_name)
);
drop table if exists comment;
drop table if exists advancedInfo;
drop table if exists basicInfoDoc;
drop table if exists basicInfoImg;
drop table if exists basicInfoText;
drop table item if exists ;
drop table category if exists ;
--类别系列类
create table category
(
category_id int not null identity primary key ,
categoryName_en varchar(64) not null,-- unique ,
categoryName_cn varchar(64) not null,-- unique ,
categoryName_hk varchar(64) not null,-- unique ,
description_en longvarchar default '',
description_cn longvarchar default '',
description_hk longvarchar default '',
img varchar(32) default '',
unique(category_id),
unique(categoryName_en),
unique(categoryName_cn),
unique(categoryName_hk)
);
--项目表
create table item
(
item_id int not null identity primary key ,
item_no varchar(6) not null,
category_id int not null ,
itemName_en varchar(64) not null,-- unique ,
itemName_cn varchar(64) not null,-- unique ,
itemName_hk varchar(64) not null,-- unique ,
img varchar(64),
publishedDt timestamp,
lastOne char(1) default 'n',
visible char(1) default 'y',
readTimes int default 0,
unique(item_no),
unique(itemname_en),
unique(itemname_cn),
unique(itemname_hk),
foreign key(category_id) references category(category_id) on update cascade on delete cascade
);
--基本文本属性表
create table basicInfoText
(
id int not null identity primary key ,
item_id int 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',
foreign key(item_id) references item(item_id) on update cascade on delete cascade
);
--基本图片属性表
create table basicInfoImg
(
id int not null identity primary key ,
item_id int not null ,
imgName_en varchar(64),
imgName_cn varchar(64),
imgName_hk varchar(64),
imgUrl varchar(64),
visible char(1) default 'y',
foreign key(item_id) references item(item_id) on update cascade on delete cascade
);
--基本图片属性表
create table basicInfoDoc
(
id int not null identity primary key ,
item_id int 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,
enable char(1) default 'y',
password varchar(32),
visible char(1) default 'y',
foreign key(item_id) references item(item_id) on update cascade on delete cascade
);
--详细属性表
create table advancedInfo
(
id int not null identity primary key ,
item_id int not null ,
content_en longvarchar ,
content_cn longvarchar ,
content_hk longvarchar ,
visible char(1) default 'y',
foreign key(item_id) references item(item_id) on update cascade on delete cascade
);
--评论表
create table comment
(
id int not null identity primary key,
item_id int not null ,
commenter varchar(32) not null,
dateTime timestamp,
content longvarchar default '',
visible char(1) default 'y',
foreign key(item_id) references item(item_id) on update cascade on delete cascade
);
drop table if exists leadWord;
--留言表
create table leadWord
(
id int not null identity primary key ,
leadWorder varchar(32) not null,
dateTime timestamp,
content longvarchar default '',
visible char(1) default 'y'
);
drop table if exists otherConfig;
--创建其它设置表
create table otherConfig
(
id int 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 longvarchar default '',
afficheCn longvarchar default '',
afficheHk longvarchar default ''
);
drop table if exists user_auth;
drop table if exists user;
drop table if exists authority;
--用户表
create table user
(
user_id int not null identity primary key ,
user_name varchar(32) not null,
password varchar(32),
ENABLED tinyint default 0
);
--用户权限表连接表
create table user_auth
(
user_id int not null,
auth_id int not null,
primary key(user_id,auth_id),
foreign key(user_id) references user(user_id) on update cascade on delete cascade
);
--创建权限表
create table authority
(
auth_id int not null identity primary key ,
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
) ;
--初始化表
--用户表
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);
insert into page
values (2,'about','about','about','about','about','about','about','about','about','about','about','about','about',0);
insert into page
values(3,'product','product','product','product','product','product','product','product','product','product','product','product','product',0);
insert into page
values(4,'services','services','services','services','services','services','services','services','services','services','services','services','services',0);
insert into page
values(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);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(2,'AUTH_FUNC_ItemManager.updateItem','FUNCTION','com.semovy.service.IItemService.updateItem','更新项目',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(3,'AUTH_FUNC_ItemManager.deleteItemById','FUNCTION','com.semovy.service.IItemService.deleteItemById','删除项目',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(4,'AUTH_FUNC_ItemManager.outPutXMLItem','FUNCTION','com.semovy.service.IItemService.outPutXMLItem','访问项目管理',NULL);
--页面资源
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(5,'AUTH_FUNC_PageManager.updatePage','FUNCTION','com.semovy.service.IPageService.updatePage','修改页面',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(6,'AUTH_FUNC_PageManager.outPutPageXML','FUNCTION','com.semovy.service.IPageService.outPutPageXML','访问管理页面',NULL);
--其它管理
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(7,'AUTH_FUNC_OtherconfigManager.updateOtherconfig','FUNCTION','com.semovy.service.IOtherconfigService.updateOtherconfig','修改其它管理',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(8,'AUTH_FUNC_OtherconfigManager.outPutOtherconfigXML','FUNCTION','com.semovy.service.IOtherconfigService.outPutOtherconfigXML','访问其它管理',NULL);
--用户管理
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(9,'AUTH_FUNC_UserManager.updateUser','FUNCTION','com.semovy.service.IUserService.updateUser','修改用户',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(10,'AUTH_FUNC_UserManager.outPutUsersListXML','FUNCTION','com.semovy.service.IUserService.outPutUsersListXML','访问用户管理',NULL);
--留言管理
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(11,'AUTH_FUNC_LeadwordManager.updateUser','FUNCTION','com.semovy.service.ILeadwordService.updateLeadword','修改留言',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(12,'AUTH_FUNC_LeadwordManager.outPutUsersListXML','FUNCTION','com.semovy.service.ILeadwordService.deleteLeadwordById','删除一条留言',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(13,'AUTH_FUNC_LeadwordManager.getLeadWordsOfPageByCriteria','FUNCTION','com.semovy.service.ILeadwordService.getLeadWordsOfPageByCriteria','获取分页留言',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(14,'AUTH_FUNC_LeadwordManager.outPutXMLLeadword','FUNCTION','com.semovy.service.ILeadwordService.outPutXMLLeadword','访问留言管理',NULL);
--评论管理
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(15,'AUTH_FUNC_CommentManager.updateComment','FUNCTION','com.semovy.service.ICommentService.updateComment','修改评论',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(16,'AUTH_FUNC_CommentManager.deleteCommentById','FUNCTION','com.semovy.service.ICommentService.deleteCommentById','删除一条评论',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(17,'AUTH_FUNC_CommentManager.outPutXMLComment','FUNCTION','com.semovy.service.ICommentService.outPutXMLComment','访问评论管理',NULL);
--项目基本文本管理
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(18,'AUTH_FUNC_BasicinfotextManager.getBasicinfotextById','FUNCTION','com.semovy.service.IBasicinfotextService.getBasicinfotextById','获取一个项目基本文本属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(19,'AUTH_FUNC_BasicinfotextManager.saveBasicinfotext','FUNCTION','com.semovy.service.IBasicinfotextService.saveBasicinfotext','保存项目基本文本属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(20,'AUTH_FUNC_BasicinfotextManager.deleteBasicinfotextById','FUNCTION','com.semovy.service.IBasicinfotextService.deleteBasicinfotextById','删除项目基本文本属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
--(21,'AUTH_FUNC_BasicinfotextManager.outPutLocaleUnitXML','FUNCTION','com.semovy.service.IBasicinfotextService.outPutLocaleUnitXML','访问基本文本属性单位',NULL),
(22,'AUTH_FUNC_BasicinfotextManager.outPutBasicinfotextXMLOfItem','FUNCTION','com.semovy.service.IBasicinfotextService.outPutBasicinfotextXMLOfItem','访问基本文本属性管理',NULL);
--项目基本图片管理
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(23,'AUTH_FUNC_BasicinfoimgManager.getBasicinfoimgById','FUNCTION','com.semovy.service.IBasicinfoimgService.getBasicinfoimgById','获取一个项目基本图片属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(24,'AUTH_FUNC_BasicinfoimgManager.saveBasicinfoimg','FUNCTION','com.semovy.service.IBasicinfoimgService.saveBasicinfoimg','保存项目基本图片属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(25,'AUTH_FUNC_BasicinfoimgManager.updateBasicinfoimg','FUNCTION','com.semovy.service.IBasicinfoimgService.updateBasicinfoimg','修改项目基本图片属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(26,'AUTH_FUNC_BasicinfoimgManager.deleteBasicinfoimgById','FUNCTION','com.semovy.service.IBasicinfoimgService.deleteBasicinfoimgById','删除基本图片属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(27,'AUTH_FUNC_BasicinfoimgManager.outputBasicinfoimgXML','FUNCTION','com.semovy.service.IBasicinfoimgService.outputBasicinfoimgXML','访问基本图片属性管理',NULL);
--项目基本文档管理
--(28,'AUTH_FUNC_BasicinfodocManager.getBasicinfodocById','FUNCTION','com.semovy.service.IBasicinfodocService.getBasicinfodocById','获取一个项目基本文档属性',NULL),
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(29,'AUTH_FUNC_BasicinfodocManager.saveBasicinfodoc','FUNCTION','com.semovy.service.IBasicinfodocService.saveBasicinfodoc','保存项目基本文档属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(30,'AUTH_FUNC_BasicinfodocManager.updateBasicinfodoc','FUNCTION','com.semovy.service.IBasicinfodocService.updateBasicinfodoc','修改项目基本文档属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(31,'AUTH_FUNC_BasicinfodocManager.deleteBasicinfodocById','FUNCTION','com.semovy.service.IBasicinfodocService.deleteBasicinfodocById','删除基本文档属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(32,'AUTH_FUNC_BasicinfodocManager.outputBasicinfodocXML','FUNCTION','com.semovy.service.IBasicinfodocService.outputBasicinfodocXML','访问基本图片文档管理',NULL);
--项目高级文本管理
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(33,'AUTH_FUNC_AdvancedinfoManager.getAdvancedinfoById','FUNCTION','com.semovy.service.IAdvancedinfoService.getAdvancedinfoById','获取一个项目高级文本属性',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(34,'AUTH_FUNC_AdvancedinfoManager.saveAdvancedinfo','FUNCTION','com.semovy.service.IAdvancedinfoService.saveAdvancedinfo','保存项目项目高级文本',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(35,'AUTH_FUNC_AdvancedinfoManager.updateAdvancedinfo','FUNCTION','com.semovy.service.IAdvancedinfoService.updateAdvancedinfo','修改项目项目高级文本',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(36,'AUTH_FUNC_AdvancedinfoManager.deleteAdvancedinfoById','FUNCTION','com.semovy.service.IAdvancedinfoService.deleteAdvancedinfoById','删除项目高级文本',NULL);
--项目类别管理
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(37,'AUTH_FUNC_CategoryManager.saveCategory','FUNCTION','com.semovy.service.ICategoryService.saveCategory','保存项目类别',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(38,'AUTH_FUNC_CategoryManager.updateCategory','FUNCTION','com.semovy.service.ICategoryService.updateCategory','修改项目类别',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(39,'AUTH_FUNC_CategoryManager.deleteCategoryById','FUNCTION','com.semovy.service.ICategoryService.deleteCategoryById','删除项目类别',NULL);
INSERT INTO authority (AUTH_ID, AUTHORITY, AUTH_TYPE, PROTECTED_RES, DISPLAY, NOTE) VALUES
(40,'AUTH_FUNC_CategoryManager.outputCategoriesXML','FUNCTION','com.semovy.service.ICategoryService.outputCategoriesXML','访问管理项目类别',NULL);
--初始化user_auth表
insert into user_auth values (1,1);
insert into user_auth values (1,2);
insert into user_auth values (1,3);
insert into user_auth values (1,4);
insert into user_auth values (1,5);
insert into user_auth values (1,6);
insert into user_auth values (1,7);
insert into user_auth values (1,8);
insert into user_auth values (1,9);
insert into user_auth values (1,10);
insert into user_auth values (1,11);
insert into user_auth values (1,12);
insert into user_auth values (1,13);
insert into user_auth values (1,14);
insert into user_auth values (1,15);
insert into user_auth values (1,16);
insert into user_auth values (1,17);
insert into user_auth values (1,18);
insert into user_auth values (1,19);
insert into user_auth values (1,20);
insert into user_auth values (1,21);
insert into user_auth values (1,22);
insert into user_auth values (1,23);
insert into user_auth values (1,24);
insert into user_auth values (1,25);
insert into user_auth values (1,26);
insert into user_auth values (1,27);
insert into user_auth values (1,28);
insert into user_auth values (1,29);
insert into user_auth values (1,30);
insert into user_auth values (1,31);
insert into user_auth values (1,32);
insert into user_auth values (1,33);
insert into user_auth values (1,34);
insert into user_auth values (1,35);
insert into user_auth values (1,36);
insert into user_auth values (1,37);
insert into user_auth values (1,38);
insert into user_auth values (1,39);
insert into user_auth values (1,40);