Rev 69 | Blame | Last modification | View Log | Download | RSS feed
/*
Remove everything with
drop table if exists _config;
drop table if exists _menu;
drop table if exists _permissions;
drop table if exists _permissions_categories;
drop table if exists _system;
drop table if exists _users_permissions;
drop view if exists _view_users_permissions;
drop table if exists client;
drop table if exists client_device;
drop table if exists client_site;
drop table if exists device;
drop table if exists device_device;
drop table if exists device_device_type;
drop table if exists device_type;
drop table if exists site;
drop table if exists site_device;
drop table if exists temp;
drop view if exists view_device_types;
drop view if exists view_device_site_client_type;
*/
/*
The base install of CAMP2.
Just track devices. A device can be any arbitrary item.
A device has one owner, and is located at one location (location)
A location also has an owner. Devices can be located on locations
owned by a different owner, ie when a device is lent to a owner
or colocated at a third party NOC.
This is very basic. It only tracks device ownership and locations,
and movement between them (via the created and removed fields).
When a device is moved to a different location, or sold to a different
owner, the old record in the linking field has its removed field
updated and a new record created with a created date. With this, we
can track the lifespan of a device.
It is assumed various modules will be created to extend the capabilities
of this basic structure. However, modules should not modify the basic
database structure, instead creating new tables that link into
these tables.
*/
/*
configuration of application. DB representation of old Windows INI file format
containing groups, and under groups key/value pairs
*/
drop table if exists _system;
create table _system (
_system_id int unsigned not null auto_increment,
group_name varchar(45) not null comment 'Group name for matching',
key_name varchar(45) not null comment 'key name for matching',
key_value text not null comment 'value for key_name',
unique key unique_group_name( group_name,key_name ),
primary key (_system_id)
)
comment = 'Stores internal system information like ini file';
/*
holds menu, which may be modified programmatically.
This is a hierarchial menu so an entry may have a parent entry
*/
drop table if exists _menu;
create table _menu (
_menu_id int unsigned not null auto_increment,
parent_id int unsigned default null references _menu (_menu_id),
caption varchar(20) not null comment 'Caption displayed for menu',
url varchar(64) default null comment 'optional URL when they click here',
shortname varchar(16) default null comment 'used for permissions to determine if we display',
primary key (_menu_id)
) comment = 'Menus for the application';
/*
insert the menu options for the main program
*/
insert into camp2._menu values (null,null,'Home', '/index.php', 'menu_home' );
insert into camp2._menu select null,_menu_id,'Owners', '/index.php?module=Owner', 'menu_owner' from camp2._menu where caption = 'Home';
insert into camp2._menu select null,_menu_id,'Locations', '/index.php?module=Location', 'menu_location' from camp2._menu where caption = 'Home';
insert into camp2._menu select null,_menu_id,'Devices', '/index.php?module=Device', 'menu_device' from camp2._menu where caption = 'Home';
insert into camp2._menu values (null,null,'Reports', '/index.php?module=report', 'menu_report' );
/*
Modules are allowed to do the same thing for different classes, ie
attribute can have a name for an Owner, and a name for a Location
This table allows us to indicate which base table an entry is for
*/
drop table if exists _base_class;
create table _base_class (
_base_class_id int unsigned not null auto_increment,
name varchar(64) comment 'name for display',
class_name varchar(64) comment 'actual class in program',
base_table varchar(64) comment 'name of table which holds the class data',
primary key (_base_class_id)
) comment 'a list of base classes';
insert into _base_class values (null,'Owner','Owner','owner');
insert into _base_class values (null,'Location','Location','location');
insert into _base_class values (null,'Device','Device','device');
/*
simple table to hold ownership information
*/
drop table if exists owner;
create table owner (
owner_id int unsigned not null auto_increment,
name varchar(64) comment 'name of owner',
uuid varchar(36) comment 'unique id of this owner, normally uuid',
created date default current_timestamp comment 'date record was created',
removed date comment 'date record was removed',
primary key (owner_id)
) comment 'hold owner information';
/*
simple table to hold location where a device is located
*/
drop table if exists location;
create table location (
location_id int unsigned not null auto_increment,
name varchar(64) comment 'name of location',
uuid varchar(36) comment 'unique id of this location, normally uuid',
created date default current_timestamp comment 'date record was created',
removed date comment 'date record was removed',
primary key (location_id)
) comment 'hold location information';
/*
table which holds a device type, such as server, workstation
printer, virtual, etc...
*/
drop table if exists device_type;
create table device_type (
device_type_id int unsigned not null auto_increment,
name varchar(64) comment 'name of device type',
created date default current_timestamp comment 'date record was created',
removed date comment 'date record was removed',
primary key (device_type_id)
) comment 'simple child table to determine the type of device we have';
/*
holds very basic information on a device such as its name and a unique id.
This is the main table for the database, and each device
should be uniquely identified. We will allow name to be modified
randomly, however.
Internally, we find this device based on device_id, but for remote
systems, we use the combined uuid and serial to uniquely identify
some manufacturers use one uuid for all systems, but the uuid and serial
number combination should be unique. serial can be any arbitrary string
and it is suggested to use manufacturer:serial or something like that
*/
drop table if exists device;
create table device (
device_id int unsigned not null auto_increment,
uuid varchar(36) comment 'unique id of this device, normally uuid',
serial varchar(32) comment 'serial number of this device, if we have it',
name varchar(64) comment 'name of device',
created date default current_timestamp comment 'date record was created',
removed date comment 'date record was removed',
unique key unique_uuid( uuid, serial ),
primary key (device_id)
) comment 'holds individual devices';
/*
Many to many join table allowing devices to have multiple device
types. NOTE: I'm using device_id and device_type_id as the composite
primary key, so no duplicates, and we don't need an 'id' column
*/
drop table if exists device_device_type;
create table device_device_type (
device_id int unsigned not null references device( device_id ),
device_type_id int unsigned not null references device_type( device_type_id ),
primary key (device_id,device_type_id)
) comment 'many to many join for device and device_type tables';
/*
Set ownership of a location. These records are not deleted, but by
setting field removed to non-null value, then creating a new record,
we can track ownership of locations.
*/
drop table if exists owner_location;
create table owner_location (
owner_id int unsigned not null references owner( owner_id ),
location_id int unsigned not null references location( location_id ),
created date default current_timestamp comment 'date record was created',
removed date comment 'date record was removed',
unique key (owner_id,location_id, removed)
) comment 'links ownership of a location to a owner';
/*
Set location of a device. These records are not deleted, but by
setting field removed to non-null value, then creating a new record,
we can track movement of devices.
*/
drop table if exists location_device;
create table location_device (
location_id int unsigned not null references location( location_id ),
device_id int unsigned not null references device( device_id ),
created date default current_timestamp comment 'date record was created',
removed date comment 'date record was removed',
unique key (location_id,device_id, removed)
) comment 'links a device to its location';
/*
Set ownership of a device. These records are not deleted, but by
setting field removed to non-null value, then creating a new record,
we can track ownership of devices.
*/
drop table if exists owner_device;
create table owner_device (
owner_id int unsigned not null references owner( owner_id ),
device_id int unsigned not null references device( device_id ),
created date default current_timestamp comment 'date record was created',
removed date default null comment 'date record was removed',
unique key ( owner_id, device_id, removed )
) comment 'links a device to its owner';
/*
There can be a parent/child relationship with devices. For example, a virtual
resides on a DOM0, so the parent_id of a DOMU is the device_id of the DOM0.
or, a printer can be attached to a computer, so the printers parent is the
device_id of the computer it is attached to.
*/
drop table if exists device_device;
create table device_device (
device_id int unsigned not null references device( device_id ),
parent_id int unsigned not null references device( device_id ),
created date default current_timestamp comment 'date record was created',
removed date default null comment 'date record was removed',
unique key ( device_id, parent_id, removed )
) comment 'links a device to another device';
/*
Some views so we don't have to reinvent the wheel when we're trying
to grab some data
*/
/*
a simple view that concats the values in device_device_type for
display. Since mySQL will not allow subqueries in views, required
to have this information in view_device_location_owner_type
*/
create or replace view view_device_types as
select
device_id,
group_concat(distinct device_type.name) as device_types,
group_concat( distinct device_type.device_type_id) as device_type_ids
from
device_device_type
join device_type using (device_type_id )
group by device_id
order by name;
/*
View to show relationships between machines, ie the part_of scheme
where one device is actually a "part of" another. Used mainly with
virtualization to show a virtual machine is on a particular
hypervisor
*/
create or replace view view_device_device as
select
device_device.device_id,
device.name device_name,
device_device.parent_id,
parent.name parent_name,
device_device.created,
device_device.removed
from
device
join device_device using (device_id)
join device parent on (device_device.parent_id = parent.device_id)
where
device_device.removed is null;
/*
View combines view_device_types, device, and the parent relationship
(device_device) to give us all the information about one simple device
*/
create or replace view view_device as
select
device.device_id,
device.uuid,
device.serial,
device.name device,
device.created device_created,
device.removed device_removed,
view_device_types.device_types,
view_device_types.device_type_ids,
view_device_device.parent_id,
view_device_device.parent_name parent,
view_device_device.created parent_added,
view_device_device.removed parent_removed
from
device
join view_device_types using (device_id)
left outer join view_device_device using (device_id);
/*
link owners and locations together for current connection only
Note that even if an owner doesn't have a location, it will still
show up here with a null location
*/
create or replace view view_owner_location as
select distinct
owner.name owner,
owner.owner_id,
location.name location,
location.location_id,
owner_location.created,
owner_location.removed
from
owner
left outer join owner_location using (owner_id)
left outer join location using (location_id)
where
owner_location.removed is null;
/*
link location to device in such a way as location will still show up
if it has no devices, ie left outer join
will not display historical where location_device is not null (ie, moved)
*/
create or replace view view_location_device as
select
location.location_id,
location.name location,
location.created location_created,
location.removed location_removed,
view_device.device_id,
view_device.device device,
view_device.device_created,
view_device.device_removed
from
location
left outer join location_device using (location_id)
left outer join view_device using (device_id)
where
location_device.removed is null;
/*
link owner to device in such a way as owner will still show up
if it has no devices, ie left outer join
*/
create or replace view view_owner_device as
select
owner.owner_id,
owner.name owner,
owner.created owner_created,
owner.removed owner_removed,
view_device.device_id,
view_device.device,
view_device.device_created,
view_device.device_removed
from
owner
left outer join owner_device using (owner_id)
left outer join view_device using (device_id)
where
owner_device.removed is null;
/*
Mongo view that gets all the information together to display
device name, location, owner and type(s)
NOTE: we are limiting the "part of" to only currently active
relationships
*/
create or replace view view_device_location_owner_type as
select
view_owner_device.device_id device_id,
view_owner_device.device device,
view_owner_device.device_created,
view_owner_device.device_removed,
view_owner_device.owner_id owner_id,
view_owner_device.owner,
view_owner_device.owner_created,
view_owner_device.owner_removed,
view_location_device.location_id location_id,
view_location_device.location,
view_location_device.location_created,
view_location_device.location_removed,
view_owner_location.owner location_owner,
view_owner_location.owner_id location_owner_id,
view_device.parent_id,
view_device.parent,
view_device.device_types
from
view_owner_device
left outer join view_location_device using (device_id)
left outer join view_device using (device_id)
left outer join view_owner_location using (location_id);
insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' )
on duplicate key update key_value = '0.1';
insert into _system ( key_value, group_name, key_name ) values ( '', 'program', 'motd' );