[Pkg-pdns-maintainers] Bug#685808: pdns-backend-pgsql: schema out-of-date, please provide no-dnssec.schema

[Robert Specht]

> So you're suggesting to add
> CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text)))
> to the CREATE TABLE statement in debian/sql/pgsql/pgsql inside the
> source package, right?
Yes, to prevent useless uppercase name(s) and trouble-shooting...
But ensure lowercase names before you add CONSTRAINT c_lowercase_name to a non empty table.
e.g. UPDATE records SET name=lower(name);

> Did we miss changes that Upstream did between 2.9 and 3.1?
You're right. The package schema is out-of-date, not equal to the original pdns schema ... missing some pdns-commits (upstream).
Ocularly differences:

table.row          | original pdns schema        | debian package schema
-----------------------------------------------------------------------------
domains.master	   | VARCHAR(128) DEFAULT NULL   | VARCHAR(20) DEFAULT NULL
records.content    | VARCHAR(65535) DEFAULT NULL | VARCHAR(255) DEFAULT NULL
tsigkeys.algorithm | VARCHAR(50)                 | VARCHAR(255)
Missing records.CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text)))

I suggest to provide two up-to-date schemas from pdns_3.1.orig.tar.gz, dnssec.schema.pgsql.sql AND no-dnssec.schema.pgsql.sql.

schemas from pdns_3.1.orig.tar.gz:

$ curl -s http://ftp.de.debian.org/debian/pool/main/p/pdns/pdns_3.1.orig.tar.gz | tar xOzf - pdns-3.1/pdns/no-dnssec.schema.pgsql.sql
create table domains (
 id		 SERIAL PRIMARY KEY,
 name		 VARCHAR(255) NOT NULL,
 master		 VARCHAR(128) DEFAULT NULL,
 last_check	 INT DEFAULT NULL,
 type		 VARCHAR(6) NOT NULL,
 notified_serial INT DEFAULT NULL, 
 account         VARCHAR(40) DEFAULT NULL
);
CREATE UNIQUE INDEX name_index ON domains(name);
  
CREATE TABLE records (
        id              SERIAL PRIMARY KEY,
        domain_id       INT DEFAULT NULL,
        name            VARCHAR(255) DEFAULT NULL,
        type            VARCHAR(10) DEFAULT NULL,
        content         VARCHAR(65535) DEFAULT NULL,
        ttl             INT DEFAULT NULL,
        prio            INT DEFAULT NULL,
        change_date     INT DEFAULT NULL, 
        CONSTRAINT domain_exists 
        FOREIGN KEY(domain_id) REFERENCES domains(id)
        ON DELETE CASCADE,
        CONSTRAINT c_lowercase_name CHECK (((name)::text = lower((name)::text)))
);

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

-- GRANT SELECT ON supermasters TO pdns;
-- GRANT ALL ON domains TO pdns;
-- GRANT ALL ON domains_id_seq TO pdns;
-- GRANT ALL ON records TO pdns;
-- GRANT ALL ON records_id_seq TO pdns;

	
$ curl -s http://ftp.de.debian.org/debian/pool/main/p/pdns/pdns_3.1.orig.tar.gz | tar xOzf - pdns-3.1/pdns/dnssec.schema.pgsql.sql
alter table records add ordername	VARCHAR(255);
alter table records add auth bool;
create index orderindex on records(ordername);

create table domainmetadata (
 id		SERIAL PRIMARY KEY,
 domain_id	INT REFERENCES domains(id) ON DELETE CASCADE,
 kind		VARCHAR(16),
 content	TEXT
);

create index domainidmetaindex on domainmetadata(domain_id);               


create table cryptokeys (
 id		SERIAL PRIMARY KEY,
 domain_id	INT REFERENCES domains(id) ON DELETE CASCADE,
 flags		INT NOT NULL,
 active		BOOL,
 content	TEXT
);		 
create index domainidindex on cryptokeys(domain_id);


-- GRANT ALL ON domainmetadata TO pdns;
-- GRANT ALL ON domainmetadata_id_seq TO pdns;
-- GRANT ALL ON cryptokeys TO pdns;
-- GRANT ALL ON cryptokeys_id_seq TO pdns;

create table tsigkeys (
 id		SERIAL PRIMARY KEY,
 name		VARCHAR(255),
 algorithm	VARCHAR(50), 
 secret		VARCHAR(255)
);

create unique index namealgoindex on tsigkeys(name, algorithm);

-- GRANT ALL ON tsigkeys TO pdns;
-- GRANT ALL ON tsigkeys_id_seq TO pdns;
alter table records alter column type type VARCHAR(10);


> Is Debian's PostgreSQL schema correct for dnssec?
I use no-dnssec.schema.pgsql.sql (no DNSSEC), dnssec.schema.pgsql.sql should work?