JOSSO DDL SQL

2006年8月30日 (三) 18:03的最后版本

CREATE TABLE JOSSO_ROLE (

      NAME                 VARCHAR2(16) NOT NULL,
      DESCRIPTION          VARCHAR2(64) NULL

);

ALTER TABLE JOSSO_ROLE

      ADD  ( PRIMARY KEY (NAME) ) ;

CREATE TABLE JOSSO_USER (

      LOGIN                VARCHAR2(16) NOT NULL,
      PASSWORD             VARCHAR2(20) NOT NULL,
      NAME                 VARCHAR2(64) NULL,
      DESCRIPTION          VARCHAR2(64) NULL

);

ALTER TABLE JOSSO_USER

      ADD  ( PRIMARY KEY (LOGIN) ) ;

CREATE TABLE JOSSO_USER_PROPERTY (

      LOGIN                VARCHAR2(16) NOT NULL,
      NAME                 VARCHAR2(255) NOT NULL,
      VALUE                VARCHAR2(255) NOT NULL

);

ALTER TABLE JOSSO_USER_PROPERTY

      ADD  ( PRIMARY KEY (LOGIN, NAME) ) ;

CREATE TABLE JOSSO_USER_ROLE (

      LOGIN                VARCHAR2(16) NOT NULL,
      NAME                 VARCHAR2(255) NOT NULL

);

ALTER TABLE JOSSO_USER_ROLE

      ADD  ( PRIMARY KEY (LOGIN, NAME) ) ;

ALTER TABLE JOSSO_USER_PROPERTY

      ADD  ( FOREIGN KEY (LOGIN)
                            REFERENCES JOSSO_USER ) ;

ALTER TABLE JOSSO_USER_ROLE

      ADD  ( FOREIGN KEY (NAME)
                            REFERENCES JOSSO_ROLE ) ;

ALTER TABLE JOSSO_USER_ROLE

      ADD  ( FOREIGN KEY (LOGIN)
                            REFERENCES JOSSO_USER ) ;

加入sample数据

INSERT INTO JOSSO_ROLE (NAME,DESCRIPTION) VALUES('role1','The Role1');

INSERT INTO JOSSO_ROLE (NAME,DESCRIPTION) VALUES('role2','The Role2');

INSERT INTO JOSSO_ROLE (NAME,DESCRIPTION) VALUES('role3','The Role3');

INSERT INTO JOSSO_USER (LOGIN,PASSWORD,DESCRIPTION) VALUES('user1', 'user1pwd', 'The User1');

INSERT INTO JOSSO_USER_ROLE (LOGIN,NAME) VALUES('user1', 'role1');

INSERT INTO JOSSO_USER_ROLE (LOGIN,NAME) VALUES('user1', 'role2');

INSERT INTO JOSSO_USER (LOGIN,PASSWORD,DESCRIPTION) VALUES('user2', 'user2pwd', 'The User2');

INSERT INTO JOSSO_USER_ROLE (LOGIN,NAME) VALUES('user2', 'role3');

INSERT INTO JOSSO_USER_PROPERTY(LOGIN,NAME,VALUE) VALUES('user1', 'user.name', 'User1 Name');

INSERT INTO JOSSO_USER_PROPERTY(LOGIN,NAME,VALUE) VALUES('user1', 'user.lastName', 'User1 Last Name');

INSERT INTO JOSSO_USER_PROPERTY(LOGIN,NAME,VALUE) VALUES('user1', 'user.registrationDate', 'User1 Registration Date');

INSERT INTO JOSSO_USER_PROPERTY(LOGIN,NAME,VALUE) VALUES('user2', 'user.name', 'User2 Name');

INSERT INTO JOSSO_USER_PROPERTY(LOGIN,NAME,VALUE) VALUES('user2', 'user.lastName', 'User2 Last Name');

INSERT INTO JOSSO_USER_PROPERTY(LOGIN,NAME,VALUE) VALUES('user2', 'user.registrationDate', 'User2 Registration Date');