Rev 38 | Blame | Last modification | View Log | Download | RSS feed
/*
mySQL Database for Computer Asset Management Program
No records are deleted by default. If record is to be deleted
it is marked as removed (removed not null)
When joining tables (ie client_device) is modified, the old record is
marked removed and a new record added, to give history of device
*/
/* used as a configuration file for the app as a whole */
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',
primary key (_system_id )
) COMMENT='Basically a configuration file equivilent to a windows INI ';
/* used by the auth class */
drop table if exists _user;
create table _user (
_user_id int unsigned not null auto_increment,
username varchar(32) not null comment 'user name for logging in',
name varchar(64) comment 'common name of user',
email varchar(64) comment 'email address of user',
notes text comment 'any notes about user',
passwd varchar(256) comment 'encrypted password of user',
access text comment 'sql to determine what records user can view',
added date not null comment 'Date record added to database',
removed date default null comment 'Date record marked as removed',
primary key ( _user_id )
) comment 'user access to program';
/* used by the menu class */
drop table if exists _menu;
create table _menu (
_menu_id int unsigned not null auto_increment,
parent_id int unsigned default 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) default 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';
/* beginning of the actual tables used by the app, client, site and device */
/*
A device is owned by a client (entity). This is a list of available
clients who can own things.
*/
drop table if exists client;
create table client (
client_id int unsigned not null auto_increment,
name varchar(64) comment 'Name of the client',
notes text comment 'Any notes you want to enter',
internal_notes text comment 'private notes accessible only to technicians',
added date not null comment 'Date record added to database',
removed date default null comment 'Date record marked as removed',
primary key (client_id)
) comment 'Stores information about a particlar client/owner';
/*
A device can be on a site. This is a list of sites which can house
devices
*/
drop table if exists site;
create table site (
site_id int unsigned not null auto_increment,
name varchar(64) comment 'Name of the site',
notes text comment 'Any notes you want to enter',
added date not null comment 'Date record added to database',
removed date default null comment 'Date record marked as removed',
primary key (site_id)
) comment 'Stores information about a particlar physical site';
/*
The basis for the program. In our context, an asset is a computer or
other device.
*/
drop table if exists device;
create table device (
device_id int unsigned not null auto_increment,
name varchar(64) not null comment 'name of the device or device',
notes text default null comment 'any notes we want to store',
device_type_id int unsigned not null references device_type( device_type_id ),
added date not null comment 'Date record added to database',
removed date default null comment 'Date record marked as removed',
primary key (device_id)
) comment 'stores information about an individual device or other device';
/*
allows devices to be related to each other, ie a "part of" scheme, where
for example, a virtualized server may be 'part of' a physical machine.
By using a child table, we can track movement across this relationship
*/
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 ),
added date not null comment 'Date record added to database',
removed date default null comment 'Date record marked as removed',
primary key (device_device_id)
) comment 'joins device to another device';
/*
device_type is a child table of device, determining what type of device it is]
such as computer, printer, router, whatever.
flags currently used as 0 is non system and 1 is system, though this could
be expanded in the future
*/
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',
flags int unsigned default 1 comment 'flags for this device type',
added date not null COMMENT 'date record was added',
removed date default NULL COMMENT 'date record was deleted/supserceded',
primary key (device_type_id)
) comment='simply a list of device types ie computer printer whatever';
/*
this is actually a join with multiple tables, depending on what the
name is associated with, client, site or device
for example, if 'id' is client_id from the client table, then 'source'
would be 'client' (name of the table);
*/
drop table if exists alias;
create table alias (
alias_id int unsigned not null auto_increment,
source varchar(64) comment 'the table this alias comes from',
id int unsigned not null comment 'the client, site or device id',
name varchar(64) comment 'the alias for the device',
primary key (alias_id)
) comment 'Allows client, site and device to have multiple names';
/*
links a site to a client. A site is "owned" by a client
*/
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 ),
added date not null comment 'Date record added to database',
removed date default null comment 'Date record marked as removed',
primary key (client_site_id)
) comment 'A client owns a site';
/*
A device is owned by a client. Ownership can be tracked by removing
a device from one client and adding it to another.
*/
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 ),
added date not null comment 'Date record added to database',
removed date default null comment 'Date record marked as removed',
primary key (client_device_id)
) comment 'Links client and device tables';
/*
A device is at a location. Location history can be tracked by removing
and adding a device to a new location
*/
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 ),
added date not null comment 'Date record added to database',
removed date default null comment 'Date record marked as removed',
primary key ( site_device_id )
) comment 'Links site and device tables';
/* some useful views */
/* active sites and their owner (from client table) */
create or replace view view_site_owner as
select
site.name site_name,
site.site_id site_id,
client.name site_owner_name,
client.client_id site_owner_id
from
site
join client_site using (site_id)
join client using (client_id)
where
1=1
and client_site.removed is null
and client.removed is null
and site.removed is null;
create or replace view view_device_client_site as
select
device.device_id device_id,
client.client_id client_id,
view_site_owner.site_id site_id,
view_site_owner.site_owner_id site_owner_id,
device.name device_name,
client.name client_name,
view_site_owner.site_name site_name,
view_site_owner.site_owner_name site_owner_name
from
device
join client_device using (device_id)
join client using (client_id)
join site_device using (device_id)
join view_site_owner using (site_id)
where
1=1
and device.removed is null
and client.removed is null;
/* add some indexes */
alter table device add index (added,removed);
alter table device add index (removed);
alter table device add index (name);
alter table device add index (device_type_id);
alter table client_site add index ( client_id,site_id );
alter table site add index (removed);
alter table client add index (removed);
/* preload some data */
/* basic menu; home and logout */
insert into _menu values ( null, null, 'Home Page', 'index.html' );
insert into _menu values ( null, null, 'Log Out', 'logout.html' );
/* one user, admin, with no password for initial access */
insert into _user( username,added) values ( 'admin', now() );
/* insert into device_type (name,flags, added ) values ( 'Computer',1,now() ); */