script.sql

-- 1.0.0

-- TYPES

CREATE TYPE T_NUMBER_20 IS TABLE OF NUMBER(20);
CREATE TYPE T_VARCHAR2_16 IS TABLE OF VARCHAR2(16);
CREATE TYPE T_VARCHAR2_36 IS TABLE OF VARCHAR2(36);
CREATE TYPE T_VARCHAR2_256 IS TABLE OF VARCHAR2(256);
CREATE TYPE T_VARCHAR2_4000 IS TABLE OF VARCHAR2(4000);

-- CHANGE

CREATE TABLE CHANGES (
  CODE    VARCHAR2(32) NOT NULL,
  CHANGED TIMESTAMP    NOT NULL
);

CREATE UNIQUE INDEX CHANGES_CODE_I
  ON CHANGES (CODE);

CREATE PROCEDURE P_CHANGED(code VARCHAR2) AS
  BEGIN
    UPDATE CHANGES
    SET CHANGED = sys_extract_utc(systimestamp)
    WHERE CODE = code;
    IF SQL%ROWCOUNT = 0
    THEN
      INSERT INTO CHANGES (CODE, CHANGED) VALUES (code, sys_extract_utc(systimestamp));
    END IF;
  END;

-- SETTINGS

CREATE TABLE SETTINGS (
  CODE  VARCHAR2(256) NOT NULL,
  VALUE VARCHAR2(256) NOT NULL
);

ALTER TABLE SETTINGS
  ADD CONSTRAINT SETTINGS_PK PRIMARY KEY (CODE)
  USING INDEX;

CREATE PROCEDURE P_SAVE_SETTING(code VARCHAR2, value VARCHAR2) AS
  BEGIN
    UPDATE SETTINGS
    SET VALUE = value
    WHERE CODE = code;
    IF SQL%ROWCOUNT = 0
    THEN
      INSERT INTO SETTINGS (CODE, VALUE) VALUES (code, value);
    END IF;
  END;

-- SYSTEM

CREATE TABLE SYSTEM (
  ID          NUMBER(20)                                      NOT NULL,
  CREATETIME  TIMESTAMP DEFAULT sys_extract_utc(systimestamp) NOT NULL,
  CODE        VARCHAR2(32)                                    NOT NULL,
  NAME        VARCHAR2(256)                                   NOT NULL,
  DESCRIPTION VARCHAR2(4000)                                  NULL,
  LOCKED      VARCHAR2(1) DEFAULT 'N'                         NOT NULL
);

ALTER TABLE SYSTEM
  ADD CONSTRAINT SYSTEM_PK PRIMARY KEY (ID)
  USING INDEX;

ALTER TABLE SYSTEM
  ADD CONSTRAINT SYSTEM_LOCKED_CH CHECK (LOCKED IN ('Y', 'N'));

CREATE SEQUENCE SYSTEM_SEQ START WITH 1;

CREATE TRIGGER SYSTEM_T
BEFORE INSERT ON SYSTEM
FOR EACH ROW
  BEGIN
    SELECT SYSTEM_SEQ.nextval
    INTO :new.ID
    FROM dual;
  END;

CREATE TRIGGER SYSTEM_CHANGES_T
AFTER INSERT OR UPDATE OR DELETE ON SYSTEM
  BEGIN
    P_CHANGED('SYSTEM');
  END;

-- APPLICATION

CREATE TABLE APPLICATION (
  ID           NUMBER(20)                                      NOT NULL,
  CREATETIME   TIMESTAMP DEFAULT sys_extract_utc(systimestamp) NOT NULL,
  PNS          VARCHAR2(8)                                     NOT NULL,
  PACKAGE_NAME VARCHAR2(256)                                   NOT NULL
);

ALTER TABLE APPLICATION
  ADD CONSTRAINT APPLICATION_PK PRIMARY KEY (ID)
  USING INDEX;

ALTER TABLE APPLICATION
  ADD CONSTRAINT APPLICATION_PNS_CH CHECK (PNS IN ('FCM', 'APNs', 'WNS', 'MPNS'));

CREATE UNIQUE INDEX APPLICATION_OS_PN_I
  ON APPLICATION (PNS, PACKAGE_NAME);

CREATE SEQUENCE APPLICATION_SEQ START WITH 1;

CREATE TRIGGER APPLICATION_T
BEFORE INSERT ON APPLICATION
FOR EACH ROW
  BEGIN
    SELECT APPLICATION_SEQ.nextval
    INTO :new.ID
    FROM dual;
  END;

CREATE TABLE APPLICATION_FCM (
  ID         NUMBER(20)   NOT NULL,
  SERVER_KEY VARCHAR2(64) NOT NULL
);

ALTER TABLE APPLICATION_FCM
  ADD CONSTRAINT APPLICATION_FCM_FK
FOREIGN KEY (ID) REFERENCES APPLICATION (ID) ON DELETE CASCADE;

CREATE TRIGGER APPLICATION_CHANGES_T
AFTER INSERT OR UPDATE OR DELETE ON APPLICATION
  BEGIN
    P_CHANGED('APPLICATION');
  END;

CREATE TRIGGER APPLICATION_FCM_CHANGES_T
AFTER INSERT OR UPDATE OR DELETE ON APPLICATION_FCM
  BEGIN
    P_CHANGED('APPLICATION');
  END;

-- SYSTEM_APPLICATION

CREATE TABLE SYSTEM_APPLICATION (
  SYSTEM_ID      NUMBER(20) NOT NULL,
  APPLICATION_ID NUMBER(20) NOT NULL
);

ALTER TABLE SYSTEM_APPLICATION
  ADD CONSTRAINT SA_SYSTEM_FK
FOREIGN KEY (SYSTEM_ID) REFERENCES SYSTEM (ID) ON DELETE CASCADE;

ALTER TABLE SYSTEM_APPLICATION
  ADD CONSTRAINT SA_APPLICATION_FK
FOREIGN KEY (APPLICATION_ID) REFERENCES APPLICATION (ID) ON DELETE CASCADE;

CREATE TRIGGER SYSTEM_APPLICATION_CHANGES_T
AFTER INSERT OR UPDATE OR DELETE ON SYSTEM_APPLICATION
  BEGIN
    P_CHANGEG('APPLICATION');
  END;

-- PUSH

CREATE TABLE PUSH (
  ID             NUMBER(20)                                      NOT NULL,
  CREATETIME     TIMESTAMP DEFAULT sys_extract_utc(systimestamp) NOT NULL,
  SYSTEM_ID      NUMBER(20)                                      NOT NULL,
  EXT_ID         VARCHAR2(36)                                    NOT NULL,
  APPLICATION_ID NUMBER(20)                                      NOT NULL,
  TOKEN          VARCHAR2(256)                                   NULL,
  TITLE          VARCHAR2(256)                                   NULL,
  BODY           VARCHAR2(4000)                                  NULL,
  ICON           VARCHAR2(16)                                    NULL,
  STATE          VARCHAR2(8)                                     NOT NULL,
  PNS_ID         VARCHAR2(36)                                    NULL,
  PNS_ERROR      VARCHAR2(32)                                    NULL
);

ALTER TABLE PUSH
  ADD CONSTRAINT PUSH_PK PRIMARY KEY (ID)
  USING INDEX;

CREATE UNIQUE INDEX PUSH_SYSTEM_EXT_I
  ON PUSH (SYSTEM_ID, EXT_ID);

ALTER TABLE PUSH
  ADD CONSTRAINT PUSH_SYSTEM_FK
FOREIGN KEY (SYSTEM_ID) REFERENCES SYSTEM (ID);

ALTER TABLE PUSH
  ADD CONSTRAINT PUSH_APPLICATION_FK
FOREIGN KEY (APPLICATION_ID) REFERENCES APPLICATION (ID);

CREATE SEQUENCE PUSH_SEQ START WITH 1;

CREATE TRIGGER PUSH_T
BEFORE INSERT ON PUSH
FOR EACH ROW
  BEGIN
    SELECT PUSH_SEQ.nextval
    INTO :new.ID
    FROM dual;
  END;

CREATE PROCEDURE P_INSERT_PUSH(id OUT         NUMBER,
                               application_id NUMBER,
                               token          VARCHAR2,
                               title          VARCHAR2,
                               body           VARCHAR2,
                               icon           VARCHAR2,
                               system_id      NUMBER,
                               ext_id         VARCHAR2
)
AS
  BEGIN
    INSERT INTO PUSH (APPLICATION_ID, TOKEN, TITLE, BODY, ICON, SYSTEM_ID, EXT_ID)
    VALUES (application_id, token, title, body, icon, system_id, ext_id)
    RETURNING ID INTO id;
  END;

CREATE PROCEDURE P_INSERT_PUSHES(id OUT         T_NUMBER_20,
                                 application_id T_NUMBER_20,
                                 token          T_VARCHAR2_256,
                                 title          T_VARCHAR2_256,
                                 body           T_VARCHAR2_4000,
                                 icon           T_VARCHAR2_16,
                                 system_id      T_NUMBER_20,
                                 ext_id         T_VARCHAR2_36)
AS
  BEGIN
    id := T_NUMBER_20();
    id.EXTEND(application_id.LAST);

    FOR i IN application_id.FIRST .. application_id.LAST
    LOOP
      P_INSERT_PUSH(id(i), application_id(i), token(i), title(i), body(i), icon(i), system_id(i), ext_id(i));
    END LOOP;
  END;