dhcpdb_create.mysql 20.5 KB
Newer Older
1
# Copyright (C) 2012-2017 Internet Systems Consortium, Inc. ("ISC")
2
#
3
4
5
# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
6

7
# This is the Kea schema specification for MySQL.
8
9
10
11
12
13
14
15
16
#
# The schema is reasonably portable (with the exception of the engine
# specification, which is MySQL-specific).  Minor changes might be needed for
# other databases.

# To create the schema, either type the command:
#
# mysql -u <user> -p <password> <database> < dhcpdb_create.mysql
#
Yusef Shaban's avatar
Yusef Shaban committed
17
# ... at the command prompt, or log in to the MySQL database and at the 'mysql>'
18
19
20
# prompt, issue the command:
#
# source dhcpdb_create.mysql
21
#
22
# This script is also called from kea-admin, see kea-admin lease-init mysql
23
24
25
26
27
28
29
#
# Over time, Kea database schema will evolve. Each version is marked with
# major.minor version. This file is organized sequentially, i.e. database
# is initialized to 1.0, then upgraded to 2.0 etc. This may be somewhat
# sub-optimal, but it ensues consistency with upgrade scripts. (It is much
# easier to maintain init and upgrade scripts if they look the same).
# Since initialization is done only once, it's perfromance is not an issue.
30

31
# This line starts database initialization to 1.0.
32

33
34
# Holds the IPv4 leases.
CREATE TABLE lease4 (
35
36
37
    address INT UNSIGNED PRIMARY KEY NOT NULL,  # IPv4 address
    hwaddr VARBINARY(20),                       # Hardware address
    client_id VARBINARY(128),                   # Client ID
38
    valid_lifetime INT UNSIGNED,                # Length of the lease (seconds)
39
    expire TIMESTAMP,                           # Expiration time of the lease
40
41
42
43
    subnet_id INT UNSIGNED,                     # Subnet identification
    fqdn_fwd BOOL,                              # Has forward DNS update been performed by a server
    fqdn_rev BOOL,                              # Has reverse DNS update been performed by a server
    hostname VARCHAR(255)                       # The FQDN of the client
44
45
    ) ENGINE = INNODB;

46

47
# Create search indexes for lease4 table
48
49
50
51
52
53
# index by hwaddr and subnet_id
CREATE INDEX lease4_by_hwaddr_subnet_id ON lease4 (hwaddr, subnet_id);

# index by client_id and subnet_id
CREATE INDEX lease4_by_client_id_subnet_id ON lease4 (client_id, subnet_id);

54
55
56
# Holds the IPv6 leases.
# N.B. The use of a VARCHAR for the address is temporary for development:
# it will eventually be replaced by BINARY(16).
57
CREATE TABLE lease6 (
58
    address VARCHAR(39) PRIMARY KEY NOT NULL,   # IPv6 address
59
    duid VARBINARY(128),                        # DUID
60
    valid_lifetime INT UNSIGNED,                # Length of the lease (seconds)
61
62
63
64
65
66
    expire TIMESTAMP,                           # Expiration time of the lease
    subnet_id INT UNSIGNED,                     # Subnet identification
    pref_lifetime INT UNSIGNED,                 # Preferred lifetime
    lease_type TINYINT,                         # Lease type (see lease6_types
                                                #    table for possible values)
    iaid INT UNSIGNED,                          # See Section 10 of RFC 3315
67
68
69
70
71
    prefix_len TINYINT UNSIGNED,                # For IA_PD only
    fqdn_fwd BOOL,                              # Has forward DNS update been performed by a server
    fqdn_rev BOOL,                              # Has reverse DNS update been performed by a server
    hostname VARCHAR(255)                       # The FQDN of the client

72
73
    ) ENGINE = INNODB;

74
75
# Create search indexes for lease4 table
# index by iaid, subnet_id, and duid
76
77
CREATE INDEX lease6_by_iaid_subnet_id_duid ON lease6 (iaid, subnet_id, duid);

78
79
# ... and a definition of lease6 types.  This table is a convenience for
# users of the database - if they want to view the lease table and use the
80
81
82
# type names, they can join this table with the lease6 table.
# Make sure those values match Lease6::LeaseType enum (see src/bin/dhcpsrv/
# lease_mgr.h)
83
CREATE TABLE lease6_types (
84
85
    lease_type TINYINT PRIMARY KEY NOT NULL,    # Lease type code.
    name VARCHAR(5)                             # Name of the lease type
86
87
    ) ENGINE = INNODB;

88
START TRANSACTION;
Yusef Shaban's avatar
Yusef Shaban committed
89
90
91
INSERT INTO lease6_types VALUES (0, 'IA_NA');   # Non-temporary v6 addresses
INSERT INTO lease6_types VALUES (1, 'IA_TA');   # Temporary v6 addresses
INSERT INTO lease6_types VALUES (2, 'IA_PD');   # Prefix delegations
92
COMMIT;
93

Tomek Mrugalski's avatar
Tomek Mrugalski committed
94
# Finally, the version of the schema.  We start at 1.0 during development.
95
96
# This table is only modified during schema upgrades.  For historical reasons
# (related to the names of the columns in the BIND 10 DNS database file), the
Yusef Shaban's avatar
Yusef Shaban committed
97
# first column is called 'version' and not 'major'.
98
CREATE TABLE schema_version (
99
100
    version INT PRIMARY KEY NOT NULL,       # Major version number
    minor INT                               # Minor version number
101
    ) ENGINE = INNODB;
102
START TRANSACTION;
103
INSERT INTO schema_version VALUES (1, 0);
104
105
COMMIT;

106
107
108
109
110
111
112
113
114
# This line concludes database initalization to version 1.0.

# This line starts database upgrade to version 2.0.
ALTER TABLE lease6
    ADD COLUMN hwaddr varbinary(20), # Hardware/MAC address, typically only 6
                                     # bytes is used, but some hardware (e.g.
                                     # Infiniband) use up to 20.
    ADD COLUMN hwtype smallint unsigned, # hardware type (16 bits)
    ADD COLUMN hwaddr_source int unsigned; # Hardware source. See description
Tomek Mrugalski's avatar
Tomek Mrugalski committed
115
                                     # of lease_hwaddr_source below.
116
117
118
119
120
121
122

# Kea keeps track of the hardware/MAC address source, i.e. how the address
# was obtained. Depending on the technique and your network topology, it may
# be more or less trustworthy. This table is a convenience for
# users of the database - if they want to view the lease table and use the
# type names, they can join this table with the lease6 table. For details,
# see constants defined in src/lib/dhcp/dhcp/pkt.h for detailed explanation.
Tomek Mrugalski's avatar
Tomek Mrugalski committed
123
CREATE TABLE lease_hwaddr_source (
124
125
    hwaddr_source INT PRIMARY KEY NOT NULL,
    name VARCHAR(40)
126
) ENGINE = INNODB;
127
128

# Hardware address obtained from raw sockets
Yusef Shaban's avatar
Yusef Shaban committed
129
INSERT INTO lease_hwaddr_source VALUES (1, 'HWADDR_SOURCE_RAW');
130
131

# Hardware address converted from IPv6 link-local address with EUI-64
Yusef Shaban's avatar
Yusef Shaban committed
132
INSERT INTO lease_hwaddr_source VALUES (2, 'HWADDR_SOURCE_IPV6_LINK_LOCAL');
133
134

# Hardware address extracted from client-id (duid)
Yusef Shaban's avatar
Yusef Shaban committed
135
INSERT INTO lease_hwaddr_source VALUES (4, 'HWADDR_SOURCE_DUID');
136
137

# Hardware address extracted from client address relay option (RFC6939)
Yusef Shaban's avatar
Yusef Shaban committed
138
INSERT INTO lease_hwaddr_source VALUES (8, 'HWADDR_SOURCE_CLIENT_ADDR_RELAY_OPTION');
139
140

# Hardware address extracted from remote-id option (RFC4649)
Yusef Shaban's avatar
Yusef Shaban committed
141
INSERT INTO lease_hwaddr_source VALUES (16, 'HWADDR_SOURCE_REMOTE_ID');
142
143

# Hardware address extracted from subscriber-id option (RFC4580)
Yusef Shaban's avatar
Yusef Shaban committed
144
INSERT INTO lease_hwaddr_source VALUES (32, 'HWADDR_SOURCE_SUBSCRIBER_ID');
145
146

# Hardware address extracted from docsis options
Yusef Shaban's avatar
Yusef Shaban committed
147
INSERT INTO lease_hwaddr_source VALUES (64, 'HWADDR_SOURCE_DOCSIS');
148

Yusef Shaban's avatar
Yusef Shaban committed
149
UPDATE schema_version SET version='2', minor='0';
150
151
152

# This line concludes database upgrade to version 2.0.

153
154
155
# This line starts database upgrade to version 3.0.
# Upgrade extending MySQL schema with the ability to store hosts.

156
157
158
159
160
161
162
163
164
165
166
167
168
169
CREATE TABLE IF NOT EXISTS hosts (
    host_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    dhcp_identifier VARBINARY(128) NOT NULL,
    dhcp_identifier_type TINYINT NOT NULL,
    dhcp4_subnet_id INT UNSIGNED NULL,
    dhcp6_subnet_id INT UNSIGNED NULL,
    ipv4_address INT UNSIGNED NULL,
    hostname VARCHAR(255) NULL,
    dhcp4_client_classes VARCHAR(255) NULL,
    dhcp6_client_classes VARCHAR(255) NULL,
    PRIMARY KEY (host_id),
    INDEX key_dhcp4_identifier_subnet_id (dhcp_identifier ASC , dhcp_identifier_type ASC),
    INDEX key_dhcp6_identifier_subnet_id (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp6_subnet_id ASC)
)  ENGINE=INNODB;
170
-- -----------------------------------------------------
171
-- Table `ipv6_reservations`
172
-- -----------------------------------------------------
173
174
175
176
177
178
179
180
181
182
183
184
185
CREATE TABLE IF NOT EXISTS ipv6_reservations (
    reservation_id INT NOT NULL AUTO_INCREMENT,
    address VARCHAR(39) NOT NULL,
    prefix_len TINYINT(3) UNSIGNED NOT NULL DEFAULT 128,
    type TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
    dhcp6_iaid INT UNSIGNED NULL,
    host_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (reservation_id),
    INDEX fk_ipv6_reservations_host_idx (host_id ASC),
    CONSTRAINT fk_ipv6_reservations_Host FOREIGN KEY (host_id)
        REFERENCES hosts (host_id)
        ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=INNODB;
186
-- -----------------------------------------------------
187
-- Table `dhcp4_options`
188
-- -----------------------------------------------------
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
CREATE TABLE IF NOT EXISTS dhcp4_options (
    option_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code TINYINT UNSIGNED NOT NULL,
    value BLOB NULL,
    formatted_value TEXT NULL,
    space VARCHAR(128) NULL,
    persistent TINYINT(1) NOT NULL DEFAULT 0,
    dhcp_client_class VARCHAR(128) NULL,
    dhcp4_subnet_id INT NULL,
    host_id INT UNSIGNED NULL,
    PRIMARY KEY (option_id),
    UNIQUE INDEX option_id_UNIQUE (option_id ASC),
    INDEX fk_options_host1_idx (host_id ASC),
    CONSTRAINT fk_options_host1 FOREIGN KEY (host_id)
        REFERENCES hosts (host_id)
        ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=INNODB;
206
-- -----------------------------------------------------
207
-- Table `dhcp6_options`
208
-- -----------------------------------------------------
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
CREATE TABLE IF NOT EXISTS dhcp6_options (
    option_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code INT UNSIGNED NOT NULL,
    value BLOB NULL,
    formatted_value TEXT NULL,
    space VARCHAR(128) NULL,
    persistent TINYINT(1) NOT NULL DEFAULT 0,
    dhcp_client_class VARCHAR(128) NULL,
    dhcp6_subnet_id INT NULL,
    host_id INT UNSIGNED NULL,
    PRIMARY KEY (option_id),
    UNIQUE INDEX option_id_UNIQUE (option_id ASC),
    INDEX fk_options_host1_idx (host_id ASC),
    CONSTRAINT fk_options_host10 FOREIGN KEY (host_id)
        REFERENCES hosts (host_id)
        ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=INNODB;
226
227

DELIMITER $$
228
CREATE TRIGGER host_BDEL BEFORE DELETE ON hosts FOR EACH ROW
229
230
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN
231
DELETE FROM ipv6_reservations WHERE ipv6_reservations.host_id = OLD.host_id;
232
233
234
235
END
$$
DELIMITER ;

236
237
UPDATE schema_version
SET version = '3', minor = '0';
238
239
# This line concludes database upgrade to version 3.0.

240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
# This line starts database upgrade to version 4.0.
# Upgrade extending MySQL schema with the state columns for lease tables.

# Add state column to the lease4 table.
ALTER TABLE lease4
    ADD COLUMN state INT UNSIGNED DEFAULT 0;

# Add state column to the lease6 table.
ALTER TABLE lease6
    ADD COLUMN state INT UNSIGNED DEFAULT 0;

# Create indexes for querying leases in a given state and segregated
# by the expiration time. One of the applications is to retrieve all
# expired leases. However, these indexes can be also used to retrieve
# leases in a given state regardless of the expiration time.
255
256
CREATE INDEX lease4_by_state_expire ON lease4 (state ASC, expire ASC);
CREATE INDEX lease6_by_state_expire ON lease6 (state ASC, expire ASC);
257

258
259
260
261
# Create table holding mapping of the lease states to their names.
# This is not used in queries from the DHCP server but rather in
# direct queries from the lease database management tools.
CREATE TABLE IF NOT EXISTS lease_state (
262
  state INT UNSIGNED PRIMARY KEY NOT NULL,
263
264
265
  name VARCHAR(64) NOT NULL
) ENGINE=INNODB;

266
# Insert currently defined state names.
Yusef Shaban's avatar
Yusef Shaban committed
267
268
269
INSERT INTO lease_state VALUES (0, 'default');
INSERT INTO lease_state VALUES (1, 'declined');
INSERT INTO lease_state VALUES (2, 'expired-reclaimed');
270

271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
# Add a constraint that any state value added to the lease4 must
# map to a value in the lease_state table.
ALTER TABLE lease4
    ADD CONSTRAINT fk_lease4_state FOREIGN KEY (state)
    REFERENCES lease_state (state);

# Add a constraint that any state value added to the lease6 must
# map to a value in the lease_state table.
ALTER TABLE lease6
    ADD CONSTRAINT fk_lease6_state FOREIGN KEY (state)
    REFERENCES lease_state (state);

# Add a constraint that lease type in the lease6 table must map
# to a lease type defined in the lease6_types table.
ALTER TABLE lease6
    ADD CONSTRAINT fk_lease6_type FOREIGN KEY (lease_type)
    REFERENCES lease6_types (lease_type);

289
290
291
292
293
294
295
# Modify the name of one of the HW address sources, and add a new one.
UPDATE lease_hwaddr_source
    SET name = 'HWADDR_SOURCE_DOCSIS_CMTS'
    WHERE hwaddr_source = 64;

INSERT INTO lease_hwaddr_source VALUES (128, 'HWADDR_SOURCE_DOCSIS_MODEM');

296
297
# Add UNSIGNED to match with the lease6.
ALTER TABLE lease_hwaddr_source
298
    MODIFY COLUMN hwaddr_source INT UNSIGNED NOT NULL;
299
300
301
302
303
304

# Add a constraint that non-null hwaddr_source in the lease6 table
# must map to an entry in the lease_hwaddr_source.
ALTER TABLE lease6
    ADD CONSTRAINT fk_lease6_hwaddr_source FOREIGN KEY (hwaddr_source)
    REFERENCES lease_hwaddr_source (hwaddr_source);
305

306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
# FUNCTION that returns a result set containing the column names for lease4 dumps
DROP PROCEDURE IF EXISTS lease4DumpHeader;
DELIMITER $$
CREATE PROCEDURE lease4DumpHeader()
BEGIN
SELECT 'address,hwaddr,client_id,valid_lifetime,expire,subnet_id,fqdn_fwd,fqdn_rev,hostname,state';
END  $$
DELIMITER ;

# FUNCTION that returns a result set containing the data for lease4 dumps
DROP PROCEDURE IF EXISTS lease4DumpData;
DELIMITER $$
CREATE PROCEDURE lease4DumpData()
BEGIN
SELECT
    INET_NTOA(l.address),
    IFNULL(HEX(l.hwaddr), ''),
    IFNULL(HEX(l.client_id), ''),
    l.valid_lifetime,
    l.expire,
    l.subnet_id,
    l.fqdn_fwd,
    l.fqdn_rev,
    l.hostname,
    s.name
331
FROM
332
    lease4 l
333
334
    LEFT OUTER JOIN lease_state s on (l.state = s.state)
ORDER BY l.address;
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
END $$
DELIMITER ;

# FUNCTION that returns a result set containing the column names for lease6 dumps
DROP PROCEDURE IF EXISTS lease6DumpHeader;
DELIMITER $$
CREATE PROCEDURE lease6DumpHeader()
BEGIN
SELECT 'address,duid,valid_lifetime,expire,subnet_id,pref_lifetime,lease_type,iaid,prefix_len,fqdn_fwd,fqdn_rev,hostname,hwaddr,hwtype,hwaddr_source,state';
END  $$
DELIMITER ;

# FUNCTION that returns a result set containing the data for lease6 dumps
DROP PROCEDURE IF EXISTS lease6DumpData;
DELIMITER $$
CREATE PROCEDURE lease6DumpData()
BEGIN
SELECT
    l.address,
    IFNULL(HEX(l.duid), ''),
    l.valid_lifetime,
    l.expire,
    l.subnet_id,
    l.pref_lifetime,
    IFNULL(t.name, ''),
    l.iaid,
    l.prefix_len,
    l.fqdn_fwd,
    l.fqdn_rev,
    l.hostname,
    IFNULL(HEX(l.hwaddr), ''),
    IFNULL(l.hwtype, ''),
367
    IFNULL(h.name, ''),
368
369
370
    IFNULL(s.name, '')
FROM lease6 l
    left outer join lease6_types t on (l.lease_type = t.lease_type)
371
    left outer join lease_state s on (l.state = s.state)
372
373
    left outer join lease_hwaddr_source h on (l.hwaddr_source = h.hwaddr_source)
ORDER BY l.address;
374
375
376
377
END $$
DELIMITER ;

# Update the schema version number
378
379
UPDATE schema_version
SET version = '4', minor = '0';
380

381
382
# This line concludes database upgrade to version 4.0.

383
384
# In the event hardware address cannot be determined, we need to satisfy
# foreign key constraint between lease6 and lease_hardware_source
Yusef Shaban's avatar
Yusef Shaban committed
385
INSERT INTO lease_hwaddr_source VALUES (0, 'HWADDR_SOURCE_UNKNOWN');
386
387
388
389
390
391
392

# Update the schema version number
UPDATE schema_version
SET version = '4', minor = '1';

# This line concludes database upgrade to version 4.1.

393
394
395
396
397
398
399
400
401
402
403
# Update index used for searching DHCPv4 reservations by identifier and subnet id.
# This index is now unique (to prevent duplicates) and includes DHCPv4 subnet
# identifier.
DROP INDEX key_dhcp4_identifier_subnet_id ON hosts;
CREATE UNIQUE INDEX key_dhcp4_identifier_subnet_id ON hosts (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp4_subnet_id ASC);

# Update index used for searching DHCPv6 reservations by identifier and subnet id.
# This index is now unique to prevent duplicates.
DROP INDEX key_dhcp6_identifier_subnet_id ON hosts;
CREATE UNIQUE INDEX key_dhcp6_identifier_subnet_id ON hosts (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp6_subnet_id ASC);

404
# Create index to search for reservations using IP address and subnet id.
Andrei Pavel's avatar
Andrei Pavel committed
405
# This unique index guarantees that there is only one occurrence of the
406
407
408
409
410
# particular IPv4 address for a given subnet.
CREATE UNIQUE INDEX key_dhcp4_ipv4_address_subnet_id ON hosts (ipv4_address ASC , dhcp4_subnet_id ASC);

# Create index to search for reservations using address/prefix and prefix
# length.
411
CREATE UNIQUE INDEX key_dhcp6_address_prefix_len ON ipv6_reservations (address ASC , prefix_len ASC);
412

413
414
415
416
417
418
419
420
421
422
# Create a table mapping host identifiers to their names. Values in this
# table are used as a foreign key in hosts table to guarantee that only
# identifiers present in host_identifier_type table are used in hosts
# table.
CREATE TABLE IF NOT EXISTS host_identifier_type (
    type TINYINT PRIMARY KEY NOT NULL,   # Lease type code.
    name VARCHAR(32)                     # Name of the lease type
) ENGINE = INNODB;

START TRANSACTION;
423
424
425
INSERT INTO host_identifier_type VALUES (0, 'hw-address');
INSERT INTO host_identifier_type VALUES (1, 'duid');
INSERT INTO host_identifier_type VALUES (2, 'circuit-id');
426
427
428
429
430
431
432
433
COMMIT;

# Add a constraint that any identifier type value added to the hosts
# must map to a value in the host_identifier_type table.
ALTER TABLE hosts
    ADD CONSTRAINT fk_host_identifier_type FOREIGN KEY (dhcp_identifier_type)
    REFERENCES host_identifier_type (type);

434
435
436
437
438
439
440
# Store DHCPv6 option code as 16-bit unsigned integer.
ALTER TABLE dhcp6_options MODIFY code SMALLINT UNSIGNED NOT NULL;

# Subnet identifier is unsigned.
ALTER TABLE dhcp4_options MODIFY dhcp4_subnet_id INT UNSIGNED NULL;
ALTER TABLE dhcp6_options MODIFY dhcp6_subnet_id INT UNSIGNED NULL;

441
442
443
444
445
446
447
448
449
# Scopes associate DHCP options stored in dhcp4_options and
# dhcp6_options tables with hosts, subnets, classes or indicate
# that they are global options.
CREATE TABLE IF NOT EXISTS dhcp_option_scope (
    scope_id TINYINT UNSIGNED PRIMARY KEY NOT NULL,
    scope_name VARCHAR(32)
) ENGINE = INNODB;

START TRANSACTION;
Yusef Shaban's avatar
Yusef Shaban committed
450
451
452
453
INSERT INTO dhcp_option_scope VALUES (0, 'global');
INSERT INTO dhcp_option_scope VALUES (1, 'subnet');
INSERT INTO dhcp_option_scope VALUES (2, 'client-class');
INSERT INTO dhcp_option_scope VALUES (3, 'host');
454
455
456
457
458
459
460
461
462
463
464
465
466
467
COMMIT;

# Add scopes into table holding DHCPv4 options
ALTER TABLE dhcp4_options ADD COLUMN scope_id TINYINT UNSIGNED NOT NULL;
ALTER TABLE dhcp4_options
    ADD CONSTRAINT fk_dhcp4_option_scope FOREIGN KEY (scope_id)
    REFERENCES dhcp_option_scope (scope_id);

# Add scopes into table holding DHCPv6 options
ALTER TABLE dhcp6_options ADD COLUMN scope_id TINYINT UNSIGNED NOT NULL;
ALTER TABLE dhcp6_options
    ADD CONSTRAINT fk_dhcp6_option_scope FOREIGN KEY (scope_id)
    REFERENCES dhcp_option_scope (scope_id);

468
469
470
471
# Add UNSIGNED to reservation_id
ALTER TABLE ipv6_reservations
    MODIFY reservation_id INT UNSIGNED NOT NULL AUTO_INCREMENT;

472
473
474
475
476
# Add columns holding reservations for siaddr, sname and file fields
# carried within DHCPv4 message.
ALTER TABLE hosts ADD COLUMN dhcp4_next_server INT UNSIGNED NULL;
ALTER TABLE hosts ADD COLUMN dhcp4_server_hostname VARCHAR(64) NULL;
ALTER TABLE hosts ADD COLUMN dhcp4_boot_file_name VARCHAR(128) NULL;
477

478
479
# Update the schema version number
UPDATE schema_version
480
481
SET version = '5', minor = '0';
# This line concludes database upgrade to version 5.0.
482

483
# Add missing 'client-id' and new 'flex-id' host identifier types.
484
INSERT INTO host_identifier_type VALUES (3, 'client-id');
485
INSERT INTO host_identifier_type VALUES (4, 'flex-id');
486
487
488
489
490
491

# Update the schema version number
UPDATE schema_version
SET version = '5', minor = '1';
# This line concludes database upgrade to version 5.1.

492
493
494
495
# Notes:
#
# Indexes
# =======
496
497
498
499
500
501
502
503
504
505
506
507
# It is likely that additional indexes will be needed.  However, the
# increase in lookup performance from these will come at the expense
# of a decrease in performance during insert operations due to the need
# to update the indexes.  For this reason, the need for additional indexes
# will be determined by experiment during performance tests.
#
# The most likely additional indexes will cover the following columns:
#
# hwaddr and client_id
# For lease stability: if a client requests a new lease, try to find an
# existing or recently expired lease for it so that it can keep using the
# same IP address.
508
509
510
511
512
513
514
515
#
# Field Sizes
# ===========
# If any of the VARxxx field sizes are altered, the lengths in the MySQL
# backend source file (mysql_lease_mgr.cc) must be correspondingly changed.
#
# Portability
# ===========
Yusef Shaban's avatar
Yusef Shaban committed
516
# The 'ENGINE = INNODB' on some tables is not portable to another database
517
518
519
520
521
# and will need to be removed.
#
# Some columns contain binary data so are stored as VARBINARY instead of
# VARCHAR.  This may be non-portable between databases: in this case, the
# definition should be changed to VARCHAR.