From f8483675c3d2f4ab13d0d41bdc6034f1accd4d5e Mon Sep 17 00:00:00 2001 From: Kees Monshouwer Date: Mon, 10 Mar 2014 20:04:57 +0100 Subject: [PATCH] schema change upgrade notes --- .../nodnssec-3.x_to_3.4_schema.mysql.sql | 4 - modules/gmysqlbackend/schema.mysql.sql | 6 - .../nodnssec-3.x_to_3.4_schema.pgsql.sql | 4 - modules/gpgsqlbackend/schema.pgsql.sql | 5 - .../nodnssec-3.x_to_3.4_schema.sqlite3.sql | 4 - modules/gsqlite3backend/schema.sqlite3.sql | 6 - pdns/docs/pdns.xml | 134 ++++++++++++++++-- 7 files changed, 123 insertions(+), 40 deletions(-) diff --git a/modules/gmysqlbackend/nodnssec-3.x_to_3.4_schema.mysql.sql b/modules/gmysqlbackend/nodnssec-3.x_to_3.4_schema.mysql.sql index 2e60c9b238..a34aca990d 100644 --- a/modules/gmysqlbackend/nodnssec-3.x_to_3.4_schema.mysql.sql +++ b/modules/gmysqlbackend/nodnssec-3.x_to_3.4_schema.mysql.sql @@ -6,7 +6,6 @@ ALTER TABLE records CHANGE COLUMN type TYPE VARCHAR(10); CREATE INDEX recordorder ON records (domain_id, ordername); - CREATE TABLE domainmetadata ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, @@ -18,7 +17,6 @@ CREATE TABLE domainmetadata ( CREATE INDEX domainmetaidindex ON domainmetadata(domain_id); - CREATE TABLE cryptokeys ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, @@ -31,7 +29,6 @@ CREATE TABLE cryptokeys ( CREATE INDEX domainidindex ON cryptokeys(domain_id); - CREATE TABLE tsigkeys ( id INT AUTO_INCREMENT, name VARCHAR(255), @@ -43,7 +40,6 @@ CREATE TABLE tsigkeys ( CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm); - CREATE TABLE comments ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, diff --git a/modules/gmysqlbackend/schema.mysql.sql b/modules/gmysqlbackend/schema.mysql.sql index eb692b3d0a..27afa2b0f9 100644 --- a/modules/gmysqlbackend/schema.mysql.sql +++ b/modules/gmysqlbackend/schema.mysql.sql @@ -12,7 +12,6 @@ create table domains ( CREATE UNIQUE INDEX name_index ON domains(name); - CREATE TABLE records ( id INT AUTO_INCREMENT, domain_id INT DEFAULT NULL, @@ -33,7 +32,6 @@ CREATE INDEX domain_id ON records(domain_id); CREATE INDEX recordorder ON records (domain_id, ordername); - CREATE TABLE supermasters ( ip VARCHAR(64) NOT NULL, nameserver VARCHAR(255) NOT NULL, @@ -42,7 +40,6 @@ CREATE TABLE supermasters ( ) Engine=InnoDB; - CREATE TABLE comments ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, @@ -59,7 +56,6 @@ CREATE INDEX comments_name_type_idx ON comments (name, type); CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); - CREATE TABLE domainmetadata ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, @@ -71,7 +67,6 @@ CREATE TABLE domainmetadata ( CREATE INDEX domainmetaidindex ON domainmetadata(domain_id); - CREATE TABLE cryptokeys ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, @@ -84,7 +79,6 @@ CREATE TABLE cryptokeys ( CREATE INDEX domainidindex ON cryptokeys(domain_id); - CREATE TABLE tsigkeys ( id INT AUTO_INCREMENT, name VARCHAR(255), diff --git a/modules/gpgsqlbackend/nodnssec-3.x_to_3.4_schema.pgsql.sql b/modules/gpgsqlbackend/nodnssec-3.x_to_3.4_schema.pgsql.sql index 1467150c07..041d55cb28 100644 --- a/modules/gpgsqlbackend/nodnssec-3.x_to_3.4_schema.pgsql.sql +++ b/modules/gpgsqlbackend/nodnssec-3.x_to_3.4_schema.pgsql.sql @@ -6,7 +6,6 @@ ALTER table records ALTER COLUMN type TYPE VARCHAR(10); CREATE INDEX recordorder ON records (domain_id, ordername text_pattern_ops); - CREATE TABLE domainmetadata ( id SERIAL PRIMARY KEY, domain_id INT REFERENCES domains(id) ON DELETE CASCADE, @@ -17,7 +16,6 @@ CREATE TABLE domainmetadata ( CREATE INDEX domainidmetaindex ON domainmetadata(domain_id); - CREATE TABLE cryptokeys ( id SERIAL PRIMARY KEY, domain_id INT REFERENCES domains(id) ON DELETE CASCADE, @@ -29,7 +27,6 @@ CREATE TABLE cryptokeys ( CREATE INDEX domainidindex ON cryptokeys(domain_id); - CREATE TABLE tsigkeys ( id SERIAL PRIMARY KEY, name VARCHAR(255), @@ -41,7 +38,6 @@ CREATE TABLE tsigkeys ( CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm); - CREATE TABLE comments ( id SERIAL PRIMARY KEY, domain_id INT NOT NULL, diff --git a/modules/gpgsqlbackend/schema.pgsql.sql b/modules/gpgsqlbackend/schema.pgsql.sql index eecc63e63e..3423e8df0d 100644 --- a/modules/gpgsqlbackend/schema.pgsql.sql +++ b/modules/gpgsqlbackend/schema.pgsql.sql @@ -36,7 +36,6 @@ CREATE INDEX domain_id ON records(domain_id); CREATE INDEX recordorder ON records (domain_id, ordername text_pattern_ops); - CREATE TABLE supermasters ( ip INET NOT NULL, nameserver VARCHAR(255) NOT NULL, @@ -45,7 +44,6 @@ CREATE TABLE supermasters ( ); - CREATE TABLE comments ( id SERIAL PRIMARY KEY, domain_id INT NOT NULL, @@ -65,7 +63,6 @@ CREATE INDEX comments_name_type_idx ON comments (name, type); CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); - CREATE TABLE domainmetadata ( id SERIAL PRIMARY KEY, domain_id INT REFERENCES domains(id) ON DELETE CASCADE, @@ -76,7 +73,6 @@ CREATE TABLE domainmetadata ( CREATE INDEX domainidmetaindex ON domainmetadata(domain_id); - CREATE TABLE cryptokeys ( id SERIAL PRIMARY KEY, domain_id INT REFERENCES domains(id) ON DELETE CASCADE, @@ -88,7 +84,6 @@ CREATE TABLE cryptokeys ( CREATE INDEX domainidindex ON cryptokeys(domain_id); - CREATE TABLE tsigkeys ( id SERIAL PRIMARY KEY, name VARCHAR(255), diff --git a/modules/gsqlite3backend/nodnssec-3.x_to_3.4_schema.sqlite3.sql b/modules/gsqlite3backend/nodnssec-3.x_to_3.4_schema.sqlite3.sql index 3c09dfe8b6..87abeb48a5 100644 --- a/modules/gsqlite3backend/nodnssec-3.x_to_3.4_schema.sqlite3.sql +++ b/modules/gsqlite3backend/nodnssec-3.x_to_3.4_schema.sqlite3.sql @@ -5,7 +5,6 @@ ALTER TABLE records ADD auth BOOL DEFAULT 1; CREATE INDEX orderindex ON records(ordername); - CREATE TABLE domainmetadata ( id INTEGER PRIMARY KEY, domain_id INT NOT NULL, @@ -16,7 +15,6 @@ CREATE TABLE domainmetadata ( CREATE INDEX domainmetaidindex on domainmetadata(domain_id); - CREATE TABLE cryptokeys ( id INTEGER PRIMARY KEY, domain_id INT NOT NULL, @@ -28,7 +26,6 @@ CREATE TABLE cryptokeys ( CREATE INDEX domainidindex ON cryptokeys(domain_id); - CREATE TABLE tsigkeys ( id INTEGER PRIMARY KEY, name VARCHAR(255) COLLATE NOCASE, @@ -39,7 +36,6 @@ CREATE TABLE tsigkeys ( CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm); - CREATE TABLE comments ( id INTEGER PRIMARY KEY, domain_id INTEGER NOT NULL, diff --git a/modules/gsqlite3backend/schema.sqlite3.sql b/modules/gsqlite3backend/schema.sqlite3.sql index 8164199bc8..fbc0850faf 100644 --- a/modules/gsqlite3backend/schema.sqlite3.sql +++ b/modules/gsqlite3backend/schema.sqlite3.sql @@ -11,7 +11,6 @@ CREATE TABLE domains ( CREATE UNIQUE INDEX name_index ON domains(name); - CREATE TABLE records ( id INTEGER PRIMARY KEY, domain_id INTEGER DEFAULT NULL, @@ -32,7 +31,6 @@ CREATE INDEX domain_id ON records(domain_id); CREATE INDEX orderindex ON records(ordername); - CREATE TABLE supermasters ( ip VARCHAR(64) NOT NULL, nameserver VARCHAR(255) NOT NULL COLLATE NOCASE, @@ -42,7 +40,6 @@ CREATE TABLE supermasters ( CREATE UNIQUE INDEX ip_nameserver_pk ON supermasters(ip, nameserver); - CREATE TABLE comments ( id INTEGER PRIMARY KEY, domain_id INTEGER NOT NULL, @@ -58,7 +55,6 @@ CREATE INDEX comments_nametype_index ON comments (name, type); CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); - CREATE TABLE domainmetadata ( id INTEGER PRIMARY KEY, domain_id INT NOT NULL, @@ -69,7 +65,6 @@ CREATE TABLE domainmetadata ( CREATE INDEX domainmetaidindex ON domainmetadata(domain_id); - CREATE TABLE cryptokeys ( id INTEGER PRIMARY KEY, domain_id INT NOT NULL, @@ -81,7 +76,6 @@ CREATE TABLE cryptokeys ( CREATE INDEX domainidindex ON cryptokeys(domain_id); - CREATE TABLE tsigkeys ( id INTEGER PRIMARY KEY, name VARCHAR(255) COLLATE NOCASE, diff --git a/pdns/docs/pdns.xml b/pdns/docs/pdns.xml index 1a65d4c9ad..67d8d478a4 100644 --- a/pdns/docs/pdns.xml +++ b/pdns/docs/pdns.xml @@ -12444,27 +12444,139 @@ create index recordorder on records (domain_id, ordername text_pattern_ops); - A GSQL Backend schema change is necessary for new features. - For MySQL: + + + The default database schema has changed. The database update below is mandatory. + + + If custom queries are in use, they probably need an update. + + + + + For gmysql backend with nodnssec schema: + + For gmysql backend with dnssec schema: ALTER TABLE records ADD disabled BOOLEAN DEFAULT 0; - - For PostgreSQL: +ALTER TABLE records MODIFY auth BOOLEAN DEFAULT 1; + + +CREATE TABLE comments ( + id INT AUTO_INCREMENT, + domain_id INT NOT NULL, + name VARCHAR(255) NOT NULL, + type VARCHAR(10) NOT NULL, + modified_at INT NOT NULL, + account VARCHAR(40) NOT NULL, + comment VARCHAR(64000) NOT NULL, + PRIMARY KEY(id) +) Engine=InnoDB; + +CREATE INDEX comments_domain_id_idx ON comments (domain_id); +CREATE INDEX comments_name_type_idx ON comments (name, type); +CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); + + For gpgsql backend with nodnssec schema: + + For gpgsql backend with dnssec schema: ALTER TABLE records ADD disabled BOOLEAN DEFAULT 'f'; - - For SQLite 3: +ALTER TABLE records ALTER COLUMN auth SET DEFAULT 't'; + + +CREATE TABLE comments ( + id SERIAL PRIMARY KEY, + domain_id INT NOT NULL, + name VARCHAR(255) NOT NULL, + type VARCHAR(10) NOT NULL, + modified_at INT NOT NULL, + account VARCHAR(40) DEFAULT NULL, + comment VARCHAR(65535) NOT 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 comments_domain_id_idx ON comments (domain_id); +CREATE INDEX comments_name_type_idx ON comments (name, type); +CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); + + For gsqlite3 backend with nodnssec schema: + + For gsqlite3 backend with dnssec schema: -ALTER TABLE records ADD disabled BOOLEAN DEFAULT 0; - - For Oracle: +BEGIN TRANSACTION; + +CREATE TEMPORARY TABLE records_backup( + id INTEGER PRIMARY KEY, + domain_id INTEGER DEFAULT NULL, + name VARCHAR(255) DEFAULT NULL, + type VARCHAR(10) DEFAULT NULL, + content VARCHAR(65535) DEFAULT NULL, + ttl INTEGER DEFAULT NULL, + prio INTEGER DEFAULT NULL, + change_date INTEGER DEFAULT NULL, + ordername VARCHAR(255), + auth BOOL DEFAULT 0 +); + +INSERT INTO records_backup SELECT id,domain_id,name,type,content,ttl,prio,change_date,ordername,auth FROM records; + +DROP TABLE records; + +CREATE TABLE records ( + id INTEGER PRIMARY KEY, + domain_id INTEGER DEFAULT NULL, + name VARCHAR(255) DEFAULT NULL, + type VARCHAR(10) DEFAULT NULL, + content VARCHAR(65535) DEFAULT NULL, + ttl INTEGER DEFAULT NULL, + prio INTEGER DEFAULT NULL, + change_date INTEGER DEFAULT NULL, + disabled BOOLEAN DEFAULT 0, + ordername VARCHAR(255), + auth BOOL DEFAULT 1 +); + +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 INDEX orderindex ON records(ordername); + +INSERT INTO records SELECT id,domain_id,name,type,content,ttl,prio,change_date,0,ordername,auth FROM records_backup; + +DROP TABLE records_backup; + +COMMIT; + + +CREATE TABLE comments ( + id INTEGER PRIMARY KEY, + domain_id INTEGER NOT NULL, + name VARCHAR(255) NOT NULL, + type VARCHAR(10) NOT NULL, + modified_at INT NOT NULL, + account VARCHAR(40) DEFAULT NULL, + comment VARCHAR(65535) NOT NULL +); + +CREATE INDEX comments_domain_id_index ON comments (domain_id); +CREATE INDEX comments_nametype_index ON comments (name, type); +CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); + + For goracle backend: ALTER TABLE records ADD disabled INT DEFAULT 0; - +ALTER TABLE records MODIFY auth INT DEFAULT 1; + +UPDATE records SET auth=1 WHERE auth IS NULL; + - + Serving authoritative DNSSEC data -- 2.47.2