Page MenuHomePhabricator
Paste P14338

zarcillo SQL schema
ActivePublic

Authored by ProdPasteBot on Feb 12 2021, 11:28 AM.
Tags
None
Referenced Files
F34101408: Masterwork From Distant Lands
Feb 12 2021, 11:28 AM
Subscribers
-- MariaDB dump 10.17 Distrib 10.4.15-MariaDB, for Linux (x86_64)
--
-- Host: db1115.eqiad.wmnet Database: zarcillo
-- ------------------------------------------------------
-- Server version 10.1.44-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `instance_status`
--
DROP TABLE IF EXISTS `instance_status`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `instance_status` (
`instance` varchar(300) CHARACTER SET latin1 NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`pooled` tinyint(1) DEFAULT NULL,
`qps` float DEFAULT NULL,
`connection_latency` float DEFAULT NULL,
`query_latency` float DEFAULT NULL,
`read_only` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`instance`)
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PAGE_CHECKSUM=1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `instances`
--
DROP TABLE IF EXISTS `instances`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `instances` (
`name` varchar(300) CHARACTER SET latin1 NOT NULL,
`server` varchar(300) CHARACTER SET latin1 NOT NULL,
`port` int(10) unsigned NOT NULL DEFAULT '3306',
`version` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`last_start` timestamp NULL DEFAULT NULL,
`group` enum('core','misc','dbstore','parsercache','labsdb','test') COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `masters`
--
DROP TABLE IF EXISTS `masters`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `masters` (
`section` varchar(16) CHARACTER SET latin1 NOT NULL,
`dc` varchar(100) CHARACTER SET latin1 NOT NULL,
`instance` varchar(100) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`section`,`dc`),
KEY `instance` (`instance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `nil_grants`
--
DROP TABLE IF EXISTS `nil_grants`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `nil_grants` (
`fqdn` varchar(300) CHARACTER SET latin1 NOT NULL,
`port` int(10) unsigned NOT NULL DEFAULT '3306',
`user` varchar(100) NOT NULL COMMENT 'User with an empty password',
`host` varchar(100) NOT NULL COMMENT 'From which hosts this user is allowed to connect with an empty password',
`last_update` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`fqdn`,`user`,`host`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='This table only stores the list of users with empty passwords and their hosts';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `replication`
--
DROP TABLE IF EXISTS `replication`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `replication` (
`master` varchar(300) NOT NULL,
`replica` varchar(300) NOT NULL,
`connection_name` varchar(300) NOT NULL,
`lag` decimal(17,6) DEFAULT NULL,
`sql_thread_running` enum('yes','no','error') DEFAULT NULL,
`io_thread_running` enum('yes','no','error') DEFAULT NULL,
`io_thread_error` text,
`sql_thread_error` text,
PRIMARY KEY (`master`,`replica`,`connection_name`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `section_instances`
--
DROP TABLE IF EXISTS `section_instances`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `section_instances` (
`instance` varchar(100) CHARACTER SET latin1 NOT NULL,
`section` varchar(16) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`instance`,`section`),
KEY `section` (`section`,`instance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `sections`
--
DROP TABLE IF EXISTS `sections`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sections` (
`name` varchar(16) NOT NULL,
`mysql_group` varchar(16) DEFAULT NULL,
`standalone` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `servers`
--
DROP TABLE IF EXISTS `servers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `servers` (
`fqdn` varchar(300) CHARACTER SET latin1 NOT NULL,
`hostname` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`dc` varchar(100) DEFAULT NULL,
`rack` varchar(5) CHARACTER SET latin1 DEFAULT NULL,
`ipv4` int(10) unsigned DEFAULT NULL,
`ipv6` binary(16) DEFAULT NULL,
`last_boot` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`fqdn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `tables`
--
DROP TABLE IF EXISTS `tables`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tables` (
`instance` varchar(100) CHARACTER SET latin1 NOT NULL,
`db` varchar(100) CHARACTER SET latin1 NOT NULL,
`table_name` varchar(100) CHARACTER SET latin1 NOT NULL,
`definition` text CHARACTER SET latin1,
`format` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`logical_size` int(10) unsigned DEFAULT NULL,
`file_size` int(10) unsigned DEFAULT NULL,
`rows` bigint(20) unsigned DEFAULT NULL,
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`instance`,`db`,`table_name`),
KEY `db_table` (`db`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-02-12 11:27:40

Event Timeline

ProdPasteBot changed the title of this paste from untitled to Masterwork From Distant Lands.
jcrespo changed the title of this paste from Masterwork From Distant Lands to zarcillo SQL schema.Feb 12 2021, 11:28 AM

I have a couple of questions about the schema.

  1. I couldn't understand why the tables are not linked to each other with foreign keys for example - section_instances and section table
  1. Some of the attributes could have boolean datatype like read_only tinyint(1) . This can improve CRUD operations on tables. Am I missing something?