PowerDNS OpenDBX Backend/Configuration/SQL statements
From Linuxnetworks
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!