AspNet-Server-Template DDL for Oracle 12c

gistfile1.txt

/* DROP STATEMENTS */
DROP TABLE USERS_USER_ROLES;
DROP TABLE USER_ROLES;
DROP TABLE USER_LOGINS;
DROP TABLE USER_CLAIMS;
DROP TABLE USERS;

/* USERS */
CREATE TABLE USERS
(
  ID                    NUMBER GENERATED BY DEFAULT AS IDENTITY,
  USERNAME              VARCHAR2(50)    NOT NULL,
  EMAIL                 VARCHAR2(100),
  EMAIL_CONFIRMED        NUMBER(1)    DEFAULT 0  NOT NULL,
  PASSWORDHASH          VARCHAR2(100),
  SECURITYSTAMP         VARCHAR2(100),
  PHONENUMBER           VARCHAR2(25),
  PHONENUMBER_CONFIRMED  NUMBER(1)    DEFAULT 0  NOT NULL,
  TWOFACTOR_ENABLED      NUMBER(1)    DEFAULT 0  NOT NULL,
  LOCKOUT_ENDDATE_UTC   DATE,
  LOCKOUT_ENABLED       NUMBER(1)    DEFAULT 0  NOT NULL,
  ACCESS_FAILED_COUNT   NUMBER    DEFAULT 0 NOT NULL,
  CONSTRAINT USERS_PK PRIMARY KEY (ID),
  CONSTRAINT USERNAME_UNQ UNIQUE (USERNAME),
  CONSTRAINT EMAIL_CONFIRMED_CHK CHECK (EMAIL_CONFIRMED IN (0, 1)),
  CONSTRAINT PHONENUMBER_CONFIRMED_CHK CHECK (PHONENUMBER_CONFIRMED IN (0, 1)),
  CONSTRAINT TWOFACTOR_ENABLED_CHK CHECK (TWOFACTOR_ENABLED IN (0, 1)),
  CONSTRAINT LOCKOUT_ENABLED_CHK CHECK (LOCKOUT_ENABLED IN (0, 1)) 
);

/* USER_CLAIMS */
CREATE TABLE USER_CLAIMS
(
  ID                    NUMBER GENERATED BY DEFAULT AS IDENTITY,
  USER_ID               NUMBER NOT NULL,
  CLAIM_TYPE            VARCHAR2(4000),
  CLAIM_VALUE           VARCHAR2(4000),
  CONSTRAINT USER_CLAIMS_PK PRIMARY KEY (ID),
  CONSTRAINT USER_CLAIMS_USER_FK FOREIGN KEY (USER_ID) REFERENCES USERS (ID)  ON DELETE CASCADE
);

CREATE INDEX USER_CLAIMS_USER_ID_IDX ON USER_CLAIMS (USER_ID);

/* USER_LOGINS */
CREATE TABLE USER_LOGINS
(
  ID                    NUMBER GENERATED BY DEFAULT AS IDENTITY,
  USER_ID               NUMBER NOT NULL,
  LOGIN_PROVIDER        VARCHAR2(128) NOT NULL,
  PROVIDER_KEY          VARCHAR2(128) NOT NULL,
  CONSTRAINT USER_LOGINS_PK PRIMARY KEY (ID),
  CONSTRAINT USER_LOGINS_UNQ UNIQUE (USER_ID, LOGIN_PROVIDER, PROVIDER_KEY),
  CONSTRAINT USER_LOGINS_USER_FK FOREIGN KEY (USER_ID) REFERENCES USERS (ID)  ON DELETE CASCADE
);

CREATE INDEX USER_LOGINS_USER_ID_IDX ON USER_LOGINS (USER_ID);

/* USER_ROLES */
CREATE TABLE USER_ROLES
(
  ID                    NUMBER GENERATED BY DEFAULT AS IDENTITY,
  NAME                  VARCHAR2(50)    NOT NULL,
  CONSTRAINT USER_ROLES_PK PRIMARY KEY (ID),
  CONSTRAINT USER_ROLES_UNQ UNIQUE (NAME)
);

/* USERS_USER_ROLES */
CREATE TABLE USERS_USER_ROLES
(
  ID                    NUMBER GENERATED BY DEFAULT AS IDENTITY,
  USER_ID               NUMBER NOT NULL,
  USER_ROLE_ID          NUMBER NOT NULL,
  CONSTRAINT USERS_USER_ROLES_PK PRIMARY KEY (ID),
  CONSTRAINT USERS_USER_ROLES_UNQ UNIQUE (USER_ID, USER_ROLE_ID),
  CONSTRAINT USERS_USER_ROLES_USER_FK FOREIGN KEY (USER_ID) REFERENCES USERS (ID)  ON DELETE CASCADE,
  CONSTRAINT USERS_USER_ROLES_USER_ROLE_FK FOREIGN KEY (USER_ROLE_ID) REFERENCES USER_ROLES (ID)  ON DELETE CASCADE 
);

/* REFERENCE DATA */
INSERT ALL
    INTO USERS (ID, USERNAME, EMAIL, PASSWORDHASH, SECURITYSTAMP) VALUES (1, 'admin', '[email protected]', 'ACe+kHUdH61ms8NbkXSCXyV34CEP7tjfj93JrtlKRPfShGurFdAujQrmbVA7J9MDbg==', '9771f91d-b4a0-45e0-8971-899b907c5863')
    INTO USERS (ID, USERNAME, EMAIL, PASSWORDHASH, SECURITYSTAMP) VALUES (2, 'user', '[email protected]', 'ACe+kHUdH61ms8NbkXSCXyV34CEP7tjfj93JrtlKRPfShGurFdAujQrmbVA7J9MDbg==', '9771f91d-b4a0-45e0-8971-899b907c5863')
    INTO USER_ROLES (ID, NAME) VALUES (1, 'Administrator')
    INTO USER_ROLES (ID, NAME) VALUES (2, 'Moderator')
SELECT  1
FROM    dual;

COMMIT;