Subversion Repositories computer_asset_manager_v2

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
33 rodolico 1
/*
46 rodolico 2
Remove everything wit
3
drop table if exists _config;
4
drop table if exists _menu;
5
drop table if exists _permissions;
6
drop table if exists _permissions_categories;
7
drop table if exists _system;
8
drop table if exists _users_permissions;
9
drop view if exists _view_users_permissions;
10
drop table if exists client;
11
drop table if exists client_device;
12
drop table if exists client_site;
13
drop table if exists device;
14
drop table if exists device_device;
15
drop table if exists device_device_type;
16
drop table if exists device_type;
17
drop table if exists site;
18
drop table if exists site_device;
19
drop table if exists temp;
20
drop view if exists view_device_types;
21
drop view if exists view_device_site_client_type;
22
 
23
*/
24
 
25
/*
33 rodolico 26
   The base install of CAMP2.
27
 
28
   Just track devices. A device can be any arbitrary item.
46 rodolico 29
   A device has one owner, and is located at one location (location)
30
   A location also has an owner. Devices can be located on locations
31
   owned by a different owner, ie when a device is lent to a owner
33 rodolico 32
   or colocated at a third party NOC.
33
 
34
   This is very basic. It only tracks device ownership and locations,
35
   and movement between them (via the created and removed fields).
36
   When a device is moved to a different location, or sold to a different
46 rodolico 37
   owner, the old record in the linking field has its removed field 
33 rodolico 38
   updated and a new record created with a created date. With this, we
39
   can track the lifespan of a device.
40
 
41
   It is assumed various modules will be created to extend the capabilities
42
   of this basic structure. However, modules should not modify the basic
43
   database structure, instead creating new tables that link into
44
   these tables.
45
*/
46
 
47
/*
48
   configuration of application. DB representation of old Windows INI file format
49
   containing groups, and under groups key/value pairs
50
*/
51
drop table if exists _system;
52
create table _system (
53
  _system_id      int unsigned not null auto_increment,
54
  group_name      varchar(45) not null comment 'Group name for matching',
55
  key_name        varchar(45) not null comment 'key name for matching',
55 rodolico 56
  key_value       text not null comment 'value for key_name',
40 rodolico 57
  unique key      unique_group_name( group_name,key_name ),
33 rodolico 58
  primary key     (_system_id)
59
)
60
comment = 'Stores internal system information like ini file';
61
 
62
/*
63
   holds menu, which may be modified programmatically.
64
   This is a hierarchial menu so an entry may have a parent entry
65
*/
66
drop table if exists _menu;
67
create table _menu (
68
   _menu_id       int unsigned not null auto_increment,
37 rodolico 69
   parent_id      int unsigned default null references _menu (_menu_id),
33 rodolico 70
   caption        varchar(20) not null comment 'Caption displayed for menu',
71
   url            varchar(64) default null comment 'optional URL when they click here',
46 rodolico 72
   shortname      varchar(16) default null comment 'used for permissions to determine if we display',
33 rodolico 73
   primary key    (_menu_id)
74
) comment = 'Menus for the application';
75
 
46 rodolico 76
/*
77
   insert the menu options for the main program
78
*/
79
insert into camp2._menu values (null,null,'Home', '/index.php', 'menu_home' );
55 rodolico 80
insert into camp2._menu select null,_menu_id,'Owners', '/index.php?module=owner', 'menu_owner' from camp2._menu where caption = 'Home';
81
insert into camp2._menu select null,_menu_id,'Locations', '/index.php?module=location', 'menu_location' from camp2._menu where caption = 'Home';
82
insert into camp2._menu select null,_menu_id,'Devices', '/index.php?module=device', 'menu_device' from camp2._menu where caption = 'Home';
83
insert into camp2._menu values (null,null,'Reports', '/index.php?module=report', 'menu_report' );
33 rodolico 84
 
85
/*
86
   simple table to hold ownership information
87
*/
46 rodolico 88
drop table if exists owner;
89
create table owner (
90
   owner_id       int unsigned not null auto_increment,
91
   name           varchar(64) comment 'name of owner',
33 rodolico 92
   created        date comment 'date record was created',
93
   removed        date comment 'date record was removed',
46 rodolico 94
   primary key    (owner_id)
95
) comment 'hold owner information';
33 rodolico 96
 
97
/*
46 rodolico 98
   simple table to hold location where a device is located
33 rodolico 99
*/
46 rodolico 100
drop table if exists location;
101
create table location (
102
   location_id    int unsigned not null auto_increment,
103
   name           varchar(64) comment 'name of location',
33 rodolico 104
   created        date comment 'date record was created',
105
   removed        date comment 'date record was removed',
46 rodolico 106
   primary key    (location_id)
107
) comment 'hold location information';
33 rodolico 108
 
109
/*
110
   table which holds a device type, such as server, workstation
111
   printer, virtual, etc...
112
*/
113
drop table if exists device_type;
114
create table device_type (
115
   device_type_id int unsigned not null auto_increment,
116
   name           varchar(64) comment 'name of device type',
117
   created        date comment 'date record was created',
118
   removed        date comment 'date record was removed',
119
   primary key    (device_type_id)
120
) comment 'simple child table to determine the type of device we have';
121
 
122
/*
40 rodolico 123
   holds very basic information on a device such as its name and a unique id.
124
   This is the main table for the database, and each device
33 rodolico 125
   should be uniquely identified. We will allow name to be modified
126
   randomly, however.
127
 
128
   Internally, we find this device based on device_id, but for remote
129
   systems, we use the combined uuid and serial to uniquely identify
130
   some manufacturers use one uuid for all systems, but the uuid and serial
131
   number combination should be unique. serial can be any arbitrary string
132
   and it is suggested to use manufacturer:serial or something like that
133
*/
134
drop table if exists device;
135
create table device (
136
   device_id      int unsigned not null auto_increment,
40 rodolico 137
   uuid           varchar(36) comment 'unique id of this device, normally uuid',
33 rodolico 138
   serial         varchar(32) comment 'serial number of this device, if we have it',
139
   name           varchar(64) comment 'name of device',
140
   created        date comment 'date record was created',
141
   removed        date comment 'date record was removed',
40 rodolico 142
   unique key     unique_uuid( uuid, serial ),
33 rodolico 143
   primary key    (device_id)
144
) comment 'holds individual devices';
145
 
40 rodolico 146
/*
147
   Many to many join table allowing devices to have multiple device
148
   types. NOTE: I'm using device_id and device_type_id as the composite
149
   primary key, so no duplicates, and we don't need an 'id' column
150
*/
33 rodolico 151
 
40 rodolico 152
drop table if exists device_device_type;
153
create table device_device_type (
154
   device_id      int unsigned not null references device( device_id ),
155
   device_type_id int unsigned not null references device_type( device_type_id ),
156
   primary key (device_id,device_type_id)
157
) comment 'many to many join for device and device_type tables';
158
 
33 rodolico 159
/*
46 rodolico 160
   Set ownership of a location. These records are not deleted, but by
33 rodolico 161
   setting field removed to non-null value, then creating a new record,
46 rodolico 162
   we can track ownership of locations.
33 rodolico 163
*/
46 rodolico 164
drop table if exists owner_location;
165
create table owner_location (
166
   owner_location_id int unsigned not null auto_increment,
167
   owner_id      int unsigned not null references owner( owner_id ),
168
   location_id        int unsigned not null references location( location_id ),
33 rodolico 169
   created        date comment 'date record was created',
170
   removed        date comment 'date record was removed',
46 rodolico 171
   index          location_device ( owner_id,location_id ),
172
   primary key    (owner_location_id)
173
) comment 'links ownership of a location to a owner';   
33 rodolico 174
 
175
/*
176
   Set location of a device. These records are not deleted, but by
177
   setting field removed to non-null value, then creating a new record,
178
   we can track movement of devices.
179
*/
46 rodolico 180
drop table if exists location_device;
181
create table location_device (
182
   location_device_id int unsigned not null auto_increment,
183
   location_id        int unsigned not null references location( location_id ),
33 rodolico 184
   device_id      int unsigned not null references device( device_id ),
185
   created        date comment 'date record was created',
186
   removed        date comment 'date record was removed',
46 rodolico 187
   index          location_device ( location_id,device_id ),
188
   primary key    (location_device_id)
33 rodolico 189
) comment 'links a device to its location';
190
 
191
/*
192
   Set ownership of a device. These records are not deleted, but by
193
   setting field removed to non-null value, then creating a new record,
194
   we can track ownership of devices.
195
*/
46 rodolico 196
drop table if exists owner_device;
197
create table owner_device (
198
   owner_device_id  int unsigned not null auto_increment,
199
   owner_id      int unsigned not null references owner( owner_id ),
33 rodolico 200
   device_id      int unsigned not null references device( device_id ),
201
   created        date comment 'date record was created',
202
   removed        date comment 'date record was removed',
46 rodolico 203
   index          owner_device( owner_id, device_id ),
204
   primary key    ( owner_device_id )
33 rodolico 205
) comment 'links a device to its owner';
206
 
207
/*
208
   There can be a parent/child relationship with devices. For example, a virtual
209
   resides on a DOM0, so the parent_id of a DOMU is the device_id of the DOM0.
210
   or, a printer can be attached to a computer, so the printers parent is the
211
   device_id of the computer it is attached to.
212
*/
213
drop table if exists device_device;
214
create table device_device (
215
   device_device_id int unsigned not null auto_increment,
216
   device_id      int unsigned not null references device( device_id ),
217
   parent_id      int unsigned not null references device( device_id ),
218
   created        date comment 'date record was created',
219
   removed        date comment 'date record was removed',
220
   index          device_device( device_id, parent_id ),
221
   primary key    ( device_device_id )
222
) comment 'links a device to another device';
223
 
224
 
40 rodolico 225
/* 
226
   Some views so we don't have to reinvent the wheel when we're trying
227
   to grab some data
228
*/
229
 
230
/*
231
  a simple view that concats the values in device_device_type for 
232
  display. Since mySQL will not allow subqueries in views, required
46 rodolico 233
  to have this information in view_device_location_owner_type
40 rodolico 234
*/
46 rodolico 235
create or replace view view_device_types as
40 rodolico 236
   select 
237
      device_id,
238
      group_concat(distinct device_type.name) as device_types 
239
   from 
240
      device_device_type 
241
      join device_type using (device_type_id ) 
242
   group by device_id 
243
   order by name;
59 rodolico 244
 
245
/*
246
   View to show relationships between machines, ie the part_of scheme
247
   where one device is actually a "part of" another. Used mainly with
248
   virtualization to show a virtual machine is on a particular 
249
   hypervisor
250
*/
251
create or replace view view_device_device as
252
   select 
253
      device_device_id,
254
      device_device.device_id,
255
      device.name device_name,
256
      device_device.parent_id,
257
      parent.name parent_name,
258
      device_device.created,
259
      device_device.removed 
260
   from 
261
      device 
262
      join device_device using (device_id) 
263
      join device parent on (device_device.parent_id = parent.device_id);
264
 
265
/* link owners and locations together */
266
create or replace view view_owner_location as
267
   select distinct
268
      owner.name owner,
269
      owner.owner_id,
270
      location.name location,
271
      location.location_id,
272
      owner_location.created,
273
      owner_location.removed
274
   from
275
      owner
276
      join owner_location using (owner_id)
277
      join location using (location_id);
40 rodolico 278
 
279
/*
280
   Mongo view that gets all the information together to display
281
   device name, location, owner and type(s)
59 rodolico 282
   NOTE: we are limiting the "part of" to only currently active
283
   relationships
40 rodolico 284
*/
285
 
46 rodolico 286
create or replace view view_device_location_owner_type as
33 rodolico 287
   select
40 rodolico 288
      device.device_id device_id,
289
      device.uuid uuid,
290
      device.serial serial,
33 rodolico 291
      device.name device,
292
      device.created device_created,
293
      device.removed device_removed,
40 rodolico 294
      view_device_types.device_types,
46 rodolico 295
      location.location_id location_id,
296
      location.name location,
297
      location.created location_created,
298
      location.removed location_removed,
299
      owner.owner_id owner_id,
300
      owner.name owner,
301
      owner.created owner_created,
54 rodolico 302
      owner.removed owner_removed,
59 rodolico 303
      view_owner_location.owner location_owner,
304
      view_owner_location.owner_id location_owner_id,
305
      view_owner_location.created location_owner_created,
306
      view_owner_location.removed location_owner_removed,
54 rodolico 307
      (
308
         owner.removed is null
309
         and location.removed is null
310
         and device.removed is null
59 rodolico 311
      ) active,
312
      parent.parent_id,
313
      parent.parent_name parent,
314
      parent.created parent_created,
315
      parent.removed parent_removed
33 rodolico 316
   from
317
      device
40 rodolico 318
      join view_device_types using (device_id )
46 rodolico 319
      join location_device using (device_id)
320
      join location using (location_id)
321
      join owner_device using (device_id)
59 rodolico 322
      join owner using (owner_id)
323
      join view_owner_location using (owner_id,location_id)
324
      left outer join view_device_device parent using (device_id)
325
   where
326
      parent.removed is null;
36 rodolico 327
 
40 rodolico 328
 
329
insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' ) 
330
   on duplicate key update key_value = '0.1';
58 rodolico 331
insert into _system ( key_value, group_name, key_name ) values ( '', 'program', 'motd' );