On 2007-12-10 at 20:33 -0600, Craig Jackson wrote:
> I have written a Mysql stored procedure to whitelist $recipients, by
> parsing that variable. It did not occur to me to use a stored procedure
> for this -- looks a lot harder to do.
I'm not a MySQL user; PostgreSQL is my poison of choice when I feel
compelled to twist my brain with SQL. (Cheaper than long-sleeved white
coats (not that I've checked into the price of those, you understand)).
Here's what I came up with for PostgreSQL using PL/pgSQL; please note
that I am not a heavy DB user and there's probably lots of ways to
optimise this. Or heck, you could write it in C and load it into Exim
after all via ${dlfunc}; hopefully what I did can be translated into
MySQL so will give you ideas.
Note that I'm such a lightweight DB user that I hadn't even gotten
around to using my idle DB for any real work and it didn't have PL/pgSQL
loaded. I connected as root to template1, did "CREATE LANGUAGE
plpgsql;" and all was well thereafter. The \copy line in what follows
is a psql load from client side of the list of two-level-tlds which
someone else posted to this thread.
CREATE DATABASE basic_resources;
\c basic_resources
CREATE TABLE two_level_tlds(tld varchar(40) NOT NULL);
\copy two_level_tlds(tld) FROM 'two-level-tlds'
CREATE UNIQUE INDEX i_two_level_tlds_tld ON two_level_tlds(tld);
CREATE FUNCTION domain_possibilities(varchar)
-- given domain a.b.c.d.e return [a.b.c.d.e],[b.c.d.e],[c.d.e],[d.e]
-- does not pay attention to any semantic value of the components
RETURNS SETOF varchar
STABLE STRICT
AS $$
DECLARE
dom varchar := $1;
BEGIN
WHILE dom LIKE '%._%' LOOP
RETURN NEXT dom;
dom := substr(dom, position('.' IN dom)+1);
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION trim_domain(varchar)
-- given domain a.b.c.d.e find the shortest domain from domain_possibilities
-- which is not a gTLD or ccTLD registrar; if none found, return original domain
RETURNS varchar
STABLE STRICT
AS $$
DECLARE
dom varchar;
tmp record;
tmp2 record;
BEGIN
SELECT INTO tmp * FROM domain_possibilities($1) ORDER BY char_length(domain_possibilities) ASC LIMIT 1;
IF NOT FOUND THEN
RETURN $1;
END IF;
dom := tmp.domain_possibilities;
SELECT INTO tmp2 tld FROM two_level_tlds WHERE tld = dom;
IF NOT FOUND THEN
RETURN dom;
END IF;
FOR tmp IN SELECT * FROM domain_possibilities($1) ORDER BY char_length(domain_possibilities) ASC LOOP
dom := tmp.domain_possibilities;
SELECT INTO tmp2 tld FROM two_level_tlds where tld = dom;
IF NOT FOUND THEN
RETURN dom;
END IF;
END LOOP;
RETURN $1;
END;
$$ LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION domain_possibilities(varchar) TO PUBLIC;
GRANT EXECUTE ON FUNCTION trim_domain(varchar) TO PUBLIC;
