WebSVN – computer_asset_manager_v2 – Diff – /head/installer/camp2.sql

 /*
+Remove everything wit
+drop table if exists _config;
+drop table if exists _menu;
+drop table if exists _permissions;
+drop table if exists _permissions_categories;
+drop table if exists _system;
+drop table if exists _users_permissions;
+drop view if exists _view_users_permissions;
+drop table if exists client;
+drop table if exists client_device;
+drop table if exists client_site;
+drop table if exists device;
+drop table if exists device_device;
+drop table if exists device_device_type;
+drop table if exists device_type;
+drop table if exists site;
+drop table if exists site_device;
+drop table if exists temp;
+drop view if exists view_device_types;
+drop view if exists view_device_site_client_type;
+*/
+/*
    The base install of CAMP2.
    Just track devices. A device can be any arbitrary item.
-   A device has one owner, and is located at one site (location)
+   A device has one owner, and is located at one location (location)
-   A site also has an owner. Devices can be located on locations
+   A location also has an owner. Devices can be located on locations
-   owned by a different client, ie when a device is lent to a client
+   owned by a different owner, ie when a device is lent to a owner
    or colocated at a third party NOC.
    This is very basic. It only tracks device ownership and locations,
    and movement between them (via the created and removed fields).
    When a device is moved to a different location, or sold to a different
-   client, the old record in the linking field has its removed field
+   owner, the old record in the linking field has its removed field
    updated and a new record created with a created date. With this, we
    can track the lifespan of a device.
    It is assumed various modules will be created to extend the capabilities
    of this basic structure. However, modules should not modify the basic
 create table _menu (
    _menu_id       int unsigned not null auto_increment,
    parent_id      int unsigned default null references _menu (_menu_id),
    caption        varchar(20) not null comment 'Caption displayed for menu',
    url            varchar(64) default null comment 'optional URL when they click here',
+   shortname      varchar(16) default null comment 'used for permissions to determine if we display',
    primary key    (_menu_id)
 ) comment = 'Menus for the application';
+/*
+   insert the menu options for the main program
+*/
-insert into camp2._menu values (null,null,'Home', '/index.php');
+insert into camp2._menu values (null,null,'Home', '/index.php', 'menu_home' );
+insert into camp2._menu select null,_menu_id,'Owners', '/index.php?action=owner', 'menu_owner' from camp2._menu where caption = 'Home';
+insert into camp2._menu select null,_menu_id,'Locations', '/index.php?action=location', 'menu_location' from camp2._menu where caption = 'Home';
+insert into camp2._menu select null,_menu_id,'Devices', '/index.php?action=device', 'menu_device' from camp2._menu where caption = 'Home';
+insert into camp2._menu values (null,null,'Reports', '/index.php?action=report', 'menu_report' );
 /*
    simple table to hold ownership information
 */
-drop table if exists client;
+drop table if exists owner;
-create table client (
+create table owner (
-   client_id      int unsigned not null auto_increment,
+   owner_id       int unsigned not null auto_increment,
-   name           varchar(64) comment 'name of client',
+   name           varchar(64) comment 'name of owner',
    created        date comment 'date record was created',
    removed        date comment 'date record was removed',
-   primary key    (client_id)
+   primary key    (owner_id)
-) comment 'hold client information';
+) comment 'hold owner information';
 /*
-   simple table to hold site where a device is located
+   simple table to hold location where a device is located
 */
-drop table if exists site;
+drop table if exists location;
-create table site (
+create table location (
-   site_id        int unsigned not null auto_increment,
+   location_id    int unsigned not null auto_increment,
-   name           varchar(64) comment 'name of site',
+   name           varchar(64) comment 'name of location',
    created        date comment 'date record was created',
    removed        date comment 'date record was removed',
-   primary key    (site_id)
+   primary key    (location_id)
-) comment 'hold site information';
+) comment 'hold location information';
 /*
    table which holds a device type, such as server, workstation
    printer, virtual, etc...
 */
 drop table if exists device_type;
 create table device_type (
    device_type_id int unsigned not null auto_increment,
    name           varchar(64) comment 'name of device type',
-   show_as_system boolean comment 'if true, this is a system, ie a computer or virtual',
+   is_system      boolean comment 'if true, this is a system, ie a computer or virtual',
    created        date comment 'date record was created',
    removed        date comment 'date record was removed',
    primary key    (device_type_id)
 ) comment 'simple child table to determine the type of device we have';
    device_type_id int unsigned not null references device_type( device_type_id ),
    primary key (device_id,device_type_id)
 ) comment 'many to many join for device and device_type tables';
 /*
-   Set ownership of a site. These records are not deleted, but by
+   Set ownership of a location. These records are not deleted, but by
    setting field removed to non-null value, then creating a new record,
-   we can track ownership of sites.
+   we can track ownership of locations.
 */
-drop table if exists client_site;
+drop table if exists owner_location;
-create table client_site (
+create table owner_location (
-   client_site_id int unsigned not null auto_increment,
+   owner_location_id int unsigned not null auto_increment,
-   client_id      int unsigned not null references client( client_id ),
+   owner_id      int unsigned not null references owner( owner_id ),
-   site_id        int unsigned not null references site( site_id ),
+   location_id        int unsigned not null references location( location_id ),
    created        date comment 'date record was created',
    removed        date comment 'date record was removed',
-   index          site_device ( client_id,site_id ),
+   index          location_device ( owner_id,location_id ),
-   primary key    (client_site_id)
+   primary key    (owner_location_id)
-) comment 'links ownership of a site to a client';
+) comment 'links ownership of a location to a owner';
 /*
    Set location of a device. These records are not deleted, but by
    setting field removed to non-null value, then creating a new record,
    we can track movement of devices.
 */
-drop table if exists site_device;
+drop table if exists location_device;
-create table site_device (
+create table location_device (
-   site_device_id int unsigned not null auto_increment,
+   location_device_id int unsigned not null auto_increment,
-   site_id        int unsigned not null references site( site_id ),
+   location_id        int unsigned not null references location( location_id ),
    device_id      int unsigned not null references device( device_id ),
    created        date comment 'date record was created',
    removed        date comment 'date record was removed',
-   index          site_device ( site_id,device_id ),
+   index          location_device ( location_id,device_id ),
-   primary key    (site_device_id)
+   primary key    (location_device_id)
 ) comment 'links a device to its location';
 /*
    Set ownership of a device. These records are not deleted, but by
    setting field removed to non-null value, then creating a new record,
    we can track ownership of devices.
 */
-drop table if exists client_device;
+drop table if exists owner_device;
-create table client_device (
+create table owner_device (
-   client_device_id  int unsigned not null auto_increment,
+   owner_device_id  int unsigned not null auto_increment,
-   client_id      int unsigned not null references client( client_id ),
+   owner_id      int unsigned not null references owner( owner_id ),
    device_id      int unsigned not null references device( device_id ),
    created        date comment 'date record was created',
    removed        date comment 'date record was removed',
-   index          client_device( client_id, device_id ),
+   index          owner_device( owner_id, device_id ),
-   primary key    ( client_device_id )
+   primary key    ( owner_device_id )
 ) comment 'links a device to its owner';
 /*
    There can be a parent/child relationship with devices. For example, a virtual
    resides on a DOM0, so the parent_id of a DOMU is the device_id of the DOM0.
 */
 /*
   a simple view that concats the values in device_device_type for
   display. Since mySQL will not allow subqueries in views, required
-  to have this information in view_device_site_client_type
+  to have this information in view_device_location_owner_type
 */
-create view view_device_types as
+create or replace view view_device_types as
    select
       device_id,
       group_concat(distinct device_type.name) as device_types
    from
       device_device_type
 /*
    Mongo view that gets all the information together to display
    device name, location, owner and type(s)
 */
-drop view if exists view_device_site_client_type;
-create view view_device_site_client_type as
+create or replace view view_device_location_owner_type as
    select
       device.device_id device_id,
       device.uuid uuid,
       device.serial serial,
       device.name device,
       device.created device_created,
       device.removed device_removed,
       view_device_types.device_types,
-      site.site_id site_id,
+      location.location_id location_id,
-      site.name site,
+      location.name location,
-      site.created site_created,
+      location.created location_created,
-      site.removed site_removed,
+      location.removed location_removed,
-      client.client_id client_id,
+      owner.owner_id owner_id,
-      client.name client,
+      owner.name owner,
-      client.created client_created,
+      owner.created owner_created,
-      client.removed client_removed
+      owner.removed owner_removed
    from
       device
       join view_device_types using (device_id )
-      join site_device using (device_id)
+      join location_device using (device_id)
-      join site using (site_id)
+      join location using (location_id)
-      join client_device using (device_id)
+      join owner_device using (device_id)
-      join client using (client_id);
+      join owner using (owner_id);
 insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' )
    on duplicate key update key_value = '0.1';

Subversion Repositories computer_asset_manager_v2

(root)/head/installer/camp2.sql @ 52 – Rev 40 → 46