/*
Standard PDNS file 
Delainhosting 2018
Updates may 2020 4.3 schema
*/
CREATE DATABASE
IF NOT EXISTS pdns_db;

USE pdns_db;
CREATE TABLE
IF NOT EXISTS domains
(
  id INT
  AUTO_INCREMENT,
  name                  VARCHAR
(255) NOT NULL,
  master                VARCHAR
(128) DEFAULT NULL,
  last_check            INT DEFAULT NULL,
  type                  VARCHAR
(6) NOT NULL,
  notified_serial       INT UNSIGNED DEFAULT NULL,
  account               VARCHAR
(40) CHARACTER
SET 'utf8'
DEFAULT NULL,
  PRIMARY KEY
(id)
) Engine=InnoDB CHARACTER
SET 'latin1';

CREATE UNIQUE INDEX
IF NOT EXISTS name_index ON domains
(name);

CREATE TABLE
IF NOT EXISTS records
(
    id BIGINT
    AUTO_INCREMENT,
  domain_id             INT DEFAULT NULL,
  name                  VARCHAR
(255) DEFAULT NULL,
  type                  VARCHAR
(10) DEFAULT NULL,
  content               VARCHAR
(64000) DEFAULT NULL,
  ttl                   INT DEFAULT NULL,
  prio                  INT DEFAULT NULL,
  disabled              TINYINT
(1) DEFAULT 0,
  ordername             VARCHAR
(255) BINARY DEFAULT NULL,
  auth                  TINYINT
(1) DEFAULT 1,
  PRIMARY KEY
(id)
) Engine=InnoDB CHARACTER
SET 'latin1';

CREATE INDEX
IF NOT EXISTS nametype_index ON records
(name,type);
CREATE INDEX
IF NOT EXISTS domain_id ON records
(domain_id);
CREATE INDEX
IF NOT EXISTS ordername ON records
(ordername);


CREATE TABLE
IF NOT EXISTS supermasters
(
      ip VARCHAR
(64) NOT NULL,
      nameserver VARCHAR
(255) NOT NULL,
      account VARCHAR
(40)
      CHARACTER
SET 'utf8'
NOT NULL,
  PRIMARY KEY
(ip, nameserver)
) Engine=InnoDB CHARACTER
SET 'latin1';
--
-- Dumping data for table `supermasters`
--

LOCK TABLES `supermasters` WRITE;
/*!40000 ALTER TABLE `supermasters` DISABLE KEYS */;
INSERT INTO `supermasters` VALUES ('209.145.52.110','dns1.delainhosting.com','admin');
INSERT INTO `supermasters`VALUES ('144.91.108.77', 'dns2.delainhosting.com', 'admin');
INSERT INTO `supermasters` VALUES ('209.126.81.64','dns3.delainhosting.com','admin');
INSERT INTO `supermasters`VALUES ('154.12.224.183', 'dns4.delainhosting.com', 'admin');
/*!40000 ALTER TABLE `supermasters` ENABLE KEYS */;
UNLOCK TABLES;


CREATE TABLE
IF NOT EXISTS comments
(
        id INT
        AUTO_INCREMENT,
  domain_id             INT NOT NULL,
  name                  VARCHAR
(255) NOT NULL,
  type                  VARCHAR
(10) NOT NULL,
  modified_at           INT NOT NULL,
  account               VARCHAR
(40) CHARACTER
SET 'utf8'
DEFAULT NULL,
  comment               TEXT CHARACTER
SET 'utf8'
NOT NULL,
  PRIMARY KEY
(id)
) Engine=InnoDB CHARACTER
SET 'latin1';

CREATE INDEX
IF NOT EXISTS comments_name_type_idx ON comments
(name, type);
CREATE INDEX
IF NOT EXISTS comments_order_idx ON comments
(domain_id, modified_at);


CREATE TABLE
IF NOT EXISTS domainmetadata
(
          id INT
          AUTO_INCREMENT,
  domain_id             INT NOT NULL,
  kind                  VARCHAR
(32),
  content               TEXT,
  PRIMARY KEY
(id)
) Engine=InnoDB CHARACTER
SET 'latin1';

CREATE INDEX
IF NOT EXISTS domainmetadata_idx ON domainmetadata
(domain_id, kind);


CREATE TABLE
IF NOT EXISTS cryptokeys
(
            id INT
            AUTO_INCREMENT,
  domain_id             INT NOT NULL,
  flags                 INT NOT NULL,
  active                BOOL,
  published             BOOL DEFAULT 1,
  content               TEXT,
  PRIMARY KEY
(id)
) Engine=InnoDB CHARACTER
SET 'latin1';

CREATE INDEX
IF NOT EXISTS domainidindex ON cryptokeys
(domain_id);


CREATE TABLE
IF NOT EXISTS tsigkeys
(
              id INT
              AUTO_INCREMENT,
  name                  VARCHAR
(255),
  algorithm             VARCHAR
(50),
  secret                VARCHAR
(255),
  PRIMARY KEY
(id)
) Engine=InnoDB CHARACTER
SET 'latin1';

CREATE UNIQUE INDEX
IF NOT EXISTS namealgoindex ON tsigkeys
(name, algorithm);

/*
Using this SQL causes Mysql to create foreign keys on your database. This will
make sure that no records, comments or keys exists for domains that you already
removed. This is not enabled by default, because we're not sure what the
consequences are from a performance point of view. If you do have feedback,
please let us know how this affects your setup.

Please note that it's not possible to apply this, before you cleaned up your
database, as the foreign keys do not exist.
*/
ALTER TABLE records ADD CONSTRAINT `records_domain_id_ibfk` FOREIGN KEY
(`domain_id`) REFERENCES `domains`
(`id`) ON
DELETE CASCADE ON
UPDATE CASCADE;
ALTER TABLE comments ADD CONSTRAINT `comments_domain_id_ibfk` FOREIGN KEY
(`domain_id`) REFERENCES `domains`
(`id`) ON
DELETE CASCADE ON
UPDATE CASCADE;
ALTER TABLE domainmetadata ADD CONSTRAINT `domainmetadata_domain_id_ibfk` FOREIGN KEY
(`domain_id`) REFERENCES `domains`
(`id`) ON
DELETE CASCADE ON
UPDATE CASCADE;
ALTER TABLE cryptokeys ADD CONSTRAINT `cryptokeys_domain_id_ibfk` FOREIGN KEY
(`domain_id`) REFERENCES `domains`
(`id`) ON
DELETE CASCADE ON
UPDATE CASCADE;
