From bb16a21c3618ec29a51bfa1f08116c4aa278614a Mon Sep 17 00:00:00 2001 From: Kees Monshouwer Date: Fri, 11 Apr 2014 20:48:36 +0200 Subject: [PATCH] gsqlite schema update improvements --- modules/gsqlite3backend/Makefile.am | 2 +- .../dnssec-3.x_to_3.4_schema.sqlite3.sql | 76 +++++++++++++++++++ .../nodnssec-3.x_to_3.4_schema.sqlite3.sql | 22 ++++++ pdns/docs/pdns.xml | 61 +-------------- 4 files changed, 100 insertions(+), 61 deletions(-) create mode 100644 modules/gsqlite3backend/dnssec-3.x_to_3.4_schema.sqlite3.sql diff --git a/modules/gsqlite3backend/Makefile.am b/modules/gsqlite3backend/Makefile.am index 49acbc3a5e..02ea5e7f7b 100644 --- a/modules/gsqlite3backend/Makefile.am +++ b/modules/gsqlite3backend/Makefile.am @@ -2,7 +2,7 @@ AM_CPPFLAGS=@THREADFLAGS@ $(BOOST_CPPFLAGS) lib_LTLIBRARIES = libgsqlite3backend.la EXTRA_DIST=OBJECTFILES OBJECTLIBS \ - schema.sqlite3.sql nodnssec-3.x_to_3.4_schema.sqlite3.sql + schema.sqlite3.sql nodnssec-3.x_to_3.4_schema.sqlite3.sql dnssec-3.x_to_3.4_schema.sqlite3.sql libgsqlite3backend_la_SOURCES=gsqlite3backend.cc gsqlite3backend.hh diff --git a/modules/gsqlite3backend/dnssec-3.x_to_3.4_schema.sqlite3.sql b/modules/gsqlite3backend/dnssec-3.x_to_3.4_schema.sqlite3.sql new file mode 100644 index 0000000000..a093c62ded --- /dev/null +++ b/modules/gsqlite3backend/dnssec-3.x_to_3.4_schema.sqlite3.sql @@ -0,0 +1,76 @@ +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); + + +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 1 + ); + + 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; + + +BEGIN TRANSACTION; + CREATE TEMPORARY TABLE supermasters_backup ( + ip VARCHAR(64) NOT NULL, + nameserver VARCHAR(255) NOT NULL COLLATE NOCASE, + account VARCHAR(40) DEFAULT NULL + ); + + INSERT INTO supermasters_backup SELECT ip,nameserver,account FROM supermasters; + DROP TABLE supermasters; + + CREATE TABLE supermasters ( + ip VARCHAR(64) NOT NULL, + nameserver VARCHAR(255) NOT NULL COLLATE NOCASE, + account VARCHAR(40) DEFAULT NULL + ); + CREATE UNIQUE INDEX ip_nameserver_pk ON supermasters(ip, nameserver); + + INSERT INTO supermasters SELECT ip,nameserver,account FROM supermasters_backup; + DROP TABLE supermasters_backup; +COMMIT; 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 87abeb48a5..919d27a4ec 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 @@ -49,3 +49,25 @@ CREATE TABLE comments ( 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); + + +BEGIN TRANSACTION; + CREATE TEMPORARY TABLE supermasters_backup ( + ip VARCHAR(64) NOT NULL, + nameserver VARCHAR(255) NOT NULL COLLATE NOCASE, + account VARCHAR(40) DEFAULT NULL + ); + + INSERT INTO supermasters_backup SELECT ip, nameserver, account FROM supermasters; + DROP TABLE supermasters; + + CREATE TABLE supermasters ( + ip VARCHAR(64) NOT NULL, + nameserver VARCHAR(255) NOT NULL COLLATE NOCASE, + account VARCHAR(40) DEFAULT NULL + ); + CREATE UNIQUE INDEX ip_nameserver_pk ON supermasters(ip, nameserver); + + INSERT INTO supermasters SELECT ip, nameserver, account FROM supermasters_backup; + DROP TABLE supermasters_backup; +COMMIT; diff --git a/pdns/docs/pdns.xml b/pdns/docs/pdns.xml index 268614e483..57616a4915 100644 --- a/pdns/docs/pdns.xml +++ b/pdns/docs/pdns.xml @@ -12465,66 +12465,7 @@ create index recordorder on records (domain_id, ordername text_pattern_ops); For gsqlite3 backend with nodnssec schema: For gsqlite3 backend with dnssec schema: - -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; -- 2.47.2