WebSVN – computer_asset_manager_v2 – Diff – /include/camp_v2.sql

 /*
    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_asset) is modified, the old record is
+   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 or replace table _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',
-  added_date      datetime not null COMMENT 'date record was added',
+  added_date      date not null COMMENT 'date record was added',
-  removed_date    datetime default NULL COMMENT 'date record was closed',
+  removed_date    date default NULL COMMENT 'date record was closed',
   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 or replace table user (
+create table _user (
-   user_id      int unsigned not null auto_increment,
+   _user_id     int unsigned not null auto_increment,
+   username     varchar(32) 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 default now() comment 'Date record added to database',
+   added_date   date not null comment 'Date record added to database',
-   removed_date date null default null comment 'Date record marked as removed',
+   removed_date date default null comment 'Date record marked as removed',
-   primary key (user_id)
+   primary key ( _user_id )
 ) comment 'user access to program';
 /* used by the menu class */
+drop table if exists _menu;
-create or replace table menu (
+create table _menu (
-   menu_id        int unsigned not null auto_increment,
+   _menu_id        int unsigned not null auto_increment,
-   parent_id      int unsigned null  comment 'If this is a submenu the id of the parent' REFERENCES menu(menu_id),
+   parent_id      int unsigned 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) null comment 'the url of the page/script to call or null if this contains sub-options',
-   primary key    (menu_id)
+   primary key    (_menu_id)
 ) comment 'We keep the entire menu structure here so modules can modify it';
-/* used by report class */
-create or replace table report (
-   report_id      int unsigned not null auto_increment,
-   name           varchar(64) not null comment 'Display Name of Report',
-   query          text not null comment 'Query to be executed',
-   parameters     text comment 'All parameters used in above',
-   screen_report  int unsigned default null comment 'Each screen can be assigned a bit and this will show up on a screen',
-   primary key    (report_id)
-) comment 'holds definition for report';
-/* beginning of the actual tables used by the app, client, site and asset */
+/* 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 or replace table 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 default now() comment 'Date record added to database',
+   added_date  date not null comment 'Date record added to database',
-   removed_date date null default null comment 'Date record marked as removed',
+   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 or replace table 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 default now() comment 'Date record added to database',
+   added_date  date not null comment 'Date record added to database',
-   removed_date date null default null comment 'Date record marked as removed',
+   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 or replace table asset (
+create table device (
-   asset_id    int unsigned not null auto_increment,
+   device_id    int unsigned not null auto_increment,
-   name        varchar(64) comment 'name of the device or asset',
+   name        varchar(64) comment 'name of the device or device',
    notes       text comment 'any notes we want to store',
-   part_of     int unsigned null references asset( asset_id),
-   asset_type_id int unsigned not null references asset_type( asset_type_id ),
+   device_type_id int unsigned not null references device_type( device_type_id ),
-   added_date  date not null default now() comment 'Date record added to database',
+   added_date  date not null comment 'Date record added to database',
-   removed_date date null default null comment 'Date record marked as removed',
+   removed_date date default null comment 'Date record marked as removed',
-   primary key (asset_id)
+   primary key (device_id)
-) comment 'stores information about an individual device or other asset';
+) 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 or replace table asset_relationship (
+create table device_device (
-   asset_relationship_id int unsigned not null auto_increment,
+   device_device_id int unsigned not null auto_increment,
-   source       int unsigned not null references asset( asset_id ),
+   source       int unsigned not null references device( device_id ),
-   target       int unsigned not null references asset( asset_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 (asset_relationship_id)
+   primary key (device_device_id)
-) comment 'joins asset to another asset';
+) comment 'joins device to another device';
 /*
-   asset_type is a child table of asset, determining what type of asset it is]
+   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 or replace table asset_type (
+create table device_type (
-  asset_type_id   int(10) unsigned NOT NULL auto_increment,
+  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 asset type',
+  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     (asset_type_id)
+  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 asset
+   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 or replace table 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 asset id',
+   id       int unsigned not null comment 'the client, site or device id',
-   name         varchar(64) comment 'the alias for the asset',
+   name         varchar(64) comment 'the alias for the device',
-   primary key (asset_alias_id)
+   primary key (alias_id)
-) comment 'Allows client, site and asset to have multiple names';
+) 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 or replace table 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 default now() comment 'Date record added to database',
+   added_date  date not null comment 'Date record added to database',
-   removed_date date null default null comment 'Date record marked as removed',
+   removed_date date default null comment 'Date record marked as removed',
-   primary key (client_id,asset_id)
+   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 or replace table client_asset (
+create table client_device (
-   client_asset_id int unsigned not null auto_increment,
+   client_device_id int unsigned not null auto_increment,
    client_id   int unsigned not null references client( client_id ),
-   asset_id    int unsigned not null references asset( asset_id ),
+   device_id    int unsigned not null references device( device_id ),
-   added_date  date not null default now() comment 'Date record added to database',
+   added_date  date not null comment 'Date record added to database',
-   removed_date date null default null comment 'Date record marked as removed',
+   removed_date date default null comment 'Date record marked as removed',
-   primary key (client_asset_id)
+   primary key (client_device_id)
-) comment 'Links client and asset tables';
+) 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 or replace table site_asset (
+create table site_device (
-   site_asset_id int unsigned not null auto_increment,
+   site_device_id int unsigned not null auto_increment,
    site_id     int unsigned not null references site( site_id ),
-   asset_id    int unsigned not null references asset( asset_id ),
+   device_id    int unsigned not null references device( device_id ),
-   added_date  date not null default now() comment 'Date record added to database',
+   added_date  date not null comment 'Date record added to database',
-   removed_date date null default null comment 'Date record marked as removed',
+   removed_date date default null comment 'Date record marked as removed',
-   primary key ( site_asset_id )
+   primary key ( site_device_id )
-) comment 'Links site and asset tables';
+) comment 'Links site and device tables';
 /* add some indexes */
-alter table asset add index (added_date,removed_date);
+alter table device add index (added_date,removed_date);
-alter table asset add index (part_of);
-alter table asset add index (removed_date);
+alter table device add index (removed_date);
-alter table asset add index (site_id);
+alter table device add index (name);
-alter table asset add index (name);
+alter table device add index (device_type_id);
-alter table asset add index (asset_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);
-/* some convenience views */
+/* 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' );
-/* this will combine asset, client and site */
+/* one user, admin, with no password for initial access */
-create or replace view view_current_asset_full as
-   select
-      asset.name 'asset',
-      client.name 'client',
-      site.name 'site'
-   from
-      asset
-      join client using( client_id )
-      join site using ( site_id )
-   where
-      client.removed_date is null
-      and site.removed_date is null
-      and asset.removed_date is null;
+insert into _user( username,added_date) values ( 'admin', now() );
-create or replace view view_asset_name as
-   select
-      asset_id 'asset_id',
-      null 'alias_id',
-      name 'name'
-   from asset
-   union
-   select
-      asset_id 'asset_id',
-      alias_id 'alias_id',
-      name 'name'
-   from
-      asset_alias
-   where
-      source = 'asset';
-create or replace view view_client_name as
-   select
-      client_id 'client_id',
-      null 'alias_id',
-      name 'name'
-   from client
-   union
-   select
-      id 'client_id',
-      alias_id 'alias_id',
-      name 'name'
-   from
-      alias
-   where
-      source = 'client';
-create or replace view view_site_name as
-   select
-      site_id 'site_id',
-      null    'alias_id',
-      name    'name'
-   from site
-   union
-   select
-      id       'site_id',
-      alias_id 'alias_id',
-      name     'name'
-   from
-      alias
-   where
-      source = 'site';
+insert into device_type (name,flags, added_date ) values ( 'Computer',1,now() );

Subversion Repositories computer_asset_manager_v2

(root)/include/camp_v2.sql @ 18 – Rev 11 → 18