1 files changed, 46 insertions, 34 deletions
diff --git a/dns/powerdns-devel/files/pdns_mysql.sql b/dns/powerdns-devel/files/pdns_mysql.sql
index 278c5d56c61c..0db1b8665734 100644
--- a/dns/powerdns-devel/files/pdns_mysql.sql
+++ b/dns/powerdns-devel/files/pdns_mysql.sql
@@ -1,38 +1,50 @@
-create table domains (
- id              INT auto_increment,
- name            VARCHAR(255) NOT NULL,
- master          VARCHAR(20) DEFAULT NULL,
- last_check      INT DEFAULT NULL,
- type            VARCHAR(6) NOT NULL,
- notified_serial INT DEFAULT NULL,
- account         VARCHAR(40) DEFAULT NULL,
- primary key (id)
-)type=InnoDB;
-
-CREATE UNIQUE INDEX name_index ON domains(name);
+CREATE TABLE domains (
+	id INT auto_increment,
+	name VARCHAR(255) NOT NULL,
+	type VARCHAR(6) NOT NULL,
+	master VARCHAR(40) DEFAULT NULL,
+	account VARCHAR(40) DEFAULT NULL,
+	notified_serial INT DEFAULT NULL,
+	last_check INT DEFAULT NULL,
+CONSTRAINT pk_id
+	PRIMARY KEY (id),
+CONSTRAINT unq_name
+	UNIQUE (name)
+) type=InnoDB;
+
 
 CREATE TABLE records (
-  id              INT auto_increment,
-  domain_id       INT DEFAULT NULL,
-  name            VARCHAR(255) DEFAULT NULL,
-  type            VARCHAR(6) DEFAULT NULL,
-  content         VARCHAR(255) DEFAULT NULL,
-  ttl             INT DEFAULT NULL,
-  prio            INT DEFAULT NULL,
-  change_date     INT DEFAULT NULL,
-  primary key(id)
-)type=InnoDB;
-
-CREATE INDEX rec_name_index ON records(name);
-CREATE INDEX nametype_index ON records(name,type);
-CREATE INDEX domain_id ON records(domain_id);
-
-create table supermasters (
-  ip VARCHAR(25) NOT NULL,
-  nameserver VARCHAR(255) NOT NULL,
-  account VARCHAR(40) DEFAULT NULL
+	id INT auto_increment,
+	domain_id INT DEFAULT NULL,
+	name VARCHAR(255) DEFAULT NULL,
+	type VARCHAR(6) DEFAULT NULL,
+	ttl INT DEFAULT NULL,
+	prio INT DEFAULT NULL,
+	content VARCHAR(255) DEFAULT NULL,
+	change_date INT DEFAULT NULL,
+CONSTRAINT pk_id
+	PRIMARY KEY (id),
+CONSTRAINT fk_domainid
+	FOREIGN KEY (domain_id)
+	REFERENCES domains(id)
+	ON UPDATE CASCADE
+	ON DELETE CASCADE
+) type=InnoDB;
+
+CREATE INDEX idx_rdomainid ON records(domain_id);
+CREATE INDEX idx_rname ON records(name);
+CREATE INDEX idx_rname_rtype ON records(name,type);
+
+
+CREATE TABLE supermasters (
+	ip VARCHAR(40) NOT NULL,
+	nameserver VARCHAR(255) NOT NULL,
+	account VARCHAR(40) DEFAULT NULL
 );
 
-GRANT SELECT ON supermasters TO pdns;
-GRANT ALL ON domains TO pdns;
-GRANT ALL ON records TO pdns;
+CREATE INDEX 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;