Re: Conditional cast for eg sorting? - Mailing list pgsql-general

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