Nose: added missing opendbx-sql-lookupsoa statement

2009-10-02T20:34:53Z

added missing opendbx-sql-lookupsoa statement

Nose: removed backlink

2008-06-22T12:37:57Z

removed backlink

Nose: sql statements

2007-11-18T15:04:24Z

sql statements

New page

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-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!

----
Back to [[PowerDNS_OpenDBX_Backend|Overview]]

← Older revision		Revision as of 12:37, 22 June 2008
Line 121:		Line 121:

	'''Note''': If you change one of the SELECT statements must not change the order of the retrieved columns!		'''Note''': If you change one of the SELECT statements must not change the order of the retrieved columns!
−
−
−	~~----~~
−	~~Back to [[PowerDNS_OpenDBX_Backend\|Overview]]~~

@@ Line 40: / Line 40: @@
   FROM "records"
   WHERE "domain_id"=:id AND "name"=':name' AND "type"=':type'
 ; opendbx-sql-zonedelete : Delete all records from zone before inserting new ones via AXFR. Default is:

PowerDNS OpenDBX Backend/Configuration/SQL statements - Revision history

Nose: added missing opendbx-sql-lookupsoa statement

Nose: removed backlink

Nose: sql statements