-- Copyright (c) 2019-2022 ZettaDB inc. All rights reserved.
-- This source code is licensed under Apache 2.0 License,
-- combined with Common Clause Condition 1.0, as detailed in the NOTICE file.

--
-- Host: localhost    Database: Kunlun_Metadata_DB
-- ------------------------------------------------------

 SET NAMES utf8mb4 ;
/*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */;
/*!50717 PREPARE s FROM @rocksdb_get_is_supported */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;
/*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;
create database Kunlun_Metadata_DB;
use Kunlun_Metadata_DB;
--
-- Table structure for table `commit_log`
--
 SET character_set_client = utf8mb4 ;
CREATE TABLE `commit_log_template_table` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `comp_node_id` int unsigned NOT NULL, -- no FK for perf
  `txn_id` bigint unsigned NOT NULL,
  `next_txn_cmd` enum('commit','abort') NOT NULL,
  `prepare_ts` timestamp(6) default current_timestamp(6),
  `written_shards` json,
  `global_version_no` bigint unsigned,
  PRIMARY KEY (`id`,`comp_node_id`,`txn_id`),
  UNIQUE KEY `commit_log_unik` (`comp_node_id`,`txn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (`comp_node_id`)
SUBPARTITION BY HASH (((`txn_id` >> 32) DIV 86400))
SUBPARTITIONS 32
(PARTITION pplaceholder VALUES IN (null) ENGINE = InnoDB) */;

--
-- Table structure for table `global_configuration`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `global_configuration` (
  `name` varchar(128) NOT NULL,
  `value` varchar(128) ,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `data_centers`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `data_centers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `dc_type` varchar(32) ,
  `owner` varchar(64),
  `province` varchar(128) ,
  `city` varchar(128) ,
  `district` varchar(128) ,
  `location` text ,
  `memo` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `db_clusters`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `db_clusters` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `nick_name` varchar(120),
  `owner` varchar(120) NOT NULL,
  `ddl_log_tblname` varchar(120) NOT NULL,
  `business` varchar(120) NOT NULL,
  `memo` text,
  `status` enum('inuse','deleted', 'creating') DEFAULT 'creating',
  `ha_mode` enum('no_rep','mgr','rbr') not null,
  `encrypt_backup` tinyint(1) NOT NULL DEFAULT '0',
  `backup_encrypt_key` varchar(256) DEFAULT NULL,
  `when_deleted` varchar(24) DEFAULT NULL,
  `when_created` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `comp_nodes`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `comp_nodes_id_seq` (
  `id` int PRIMARY KEY AUTO_INCREMENT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 SET character_set_client = utf8mb4 ;
CREATE TABLE `comp_nodes` (
  `id` int unsigned NOT NULL,
  `name` varchar(64) NOT NULL,
  `hostaddr` varchar(8192) NOT NULL,
  `port` smallint unsigned NOT NULL,
  `mysql_port` smallint unsigned NOT NULL,
  `db_cluster_id` int unsigned NOT NULL,
  `user_name` varchar(64) NOT NULL,
  `passwd` varchar(16) NOT NULL,
  `status` enum('creating','inactive','active','deleted','manual_stop') DEFAULT 'creating',
  `svr_node_id` int unsigned NOT NULL,

  -- resource limits, 0 means unlimited
  cpu_cores smallint unsigned NOT NULL default 0,
  cpu_limit_mode enum('share','quota') DEFAULT 'share',
  max_mem_MB int unsigned NOT NULL default 0,
  max_conns int unsigned NOT NULL DEFAULT 0,

  extra_props text,
  `coldbackup_period` varchar(50) NOT NULL default '01:00:00-02:00:00',
  `when_deleted` varchar(24) DEFAULT NULL,
  `when_created` varchar(24) DEFAULT NULL,
  PRIMARY KEY (db_cluster_id, `id`),
  UNIQUE KEY `cluster_id_name` (db_cluster_id, `name`),
  FOREIGN KEY (db_cluster_id) references db_clusters(id),
  FOREIGN KEY (svr_node_id) references server_nodes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 SET character_set_client = utf8mb4 ;
CREATE TABLE `proxysql_nodes` (
  `id` int unsigned NOT NULL,
  `name` varchar(64) NOT NULL,
  `hostaddr` varchar(8192) NOT NULL,
  `port` smallint unsigned NOT NULL,
  `db_cluster_id` int unsigned NOT NULL,
  `user_name` varchar(64) NOT NULL,
  `passwd` varchar(16) NOT NULL,
  `status` enum('creating','inactive','active','deleted','manual_stop') DEFAULT 'creating',
  `svr_node_id` int unsigned NOT NULL,
  `cpu_cores` smallint unsigned NOT NULL DEFAULT '0',
  `max_mem_MB` int unsigned NOT NULL DEFAULT '0',
  `max_conns` int unsigned NOT NULL DEFAULT '0',
  `extra_props` text,
  `when_deleted` varchar(24) DEFAULT NULL,
  `when_created` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`db_cluster_id`,`id`),
  UNIQUE KEY `cluster_id_name` (`db_cluster_id`,`name`),
  KEY `svr_node_id` (`svr_node_id`),
  CONSTRAINT `proxysql_nodes_ibfk_1` FOREIGN KEY (`db_cluster_id`) REFERENCES `db_clusters` (`id`),
  CONSTRAINT `proxysql_nodes_ibfk_2` FOREIGN KEY (`svr_node_id`) REFERENCES `server_nodes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `ddl_ops_log_template_table`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `ddl_ops_log_template_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `objname` varchar(64) NOT NULL,
  `db_name` varchar(64) NOT NULL,
  `schema_name` varchar(64) NOT NULL,
  `user_name` varchar(64) NOT NULL,
  `role_name` varchar(64) NOT NULL,
  `search_path` text NOT NULL,
  `optype` enum('create','drop','rename','alter','replace', 'remap_shardid', 'add_shard', 'add_node', 'delete_shard', 'delete_node', 'add_computer', 'rcr_shard','others') NOT NULL,
  `objtype` enum('db','index','matview','partition','schema','seq','table','func','role_or_group','proc','stats','user','view', 'others') NOT NULL,
  `when_logged` timestamp(6) NULL DEFAULT current_timestamp(6),
  `sql_src` longtext NOT NULL,
  `sql_storage_node` longtext NOT NULL,
  `target_shard_id` int unsigned NOT NULL, -- no FK for perf, references shards.id
  `initiator` int unsigned NOT NULL, -- no FK for perf, references comp_nodes.id
  `txn_id` bigint unsigned NOT NULL,
  `rcr_shard_map` varchar(4096) character set latin1 default '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `meta_db_nodes`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `meta_db_nodes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `hostaddr` varchar(8192) NOT NULL,
  `port` smallint unsigned NOT NULL,
  `user_name` varchar(64) NOT NULL,
  `passwd` varchar(120) NOT NULL,
  `master_priority` smallint NOT NULL DEFAULT 1,
  `member_state` enum('source','replica') NOT NULL DEFAULT 'replica',
  `sync_state` enum('fsync','async') NOT NULL DEFAULT 'fsync',
  `degrade_conf_state` enum('ON','OFF') NOT NULL DEFAULT 'OFF',
  `degrade_run_state` enum('ON','OFF') NOT NULL DEFAULT 'OFF',
  `replica_delay` int NOT NULL default 0,
  `degrade_conf_time` int NOT NULL default 0,
  `sync_num` smallint unsigned NOT NULL DEFAULT 1,
  `data_storage_MB` int unsigned NOT NULL DEFAULT '0',
  `log_storage_MB` int unsigned NOT NULL DEFAULT '0',
  `nodemgr_bin_path` varchar(8192) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `server_nodes`
--
CREATE TABLE `server_nodes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `hostaddr` varchar(8192) character set latin1 NOT NULL,
  `dc_id` int unsigned,
  `rack_id` varchar(256) character set latin1 DEFAULT NULL,

  -- In all below 4 paths fields, there can be multiple paths, which are
  -- seperated by some char such as a colon(;), these path strings are
  -- only parsed by app code not SQL. Each path is often on one unique storage
  -- device, in order to achieve high parallel IO.

  -- full paths to computing node data dir
  `comp_datadir` text,
  -- full paths to store data directories of all computing nodes
  `datadir` text,
  -- full paths to store binlog dirs of all storage nodes
  `logdir` text,
  -- full paths to store wal log dirs of all storage nodes
  `wal_log_dir` text,
  -- full path to store keyring files
  `keyring_dir` text,
  -- there may be multiple storage devices, detailed stats in server_nodes_stats
  -- `total_storage` bigint unsigned NOT NULL, -- in MBs.
  `total_mem` int unsigned DEFAULT 0, -- in MBs
  `total_cpu_cores` int unsigned DEFAULT 0,
  -- `network_bandwidth` int unsigned NOT NULL, -- in MBps this is often consistent in a DC
  -- when the server started to be in use
  `svc_since` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  -- the port number the node_mgr on this server is listening on, if not using default one. NULL: using default app defined port.
  `nodemgr_port` int,
  `nodemgr_bin_path` text,
  `nodemgr_tmp_data_abs_path` text DEFAULT NULL,
  `extra_props` text,
  `node_stats` enum('running','idle','dead') DEFAULT 'running',
  `nodemgr_tcp_port` int DEFAULT NULL,
  `nodemgr_prometheus_port` int DEFAULT NULL,
  `current_port_pos` int DEFAULT NULL,
  `machine_type` enum('storage','computer') DEFAULT NULL,
  `port_range` varchar(256) DEFAULT NULL,
  `used_port` text,
  `update_ts` datetime DEFAULT NULL,
  `installing_port` text,
  PRIMARY KEY (`id`),
  FOREIGN KEY (dc_id) references data_centers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Insert a pseudo server row in order to create cluster without registering valid server nodes.
insert into server_nodes(hostaddr, total_mem, total_cpu_cores) values('pseudo_server_useless',16*1024,8);


CREATE TABLE `server_nodes_stats` (
  `id` int unsigned NOT NULL PRIMARY KEY, -- server node id
   comp_datadir_used int not null default 0, -- in MBs
   comp_datadir_avail int not null, -- in MBs. available space for all storage devices of computing node data dirs
   datadir_used int not null default 0, -- in MBs
   datadir_avail int not null, -- in MBs
   wal_log_dir_used int not null default 0, -- in MBs
   wal_log_dir_avail int not null, -- in MBs
   log_dir_used int not null default 0, -- in MBs
   log_dir_avail int not null, -- in MBs
   avg_network_usage_pct int not null default 0,
  FOREIGN KEY (id)  references server_nodes(id)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- connectivity statistics between server nodes
CREATE TABLE `server_nodes_conn_stats` (
  `id1` varchar(64) NOT NULL , -- server node id
  `id2` varchar(64) NOT NULL , -- server node id
  rtt_ms int unsigned NOT NULL , -- round trip time in microsecs
  time_diff_ms bigint unsigned NOT NULL , -- local time difference in microsecs
  -- app guarantees no pair stored twice.
  PRIMARY KEY(id1, id2)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `shards`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `shards` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `num_nodes` smallint unsigned NOT NULL,
  `space_volumn` bigint unsigned NOT NULL DEFAULT '0',
  `num_tablets` int unsigned NOT NULL DEFAULT '0',
  `db_cluster_id` int unsigned NOT NULL,
  -- how many slave ACKs to wait for
  `sync_num` smallint unsigned NOT NULL default 1,
  -- extra properties
  extra_props text,
  -- fields for rbr
  `degrade_conf_state` enum('ON','OFF') DEFAULT 'OFF',
  `degrade_run_state` enum('ON','OFF') DEFAULT 'OFF',
  `degrade_conf_time` int NOT NULL default 0,
  `status` enum('inuse','deleted') DEFAULT 'inuse',
  `coldbackup_period` varchar(50) NOT NULL default '01:00:00-02:00:00',
  `when_deleted` varchar(24) DEFAULT NULL,
  `when_created` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (db_cluster_id, `name`),
  FOREIGN KEY (db_cluster_id)  references db_clusters(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `shard_nodes`
--

 SET character_set_client = utf8mb4 ;
CREATE TABLE `shard_nodes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ro_weight` smallint DEFAULT '0',
  `hostaddr` varchar(8192) NOT NULL,
  `port` smallint unsigned NOT NULL,
  `user_name` varchar(64) NOT NULL,
  `passwd` varchar(120) NOT NULL,
  `shard_id` int unsigned NOT NULL,
  `db_cluster_id` int unsigned NOT NULL,
  `svr_node_id` int unsigned NOT NULL,
  `master_priority` smallint NOT NULL default 1,
  `status` enum('creating','inactive','active','deleted','manual_stop','peer_isolate') DEFAULT 'creating',

  -- resource limits, 0 means unlimited
  cpu_cores smallint unsigned NOT NULL default 0,
  cpu_limit_mode enum('share','quota') DEFAULT 'share',
  initial_storage_GB int unsigned NOT NULL default 0,
  max_storage_GB int unsigned NOT NULL default 0,
  data_storage_MB int unsigned NOT NULL DEFAULT '0',
  log_storage_MB int unsigned NOT NULL DEFAULT '0',
  innodb_buffer_pool_MB int unsigned NOT NULL default 0,
  rocksdb_buffer_pool_MB int unsigned NOT NULL default 0,

  -- channel properties, such as channel name, etc.
  extra_props text,

  -- fields for rbr, backup, etc.
  `sync_state` enum('fsync','async') DEFAULT 'fsync',
  `member_state` enum('source','replica') DEFAULT 'replica',
  `replica_delay` int NOT NULL default 0,
  `backup_node` enum ('OFF', 'ON') DEFAULT 'OFF',
  `when_deleted` varchar(24) DEFAULT NULL,
  `when_created` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `shard_nodes_chk_1` CHECK (((`ro_weight` > 0) and (`ro_weight` < 100))),
  FOREIGN KEY (db_cluster_id) references db_clusters(id),
  FOREIGN KEY (svr_node_id) references server_nodes(id),
  FOREIGN KEY (shard_id) references shards(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping routines for database 'Kunlun_Metadata_DB'
--
/*!50003 DROP PROCEDURE IF EXISTS `append_ddl_log_entry` */;
CREATE PROCEDURE `append_ddl_log_entry`(
  tblname varchar(256),
  dbname varchar(64),
  schema_name varchar(64),
  role_name varchar(64),
  user_name varchar(64),
  search_path text,
  objname varchar(64),
  obj_type varchar(16),
  op_type varchar(16),
  cur_opid bigint unsigned,
  sql_src longtext,
  sql_src_sn longtext,
  target_shardid int unsigned,
  initiator_id int unsigned,
  txn_id bigint unsigned,
  OUT my_opid bigint unsigned
)
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN
    set @dbname = dbname;
    set @schema_name = schema_name;
	  set @role_name = role_name;
	  set @user_name = user_name;
	  set @search_path = search_path;
    set @objname = objname;
    set @obj_type = obj_type;
    set @op_type = op_type;
    set @sql_src = sql_src;
    set @sql_src_sn = sql_src_sn;
    set @target_shardid = target_shardid;
    set @initiator_id = initiator_id;
    set @txn_id = txn_id;

    if COALESCE(IS_USED_LOCK('DDL'), 0) != CONNECTION_ID() then
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'DDL lock is hold by current session';
    end if;

    SET @sql = CONCAT(
        'INSERT INTO ',
        tblname,
        '(db_name, schema_name, role_name, user_name, search_path, objname, objtype, optype, sql_src, sql_storage_node, target_shard_id, initiator, txn_id)values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
      );
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @dbname, @schema_name, @role_name, @user_name, @search_path, @objname, @obj_type, @op_type, @sql_src, @sql_src_sn, @target_shardid, @initiator_id, @txn_id;
    set my_opid = LAST_INSERT_ID();
    DEALLOCATE PREPARE stmt;
END ;
/*!50112 PREPARE s FROM @disable_bulk_load */;
/*!50112 EXECUTE s */;
/*!50112 DEALLOCATE PREPARE s */;


create table backup_storage(
	id int unsigned primary key auto_increment,
	name varchar(256),
	stype enum('HDFS', 'S3', 'EBS','CEPH','SSH','OTHER'), -- type of storage service
	-- connection info
	conn_str varchar(8192),
	hostaddr varchar(8192),
	port int,
	user_name varchar(128),
	passwd varchar(32),
	extra text
) ENGINE=InnoDB DEFAULT charset=utf8;
 
-- available successful cluster backups, which can be used to restore clusters
create table cluster_coldbackups (
	id serial primary key,
	-- which backup media is this backup stored into ?
	storage_id int not null default 0,
	-- which kunlun cluster is this from?
	cluster_id int not null default 0,
	-- which kunlun shard is this from?
	shard_id int not null default 0,
	-- which kunlun compute node is this from?
	comp_id int not null default 0,
	-- type of backup: all storage shards, only metadata shard, metadata shard and all storage shards
	backup_type enum('storage', 'compute') default NULL,
	status enum('not_started', 'ongoing', 'done', 'failed') not null default 'not_started',
	start_ts timestamp(6) not null default current_timestamp(6),
	end_ts timestamp(6) not null default 0 ON UPDATE CURRENT_TIMESTAMP(6),
	-- extra info for expanding 
	memo text default null,
	backup_addr varchar(1024) -- the root path and/or directory of the backup files.
) ENGINE=InnoDB DEFAULT charset=utf8;

-- if a cluster backup or restore fails half way, try to avoid repeated shard node backups/restores which have
-- already succeeded. so record successful shard backups.
create table cluster_shard_backup_restore_log (
	id serial primary key,
	-- which backup media is this backup stored into ?
	storage_id int unsigned not null,
	-- which kunlun cluster is this from?
	cluster_id int unsigned not null,
	shard_id int unsigned not null,
	shard_node_id int unsigned not null,
	optype enum('backup', 'restore') not null,
	-- by default NULL and app define place under cluster_backups.backup_addr
	shard_backup_path text,
	status enum('not_started', 'ongoing', 'done', 'failed') not null default 'not_started',
	-- extra info for expanding 
	memo text default null,
	when_started timestamp(6) not null default current_timestamp(6), -- when the operation was issued
	when_ended timestamp(6), -- when the operation ended(either done or failed)

    FOREIGN KEY (storage_id) references backup_storage(id),
 	FOREIGN KEY (cluster_id) references db_clusters(id), 
 	FOREIGN KEY (shard_id)  references shards(id),
 	FOREIGN KEY (shard_node_id) references shard_nodes(id)
) ENGINE=InnoDB DEFAULT charset=utf8;

-- general jobs including the create and drop of a cluster, a computing node, a shard and a shard node.
-- log them in order to recover from failures halfway.
create table cluster_general_job_log (
	id serial primary key,
	job_id varchar(128),
	related_id varchar(128),
	job_type varchar(128) DEFAULT null,
	-- an operation's status goes through the 3 phases: not_started -> ongoing -> done/failed
	status enum ('not_started', 'ongoing', 'done', 'failed') not null default 'not_started',
	-- extra info for expanding 
	memo text default null,
	when_started timestamp(6) not null default current_timestamp(6), -- when the operation was issued
	when_ended timestamp(6), -- when the operation ended(either done or failed)
	job_info text default null,
	user_name varchar(128)
) ENGINE=InnoDB DEFAULT charset=utf8;

-- roll back record for install error
create table cluster_roll_back_record (
	id serial primary key,
	job_id varchar(128) not null,
	roll_info varchar(512) not null
) ENGINE=InnoDB DEFAULT charset=utf8;

-- table move logs, used to recover from broken procedures of a table-move operation.
create table table_move_jobs (
	id serial primary key,
	table_list text default null, -- target table to move
	src_shard int unsigned default null, -- old shard id
	-- data source, dumping the table in this node
	src_shard_node int unsigned default null,
	dest_shard int unsigned default null, -- new shard id
	-- the shard node to move into, must be dest_shard's current master
	dest_shard_node int unsigned default null,
	-- where to replay binlogs from, replication-starting-point(file)
	snapshot_binlog_file_idx varchar(256) default null,
	-- replication-starting-point(offset)
	snapshot_binlog_file_offset bigint unsigned default null,

	-- file format of the table being moved:
	-- logical: a logical dump produced by tools like mydumper, etc;
	-- physical: produced by EXPORT cmd;
	-- dyn_clone: produced by clone cmd(currently only available in innodb)
	tab_file_format enum('logical', 'physical', 'dyn_clone') not null,
	when_started timestamp(6) not null default current_timestamp(6),
	when_ended timestamp(6) NULL default null,
	status varchar(128) not null default 'not_started', -- ('not_started', 'done', 'failed'),
	stage varchar(128) not null default 'not_started', -- ('not_started', 'dumped', 'transmitted', 'loaded', 'caught_up', 'renamed', 'rerouted', 'done', 'failed'),
  binlog_gtid_before_reroute varchar(256) NULL default null,
  binlog_idx_before_reroute varchar(256) NULL default null,
  binlog_offset_before_reroute bigint unsigned NULL default null,
  binlog_pos_before_reroute_timestamp timestamp(6) NULL default null,
	-- extra info for expanding 
	memo text default null
) ENGINE=InnoDB DEFAULT charset=utf8;

-- Rbr failover table
CREATE TABLE `rbr_consfailover` (
	`id` int unsigned NOT NULL AUTO_INCREMENT,
	`host` varchar(8192) NOT NULL,
	`shard_id` int  NOT NULL,
	`cluster_id` int  NOT NULL,
	`taskid` varchar(255) NOT NULL,
	`step` varchar(255) NOT NULL,
	`new_master_host` varchar(255) DEFAULT NULL,
	`description` varchar(255) DEFAULT NULL,
	`consfailover_msg` text,
	`timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
	`err_code` int DEFAULT NULL,
	`err_msg` varchar(255) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT charset=utf8;

-- info for mysqld errlog
CREATE TABLE `node_mgr_mysqlerr` (
	`hostaddr` varchar(8192) character set latin1 NOT NULL,
	`port` int unsigned NOT NULL,
	`err_file` varchar(8192) NOT NULL,
	`scan_timestamp` int unsigned NOT NULL,
	`scan_filesize` int unsigned NOT NULL,
	PRIMARY KEY (`hostaddr`(512), `port`)
) ENGINE=InnoDB DEFAULT charset=utf8;

CREATE TABLE `cluster_mgr_nodes` (
	`id` int unsigned NOT NULL AUTO_INCREMENT,
	`hostaddr` varchar(8192) character set latin1 NOT NULL,
	`port` int unsigned DEFAULT NULL,
	`raft_port` int unsigned DEFAULT NULL,
	`prometheus_port` int unsigned DEFAULT NULL,
	`update_ts` datetime DEFAULT NULL,
	`member_state` enum('source','replica') DEFAULT 'replica',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT charset=utf8;

CREATE TABLE `node_map_master` (
	`id` int unsigned NOT NULL AUTO_INCREMENT,
	`cluster_id` int NOT NULL,
	`node_host` varchar(255) NOT NULL,
	`master_host` varchar(255) NOT NULL,
	`master_uuid` varchar(1024) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT charset=utf8;

CREATE TABLE `mysql_pg_install_log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `general_log_id` bigint unsigned DEFAULT NULL,
  `install_type` varchar(50) DEFAULT NULL,
  `when_started` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `when_ended` timestamp(6) NOT NULL DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP(6),
  `status` enum('not_started','ongoing','done','failed') NOT NULL DEFAULT 'not_started',
  `job_info` text default NULL,
  `memo` text default NULL,
  PRIMARY KEY (`id`),
  KEY `general_log_id` (`general_log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `restore_log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `general_log_id` bigint unsigned DEFAULT NULL,
  `restore_type` varchar(50) DEFAULT NULL,
  `when_started` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `when_ended` timestamp(6) NOT NULL DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP(6),
  `status` enum('not_started','ongoing','done','failed') NOT NULL DEFAULT 'not_started',
  `job_info` text default NULL,
  `memo` text default NULL,
  PRIMARY KEY (`id`),
  KEY `general_log_id` (`general_log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `cluster_shard_topology` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `cluster_id` int unsigned NOT NULL,
  `shard_id` text,
  `max_commit_log_id` int unsigned NOT NULL,
  `max_ddl_log_id` int unsigned NOT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `cluster_es_conf` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `is_install` enum ('no','yes') DEFAULT 'no',
  `es_hostaddr` varchar(8192) NOT NULL,
  `es_port` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `proxysql_nodes` (
  `id` int unsigned NOT NULL,
  `name` varchar(64) NOT NULL,
  `hostaddr` varchar(8192) NOT NULL,
  `port` smallint unsigned NOT NULL,
  `db_cluster_id` int unsigned NOT NULL,
  `when_created` timestamp NULL DEFAULT (now()),
  `user_name` varchar(64) NOT NULL,
  `passwd` varchar(16) NOT NULL,
  `status` enum('creating','inactive','active','deleted','manual_stop') DEFAULT 'creating',
  `svr_node_id` int unsigned NOT NULL,
  `cpu_cores` smallint unsigned NOT NULL DEFAULT '0',
  `max_mem_MB` int unsigned NOT NULL DEFAULT '0',
  `max_conns` int unsigned NOT NULL DEFAULT '0',
  `extra_props` text,
  PRIMARY KEY (`db_cluster_id`,`id`),
  UNIQUE KEY `cluster_id_name` (`db_cluster_id`,`name`),
  KEY `svr_node_id` (`svr_node_id`),
  CONSTRAINT `proxysql_nodes_ibfk_1` FOREIGN KEY (`db_cluster_id`) REFERENCES `db_clusters` (`id`),
  CONSTRAINT `proxysql_nodes_ibfk_2` FOREIGN KEY (`svr_node_id`) REFERENCES `server_nodes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `cluster_logicalbackups` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cluster_id` int NOT NULL DEFAULT '0',
  `db_table` varchar(1024) COLLATE utf8mb4_0900_as_cs DEFAULT '',
  `backup_time` varchar(255) COLLATE utf8mb4_0900_as_cs DEFAULT '',
  `status` enum('not_started','ongoing','done','failed') COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT 'not_started',
  `backup_addr` varchar(1024) COLLATE utf8mb4_0900_as_cs DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `cluster_logicalbackup_record` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cluster_id` int NOT NULL DEFAULT '0',
  `db_table` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs DEFAULT '',
  `execute_host` varchar(128) DEFAULT NULL,
  `job_id` varchar(128) DEFAULT NULL,
  `backup_time` timestamp NULL DEFAULT (now()),
  `backup_state` enum('done','failed') CHARACTER SET latin1 NOT NULL DEFAULT 'failed',
  `backup_info` text,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;

CREATE TABLE `cluster_scheduler_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `job_type` enum('del_table_repartition_src_table') DEFAULT NULL,
  `exec_time` datetime DEFAULT NULL,
  `status` enum('running','done','deleted') DEFAULT 'running',
  `memo` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `user_consumption` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `cluster_id` bigint unsigned NOT NULL,
  `user_name` varchar(128) COLLATE utf8mb4_0900_as_cs NOT NULL,
  `queries` bigint unsigned DEFAULT '0',
  `txns_committed` bigint unsigned DEFAULT '0',
  `txn_write_size` bigint unsigned DEFAULT '0',
  `scanned_rows` bigint unsigned DEFAULT '0',
  `write_rows` bigint unsigned DEFAULT '0',
  `net_io` bigint unsigned DEFAULT '0',
  `disk_space_MB` bigint unsigned DEFAULT '0',
  `begin_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `end_time` datetime DEFAULT NULL,
  `status` enum('open','frozen') COLLATE utf8mb4_0900_as_cs DEFAULT 'open',
  `business_trade_id` varchar(255) COLLATE utf8mb4_0900_as_cs DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `id_name` (`cluster_id`,`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

create table `cluster_rcr_infos` (
	`id` int unsigned NOT NULL AUTO_INCREMENT,
	`master_rcr_meta` varchar(255) default '',
	`master_cluster_id` int unsigned default '0',
	`slave_rcr_meta` varchar(255) default '',
	`slave_cluster_id` int unsigned default '0',
	`shard_maps` text,
	`status` enum('creating','running','deleted','manual_stop','manual_rcrsw') DEFAULT 'creating',
	`conf_delay_sync` int NOT NULL DEFAULT '0',
	`sync_host_delay` int NOT NULL DEFAULT '0',
	`slave_replica_delay` int NOT NULL DEFAULT '0',
	`double_sync` int NOT NULL DEFAULT '0',
	`master_replica_delay` int NOT NULL DEFAULT '0',
	`smap_version` int NOT NULL DEFAULT '1',
	`when_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
	primary key(`id`)
);

create table `cluster_rcr_meta_sync` (
	`id` int unsigned NOT NULL AUTO_INCREMENT,
	`rcr_infos_id` int unsigned default '0',
	`dump_host` varchar(32) default '',
	`meta_sync_state` enum('creating','dump','sync','disconnect','deleted') default 'creating',
	`sync_binlog_file` varchar(255) default '',
	`sync_binlog_pos` bigint NOT NULL DEFAULT '0',
	`sync_gtid` text,
	`alldump_pos` int unsigned DEFAULT '0',
	`update_ts` varchar(16) default '',
	primary key(`id`)
);

create table `global_var_setting` (
       `cluster_id` int not null,
       `varname` varchar(64) not null,
       `owner_sys` enum('computing_node','storage_node') not null,
       `value` varchar(256) not null,
       `old_value` varchar(256),
       `ts` timestamp default now(),
       primary key(`cluster_id`, `varname`, `owner_sys`)
) ENGINE=InnoDB;

CREATE TABLE `cluster_variable_config` (
  `name` varchar(256) NOT NULL,
  `node_type` enum('storage','computer') NOT NULL,
  `var_range` text,
  PRIMARY KEY (`name`,`node_type`)
) ENGINE=InnoDB;
