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

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

/*

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