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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
