Search Postgresql Archives

Re: Conditional cast for eg sorting?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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 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
host              type   val       NS   A  A+PTR

reverse zone
host                             type   val                  NS               PTR                 A+PTR                 NS  CNAME  CNAME
...  CNAME

(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
host                type  val    NS  PTR

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 name on

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.


Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:

[Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Programming PHP]     [Kernel Newbies]     [PHP Classes]     [Find Someone Nice]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

Add to Google Powered by Linux