Blame | Last modification | View Log | Download | RSS feed
/* sql table creator for computer_asset_mananagement_program */
/* Author: R. W. Rodolico, http://www.dailydata.net */
/* Software License: GPL */
/* version 0.10 */
/* 20071103 - Modified to break software packages and names into lookup tables */
/* version 0.20 */
/* Major modification to handle a lot more information such as additional device types, additional attributes */
/* and network interconnects */
/* version 2.0 */
/* previously, this was an "all in one" structure. Now, reduced to minimal with each module creating its own */
/* modifications. This is the very basic set of tables needed */
drop table if exists _system;
create table _system (
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 comment 'the actual value of this entry',
primary key (group_name,key_name)
) comment 'Basically a configuration file, equivilent to a windows INI file';
drop table if exists client;
create table client (
client_id int unsigned not null auto_increment,
name varchar(64) 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 date comment 'date record was added',
removed_date date 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 unsigned not null auto_increment,
client_id int unsigned not null comment 'a link to the client table',
name varchar(64) comment 'the visible, displayed name',
notes text comment 'Just a place to record some notes',
added_date date comment 'date record was added',
removed_date date 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 unsigned not null auto_increment,
site_id int unsigned not null comment 'a link to the site table',
device_type_id int unsigned not null comment 'a link to the device_type table',
name varchar(255) comment 'the visible, displayed name',
notes text comment 'Just a place to record some notes',
part_of int unsigned null comment 'If this device is a part of a larger device, this points to the larger device',
added_date date comment 'date record was added',
removed_date date comment 'date record was deleted/supserceded',
primary key (device_id)
) comment 'information on an indivicual device (computer, router, printer, etc)';
drop table if exists attrib;
create table attrib (
attrib_id int unsigned not null auto_increment,
name varchar(64) comment 'the visible, displayed name',
added_date date comment 'date record was added',
removed_date date 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 device_attrib;
create table device_attrib (
device_id int unsigned not null comment 'a link to the device table',
attrib_id int unsigned not null comment 'a link to the attrib table',
value text comment 'The actual value of this attribute.',
added_date date comment 'date record was added',
removed_date date comment 'date record was deleted/supserceded',
primary key (device_id,attrib_id,removed_date)
) comment'links devices and their attributes together';
drop table if exists device_type;
create table device_type (
device_type_id int unsigned not null auto_increment,
name varchar(64) comment 'the visible, displayed name',
added_date date comment 'date record was added',
removed_date date 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 unsigned not null auto_increment,
email varchar(64) comment 'email address, used as login id',
pass char(32) comment 'the encrypted password of the user',
primary key (login_id)
) comment 'table for logging into the the maintenance system';
drop table if exists permissions;
create table permissions (
permissions_id int unsigned not null auto_increment,
description varchar(64) comment 'Description of this permission',
php_function text comment 'Holds PHP code that, when evaluated, determines whether user has access to a record',
primary key (permissions_id)
) comment 'Holds the definition of permissions available on the system';
drop table if exists login_permissions;
create table login_permissions (
login_permissions_id int unsigned not null auto_increment,
login_id int unsigned not null comment 'the login this record is associated with',
permissions_id int unsigned not null comment 'the permission this record is associated with',
parameter_list text comment 'Any additional parameters needed by this permission to validate',
primary key (login_permissions_id)
) comment 'Hold a list of permissions available to the login id';
/* get some child tables out of the way */
insert into attrib(name,added_date) values ('Operating System', now());
insert into attrib(name,added_date) values ('Memory', now());
insert into attrib(name,added_date) values ('CPU Count', now());
insert into attrib(name,added_date) values ('CPU Speed', now());
insert into attrib(name,added_date) values ('CPU Type', now());
insert into attrib(name,added_date) values ('CPU Subtype', now());
insert into attrib(name,added_date) values ('Brand',now());
insert into attrib(name,added_date) values ('Model Number',now());
insert into attrib(name,added_date) values ('Serial Number',now());
insert into attrib(name,added_date) values ('Printer Cartridge', now());
insert into device_type (name,added_date) values ('Computer',now());
insert into device_type (name,added_date) values ('Router',now());
insert into device_type (name,added_date) values ('Switch',now());
insert into device_type (name,added_date) values ('Printer',now());
insert into _system( group_name,key_name,theValue) values ( 'default','ALL','Brand,Model Number,Serial Number');
insert into _system( group_name,key_name,theValue) values ( 'default','Computer','Operating System,Memory,CPU Count,CPU Speed,CPU Type,CPU Subtype');
insert into _system( group_name,key_name,theValue) values ( 'default','Printer','Printer Cartridge' );
insert into default_attrib(device_type_id,attrib_id,added_date)
select device_type_id,attrib_id,now()
from device_type,attrib
where attrib.name in ('Brand','Model Number','Serial Number');
insert into default_attrib(device_type_id,attrib_id,added_date)
select device_type_id,attrib_id,now()
from device_type,attrib
where device_type.name = 'Computer' and attrib.name in ('Operating System','Memory','CPU Count','CPU Speed','CPU Type','CPU Subtype');
insert into default_attrib(device_type_id,attrib_id,added_date)
select device_type_id,attrib_id,now()
from device_type,attrib
where device_type.name = 'Printer' and attrib.name in ('Printer Cartridge');
/* Now, create a client site */
insert into client( name,added_date) values ( 'Daily Data', now() );
insert into site ( client_id, name,added_date) select client_id,'Main Office',now() from client where name = 'Daily Data';
insert into device ( site_id, device_type_id,name,added_date) select site_id,device_type_id, 'PIX 501',now() from site,device_type where site.name = 'Main Office' and device_type.name = 'Router';