Initial source code

[oam/tr069-adapter.git] / acs / application-booter / src / main / resources / schema-mariadb.sql

diff --git a/acs/application-booter/src/main/resources/schema-mariadb.sql b/acs/application-booter/src/main/resources/schema-mariadb.sql
new file mode 100644 (file)
index 0000000..664cb66
--- /dev/null
+++ b/acs/application-booter/src/main/resources/schema-mariadb.sql
@@ -0,0 +1,209 @@
+-- ACS Module related tables - START\r
+\r
+CREATE TABLE IF NOT EXISTS tr069_device \r
+  (\r
+  ID int null,\r
+  DEVICE_ID varchar(30) not null,\r
+  USER_NAME varchar(256),\r
+  PASSWORD varchar(256),\r
+  SW_VERSION varchar(20),\r
+  HW_VERSION varchar(20),\r
+  CONNECTION_REQUEST_URL varchar(1024),\r
+  LAST_UPDATED_TIME Timestamp\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS tr069_nbi_request \r
+  (\r
+  ID int null, \r
+  DEVICE_ID varchar(30) not null, \r
+  OPERATION_ID bigint unsigned not null,\r
+  OPERATION_CODE int null,\r
+  ATTRIBUTE_JSON1 varchar(4000),\r
+  ATTRIBUTE_JSON2 varchar(4000),\r
+  ATTRIBUTE_JSON3 varchar(4000),\r
+  REQUEST_TIME_OUT int,\r
+  CREATE_TIME DATETIME(6),\r
+  IS_PROCESSED int DEFAULT 0\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS tr069_session \r
+  (\r
+  DEVICE_ID varchar(30) not null,\r
+  SESSION_ID varchar(64) not null,\r
+  STATE int,\r
+  CURRENT_OPERATION_ID bigint unsigned,\r
+  SESSION_START_TIME TIMESTAMP\r
+);\r
+\r
+CREATE SEQUENCE IF NOT EXISTS hibernate_sequence MINVALUE 1 MAXVALUE 4611686018427387903 CYCLE CACHE 10;\r
+\r
+-- ACS Module related tables - END\r
+\r
+-- QUARTZ related tables - START\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_JOB_DETAILS\r
+       (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    JOB_NAME  VARCHAR(200) NOT NULL,\r
+    JOB_GROUP VARCHAR(200) NOT NULL,\r
+    DESCRIPTION VARCHAR(250) NULL,\r
+    JOB_CLASS_NAME   VARCHAR(250) NOT NULL,\r
+    IS_DURABLE VARCHAR(1) NOT NULL,\r
+    IS_NONCONCURRENT VARCHAR(1) NOT NULL,\r
+    IS_UPDATE_DATA VARCHAR(1) NOT NULL,\r
+    REQUESTS_RECOVERY VARCHAR(1) NOT NULL,\r
+    JOB_DATA BLOB NULL,\r
+    PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_TRIGGERS\r
+  (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    TRIGGER_NAME VARCHAR(200) NOT NULL,\r
+    TRIGGER_GROUP VARCHAR(200) NOT NULL,\r
+    JOB_NAME  VARCHAR(200) NOT NULL,\r
+    JOB_GROUP VARCHAR(200) NOT NULL,\r
+    DESCRIPTION VARCHAR(250) NULL,\r
+    NEXT_FIRE_TIME BIGINT(13) NULL,\r
+    PREV_FIRE_TIME BIGINT(13) NULL,\r
+    PRIORITY INTEGER NULL,\r
+    TRIGGER_STATE VARCHAR(16) NOT NULL,\r
+    TRIGGER_TYPE VARCHAR(8) NOT NULL,\r
+    START_TIME BIGINT(13) NOT NULL,\r
+    END_TIME BIGINT(13) NULL,\r
+    CALENDAR_NAME VARCHAR(200) NULL,\r
+    MISFIRE_INSTR SMALLINT(2) NULL,\r
+    JOB_DATA BLOB NULL,\r
+    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),\r
+    FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)\r
+        REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_SIMPLE_TRIGGERS\r
+  (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    TRIGGER_NAME VARCHAR(200) NOT NULL,\r
+    TRIGGER_GROUP VARCHAR(200) NOT NULL,\r
+    REPEAT_COUNT BIGINT(7) NOT NULL,\r
+    REPEAT_INTERVAL BIGINT(12) NOT NULL,\r
+    TIMES_TRIGGERED BIGINT(10) NOT NULL,\r
+    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),\r
+    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)\r
+        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_CRON_TRIGGERS\r
+  (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    TRIGGER_NAME VARCHAR(200) NOT NULL,\r
+    TRIGGER_GROUP VARCHAR(200) NOT NULL,\r
+    CRON_EXPRESSION VARCHAR(200) NOT NULL,\r
+    TIME_ZONE_ID VARCHAR(80),\r
+    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),\r
+    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)\r
+        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_SIMPROP_TRIGGERS\r
+  (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    TRIGGER_NAME VARCHAR(200) NOT NULL,\r
+    TRIGGER_GROUP VARCHAR(200) NOT NULL,\r
+    STR_PROP_1 VARCHAR(512) NULL,\r
+    STR_PROP_2 VARCHAR(512) NULL,\r
+    STR_PROP_3 VARCHAR(512) NULL,\r
+    INT_PROP_1 INT NULL,\r
+    INT_PROP_2 INT NULL,\r
+    LONG_PROP_1 BIGINT NULL,\r
+    LONG_PROP_2 BIGINT NULL,\r
+    DEC_PROP_1 NUMERIC(13,4) NULL,\r
+    DEC_PROP_2 NUMERIC(13,4) NULL,\r
+    BOOL_PROP_1 VARCHAR(1) NULL,\r
+    BOOL_PROP_2 VARCHAR(1) NULL,\r
+    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),\r
+    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)\r
+    REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_BLOB_TRIGGERS\r
+  (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    TRIGGER_NAME VARCHAR(200) NOT NULL,\r
+    TRIGGER_GROUP VARCHAR(200) NOT NULL,\r
+    BLOB_DATA BLOB NULL,\r
+    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),\r
+    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)\r
+        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_CALENDARS\r
+  (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    CALENDAR_NAME  VARCHAR(200) NOT NULL,\r
+    CALENDAR BLOB NOT NULL,\r
+    PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_PAUSED_TRIGGER_GRPS\r
+  (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    TRIGGER_GROUP  VARCHAR(200) NOT NULL,\r
+    PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_FIRED_TRIGGERS\r
+  (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    ENTRY_ID VARCHAR(95) NOT NULL,\r
+    TRIGGER_NAME VARCHAR(200) NOT NULL,\r
+    TRIGGER_GROUP VARCHAR(200) NOT NULL,\r
+    INSTANCE_NAME VARCHAR(200) NOT NULL,\r
+    FIRED_TIME BIGINT(13) NOT NULL,\r
+    SCHED_TIME BIGINT(13) NOT NULL,\r
+    PRIORITY INTEGER NOT NULL,\r
+    STATE VARCHAR(16) NOT NULL,\r
+    JOB_NAME VARCHAR(200) NULL,\r
+    JOB_GROUP VARCHAR(200) NULL,\r
+    IS_NONCONCURRENT VARCHAR(1) NULL,\r
+    REQUESTS_RECOVERY VARCHAR(1) NULL,\r
+    PRIMARY KEY (SCHED_NAME,ENTRY_ID)\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_SCHEDULER_STATE\r
+  (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    INSTANCE_NAME VARCHAR(200) NOT NULL,\r
+    LAST_CHECKIN_TIME BIGINT(13) NOT NULL,\r
+    CHECKIN_INTERVAL BIGINT(13) NOT NULL,\r
+    PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)\r
+);\r
+\r
+\r
+CREATE TABLE IF NOT EXISTS QRTZ_LOCKS\r
+  (\r
+    SCHED_NAME VARCHAR(120) NOT NULL,\r
+    LOCK_NAME  VARCHAR(40) NOT NULL,\r
+    PRIMARY KEY (SCHED_NAME,LOCK_NAME)\r
+);\r
+\r
+-- QUARTZ related tables - END\r
+\r
+-- Create Purge for table _tr069_nbi_request - START\r
+\r
+CREATE OR REPLACE EVENT purge_tr069_nbi_request\r
+ON SCHEDULE EVERY 24 HOUR\r
+DO\r
+DELETE FROM tr069_nbi_request WHERE IS_PROCESSED = 1;\r
+\r
+-- Create Purge for table _tr069_nbi_request - END\r