DB utils update for db initialization

Issue-ID: DMAAP-566
Change-Id: I3dad4059a3b192f2c54598985893b1334809d1f0
Signed-off-by: Fiachra Corcoran <fiachra.corcoran@ericsson.com>

4 files changed, 50 insertions, 41 deletions

diff --git a/datarouter-prov/src/main/resources/misc/drtrprov b/datarouter-prov/src/main/resources/misc/drtrprov
index 2afb6ecb..9f86c23b 100644
--- a/datarouter-prov/src/main/resources/misc/drtrprov
+++ b/datarouter-prov/src/main/resources/misc/drtrprov
@@ -56,7 +56,7 @@ start() {
 # 	fi
 	if [ "`pgrep -u mysql mysqld`" = "" ]
 	then
-		echo MySQL is not running.  It must be started before drtrprov
+		echo MariaDB is not running.  It must be started before drtrprov
 		exit 0
 	fi
 	PIDS=`pids`
diff --git a/datarouter-prov/src/main/resources/misc/notes b/datarouter-prov/src/main/resources/misc/notes
index e3f872e7..4888dc27 100644
--- a/datarouter-prov/src/main/resources/misc/notes
+++ b/datarouter-prov/src/main/resources/misc/notes
@@ -71,8 +71,8 @@ DRTR_PROV_TSTOREPASS (default changeit)
 	file is specified.
 
 DRTR_PROV_DBLOGIN (default datarouter)
-	The login used to access MySQL
+	The login used to access MariaDB
 DRTR_PROV_DBPASS (default datarouter)
-	The password used to access MySQL
+	The password used to access MariaDB
 DRTR_PROV_DBSCRIPTS (default /opt/app/datartr/etc)
 	The directory containing DB initialization scripts
diff --git a/datarouter-prov/src/main/resources/misc/runreports b/datarouter-prov/src/main/resources/misc/runreports
index 170d6efb..f6037f4b 100644
--- a/datarouter-prov/src/main/resources/misc/runreports
+++ b/datarouter-prov/src/main/resources/misc/runreports
@@ -47,7 +47,7 @@ then
 fi
 if [ "`pgrep -u mysql mysqld`" = "" ]
 then
-	echo MySQL is not running.  It must be started before runreports
+	echo MariaDB is not running.  It must be started before runreports
 	exit 1
 fi
 
diff --git a/datarouter-prov/src/main/resources/misc/mysql_dr_schema.sql b/datarouter-prov/src/main/resources/misc/sql_init_01.sql
index 837030c3..e1dfd0c8 100644
--- a/datarouter-prov/src/main/resources/misc/mysql_dr_schema.sql
+++ b/datarouter-prov/src/main/resources/misc/sql_init_01.sql
@@ -1,12 +1,12 @@
-create database 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),
@@ -14,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 (
@@ -31,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),
@@ -39,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 (
@@ -48,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,
@@ -116,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',  '127.0.0.1'),
-	('PROV_ACTIVE_NAME',  '${PROV_ACTIVE_NAME}'),
-	('STANDBY_POD', '${DRTR_PROV_STANDBYPOD}'),
-	('PROV_NAME',   'ALCDTL47TJ6015:6080'),
-	('NODES',       '127.0.0.1:8080'),
-	('PROV_DOMAIN', '127.0.0.1'),
-	('DELIVERY_INIT_RETRY_INTERVAL', '10'),
-	('DELIVERY_MAX_AGE', '86400'),
-	('DELIVERY_MAX_RETRY_INTERVAL', '3600'),
-	('DELIVERY_RETRY_RATIO', '2'),
-	('LOGROLL_INTERVAL', '300'),
-	('PROV_AUTH_ADDRESSES', '192.168.56.1'), 
-	('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