script.sql

-- TO USE IDENTITY WE HAVE TO WORK WITH ANY ORACLE VERSION FROM 12C 

CREATE TABLE READERS (
    ID  NUMBER(8,0) GENERATED BY DEFAULT ON NULL AS IDENTITY,
    NAME VARCHAR2(8) NOT NULL,
    CONSTRAINT READERS_PK PRIMARY KEY (ID)
);

/
CREATE TABLE BLOGS (
    ID NUMBER (8,0) GENERATED BY DEFAULT ON NULL AS IDENTITY,
    TITLE VARCHAR2(50) NOT NULL,
    DESCRIPTION VARCHAR2(4000) NOT NULL,
    CONSTRAINT BLOGS_PK PRIMARY KEY (ID)
);

/
CREATE TABLE BLOGS_READERS(
    R_ID NUMBER(8,0),
    B_ID NUMBER(8,0),
    CONSTRAINT TABLE_3_BLOGS_FK FOREIGN KEY (B_ID) REFERENCES BLOGS(ID),
    CONSTRAINT TABLE_3_READERS_FK FOREIGN KEY (R_ID) REFERENCES READERS(ID)
);

/
CREATE TABLE APPLICATION_USER(
    ID  NUMBER(8,0) GENERATED BY DEFAULT ON NULL AS IDENTITY,
    NAME VARCHAR2(50) NOT NULL,
    USERNAME VARCHAR2(50) NOT NULL,
    PASSWORD VARCHAR2(300) NOT NULL,
    SESSIONCODE VARCHAR2(50),
    STATUS VARCHAR2(20),
    CONSTRAINT APPLICATION_USER_PK PRIMARY KEY (ID),
    CONSTRAINT UNIQUE_FIELD_USERNAME UNIQUE (USERNAME)
);

/
CREATE TABLE APPLICATION_ROLE(
    ID  NUMBER(8,0) GENERATED BY DEFAULT ON NULL AS IDENTITY,
    ROLENAME VARCHAR2(50) NOT NULL,
    CONSTRAINT APPLICATION_ROLE_PK PRIMARY KEY (ID),
    CONSTRAINT UNIQUE_FIELD_ROLENAME UNIQUE (ROLENAME)
);

/
CREATE TABLE USER_ROLE(
    U_ID NUMBER(8,0),
    R_ID NUMBER(8,0),
    CONSTRAINT TABLE_3_USERS_FK FOREIGN KEY (R_ID) REFERENCES APPLICATION_USER(ID),
    CONSTRAINT TABLE_3_ROLES_FK FOREIGN KEY (R_ID) REFERENCES APPLICATION_ROLE(ID)
);

SET DEFINE OFF;
Insert into BANDESALBLOG.APPLICATION_USER (ID,NAME,USERNAME,PASSWORD,SESSIONCODE,STATUS) values (1,'Isaac Molina','IMOLINA','$2a$15$mVc/wOfoh0ZoS08ePU37Iu9E5F9vY4f1IVshfhiq0xt200dj6iZw2',null,'ALTA');
Insert into BANDESALBLOG.APPLICATION_ROLE (ID,ROLENAME) values (1,'ADMIN');
Insert into BANDESALBLOG.USER_ROLE (U_ID,R_ID) values (1,1);