Difference between revisions of "PowerDNS OpenDBX Backend/Configuration/SQL statements"

Latest revision as of 22:34, 2 October 2009

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!