1 CREATE DATABASE IF NOT EXISTS datarouter;
3 CREATE USER 'datarouter'@'%' IDENTIFIED BY 'datarouter';
5 GRANT ALL PRIVILEGES ON * . * TO 'datarouter'@'%';
10 FEEDID INT UNSIGNED NOT NULL PRIMARY KEY,
11 NAME VARCHAR(20) NOT NULL,
12 VERSION VARCHAR(20) NOT NULL,
13 DESCRIPTION VARCHAR(256),
14 AUTH_CLASS VARCHAR(32) NOT NULL,
15 PUBLISHER VARCHAR(8) NOT NULL,
16 SELF_LINK VARCHAR(256),
17 PUBLISH_LINK VARCHAR(256),
18 SUBSCRIBE_LINK VARCHAR(256),
19 LOG_LINK VARCHAR(256),
20 DELETED BOOLEAN DEFAULT FALSE,
21 LAST_MOD TIMESTAMP DEFAULT CURRENT_TIMESTAMP
24 CREATE TABLE FEED_ENDPOINT_IDS (
25 FEEDID INT UNSIGNED NOT NULL,
26 USERID VARCHAR(20) NOT NULL,
27 PASSWORD VARCHAR(32) NOT NULL
30 CREATE TABLE FEED_ENDPOINT_ADDRS (
31 FEEDID INT UNSIGNED NOT NULL,
32 ADDR VARCHAR(44) NOT NULL
35 CREATE TABLE SUBSCRIPTIONS (
36 SUBID INT UNSIGNED NOT NULL PRIMARY KEY,
37 FEEDID INT UNSIGNED NOT NULL,
38 DELIVERY_URL VARCHAR(256),
39 DELIVERY_USER VARCHAR(20),
40 DELIVERY_PASSWORD VARCHAR(32),
41 DELIVERY_USE100 BOOLEAN DEFAULT FALSE,
42 METADATA_ONLY BOOLEAN DEFAULT FALSE,
43 SUBSCRIBER VARCHAR(8) NOT NULL,
44 SELF_LINK VARCHAR(256),
45 LOG_LINK VARCHAR(256),
46 LAST_MOD TIMESTAMP DEFAULT CURRENT_TIMESTAMP
49 CREATE TABLE PARAMETERS (
50 KEYNAME VARCHAR(32) NOT NULL PRIMARY KEY,
51 VALUE VARCHAR(4096) NOT NULL
54 CREATE TABLE LOG_RECORDS (
55 TYPE ENUM('pub', 'del', 'exp') NOT NULL,
56 EVENT_TIME BIGINT NOT NULL, /* time of the publish request */
57 PUBLISH_ID VARCHAR(64) NOT NULL, /* unique ID assigned to this publish attempt */
58 FEEDID INT UNSIGNED NOT NULL, /* pointer to feed in FEEDS */
59 REQURI VARCHAR(256) NOT NULL, /* request URI */
60 METHOD ENUM('DELETE', 'GET', 'HEAD', 'OPTIONS', 'PUT', 'POST', 'TRACE') NOT NULL, /* HTTP method */
61 CONTENT_TYPE VARCHAR(256) NOT NULL, /* content type of published file */
62 CONTENT_LENGTH BIGINT UNSIGNED NOT NULL, /* content length of published file */
64 FEED_FILEID VARCHAR(128), /* file ID of published file */
65 REMOTE_ADDR VARCHAR(40), /* IP address of publishing endpoint */
66 USER VARCHAR(20), /* user name of publishing endpoint */
67 STATUS SMALLINT, /* status code returned to delivering agent */
69 DELIVERY_SUBID INT UNSIGNED, /* pointer to subscription in SUBSCRIPTIONS */
70 DELIVERY_FILEID VARCHAR(128), /* file ID of file being delivered */
71 RESULT SMALLINT, /* result received from subscribing agent */
73 ATTEMPTS INT, /* deliveries attempted */
74 REASON ENUM('notRetryable', 'retriesExhausted'),
76 RECORD_ID BIGINT UNSIGNED NOT NULL PRIMARY KEY, /* unique ID for this record */
78 INDEX (FEEDID) USING BTREE,
79 INDEX (DELIVERY_SUBID) USING BTREE,
80 INDEX (RECORD_ID) USING BTREE
83 CREATE TABLE INGRESS_ROUTES (
84 SEQUENCE INT UNSIGNED NOT NULL,
85 FEEDID INT UNSIGNED NOT NULL,
88 NODESET INT UNSIGNED NOT NULL
91 CREATE TABLE EGRESS_ROUTES (
92 SUBID INT UNSIGNED NOT NULL PRIMARY KEY,
93 NODEID INT UNSIGNED NOT NULL
96 CREATE TABLE NETWORK_ROUTES (
97 FROMNODE INT UNSIGNED NOT NULL,
98 TONODE INT UNSIGNED NOT NULL,
99 VIANODE INT UNSIGNED NOT NULL
102 CREATE TABLE NODESETS (
103 SETID INT UNSIGNED NOT NULL,
104 NODEID INT UNSIGNED NOT NULL
108 NODEID INT UNSIGNED NOT NULL PRIMARY KEY,
109 NAME VARCHAR(255) NOT NULL,
110 ACTIVE BOOLEAN DEFAULT TRUE
113 CREATE TABLE GROUPS (
114 GROUPID INT UNSIGNED NOT NULL PRIMARY KEY,
115 AUTHID VARCHAR(100) NOT NULL,
116 NAME VARCHAR(50) NOT NULL,
117 DESCRIPTION VARCHAR(255),
118 CLASSIFICATION VARCHAR(20) NOT NULL,
120 LAST_MOD TIMESTAMP DEFAULT CURRENT_TIMESTAMP
123 -- 'PROV_AUTH_ADDRESSES', '192.168.56.1' ipv4 address of provision server
124 INSERT INTO PARAMETERS VALUES
125 ('ACTIVE_POD', 'prov.datarouternew.com'),
126 ('PROV_ACTIVE_NAME', 'prov.datarouternew.com'),
128 ('PROV_NAME', 'prov.datarouternew.com'),
129 ('NODES', 'node.datarouternew.com'),
130 ('PROV_DOMAIN', 'datarouternew.com'),
131 ('DELIVERY_INIT_RETRY_INTERVAL', '10'),
132 ('DELIVERY_MAX_AGE', '86400'),
133 ('DELIVERY_MAX_RETRY_INTERVAL', '3600'),
134 ('DELIVERY_RETRY_RATIO', '2'),
135 ('LOGROLL_INTERVAL', '300'),
136 ('PROV_AUTH_ADDRESSES', 'prov.datarouternew.com'),
137 ('PROV_AUTH_SUBJECTS', ''),
138 ('PROV_MAXFEED_COUNT', '10000'),
139 ('PROV_MAXSUB_COUNT', '100000'),
140 ('PROV_REQUIRE_CERT', 'false'),
141 ('PROV_REQUIRE_SECURE', 'false'),
142 ('_INT_VALUES', 'LOGROLL_INTERVAL|PROV_MAXFEED_COUNT|PROV_MAXSUB_COUNT|DELIVERY_INIT_RETRY_INTERVAL|DELIVERY_MAX_RETRY_INTERVAL|DELIVERY_RETRY_RATIO|DELIVERY_MAX_AGE')