AspNet-Server-Template DDL for Oracle 11g

gistfile1.txt

/* DROP STATEMENTS */
DROP TABLE USER_ROLES;
DROP TABLE USER_LOGINS;
DROP TABLE USER_CLAIMS;
DROP TABLE USERS;
DROP SEQUENCE USER_ROLES_SEQ;
DROP SEQUENCE USER_LOGINS_SEQ;
DROP SEQUENCE USER_CLAIMS_SEQ;
DROP SEQUENCE USERS_SEQ;

/* USERS */
CREATE TABLE USERS
(
   ID                      NUMBER NOT NULL,
   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))
);

CREATE SEQUENCE USERS_SEQ;

CREATE TRIGGER USERS_BIR
   BEFORE INSERT
   ON USERS
   FOR EACH ROW
   WHEN (new.id IS NULL)
BEGIN
   :new.id := USERS_SEQ.NEXTVAL;
END;

/* USER_CLAIMS */
CREATE TABLE USER_CLAIMS
(
   ID            NUMBER NOT NULL,
   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);

CREATE SEQUENCE USER_CLAIMS_SEQ;

CREATE TRIGGER USER_CLAIMS_BIR
   BEFORE INSERT
   ON USER_CLAIMS
   FOR EACH ROW
   WHEN (new.id IS NULL)
BEGIN
   :new.id := USER_CLAIMS_SEQ.NEXTVAL;
END;

/* USER_LOGINS */
CREATE TABLE USER_LOGINS
(
   ID               NUMBER NOT NULL,
   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);

CREATE SEQUENCE USER_LOGINS_SEQ;

CREATE TRIGGER USER_LOGINS_BIR
   BEFORE INSERT
   ON USER_LOGINS
   FOR EACH ROW
   WHEN (new.id IS NULL)
BEGIN
   :new.id := USER_LOGINS_SEQ.NEXTVAL;
END;

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

CREATE SEQUENCE USER_ROLES_SEQ;

CREATE TRIGGER USER_ROLES_BIR
   BEFORE INSERT
   ON USER_ROLES
   FOR EACH ROW
   WHEN (new.id IS NULL)
BEGIN
   :new.id := USER_ROLES_SEQ.NEXTVAL;
END;

/* REFERENCE DATA */
INSERT ALL
  INTO USERS (ID,
              USERNAME,
              EMAIL,
              PASSWORDHASH,
              SECURITYSTAMP)
VALUES (1,
        'admin',
        'admin@example.com',
        'ACe+kHUdH61ms8NbkXSCXyV34CEP7tjfj93JrtlKRPfShGurFdAujQrmbVA7J9MDbg==',
        '9771f91d-b4a0-45e0-8971-899b907c5863')
  INTO USERS (ID,
              USERNAME,
              EMAIL,
              PASSWORDHASH,
              SECURITYSTAMP)
VALUES (2,
        'user',
        'user@example.com',
        '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;