Thread: Conditional cast for eg sorting?
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.
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.
-kgd
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
Steve Atkins wrote: > > On Jun 21, 2012, at 8:45 AM, Kris Deugau wrote: >> 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 >> ); > 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. *nod* Actually, it works out fine: (Note, type is really stored as the suitable value; just using the common abbreviations for convenience. A+PTR is a stored pseudotype that exports to tinydns' "=" record, publishing both an A record and PTR record. If/when I ever get around to implementing BIND export, it would create the separate A and PTR records as appropriate.) forward zone example.com: host type val example.com NS ns1.example.com foo.example.com A 192.168.2.4 both.example.com A+PTR 192.168.2.5 reverse zone 192.168.2.0/24: host type val ns2.example.com NS 192.168.2.0/24 notfoo.example.com PTR 192.168.2.6 both.example.com A+PTR 192.168.2.5 ns.small.company NS 192.168.2.16/28 16.16-31.2.168.192.in-addr.arpa CNAME 192.168.2.16 17.16-31.2.168.192.in-addr.arpa CNAME 192.168.2.17 ... 31.16-31.2.168.192.in-addr.arpa CNAME 192.168.2.31 (Or just use the handy "Delegate" pseudotype I've implemented, which creates both the NS record and all necessary CNAME records on export instead of making you manage them by hand.) reverse zone 192.168.2.16/28: host type val ns.small.company NS 192.168.2.16/28 mail.small.company PTR 192.168.2.18 Notice that the "LHS/RHS" logic of the host/val columns is inverted for reverse zones; this was required for the A+PTR type which uses only one record, but with nonzero values in both the domain_id and rdns_id FK columns to refer to both zones. The IPs and CIDR blocks are converted to a suitable in-addr.arpa name on export. Someday I may add a configuration flag, or even a per-use-case flag, to pick which delegation scheme to use for sub-/24 blocks, but this one seemed to make the most sense to me. I hadn't thought about allowing TXT records in reverse zones; but I don't see any real problems with allowing it. > 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. *nod* It's a pretty minor irritation, on the whole. And in large zones the UI's filtering/searching capability would be more useful in finding a specific record. > 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'); Thanks for the pointer. I'll have to play with it to see if it runs fast enough to not be a nasty performance drain. > (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. :P That helps too, I wasn't sure what to look for in the docs to find out where to wedge this in. -kgd