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"
	);
