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
16 CREATE TABLE IF NOT EXISTS tr069_nbi_request
\r
19 DEVICE_ID varchar(30) not null,
\r
20 OPERATION_ID bigint unsigned not null,
\r
21 OPERATION_CODE int null,
\r
22 ATTRIBUTE_JSON1 varchar(4000),
\r
23 ATTRIBUTE_JSON2 varchar(4000),
\r
24 ATTRIBUTE_JSON3 varchar(4000),
\r
25 REQUEST_TIME_OUT int,
\r
26 CREATE_TIME DATETIME(6),
\r
27 IS_PROCESSED int DEFAULT 0
\r
31 CREATE TABLE IF NOT EXISTS tr069_session
\r
33 DEVICE_ID varchar(30) not null,
\r
34 SESSION_ID varchar(64) not null,
\r
36 CURRENT_OPERATION_ID bigint unsigned,
\r
37 SESSION_START_TIME TIMESTAMP
\r
40 CREATE SEQUENCE IF NOT EXISTS hibernate_sequence MINVALUE 1 MAXVALUE 4611686018427387903 CYCLE CACHE 10;
\r
42 -- ACS Module related tables - END
\r
44 -- QUARTZ related tables - START
\r
46 CREATE TABLE IF NOT EXISTS QRTZ_JOB_DETAILS
\r
48 SCHED_NAME VARCHAR(120) NOT NULL,
\r
49 JOB_NAME VARCHAR(200) NOT NULL,
\r
50 JOB_GROUP VARCHAR(200) NOT NULL,
\r
51 DESCRIPTION VARCHAR(250) NULL,
\r
52 JOB_CLASS_NAME VARCHAR(250) NOT NULL,
\r
53 IS_DURABLE VARCHAR(1) NOT NULL,
\r
54 IS_NONCONCURRENT VARCHAR(1) NOT NULL,
\r
55 IS_UPDATE_DATA VARCHAR(1) NOT NULL,
\r
56 REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
\r
58 PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
\r
62 CREATE TABLE IF NOT EXISTS QRTZ_TRIGGERS
\r
64 SCHED_NAME VARCHAR(120) NOT NULL,
\r
65 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
66 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
67 JOB_NAME VARCHAR(200) NOT NULL,
\r
68 JOB_GROUP VARCHAR(200) NOT NULL,
\r
69 DESCRIPTION VARCHAR(250) NULL,
\r
70 NEXT_FIRE_TIME BIGINT(13) NULL,
\r
71 PREV_FIRE_TIME BIGINT(13) NULL,
\r
72 PRIORITY INTEGER NULL,
\r
73 TRIGGER_STATE VARCHAR(16) NOT NULL,
\r
74 TRIGGER_TYPE VARCHAR(8) NOT NULL,
\r
75 START_TIME BIGINT(13) NOT NULL,
\r
76 END_TIME BIGINT(13) NULL,
\r
77 CALENDAR_NAME VARCHAR(200) NULL,
\r
78 MISFIRE_INSTR SMALLINT(2) NULL,
\r
80 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
\r
81 FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
\r
82 REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
\r
86 CREATE TABLE IF NOT EXISTS QRTZ_SIMPLE_TRIGGERS
\r
88 SCHED_NAME VARCHAR(120) NOT NULL,
\r
89 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
90 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
91 REPEAT_COUNT BIGINT(7) NOT NULL,
\r
92 REPEAT_INTERVAL BIGINT(12) NOT NULL,
\r
93 TIMES_TRIGGERED BIGINT(10) NOT NULL,
\r
94 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
\r
95 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
96 REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
100 CREATE TABLE IF NOT EXISTS QRTZ_CRON_TRIGGERS
\r
102 SCHED_NAME VARCHAR(120) NOT NULL,
\r
103 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
104 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
105 CRON_EXPRESSION VARCHAR(200) NOT NULL,
\r
106 TIME_ZONE_ID VARCHAR(80),
\r
107 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
\r
108 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
109 REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
113 CREATE TABLE IF NOT EXISTS QRTZ_SIMPROP_TRIGGERS
\r
115 SCHED_NAME VARCHAR(120) NOT NULL,
\r
116 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
117 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
118 STR_PROP_1 VARCHAR(512) NULL,
\r
119 STR_PROP_2 VARCHAR(512) NULL,
\r
120 STR_PROP_3 VARCHAR(512) NULL,
\r
121 INT_PROP_1 INT NULL,
\r
122 INT_PROP_2 INT NULL,
\r
123 LONG_PROP_1 BIGINT NULL,
\r
124 LONG_PROP_2 BIGINT NULL,
\r
125 DEC_PROP_1 NUMERIC(13,4) NULL,
\r
126 DEC_PROP_2 NUMERIC(13,4) NULL,
\r
127 BOOL_PROP_1 VARCHAR(1) NULL,
\r
128 BOOL_PROP_2 VARCHAR(1) NULL,
\r
129 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
\r
130 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
131 REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
135 CREATE TABLE IF NOT EXISTS QRTZ_BLOB_TRIGGERS
\r
137 SCHED_NAME VARCHAR(120) NOT NULL,
\r
138 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
139 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
140 BLOB_DATA BLOB NULL,
\r
141 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
\r
142 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
143 REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
\r
147 CREATE TABLE IF NOT EXISTS QRTZ_CALENDARS
\r
149 SCHED_NAME VARCHAR(120) NOT NULL,
\r
150 CALENDAR_NAME VARCHAR(200) NOT NULL,
\r
151 CALENDAR BLOB NOT NULL,
\r
152 PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
\r
156 CREATE TABLE IF NOT EXISTS QRTZ_PAUSED_TRIGGER_GRPS
\r
158 SCHED_NAME VARCHAR(120) NOT NULL,
\r
159 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
160 PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
\r
164 CREATE TABLE IF NOT EXISTS QRTZ_FIRED_TRIGGERS
\r
166 SCHED_NAME VARCHAR(120) NOT NULL,
\r
167 ENTRY_ID VARCHAR(95) NOT NULL,
\r
168 TRIGGER_NAME VARCHAR(200) NOT NULL,
\r
169 TRIGGER_GROUP VARCHAR(200) NOT NULL,
\r
170 INSTANCE_NAME VARCHAR(200) NOT NULL,
\r
171 FIRED_TIME BIGINT(13) NOT NULL,
\r
172 SCHED_TIME BIGINT(13) NOT NULL,
\r
173 PRIORITY INTEGER NOT NULL,
\r
174 STATE VARCHAR(16) NOT NULL,
\r
175 JOB_NAME VARCHAR(200) NULL,
\r
176 JOB_GROUP VARCHAR(200) NULL,
\r
177 IS_NONCONCURRENT VARCHAR(1) NULL,
\r
178 REQUESTS_RECOVERY VARCHAR(1) NULL,
\r
179 PRIMARY KEY (SCHED_NAME,ENTRY_ID)
\r
183 CREATE TABLE IF NOT EXISTS QRTZ_SCHEDULER_STATE
\r
185 SCHED_NAME VARCHAR(120) NOT NULL,
\r
186 INSTANCE_NAME VARCHAR(200) NOT NULL,
\r
187 LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
\r
188 CHECKIN_INTERVAL BIGINT(13) NOT NULL,
\r
189 PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
\r
193 CREATE TABLE IF NOT EXISTS QRTZ_LOCKS
\r
195 SCHED_NAME VARCHAR(120) NOT NULL,
\r
196 LOCK_NAME VARCHAR(40) NOT NULL,
\r
197 PRIMARY KEY (SCHED_NAME,LOCK_NAME)
\r
200 -- QUARTZ related tables - END
\r
202 -- Create Purge for table _tr069_nbi_request - START
\r
204 CREATE OR REPLACE EVENT purge_tr069_nbi_request
\r
205 ON SCHEDULE EVERY 24 HOUR
\r
207 DELETE FROM tr069_nbi_request WHERE IS_PROCESSED = 1;
\r
209 -- Create Purge for table _tr069_nbi_request - END
\r