1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
|
CREATE TABLE FEEDS (
FEEDID INT UNSIGNED NOT NULL PRIMARY KEY,
GROUPID INT(10) UNSIGNED NOT NULL DEFAULT 0,
NAME VARCHAR(255) NOT NULL,
VERSION VARCHAR(20) NOT NULL,
DESCRIPTION VARCHAR(1000),
BUSINESS_DESCRIPTION VARCHAR(1000) DEFAULT NULL,
AUTH_CLASS VARCHAR(32) NOT NULL,
PUBLISHER VARCHAR(8) NOT NULL,
SELF_LINK VARCHAR(256),
PUBLISH_LINK VARCHAR(256),
SUBSCRIBE_LINK VARCHAR(256),
LOG_LINK VARCHAR(256),
DELETED BOOLEAN DEFAULT FALSE,
LAST_MOD TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
SUSPENDED BOOLEAN DEFAULT FALSE,
CREATED_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE FEED_ENDPOINT_IDS (
FEEDID INT UNSIGNED NOT NULL,
USERID VARCHAR(20) NOT NULL,
PASSWORD VARCHAR(32) NOT NULL
);
CREATE TABLE FEED_ENDPOINT_ADDRS (
FEEDID INT UNSIGNED NOT NULL,
ADDR VARCHAR(44) NOT NULL
);
CREATE TABLE SUBSCRIPTIONS (
SUBID INT UNSIGNED NOT NULL PRIMARY KEY,
FEEDID INT UNSIGNED NOT NULL,
GROUPID INT(10) UNSIGNED NOT NULL DEFAULT 0,
DELIVERY_URL VARCHAR(256),
DELIVERY_USER VARCHAR(20),
DELIVERY_PASSWORD VARCHAR(32),
DELIVERY_USE100 BOOLEAN DEFAULT FALSE,
METADATA_ONLY BOOLEAN DEFAULT FALSE,
SUBSCRIBER VARCHAR(8) NOT NULL,
SELF_LINK VARCHAR(256),
LOG_LINK VARCHAR(256),
LAST_MOD TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
SUSPENDED BOOLEAN DEFAULT FALSE,
PRIVILEGED_SUBSCRIBER BOOLEAN DEFAULT FALSE,
DECOMPRESS BOOLEAN DEFAULT FALSE,
CREATED_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE PARAMETERS (
KEYNAME VARCHAR(32) NOT NULL PRIMARY KEY,
VALUE VARCHAR(4096) NOT NULL
);
CREATE TABLE LOG_RECORDS (
TYPE ENUM('pub', 'del', 'exp', 'pbf', 'dlx') NOT NULL,
EVENT_TIME BIGINT NOT NULL, /* time of the publish request */
PUBLISH_ID VARCHAR(64) NOT NULL, /* unique ID assigned to this publish attempt */
FEEDID INT UNSIGNED NOT NULL, /* pointer to feed in FEEDS */
REQURI VARCHAR(256) NOT NULL, /* request URI */
METHOD ENUM('DELETE', 'GET', 'HEAD', 'OPTIONS', 'PUT', 'POST', 'TRACE') NOT NULL, /* HTTP method */
CONTENT_TYPE VARCHAR(256) NOT NULL, /* content type of published file */
CONTENT_LENGTH BIGINT NOT NULL, /* content length of published file */
FEED_FILEID VARCHAR(256), /* file ID of published file */
REMOTE_ADDR VARCHAR(40), /* IP address of publishing endpoint */
USER VARCHAR(50), /* user name of publishing endpoint */
STATUS SMALLINT, /* status code returned to delivering agent */
DELIVERY_SUBID INT UNSIGNED, /* pointer to subscription in SUBSCRIPTIONS */
DELIVERY_FILEID VARCHAR(256), /* file ID of file being delivered */
RESULT SMALLINT, /* result received from subscribing agent */
ATTEMPTS INT, /* deliveries attempted */
REASON ENUM('notRetryable', 'retriesExhausted', 'diskFull', 'other'),
RECORD_ID BIGINT UNSIGNED NOT NULL PRIMARY KEY, /* unique ID for this record */
CONTENT_LENGTH_2 BIGINT,
FILENAME VARCHAR(256), /* Name of the file being published on DR */
INDEX (FEEDID) USING BTREE,
INDEX (DELIVERY_SUBID) USING BTREE,
INDEX (RECORD_ID) USING BTREE
) ENGINE = MyISAM;
CREATE TABLE INGRESS_ROUTES (
SEQUENCE INT UNSIGNED NOT NULL,
FEEDID INT UNSIGNED NOT NULL,
USERID VARCHAR(20),
SUBNET VARCHAR(44),
NODESET INT UNSIGNED NOT NULL
);
CREATE TABLE EGRESS_ROUTES (
SUBID INT UNSIGNED NOT NULL PRIMARY KEY,
NODEID INT UNSIGNED NOT NULL
);
CREATE TABLE NETWORK_ROUTES (
FROMNODE INT UNSIGNED NOT NULL,
TONODE INT UNSIGNED NOT NULL,
VIANODE INT UNSIGNED NOT NULL
);
CREATE TABLE NODESETS (
SETID INT UNSIGNED NOT NULL,
NODEID INT UNSIGNED NOT NULL
);
CREATE TABLE NODES (
NODEID INT UNSIGNED NOT NULL PRIMARY KEY,
NAME VARCHAR(255) NOT NULL,
ACTIVE BOOLEAN DEFAULT TRUE
);
CREATE TABLE GROUPS (
GROUPID INT UNSIGNED NOT NULL PRIMARY KEY,
AUTHID VARCHAR(100) NOT NULL,
NAME VARCHAR(50) NOT NULL,
DESCRIPTION VARCHAR(255),
CLASSIFICATION VARCHAR(20) NOT NULL,
MEMBERS TINYTEXT,
LAST_MOD TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO PARAMETERS VALUES
('ACTIVE_POD', 'dmaap-dr-prov'),
('PROV_ACTIVE_NAME', 'dmaap-dr-prov'),
('STANDBY_POD', ''),
('PROV_NAME', 'dmaap-dr-prov'),
('NODES', 'dmaap-dr-node'),
('PROV_DOMAIN', ''),
('DELIVERY_INIT_RETRY_INTERVAL', '10'),
('DELIVERY_MAX_AGE', '86400'),
('DELIVERY_MAX_RETRY_INTERVAL', '3600'),
('DELIVERY_FILE_PROCESS_INTERVAL', '600'),
('DELIVERY_RETRY_RATIO', '2'),
('LOGROLL_INTERVAL', '300'),
('PROV_AUTH_ADDRESSES', 'dmaap-dr-prov|dmaap-dr-node'),
('PROV_AUTH_SUBJECTS', ''),
('PROV_MAXFEED_COUNT', '10000'),
('PROV_MAXSUB_COUNT', '100000'),
('PROV_REQUIRE_CERT', 'false'),
('PROV_REQUIRE_SECURE', 'true'),
('_INT_VALUES', 'LOGROLL_INTERVAL|PROV_MAXFEED_COUNT|PROV_MAXSUB_COUNT|DELIVERY_INIT_RETRY_INTERVAL|DELIVERY_MAX_RETRY_INTERVAL|DELIVERY_RETRY_RATIO|DELIVERY_MAX_AGE|DELIVERY_FILE_PROCESS_INTERVAL')
;
INSERT INTO GROUPS(GROUPID, AUTHID, NAME, DESCRIPTION, CLASSIFICATION, MEMBERS)
VALUES (1, 'Basic dXNlcjE6cGFzc3dvcmQx', 'Group1', 'First Group for testing', 'Class1', 'Member1');
INSERT INTO SUBSCRIPTIONS(SUBID, FEEDID, DELIVERY_URL, DELIVERY_USER, DELIVERY_PASSWORD, DELIVERY_USE100, METADATA_ONLY, SUBSCRIBER, SUSPENDED, GROUPID, PRIVILEGED_SUBSCRIBER, DECOMPRESS)
VALUES (1, 1, 'https://172.100.0.5:8080', 'user1', 'password1', true, false, 'user1', false, 1, false, false);
INSERT INTO SUBSCRIPTIONS(SUBID, FEEDID, DELIVERY_URL, DELIVERY_USER, DELIVERY_PASSWORD, SUBSCRIBER, SELF_LINK, LOG_LINK)
VALUES (23, 1, 'http://delivery_url', 'user1', 'somepassword', 'sub123', 'selflink', 'loglink');
INSERT INTO FEED_ENDPOINT_IDS(FEEDID, USERID, PASSWORD)
VALUES (1, 'USER', 'PASSWORD');
INSERT INTO FEED_ENDPOINT_ADDRS(FEEDID, ADDR)
VALUES (1, '172.0.0.1');
INSERT INTO FEEDS(FEEDID, GROUPID, NAME, VERSION, DESCRIPTION, BUSINESS_DESCRIPTION, AUTH_CLASS, PUBLISHER, SELF_LINK, PUBLISH_LINK, SUBSCRIBE_LINK, LOG_LINK)
VALUES (1, 1,'Feed1','v0.1', 'First Feed for testing', 'First Feed for testing', 'auth_class', 'pub','self_link','publish_link','subscribe_link','log_link');
insert into INGRESS_ROUTES(SEQUENCE, FEEDID , USERID, SUBNET, NODESET)
VALUES (1,1,'user',null,2);
insert into INGRESS_ROUTES(SEQUENCE, FEEDID , USERID, SUBNET, NODESET)
VALUES (2,1,'user',null,2);
insert into NODESETS(SETID, NODEID)
VALUES (2,2);
insert into LOG_RECORDS(RECORD_ID,TYPE,EVENT_TIME,PUBLISH_ID,FEEDID,REQURI,METHOD,CONTENT_TYPE,CONTENT_LENGTH,FEED_FILEID,REMOTE_ADDR,USER,STATUS,DELIVERY_SUBID,DELIVERY_FILEID,RESULT,ATTEMPTS,REASON,FILENAME)
VALUES(1,'pub',2536159564422,'ID',1,'URL/file123','PUT','application/vnd.dmaap-dr.log-list; version=1.0',100,1,'172.0.0.8','user',204,1,1,204,0,'other','file123');
CREATE ALIAS IF NOT EXISTS `SUBSTRING_INDEX` AS $$
String Function(String one, String two, String three){
return "url";
}
$$;
insert into NETWORK_ROUTES(FROMNODE, TONODE, VIANODE)
VALUES (1, 3, 2);
insert into NODES(NODEID, NAME) values
(1, 'stub_from.'),
(2, 'stub_via.'),
(3, 'stub_to.'),
(4, 'node01.'),
(5, 'node02.'),
(6, 'node03.')
;
insert into EGRESS_ROUTES(SUBID, NODEID) values (1, 1);
|
