[Pdns-dev] [PATCH] Allow gmysql backend to call stored procedures

[Jonathan Oddy]

Calling stored procedures using the mysql client library requires that 
the connection is opened with the CLIENT_MULTI_RESULTS flag, and that 
the client is capable of dealing with multiple result sets. The attached 
patch sets that flag and copes with multiple result sets by ignoring 
(and freeing) all but the first one returned.

Why this crazy requirement? I've got a database based on the myDNS 
schema and can't easily change our management tools to migrate away from 
said schema. Unfortunately the performance of a VIEW that presents the 
data in a format suitable for pDNS is dreadful, as there are significant 
differences between myDNS and pDNS behaviour. I've also considered 
triggers on the myDNS tables that update pDNS friendly tables, however 
that came with a different set of problems.

So, thus came about the attached patch and set of stored procedures. The 
former might be useful for other people, the latter I've attached to 
satisfy the readers' morbid curiosity. I'm well aware that performance 
will not be ideal, however it seems to work and it's faster than the 
alternatives.

Any feedback would be appreciated.
-- 
Jonathan Oddy
Senior Linux Systems Engineer
Hostway UK
-------------- next part --------------

delimiter //

DROP PROCEDURE pdns_find_zone//

CREATE PROCEDURE pdns_find_zone(IN fqdn VARCHAR(255), OUT zoneid INT, OUT host VARCHAR(255), OUT domain VARCHAR(255))
BEGIN
	DECLARE npos INT;

	SET npos = 1;
	SET zoneid = NULL;
	SET host = '';
	SET domain = fqdn;
	
	SELECT id INTO zoneid FROM Zone WHERE origin = CONCAT(domain, '.');

	SET npos = LOCATE('.', domain);
	WHILE npos != 0 AND zoneid IS NULL
	DO
		SET host = CONCAT(host, IF(host != '','.',''), LEFT(domain, npos-1));
		SET domain = RIGHT(domain, LENGTH(domain)-npos);
		
		SELECT id INTO zoneid FROM Zone WHERE origin = CONCAT(domain, '.');

		SET npos = LOCATE('.', domain);
	END WHILE;
	
	IF zoneid IS NULL
	THEN
		SET host = NULL;
		SET domain = NULL;
	END IF;
END;//

DROP PROCEDURE pdns_build_soa//

CREATE PROCEDURE pdns_build_soa(IN zoneid INT, OUT soa VARCHAR(255))
BEGIN
	SELECT CONCAT(z.ns, ' ', z.mbox, ' ', z.serial, ' ', z.refresh, ' ', z.retry, ' ', z.expire, ' ', z.ttl) INTO soa FROM Zone AS z WHERE z.id = zoneid;
END;//

DROP PROCEDURE pdns_basic_query//
CREATE PROCEDURE pdns_basic_query (type VARCHAR(6), name VARCHAR(255))
BEGIN
	DECLARE zoneid INT;
	DECLARE hname VARCHAR(255);
	DECLARE dname VARCHAR(255);
	DECLARE soar VARCHAR(255);

	CALL pdns_find_zone(name, zoneid, hname, dname);

	IF zoneid IS NOT NULL
	THEN
		IF type = 'SOA'
		THEN
			IF name = dname
			THEN
				CALL pdns_build_soa(zoneid, soar);
				SELECT soar AS content, 10800 AS ttl, 0 AS prio, 'SOA' AS type, zoneid AS domain_id, dname AS name;
			ELSE
				SELECT 1 LIMIT 0;
			END IF;
		ELSE
			SELECT TRIM(TRAILING '.' FROM IF(zr.type NOT IN ('NS', 'MX', 'CNAME', 'PTR') OR RIGHT(zr.data, 1) = '.',zr.data,CONCAT(zr.data,'.',dname))) AS content, zr.ttl AS ttl, zr.aux AS prio, zr.type AS type, zr.zone AS domain_id, name AS name FROM ZoneRecord as zr WHERE zr.zone = zoneid AND (zr.name = hname OR zr.name = CONCAT(name, '.')) AND zr.active = 1 AND zr.type = type;
		END IF;
	ELSE
		SELECT 1 LIMIT 0;
	END IF;
END;//

DROP PROCEDURE pdns_id_query//
CREATE PROCEDURE pdns_id_query (type VARCHAR(6), name VARCHAR(255), domainid INT)
BEGIN
	DECLARE zoneid INT;
	DECLARE hname VARCHAR(255);
	DECLARE dname VARCHAR(255);
	DECLARE soar VARCHAR(255);

	CALL pdns_find_zone(name, zoneid, hname, dname);

	IF zoneid IS NOT NULL AND domainid = zoneid
	THEN
		IF type = 'SOA'
		THEN
			IF name = dname
			THEN
				CALL pdns_build_soa(zoneid, soar);
				SELECT soar AS content, 10800 AS ttl, 0 AS prio, 'SOA' AS type, zoneid AS domain_id, dname AS name;
			ELSE
				SELECT 1 LIMIT 0;
			END IF;
		ELSE
			SELECT TRIM(TRAILING '.' FROM IF(zr.type NOT IN ('NS', 'MX', 'CNAME', 'PTR') OR RIGHT(zr.data, 1) = '.',zr.data,CONCAT(zr.data,'.',dname))) AS content, zr.ttl AS ttl, zr.aux AS prio, zr.type AS type, zr.zone AS domain_id, name AS name FROM ZoneRecord as zr WHERE zr.zone = zoneid AND (zr.name = hname OR zr.name = CONCAT(name, '.')) AND zr.active = 1 AND zr.type = type;
		END IF;
	ELSE
		SELECT 1 LIMIT 0;
	END IF;
END;//

DROP PROCEDURE pdns_any_query//
CREATE PROCEDURE pdns_any_query (name VARCHAR(255))
BEGIN
	DECLARE zoneid INT;
	DECLARE hname VARCHAR(255);
	DECLARE dname VARCHAR(255);
	DECLARE soar VARCHAR(255);

	CALL pdns_find_zone(name, zoneid, hname, dname);

	IF zoneid IS NOT NULL
	THEN
		IF name = dname
		THEN
			CALL pdns_build_soa(zoneid, soar);
			SELECT soar AS content, 10800 AS ttl, 0 AS prio, 'SOA' AS type, zoneid AS domain_id, dname AS name UNION SELECT TRIM(TRAILING '.' FROM IF(zr.type NOT IN ('NS', 'MX', 'CNAME', 'PTR') OR RIGHT(zr.data, 1) = '.',zr.data,CONCAT(zr.data,'.',dname))) AS content, zr.ttl AS ttl, zr.aux AS prio, zr.type AS type, zr.zone AS domain_id, name AS name FROM ZoneRecord as zr WHERE zr.zone = zoneid AND (zr.name = hname OR zr.name = CONCAT(name, '.')) AND zr.active = 1;
		ELSE
			SELECT TRIM(TRAILING '.' FROM IF(zr.type NOT IN ('NS', 'MX', 'CNAME', 'PTR') OR RIGHT(zr.data, 1) = '.',zr.data,CONCAT(zr.data,'.',dname))) AS content, zr.ttl AS ttl, zr.aux AS prio, zr.type AS type, zr.zone AS domain_id, name AS name FROM ZoneRecord as zr WHERE zr.zone = zoneid AND (zr.name = hname OR zr.name = CONCAT(name, '.')) AND zr.active = 1;
		END IF;
	ELSE
		SELECT 1 LIMIT 0;
	END IF;
END;//

DROP PROCEDURE pdns_any_id_query//
CREATE PROCEDURE pdns_any_id_query (name VARCHAR(255), domainid INT)
BEGIN
	DECLARE zoneid INT;
	DECLARE hname VARCHAR(255);
	DECLARE dname VARCHAR(255);
	DECLARE soar VARCHAR(255);

	CALL pdns_find_zone(name, zoneid, hname, dname);

	IF zoneid IS NOT NULL AND domainid = zoneid
	THEN
		IF name = dname
		THEN
			CALL pdns_build_soa(zoneid, soar);
			SELECT soar AS content, 10800 AS ttl, 0 AS prio, 'SOA' AS type, zoneid AS domain_id, dname AS name UNION SELECT TRIM(TRAILING '.' FROM IF(zr.type NOT IN ('NS', 'MX', 'CNAME', 'PTR') OR RIGHT(zr.data, 1) = '.',zr.data,CONCAT(zr.data,'.',dname))) AS content, zr.ttl AS ttl, zr.aux AS prio, zr.type AS type, zr.zone AS domain_id, name AS name FROM ZoneRecord as zr WHERE zr.zone = zoneid AND (zr.name = hname OR zr.name = CONCAT(name, '.')) AND zr.active = 1;
		ELSE
			SELECT TRIM(TRAILING '.' FROM IF(zr.type NOT IN ('NS', 'MX', 'CNAME', 'PTR') OR RIGHT(zr.data, 1) = '.',zr.data,CONCAT(zr.data,'.',dname))) AS content, zr.ttl AS ttl, zr.aux AS prio, zr.type AS type, zr.zone AS domain_id, name AS name FROM ZoneRecord as zr WHERE zr.zone = zoneid AND (zr.name = hname OR zr.name = CONCAT(name, '.')) AND zr.active = 1;
		END IF;
	ELSE
		SELECT 1 LIMIT 0;
	END IF;
END;//

DROP PROCEDURE pdns_list_query//
CREATE PROCEDURE pdns_list_query (domainid INT)
BEGIN
	DECLARE domain VARCHAR(255);
	DECLARE soar VARCHAR(255);

	SELECT origin INTO domain FROM Zone WHERE id = domainid;
	IF domain IS NOT NULL
	THEN
		CALL pdns_build_soa(domainid, soar);
		SELECT TRIM(TRAILING '.' FROM IF(zr.type NOT IN ('NS', 'MX', 'CNAME', 'PTR') OR RIGHT(zr.data, 1) = '.',zr.data,CONCAT(zr.data,'.',domain))) AS content, zr.ttl AS ttl, zr.aux AS prio, zr.type AS type, zr.zone AS domain_id, TRIM(TRAILING '.' FROM IF(zr.name IS NULL OR zr.name = '',domain,IF(RIGHT(zr.name, 1) = '.',zr.name,CONCAT(zr.name, '.', domain)))) AS name FROM ZoneRecord as zr WHERE zr.zone = domainid AND zr.active = 1 AND (RIGHT(zr.name, 1) != '.' OR (LENGTH(TRIM(TRAILING '.' FROM domain)) <= LENGTH(TRIM(TRAILING '.' FROM zr.name)) AND (RIGHT(TRIM(TRAILING '.' FROM (zr.name)), LENGTH(TRIM(TRAILING '.' FROM (domain)))) = TRIM(TRAILING '.' FROM (domain))))) UNION SELECT soar AS content, 10800 AS ttl, 0 AS prio, 'SOA' AS type, domainid AS domain_id, domain AS name;
	ELSE
		SELECT 1 LIMIT 0;
	END IF;
END;//

delimiter ;

-------------- next part --------------
A non-text attachment was scrubbed...
Name: pdns_multi_results_support.diff
Type: text/x-diff
Size: 722 bytes
Desc: not available
Url : http://mailman.powerdns.com/pipermail/pdns-dev/attachments/20081105/f5a28a74/pdns_multi_results_support.bin