Re: [exim] Regex or sg how

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;