[Pdns-users] PowerDNS MySQL API in Perl by a PowerDNS user

[Mark Watts]

On Wednesday 14 November 2007 23:51:16 Duane wrote:
> bert hubert wrote:
>
> I've been thinking about something similar for a while, but a little
> different, in fact a lot of the work in the perl code I've seen could
> easily be dealt with in another manner and a lot more efficient and
> language neutral.
>
> What the default pdns mysql schema lacks is foreign keys and a couple of
> well crafted triggers and/or stored procedures, even though InnoDB is
> already the suggested table type. Well InnoDB has foreign keys, but it
> needs MySQL 5+ for triggers and stored procedures.
>
> For example, a trigger could be added to remove all rows from the
> records table when the zone is removed.

No need for a trigger:

CREATE TABLE records (
        id              INT auto_increment,
        domain_id       INT DEFAULT NULL,
        name            VARCHAR(255) DEFAULT NULL,
        type            VARCHAR(6) DEFAULT NULL,
        content         VARCHAR(255) DEFAULT NULL,
        ttl             INT DEFAULT NULL,
        prio            INT DEFAULT NULL,
        change_date     INT DEFAULT NULL,
        primary key(id),
        CONSTRAINT `records_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES 
`domains` (`id`) ON DELETE CASCADE
)type=InnoDB;

Note the CONSTRAINT in there. It uses referential integrity to ensure that 
records get deleted when a domain is.

> Also in the perl code is a function to check record type, why not just
> use an enum field and check for insert/update errors?
>
> Also on the insert/update/delete of rows in the records table you could
> have another trigger to update the SOA for the domain.
>
> I don't mean to disuade the person that has coded this, but imho isn't
> the best way to go when dealing with MySQL specifically.

Mark.

-- 
Mark Watts BSc RHCE MBCS
Senior Systems Engineer
QinetiQ Trusted Information Management
Trusted Solutions and Services Group
GPG Key: http://keyserver.veridis.com:11371/search?q=0x455420ED
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part.
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20071115/fe3f93d3/attachment-0001.sig>