Subversion Repositories computer_asset_manager_v2

/*

Remove everything with

drop table if exists _menu;

drop table if exists _system;

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

drop table if exists location_device;

drop table if exists mapping;

drop table if exists owner;

drop table if exists owner_device;

drop table if exists owner_location;

drop view if exists view_device;

drop view if exists view_device_device;

drop view if exists view_device_location_owner_type;

drop view if exists view_device_types;

drop view if exists view_location_device;

drop view if exists view_owner_device;

drop view if exists view_owner_location;

*/

/*

   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 _menu values (null,null,'Home', '/index.php', 'menu_home' );

insert into _menu select null,_menu_id,'Owners', '/index.php?module=Owner', 'menu_owner' from _menu where caption = 'Home';

insert into _menu select null,_menu_id,'Locations', '/index.php?module=Location', 'menu_location' from _menu where caption = 'Home';

insert into _menu select null,_menu_id,'Devices', '/index.php?module=Device', 'menu_device' from _menu where caption = 'Home';

insert into _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' );