PowerDNS OpenDBX Backend/Configuration/SQL statements

From Linuxnetworks
Jump to: navigation, search

The opendbx backend gives you the power to modify all the SQL statements used in its code. This enables you to enhance them for specific needs or adapt them to your environment. The list of statements consists of:

opendbx-sql-list 
Select records which will be returned to clients asking for zone transfers (AXFR). Default is:
SELECT
  "domain_id", "name", "type",
  "ttl", "prio", "content"
FROM "records"
WHERE "domain_id"=:id
opendbx-sql-lookup 
Retrieve DNS records by name. Default is:
SELECT
  "domain_id", "name", "type",
  "ttl", "prio", "content"
FROM "records"
WHERE "name"=':name'
opendbx-sql-lookupid 
Retrieve DNS records by id and name. Default is:
SELECT
  "domain_id", "name", "type",
  "ttl", "prio", "content"
FROM "records"
WHERE "domain_id"=:id AND "name"=':name'
opendbx-sql-lookuptype 
Retrieve DNS records by name and type. Default is:
SELECT
  "domain_id", "name", "type",
  "ttl", "prio", "content"
FROM "records"
WHERE "name"=':name' AND "type"=':type'
opendbx-sql-lookuptypeid 
Retrieve DNS records by id, name and type. Default is:
SELECT
  "domain_id", "name", "type",
  "ttl", "prio", "content"
FROM "records"
WHERE "domain_id"=:id AND "name"=':name' AND "type"=':type'
opendbx-sql-lookupsoa 
Retrieve SOA record for domain. Default is:
SELECT
  d."id", d."auto_serial", r."ttl", r."content"
FROM "records" r
JOIN "domains" d ON r."domain_id"=d."id"
WHERE r."name"=':name' AND r."type"='SOA' AND d."status"='A'
opendbx-sql-zonedelete 
Delete all records from zone before inserting new ones via AXFR. Default is:
DELETE FROM "records" WHERE "domain_id"=:id
opendbx-sql-zoneinfo 
Get stored information about a domain. Default is:
SELECT
  d."id", d."name", d."type",
  d."master", d."last_check", r."content"
FROM "domains" d
LEFT JOIN "records" r ON ( d."id"=r."domain_id" AND r."type"='SOA' )
WHERE d."name"=':name' AND d."status"='A'
opendbx-sql-transactbegin 
Start transaction before updating a zone via AXFR. Default is:
BEGIN
opendbx-sql-transactend 
Commit transaction after updating a zone via AXFR. Default is:
COMMIT
opendbx-sql-transactabort 
Undo changes if an error occurred while updating a zone via AXFR. Default is:
ROLLBACK
opendbx-sql-insert-slave 
Adds a new zone from the authoritative DNS server which is currently retrieved via AXFR. Default is:
INSERT INTO "domains"
  ( "name", "type", "master", "account" )
  VALUES ( '%s', 'SLAVE', '%s', '%s' )
opendbx-sql-insert-record 
Adds new records of a zone form the authoritative DNS server which are currently retrieved via AXFR. Default is:
INSERT INTO "records"
  ( "domain_id", "name", "type", "ttl", "prio", "content" )
  VALUES ( %d, '%s', '%s', %d, %d, '%s' )
opendbx-sql-update-serial 
Set zone serial to value of last update. Default is:
UPDATE "domains"
SET "notified_serial"=%d
WHERE "id"=%d
opendbx-sql-update-lastcheck 
Set time of last zone check. Default is:
UPDATE "domains"
SET "last_check"=%d
WHERE "id"=%d
opendbx-sql-master 
Get master record for zone. Default is:
SELECT "master"
FROM "domains"
WHERE "name"=':name' AND "status"='A' AND "type"='SLAVE'
opendbx-sql-supermaster 
Get supermaster info. Default is:
SELECT "account"
FROM "supermasters"
WHERE "ip"=':ip' AND "nameserver"=':ns'
opendbx-sql-infoslaves 
Get all unfresh slaves. Default is:
SELECT
  d."id", d."name", d."master", d."notified_serial",
  d."last_check", r."change_date", r."content"
FROM "domains" d
LEFT JOIN "records" r ON ( d."id"=r."domain_id" AND r."type"='SOA' )
WHERE d."status"='A' AND d."type"='SLAVE'
opendbx-sql-infomasters 
Get all updates masters. Default is:
SELECT
  d."id", d."name", d."master", d."notified_serial",
  d."last_check", r."change_date", r."content"
FROM "domains" d
JOIN "records" r ON d."id"=r."domain_id"
WHERE d."status"='A' AND d."type"='MASTER' AND r."type"='SOA'

Note: If you change one of the SELECT statements must not change the order of the retrieved columns!