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_date 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',

   pass         varchar(256) comment 'encrypted password of user',

   access       text comment 'sql to determine what records user can view',

   added_date   date not null comment 'Date record added to database',

   removed_date 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',

   added_date  date not null comment 'Date record added to database',

   removed_date 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  date not null comment 'Date record added to database',

   removed_date 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  date not null comment 'Date record added to database',

   removed_date 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,

   source       int unsigned not null references device( device_id ),

   target       int unsigned not null references device( device_id ),

   added_date  date not null comment 'Date record added to database',

   removed_date 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      date not null COMMENT 'date record was added',

  removed_date    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  date not null comment 'Date record added to database',

   removed_date 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  date not null comment 'Date record added to database',

   removed_date 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  date not null comment 'Date record added to database',

   removed_date date default null comment 'Date record marked as removed',

   primary key ( site_device_id )

) comment 'Links site and device tables';

/* add some indexes */

alter table device add index (added_date,removed_date);

alter table device add index (removed_date);

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_date);

alter table client add index (removed_date);

/* 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_date) values ( 'admin', now() );

insert into device_type (name,flags, added_date ) values ( 'Computer',1,now() );