-------------------------------------------
----- 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);