java创建mysql sche_mysql项目sql schema

###########################################

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