BIND 10 #2142: Design and Creation Script for SQL Lease Database

[BIND 10 Development]

#2142: Design and Creation Script for SQL Lease Database
-------------------------------------+-------------------------------------
                   Reporter:         |                 Owner:  tomek
  stephen                            |                Status:  reviewing
                       Type:  task   |             Milestone:  Sprint-
                   Priority:         |  DHCP-20121018
  medium                             |            Resolution:
                  Component:  dhcp   |             Sensitive:  0
                   Keywords:         |           Sub-Project:  DHCP
            Defect Severity:  N/A    |  Estimated Difficulty:  0
Feature Depending on Ticket:         |           Total Hours:  0
        Add Hours to Ticket:  0      |
                  Internal?:  0      |
-------------------------------------+-------------------------------------
Changes (by stephen):

 * owner:  stephen => tomek


Comment:

 > 1. There should be a preferred-lifetime field in lease6.
 Added.

 > 2. Typo "leasei6" before lease6_types table.
 Corrected.

 > 3. There should be a comment about lack of indices. (That this is a
 design choice, because DHCP database is very dynamic and would require
 very frequent index regeneration, so the overhead is likely to outweigh
 read performance boosts that index provides).
 There is, at the end of the file.

 In practice, the need for additional indexes will be determined by access
 pattern.  By default an index is created for the primary key (lookup by IP
 address).  An index on expiration time is probably not needed unless we
 are scanning for deleted leases very frequently (although when we do scan,
 all records will be read).

 Indexes on client ID or hardware address may be needed if the lookup on
 these is done frequently or if the table is millions of rows long.

 > 4. There should be a comment about portability (this was explicitly
 requested). It should be pointed that the schema can be used as is, with
 the exception of engine specification, which is likely MySQL specific.
 Added.

 > 5. InnoDB in its default configuration is fully synchronous, which is
 safe, but yields very low performance. There should at least be a
 discussion about
 I don't think it is needed.  The MySQL database layer constructor turns
 off autocommit which makes all operations transaction-based.

 > We should consider adding "CREATE database Kea;" at the beginning.
 I'd rather not do this - there is always the possibility that the physical
 database is shared between two independent servers: in this case, it could
 be that two databases are used.

 Also, creating the database required logging in as a privileged user,
 whereas an unprivileged user with access to a database can always create
 extra tables.

 > 7. The database installation and setup must be documented somehow. It
 doesn't have to be done as part of this ticket. Probably MySQL backend
 ticket will be more appropriate for that, but this script should at least
 have 2 line instruction, how to use that schema. Something along the
 lines:
 Done. The documentation will be added to the backend ticket.

-- 
Ticket URL: <http://bind10.isc.org/ticket/2142#comment:5>
BIND 10 Development <http://bind10.isc.org>
BIND 10 Development