Rev 99 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
/*
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 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_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 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 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 values (
null,
'Virtuals on Hypervisor',
'select device_id id,name Name from device where removed_date is null and device_id in (select device_id from device join device_type using (device_type_id) where part_of = <device_id> and device_type.show_as_system = \'Y\') order by name',
'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;
create or replace view view_device_systems as select * from device where device_type_id in (select device_type_id from device_type where show_as_system = 'Y');
/* 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() );