test.script

anewssystem新闻发布系统集成使用了spring hibernate freemarker EXTJS等开源框架 可以作为学习参考

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE ARTICLE(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,TITLE VARCHAR(255),CATEGORY_ID INTEGER)
CREATE MEMORY TABLE CATEGORY(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255))
CREATE MEMORY TABLE MENU(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,SEQ INTEGER,TIP VARCHAR(255),FORWARD VARCHAR(255),NAME VARCHAR(255),TITLE VARCHAR(255),IMAGE VARCHAR(255),TARGET VARCHAR(255),DESCRIPTION VARCHAR(255),PARENT_ID INTEGER,CONSTRAINT FK24897F3F311C6A FOREIGN KEY(PARENT_ID) REFERENCES MENU(ID),CONSTRAINT FK24897FC3CA78B5 FOREIGN KEY(ID) REFERENCES MENU(ID))
CREATE MEMORY TABLE COMMENT(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,EMAIL VARCHAR(255),PUBLISHDATE TIMESTAMP,CONTENT VARCHAR(255),TITLE VARCHAR(255),ARTICLE_ID INTEGER,CONSTRAINT FK9BDE863F66981DF FOREIGN KEY(ARTICLE_ID) REFERENCES ARTICLE(ID))
CREATE MEMORY TABLE SS_USERS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL,PASSWD VARCHAR(255) NOT NULL,NAME VARCHAR(80) NOT NULL,EMAIL VARCHAR(255),STATUS INTEGER NOT NULL,DESCN VARCHAR(255),CONSTRAINT PK_USERS PRIMARY KEY(ID))
CREATE MEMORY TABLE SS_ROLES(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL,NAME VARCHAR(80) NOT NULL,DESCN VARCHAR(255),CONSTRAINT PK_ROLES PRIMARY KEY(ID))
CREATE MEMORY TABLE SS_USER_ROLE(USER_ID INTEGER NOT NULL,ROLE_ID INTEGER NOT NULL,CONSTRAINT PK_USER_ROLE PRIMARY KEY(USER_ID,ROLE_ID),CONSTRAINT FK_USER_ROLE_1 FOREIGN KEY(USER_ID) REFERENCES SS_USERS(ID),CONSTRAINT FK_USER_ROLE_2 FOREIGN KEY(ROLE_ID) REFERENCES SS_ROLES(ID))
CREATE MEMORY TABLE SS_RESOURCES(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL,NAME VARCHAR(80) NOT NULL,RES_TYPE VARCHAR(20) NOT NULL,RES_STRING VARCHAR(255) NOT NULL,DESCN VARCHAR(255),CONSTRAINT PK_RESOURCES PRIMARY KEY(ID))
CREATE MEMORY TABLE SS_ROLE_RESC(ROLE_ID INTEGER NOT NULL,RESC_ID INTEGER NOT NULL,CONSTRAINT PK_ROLE_RESC PRIMARY KEY(ROLE_ID,RESC_ID),CONSTRAINT FK_ROLE_RESC_1 FOREIGN KEY(ROLE_ID) REFERENCES SS_ROLES(ID),CONSTRAINT FK_ROLE_RESC_2 FOREIGN KEY(RESC_ID) REFERENCES SS_RESOURCES(ID))
CREATE MEMORY TABLE SS_ROLE_MENU(ROLE_ID INTEGER NOT NULL,MENU_ID INTEGER NOT NULL,PRIMARY KEY(MENU_ID,ROLE_ID),CONSTRAINT FK27DEB46915DE6C35 FOREIGN KEY(ROLE_ID) REFERENCES SS_ROLES(ID),CONSTRAINT FK27DEB469FC6FD795 FOREIGN KEY(MENU_ID) REFERENCES MENU(ID))
ALTER TABLE ARTICLE ADD CONSTRAINT FK379164D63DE729B5 FOREIGN KEY(CATEGORY_ID) REFERENCES CATEGORY(ID)
ALTER TABLE ARTICLE ALTER COLUMN ID RESTART WITH 2
ALTER TABLE CATEGORY ALTER COLUMN ID RESTART WITH 2
ALTER TABLE MENU ALTER COLUMN ID RESTART WITH 14
ALTER TABLE COMMENT ALTER COLUMN ID RESTART WITH 1
ALTER TABLE SS_USERS ALTER COLUMN ID RESTART WITH 23
ALTER TABLE SS_ROLES ALTER COLUMN ID RESTART WITH 4
ALTER TABLE SS_RESOURCES ALTER COLUMN ID RESTART WITH 19
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO ARTICLE VALUES(1,'jjjjjjjjjjjj',1)
INSERT INTO CATEGORY VALUES(1,'f')
INSERT INTO MENU VALUES(1,1,'','','Menu','\u83dc\u5355\u7ba1\u7406','','','',NULL)
INSERT INTO MENU VALUES(2,3,'','','Logout','\u9000\u51fa\u7cfb\u7edf','','','',NULL)
INSERT INTO MENU VALUES(3,0,'tip','menu/list.htm','ListMenu','\u6d4f\u89c8\u83dc\u5355','../images/icon/16x16/manage.gif','dblselect','desc',1)
INSERT INTO MENU VALUES(4,0,'tip','menu/create.htm','AddMenu','\u6dfb\u52a0\u83dc\u5355','../images/icon/16x16/new.gif','dblselect','desc',1)
INSERT INTO MENU VALUES(5,0,'tip','j_acegi_logout','Logout','\u9000\u51fa\u7cfb\u7edf','../images/icon/16x16/group.jpg','dblselect','desc',2)
INSERT INTO MENU VALUES(6,2,'','','Article','\u6587\u7ae0\u7ba1\u7406','','','',NULL)
INSERT INTO MENU VALUES(7,0,'','category/create.htm','EditCategory','\u6dfb\u52a0\u6587\u7ae0\u76ee\u5f55','../images/icon/16x16/manage.gif','dblselect','',6)
INSERT INTO MENU VALUES(8,0,'','category/list.htm','ListCategory','\u6d4f\u89c8\u6587\u7ae0\u76ee\u5f55','../images/icon/16x16/manage.gif','dblselect','',6)
INSERT INTO MENU VALUES(9,0,'','article/create.htm','EditArticle','\u6dfb\u52a0\u6587\u7ae0','../images/icon/16x16/manage.gif','dblselect','',6)
INSERT INTO MENU VALUES(10,0,'','article/list.htm','ListArticle','\u6d4f\u89c8\u6587\u7ae0','../images/icon/16x16/manage.gif','dblselect','',6)
INSERT INTO MENU VALUES(11,0,'','comment/create.htm','EditComment','\u6dfb\u52a0\u8bc4\u8bba','../images/icon/16x16/manage.gif','dblselect','',6)
INSERT INTO MENU VALUES(12,0,'','comment/list.htm','ListComment','\u6d4f\u89c8\u8bc4\u8bba','../images/icon/16x16/manage.gif','dblselect','',6)
INSERT INTO MENU VALUES(13,0,'','menu/listMenuRole.htm','MenuRole','\u83dc\u5355\u6743\u9650','../images/icon/16x16/manage.gif','dblselect','',1)
INSERT INTO SS_USERS VALUES(1,'21232f297a57a5a743894a0e4a801fc3','admin','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(2,'fa5473530e4d1a5a1e1eb53d2fedb10c','employee','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(3,'eaa46a95052be5c821346efc89d40cc9','Alex','[e-mail]',1,'111111')
INSERT INTO SS_USERS VALUES(4,'fa5473530e4d1a5a1e1eb53d2fedb10c','Bruce','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(5,'fa5473530e4d1a5a1e1eb53d2fedb10c','Fenly','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(6,'fa5473530e4d1a5a1e1eb53d2fedb10c','Johnson','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(7,'fa5473530e4d1a5a1e1eb53d2fedb10c','Jason','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(8,'fa5473530e4d1a5a1e1eb53d2fedb10c','Kelly','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(9,'fa5473530e4d1a5a1e1eb53d2fedb10c','Sam','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(10,'fa5473530e4d1a5a1e1eb53d2fedb10c','Derek','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(11,'fa5473530e4d1a5a1e1eb53d2fedb10c','Chansin','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(12,'fa5473530e4d1a5a1e1eb53d2fedb10c','Herry','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(13,'fa5473530e4d1a5a1e1eb53d2fedb10c','Terry','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(14,'fa5473530e4d1a5a1e1eb53d2fedb10c','Gibson','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(15,'785b7e94e50bb0d5d226c93e768f6437','Teresa','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(16,'fa5473530e4d1a5a1e1eb53d2fedb10c','Jacky','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(17,'fa5473530e4d1a5a1e1eb53d2fedb10c','Anderson','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(18,'fa5473530e4d1a5a1e1eb53d2fedb10c','Joyce','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(19,'fa5473530e4d1a5a1e1eb53d2fedb10c','Mark','[e-mail]',1,NULL)
INSERT INTO SS_USERS VALUES(20,'fa5473530e4d1a5a1e1eb53d2fedb10c','Ken','[e-mail]',1,NULL)
INSERT INTO SS_ROLES VALUES(1,'ROLE_ADMIN','Admin')
INSERT INTO SS_ROLES VALUES(2,'ROLE_EMPLOYEE','Employee')
INSERT INTO SS_ROLES VALUES(3,'aaaaa','aaafffffffff')
INSERT INTO SS_USER_ROLE VALUES(1,1)
INSERT INTO SS_USER_ROLE VALUES(1,2)
INSERT INTO SS_USER_ROLE VALUES(2,2)
INSERT INTO SS_USER_ROLE VALUES(3,1)
INSERT INTO SS_USER_ROLE VALUES(3,2)
INSERT INTO SS_USER_ROLE VALUES(3,3)
INSERT INTO SS_USER_ROLE VALUES(15,2)
INSERT INTO SS_RESOURCES VALUES(1,'Login the admin','URL','/admin/**','')
INSERT INTO SS_RESOURCES VALUES(2,'Access User admin','URL','/user/**','')
INSERT INTO SS_RESOURCES VALUES(3,'Access Role admin','URL','/role/**','')
INSERT INTO SS_RESOURCES VALUES(4,'Access Resource admin','URL','/resource/**','')
INSERT INTO SS_RESOURCES VALUES(5,'Remove User','METHOD','lg.lag.dao.UserDao.delUserInTable','')
INSERT INTO SS_RESOURCES VALUES(6,'Edit User','METHOD','lg.lag.dao.UserDao.save','')
INSERT INTO SS_RESOURCES VALUES(7,'Authorize Role','METHOD','lg.lag.dao.UserDao.authRoleForUser','')
INSERT INTO SS_RESOURCES VALUES(8,'Remove Role','METHOD','lg.lag.dao.RoleDao.delRoleInTable','')
INSERT INTO SS_RESOURCES VALUES(9,'Edit Role','METHOD','lg.lag.dao.RoleDao.save','')
INSERT INTO SS_RESOURCES VALUES(10,'Authorize Resource','METHOD','lg.lag.dao.RoleDao.authRescForRole','')
INSERT INTO SS_RESOURCES VALUES(11,'Login the admin','URL','/article/**','')
INSERT INTO SS_RESOURCES VALUES(12,'Access User admin','URL','/category/**','')
INSERT INTO SS_RESOURCES VALUES(13,'Access Role admin','URL','/comment/**','')
INSERT INTO SS_RESOURCES VALUES(14,'Access Resource admin','URL','/menu/**','')
INSERT INTO SS_RESOURCES VALUES(15,'Access Resource admin','URL','/index.jsp**','')
INSERT INTO SS_RESOURCES VALUES(16,'asd','aaa','aaa','aaafffffffff')
INSERT INTO SS_RESOURCES VALUES(17,'asdfk','lk','lkj','lk')
INSERT INTO SS_RESOURCES VALUES(18,'aaaaa','URL','sdf','sadf')
INSERT INTO SS_ROLE_RESC VALUES(1,1)
INSERT INTO SS_ROLE_RESC VALUES(1,2)
INSERT INTO SS_ROLE_RESC VALUES(1,3)
INSERT INTO SS_ROLE_RESC VALUES(1,4)
INSERT INTO SS_ROLE_RESC VALUES(1,5)
INSERT INTO SS_ROLE_RESC VALUES(1,6)
INSERT INTO SS_ROLE_RESC VALUES(1,7)
INSERT INTO SS_ROLE_RESC VALUES(1,8)
INSERT INTO SS_ROLE_RESC VALUES(1,9)
INSERT INTO SS_ROLE_RESC VALUES(1,10)
INSERT INTO SS_ROLE_RESC VALUES(1,11)
INSERT INTO SS_ROLE_RESC VALUES(1,12)
INSERT INTO SS_ROLE_RESC VALUES(1,13)
INSERT INTO SS_ROLE_RESC VALUES(1,14)
INSERT INTO SS_ROLE_RESC VALUES(1,15)
INSERT INTO SS_ROLE_RESC VALUES(2,1)
INSERT INTO SS_ROLE_RESC VALUES(2,2)
INSERT INTO SS_ROLE_RESC VALUES(2,3)
INSERT INTO SS_ROLE_RESC VALUES(2,6)
INSERT INTO SS_ROLE_RESC VALUES(3,1)
INSERT INTO SS_ROLE_RESC VALUES(3,2)
INSERT INTO SS_ROLE_MENU VALUES(1,1)
INSERT INTO SS_ROLE_MENU VALUES(2,1)
INSERT INTO SS_ROLE_MENU VALUES(1,2)
INSERT INTO SS_ROLE_MENU VALUES(1,3)
INSERT INTO SS_ROLE_MENU VALUES(2,3)
INSERT INTO SS_ROLE_MENU VALUES(1,4)
INSERT INTO SS_ROLE_MENU VALUES(2,4)
INSERT INTO SS_ROLE_MENU VALUES(1,5)
INSERT INTO SS_ROLE_MENU VALUES(1,6)
INSERT INTO SS_ROLE_MENU VALUES(2,6)
INSERT INTO SS_ROLE_MENU VALUES(1,7)
INSERT INTO SS_ROLE_MENU VALUES(1,8)
INSERT INTO SS_ROLE_MENU VALUES(2,8)
INSERT INTO SS_ROLE_MENU VALUES(1,9)
INSERT INTO SS_ROLE_MENU VALUES(2,9)
INSERT INTO SS_ROLE_MENU VALUES(1,10)
INSERT INTO SS_ROLE_MENU VALUES(1,11)
INSERT INTO SS_ROLE_MENU VALUES(1,12)
INSERT INTO SS_ROLE_MENU VALUES(1,13)
INSERT INTO SS_ROLE_MENU VALUES(2,13)