Rev 58 | Blame | Last modification | View Log | Download | RSS feed
/*
Remove everything wit
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' );
/*
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',
created date 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',
created date 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',
is_system boolean comment 'if true, this is a system, ie a computer or virtual',
created date 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 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_location_id int unsigned not null auto_increment,
owner_id int unsigned not null references owner( owner_id ),
location_id int unsigned not null references location( location_id ),
created date comment 'date record was created',
removed date comment 'date record was removed',
index location_device ( owner_id,location_id ),
primary key (owner_location_id)
) 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_device_id int unsigned not null auto_increment,
location_id int unsigned not null references location( location_id ),
device_id int unsigned not null references device( device_id ),
created date comment 'date record was created',
removed date comment 'date record was removed',
index location_device ( location_id,device_id ),
primary key (location_device_id)
) 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_device_id int unsigned not null auto_increment,
owner_id int unsigned not null references owner( owner_id ),
device_id int unsigned not null references device( device_id ),
created date comment 'date record was created',
removed date comment 'date record was removed',
index owner_device( owner_id, device_id ),
primary key ( owner_device_id )
) 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_device_id int unsigned not null auto_increment,
device_id int unsigned not null references device( device_id ),
parent_id int unsigned not null references device( device_id ),
created date comment 'date record was created',
removed date comment 'date record was removed',
index device_device( device_id, parent_id ),
primary key ( device_device_id )
) 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
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_id,
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);
/* link owners and locations together */
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
join owner_location using (owner_id)
join location using (location_id);
/*
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
device.device_id device_id,
device.uuid uuid,
device.serial serial,
device.name device,
device.created device_created,
device.removed device_removed,
view_device_types.device_types,
location.location_id location_id,
location.name location,
location.created location_created,
location.removed location_removed,
owner.owner_id owner_id,
owner.name owner,
owner.created owner_created,
owner.removed owner_removed,
view_owner_location.owner location_owner,
view_owner_location.owner_id location_owner_id,
view_owner_location.created location_owner_created,
view_owner_location.removed location_owner_removed,
(
owner.removed is null
and location.removed is null
and device.removed is null
) active,
parent.parent_id,
parent.parent_name parent,
parent.created parent_created,
parent.removed parent_removed
from
device
join view_device_types using (device_id )
join location_device using (device_id)
join location using (location_id)
join owner_device using (device_id)
join owner using (owner_id)
join view_owner_location using (owner_id,location_id)
left outer join view_device_device parent using (device_id)
where
parent.removed is null;
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' );