DB utils update for db initialization

[dmaap/datarouter.git] / datarouter-prov / src / main / resources / misc / sql_init_01.sql

diff --git a/datarouter-prov/src/main/resources/docker-compose/database/install_db.sql b/datarouter-prov/src/main/resources/misc/sql_init_01.sql
similarity index 55%
rename from datarouter-prov/src/main/resources/docker-compose/database/install_db.sql
rename to datarouter-prov/src/main/resources/misc/sql_init_01.sql
index 64a0762..e1dfd0c 100644 (file)
--- a/datarouter-prov/src/main/resources/docker-compose/database/install_db.sql
+++ b/datarouter-prov/src/main/resources/misc/sql_init_01.sql
@@ -1,16 +1,12 @@
-CREATE DATABASE IF NOT EXISTS datarouter;
-
-CREATE USER 'datarouter'@'%' IDENTIFIED BY 'datarouter';
-
-GRANT ALL PRIVILEGES ON * . * TO 'datarouter'@'%';
-
 use datarouter;
 
 CREATE TABLE FEEDS (
     FEEDID         INT UNSIGNED NOT NULL PRIMARY KEY,
-    NAME           VARCHAR(20) NOT NULL,
+    GROUPID        INT(10) UNSIGNED NOT NULL DEFAULT 0,
+    NAME           VARCHAR(255) NOT NULL,
     VERSION        VARCHAR(20) NOT NULL,
-    DESCRIPTION    VARCHAR(256),
+    DESCRIPTION    VARCHAR(1000),
+    BUSINESS_DESCRIPTION VARCHAR(1000) DEFAULT NULL,
     AUTH_CLASS     VARCHAR(32) NOT NULL,
     PUBLISHER      VARCHAR(8) NOT NULL,
     SELF_LINK      VARCHAR(256),
@@ -18,7 +14,9 @@ CREATE TABLE FEEDS (
     SUBSCRIBE_LINK VARCHAR(256),
     LOG_LINK       VARCHAR(256),
     DELETED        BOOLEAN DEFAULT FALSE,
-    LAST_MOD       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+    LAST_MOD       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    SUSPENDED      BOOLEAN DEFAULT FALSE,
+    CREATED_DATE   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );
 
 CREATE TABLE FEED_ENDPOINT_IDS (
@@ -35,6 +33,7 @@ CREATE TABLE FEED_ENDPOINT_ADDRS (
 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),
@@ -43,7 +42,10 @@ CREATE TABLE SUBSCRIPTIONS (
     SUBSCRIBER         VARCHAR(8) NOT NULL,
     SELF_LINK          VARCHAR(256),
     LOG_LINK           VARCHAR(256),
-    LAST_MOD           TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+    LAST_MOD           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    SUSPENDED          BOOLEAN DEFAULT FALSE,
+    CREATED_DATE       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+
 );
 
 CREATE TABLE PARAMETERS (
@@ -52,28 +54,29 @@ CREATE TABLE PARAMETERS (
 );
 
 CREATE TABLE LOG_RECORDS (
-    TYPE          ENUM('pub', 'del', 'exp') NOT NULL,
+    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 UNSIGNED NOT NULL,  /* content length of published file */
+    CONTENT_LENGTH BIGINT NOT NULL,  /* content length of published file */
 
-    FEED_FILEID    VARCHAR(128),               /* file ID of published file */
-    REMOTE_ADDR    VARCHAR(40),                        /* IP address of publishing endpoint */
-    USER           VARCHAR(20),                        /* user name of publishing endpoint */
-    STATUS         SMALLINT,                   /* status code returned to delivering agent */
+    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(128),             /* file ID of file being delivered */
-    RESULT         SMALLINT,                   /* result received from subscribing 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'),
+    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,
 
     INDEX (FEEDID) USING BTREE,
     INDEX (DELIVERY_SUBID) USING BTREE,
@@ -120,24 +123,26 @@ CREATE TABLE GROUPS (
     LAST_MOD       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );
 
--- 'PROV_AUTH_ADDRESSES', '192.168.56.1' ipv4 address of provision server
 INSERT INTO PARAMETERS VALUES
-       ('ACTIVE_POD',  'prov.datarouternew.com'),
-       ('PROV_ACTIVE_NAME',  'prov.datarouternew.com'),
-       ('STANDBY_POD', ''),
-       ('PROV_NAME',   'prov.datarouternew.com'),
-       ('NODES',       'node.datarouternew.com'),
-       ('PROV_DOMAIN', 'datarouternew.com'),
-       ('DELIVERY_INIT_RETRY_INTERVAL', '10'),
-       ('DELIVERY_MAX_AGE', '86400'),
-       ('DELIVERY_MAX_RETRY_INTERVAL', '3600'),
-       ('DELIVERY_RETRY_RATIO', '2'),
-       ('LOGROLL_INTERVAL', '300'),
-       ('PROV_AUTH_ADDRESSES', 'prov.datarouternew.com'), 
-       ('PROV_AUTH_SUBJECTS', ''),
-       ('PROV_MAXFEED_COUNT',  '10000'),
-       ('PROV_MAXSUB_COUNT',   '100000'),
-       ('PROV_REQUIRE_CERT', 'false'),
-       ('PROV_REQUIRE_SECURE', 'false'),
-       ('_INT_VALUES', 'LOGROLL_INTERVAL|PROV_MAXFEED_COUNT|PROV_MAXSUB_COUNT|DELIVERY_INIT_RETRY_INTERVAL|DELIVERY_MAX_RETRY_INTERVAL|DELIVERY_RETRY_RATIO|DELIVERY_MAX_AGE')
-       ;
\ No newline at end of file
+    ('ACTIVE_POD',  'prov.datarouternew.com'),
+    ('PROV_ACTIVE_NAME',  'prov.datarouternew.com'),
+    ('STANDBY_POD', ''),
+    ('PROV_NAME',   'prov.datarouternew.com'),
+    ('NODES',       '172.100.0.1|node.datarouternew.com'),
+    ('PROV_DOMAIN', 'datarouternew.com'),
+    ('DELIVERY_INIT_RETRY_INTERVAL', '10'),
+    ('DELIVERY_MAX_AGE', '86400'),
+    ('DELIVERY_MAX_RETRY_INTERVAL', '3600'),
+    ('DELIVERY_RETRY_RATIO', '2'),
+    ('LOGROLL_INTERVAL', '300'),
+    ('PROV_AUTH_ADDRESSES', '172.100.0.1|prov.datarouternew.com|node.datarouternew.com'),
+    ('PROV_AUTH_SUBJECTS', ''),
+    ('PROV_MAXFEED_COUNT',  '10000'),
+    ('PROV_MAXSUB_COUNT',   '100000'),
+    ('PROV_REQUIRE_CERT', 'false'),
+    ('PROV_REQUIRE_SECURE', 'false'),
+    ('_INT_VALUES', 'LOGROLL_INTERVAL|PROV_MAXFEED_COUNT|PROV_MAXSUB_COUNT|DELIVERY_INIT_RETRY_INTERVAL|DELIVERY_MAX_RETRY_INTERVAL|DELIVERY_RETRY_RATIO|DELIVERY_MAX_AGE')
+    ;
+
+INSERT INTO FEED_ENDPOINT_ADDRS VALUES
+    (1,  '172.100.0.1');
\ No newline at end of file