Created
February 20, 2016 20:15
-
-
Save Entalyan/c7db5160b8c1bbd9b353 to your computer and use it in GitHub Desktop.
AspNet-Server-Template DDL for Oracle 11g
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment