From 58333d7d02c627a2f3445fd31b664c97feaa6e32 Mon Sep 17 00:00:00 2001 From: Pieter Lexis Date: Fri, 11 Dec 2015 21:02:36 +0100 Subject: [PATCH] docs: Expand opendbx-backend information This is mostly ported from @nos3's website (thanks!) Closes: #445 --- .../markdown/authoritative/backend-opendbx.md | 745 +++++++++++++++++- 1 file changed, 743 insertions(+), 2 deletions(-) diff --git a/docs/markdown/authoritative/backend-opendbx.md b/docs/markdown/authoritative/backend-opendbx.md index bcc964d17..d04d8b944 100644 --- a/docs/markdown/authoritative/backend-opendbx.md +++ b/docs/markdown/authoritative/backend-opendbx.md @@ -13,6 +13,747 @@ The OpenDBX backend allows the authoritative server to connect to any backend supported by [OpenDBX](http://www.linuxnetworks.de/doc/index.php/OpenDBX). -Full documentation is supplied by the author Norbert Sendetzky [here](http://www.linuxnetworks.de/doc/index.php/PowerDNS_OpenDBX_Backend). +This document contains a subset of the [full documentation](http://www.linuxnetworks.de/doc/index.php/PowerDNS_OpenDBX_Backend) +supplied by the author Norbert Sendetzky . This module is fully supported (and +tested) by PowerDNS. -This module is fully supported (and tested) by PowerDNS. +The OpenDBX backend has a mechanism to connect different database servers for +read and write actions. + +The domains table for the opendbx backend has a "status" column, when set to "A", +the domain is considered active and is actually served. + +# Settings +## opendbx-backend +Name of the backend used to connect to the database server. Currently mysql, +pgsql, sqlite, sqlite3 and sybase are available. Default=mysql. + +## opendbx-host-read +One or more host names or IP addresses of the database servers. These hosts will +be used for retrieving the records via SELECT queries. Default=127.0.0.1 + +## opendbx-host-write +One or more host names or IP addresses of the database servers. These hosts will +be used for INSERT/UPDATE statements (mostly used by zonetransfers). Default=127.0.0.1 + +## opendbx-port +TCP/IP port number where the database server is listening to. Most databases will +use their default port if you leave this empty. + +## opendbx-database +The database name where all domain and record entries are stored. Default=powerdns + +## opendbx-username +Name of the user send to the DBMS for authentication. Default=powerdns. + +## opendbx-password +Clear text password for authentication in combination with the username. + +## Queries +As with the [Generic SQL backends](backend-generic-sql.md), queries are configurable. +Note: If you change one of the SELECT statements must not change the order of +the retrieved columns! To get the default queries, run `pdns_server --no-config --launch=opendbx --config`. +The following queries are configurable: + +- `opendbx-sql-list`: Select records which will be returned to clients asking for zone transfers (AXFR). +- `opendbx-sql-lookup`: Retrieve DNS records by name. +- `opendbx-sql-lookupid`: Retrieve DNS records by id and name. +- `opendbx-sql-lookuptype`: Retrieve DNS records by name and type. +- `opendbx-sql-lookuptypeid`: Retrieve DNS records by id, name and type. +- `opendbx-sql-lookupsoa`: Retrieve SOA record for domain. +- `opendbx-sql-zonedelete`: Delete all records from zone before inserting new ones via AXFR. +- `opendbx-sql-zoneinfo`: Get stored information about a domain. +- `opendbx-sql-transactbegin`: Start transaction before updating a zone via AXFR. +- `opendbx-sql-transactend`: Commit transaction after updating a zone via AXFR. +- `opendbx-sql-transactabort`: Undo changes if an error occurred while updating a zone via AXFR. +- `opendbx-sql-insert-slave`: Adds a new zone from the authoritative DNS server which is currently retrieved via AXFR. +- `opendbx-sql-insert-record`: Adds new records of a zone form the authoritative DNS server which are currently retrieved via AXFR. +- `opendbx-sql-update-serial`: Set zone serial to value of last update. +- `opendbx-sql-update-lastcheck`: Set time of last zone check. +- `opendbx-sql-master`: Get master record for zone. +- `opendbx-sql-supermaster`: Get supermaster info. +- `opendbx-sql-infoslaves`: Get all unfresh slaves. +- `opendbx-sql-infomasters`: Get all updates masters. + +# Database schemas and information +## Mysql +The file below also contains trigger definitions which are necessary for [auto serial](backend-generic-sql.md#autoserial) +support, but they are only available in MySQL 5 and later. If you are still +using MySQL 4.x and don't want to utilize the automatically generated zone serials, +you can safely remove the "CREATE TRIGGER" statements from the file before +creating the database tables. + +``` +SET SESSION sql_mode='ANSI'; + +CREATE TABLE "domains" ( + "id" INTEGER NOT NULL AUTO_INCREMENT, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "master" VARCHAR(40) NOT NULL DEFAULT '', + "account" VARCHAR(40) NOT NULL DEFAULT '', + "last_check" INTEGER DEFAULT NULL, + "notified_serial" INTEGER DEFAULT NULL, + "auto_serial" INTEGER NOT NULL DEFAULT 0, + "status" CHAR(1) NOT NULL DEFAULT 'A', +CONSTRAINT "pdns_pk_domains_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_unq_domains_name" + UNIQUE ("name") +) type=InnoDB; + +CREATE INDEX "pdns_idx_domains_status_type" ON "domains" ("status","type"); + +CREATE TABLE "records" ( + "id" INTEGER NOT NULL AUTO_INCREMENT, + "domain_id" INTEGER NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "ttl" INTEGER DEFAULT NULL, + "prio" INTEGER DEFAULT NULL, + "content" VARCHAR(255) NOT NULL, +CONSTRAINT "pdns_pk_records_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_fk_records_domainid" + FOREIGN KEY ("domain_id") + REFERENCES "domains" ("id") + ON UPDATE CASCADE + ON DELETE CASCADE +) type=InnoDB; + +CREATE INDEX "pdns_idx_records_name_type" ON "records" ("name","type"); +CREATE INDEX "pdns_idx_records_type" ON "records" ("type"); + +CREATE TABLE "supermasters" ( + "ip" VARCHAR(40) NOT NULL, + "nameserver" VARCHAR(255) NOT NULL, + "account" VARCHAR(40) NOT NULL DEFAULT '' +); + +CREATE INDEX "pdns_idx_smaster_ip_ns" ON "supermasters" ("ip","nameserver"); + +GRANT SELECT ON "supermasters" TO "powerdns"; +GRANT ALL ON "domains" TO "powerdns"; +GRANT ALL ON "records" TO "powerdns"; + +DELIMITER : + +CREATE TRIGGER "pdns_trig_records_insert" +AFTER INSERT ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" d SET d."auto_serial" = d."auto_serial" + 1 + WHERE d."id" = NEW."domain_id"; +END;: + +CREATE TRIGGER "pdns_trig_records_update" +AFTER UPDATE ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" d SET d."auto_serial" = d."auto_serial" + 1 + WHERE d."id" = NEW."domain_id"; +END;: + +CREATE TRIGGER "pdns_trig_records_delete" +AFTER DELETE ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" d SET d."auto_serial" = d."auto_serial" + 1 + WHERE d."id" = OLD."domain_id"; +END;: + +DELIMITER ; +``` + +## PostgreSQL +``` +CREATE TABLE "domains" ( + "id" SERIAL NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "master" VARCHAR(40) NOT NULL DEFAULT '', + "account" VARCHAR(40) NOT NULL DEFAULT '', + "last_check" INTEGER DEFAULT NULL, + "notified_serial" INTEGER DEFAULT NULL, + "auto_serial" INTEGER NOT NULL DEFAULT 0, + "status" CHAR(1) NOT NULL DEFAULT 'A', +CONSTRAINT "pdns_pk_domains_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_unq_domains_name" + UNIQUE ("name") +); + +CREATE INDEX "pdns_idx_domains_status_type" ON "domains" ("status","type"); + +CREATE TABLE "records" ( + "id" SERIAL NOT NULL, + "domain_id" INTEGER NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "ttl" INTEGER DEFAULT NULL, + "prio" INTEGER DEFAULT NULL, + "content" VARCHAR(255) NOT NULL, +CONSTRAINT "pdns_pk_records_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_fk_records_domainid" + FOREIGN KEY ("domain_id") + REFERENCES "domains" ("id") + ON UPDATE CASCADE + ON DELETE CASCADE +); + +CREATE INDEX "pdns_idx_records_name_type" ON "records" ("name","type"); +CREATE INDEX "pdns_idx_records_type" ON "records" ("type"); + +CREATE TABLE "supermasters" ( + "ip" VARCHAR(40) NOT NULL, + "nameserver" VARCHAR(255) NOT NULL, + "account" VARCHAR(40) NOT NULL DEFAULT '' +); + +CREATE INDEX "pdns_idx_smaster_ip_ns" ON "supermasters" ("ip","nameserver"); + +GRANT SELECT ON "supermasters" TO "powerdns"; +GRANT ALL ON "domains" TO "powerdns"; +GRANT ALL ON "domains_id_seq" TO "powerdns"; +GRANT ALL ON "records" TO "powerdns"; +GRANT ALL ON "records_id_seq" TO "powerdns"; + +CREATE RULE "pdns_rule_records_insert" +AS ON INSERT TO "records" DO + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 WHERE "id" = NEW."domain_id"; + +CREATE RULE "pdns_rule_records_update" +AS ON UPDATE TO "records" DO + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 WHERE "id" = NEW."domain_id"; + +CREATE RULE "pdns_rule_records_delete" +AS ON DELETE TO "records" DO + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 WHERE "id" = OLD."domain_id"; +``` + +## SQLite and SQLite3 +Supported without changes since OpenDBX 1.0.0 but requires to set [`opendbx-host`](#opendbs-host) +to the path of the SQLite file (including the trailing slash or backslash, +depending on your operating system) and opendbx-database to the name of the file. + +``` +opendbx-host-read = /path/to/file/ +opendbx-host-write = /path/to/file/ +opendbx-database = powerdns.sqlite +``` + +### SQLite Schema +``` +CREATE TABLE "domains" ( + "id" INTEGER NOT NULL PRIMARY KEY, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "master" VARCHAR(40) NOT NULL DEFAULT '', + "account" VARCHAR(40) NOT NULL DEFAULT '', + "last_check" INTEGER DEFAULT NULL, + "notified_serial" INTEGER DEFAULT NULL, + "auto_serial" INTEGER NOT NULL DEFAULT 0, + "status" CHAR(1) NOT NULL DEFAULT 'A', +CONSTRAINT "pdns_unq_domains_name" + UNIQUE ("name") +); + +CREATE INDEX "pdns_idx_domains_status_type" ON "domains" ("status","type"); + +CREATE TABLE "records" ( + "id" INTEGER NOT NULL PRIMARY KEY, + "domain_id" INTEGER NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "ttl" INTEGER DEFAULT NULL, + "prio" INTEGER DEFAULT NULL, + "content" VARCHAR(255) NOT NULL, +CONSTRAINT "pdns_fk_records_domainid" + FOREIGN KEY ("domain_id") + REFERENCES "domains" ("id") + ON UPDATE CASCADE + ON DELETE CASCADE +); + +CREATE INDEX "pdns_idx_records_name_type" ON "records" ("name","type"); +CREATE INDEX "pdns_idx_records_type" ON "records" ("type"); + +CREATE TABLE "supermasters" ( + "ip" VARCHAR(40) NOT NULL, + "nameserver" VARCHAR(255) NOT NULL, + "account" VARCHAR(40) NOT NULL DEFAULT '' +); + +CREATE INDEX "pdns_idx_smaster_ip_ns" ON "supermasters" ("ip","nameserver"); + +CREATE TRIGGER "pdns_trig_records_insert" +AFTER INSERT ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = NEW."domain_id"; +END; + +CREATE TRIGGER "pdns_trig_records_update" +AFTER UPDATE ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = NEW."domain_id"; +END; + +CREATE TRIGGER "pdns_trig_records_delete" +AFTER DELETE ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = OLD."domain_id"; +END; +``` + +### SQLite3 Schema +``` +CREATE TABLE "domains" ( + "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "master" VARCHAR(40) NOT NULL DEFAULT '', + "account" VARCHAR(40) NOT NULL DEFAULT '', + "last_check" INTEGER DEFAULT NULL, + "notified_serial" INTEGER DEFAULT NULL, + "auto_serial" INTEGER NOT NULL DEFAULT 0, + "status" CHAR(1) NOT NULL DEFAULT 'A', +CONSTRAINT "pdns_unq_domains_name" + UNIQUE ("name") +); + +CREATE INDEX "pdns_idx_domains_status_type" ON "domains" ("status","type"); + +CREATE TABLE "records" ( + "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + "domain_id" INTEGER NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "ttl" INTEGER DEFAULT NULL, + "prio" INTEGER DEFAULT NULL, + "content" VARCHAR(255) NOT NULL, +CONSTRAINT "pdns_fk_records_domainid" + FOREIGN KEY ("domain_id") + REFERENCES "domains" ("id") + ON UPDATE CASCADE + ON DELETE CASCADE +); + +CREATE INDEX "pdns_idx_records_name_type" ON "records" ("name","type"); +CREATE INDEX "pdns_idx_records_type" ON "records" ("type"); + +CREATE TABLE "supermasters" ( + "ip" VARCHAR(40) NOT NULL, + "nameserver" VARCHAR(255) NOT NULL, + "account" VARCHAR(40) NOT NULL DEFAULT '' +); + +CREATE INDEX "pdns_idx_smaster_ip_ns" ON "supermasters" ("ip","nameserver"); + +CREATE TRIGGER "pdns_trig_records_insert" +AFTER INSERT ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = NEW."domain_id"; +END; + +CREATE TRIGGER "pdns_trig_records_update" +AFTER UPDATE ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = NEW."domain_id"; +END; + +CREATE TRIGGER "pdns_trig_records_delete" +AFTER DELETE ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = OLD."domain_id"; +END; +``` + +## Firebird/Interbase +Requires [`opendbx-database`](#opendbx-database) set to the path of the database +file and doesn't support the default statement for starting transactions. Please +add the following lines to your pdns.conf: + +``` +opendbx-database = /var/lib/firebird2/data/powerdns.gdb +opendbx-sql-transactbegin = SET TRANSACTION +``` + +When creating the database please make sure that you call the `isql` tool with +the parameter `-page 4096`. Otherwise, you will get an error (key size exceeds +implementation restriction for index "pdns_unq_domains_name") when creating the tables. + +``` +CREATE TABLE "domains" ( + "id" INTEGER NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "master" VARCHAR(40) DEFAULT '' NOT NULL, + "account" VARCHAR(40) DEFAULT '' NOT NULL, + "last_check" INTEGER, + "notified_serial" INTEGER, + "auto_serial" INTEGER DEFAULT 0 NOT NULL, + "status" CHAR(1) DEFAULT 'A' NOT NULL, +CONSTRAINT "pdns_pk_domains_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_unq_domains_name" + UNIQUE ("name") +); + +CREATE GENERATOR "pdns_gen_domains_id"; + +SET TERM !!; +CREATE TRIGGER "pdns_trig_domains_id" FOR "domains" +ACTIVE BEFORE INSERT AS +BEGIN + IF (NEW."id" IS NULL) THEN + NEW."id" = GEN_ID("pdns_gen_domains_id",1); +END !! +SET TERM ;!! + +CREATE INDEX "pdns_idx_domains_status_type" ON "domains" ("status","type"); + +CREATE TABLE "records" ( + "id" INTEGER NOT NULL, + "domain_id" INTEGER NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "ttl" INTEGER DEFAULT NULL, + "prio" INTEGER DEFAULT NULL, + "content" VARCHAR(255) NOT NULL, +CONSTRAINT "pdns_pk_records_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_fk_records_domainid" + FOREIGN KEY ("domain_id") + REFERENCES "domains" ("id") + ON UPDATE CASCADE + ON DELETE CASCADE +); + +CREATE GENERATOR "pdns_gen_records_id"; + +SET TERM !!; +CREATE TRIGGER "pdns_trig_records_id" FOR "records" +ACTIVE BEFORE INSERT AS +BEGIN + IF (NEW."id" IS NULL) THEN + NEW."id" = GEN_ID("pdns_gen_records_id",1); +END !! +SET TERM ;!! + +CREATE INDEX "idx_records_name_type" ON "records" ("name","type"); +CREATE INDEX "idx_records_type" ON "records" ("type"); + +CREATE TABLE "supermasters" ( + "ip" VARCHAR(40) NOT NULL, + "nameserver" VARCHAR(255) NOT NULL, + "account" VARCHAR(40) DEFAULT '' NOT NULL +); + +CREATE INDEX "pdns_idx_smaster_ip_ns" ON "supermasters" ("ip","nameserver"); + +GRANT SELECT ON "supermasters" TO "powerdns"; +GRANT ALL ON "domains" TO "powerdns"; +GRANT ALL ON "records" TO "powerdns"; + +SET TERM !!; + +CREATE TRIGGER "pdns_trig_records_insert" FOR "records" +ACTIVE AFTER INSERT AS +BEGIN + UPDATE "domains" d SET d."auto_serial" = d."auto_serial" + 1 + WHERE d."id" = NEW."domain_id"; +END !! + +CREATE TRIGGER "pdns_trig_records_update" FOR "records" +ACTIVE AFTER UPDATE AS +BEGIN + UPDATE "domains" d SET d."auto_serial" = d."auto_serial" + 1 + WHERE d."id" = NEW."domain_id"; +END !! + +CREATE TRIGGER "pdns_trig_records_delete" FOR "records" +ACTIVE AFTER DELETE AS +BEGIN + UPDATE "domains" d SET d."auto_serial" = d."auto_serial" + 1 + WHERE d."id" = OLD."domain_id"; +END !! + +SET TERM ;!! +``` + +## Microsoft SQL Server +Supported using the FreeTDS library. It uses a different scheme for host +configuration (requires the name of the host section in the configuration file +of the dblib client library) and doesn't support the default statement for +starting transactions. Please add the following lines to your pdns.conf: + +``` +opendbx-host-read = MSSQL2k +opendbx-host-write = MSSQL2k +opendbx-sql-transactbegin = BEGIN TRANSACTION +``` + +``` +SET quoted_identifier ON; + + +CREATE TABLE "domains" ( + "id" INTEGER NOT NULL IDENTITY, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "master" VARCHAR(40) DEFAULT '' NOT NULL, + "account" VARCHAR(40) DEFAULT '' NOT NULL, + "last_check" INTEGER NULL, + "notified_serial" INTEGER NULL, + "auto_serial" INTEGER NOT NULL DEFAULT 0, + "status" CHAR(1) DEFAULT 'A' NOT NULL, +CONSTRAINT "pdns_pk_domains_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_unq_domains_name" + UNIQUE ("name") +); + +CREATE INDEX "pdns_idx_domains_status_type" ON "domains" ("status","type"); + +CREATE TABLE "records" ( + "id" INTEGER NOT NULL IDENTITY, + "domain_id" INTEGER NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "ttl" INTEGER NULL, + "prio" INTEGER NULL, + "content" VARCHAR(255) NOT NULL, + "change_date" INTEGER NULL, +CONSTRAINT "pdns_pk_records_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_fk_records_domainid" + FOREIGN KEY ("domain_id") + REFERENCES "domains" ("id") +); + +CREATE INDEX "pdns_idx_records_name_type" ON "records" ("name","type"); +CREATE INDEX "pdns_idx_records_type" ON "records" ("type"); + +CREATE TABLE "supermasters" ( + "ip" VARCHAR(40) NOT NULL, + "nameserver" VARCHAR(255) NOT NULL, + "account" VARCHAR(40) DEFAULT '' NOT NULL +); + +CREATE INDEX "pdns_idx_smip_smns" ON "supermasters" ("ip","nameserver"); + +GRANT SELECT ON "supermasters" TO "powerdns"; +GRANT ALL ON "domains" TO "powerdns"; +GRANT ALL ON "records" TO "powerdns"; + +CREATE TRIGGER "pdns_trig_records_insert" +ON "records" FOR INSERT AS + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = ANY ( + SELECT i."domain_id" FROM "inserted" i GROUP BY i."domain_id" + ); + +CREATE TRIGGER "pdns_trig_records_update" +ON "records" FOR UPDATE AS + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = ANY ( + SELECT i."domain_id" FROM "inserted" i GROUP BY i."domain_id" + ); + +CREATE TRIGGER "pdns_trig_records_delete" +ON "records" FOR DELETE AS + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = ANY ( + SELECT d."domain_id" FROM "deleted" d GROUP BY d."domain_id" + ); +``` + +## Sybase ASE +Supported using the native Sybase ctlib or the FreeTDS library. It uses a +different scheme for host configuration (requires the name of the host section +in the configuration file of the ctlib client library) and doesn't support the +default statement for starting transactions. Please add the following lines to +your pdns.conf: + +``` +opendbx-host-read = SYBASE +opendbx-host-write = SYBASE +opendbx-sql-transactbegin = BEGIN TRANSACTION +``` + +``` +SET quoted_identifier ON; + +CREATE TABLE "domains" ( + "id" INTEGER NOT NULL IDENTITY, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "master" VARCHAR(40) DEFAULT '' NOT NULL, + "account" VARCHAR(40) DEFAULT '' NOT NULL, + "last_check" INTEGER NULL, + "notified_serial" INTEGER NULL, + "auto_serial" INTEGER NOT NULL DEFAULT 0, + "status" CHAR(1) DEFAULT 'A' NOT NULL, +CONSTRAINT "pdns_pk_domains_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_unq_domains_name" + UNIQUE ("name") +); + +CREATE INDEX "pdns_idx_domains_status_type" ON "domains" ("status","type"); + +CREATE TABLE "records" ( + "id" INTEGER NOT NULL IDENTITY, + "domain_id" INTEGER NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "ttl" INTEGER NULL, + "prio" INTEGER NULL, + "content" VARCHAR(255) NOT NULL, + "change_date" INTEGER NULL, +CONSTRAINT "pdns_pk_records_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_fk_records_domainid" + FOREIGN KEY ("domain_id") + REFERENCES "domains" ("id") +); + +CREATE INDEX "pdns_idx_records_name_type" ON "records" ("name","type"); +CREATE INDEX "pdns_idx_records_type" ON "records" ("type"); + +CREATE TABLE "supermasters" ( + "ip" VARCHAR(40) NOT NULL, + "nameserver" VARCHAR(255) NOT NULL, + "account" VARCHAR(40) DEFAULT '' NOT NULL +); + +CREATE INDEX "pdns_idx_smip_smns" ON "supermasters" ("ip","nameserver"); + +GRANT SELECT ON "supermasters" TO "powerdns"; +GRANT ALL ON "domains" TO "powerdns"; +GRANT ALL ON "records" TO "powerdns"; + +CREATE TRIGGER "pdns_trig_records_insert" +ON "records" FOR INSERT AS + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = ANY ( + SELECT i."domain_id" FROM "inserted" i GROUP BY i."domain_id" + ); + +CREATE TRIGGER "pdns_trig_records_update" +ON "records" FOR UPDATE AS + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = ANY ( + SELECT i."domain_id" FROM "inserted" i GROUP BY i."domain_id" + ); + +CREATE TRIGGER "pdns_trig_records_delete" +ON "records" FOR DELETE AS + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = ANY ( + SELECT d."domain_id" FROM "deleted" d GROUP BY d."domain_id" + ); +``` + +## Oracle +Uses a different syntax for transactions and requires the following additional +line in your pdns.conf: + +``` +opendbx-sql-transactbegin = SET TRANSACTION NAME 'AXFR' +``` + +``` +CREATE TABLE "domains" ( + "id" INTEGER NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "master" VARCHAR(40) DEFAULT '', + "account" VARCHAR(40) DEFAULT '', + "last_check" INTEGER, + "notified_serial" INTEGER, + "auto_serial" INTEGER DEFAULT 0, + "status" CHAR(1) DEFAULT 'A', +CONSTRAINT "pdns_pk_domains_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_unq_domains_name" + UNIQUE ("name") +); + +CREATE SEQUENCE "pdns_seq_domains_id" START WITH 1 INCREMENT BY 1; + +CREATE TRIGGER "pdns_trig_domains_id" +BEFORE INSERT ON "domains" +FOR EACH ROW +BEGIN + SELECT "pdns_seq_domains_id".nextval INTO :NEW."id" FROM dual; +END; + +CREATE INDEX "pdns_idx_domains_status_type" ON "domains" ("status","type"); + +CREATE TABLE "records" ( + "id" INTEGER NOT NULL, + "domain_id" INTEGER NOT NULL, + "name" VARCHAR(255) NOT NULL, + "type" VARCHAR(6) NOT NULL, + "ttl" INTEGER NULL, + "prio" INTEGER NULL, + "content" VARCHAR(255) NOT NULL, + "change_date" INTEGER NULL, +CONSTRAINT "pdns_pk_records_id" + PRIMARY KEY ("id"), +CONSTRAINT "pdns_fk_records_domainid" + FOREIGN KEY ("domain_id") + REFERENCES "domains" ("id") + ON DELETE CASCADE +); + +CREATE SEQUENCE "pdns_seq_records_id" START WITH 1 INCREMENT BY 1; + +CREATE TRIGGER "pdns_trig_records_id" +BEFORE INSERT ON "records" +FOR EACH ROW +BEGIN + SELECT "pdns_seq_records_id".nextval INTO :NEW."id" FROM dual; +END; + +CREATE INDEX "pdns_idx_records_name_type" ON "records" ("name","type"); +CREATE INDEX "pdns_idx_records_type" ON "records" ("type"); + +CREATE TABLE "supermasters" ( + "ip" VARCHAR(40) NOT NULL, + "nameserver" VARCHAR(255) NOT NULL, + "account" VARCHAR(40) NOT NULL +); + +CREATE INDEX "pdns_idx_smaster_ip_ns" ON "supermasters" ("ip","nameserver"); + +GRANT SELECT ON "supermasters" TO "powerdns"; +GRANT ALL ON "domains" TO "powerdns"; +GRANT ALL ON "records" TO "powerdns"; + +CREATE TRIGGER "pdns_trig_records_insert" +AFTER INSERT ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = :NEW."domain_id"; +END; + +CREATE TRIGGER "pdns_trig_records_update" +AFTER UPDATE ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = :NEW."domain_id"; +END; + +CREATE TRIGGER "pdns_trig_records_delete" +AFTER DELETE ON "records" +FOR EACH ROW BEGIN + UPDATE "domains" SET "auto_serial" = "auto_serial" + 1 + WHERE "id" = :OLD."domain_id"; +END; +``` -- 2.40.0