2018-03-23 17:16:39 +00:00
-- phpMyAdmin SQL Dump
-- version 4.5.4.1deb2ubuntu2
-- http://www.phpmyadmin.net
--
-- Host: localhost
2019-12-25 22:30:54 +00:00
-- Erstellungszeit: 25. Dez 2019 um 23:22
-- Server-Version: 5.7.23-0ubuntu0.16.04.1
-- PHP-Version: 7.0.30-0ubuntu0.16.04.1
2018-03-23 17:16:39 +00:00
2019-12-25 22:30:54 +00:00
SET FOREIGN_KEY_CHECKS = 0 ;
2018-03-23 17:16:39 +00:00
SET SQL_MODE = " NO_AUTO_VALUE_ON_ZERO " ;
SET time_zone = " +00:00 " ;
2019-12-25 22:43:48 +00:00
DROP TABLE IF EXISTS ` comments ` , ` cryptokeys ` , ` domainmetadata ` , ` domains ` , ` options ` , ` permissions ` , ` records ` , ` remote ` , ` supermasters ` , ` tsigkeys ` , ` users ` ;
2018-03-23 17:16:39 +00:00
/* !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 */ ;
2018-03-31 10:59:12 +00:00
--
2019-12-25 22:30:54 +00:00
-- Datenbank: `pdnsnew`
2018-03-31 10:59:12 +00:00
--
2018-03-23 17:16:39 +00:00
-- --------------------------------------------------------
--
2019-12-25 22:30:54 +00:00
-- Tabellenstruktur für Tabelle `comments`
2018-03-23 17:16:39 +00:00
--
DROP TABLE IF EXISTS ` comments ` ;
CREATE TABLE ` comments ` (
` id ` int ( 11 ) NOT NULL ,
` domain_id ` int ( 11 ) NOT NULL ,
` name ` varchar ( 255 ) NOT NULL ,
` type ` varchar ( 10 ) NOT NULL ,
` modified_at ` int ( 11 ) NOT NULL ,
` account ` varchar ( 40 ) CHARACTER SET utf8 DEFAULT NULL ,
` comment ` text CHARACTER SET utf8 NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1 ;
-- --------------------------------------------------------
--
2019-12-25 22:30:54 +00:00
-- Tabellenstruktur für Tabelle `cryptokeys`
2018-03-23 17:16:39 +00:00
--
DROP TABLE IF EXISTS ` cryptokeys ` ;
CREATE TABLE ` cryptokeys ` (
` id ` int ( 11 ) NOT NULL ,
` domain_id ` int ( 11 ) NOT NULL ,
` flags ` int ( 11 ) NOT NULL ,
` active ` tinyint ( 1 ) DEFAULT NULL ,
` content ` text
) ENGINE = InnoDB DEFAULT CHARSET = latin1 ;
-- --------------------------------------------------------
--
2019-12-25 22:30:54 +00:00
-- Tabellenstruktur für Tabelle `domainmetadata`
2018-03-23 17:16:39 +00:00
--
DROP TABLE IF EXISTS ` domainmetadata ` ;
CREATE TABLE ` domainmetadata ` (
` id ` int ( 11 ) NOT NULL ,
` domain_id ` int ( 11 ) NOT NULL ,
` kind ` varchar ( 32 ) DEFAULT NULL ,
` content ` text
) ENGINE = InnoDB DEFAULT CHARSET = latin1 ;
-- --------------------------------------------------------
--
2019-12-25 22:30:54 +00:00
-- Tabellenstruktur für Tabelle `domains`
2018-03-23 17:16:39 +00:00
--
DROP TABLE IF EXISTS ` domains ` ;
CREATE TABLE ` domains ` (
` id ` int ( 11 ) NOT NULL ,
` name ` varchar ( 255 ) NOT NULL ,
` master ` varchar ( 128 ) DEFAULT NULL ,
` last_check ` int ( 11 ) DEFAULT NULL ,
` type ` varchar ( 6 ) NOT NULL ,
` notified_serial ` int ( 10 ) UNSIGNED DEFAULT NULL ,
` account ` varchar ( 40 ) CHARACTER SET utf8 DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1 ;
--
2019-12-25 22:30:54 +00:00
-- Daten für Tabelle `domains`
2018-03-23 17:16:39 +00:00
--
INSERT INTO ` domains ` ( ` id ` , ` name ` , ` master ` , ` last_check ` , ` type ` , ` notified_serial ` , ` account ` ) VALUES
( 1 , ' example.com ' , NULL , NULL , ' MASTER ' , NULL , NULL ) ,
2018-03-24 12:32:34 +00:00
( 2 , ' slave.example.net ' , ' 12.34.56.78 ' , NULL , ' SLAVE ' , NULL , NULL ) ,
( 3 , ' foo.de ' , NULL , NULL , ' NATIVE ' , NULL , NULL ) ,
( 4 , ' bar.net ' , NULL , NULL , ' MASTER ' , NULL , NULL ) ,
( 5 , ' baz.org ' , NULL , NULL , ' MASTER ' , NULL , NULL ) ;
2018-03-23 17:16:39 +00:00
-- --------------------------------------------------------
--
2019-12-25 22:30:54 +00:00
-- Tabellenstruktur für Tabelle `options`
--
DROP TABLE IF EXISTS ` options ` ;
CREATE TABLE ` options ` (
` name ` varchar ( 255 ) NOT NULL ,
` value ` varchar ( 2000 ) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8 ;
--
-- Daten für Tabelle `options`
--
INSERT INTO ` options ` ( ` name ` , ` value ` ) VALUES
( ' schema_version ' , ' 7 ' ) ;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `permissions`
2018-03-23 17:16:39 +00:00
--
DROP TABLE IF EXISTS ` permissions ` ;
CREATE TABLE ` permissions ` (
` domain_id ` int ( 11 ) NOT NULL ,
` user_id ` int ( 11 ) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8 ;
2018-03-24 14:18:15 +00:00
--
2019-12-25 22:30:54 +00:00
-- Daten für Tabelle `permissions`
2018-03-24 14:18:15 +00:00
--
INSERT INTO ` permissions ` ( ` domain_id ` , ` user_id ` ) VALUES
( 1 , 2 ) ,
( 2 , 2 ) ;
2018-03-23 17:16:39 +00:00
-- --------------------------------------------------------
--
2019-12-25 22:30:54 +00:00
-- Tabellenstruktur für Tabelle `records`
2018-03-23 17:16:39 +00:00
--
DROP TABLE IF EXISTS ` records ` ;
CREATE TABLE ` records ` (
` id ` bigint ( 20 ) NOT NULL ,
` domain_id ` int ( 11 ) DEFAULT NULL ,
` name ` varchar ( 255 ) DEFAULT NULL ,
` type ` varchar ( 10 ) DEFAULT NULL ,
` content ` varchar ( 64000 ) DEFAULT NULL ,
` ttl ` int ( 11 ) DEFAULT NULL ,
` prio ` int ( 11 ) DEFAULT NULL ,
` change_date ` int ( 11 ) DEFAULT NULL ,
` disabled ` tinyint ( 1 ) DEFAULT ' 0 ' ,
` ordername ` varchar ( 255 ) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL ,
` auth ` tinyint ( 1 ) DEFAULT ' 1 '
) ENGINE = InnoDB DEFAULT CHARSET = latin1 ;
--
2019-12-25 22:30:54 +00:00
-- Daten für Tabelle `records`
2018-03-23 17:16:39 +00:00
--
INSERT INTO ` records ` ( ` id ` , ` domain_id ` , ` name ` , ` type ` , ` content ` , ` ttl ` , ` prio ` , ` change_date ` , ` disabled ` , ` ordername ` , ` auth ` ) VALUES
2018-03-29 13:16:54 +00:00
( 1 , 1 , ' test.example.com ' , ' A ' , ' 12.34.56.78 ' , 86400 , 0 , 1521645110 , 0 , NULL , 1 ) ,
( 2 , 1 , ' sdfdf.example.com ' , ' TXT ' , ' foo bar baz ' , 60 , 10 , 1522321931 , 0 , NULL , 1 ) ,
( 3 , 1 , ' foo.example.com ' , ' AAAA ' , ' ::1 ' , 86400 , 0 , 1522321902 , 0 , NULL , 1 ) ,
2018-04-13 15:13:53 +00:00
( 4 , 3 , ' foo.de ' , ' A ' , ' 9.8.7.6 ' , 86400 , 0 , 1522321989 , 0 , NULL , 1 ) ,
( 5 , 1 , ' example.com ' , ' SOA ' , ' ns1.example.com hostmaster.example.com 2018041300 3600 900 604800 86400 ' , 86400 , NULL , 1523629358 , 0 , NULL , 1 ) ;
2018-03-23 17:16:39 +00:00
-- --------------------------------------------------------
2018-03-31 10:59:12 +00:00
--
2019-12-25 22:30:54 +00:00
-- Tabellenstruktur für Tabelle `remote`
2018-03-31 10:59:12 +00:00
--
DROP TABLE IF EXISTS ` remote ` ;
CREATE TABLE ` remote ` (
` id ` int ( 11 ) NOT NULL ,
2019-12-25 22:30:54 +00:00
` record ` bigint ( 20 ) DEFAULT NULL ,
2018-03-31 10:59:12 +00:00
` description ` varchar ( 255 ) NOT NULL ,
` type ` varchar ( 20 ) NOT NULL ,
` security ` varchar ( 2000 ) NOT NULL ,
` nonce ` varchar ( 255 ) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8 ;
--
2019-12-25 22:30:54 +00:00
-- Daten für Tabelle `remote`
2018-03-31 10:59:12 +00:00
--
INSERT INTO ` remote ` ( ` id ` , ` record ` , ` description ` , ` type ` , ` security ` , ` nonce ` ) VALUES
2018-04-25 17:35:11 +00:00
( 1 , 1 , ' Password Test ' , ' password ' , ' $2y$10$abocd6jj/Tw4jzDtqTnjreNzwcerzkXwoVc.JvZBoZ6p0grEKDWoW ' , NULL ) ,
2018-03-31 10:59:12 +00:00
( 2 , 4 , ' Key Test ' , ' key ' , ' -----BEGIN PUBLIC KEY-----\nMIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEA5mu3aH90uSXY9sVLgVSz\nKj4FEctrpFDPyVC4ufbJa/44fuLABFe+IizgZUheNBBO7FjpLJYvsL24o6TEeht4\no5j0KHrRHXqp4WQuAL3ZREv/AhNaOC9/xyjoGwUkKkdC2bIfh0J/ACkezxvUrPsh\nbzhzY+co/M9PqlgTbjKjvlv/pRj2dSp98FzUme3HCh7Nn1EOM3yPMtaKNA9Qkkz1\noalfR3xmJjIanoS9zcK77/yyQ8VwI//CgxvnpnWbORZG0B9W2ZBoI8Bj4zprbbFG\nKNmrb403wfDijYF3MXpSMjKvJ5YVuZsn35EWIi5tqFc0oV7Ryy9nBHzKeoYN7Szs\nrXIS5+ZcQDLuN+pqJ7ByVaw4aVn85py8IdO0IYD5xeKd1i0iqm+KSoFTS1jiNSZu\n6iVl4odixWtW7oPLYBbd/vD2F7Ua5cLd12Rs+6kEVtlpnIf7txyFQL4QHYJxB7fI\ny+m70mfufVvKbFh/mHkhe+Arv71ERDMfAV3AD8++axLqYfU/LLFzanjwIBctAA9a\nj++G0lwl1adURwnBeq8+YrMU4/wg9efquKXLR40dU9nkMJOm5tPm+XHt4o3wio4X\n2FqnD57I7qJCWVc00HtpeWno5vHL+eJu0TdxjBuYXnQfwa1z9pWvGaoBtg7tyHgv\ng7YZJzF1MW5N9ZqnkdFJVEsCAwEAAQ==\n-----END PUBLIC KEY----- ' , NULL ) ,
2018-04-29 14:47:20 +00:00
( 3 , 1 , ' Key Test 2 ' , ' key ' , ' -----BEGIN PUBLIC KEY-----\nMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCrJ/UoQoN5rO1nwrWBNDr3TgPB\nkm6UmN/B6NY7RXcYTJOFEP6iWqTj9Pw8aT8/DSn2uTMeQK6kWNUAWmRaylQI2QHQ\ndPtrI6piTpjvKm+KbR+n3e4QJ/zOcg06cHYJJiyhPjfC12j3ZxINOV3LDbEKq4s0\nHxMGYZHPu+UezapeeQIDAQAB\n-----END PUBLIC KEY----- ' , NULL ) ;
2018-03-31 10:59:12 +00:00
-- --------------------------------------------------------
2018-03-23 17:16:39 +00:00
--
2019-12-25 22:30:54 +00:00
-- Tabellenstruktur für Tabelle `supermasters`
2018-03-23 17:16:39 +00:00
--
DROP TABLE IF EXISTS ` supermasters ` ;
CREATE TABLE ` supermasters ` (
` ip ` varchar ( 64 ) NOT NULL ,
` nameserver ` varchar ( 255 ) NOT NULL ,
` account ` varchar ( 40 ) CHARACTER SET utf8 NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1 ;
-- --------------------------------------------------------
--
2019-12-25 22:30:54 +00:00
-- Tabellenstruktur für Tabelle `tsigkeys`
2018-03-23 17:16:39 +00:00
--
DROP TABLE IF EXISTS ` tsigkeys ` ;
CREATE TABLE ` tsigkeys ` (
` id ` int ( 11 ) NOT NULL ,
` name ` varchar ( 255 ) DEFAULT NULL ,
` algorithm ` varchar ( 50 ) DEFAULT NULL ,
` secret ` varchar ( 255 ) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1 ;
-- --------------------------------------------------------
2018-04-29 17:03:01 +00:00
--
2019-12-25 22:30:54 +00:00
-- Tabellenstruktur für Tabelle `users`
2018-03-23 17:16:39 +00:00
--
DROP TABLE IF EXISTS ` users ` ;
CREATE TABLE ` users ` (
` id ` int ( 11 ) NOT NULL ,
` name ` varchar ( 50 ) NOT NULL ,
` backend ` varchar ( 50 ) NOT NULL ,
` type ` varchar ( 20 ) NOT NULL ,
` password ` varchar ( 255 ) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8 ;
--
2019-12-25 22:30:54 +00:00
-- Daten für Tabelle `users`
2018-03-24 14:18:15 +00:00
--
2018-03-23 17:16:39 +00:00
INSERT INTO ` users ` ( ` id ` , ` name ` , ` backend ` , ` type ` , ` password ` ) VALUES
2018-03-24 13:48:49 +00:00
( 1 , ' admin ' , ' native ' , ' admin ' , ' $2y$10$9iIDHWgjY0pEsz8pZLXPx.gkMNDxTMzb7U0Um5hUGjKmUUHWQNXcW ' ) ,
2018-04-05 12:53:56 +00:00
( 2 , ' user ' , ' native ' , ' user ' , ' $2y$10$MktCI4XcfD0FpIFSkxex6OVifnIw3Nqw6QJueWmjVte99wx6XGBoq ' ) ,
( 3 , ' configuser ' , ' config ' , ' user ' , NULL ) ;
2018-03-23 17:16:39 +00:00
--
2019-12-25 22:30:54 +00:00
-- Indizes der exportierten Tabellen
2018-03-23 17:16:39 +00:00
--
--
2019-12-25 22:30:54 +00:00
-- Indizes für die Tabelle `comments`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` comments `
ADD PRIMARY KEY ( ` id ` ) ,
ADD KEY ` comments_name_type_idx ` ( ` name ` , ` type ` ) ,
ADD KEY ` comments_order_idx ` ( ` domain_id ` , ` modified_at ` ) ;
--
2019-12-25 22:30:54 +00:00
-- Indizes für die Tabelle `cryptokeys`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` cryptokeys `
ADD PRIMARY KEY ( ` id ` ) ,
ADD KEY ` domainidindex ` ( ` domain_id ` ) ;
--
2019-12-25 22:30:54 +00:00
-- Indizes für die Tabelle `domainmetadata`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` domainmetadata `
ADD PRIMARY KEY ( ` id ` ) ,
ADD KEY ` domainmetadata_idx ` ( ` domain_id ` , ` kind ` ) ;
--
2019-12-25 22:30:54 +00:00
-- Indizes für die Tabelle `domains`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` domains `
2019-12-25 22:30:54 +00:00
ADD PRIMARY KEY ( ` id ` ) ;
2018-03-23 17:16:39 +00:00
--
2019-12-25 22:30:54 +00:00
-- Indizes für die Tabelle `options`
--
ALTER TABLE ` options `
ADD PRIMARY KEY ( ` name ` ) ;
--
-- Indizes für die Tabelle `permissions`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` permissions `
2019-12-25 22:30:54 +00:00
ADD PRIMARY KEY ( ` domain_id ` , ` user_id ` ) ,
ADD KEY ` permissions_ibfk_2 ` ( ` user_id ` ) ;
2018-03-23 17:16:39 +00:00
--
2019-12-25 22:30:54 +00:00
-- Indizes für die Tabelle `records`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` records `
ADD PRIMARY KEY ( ` id ` ) ,
ADD KEY ` nametype_index ` ( ` name ` , ` type ` ) ,
ADD KEY ` domain_id ` ( ` domain_id ` ) ,
ADD KEY ` ordername ` ( ` ordername ` ) ;
2018-03-31 10:59:12 +00:00
--
2019-12-25 22:30:54 +00:00
-- Indizes für die Tabelle `remote`
2018-03-31 10:59:12 +00:00
--
ALTER TABLE ` remote `
2019-12-25 22:30:54 +00:00
ADD PRIMARY KEY ( ` id ` ) ,
ADD KEY ` remote_ibfk_1 ` ( ` record ` ) ;
2018-03-31 10:59:12 +00:00
2018-03-23 17:16:39 +00:00
--
2019-12-25 22:30:54 +00:00
-- Indizes für die Tabelle `supermasters`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` supermasters `
ADD PRIMARY KEY ( ` ip ` , ` nameserver ` ) ;
--
2019-12-25 22:30:54 +00:00
-- Indizes für die Tabelle `tsigkeys`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` tsigkeys `
ADD PRIMARY KEY ( ` id ` ) ,
ADD UNIQUE KEY ` namealgoindex ` ( ` name ` , ` algorithm ` ) ;
--
2019-12-25 22:30:54 +00:00
-- Indizes für die Tabelle `users`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` users `
ADD PRIMARY KEY ( ` id ` ) ;
--
2019-12-25 22:30:54 +00:00
-- AUTO_INCREMENT für exportierte Tabellen
2018-03-23 17:16:39 +00:00
--
--
2019-12-25 22:30:54 +00:00
-- AUTO_INCREMENT für Tabelle `comments`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` comments `
MODIFY ` id ` int ( 11 ) NOT NULL AUTO_INCREMENT ;
--
2019-12-25 22:30:54 +00:00
-- AUTO_INCREMENT für Tabelle `cryptokeys`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` cryptokeys `
MODIFY ` id ` int ( 11 ) NOT NULL AUTO_INCREMENT ;
--
2019-12-25 22:30:54 +00:00
-- AUTO_INCREMENT für Tabelle `domainmetadata`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` domainmetadata `
MODIFY ` id ` int ( 11 ) NOT NULL AUTO_INCREMENT ;
--
2019-12-25 22:30:54 +00:00
-- AUTO_INCREMENT für Tabelle `domains`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` domains `
2018-03-24 12:32:34 +00:00
MODIFY ` id ` int ( 11 ) NOT NULL AUTO_INCREMENT , AUTO_INCREMENT = 6 ;
2018-03-23 17:16:39 +00:00
--
2019-12-25 22:30:54 +00:00
-- AUTO_INCREMENT für Tabelle `records`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` records `
2019-12-25 22:30:54 +00:00
MODIFY ` id ` bigint ( 20 ) NOT NULL AUTO_INCREMENT , AUTO_INCREMENT = 6 ;
2018-03-23 17:16:39 +00:00
--
2019-12-25 22:30:54 +00:00
-- AUTO_INCREMENT für Tabelle `remote`
2018-03-31 10:59:12 +00:00
--
ALTER TABLE ` remote `
MODIFY ` id ` int ( 11 ) NOT NULL AUTO_INCREMENT , AUTO_INCREMENT = 4 ;
--
2019-12-25 22:30:54 +00:00
-- AUTO_INCREMENT für Tabelle `tsigkeys`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` tsigkeys `
MODIFY ` id ` int ( 11 ) NOT NULL AUTO_INCREMENT ;
--
2019-12-25 22:30:54 +00:00
-- AUTO_INCREMENT für Tabelle `users`
2018-03-23 17:16:39 +00:00
--
ALTER TABLE ` users `
2018-04-05 12:53:56 +00:00
MODIFY ` id ` int ( 11 ) NOT NULL AUTO_INCREMENT , AUTO_INCREMENT = 4 ;
2019-12-25 22:30:54 +00:00
--
-- Constraints der exportierten Tabellen
--
--
-- Constraints der Tabelle `permissions`
--
ALTER TABLE ` permissions `
ADD CONSTRAINT ` permissions_ibfk_1 ` FOREIGN KEY ( ` domain_id ` ) REFERENCES ` domains ` ( ` id ` ) ON DELETE CASCADE ,
ADD CONSTRAINT ` permissions_ibfk_2 ` FOREIGN KEY ( ` user_id ` ) REFERENCES ` users ` ( ` id ` ) ON DELETE CASCADE ;
--
-- Constraints der Tabelle `records`
--
ALTER TABLE ` records `
ADD CONSTRAINT ` records_ibfk_1 ` FOREIGN KEY ( ` domain_id ` ) REFERENCES ` domains ` ( ` id ` ) ON DELETE CASCADE ;
--
-- Constraints der Tabelle `remote`
--
ALTER TABLE ` remote `
ADD CONSTRAINT ` remote_ibfk_1 ` FOREIGN KEY ( ` record ` ) REFERENCES ` records ` ( ` id ` ) ;
SET FOREIGN_KEY_CHECKS = 1 ;
2018-03-23 17:16:39 +00:00
/* !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 */ ;