[kea-dev] DHCP Hackaton summary

Thomas Markwalder tmark at isc.org
Wed Sep 24 14:05:52 UTC 2014


On 9/24/14, 7:47 AM, Marcin Siodelski wrote:
> I was thinking about organizing the data in a database a bit more and I
> feel like the reserved IPv6 addresses/prefixes should not be stored as a
> list but they should rather be stored in a separate table and reference
> the Host table (1:n) - just like we decided for options. Storing the
> reservations as a list in a single field will have numerous pitfalls
> listed here in no particular order:
>
> - No easy way to search a reservation for a particular IP address or
> prefix unless you use SQL functions searching by substring
> - No way to use the indexes per-address because they would point to a
> list of addresses. Note that indexes play important role for improving
> performance of queries.
> - No way to enforce uniqueness of the addresses in the database
> - Number of addresses is constrained by the number of bytes allocated
> for a string or a blob holding a list
> - Complex notation of the prefix, as it needs to be encoded with its length.
> - Can't delete a single reservation without fetching all addresses
> - It is hard to count reserved resources (for monitoring/statistical
> purposes)
> - It is hard to query for all reserved addresses
> - It complicates life of implementors of future tools to manage
> reservations as these tools would have to handle complex data structures
> within a particular data field
>
> So, I'd really like to see the host reservations in a separate table
> as depicted here
> http://kea.isc.org/attachment/wiki/HostReservationDesign/kea_host_reservation_db.png.
> The only reservation that may remain in the Hosts table is an IPv4
> reservation (or NULL).Technically, it could also be moved to the
> reservations table but since IPv4 address is only 4 bytes long it should
> be rather stored in a column of a different type than the IPv6 address
> or prefix. So, for performance reasons it doesn't make much sense to
> store IPV4 address as a text in the same column as IPv6 address.
>
> I also point out that the entry in the table with reservations doesn't
> make sense on its own (Identifying Relationship) so there should
> probably be triggers on the table with hosts, which removes the
> reservations from the reservations table before the DELETE operation on
> the Hosts table. This way, removal of the host entry would trigger
> removal of all reservations.
Marcin is correct.  The ipv6 values should be in their own table. You do
not need a trigger to delete reservations when a host is deleted, rather
use a cascading delete, standard SQL construct:


http://www.mysqltutorial.org/mysql-on-delete-cascade/

Thomas


More information about the kea-dev mailing list