Rev 36 | Rev 40 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
/*
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 site (location)
A site also has an owner. Devices can be located on locations
owned by a different client, ie when a device is lent to a client
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
client, 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 varchar(45) not null comment 'value for 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',
primary key (_menu_id)
) comment = 'Menus for the application';
insert into camp2._menu values (null,null,'Home', '/index.php');
/*
simple table to hold ownership information
*/
drop table if exists client;
create table client (
client_id int unsigned not null auto_increment,
name varchar(64) comment 'name of client',
created date comment 'date record was created',
removed date comment 'date record was removed',
primary key (client_id)
) comment 'hold client information';
/*
simple table to hold site where a device is located
*/
drop table if exists site;
create table site (
site_id int unsigned not null auto_increment,
name varchar(64) comment 'name of site',
created date comment 'date record was created',
removed date comment 'date record was removed',
primary key (site_id)
) comment 'hold site 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',
show_as_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 type, location,
and owner. 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(32) 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',
device_type_id int unsigned not null references device_type( device_type_id ),
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';
/*
Set ownership of a site. These records are not deleted, but by
setting field removed to non-null value, then creating a new record,
we can track ownership of sites.
*/
drop table if exists client_site;
create table client_site (
client_site_id int unsigned not null auto_increment,
client_id int unsigned not null references client( client_id ),
site_id int unsigned not null references site( site_id ),
created date comment 'date record was created',
removed date comment 'date record was removed',
index site_device ( client_id,site_id ),
primary key (client_site_id)
) comment 'links ownership of a site to a client';
/*
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 site_device;
create table site_device (
site_device_id int unsigned not null auto_increment,
site_id int unsigned not null references site( site_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 site_device ( site_id,device_id ),
primary key (site_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 client_device;
create table client_device (
client_device_id int unsigned not null auto_increment,
client_id int unsigned not null references client( client_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 client_device( client_id, device_id ),
primary key ( client_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';
drop view if exists view_device_site_client;
create view view_device_site_client as
select
device.device_id,
device.name device,
device.created device_created,
device.removed device_removed,
device_type.device_type_id,
device_type.name device_type,
site.site_id,
site.name site,
site.created site_created,
site.removed site_removed,
client.client_id,
client.name client,
client.created client_created,
client.removed client_removed
from
device
join site_device using (device_id)
join site using (site_id)
join client_device using (device_id)
join client using (client_id)
join device_type using (device_type_id);
insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' );