PowerDNS OpenDBX Backend/Configuration/SQL domain check - Revision history

Nose: Fixed statements as :id won't be replaced in all statements by the PowerDNS odbxbackend

2009-12-09T20:54:15Z

Fixed statements as :id won't be replaced in all statements by the PowerDNS odbxbackend

Nose: removed backlink

2008-06-22T12:38:19Z

removed backlink

Nose: initial

2007-11-19T17:48:44Z

initial

New page

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"=: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"=: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"=: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"=: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"=:id
WHERE
r."domain_id"=:id AND r."name"=':name'
AND r."type"=':type' AND d.status='A'

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

← Older revision		Revision as of 12:38, 22 June 2008
Line 57:		Line 57:
	r."domain_id"=:id AND r."name"=':name'		r."domain_id"=:id AND r."name"=':name'
	AND r."type"=':type' AND d.status='A'		AND r."type"=':type' AND d.status='A'
−
−
−	~~----~~
−	~~Back to [[PowerDNS_OpenDBX_Backend\|Overview]]~~

@@ Line 11: / Line 11: @@
     r."ttl", r."prio", r."content"
   FROM "records" r
-  JOIN "domains" d ON d."id"=:id
+  JOIN "domains" d ON d."id"=r."domain_id"
   WHERE
     r."domain_id"=:id AND d."status"='A'
@@ Line 21: / Line 21: @@
     r."ttl", r."prio", r."content"
   FROM "records" r
-  JOIN "domains" d ON d."id"=:id
+  JOIN "domains" d ON d."id"=r."domain_id"
   WHERE
     r."name"=':name' AND d."status"='A'
@@ Line 31: / Line 31: @@
     r."ttl", r."prio", r."content"
   FROM "records" r
-  JOIN "domains" d ON d."id"=:id
+  JOIN "domains" d ON d."id"=r."domain_id"
   WHERE
     r."domain_id"=:id AND r."name"=':name'
@@ Line 42: / Line 42: @@
     r."ttl", r."prio", r."content"
   FROM "records" r
-  JOIN "domains" d ON d."id"=:id
+  JOIN "domains" d ON d."id"=r."domain_id"
   WHERE
     r."name"=':name' AND r."type"=':type'
@@ Line 53: / Line 53: @@
     r."ttl", r."prio", r."content"
   FROM "records" r
-  JOIN "domains" d ON d."id"=:id
+  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'