| -- |
| -- Licensed to the Apache Software Foundation (ASF) under one |
| -- or more contributor license agreements. See the NOTICE file |
| -- distributed with this work for additional information |
| -- regarding copyright ownership. The ASF licenses this file |
| -- to you under the Apache License, Version 2.0 (the |
| -- "License"); you may not use this file except in compliance |
| -- with the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, |
| -- software distributed under the License is distributed on an |
| -- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| -- KIND, either express or implied. See the License for the |
| -- specific language governing permissions and limitations |
| -- under the License. |
| -- |
| |
| CREATE DATABASE IF NOT EXISTS graph_dev; |
| |
| CREATE USER 'graph'@'%' IDENTIFIED BY 'graph'; |
| |
| GRANT ALL PRIVILEGES ON graph_dev.* TO 'graph'@'%' identified by 'graph'; |
| |
| flush privileges; |
| |
| use graph_dev; |
| |
| |
| SET FOREIGN_KEY_CHECKS = 0; |
| |
| -- ---------------------------- |
| -- Table structure for `services` |
| -- ---------------------------- |
| DROP TABLE IF EXISTS `services`; |
| CREATE TABLE `services` ( |
| `id` integer NOT NULL AUTO_INCREMENT, |
| `service_name` varchar(64) NOT NULL, |
| `access_token` varchar(64) NOT NULL, |
| `cluster` varchar(255) NOT NULL, |
| `hbase_table_name` varchar(255) NOT NULL, |
| `pre_split_size` integer NOT NULL default 0, |
| `hbase_table_ttl` integer, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ux_service_name` (`service_name`), |
| INDEX `idx_access_token` (`access_token`), |
| INDEX `idx_cluster` (cluster(75)) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| |
| |
| -- ---------------------------- |
| -- Table structure for `services_columns` |
| -- ---------------------------- |
| DROP TABLE IF EXISTS `service_columns`; |
| CREATE TABLE `service_columns` ( |
| `id` integer NOT NULL AUTO_INCREMENT, |
| `service_id` integer NOT NULL, |
| `column_name` varchar(64) NOT NULL, |
| `column_type` varchar(8) NOT NULL, |
| `schema_version` varchar(8) NOT NULL default 'v2', |
| `options` text, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ux_service_id_column_name` (`service_id`, `column_name`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| |
| ALTER TABLE service_columns add FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE CASCADE; |
| |
| |
| -- ---------------------------- |
| -- Table structure for `column_metas` |
| -- ---------------------------- |
| DROP TABLE IF EXISTS `column_metas`; |
| CREATE TABLE `column_metas` ( |
| `id` integer NOT NULL AUTO_INCREMENT, |
| `column_id` integer NOT NULL, |
| `name` varchar(64) NOT NULL, |
| `seq` tinyint NOT NULL, |
| `data_type` varchar(8) NOT NULL DEFAULT 'string', |
| `default_value` varchar(64) NOT NULL DEFAULT '', |
| `store_in_global_index` tinyint NOT NULL DEFAULT 0, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ux_column_id_name` (`column_id`, `name`), |
| INDEX `idx_column_id_seq` (`column_id`, `seq`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| |
| ALTER TABLE column_metas ADD FOREIGN KEY(column_id) REFERENCES service_columns(id) ON DELETE CASCADE; |
| |
| -- ---------------------------- |
| -- Table structure for `labels` |
| -- ---------------------------- |
| |
| DROP TABLE IF EXISTS `labels`; |
| CREATE TABLE `labels` ( |
| `id` integer NOT NULL AUTO_INCREMENT, |
| `label` varchar(128) NOT NULL, |
| `src_service_id` integer NOT NULL, |
| `src_column_name` varchar(64) NOT NULL, |
| `src_column_type` varchar(8) NOT NULL, |
| `tgt_service_id` integer NOT NULL, |
| `tgt_column_name` varchar(64) NOT NULL, |
| `tgt_column_type` varchar(8) NOT NULL, |
| `is_directed` tinyint NOT NULL DEFAULT 1, |
| `service_name` varchar(64), |
| `service_id` integer NOT NULL, |
| `consistency_level` varchar(8) NOT NULL DEFAULT 'weak', |
| `hbase_table_name` varchar(255) NOT NULL DEFAULT 's2graph', |
| `hbase_table_ttl` integer, |
| `schema_version` varchar(8) NOT NULL default 'v2', |
| `is_async` tinyint(4) NOT NULL default '0', |
| `compressionAlgorithm` varchar(64) NOT NULL DEFAULT 'lz4', |
| `options` text, |
| `deleted_at` datetime DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ux_label` (`label`), |
| INDEX `idx_src_column_name` (`src_column_name`), |
| INDEX `idx_tgt_column_name` (`tgt_column_name`), |
| INDEX `idx_src_service_id` (`src_service_id`), |
| INDEX `idx_tgt_service_id` (`tgt_service_id`), |
| INDEX `idx_service_name` (`service_name`), |
| INDEX `idx_service_id` (`service_id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| |
| ALTER TABLE labels add FOREIGN KEY(service_id) REFERENCES services(id); |
| |
| -- ---------------------------- |
| -- Table structure for `global_index` |
| -- ---------------------------- |
| DROP TABLE IF EXISTS `global_indices`; |
| CREATE TABLE `global_indices` ( |
| `id` integer NOT NULL AUTO_INCREMENT, |
| `element_type` varchar(64) NOT NULL, |
| `prop_names` varchar(255) NOT NULL, |
| `index_name` varchar(64) NOT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ux_global_index_element_type_index_name` (`element_type`, `index_name`), |
| UNIQUE KEY `ux_global_index_element_type_prop_names` (`element_type`, `prop_names`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| |
| |
| -- ---------------------------- |
| -- Table structure for `label_metas` |
| -- ---------------------------- |
| DROP TABLE IF EXISTS `label_metas`; |
| CREATE TABLE `label_metas` ( |
| `id` integer NOT NULL AUTO_INCREMENT, |
| `label_id` integer NOT NULL, |
| `name` varchar(64) NOT NULL, |
| `seq` tinyint NOT NULL, |
| `default_value` varchar(64) NOT NULL, |
| `data_type` varchar(8) NOT NULL DEFAULT 'long', |
| `used_in_index` tinyint NOT NULL DEFAULT 0, |
| `store_in_global_index` tinyint NOT NULL DEFAULT 0, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ux_label_id_name` (`label_id`, `name`), |
| INDEX `idx_label_id_seq` (`label_id`, `seq`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| |
| ALTER TABLE label_metas ADD FOREIGN KEY(label_id) REFERENCES labels(id) ON DELETE CASCADE; |
| |
| |
| -- ---------------------------- |
| -- Table structure for `label_indices` |
| -- ---------------------------- |
| DROP TABLE IF EXISTS `label_indices`; |
| CREATE TABLE `label_indices` ( |
| `id` int(11) NOT NULL AUTO_INCREMENT, |
| `label_id` int(11) NOT NULL, |
| `name` varchar(64) NOT NULL DEFAULT '_PK', |
| `seq` tinyint(4) NOT NULL, |
| `meta_seqs` varchar(64) NOT NULL, |
| `formulars` varchar(255) DEFAULT NULL, |
| `dir` int DEFAULT NULL, |
| `options` text, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ux_label_id_seq` (`label_id`,`meta_seqs`), |
| UNIQUE KEY `ux_label_id_name` (`label_id`,`name`), |
| UNIQUE KEY `ux_label_id_meta_seqs_dir` (`label_id`,`meta_seqs`,`dir`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| |
| ALTER TABLE label_indices ADD FOREIGN KEY(label_id) REFERENCES labels(id) ON DELETE CASCADE; |
| |
| |
| -- ---------------------------- |
| -- Table structure for `service_column_indices` |
| -- ---------------------------- |
| DROP TABLE IF EXISTS `service_column_indices`; |
| CREATE TABLE `service_column_indices` ( |
| `id` int(11) NOT NULL AUTO_INCREMENT, |
| `service_id` int(11) NOT NULL, |
| `service_column_id` int(11) NOT NULL, |
| `name` varchar(64) NOT NULL DEFAULT '_PK', |
| `seq` tinyint(4) NOT NULL, |
| `meta_seqs` varchar(64) NOT NULL, |
| `options` text, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ux_service_id_service_column_id_seq` (`service_id`,`service_column_id`,`seq`), |
| UNIQUE KEY `ux_service_id_service_column_id_name` (`service_id`, `service_column_id`,`name`), |
| UNIQUE KEY `ux_service_id_service_column_id_seqs` (`service_id`, `service_column_id`,`meta_seqs`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| |
| ALTER TABLE service_column_indices ADD FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE CASCADE; |
| ALTER TABLE service_column_indices ADD FOREIGN KEY(service_column_id) REFERENCES service_columns(id) ON DELETE CASCADE; |
| |
| |
| -- ---------------------------- |
| -- Table structure for `experiments` |
| -- ---------------------------- |
| DROP TABLE IF EXISTS `experiments`; |
| CREATE TABLE `experiments` ( |
| `id` integer NOT NULL AUTO_INCREMENT, |
| `service_id` integer NOT NULL, |
| `service_name` varchar(128) NOT NULL, |
| `name` varchar(64) NOT NULL, |
| `description` varchar(255) NOT NULL, |
| `experiment_type` varchar(8) NOT NULL DEFAULT 'u', |
| `total_modular` int NOT NULL DEFAULT 100, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ux_service_id_name` (`service_id`, `name`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| |
| -- ALTER TABLE experiments ADD FOREIGN KEY(service_id) REFERENCES service(id) ON DELETE CASCADE; |
| |
| |
| -- ---------------------------- |
| -- Table structure for `buckets` |
| -- ---------------------------- |
| DROP TABLE IF EXISTS `buckets`; |
| CREATE TABLE `buckets` ( |
| `id` integer NOT NULL AUTO_INCREMENT, |
| `experiment_id` integer NOT NULL, |
| `modular` varchar(64) NOT NULL, |
| `http_verb` varchar(8) NOT NULL, |
| `api_path` text NOT NULL, |
| `uuid_key` varchar(128), |
| `uuid_placeholder` varchar(64), |
| `request_body` text NOT NULL, |
| `timeout` int NOT NULL DEFAULT 1000, |
| `impression_id` varchar(64) NOT NULL, |
| `is_graph_query` tinyint NOT NULL DEFAULT 1, |
| `is_empty` tinyint NOT NULL DEFAULT 0, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ux_impression_id` (`impression_id`), |
| INDEX `idx_experiment_id` (`experiment_id`), |
| INDEX `idx_impression_id` (`impression_id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| |
| SET FOREIGN_KEY_CHECKS = 1; |
| |
| |
| -- ---------------------------- |
| -- Table structure for `counter` |
| -- ---------------------------- |
| DROP TABLE IF EXISTS `counter`; |
| CREATE TABLE `counter` ( |
| `id` int(11) unsigned NOT NULL AUTO_INCREMENT, |
| `use_flag` tinyint(1) NOT NULL DEFAULT '0', |
| `version` smallint(1) NOT NULL DEFAULT '1', |
| `service` varchar(64) NOT NULL DEFAULT '', |
| `action` varchar(64) NOT NULL DEFAULT '', |
| `item_type` int(11) NOT NULL DEFAULT '0', |
| `auto_comb` tinyint(1) NOT NULL DEFAULT '1', |
| `dimension` varchar(1024) NOT NULL, |
| `use_profile` tinyint(1) NOT NULL DEFAULT '0', |
| `bucket_imp_id` varchar(64) DEFAULT NULL, |
| `use_exact` tinyint(1) NOT NULL DEFAULT '1', |
| `use_rank` tinyint(1) NOT NULL DEFAULT '1', |
| `ttl` int(11) NOT NULL DEFAULT '172800', |
| `daily_ttl` int(11) DEFAULT NULL, |
| `hbase_table` varchar(1024) DEFAULT NULL, |
| `interval_unit` varchar(1024) DEFAULT NULL, |
| `rate_action_id` int(11) unsigned DEFAULT NULL, |
| `rate_base_id` int(11) unsigned DEFAULT NULL, |
| `rate_threshold` int(11) DEFAULT NULL, |
| `top_k` int(11) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `svc` (`service`,`action`), |
| KEY `rate_action_id` (`rate_action_id`), |
| KEY `rate_base_id` (`rate_base_id`), |
| CONSTRAINT `rate_action_id` FOREIGN KEY (`rate_action_id`) REFERENCES `counter` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, |
| CONSTRAINT `rate_base_id` FOREIGN KEY (`rate_base_id`) REFERENCES `counter` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
