Hey ppl! I wanted to install a powerdns secondary nameserver for my ispconfig installation, so i took the time and built the basic queries for standard everyday operation. It doesn't have all the queries implemented, but i think i haven't missed any important one. So far it has been working fine. -------------------------------------------------------------------------------------------------------------------------------------- gmysql-any-id-query=SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM \ ( SELECT \ IF(type='CNAME' AND SUBSTR(name, -1,1) <> '.', CONCAT(data, '.', origin), data) as content, \ dns_rr.ttl, aux as prio, type, zone as domain_id, 0 as disabled,\ IF(SUBSTR(name, -1,1) = '.', SUBSTRING(name FROM 1 FOR CHAR_LENGTH(name) - 1), SUBSTRING(concat(name, '.', origin) FROM 1 FOR CHAR_LENGTH(concat(name, '.', origin)) - 1)) as name,\ 1 as auth, dns_rr.active \ FROM dns_rr inner join dns_soa on dns_soa.id = dns_rr.zone \ union \ select \ CONCAT('ns1.suserver.com. ', REPLACE(mbox,'@', '.'), ' ',serial, ' ', refresh, ' ', retry, ' ', expire, ' ', minimum) as content,\ ttl,0 as prio,'SOA' as type,id as domain_id,0 as disabled, substring(origin from 1 for char_length(origin)-1) as name, 1 as auth,active \ from dns_soa \ ) as records where active = 'Y' AND name = ? AND domain_id = ? gmysql-any-query=SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM \ ( SELECT \ IF(type='CNAME' AND SUBSTR(name, -1,1) <> '.', CONCAT(data, '.', origin), data) as content, \ dns_rr.ttl, aux as prio, type, zone as domain_id, 0 as disabled, \ IF(SUBSTR(name, -1,1) = '.', SUBSTRING(name FROM 1 FOR CHAR_LENGTH(name) - 1), SUBSTRING(concat(name, '.', origin) FROM 1 FOR CHAR_LENGTH(concat(name, '.', origin)) - 1)) as name,\ 1 as auth, dns_rr.active FROM dns_rr inner join dns_soa on dns_soa.id = dns_rr.zone \ union \ select \ CONCAT('ns1.suserver.com. ', REPLACE(mbox,'@', '.'), ' ',serial, ' ', refresh, ' ', retry, ' ', expire, ' ', minimum) as content,\ ttl,0 as prio,'SOA' as type,id as domain_id,0 as disabled, substring(origin from 1 for char_length(origin)-1) as name, 1 as auth,active \ from dns_soa \ ) as records where active = 'Y' AND name = ? gmysql-basic-query=SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM \ ( SELECT \ IF(type='CNAME' AND SUBSTR(name, -1,1) <> '.', CONCAT(data, '.', origin), data) as content, \ dns_rr.ttl, aux as prio, type, zone as domain_id, 0 as disabled, \ IF(SUBSTR(name, -1,1) = '.', SUBSTRING(name FROM 1 FOR CHAR_LENGTH(name) - 1), SUBSTRING(concat(name, '.', origin) FROM 1 FOR CHAR_LENGTH(concat(name, '.', origin)) - 1)) as name,\ 1 as auth, dns_rr.active \ FROM dns_rr inner join dns_soa on dns_soa.id = dns_rr.zone \ union \ select \ CONCAT('ns1.suserver.com. ', REPLACE(mbox,'@', '.'), ' ',serial, ' ', refresh, ' ', retry, ' ', expire, ' ', minimum) as content,\ ttl,0 as prio,'SOA' as type,id as domain_id,0 as disabled, substring(origin from 1 for char_length(origin)-1) as name, 1 as auth,active \ from dns_soa \ ) as records where active = 'Y' AND type = ? AND name = ? gmysql-get-all-domain-metadata-query=select '' as kind, '' as content from dns_soa where false and origin = ? gmysql-get-all-domains-query=select ds.id, SUBSTRING(origin FROM 1 FOR CHAR_LENGTH(origin) - 1) as name, CONCAT('ns1.suserver.com. ', REPLACE(mbox,'@', '.'), ' ',serial, ' ', refresh, ' ', retry, ' ', expire, ' ', minimum) as content, 'MASTER' as type, NULL as master, serial as notified_serial, NULL as last_check, NULL as account from dns_soa ds where active = 'Y' or ? gmysql-get-domain-metadata-query=select '' as content from dns_soa where false and origin = ? and origin = ? gmysql-id-query=SELECT data as content, ttl, aux as prio, type, zone as domain_id, 0 as disabled, name, 1 as auth FROM dns_rr WHERE active = 'Y' AND type = ? AND name = ? AND zone = ? gmysql-info-all-master-query=select ds.id, SUBSTRING(origin FROM 1 FOR CHAR_LENGTH(origin) - 1) as name, serial as notified_serial, CONCAT('ns1.suserver.com. ', REPLACE(mbox,'@', '.'), ' ',serial, ' ', refresh, ' ', retry, ' ', expire, ' ', minimum) as content from dns_soa ds gmysql-info-zone-query=SELECT id,SUBSTRING(origin FROM 1 FOR CHAR_LENGTH(origin) - 1) as name,NULL as master,NULL as last_check,serial as notified_serial,'MASTER' as type,NULL as account from dns_soa where SUBSTRING(origin FROM 1 FOR CHAR_LENGTH(origin) - 1)=? gmysql-list-query=SELECT IF(type IN ('MX','NS') AND SUBSTR(data, -1,1) = '.', SUBSTR(data, 1, CHAR_LENGTH(data)-1), data) as content, dns_rr.ttl, aux as prio, type, zone as domain_id, 0 as disabled, IF(SUBSTR(name, -1,1) = '.', name, concat(name,'.',origin)) , 1 as auth, IF(SUBSTR(name, -1,1) = '.', name, concat(name,'.',origin)) as ordername FROM dns_rr inner join dns_soa on dns_soa.id = dns_rr.zone WHERE (dns_rr.active = 'Y' OR ?) AND zone = ? ORDER BY name, type gmysql-list-subzone-query=SELECT IF(type IN ('MX','NS') AND SUBSTR(data, -1,1) = '.', SUBSTR(data, 1, CHAR_LENGTH(data)-1), data) as content, dns_rr.ttl, aux as prio, type, zone as domain_id, 0 as disabled, IF(SUBSTR(name, -1,1) = '.', name, concat(name,'.',origin)) , 1 as auth FROM dns_rr inner join dns_soa on dns_soa.id = dns_rr.zone WHERE dns_rr.active = 'Y' AND (name = ? OR name LIKE ?) AND zone = ? gmysql-search-records-query=SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM \ ( SELECT \ IF(type='CNAME' AND SUBSTR(name, -1,1) <> '.', CONCAT(data, '.', origin), data) as content, \ dns_rr.ttl, aux as prio, type, zone as domain_id, 0 as disabled,\ IF(SUBSTR(name, -1,1) = '.', SUBSTRING(name FROM 1 FOR CHAR_LENGTH(name) - 1), SUBSTRING(concat(name, '.', origin) FROM 1 FOR CHAR_LENGTH(concat(name, '.', origin)) - 1)) as name,\ 1 as auth, dns_rr.active \ FROM dns_rr inner join dns_soa on dns_soa.id = dns_rr.zone \ union \ select \ CONCAT('ns1.suserver.com. ', REPLACE(mbox,'@', '.'), ' ',serial, ' ', refresh, ' ', retry, ' ', expire, ' ', minimum) as content,\ ttl,0 as prio,'SOA' as type,id as domain_id,0 as disabled, substring(origin from 1 for char_length(origin)-1) as name, 1 as auth,active \ from dns_soa \ ) as records where name LIKE ? OR content LIKE ? LIMIT ? gmysql-update-serial-query=update dns_soa set serial=? where id=? ------------------------------------------------------------------------------------------- I hope somebody else finds it useful, and any improvements/suggestions are welcome! Best! Leo
