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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
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
331
332
|
-- The database is named it
create database it;
use it;
-- VLANs
CREATE TABLE vlans (
vl_s char(8) NOT NULL default '', -- short VLAN type, i.e. 500
vl_l char(64) default 'my network', -- long type, description
vl_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (vl_s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE networks(
nw_ip char(18) NOT NULL default '', -- IP
nw_ma char(18) default '', -- network mask
nw_gw char(18) default '', -- default gateway
nw_bc char(18) default '', -- broadcast address
vl_s char(8) default '', -- VLAN
nw_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(nw_ip),
INDEX(vl_s),
CONSTRAINT cf FOREIGN KEY(vl_s) REFERENCES vlans(vl_s) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- network connection speed setup
CREATE TABLE ddspeed (
sp_s char(12) NOT NULL default '', -- short name (abbreviation)
sp_l char(64) default NULL, -- long name, description
sp_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(sp_s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO ddspeed (sp_s, sp_l ) VALUES
('auto', 'Autonegotiation'),
('10', '10 MBit/s half-duplex'),
('100', '100 MBit/s half-duplex'),
('10fd', '10 MBit/s full-duplex'),
('100fd', '100 MBit/s full-duplex');
-- guest flags
CREATE TABLE guestflag (
gu_f integer , -- integer value in computers/co_gu
gu_d char(64) default NULL, -- description
gu_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(gu_f)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO guestflag (gu_f, gu_d) VALUES
(0, 'Normal computer'),
(1, 'BYOD: Bring your own device, private computers'),
(2, 'Outside our responsibility'),
(3, 'DHCP: IP addresses and host named dynamically assigned'),
(4, 'Secondary network interfaces of computers'),
(5, 'No software: Measurement device, oscilloscope...'),
(6, 'Reserved: IP address/host name reserved, but not yet used'),
(7, 'Retired: Computer or device no longer in use');
-- buildings
CREATE TABLE buildings (
gb_s char(4) NOT NULL default '', -- short name (abbreviation)
gb_l char(64) default NULL, -- complete name
gb_a1 char(64) default NULL, -- address line 1
gb_a2 char(64) default NULL, -- address line 2
gb_a3 char(64) default NULL, -- address line 3
gb_a4 char(64) default NULL, -- address line 4
gb_plz char(32) default '12345', -- ZIP code
gb_ort char(64) default 'Somewhere', -- location (town or city name)
gb_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(gb_s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- network connections
CREATE TABLE patches (
dd_n char(16) NOT NULL default '', -- short name, label on socket
sp_s char(12) default 'auto', -- speed setup
vl_s char(8) default '-', -- VLAN
dd_p char(16) default NULL, -- hub port used for socket
gb_s char(4) default '', -- building
dd_r char(8) default NULL, -- room
dd_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(dd_n),
INDEX(sp_s),
INDEX(vl_s),
INDEX(gb_s),
CONSTRAINT c2 FOREIGN KEY (sp_s) REFERENCES ddspeed(sp_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT c3 FOREIGN KEY (vl_s) REFERENCES vlans(vl_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT c4 FOREIGN KEY (gb_s) REFERENCES buildings(gb_s) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- persons (computer owners, license owners)
CREATE TABLE users (
us_s char(12) NOT NULL default '-', -- short name, i.e. login name
us_t char(32) default NULL, -- title
us_sn char(64) default '', -- surname
us_fn char(64) default '', -- name
us_em char(64) default NULL, -- e-mail address
us_ko char(16) default NULL, -- department (costs controlling ID)
us_se char(16) default NULL, -- staff ID number
us_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(us_s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- netgroups
CREATE TABLE netgroups (
ng_s char(12) NOT NULL default '-', -- name
ng_l char(64) default '', -- description
ng_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(ng_s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- netgroup relations
CREATE TABLE ngdeps (
nd_pk int(10) unsigned NOT NULL auto_increment,
nd_p char(12) default NULL, -- parent group
nd_c char(12) default NULL, -- child group
nd_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(nd_pk),
INDEX(nd_p),
INDEX(nd_c),
CONSTRAINT c5 FOREIGN KEY (nd_p) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT c6 FOREIGN KEY (nd_c) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- DHCP classes
CREATE TABLE dhcpclasses (
dc_s char(16) NOT NULL default '-', -- DHCP class name
dc_d char(64) default NULL, -- description
nw_ip char(18) NOT NULL default '0.0.0.0', -- IP network for class
dc_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(dc_s),
INDEX(nw_ip),
CONSTRAINT cm FOREIGN KEY (nw_ip) REFERENCES networks(nw_ip) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- DHCP pools
CREATE TABLE dhcppools(
dp_pk int(10) unsigned NOT NULL auto_increment, -- primary key
dp_st char(18) default NULL, -- start address
dp_en char(18) default NULL, -- end address
nw_ip char(18) default NULL, -- network
dp_al integer default 0, -- allow unknown clients
dp_dn integer default 1, -- deny unknown clients
dc_s char(16) default NULL, -- allowed DHCP class
dp_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(dp_pk),
INDEX(nw_ip),
CONSTRAINT ci FOREIGN KEY(nw_ip) REFERENCES networks(nw_ip) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT cj FOREIGN KEY(dc_s) REFERENCES dhcpclasses(dc_s) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- DHCP groups
CREATE TABLE dhcpgroups(
dg_s char(16) NOT NULL, -- group name, primary key
dg_l char(64) default NULL, -- description
dg_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(dg_s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- DHCP options
CREATE TABLE dhcpoptions(
do_pk int(10) unsigned NOT NULL auto_increment, -- primary key
do_sc ENUM('server', 'vlan', 'network', 'group', 'pool', 'host') default 'server', -- scope type
do_sn char(32) default NULL, -- scope name
do_n char(64) NOT NULL, -- option name
do_v char(128) NOT NULL, -- option value
do_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(do_pk),
INDEX(do_sc),
INDEX(do_sn),
INDEX(do_n)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- hosts
CREATE TABLE computers (
co_s char(16) NOT NULL default 'e__', -- hostname
us_s char(12) default NULL, -- system owner
co_ip char(18) default NULL, -- IP address
co_mc char(18) default NULL, -- MAC address
ng_s char(12) default NULL, -- netgroup
co_co char(64) default NULL, -- comment, description
dc_s char(16) default NULL, -- DHCP class
dg_s char(16) default NULL, -- DHCP group
dd_n char(16) default NULL, -- network patch socket
gb_s char(4) default NULL, -- building
co_r char(8) default NULL, -- room
co_in char(12) default NULL, -- inventory number
co_sn char(64) default NULL, -- serial number
co_dd char(64) default NULL, -- DNS domain
co_ff integer default 0, -- full name first flag
co_gu integer default 0, -- guest PC flag
co_hi char(32) default NULL, -- host ID
co_ex DATE default NULL, -- expiration date (for guest PCs)
co_nn integer default 0, -- skip host when creating network docu
co_na integer default 0, -- skip in arpwatch
co_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (co_s),
INDEX(us_s),
INDEX(co_ip),
INDEX(ng_s),
INDEX(dc_s),
INDEX(dg_s),
INDEX(dd_n),
INDEX(gb_s),
INDEX(co_r),
INDEX(co_in),
INDEX(co_sn),
CONSTRAINT c7 FOREIGN KEY (us_s) REFERENCES users(us_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT c8 FOREIGN KEY (ng_s) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT cg FOREIGN KEY (gb_s) REFERENCES buildings(gb_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ch FOREIGN KEY (dc_s) REFERENCES dhcpclasses(dc_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ck FOREIGN KEY (dg_s) REFERENCES dhcpgroups(dg_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT cn FOREIGN KEY (dd_n) REFERENCES patches(dd_n) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT cp FOREIGN KEY (co_gu) REFERENCES guestflag(gu_f) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE aliases (
al_s char(80) NOT NULL, -- the alias
co_s char(16) default NULL, -- alias target
co_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(al_s),
INDEX(co_s),
CONSTRAINT co FOREIGN KEY(co_s) REFERENCES computers(co_s) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- software manufacturers
CREATE TABLE swmanufacturers (
sm_s char(16) NOT NULL, -- short name (abbreviation, key)
sm_l char(64) default '', -- full name
sm_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(sm_s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- software products
CREATE TABLE swproducts (
sw_s char(16) NOT NULL, -- short name (order key or abbreviation)
sw_l char(64) default '', -- full product name
sm_s char(16) default '', -- software manufacturer
sw_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(sw_s),
INDEX(sm_s),
CONSTRAINT c9 FOREIGN KEY (sm_s) REFERENCES swmanufacturers(sm_s) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- software resellers
CREATE TABLE swresellers (
sr_s char(16) NOT NULL, -- short name (abbreviation, key)
sr_l char(64) default '', -- full name
sr_a1 char(64) default NULL, -- address line 1
sr_a2 char(64) default NULL, -- address line 2
sr_a3 char(64) default NULL, -- address line 3
sr_a4 char(64) default NULL, -- address line 4
sr_plz char(8) default NULL, -- ZIP code
sr_ort char(64) default NULL, -- location (town or city name)
sr_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(sr_s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- license types
CREATE TABLE licensetypes (
lt_s char(16) NOT NULL, -- short name (abbreviation, key)
lt_l char(64) default '', -- full name of license type
lt_i int default 1, -- number of places for concurrent use
lt_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(lt_s),
INDEX(lt_l)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO licensetypes (lt_s, lt_l, lt_i) VALUES
('s', 'Single user user license', 1),
('c', 'Single user concurrent license', 1),
('flexlm', 'Flexlm controlled client installation', 1),
('gpl2', 'GNU General Public License, version 2', 1),
('lgpl2', 'GNU Lesser General Public License, version 2', 1),
('gpl3', 'GNU General Public License, version 3', 1),
('lgpl3', 'GNU Lesser General Public License, version 3', 1),
('bsd', 'BSD style license', 1),
('flexlm-s5', 'Flexlm license server, 5 users', 5);
-- list of obtained licenses/installations
CREATE TABLE licenses (
li_pk int(10) unsigned NOT NULL auto_increment,
li_on char(24) default NULL, -- internal order ID
sw_s char(16) default NULL, -- software product
us_s char(12) default NULL, -- user name of license owner
co_s char(16) default NULL, -- computer where license is used
lt_s char(16) default 's', -- license type
li_no char(64) default NULL, -- notes
sr_s char(16) default NULL, -- software reseller
li_dd char(16) default NULL, -- date of delivery
li_nd char(16) default NULL, -- delivery letter ID
li_di char(16) default NULL, -- date of invoice
li_ni char(16) default NULL, -- invoice ID
li_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(li_pk),
INDEX(li_on),
INDEX(sw_s),
INDEX(us_s),
INDEX(co_s),
INDEX(lt_s),
INDEX(sr_s),
INDEX(li_dd),
INDEX(li_nd),
INDEX(li_di),
INDEX(li_ni),
CONSTRAINT ca FOREIGN KEY (sw_s) REFERENCES swproducts(sw_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT cb FOREIGN KEY (sr_s) REFERENCES swresellers(sr_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT cc FOREIGN KEY (lt_s) REFERENCES licensetypes(lt_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT cd FOREIGN KEY (us_s) REFERENCES users(us_s) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ce FOREIGN KEY (co_s) REFERENCES computers(co_s) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|