diff options
Diffstat (limited to 'components/datalake-handler/feeder/src/assembly/scripts/init_db.sql')
-rw-r--r-- | components/datalake-handler/feeder/src/assembly/scripts/init_db.sql | 192 |
1 files changed, 95 insertions, 97 deletions
diff --git a/components/datalake-handler/feeder/src/assembly/scripts/init_db.sql b/components/datalake-handler/feeder/src/assembly/scripts/init_db.sql index ad142dcf..72892a27 100644 --- a/components/datalake-handler/feeder/src/assembly/scripts/init_db.sql +++ b/components/datalake-handler/feeder/src/assembly/scripts/init_db.sql @@ -1,125 +1,123 @@ +drop DATABASE datalake;
create database datalake;
use datalake;
-CREATE TABLE `topic` (
- `name` varchar(255) NOT NULL,
- `correlate_cleared_message` bit(1) DEFAULT NULL,
- `enabled` bit(1) DEFAULT 0,
- `login` varchar(255) DEFAULT NULL,
- `message_id_path` varchar(255) DEFAULT NULL,
- `aggregate_array_path` varchar(2000) DEFAULT NULL,
- `flatten_array_path` varchar(2000) DEFAULT NULL,
- `pass` varchar(255) DEFAULT NULL,
- `save_raw` bit(1) DEFAULT NULL,
- `ttl` int(11) DEFAULT NULL,
- `data_format` varchar(255) DEFAULT NULL,
- PRIMARY KEY (`name`)
+CREATE TABLE `topic_name` (
+ `id` varchar(255) NOT NULL,
+ PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE `db_type` (
+ `id` varchar(255) NOT NULL,
+ `default_port` int(11) DEFAULT NULL,
+ `name` varchar(255) NOT NULL,
+ `tool` bit(1) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `db` (
- `name` varchar(255) NOT NULL,
- `enabled` bit(1) DEFAULT 0,
- `host` varchar(255) DEFAULT NULL,
- `port` int(11) DEFAULT NULL,
+ `id` int(11) NOT NULL AUTO_INCREMENT,
`database_name` varchar(255) DEFAULT NULL,
+ `enabled` bit(1) NOT NULL,
`encrypt` bit(1) DEFAULT NULL,
+ `host` varchar(255) DEFAULT NULL,
`login` varchar(255) DEFAULT NULL,
+ `name` varchar(255) DEFAULT NULL,
`pass` varchar(255) DEFAULT NULL,
+ `port` int(11) DEFAULT NULL,
`property1` varchar(255) DEFAULT NULL,
`property2` varchar(255) DEFAULT NULL,
`property3` varchar(255) DEFAULT NULL,
- PRIMARY KEY (`name`)
+ `db_type_id` varchar(255) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `FK3njadtw43ieph7ftt4kxdhcko` (`db_type_id`),
+ CONSTRAINT `FK3njadtw43ieph7ftt4kxdhcko` FOREIGN KEY (`db_type_id`) REFERENCES `db_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-CREATE TABLE `map_db_topic` (
- `db_name` varchar(255) NOT NULL,
- `topic_name` varchar(255) NOT NULL,
- PRIMARY KEY (`db_name`,`topic_name`),
- KEY `FK_topic_name` (`topic_name`),
- CONSTRAINT `FK_topic_name` FOREIGN KEY (`topic_name`) REFERENCES `topic` (`name`),
- CONSTRAINT `FK_db_name` FOREIGN KEY (`db_name`) REFERENCES `db` (`name`)
+CREATE TABLE `design_type` (
+ `id` varchar(255) NOT NULL,
+ `name` varchar(255) DEFAULT NULL,
+ `note` varchar(255) DEFAULT NULL,
+ `db_type_id` varchar(255) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `FKm8rkv2qkq01gsmeq1c3y4w02x` (`db_type_id`),
+ CONSTRAINT `FKm8rkv2qkq01gsmeq1c3y4w02x` FOREIGN KEY (`db_type_id`) REFERENCES `db_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-CREATE TABLE `portal` (
- `name` varchar(255) NOT NULL DEFAULT '',
- `enabled` bit(1) DEFAULT 0,
- `host` varchar(500) DEFAULT NULL,
- `port` int(5) unsigned DEFAULT NULL,
- `login` varchar(255) DEFAULT NULL,
- `pass` varchar(255) DEFAULT NULL,
- `related_db` varchar(255) DEFAULT NULL,
- PRIMARY KEY (`name`),
- KEY `FK_related_db` (`related_db`),
- CONSTRAINT `FK_related_db` FOREIGN KEY (`related_db`) REFERENCES `db` (`name`) ON DELETE SET NULL
+CREATE TABLE `design` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `body` varchar(255) DEFAULT NULL,
+ `name` varchar(255) DEFAULT NULL,
+ `note` varchar(255) DEFAULT NULL,
+ `submitted` bit(1) DEFAULT NULL,
+ `design_type_id` varchar(255) NOT NULL,
+ `topic_name_id` varchar(255) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `FKo43yi6aputq6kwqqu8eqbspm5` (`design_type_id`),
+ KEY `FKabb8e74230glxpaiai4aqsr34` (`topic_name_id`),
+ CONSTRAINT `FKabb8e74230glxpaiai4aqsr34` FOREIGN KEY (`topic_name_id`) REFERENCES `topic_name` (`id`),
+ CONSTRAINT `FKo43yi6aputq6kwqqu8eqbspm5` FOREIGN KEY (`design_type_id`) REFERENCES `design_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-CREATE TABLE `design_type` (
+CREATE TABLE `kafka` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `broker_list` varchar(255) NOT NULL,
+ `consumer_count` int(11) DEFAULT 3,
+ `enabled` bit(1) NOT NULL,
+ `excluded_topic` varchar(1023) DEFAULT '__consumer_offsets,__transaction_state',
+ `group` varchar(255) DEFAULT 'datalake',
+ `included_topic` varchar(255) DEFAULT NULL,
+ `login` varchar(255) DEFAULT NULL,
`name` varchar(255) NOT NULL,
- `portal` varchar(255) DEFAULT NULL,
- `note` text DEFAULT NULL,
- PRIMARY KEY (`name`),
- KEY `FK_portal` (`portal`),
- CONSTRAINT `FK_portal` FOREIGN KEY (`portal`) REFERENCES `portal` (`name`) ON DELETE SET NULL
+ `pass` varchar(255) DEFAULT NULL,
+ `secure` bit(1) DEFAULT b'0',
+ `security_protocol` varchar(255) DEFAULT NULL,
+ `timeout_sec` int(11) DEFAULT 10,
+ `zk` varchar(255) NOT NULL,
+ PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-CREATE TABLE `portal_design` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(255) NOT NULL,
- `submitted` bit(1) DEFAULT 0,
- `body` text DEFAULT NULL,
- `note` text DEFAULT NULL,
- `topic` varchar(255) DEFAULT NULL,
- `type` varchar(255) DEFAULT NULL,
+CREATE TABLE `topic` (
+ `id` int(11) NOT NULL,
+ `aggregate_array_path` varchar(255) DEFAULT NULL,
+ `correlate_cleared_message` bit(1) NOT NULL DEFAULT b'0',
+ `data_format` varchar(255) DEFAULT NULL,
+ `enabled` bit(1) NOT NULL,
+ `flatten_array_path` varchar(255) DEFAULT NULL,
+ `login` varchar(255) DEFAULT NULL,
+ `message_id_path` varchar(255) DEFAULT NULL,
+ `pass` varchar(255) DEFAULT NULL,
+ `save_raw` bit(1) NOT NULL DEFAULT b'0',
+ `ttl_day` int(11) DEFAULT NULL,
+ `topic_name_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
- KEY `FK_topic` (`topic`),
- KEY `FK_type` (`type`),
- CONSTRAINT `FK_topic` FOREIGN KEY (`topic`) REFERENCES `topic` (`name`) ON DELETE SET NULL,
- CONSTRAINT `FK_type` FOREIGN KEY (`type`) REFERENCES `design_type` (`name`) ON DELETE SET NULL
+ KEY `FKj3pldlfaokdhqjfva8n3pkjca` (`topic_name_id`),
+ CONSTRAINT `FKj3pldlfaokdhqjfva8n3pkjca` FOREIGN KEY (`topic_name_id`) REFERENCES `topic_name` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-insert into db (`name`,`host`,`login`,`pass`,`database_name`) values ('Couchbase','dl_couchbase','dl','dl1234','datalake');
-insert into db (`name`,`host`) values ('Elasticsearch','dl_es');
-insert into db (`name`,`host`,`port`,`database_name`) values ('MongoDB','dl_mongodb',27017,'datalake');
-insert into db (`name`,`host`) values ('Druid','dl_druid');
-insert into db (`name`,`host`,`login`) values ('HDFS','dlhdfs','dl');
-
-
--- in production, default enabled should be off
-insert into `topic`(`name`,`enabled`,`save_raw`,`ttl`,`data_format`) values ('_DL_DEFAULT_',1,0,3650,'JSON');
-insert into `topic`(`name`,correlate_cleared_message,`enabled`, message_id_path,`data_format`) values ('unauthenticated.SEC_FAULT_OUTPUT',1,1,'/event/commonEventHeader/eventName,/event/commonEventHeader/reportingEntityName,/event/faultFields/specificProblem,/event/commonEventHeader/eventId','JSON');
-insert into `topic`(`name`,`enabled`, aggregate_array_path,flatten_array_path,`data_format`)
-values ('unauthenticated.VES_MEASUREMENT_OUTPUT',1,
-'/event/measurementsForVfScalingFields/diskUsageArray,/event/measurementsForVfScalingFields/cpuUsageArray,/event/measurementsForVfScalingFields/vNicPerformanceArray',
-'/event/measurementsForVfScalingFields/astriMeasurement/astriDPMeasurementArray/astriInterface',
-'JSON');
-
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('Couchbase','_DL_DEFAULT_');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('Elasticsearch','_DL_DEFAULT_');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('MongoDB','_DL_DEFAULT_');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('Druid','_DL_DEFAULT_');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('HDFS','_DL_DEFAULT_');
-
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('Couchbase','unauthenticated.SEC_FAULT_OUTPUT');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('Elasticsearch','unauthenticated.SEC_FAULT_OUTPUT');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('MongoDB','unauthenticated.SEC_FAULT_OUTPUT');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('Druid','unauthenticated.SEC_FAULT_OUTPUT');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('HDFS','unauthenticated.SEC_FAULT_OUTPUT');
-
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('Couchbase','unauthenticated.VES_MEASUREMENT_OUTPUT');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('Elasticsearch','unauthenticated.VES_MEASUREMENT_OUTPUT');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('MongoDB','unauthenticated.VES_MEASUREMENT_OUTPUT');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('Druid','unauthenticated.VES_MEASUREMENT_OUTPUT');
-insert into `map_db_topic`(`db_name`,`topic_name`) values ('HDFS','unauthenticated.VES_MEASUREMENT_OUTPUT');
-
-insert into portal (`name`,`related_db`, host) values ('Kibana', 'Elasticsearch', 'dl_es');
-insert into portal (`name`,`related_db`) values ('Elasticsearch', 'Elasticsearch');
-insert into portal (`name`,`related_db`) values ('Druid', 'Druid');
+CREATE TABLE `map_db_design` (
+ `design_id` int(11) NOT NULL,
+ `db_id` int(11) NOT NULL,
+ PRIMARY KEY (`design_id`,`db_id`),
+ KEY `FKhpn49r94k05mancjtn301m2p0` (`db_id`),
+ CONSTRAINT `FKfli240v96cfjbnmjqc0fvvd57` FOREIGN KEY (`design_id`) REFERENCES `design` (`id`),
+ CONSTRAINT `FKhpn49r94k05mancjtn301m2p0` FOREIGN KEY (`db_id`) REFERENCES `db` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-insert into design_type (`name`,`portal`) values ('Kibana Dashboard', 'Kibana');
-insert into design_type (`name`,`portal`) values ('Kibana Search', 'Kibana');
-insert into design_type (`name`,`portal`) values ('Kibana Visualization', 'Kibana');
-insert into design_type (`name`,`portal`) values ('Elasticsearch Field Mapping Template', 'Elasticsearch');
-insert into design_type (`name`,`portal`) values ('Druid Kafka Indexing Service Supervisor', 'Druid');
+CREATE TABLE `map_db_topic` (
+ `topic_id` int(11) NOT NULL,
+ `db_id` int(11) NOT NULL,
+ PRIMARY KEY (`db_id`,`topic_id`),
+ KEY `FKq1jon185jnrr7dv1dd8214uw0` (`topic_id`),
+ CONSTRAINT `FKirro29ojp7jmtqx9m1qxwixcc` FOREIGN KEY (`db_id`) REFERENCES `db` (`id`),
+ CONSTRAINT `FKq1jon185jnrr7dv1dd8214uw0` FOREIGN KEY (`topic_id`) REFERENCES `topic` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE `map_kafka_topic` (
+ `kafka_id` int(11) NOT NULL,
+ `topic_id` int(11) NOT NULL,
+ PRIMARY KEY (`topic_id`,`kafka_id`),
+ KEY `FKtdrme4h7rxfh04u2i2wqu23g5` (`kafka_id`),
+ CONSTRAINT `FK5q7jdxy54au5rcrhwa4a5igqi` FOREIGN KEY (`topic_id`) REFERENCES `topic` (`id`),
+ CONSTRAINT `FKtdrme4h7rxfh04u2i2wqu23g5` FOREIGN KEY (`kafka_id`) REFERENCES `kafka` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|