Subversion Repositories computer_asset_manager_v2

/*

   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',

  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',

   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 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 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';

/* 

   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_site_client_type

*/

create 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;

/*

   Mongo view that gets all the information together to display

   device name, location, owner and type(s)

*/

drop view if exists view_device_site_client_type;

create view view_device_site_client_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,

      site.site_id site_id,

      site.name site,

      site.created site_created,

      site.removed site_removed,

      client.client_id client_id,

      client.name client,

      client.created client_created,

      client.removed client_removed

   from

      device

      join view_device_types using (device_id )

      join site_device using (device_id)

      join site using (site_id)

      join client_device using (device_id)

      join client using (client_id);

insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' ) 

   on duplicate key update key_value = '0.1';