Subversion Repositories computer_asset_manager_v2

CAMP is designed to be run with a relational database which is supported by the PHP PDO class. It has been developed and tested using MariaDB.

         CAMP is designed to be run with a relational database which is supported by the PHP PDO class. It has been developed and tested using MariaDB.

         Version 2 is a complete rewrite, with emphasis on getting the basic system right, then adding functionality through modules. Thus, the decision was made to only include the minimum functionality required to track an asset (called a device in the app) through sites and clients. Since modules are not allowed to modify the structure of any tables, this basic database structure will be static, requiring a major revision to modify.

Version 2 is a complete rewrite, with emphasis on getting the basic system right, then adding functionality through modules. Thus, the decision was made to only include the minimum functionality required to track an asset (called a device in the app) through sites and clients. Since modules are not allowed to modify the structure of any tables, this basic database structure will be static, requiring a major revision to modify.

      </p>

      <p>

This document describes the structure of the basic database, without modules.

         This document describes the structure of the basic database, without modules.

      <p>

CAMP has two types of tables; system and data. System tables are preceded by an underscore, and are used for program flow.

         CAMP has two types of tables; system and data. System tables are preceded by an underscore, and are used for program flow.

      <p>

System tables can have rows edited, updated, added and deleted as necessary. For example, adding a new module often requires adding rows to _system and _menu, and removing a module will remove those entries.

         System tables can have rows edited, updated, added and deleted as necessary. For example, adding a new module often requires adding rows to _system and _menu, and removing a module will remove those entries.

      </p>

_system - configuration of system, similar to an INI file structure

                configuration of system, similar to an INI file structure. Contains columns group_name, key_name and theValue, corresponding to<br />

_menu - describes the menuing system used in the program

                key_name=theValue<br />

_user - defines access to the program, but see below

            <td valign='top'>_menu</td>

            <td valign='top'>describes the menuing system used in the program. This is based on the library DBHierarchialHash (DBMenu extends it). Each entry is a menu, with a (possibly null) parent, a caption, and a URL to go to if the menu item is selected.<br />This is modified by modules, with menu items added when a module is activated</td>

_user entries should never be removed as the can be linked to various modules. Thus, it has two date fields; added_date and removed_date to allow removal from access while maintaining structual integrity.

            <td valign='top'>defines access to the program. However, _user entries should never be removed as the can be linked to various modules. Thus, it has two date fields; added_date and removed_date to allow removal from access while maintaining structual integrity.</td>

      <p>

The remaining tables should never have rows deleted under normal circumstances, in or to track history and/or maintain data integrity. They have two additional date fields, added_date and removed_date. An edit of any of these tables proceeds as follows:

         The remaining tables should never have rows deleted under normal circumstances, in or to track history and/or maintain data integrity. They have two additional date fields, added_date and removed_date. An edit of any of these tables proceeds as follows:

mark "deleted" row with removed_date = a non-null value

         mark "deleted" row with removed_date = a non-null value

copy the newly removed row's data to new row, modifying the fields necessary (added_date always)

         copy the newly removed row's data to new row, modifying the fields necessary (added_date always)

         The basis of the whole system is the device (aka asset). An asset has an owner (client) and a location (site). Since we don't want to lose historical data, the linkage between devices, clients and sites is done through join tables, named by the tables they join, thus, client_device links the client and device tables. Additionally, a site is owned by a client, so there is a client_site table.

The basis of the whole system is the device (aka asset). An asset has an owner (client) and a location (site). Since we don't want to lose historical data, the linkage between devices, clients and sites is done through join tables, named by the tables they join, thus, client_device links the client and device tables. Additionally, a site is owned by a client, so there is a client_site table.

      </p>

      <p>

device <- client_device -> client

            <th>Linkage Table</th>

device <- client_site -> site

            <th>Notes</th>

site <- client_site -> client

            <td valign='top'>client</td>

         <tr>

A device can also be a part of another device in the table. An example is a virtual machine which is hosted by a physical machine. In this case, we use a separate table to mark the "part of" relationship.

            <td valign='top'>A client "owns" a site. This client is not necessarily the same as the owner of a device on that site</td>

         <tr>

device <- device_device -> device

            <td valign='top'>device_device</td>

      <p>

The current (2017) plan is that linkage tables will only be one-to-one.

         The current (2017) plan is that linkage tables will only be one-to-one.

         A device also has one child table, device_type. device_type allows us to categorize devices by function; router, server, workstation, virtual. This relationship does not involve link tables; one of the columns in device is device_type_id, so a device can only have one type.

A device also has one child table, device_type. device_type allows us to categorize devices by function; router, server, workstation, virtual. This relationship does not involve link tables; one of the columns in device is device_type_id, so a device can only have one type.

      </p>

      <p>

Finally, there is one weird table, alias. I found computer names change over a period of time, and there can actually be multiple names for a device at the same time, especially when automated reporting is done.

         Finally, there is one weird table, alias. I found computer names change over a period of time, and there can actually be multiple names for a device at the same time, especially when automated reporting is done.

         alias is not a true relational table. Instead, it tracks aliases for devices, sites or clients, depending on the source column; a 64 varchar which is designed to store the table name it is aliasing. This is a kludge to solve a problem that exists in real life, but has no elegant solution in a DBMS.

alias is not a true relational table. Instead, it tracks aliases for devices, sites or clients, depending on the source column; a 64 varchar which is designed to store the table name it is aliasing. This is a kludge to solve a problem that exists in real life, but has no elegant solution in a DBMS.

      </p>

      <p>

See database.png for a diagram of the basic database.

         See database.png for a diagram of the basic database.

         IMPORTANT: This document describes the actual database layout. However, CAMP does not read the layout from the database. Instead, it uses a hash to describe the database and some PHP classes that automate a lot of the screens. After the database is defined, the hash must be created. See DatabaseDefinition.php in the include directory.

IMPORTANT: This document describes the actual database layout. However, CAMP does not read the layout from the database. Instead, it uses a hash to describe the database and some PHP classes that automate a lot of the screens. After the database is defined, the hash must be created. See DatabaseDefinition.php in the include directory.

      <table border='1'>

               things get renamed occasionally. Sites may go by one or two names, clients can change names, and devices especially can change names when pulled and redeployed. However, they are still the same item. This is especially important when automated processes (backups, sysinfo) can update the database and use a different name than is stored her, or when a device has a name change.