PowerDNS annotated SQLite queries

00-README.md

Annotated list of SQLite queries for PowerDNS 4.1+

To retrieve all queries known to PowerDNS, run something like

# pdns_server --no-config --launch=gsqlite3 --config | fgrep '# gsqlite3-'

Then go visit the Generic SQL Backends documentation and find your query documentation (by removing the gsqlite3- prefix from the pdns_server output from above).

Or simply use the SQL lookup file below.

For reference, the default schema can be found in the pdns repository.

Note: These are the prepared statements for the SQLite3 backend. The queries for PostgreSQL, MySQL/MariaDB, etc. might look similar, by may contain DB-specific optimizations and/or another syntax for prepared statements.

01-regular-queries.sql

--
-- Regular queries
-- https://doc.powerdns.com/authoritative/backends/generic-sql.html#regular-queries
--

-- gsqlite3-basic-query
-- This is the most used query, needed for doing 1:1 lookups of qtype/name values.
select content, ttl, prio, type, domain_id, disabled, name, auth
  from records
  where disabled = 0
    and type = :qtype
    and name = :qname;

-- gsqlite3-id-query
-- Used for doing lookups within a domain.
select content, ttl, prio, type, domain_id, disabled, name, auth
  from records
  where disabled = 0
    and type = :qtype
    and name = :qname
    and domain_id = :domain_id;

-- gsqlite3-any-query
-- For doing ANY queries. Also used internally.
select content, ttl, prio, type, domain_id, disabled, name, auth
from records
where disabled = 0
  and name = :qname;

-- gsqlite3-any-id-query
-- For doing ANY queries within a domain. Also used internally.
select content, ttl, prio, type, domain_id, disabled, name, auth
  from records
  where disabled = 0
    and name = :qname
    and domain_id = :domain_id;

-- gsqlite3-list-query
-- For doing AXFRs, lists all records in the zone. Also used internally.
select content, ttl, prio, type, domain_id, disabled, name, auth
  from records
  where (disabled = 0 or :include_disabled)
    and domain_id = :domain_id
  order by name, type;

-- gsqlite3-list-subzone-query
-- For doing RFC 2136 DNS Updates, lists all records below a zone.
select content, ttl, prio, type, domain_id, disabled, name, auth
  from records
  where disabled = 0
    and (name = :zone or name like :wildzone)
    and domain_id = :domain_id;

-- gsqlite3-search-records-query
-- To search for records on name and content.
select content, ttl, prio, type, domain_id, disabled, name, auth
  from records
  where name like :value
    or content like :value2
  limit :limit;

02-dnssec-queries.sql

--
-- DNSSEC queries
-- https://doc.powerdns.com/authoritative/backends/generic-sql.html#dnssec-queries
--

-- gsqlite3-insert-empty-non-terminal-order-query
-- Insert empty non-terminal in zone.
--   (documented as insert-empty-non-terminal-order--query)
insert into records (type, domain_id, disabled, name, ordername, auth, ttl, prio, change_date, content)
  values (null, :domain_id, 0, :qname, :ordername, :auth, null, null, null, null);

-- gsqlite3-delete-empty-non-terminal-query
-- Delete an empty non-terminal in a zone.
delete from records
  where domain_id = :domain_id
    and name = :qname
    and type is null;

-- gsqlite3-remove-empty-non-terminals-from-zone-query
-- remove all empty non-terminals from zone.
delete from records
  where domain_id = :domain_id
    and type is null;

-- gsqlite3-get-order-first-query
-- DNSSEC Ordering Query, first.
select ordername
  from records
  where disabled = 0
    and domain_id = :domain_id
    and ordername is not null
  order by 1 asc
  limit 1;

-- gsqlite3-get-order-before-query
-- DNSSEC Ordering Query, before.
select ordername, name
  from records
  where disabled = 0
    and ordername <= :ordername
    and domain_id = :domain_id
    and ordername is not null
  order by 1 desc
  limit 1;

-- gsqlite3-get-order-after-query
-- DNSSEC Ordering Query, after.
select min(ordername)
  from records
  where disabled = 0
    and ordername > :ordername
    and domain_id = :domain_id
    and ordername is not null;

-- gsqlite3-get-order-last-query
-- DNSSEC Ordering Query, last.
select ordername, name
  from records
  where disabled = 0
    and ordername != ''
    and domain_id = :domain_id
    and ordername is not null
  order by 1 desc
  limit 1;

-- gsqlite3-update-ordername-and-auth-query
-- DNSSEC update ordername and auth for a qname query.
update records
  set ordername = :ordername
    , auth = :auth
  where domain_id = :domain_id
    and name = :qname
    and disabled = 0;

-- gsqlite3-update-ordername-and-auth-type-query
-- DNSSEC update ordername and auth for a rrset query.
update records
  set ordername = :ordername
    , auth = :auth
  where domain_id = :domain_id
    and name = :qname
    and type = :qtype
    and disabled = 0;

-- gsqlite3-nullify-ordername-and-update-auth-query
-- DNSSEC nullify ordername and update auth for a qname query.
update records
  set ordername = NULL
    , auth = :auth
  where domain_id = :domain_id
    and name = :qname
    and disabled = 0;

-- gsqlite3-nullify-ordername-and-update-auth-type-query
-- DNSSEC nullify ordername and update auth for a rrset query.
update records
  set ordername = NULL
    , auth = :auth
  where domain_id = :domain_id
    and name = :qname
    and type = :qtype
    and disabled = 0;

03-domain-zone-manipulation.sql

--
-- Domain and zone manipulation
-- https://doc.powerdns.com/authoritative/backends/generic-sql.html#domain-and-zone-manipulation
--

-- gsqlite3-is-our-domain-query
-- Checks if the domain (either id or name) is in the ‘domains’ table. This query is run before any other (possibly heavy) query.
-- MISSING

-- gsqlite3-insert-zone-query
-- Add a new domain. This query also requires the type, masters and account fields
insert into domains (type, name, master, account, last_check, notified_serial)
  values (:type, :domain, :masters, :account, null, null);

-- gsqlite3-update-kind-query
-- Called to update the type of domain.
update domains
  set type = :kind
  where name = :domain;

-- gsqlite3-delete-zone-query
-- Called to delete all records of a zone. Used before an incoming AXFR.
delete from records
  where domain_id = :domain_id;

-- gsqlite3-delete-domain-query
-- Called to delete a domain from the domains-table.
delete from domains
  where name = :domain;

-- gsqlite3-get-all-domains-query
-- Used to get information on all active domains.
select domains.id, domains.name, records.content, domains.type, domains.master, domains.notified_serial, domains.last_check, domains.account
  from domains
  left join records on records.domain_id = domains.id
    and records.type = 'SOA'
    and records.name = domains.name
  where records.disabled = 0
    or :include_disabled;

-- gsqlite3-info-zone-query
-- Called to retrieve (nearly) all information for a domain.
select id, name, master, last_check, notified_serial, type, account
  from domains
  where name = :domain;

-- gsqlite3-insert-record-query
-- Called during incoming AXFR.
insert into records (content, ttl, prio, type, domain_id, disabled, name, ordername, auth, change_date)
  values (:content, :ttl, :priority, :qtype, :domain_id, :disabled, :qname, :ordername, :auth, null);

-- gsqlite3-update-account-query
-- Set the account for a domain.
update domains
  set account = :account
  where name = :domain;

-- gsqlite3-delete-names-query
-- Called to delete all records of a certain name.
delete from records
  where domain_id = :domain_id
    and name = :qname;

-- gsqlite3-delete-rrset-query
-- Called to delete an RRset based on domain_id, name and type.
delete from records
  where domain_id = :domain_id
    and name = :qname
    and type = :qtype;

-- gsqlite3-get-all-domain-metadata-query
-- Get all domain metadata for a domain.
-- https://doc.powerdns.com/authoritative/domainmetadata.html
select kind, content
  from domains, domainmetadata
  where domainmetadata.domain_id = domains.id
    and name = :domain;

-- gsqlite3-get-domain-metadata-query
-- Get a single piece of domain metadata.
-- https://doc.powerdns.com/authoritative/domainmetadata.html
select content
  from domains, domainmetadata
  where domainmetadata.domain_id = domains.id
    and name = :domain
    and domainmetadata.kind = :kind;

-- gsqlite3-clear-domain-metadata-query
-- Delete a single entry of domain metadata.
delete from domainmetadata
  where domain_id = (select id from domains where name = :domain)
    and domainmetadata.kind = :kind;

-- gsqlite3-clear-domain-all-metadata-query
-- Remove all domain metadata for a domain.
delete from domainmetadata
  where domain_id = (select id from domains where name = :domain);

-- gsqlite3-set-domain-metadata-query
-- Add domain metadata for a zone.
insert into domainmetadata (domain_id, kind, content)
  select id, :kind, :content from domains where name = :domain;

-- gsqlite3-add-domain-key-query
-- Called to a cryptokey to a domain.
insert into cryptokeys (domain_id, flags, active, content)
  select id, :flags, :active, :content
  from domains
  where name = :domain;

-- gsqlite3-list-domain-keys-query
-- Called to get all cryptokeys for a domain.
select cryptokeys.id, flags, active, content
  from domains, cryptokeys
  where cryptokeys.domain_id = domains.id
    and name = :domain;

-- gsqlite3-activate-domain-key-query
-- Called to set a cryptokey to active.
update cryptokeys
  set active = 1
  where domain_id = (select id from domains where name = :domain)
    and cryptokeys.id = :key_id;

-- gsqlite3-deactivate-domain-key-query
-- Called to set a cryptokey to inactive.
update cryptokeys
  set active = 0
  where domain_id = (select id from domains where name = :domain)
    and cryptokeys.id = :key_id;

-- gsqlite3-clear-domain-all-keys-query
-- Called to remove all DNSSEC keys for a zone.
delete from cryptokeys
  where domain_id = (select id from domains where name = :domain);

-- gsqlite3-remove-domain-key-query
-- Called to remove a crypto key.
delete from cryptokeys
  where domain_id = (select id from domains where name = :domain)
    and cryptokeys.id = :key_id;

04-master-slave-queries.sql

--
-- Master/slave queries
-- https://doc.powerdns.com/authoritative/backends/generic-sql.html#master-slave-queries
--

-- gsqlite3-info-all-master-query   (on masters)
-- Called to get data on all domains for which the server is master.
select id, name, master, last_check, notified_serial, type
  from domains
  where type = 'MASTER';

-- gsqlite3-update-serial-query   (on masters)
-- Called to update the last notified serial of a master domain.
update domains
  set notified_serial = :serial
  where id = :domain_id;

-- gsqlite3-info-all-slaves-query   (on slaves)
-- Called to retrieve all slave domains.
select id, name, master, last_check
  from domains
  where type = 'SLAVE';

-- gsqlite3-update-lastcheck-query    (on slaves)
-- Called to update the last time a slave domain was successfully checked for freshness.
update domains
  set last_check = :last_check
  where id = :domain_id;

-- gsqlite3-update-master-query   (on slaves)
-- Called to update the master address of a domain.
update domains
  set master = :master
  where name = :domain;

-- gsqlite3-supermaster-query   (on superslaves)
-- Called to determine if a certain host is a supermaster for a certain domain name.
select account
  from supermasters
  where ip = :ip
    and nameserver = :nameserver;

-- gsqlite3-supermaster-name-to-ips   (on superslaves)
-- Called to the IP and account for a supermaster.
select ip, account
  from supermasters
  where nameserver = :nameserver
    and account = :account;

05-tsig-queries.sql

--
-- TSIG
-- https://doc.powerdns.com/authoritative/backends/generic-sql.html#tsig
--

-- gsqlite3-get-tsig-key-query
-- Called to get the algorithm and secret from a named TSIG key.
select algorithm, secret
  from tsigkeys
  where name = :key_name;

-- gsqlite3-get-tsig-keys-query
-- Called to get all TSIG keys.
select name, algorithm, secret
  from tsigkeys;

-- gsqlite3-set-tsig-key-query
-- Called to set the algorithm and secret for a named TSIG key.
replace into tsigkeys (name, algorithm, secret)
  values (:key_name, :algorithm, :content);

-- gsqlite3-delete-tsig-key-query
-- Called to delete a named TSIG key.
delete from tsigkeys
  where name = :key_name;

06-comment-queries.sql

--
-- Comment queries
-- https://doc.powerdns.com/authoritative/backends/generic-sql.html#comment-queries
--

-- gsqlite3-list-comments-query
-- Called to get all comments in a zone.
select domain_id, name, type, modified_at, account, comment
  from comments
  where domain_id = :domain_id;

-- gsqlite3-insert-comment-query
-- Called to create a single comment for a specific RRSet.
insert into comments (domain_id, name, type, modified_at, account, comment)
  VALUES (:domain_id, :qname, :qtype, :modified_at, :account, :content);

-- gsqlite3-delete-comment-rrset-query
-- Called to delete all comments for a specific RRset.
delete from comments
  where domain_id = :domain_id
    and name = :qname
    and type = :qtype;

-- gsqlite3-delete-comments-query
-- Called to delete all comments for a zone. Usually called before deleting the entire zone.
delete from comments
  where domain_id = :domain_id;

-- gsqlite3-search-comments-query
-- Called to search for comment by name or content.
select domain_id, name, type, modified_at, account, comment
  from comments
  where name like :value
    or comment like :value2
  limit :limit;

99-undocumented.sql

--
-- undocumented queries (internal only?)
--

-- gsqlite3-get-last-inserted-key-id-query
select last_insert_rowid();

-- gsqlite3-master-zone-query
select master
  from domains
  where name = :domain
    and type = 'SLAVE';

-- gsqlite3-zone-lastchange-query
select max(change_date)
  from records
  where domain_id = :domain_id;