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
signature.asc
Description: This is a digitally signed message part.
_______________________________________________ Pdns-users mailing list [email protected] http://mailman.powerdns.com/mailman/listinfo/pdns-users
