1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
/*
* IP Engineering RADIUS Servers
*
* ippool-schema.sql - builds database schema for RADIUS IP Pools
*
*/
SET ROLE radius_ippool;
CREATE TABLE IF NOT EXISTS pool
(
id SERIAL PRIMARY KEY,
name VARCHAR(32) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS region
(
id SERIAL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
description VARCHAR(128)
);
CREATE TABLE IF NOT EXISTS nas
(
id SERIAL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
regionId INTEGER NOT NULL,
FOREIGN KEY (regionId) REFERENCES region (id)
);
CREATE TABLE IF NOT EXISTS block
(
id SERIAL PRIMARY KEY,
poolId INTEGER NOT NULL,
regionId INTEGER NOT NULL,
net INET NOT NULL UNIQUE,
active BOOLEAN NOT NULL DEFAULT TRUE,
description VARCHAR(32),
FOREIGN KEY (poolId) REFERENCES pool (id),
FOREIGN KEY (regionId) REFERENCES region (id)
);
CREATE TABLE IF NOT EXISTS address
(
address INET PRIMARY KEY,
poolId INTEGER NOT NULL,
assigned BOOLEAN NOT NULL DEFAULT FALSE,
lastChanged timestamp WITH TIME ZONE NOT NULL DEFAULT TIMESTAMP 'epoch',
username VARCHAR(64),
FOREIGN KEY (poolId) REFERENCES pool (id)
);
CREATE INDEX IF NOT EXISTS pool_index ON pool ( id );
CREATE INDEX IF NOT EXISTS region_index ON region ( id );
CREATE INDEX IF NOT EXISTS nas_index ON nas ( id, regionId );
CREATE INDEX IF NOT EXISTS block_index ON block ( id, poolId, net, active );
CREATE INDEX IF NOT EXISTS address_index ON address ( address, poolId,
assigned, lastChanged, lastuser );
/* end of sql */
|
