Rev 26 | Blame | Last modification | View Log | Download | RSS feed
<html>
<body>
<p>
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.
</p>
<p>
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.
</p>
<p>
CAMP has two types of tables; system and data. System tables are preceded by an underscore, and are used for program flow.
</p>
<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.
</p>
<table border='1'>
<tr>
<td valign='top'>_system</td>
<td valign='top'>
configuration of system, similar to an INI file structure. Contains columns group_name, key_name and theValue, corresponding to<br />
[group_name]<br />
key_name=theValue<br />
This is modified programmatically any time a module is added or removed, or if system wide configurations need to be made.
</td>
</tr>
<tr>
<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>
</tr>
</table>
<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:
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)
</p>
<p>
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>
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.
</p>
<table border='1'>
<tr>
<th>Linkage Table</th>
<th>Connection 1</th>
<th>Connection 2</th>
<th>Notes</th>
</tr>
<tr>
<td valign='top'>client_device</td>
<td valign='top'>client</td>
<td valign='top'>device</td>
<td valign='top'>A client "owns" a device, but this can change.</td>
</tr>
<tr>
<td valign='top'>client_site</td>
<td valign='top'>client</td>
<td valign='top'>site</td>
<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>
<tr>
<td valign='top'>site_device</td>
<td valign='top'>site</td>
<td valign='top'>device</td>
<td valign='top'>A device is at a particular site (which may not be the owners site)</td>
</tr>
<tr>
<td valign='top'>device_device</td>
<td valign='top'>device</td>
<td valign='top'>device (parent)</td>
<td valign='top'>A device can be part of another device. For example, a USB printer can be part of a computer, or a Xen DOMU may be on a physical DOM0</td>
</tr>
</table>
<p>
The current (2017) plan is that linkage tables will only be one-to-one.
</p>
<p>
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>
Device has three additional fields for unique identification:
<table border='1'>
<tr><th>Name</th><th>Definition</th><th>Notes</th></tr>
<tr>
<td>uuid</td>
<td>varchar(32)</td>
<td>UUID of the machine, if available</td>
</tr>
<tr>
<td>serial</td>
<td>varchar(32)</td>
<td>Manufacturer's Serial Number of machine, if available</td>
</tr>
<tr>
<td>name</td>
<td>varchar(64)</td>
<td>Name of machine (can change)</td>
</tr>
</table>
These are arbitrary, but the combination should uniquely determine any entry. This is designed to allow automated processes to locate a specific machine, and to allow export/import of data in a consistent manner. While UUID may be enough by itself, it is not always unique, even amongst server class machines. All of these may be null except name. NOTE: name can be changed. Internally, devices are identified by the key field, device_id.
</p>
<table border='1'>
<tr>
<th>
Table
</th>
<th>
Description
</th>
</tr>
<tr>
<td valign='top'>
device
</td>
<td valign='top'>
Holds some basic information about a device; name, device_type, uuid and/or serial number
</td>
</tr>
<tr>
<td valign='top'>
client
</td>
<td valign='top'>
Holds the name of particular client (ownership)
</td>
</tr>
<tr>
<td valign='top'>
site
</td>
<td valign='top'>
Physical sites which may house devices. Table holds name.
</td>
</tr>
</table>
<p>
Originally, additional information was to be stored in the device/client/site tables, but they have been removed to decrease the complexity of the base program. Instead, this information will be managed by the modules aliases and notes.
</p>
</body>
</html>