Subversion Repositories computer_asset_manager_v2

/* 

   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() ); */