Re: Conditional cast for eg sorting?

On Jun 21, 2012, at 8:45 AM, Kris Deugau wrote:

> I'm writing a tool for web-based management of DNS records, and I've
> come up against a UI nuisance that I'm hoping I can get solved in
> Postgres instead of some higher layer.
>
> One table contains all of the live records:
>
> CREATE TABLE records (
> domain_id integer NOT NULL DEFAULT 0,
> rdns_id integer NOT NULL DEFAULT 0,
> record_id serial NOT NULL,
> host text DEFAULT '' NOT NULL,
> "type" integer DEFAULT 1 NOT NULL,
> val text DEFAULT '' NOT NULL,
> distance integer DEFAULT 0 NOT NULL,
> weight integer DEFAULT 0 NOT NULL,
> port integer DEFAULT 0 NOT NULL,
> ttl integer DEFAULT 7200 NOT NULL,
> description text
> );
>
> host is the hostname, val is the target or result for forward zones
>
> For reverse zones, val is the IP (strictly speaking, the ip6.arpa or
> in-addr.arpa "hostname", stored as an IP address and converted on
> export), and host is the resulting hostname.
>
> For reverse zones I can simply sort on CAST(val AS inet), since val
> should never be anything other than a valid IP or CIDR.

It's valid to have other entries in in-addr.arpa zones. TXT, NS and
CNAME are fairly common - see RFC 2317 or 4183, or the DeGroot
hack.

If you block those in your UI, you're putting artificial limits on what your
users can do (and there's been a long history of regretting that, back at
least to verisign's web interface being unable to add SPF records). If you
don't block them in your UI you risk your queries throwing errors and
failing due to that cast.

> For forward zones, though, I can't just unconditionally cast the column
> as inet, because there are all kinds of values that are not valid IP or
> CIDR addresses. I still want to sort the IPs in this field properly
> though; eg, 192.168.1.100 should come just after 192.168.1.99, not
> 192.168.1.10.
>
> Is there any way to conditionally cast a value for sorting? I don't
> care if IP addresses end up in a big block at the beginning or end of
> the list so long as it's consistent.

I suspect that users aren't going to care about sorting by the RHS
much, rather they're going to want to sort by the LHS,
so it's probably not an issue that'll be too serious with real DNS
data.

In general, though, I'd create a function that took "type" and "val" and
converted them into a string suitable for sorting on. At it's simplest
that might leave everything but A records as-is, and convert
A records to something sortable:

lpad(split_part($1, '.', 1), 3, '000') || lpad(split_part($1, '.', 2), 3, '000') || lpad(split_part($1, '.', 3), 3, '000') || lpad(split_part($1, '.', 4), 3, '000');

(Wrapping that in a SQL or pl/pgsql function with CASE statement to
handle A records differently is left as an exercise for the reader :) ).

Then you can order by the result of that function, and it should seem
sensible to the user.

Cheers,
Steve