Subversion Repositories computer_asset_manager_v1

/*

   SQL information for the root module of CAMP

   Some conventions for these tables:

    tablename_id - Primary key for table

    added_date   - Date the record was added

    removed_date - Date record was removed/superceded

    fk id        - links into a separate table (ie, foreign key)

*/

DROP TABLE IF EXISTS _system;

CREATE TABLE _system (

  _system_id      int unsigned not null auto_increment,

  group_name      varchar(64) NOT NULL COMMENT 'used to group keys together',

  key_name        varchar(64) NOT NULL COMMENT 'key into this value',

  theValue        text null COMMENT 'the actual value of this entry',

  added_date      datetime not null COMMENT 'date record was added',

  removed_date    datetime default NULL COMMENT 'date record was closed',

  PRIMARY KEY     (_system_id )

)  COMMENT='Basically a configuration file equivilent to a windows INI ';

DROP TABLE IF EXISTS attrib;

CREATE TABLE attrib (

  attrib_id       int(10) unsigned NOT NULL auto_increment,

  name            varchar(64) not null unique COMMENT 'the visible displayed name',

  added_date      datetime not null COMMENT 'date record was added',

  removed_date    datetime default NULL COMMENT 'date record was deleted/supserceded',

  PRIMARY KEY     (attrib_id)

)   COMMENT='These are attributes that can be applied to a device';

DROP TABLE IF EXISTS client;

CREATE TABLE client (

  client_id       int(10) unsigned NOT NULL auto_increment,

  name            varchar(64) not null COMMENT 'the visible displayed name',

  notes           text COMMENT 'world visible notes on the client',

  internal_notes  text COMMENT 'These are internal notes visible only to us',

  added_date      datetime not null COMMENT 'date record was added',

  removed_date    datetime default NULL COMMENT 'date record was deleted/supserceded',

  PRIMARY KEY     (client_id)

)   COMMENT='information on a particular client';

DROP TABLE IF EXISTS site;

CREATE TABLE site (

  site_id         int(10) unsigned NOT NULL auto_increment,

  client_id       int(10) unsigned NOT NULL REFERENCES client(client_id),

  name            varchar(64) not null COMMENT 'the visible displayed name',

  notes           text COMMENT 'Just a place to record some notes',

  added_date      datetime not null COMMENT 'date record was added',

  removed_date    datetime default NULL COMMENT 'date record was deleted/supserceded',

  PRIMARY KEY     (site_id)

)   COMMENT='information on a site which is tied to a client';

DROP TABLE IF EXISTS device;

CREATE TABLE device (

  device_id       int(10) unsigned NOT NULL auto_increment,

  site_id         int(10) unsigned NOT NULL REFERENCES site(site_id),

  device_type_id  int(10) unsigned NOT NULL REFERENCES device_type (device_type_id),

  name            varchar(255) not NULL COMMENT 'the visible displayed name',

  notes           text COMMENT 'Just a place to record some notes',

  part_of         int(10) unsigned default NULL  COMMENT 'If this device is a part of a larger device this points to the larger device' REFERENCES device(device_id),

  added_date      datetime not null COMMENT 'date record was added',

  removed_date    datetime default NULL COMMENT 'date record was deleted/supserceded',

  PRIMARY KEY     (device_id)

)   COMMENT='information on an individual device computer router print';

DROP TABLE IF EXISTS device_attrib;

CREATE TABLE device_attrib (

  device_attrib_id int unsigned not null auto_increment,

  device_id       int(10) unsigned NOT NULL REFERENCES device(device_id),

  attrib_id       int(10) unsigned NOT NULL REFERENCES attrib(attrib_id),

  value           text COMMENT 'The actual value of this attribute.',

  added_date      datetime NOT NULL COMMENT 'date record was added',

  removed_date    datetime default NULL,

  PRIMARY KEY     (device_attrib_id)

)  COMMENT='links devices and their attributes together';

DROP TABLE IF EXISTS device_type;

CREATE TABLE device_type (

  device_type_id  int(10) unsigned NOT NULL auto_increment,

  name            varchar(64) not null COMMENT 'the visible displayed name',

  show_as_system  char(1) default 'Y',

  added_date      datetime not null COMMENT 'date record was added',

  removed_date    datetime default NULL COMMENT 'date record was deleted/supserceded',

  PRIMARY KEY     (device_type_id)

)   COMMENT='simply a list of device types ie computer printer whatever';

DROP TABLE IF EXISTS login;

CREATE TABLE login (

  login_id        int(10) unsigned NOT NULL auto_increment,

  email           varchar(64) not null COMMENT 'email address used as login id',

  pass            char(32) default NULL COMMENT 'the encrypted password of the user',

  where_clause    text COMMENT 'clause that limits what a user can see',

  added_date      datetime not null COMMENT 'date record was added',

  removed_date    datetime default NULL COMMENT 'date record was closed',

  PRIMARY KEY     (login_id)

)   COMMENT='table for logging into the the maintenance system';

DROP TABLE IF EXISTS menu;

CREATE TABLE menu (

   menu_id        int unsigned not null auto_increment,

   parent_id      int unsigned null  comment 'If this is a submenu the id of the parent' REFERENCES menu(menu_id),

   caption        varchar(20) not null comment 'The actual caption displayed',

   url            varchar(120) null comment 'the url of the page/script to call or null if this contains sub-options',

   primary key    (menu_id)

) comment 'We keep the entire menu structure here so modules can modify it';

drop table if exists report;

create table report (

   report_id      int unsigned not null auto_increment,

   name           varchar(64) not null comment 'Display Name of Report',

   query          text not null comment 'Query to be executed',

   parameters     text comment 'All parameters used in above',

   screen_report  int unsigned default null comment 'Each screen can be assigned a bit and this will show up on a screen',

   primary key    (report_id)

) comment 'holds definition for report';

/* create some keys */

alter table attrib add index (name);

alter table attrib add index (added_date,removed_date);

alter table device add index (added_date,removed_date);

alter table device add index (part_of);

alter table device add index (removed_date);

alter table device add index (site_id);

alter table device add index (name);

alter table device add index (device_type_id);

alter table device_attrib add index (device_id);

alter table device_attrib add index (added_date,removed_date);

alter table site add index (client_id);

alter table site add index (removed_date);

alter table client add index (removed_date);

alter table login add index (email);

alter table login add index (removed_date);

/* a couple of baby reports */

insert into report( name, query, parameters ) values ('Current Clients','select name Client, notes Notes, internal_notes "Internal Notes", date(added_date) Added from current_client', null );

insert into report( name, query, parameters ) values ('Current Sites', 'select client.name Client, site.name Site, site.added_date "Site Added" from current_client client join current_site site using (client_id) order by client.name,site.name', null );

insert into report( name, query, parameters ) values ('Client Devices','select client.name,site.name,device.name

from current_client client join current_site site using (client_id) 

join current_device device using (site_id)

where client.client_id = <client_id>','client_id++Client++select client_id,name from current_client order by name++select name from current_client where client_id = <value>');

insert into report( name, query, parameters, screen_report ) values ('Device Attributes','select attrib.name "Attribute",device_attrib.value "Value" 

         from device_attrib join attrib using (attrib_id) 

         where device_id = <device_id> and device_attrib.removed_date is null',

         'device_id++Device++select device_id,name from device where removed_date is null++select name from device where device_id = <value>', 1);

/* base menu */

insert into menu( url,caption,parent_id) values ('/index.html','Home Page',null);

insert into menu( url,caption,parent_id) values ('/reports.html','Reports',null);

insert into menu( url,caption,parent_id) values ('/backup.html', 'Backup', null);

/* insert into menu( url,caption,parent_id) select 'reports.html','Generic Report Writer', menu_id from menu where caption = 'Reports'; */

/* Some convenience views that return only current entries (ie, removed_date is null) */

create or replace view current_client as select * from client where removed_date is null;

create or replace view current_site as select * from site where removed_date is null;

create or replace view current_device as select * from device where removed_date is null;

/* A couple of additional convenience views */

create or replace view current_systems as select * from device where removed_date is null and device.device_type_id in (select device_type_id from device_type where show_as_system = 'Y');

create or replace view client_site as select concat( client.name, ' - ', ifnull(site.name,'None' )) name, client.client_id,site.site_id from client left outer join site using (client_id);

create or replace view client_systems as select device.device_id,concat(device.name, ' (', client_site.name, ')') 'Device' from device join client_site using (site_id) join device_type using (device_type_id) where device_type.show_as_system = 'Y';

/* just a way of making sure we can log in */

insert into login (email, pass, where_clause, added_date ) values ('admin', md5('pass') , 'ADMINISTRATOR', now() );

/* create some defaults for device_type */

insert into device_type ( name, show_as_system, added_date ) values ( 'Computer', 'Y', now() );

insert into device_type ( name, show_as_system, added_date ) values ( 'Virtual', 'Y', now() );

insert into device_type ( name, show_as_system, added_date ) values ( 'Router', 'Y', now() );

insert into device_type ( name, show_as_system, added_date ) values ( 'Switch', 'Y', now() );

insert into device_type ( name, show_as_system, added_date ) values ( 'Network Printer', 'Y', now() );

insert into device_type ( name, show_as_system, added_date ) values ( 'PCI Card', 'N', now() );

insert into device_type ( name, show_as_system, added_date ) values ( 'Periphial', 'N', now() );

/* create some defaults for attrib */

insert into attrib (name,added_date) values ( 'Manufacturer', now() );

insert into attrib (name,added_date) values ( 'Model', now() );

insert into attrib (name,added_date) values ( 'Serial Number', now() );