1 -- ACS Module related tables - START
\r
3 CREATE TABLE IF NOT EXISTS tr069_device
\r
6 DEVICE_ID varchar(30) not null,
\r
7 USER_NAME varchar(256),
\r
8 PASSWORD varchar(256),
\r
9 SW_VERSION varchar(20),
\r
10 HW_VERSION varchar(20),
\r
11 CONNECTION_REQUEST_URL varchar(1024),
\r
12 LAST_UPDATED_TIME Timestamp,
\r
13 LAST_FAILED_ATTEMPT_TIME Timestamp,
\r
14 CONN_STATUS BOOLEAN,
\r
15 ERROR_MESSGAE varchar(256)
\r
19 CREATE TABLE IF NOT EXISTS tr069_nbi_request
\r
22 DEVICE_ID varchar(30) not null,
\r
23 OPERATION_ID bigint unsigned not null,
\r
24 OPERATION_CODE int null,
\r
25 ATTRIBUTE_JSON1 varchar(4000),
\r
26 ATTRIBUTE_JSON2 varchar(4000),
\r
27 ATTRIBUTE_JSON3 varchar(4000),
\r
28 REQUEST_TIME_OUT int,
\r
29 CREATE_TIME DATETIME(6),
\r
30 IS_PROCESSED int DEFAULT 0
\r
34 CREATE TABLE IF NOT EXISTS tr069_session
\r
36 DEVICE_ID varchar(30) not null,
\r
37 SESSION_ID varchar(64) not null,
\r
39 CURRENT_OPERATION_ID bigint unsigned,
\r
40 SESSION_START_TIME TIMESTAMP
\r
43 CREATE SEQUENCE IF NOT EXISTS hibernate_sequence MINVALUE 1 MAXVALUE 4611686018427387903 CYCLE CACHE 10;
\r
45 -- ACS Module related tables - END
\r
47 -- QUARTZ related tables - START
\r
49 CREATE TABLE IF NOT EXISTS QRTZ_JOB_DETAILS
\r
51 SCHED_NAME VARCHAR(120) NOT NULL,
\r
52 JOB_NAME VARCHAR(200) NOT NULL,
\r
53 JOB_GROUP VARCHAR(200) NOT NULL,
\r
54 DESCRIPTION VARCHAR(250) NULL,
\r
55 JOB_CLASS_NAME VARCHAR(250) NOT NULL,
\r
56 IS_DURABLE VARCHAR(1) NOT NULL,
\r
57 IS_NONCONCURRENT VARCHAR(1) NOT NULL,
\r
58 IS_UPDATE_DATA VARCHAR(1) NOT NULL,
\r
59 REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
\r
61 PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
\r
65 CREATE TABLE IF NOT EXISTS QRTZ_TRIGGERS
\r
67 SCHED_NAME VARCHAR(120) NOT NULL,
\r
68 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
69 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
70 JOB_NAME VARCHAR(200) NOT NULL,
\r
71 JOB_GROUP VARCHAR(200) NOT NULL,
\r
72 DESCRIPTION VARCHAR(250) NULL,
\r
73 NEXT_FIRE_TIME BIGINT(13) NULL,
\r
74 PREV_FIRE_TIME BIGINT(13) NULL,
\r
75 PRIORITY INTEGER NULL,
\r
76 TRIGGER_STATE VARCHAR(16) NOT NULL,
\r
77 TRIGGER_TYPE VARCHAR(8) NOT NULL,
\r
78 START_TIME BIGINT(13) NOT NULL,
\r
79 END_TIME BIGINT(13) NULL,
\r
80 CALENDAR_NAME VARCHAR(200) NULL,
\r
81 MISFIRE_INSTR SMALLINT(2) NULL,
\r
83 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
\r
84 FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
\r
85 REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
\r
89 CREATE TABLE IF NOT EXISTS QRTZ_SIMPLE_TRIGGERS
\r
91 SCHED_NAME VARCHAR(120) NOT NULL,
\r
92 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
93 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
94 REPEAT_COUNT BIGINT(7) NOT NULL,
\r
95 REPEAT_INTERVAL BIGINT(12) NOT NULL,
\r
96 TIMES_TRIGGERED BIGINT(10) NOT NULL,
\r
97 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
\r
98 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
99 REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
103 CREATE TABLE IF NOT EXISTS QRTZ_CRON_TRIGGERS
\r
105 SCHED_NAME VARCHAR(120) NOT NULL,
\r
106 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
107 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
108 CRON_EXPRESSION VARCHAR(200) NOT NULL,
\r
109 TIME_ZONE_ID VARCHAR(80),
\r
110 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
\r
111 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
112 REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
116 CREATE TABLE IF NOT EXISTS QRTZ_SIMPROP_TRIGGERS
\r
118 SCHED_NAME VARCHAR(120) NOT NULL,
\r
119 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
120 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
121 STR_PROP_1 VARCHAR(512) NULL,
\r
122 STR_PROP_2 VARCHAR(512) NULL,
\r
123 STR_PROP_3 VARCHAR(512) NULL,
\r
124 INT_PROP_1 INT NULL,
\r
125 INT_PROP_2 INT NULL,
\r
126 LONG_PROP_1 BIGINT NULL,
\r
127 LONG_PROP_2 BIGINT NULL,
\r
128 DEC_PROP_1 NUMERIC(13,4) NULL,
\r
129 DEC_PROP_2 NUMERIC(13,4) NULL,
\r
130 BOOL_PROP_1 VARCHAR(1) NULL,
\r
131 BOOL_PROP_2 VARCHAR(1) NULL,
\r
132 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
\r
133 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
134 REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
138 CREATE TABLE IF NOT EXISTS QRTZ_BLOB_TRIGGERS
\r
140 SCHED_NAME VARCHAR(120) NOT NULL,
\r
141 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
142 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
143 BLOB_DATA BLOB NULL,
\r
144 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
\r
145 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
146 REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
150 CREATE TABLE IF NOT EXISTS QRTZ_CALENDARS
\r
152 SCHED_NAME VARCHAR(120) NOT NULL,
\r
153 CALENDAR_NAME VARCHAR(200) NOT NULL,
\r
154 CALENDAR BLOB NOT NULL,
\r
155 PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
\r
159 CREATE TABLE IF NOT EXISTS QRTZ_PAUSED_TRIGGER_GRPS
\r
161 SCHED_NAME VARCHAR(120) NOT NULL,
\r
162 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
163 PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
\r
167 CREATE TABLE IF NOT EXISTS QRTZ_FIRED_TRIGGERS
\r
169 SCHED_NAME VARCHAR(120) NOT NULL,
\r
170 ENTRY_ID VARCHAR(95) NOT NULL,
\r
171 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
172 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
173 INSTANCE_NAME VARCHAR(200) NOT NULL,
\r
174 FIRED_TIME BIGINT(13) NOT NULL,
\r
175 SCHED_TIME BIGINT(13) NOT NULL,
\r
176 PRIORITY INTEGER NOT NULL,
\r
177 STATE VARCHAR(16) NOT NULL,
\r
178 JOB_NAME VARCHAR(200) NULL,
\r
179 JOB_GROUP VARCHAR(200) NULL,
\r
180 IS_NONCONCURRENT VARCHAR(1) NULL,
\r
181 REQUESTS_RECOVERY VARCHAR(1) NULL,
\r
182 PRIMARY KEY (SCHED_NAME,ENTRY_ID)
\r
186 CREATE TABLE IF NOT EXISTS QRTZ_SCHEDULER_STATE
\r
188 SCHED_NAME VARCHAR(120) NOT NULL,
\r
189 INSTANCE_NAME VARCHAR(200) NOT NULL,
\r
190 LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
\r
191 CHECKIN_INTERVAL BIGINT(13) NOT NULL,
\r
192 PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
\r
196 CREATE TABLE IF NOT EXISTS QRTZ_LOCKS
\r
198 SCHED_NAME VARCHAR(120) NOT NULL,
\r
199 LOCK_NAME VARCHAR(40) NOT NULL,
\r
200 PRIMARY KEY (SCHED_NAME,LOCK_NAME)
\r
203 -- QUARTZ related tables - END
\r
205 -- Create Purge for table _tr069_nbi_request - START
\r
207 CREATE OR REPLACE EVENT purge_tr069_nbi_request
\r
208 ON SCHEDULE EVERY 24 HOUR
\r
210 DELETE FROM tr069_nbi_request WHERE IS_PROCESSED = 1;
\r
212 -- Create Purge for table _tr069_nbi_request - END
\r