Fill DNS

dns_records.sql

CREATE TABLE `dns_record` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `origin` varchar(63) COLLATE utf8_bin NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `type` char(6) COLLATE utf8_bin NOT NULL,
  `value` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `origin` (`origin`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `dns_record_type` (
  `name` char(6) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `dns_zone` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `origin` varchar(63) COLLATE utf8_bin NOT NULL,
  `ns` varchar(63) COLLATE utf8_bin NOT NULL DEFAULT 'ns1.sunfox.cz.',
  `mbox` varchar(63) COLLATE utf8_bin NOT NULL DEFAULT 'sunfox.sunfox.cz.',
  `serial` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `refresh` int(11) UNSIGNED NOT NULL DEFAULT '28800',
  `retry` int(11) UNSIGNED NOT NULL DEFAULT '7200',
  `expire` int(11) UNSIGNED NOT NULL DEFAULT '604800',
  `minimum` int(11) UNSIGNED NOT NULL DEFAULT '86400',
  `ttl` int(11) UNSIGNED NOT NULL DEFAULT '3600',
  PRIMARY KEY (`id`),
  UNIQUE KEY `origin` (`origin`),
  FOREIGN KEY (`origin`) REFERENCES `dns_zone` (`origin`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`type`) REFERENCES `dns_record_type` (`name`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

fill_dns.php

<?php

$pdo = new PDO('mysql:dbname=server;host=masterdb', '', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$res = $pdo->query('SELECT `origin` FROM `dns_zone` ORDER BY `origin`');
$rows = $res->fetchAll(PDO::FETCH_ASSOC);

$pdo->exec('TRUNCATE `dns_record`');

foreach ($rows as $row) {
    $origin = $row['origin'];
    $domain = trim($origin, '.');

    $stmt = $pdo->prepare('INSERT INTO `dns_record` (`origin`, `name`, `type`, `value`) VALUES (?, ?, ?, ?), (?, ?, ?, ?)');
    $stmt->execute([
        $origin, '@', 'NS', 'ns1.sunfox.cz.',
        $origin, '@', 'NS', 'ns3.sunfox.cz.',
    ]);

    $domainA = @dns_get_record($domain, DNS_A);
    if ($domainA) {
        // echo "$domain: {$domainA[0]['ip']}" . PHP_EOL;

        $stmt = $pdo->prepare('INSERT INTO `dns_record` (`origin`, `name`, `type`, `value`) VALUES (?, ?, ?, ?)');
        $stmt->execute([$origin, '@', 'A', $domainA[0]['ip']]);

        $domainWwwA = @dns_get_record('www.' . $domain, DNS_A);
        if ($domainWwwA) {
            $stmt = $pdo->prepare('INSERT INTO `dns_record` (`origin`, `name`, `type`, `value`) VALUES (?, ?, ?, ?)');
            $stmt->execute([$origin, '*', 'A', $domainWwwA[0]['ip']]);
        }

        $domainMX = @dns_get_record($domain, DNS_MX);
        if ($domainMX) {
            foreach ($domainMX as $mx) {
                $stmt = $pdo->prepare('INSERT INTO `dns_record` (`origin`, `name`, `type`, `value`) VALUES (?, ?, ?, ?)');
                $stmt->execute([$origin, '@', 'MX', $mx['pri'].' '.$mx['target'].'.']);
            }
        }

        $domainTXT = @dns_get_record($domain, DNS_TXT);
        if ($domainTXT) {
            foreach ($domainTXT as $txt) {
                $stmt = $pdo->prepare('INSERT INTO `dns_record` (`origin`, `name`, `type`, `value`) VALUES (?, ?, ?, ?)');
                $stmt->execute([$origin, '@', 'TXT', $txt['txt']]);
            }
        }

        $googleTXT = @dns_get_record('google._domainkey.'.$domain, DNS_TXT);
        if ($googleTXT) {
            $stmt = $pdo->prepare('INSERT INTO `dns_record` (`origin`, `name`, `type`, `value`) VALUES (?, ?, ?, ?)');
            $stmt->execute([$origin, 'google._domainkey', 'TXT', $googleTXT[0]['txt']]);
        }

        $mailTXT = @dns_get_record('mail._domainkey.'.$domain, DNS_TXT);
        if ($mailTXT) {
            $stmt = $pdo->prepare('INSERT INTO `dns_record` (`origin`, `name`, `type`, `value`) VALUES (?, ?, ?, ?)');
            $stmt->execute([$origin, 'mail._domainkey', 'TXT', $mailTXT[0]['txt']]);
        }

        $stmt = $pdo->prepare('UPDATE `dns_zone` SET `serial` = ? WHERE `origin` = ?');
        $stmt->execute([date('Ymd').'01', $origin]);
    }
}