Subversion Repositories computer_asset_manager_v1

   /* 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';