[Pdns-users] OpenDBX Backend and serial numbers.

[Max Lock]

http://www.datanet.co.uk/default.aspx http://www.datanet.co.uk/default.aspx
 
--------------------------------------------------------------------------
Hi,

 Well currently I have a legacy database backend, that I've grafted pdns
onto. That database also drives some scripts that create bind zonefiles
for some other dns servers. The database also contains a field that
holds the serial number for the zone, and that serial number must match
across all authorative dns servers for a given zone.

 At the moment I have pdns working fine, but it seems to be auto
generating a serial number for the zones. Having looked at the opendbx
backend, I see that it can handle stored serial numbers, but there's no
example of an sql query which returns the serial number from the
database, so I don't know what fieldname to generate, and in what
position to place it. For example, one of my (horrible) sql queries
looks like this:

opendbx-sql-list=\
select domain_id,name,type,ttl,prio,content from (\
select domain_id,name,type,ttl,prio,content from (select domains.id as
domain_id, concat(hosts.host, '.', domains.domain) as name, hosts.type,
14400 as ttl, 0 as prio, ho
sts.ip as content from hosts left join domains on hosts.domain =
domains.domain) as hoststable where domain_id=':id' \
union all \
select domain_id,name,type,ttl,prio,content from (select domains.id as
domain_id, mx.domain as name, 'MX' as type, 14400 as ttl, mx.level as
prio, mx.host as content from
 mx left join domains on mx.domain = domains.domain where mx.sub='') as
mxtable where domain_id=':id' \
union all \
select domain_id,name,type,ttl,prio,content from (select domains.id as
domain_id, concat(mx.sub,'.',mx.domain) as name, 'MX' as type, 14400 as
ttl, mx.level as prio, mx.h
ost as content from mx left join domains on mx.domain = domains.domain
where mx.sub!='') as mxsubtable where name=':name' and type=':type' \
union all \
select domain_id,name,type,ttl,prio,content from (select domains.id as
domain_id, domains.domain as name, 'SOA' as type, '' as ttl, 0 as prio,
'' as content from domains)
 as domainsoatable where domain_id=':id' \
) as maintable \
where maintable.domain_id=':id'

So for example, I can display the correct serial number for a given
domain with the following:

select from_unixtime(date_ammend,'%Y%m%d%h') from domains where domain =
'foo.com';

-Cheers Max.



--------------------------------------------------------------------------
Max Lock - Senior Systems Administrator
Datanet - Hosting, Connectivity & Business Continuity
0845 130 6010
0845 130 6020
mailto:Max.Lock at datanet.co.uk
http://www.datanet.co.uk/
Registered Office: DATANET.CO.UK Limited, Aspen House, Barley Way, Ancells Business Park, Fleet, Hampshire, GU51 2UT Registered in England - No. 3214053 
Providing Internet Solutions for Business since 1996, Datanet, over 12 years of excellence in service, support and IP solutions
http://www.datanet.co.uk/awards_and_affiliates.aspx
http://www.datanet.co.uk/datacentre_diary.aspx
 
-----Original Message-----

From: Norbert Sendetzky <norbert at linuxnetworks.de>
To: pdns-users at mailman.powerdns.com
Subject: Re: [Pdns-users] OpenDBX Backend and serial numbers.
Date: Thu, 16 Oct 2008 19:05:31 +0200


Hi Max

>  I'm using the OpenDBX backend, and by default it generates serial
> numbers as it should. However I want to use the notified_serial field in
> the database. How shoud the sql queries for opendbx-sql-list be changed?
> The default is:
>
> opendbx-sql-list=SELECT "domain_id", "name", "type", "ttl", "prio",
> "content" FROM "records" WHERE "domain_id"=:id

What do you want to use the notified_serial field for?


Norbert
_______________________________________________
Pdns-users mailing list
Pdns-users at mailman.powerdns.com
http://mailman.powerdns.com/mailman/listinfo/pdns-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20081017/ffd1aa2d/attachment-0001.html>