dhcpdb_create.cql 7.5 KB
Newer Older
1
-- Copyright (C) 2015-2017 Deutsche Telekom AG.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- Author: Razvan Becheriu <razvan.becheriu@qualitance.com>

-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at

--           http://www.apache.org/licenses/LICENSE-2.0

-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

Tomek Mrugalski's avatar
Tomek Mrugalski committed
17
-- This is the Kea schema specification for Cassandra CQL.
18
19

-- The schema is reasonably portable (with the exception of the engine
Tomek Mrugalski's avatar
Tomek Mrugalski committed
20
-- specification, which is Cassandra CQL-specific).  Minor changes might be needed for
21
22
23
24
25
26
-- other databases.

-- To create the schema, either type the command:

-- cqlsh -u <user> -p <password> -k <database> -f dhcpdb_create.cql

Tomek Mrugalski's avatar
Tomek Mrugalski committed
27
-- ... at the command prompt, or log in to the CQL database and at the "cqlsh>"
28
29
30
31
-- prompt, issue the command:

-- SOURCE dhcpdb_create.cql

Razvan Becheriu's avatar
Razvan Becheriu committed
32
-- This script is also called from kea-admin, see kea-admin lease-init cql
33
34
35
36
37
38
39
40
41
42
43

-- 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 performance is not an issue.

-- This line starts database initialization to 1.0.

-- Holds the IPv4 leases.
Andrei Pavel's avatar
Andrei Pavel committed
44
45
46
-- -----------------------------------------------------
-- Table `lease4`
-- -----------------------------------------------------
47
CREATE TABLE IF NOT EXISTS lease4 (
48
49
50
51
52
53
54
55
56
57
    address int,
    hwaddr blob,
    client_id blob,
    valid_lifetime bigint,
    expire bigint,
    subnet_id int,
    fqdn_fwd boolean,
    fqdn_rev boolean,
    hostname varchar,
    state int,
58
    PRIMARY KEY ((address))
59
60
61
);

-- Create search indexes for lease4 table
62
63
64
65
66
CREATE INDEX IF NOT EXISTS lease4index1 ON lease4 (client_id);
CREATE INDEX IF NOT EXISTS lease4index2 ON lease4 (subnet_id);
CREATE INDEX IF NOT EXISTS lease4index3 ON lease4 (hwaddr);
CREATE INDEX IF NOT EXISTS lease4index4 ON lease4 (expire);
CREATE INDEX IF NOT EXISTS lease4index5 ON lease4 (state);
67
68
69
70

-- 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).
Andrei Pavel's avatar
Andrei Pavel committed
71
72
73
-- -----------------------------------------------------
-- Table `lease6`
-- -----------------------------------------------------
74
CREATE TABLE IF NOT EXISTS lease6 (
75
76
77
78
79
    address varchar,
    valid_lifetime bigint,
    expire bigint,
    subnet_id int,
    pref_lifetime bigint,
80
    duid blob,
81
    iaid int,
82
    lease_type int,
83
84
85
86
87
88
89
90
    prefix_len int,
    fqdn_fwd boolean,
    fqdn_rev boolean,
    hostname varchar,
    hwaddr blob,
    hwtype int,
    hwaddr_source int,
    state int,
91
    PRIMARY KEY ((address))
92
93
94
);

-- Create search indexes for lease6 table
95
96
97
98
99
100
CREATE INDEX IF NOT EXISTS lease6index1 ON lease6 (duid);
CREATE INDEX IF NOT EXISTS lease6index2 ON lease6 (iaid);
CREATE INDEX IF NOT EXISTS lease6index3 ON lease6 (lease_type);
CREATE INDEX IF NOT EXISTS lease6index4 ON lease6 (subnet_id);
CREATE INDEX IF NOT EXISTS lease6index5 ON lease6 (expire);
CREATE INDEX IF NOT EXISTS lease6index6 ON lease6 (state);
101
102
103
104
105
106

-- ... 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
-- 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)
Andrei Pavel's avatar
Andrei Pavel committed
107
108
109
-- -----------------------------------------------------
-- Table `lease6_types`
-- -----------------------------------------------------
110
CREATE TABLE IF NOT EXISTS lease6_types (
111
112
    lease_type int,                             -- Lease type code.
    name varchar,                               -- Name of the lease type
113
    PRIMARY KEY ((lease_type))
114
115
116
117
118
119
120
121
122
123
124
);
INSERT INTO lease6_types (lease_type, name) VALUES (0, 'IA_NA');   -- Non-temporary v6 addresses
INSERT INTO lease6_types (lease_type, name) VALUES (1, 'IA_TA');   -- Temporary v6 addresses
INSERT INTO lease6_types (lease_type, name) VALUES (2, 'IA_PD');   -- Prefix delegations

-- 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.
Andrei Pavel's avatar
Andrei Pavel committed
125
126
127
-- -----------------------------------------------------
-- Table `lease_hwaddr_source`
-- -----------------------------------------------------
128
CREATE TABLE IF NOT EXISTS lease_hwaddr_source (
129
130
    hwaddr_source int,
    name varchar,
131
    PRIMARY KEY ((hwaddr_source))
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
);

-- Hardware address obtained from raw sockets
INSERT INTO lease_hwaddr_source (hwaddr_source, name) VALUES (1, 'HWADDR_SOURCE_RAW');

-- Hardware address converted from IPv6 link-local address with EUI-64
INSERT INTO lease_hwaddr_source (hwaddr_source, name) VALUES (2, 'HWADDR_SOURCE_IPV6_LINK_LOCAL');

-- Hardware address extracted from client-id (duid)
INSERT INTO lease_hwaddr_source (hwaddr_source, name) VALUES (4, 'HWADDR_SOURCE_DUID');

-- Hardware address extracted from client address relay option (RFC6939)
INSERT INTO lease_hwaddr_source (hwaddr_source, name) VALUES (8, 'HWADDR_SOURCE_CLIENT_ADDR_RELAY_OPTION');

-- Hardware address extracted from remote-id option (RFC4649)
INSERT INTO lease_hwaddr_source (hwaddr_source, name) VALUES (16, 'HWADDR_SOURCE_REMOTE_ID');

-- Hardware address extracted from subscriber-id option (RFC4580)
INSERT INTO lease_hwaddr_source (hwaddr_source, name) VALUES (32, 'HWADDR_SOURCE_SUBSCRIBER_ID');

-- Hardware address extracted from docsis options
INSERT INTO lease_hwaddr_source (hwaddr_source, name) VALUES (64, 'HWADDR_SOURCE_DOCSIS_CMTS');

-- 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.
Andrei Pavel's avatar
Andrei Pavel committed
158
159
160
-- -----------------------------------------------------
-- Table `lease_state`
-- -----------------------------------------------------
161
CREATE TABLE IF NOT EXISTS lease_state (
162
163
    state int,
    name varchar,
164
    PRIMARY KEY ((state))
165
166
167
168
169
170
171
172
173
174
175
);

-- Insert currently defined state names.
INSERT INTO lease_state (state, name) VALUES (0, 'default');
INSERT INTO lease_state (state, name) VALUES (1, 'declined');
INSERT INTO lease_state (state, name) VALUES (2, 'expired-reclaimed');

-- Finally, the version of the schema.  We start at 1.0 during development.
-- 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
-- first column is called "version" and not "major".
Andrei Pavel's avatar
Andrei Pavel committed
176
177
178
-- -----------------------------------------------------
-- Table `schema_version`
-- -----------------------------------------------------
179
CREATE TABLE IF NOT EXISTS schema_version (
180
181
    version int,
    minor int,
182
    PRIMARY KEY ((version))
183
);
Andrei Pavel's avatar
Andrei Pavel committed
184

185
INSERT INTO schema_version (version, minor) VALUES (1, 0);
Andrei Pavel's avatar
Andrei Pavel committed
186

187
-- This line concludes database initialization to version 1.0.