PowerDNS OpenDBX Backend/Configuration/SQL domain check

The PowerDNS opendbx backend offers the possibility to adapt all SQL statements for specific needs or to provide additional functionality. The SQL statements for the configuration options below allows you to disable domains without deleting them from the database. This is especially useful if you need to remove a DNS zone from your server temporarily.

Note: Joining tables is more costly than reading from a single table. Depending on the database system used, your performance will degrade more or less.

Modified SQL statements

opendbx-sql-list 

Retrieve records for AXFR zone transfers

SELECT
  r."domain_id", r."name", r."type",
  r."ttl", r."prio", r."content"
FROM "records" r
JOIN "domains" d ON d."id"=r."domain_id"
WHERE
  r."domain_id"=:id AND d."status"='A'

opendbx-sql-lookup 

Lookup records by host name

SELECT
  r."domain_id", r."name", r."type",
  r."ttl", r."prio", r."content"
FROM "records" r
JOIN "domains" d ON d."id"=r."domain_id"
WHERE
  r."name"=':name' AND d."status"='A'

opendbx-sql-lookupid 

Lookup DNS records by domain id and host name

SELECT
  r."domain_id", r."name", r."type",
  r."ttl", r."prio", r."content"
FROM "records" r
JOIN "domains" d ON d."id"=r."domain_id"
WHERE
  r."domain_id"=:id AND r."name"=':name'
  AND d."status"='A'

opendbx-sql-lookuptype 

Lookup DNS records by host name and DNS type

SELECT
  r."domain_id", r."name", r."type",
  r."ttl", r."prio", r."content"
FROM "records" r
JOIN "domains" d ON d."id"=r."domain_id"
WHERE
  r."name"=':name' AND r."type"=':type'
  AND d.status='A'

opendbx-sql-lookuptypeid 

Lookup DNS records by domain id, host name and DNS type

SELECT
  r."domain_id", r."name", r."type",
  r."ttl", r."prio", r."content"
FROM "records" r
JOIN "domains" d ON d."id"=r."domain_id"
WHERE
  r."domain_id"=:id AND r."name"=':name'
  AND r."type"=':type' AND d.status='A'