WebSVN – computer_asset_manager_v2 – Diff – /head/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)
+   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 */
 )  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,
+   _user_id       int unsigned not null auto_increment,
-   username     varchar(32) not null comment 'user name for logging in',
+   username       varchar(32) not null comment 'user name for logging in',
-   name         varchar(64) comment 'common name of user',
+   name           varchar(64) comment 'common name of user',
-   email        varchar(64) comment 'email address of user',
+   email          varchar(64) comment 'email address of user',
-   notes        text comment 'any notes about user',
+   notes          text comment 'any notes about user',
-   passwd       varchar(256) comment 'encrypted password of user',
+   passwd         varchar(256) comment 'encrypted password of user',
-   access       text comment 'sql to determine what records user can view',
+   access         text comment 'sql to determine what records user can view',
-   added_date   date not null comment 'Date record added to database',
+   added          date not null comment 'Date record added to database',
-   removed_date date default null comment 'Date record marked as removed',
+   removed        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 table _menu (
-   _menu_id        int unsigned not null auto_increment,
+   _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';
    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,
+   client_id      int unsigned not null auto_increment,
-   name        varchar(64) comment 'Name of the client',
+   name           varchar(64) comment 'Name of the client',
-   notes       text comment 'Any notes you want to enter',
+   notes          text comment 'Any notes you want to enter',
    internal_notes text comment 'private notes accessible only to technicians',
-   added_date  date not null comment 'Date record added to database',
+   added          date not null comment 'Date record added to database',
-   removed_date date default null comment 'Date record marked as removed',
+   removed        date default null comment 'Date record marked as removed',
-   primary key (client_id)
+   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',
+   added       date not null comment 'Date record added to database',
-   removed_date date default null comment 'Date record marked as removed',
+   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,
+   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',
+   added       date not null comment 'Date record added to database',
-   removed_date date default null comment 'Date record marked as removed',
+   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
    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 ),
+   device_id   int unsigned not null references device( device_id ),
-   parent_id    int unsigned not null references device( device_id ),
+   parent_id   int unsigned not null references device( device_id ),
-   added_date  date not null comment 'Date record added to database',
+   added       date not null comment 'Date record added to database',
-   removed_date date default null comment 'Date record marked as removed',
+   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]
    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',
+  name         varchar(64) not null COMMENT 'the visible displayed name',
-  flags           int unsigned default 1 comment 'flags for this device type',
+  flags        int unsigned default 1 comment 'flags for this device type',
-  added_date      date not null COMMENT 'date record was added',
+  added        date not null COMMENT 'date record was added',
-  removed_date    date default NULL COMMENT 'date record was deleted/supserceded',
+  removed      date default NULL COMMENT 'date record was deleted/supserceded',
-  primary key     (device_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
    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',
+   source      varchar(64) comment 'the table this alias comes from',
-   id       int unsigned not null comment 'the client, site or device id',
+   id          int unsigned not null comment 'the client, site or device id',
-   name         varchar(64) comment 'the alias for the device',
+   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',
+   added       date not null comment 'Date record added to database',
-   removed_date date default null comment 'Date record marked as removed',
+   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
 */
 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 ),
+   device_id   int unsigned not null references device( device_id ),
-   added_date  date not null comment 'Date record added to database',
+   added       date not null comment 'Date record added to database',
-   removed_date date default null comment 'Date record marked as removed',
+   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
 */
 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 ),
+   device_id   int unsigned not null references device( device_id ),
-   added_date  date not null comment 'Date record added to database',
+   added       date not null comment 'Date record added to database',
-   removed_date date default null comment 'Date record marked as removed',
+   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
+      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
+      and device.removed is null
+      and client.removed is null;
 /* add some indexes */
-alter table device add index (added_date,removed_date);
+alter table device add index (added,removed);
-alter table device add index (removed_date);
+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_date);
+alter table site add index (removed);
-alter table client add index (removed_date);
+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_date) values ( 'admin', now() );
+insert into _user( username,added) values ( 'admin', now() );
-/* insert into device_type (name,flags, added_date ) values ( 'Computer',1,now() ); */
+/* insert into device_type (name,flags, added ) values ( 'Computer',1,now() ); */

Subversion Repositories computer_asset_manager_v2

(root)/head/include/camp_v2.sql @ 45 – Rev 26 → 38