[Kea-users] Kea schema meta-issues (was Host reservations in PostgreSQL)

[Shane Kerr]

Tomek,

At 2015-12-29 17:22:21 +0100
Tomek Mrugalski <tomasz at isc.org> wrote:

> > I ask because if it's a schema issue then I'm happy to attempt this,
> > because I kind of want this for my environment. :) (I could switch to  
> By all means, please do! Updating the schema is the first step to add
> this support. Take a look at the schema init and upgrade scripts in
> src/bin/admin/scripts/pgsql. We do have a proper design for this
> feature: http://kea.isc.org/wiki/HostReservationDesign. It would be
> highly appreciated if the patch also covered unit-tests :)
> 
> > MySQL, but I'd prefer to avoid that since I have a working setup and
> > also I would have to figure out which of the 32 forks of MySQL to
> > use....) ;)  
> Acknowledged. There are users who prefer MySQL, so we decided to keep
> supporting both. Also, as part of the 1.0 post mortem discussions, we
> decided to aim for MySQL/PostgreSQL parity. I admit that we neglected
> its development a bit, but we'll get it back in shape soon.
> 
> We're still in the 1.1 planning, but it's very likely that finishing
> IPv6 support in MySQL and adding reservations is Postgres will be part
> of the next release after 1.0.

I spent a short time looking at the schema for the PostgreSQL and I
have a few questions before I go further.

----

First, a question for both MySQL and PostgreSQL. There are certain
values which are basically enumerated data types: 

- lease6_types
- lease_hwaddr_source
- lease_state

I guess it was an intentional choice not to use enum types here, but
both MySQL and PostgreSQL support enum types, so I'm not sure of the
motivation. I guess the reason is that the Kea C++ code uses constants
defined elsewhere to match these (or perhaps these come from the RFC
documents)? It's not a big deal, but in the interests of consistency I
would generally put a foreign key constraint there.

----

Second, there is support in the MySQL schema that is missing in the
PostgreSQL schema beyond the host reservations. For example, the MySQL
schema includes information about hardware/MAC address in the lease6
database. This was added to the MySQL schema in 2.0 of the schema.

Does it make sense to try to get the PostgreSQL schema revised to
include all of the missing bits from 2.0 of the schema before moving on
to 3.0?

Speaking of schema versions... I assume that the schemas use something
like semantic versioning? So 3.0 to 3.1 is a compatible change that
adds new features but 3.1 to 4.0 is incompatible?

http://semver.org/ 

What is the idea when it comes to supporting multiple databases? I ask
because I assume that schemas cannot go backwards, so presumably the
changes to add hardware/MAC address to lease6 would change the
PostgreSQL from 3.0 to 4.0, even though there would be no change at all
for the MySQL schema.

I guess for simplicity it makes sense to have a single schema version,
but it also means that administrators may be scared of updates because
a 3.0 to 4.0 seems more serious than a 3.0 to 3.1... even if it does
not affect their install at all.

----

Speaking of updating schemas... :)

In PostgreSQL it is possible to use support for the poorly-named
SCHEMA abstraction along with triggers to perform transparent, in-place
modification of schemas.

Basically you can do something like:

1. start with a schema named something like kea_db_3.0 (default for the
   kea user)
2. when a new version is created, make kea_db_4.0 along with a set of
   triggers to keep data in sync between the two versions
3. migrate the data to the new version
4. change the default of the kea user to go to kea_db_4.0
5. sometime when everything has been working for a while drop the
   kea_db_3.0 schema

http://www.postgresql.org/docs/9.4/static/ddl-schemas.html

I don't know if there is any support for such things in MySQL
databases, but there are a lot of advantages to this approach, even
though it involves more developer work.

It won't be top of my list, but this may be something I want to explore
as well.

Cheers,

--
Shane