Subversion Repositories computer_asset_manager_v2

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
24 rodolico 1 
<html>
2 
   <body>
3 
      <p>
4 
         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.
5 
      </p>
6 
      <p>
7 
         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.
8 
      </p>
9 
      <p>
10 
         This document describes the structure of the basic database, without modules.
11 
      </p>
12 
      <p>
13 
         CAMP has two types of tables; system and data. System tables are preceded by an underscore, and are used for program flow.
14 
      </p>
15 
      <p>
16 
         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.
17 
      </p>
18 
      <table border='1'>
19 
         <tr>
20 
            <td valign='top'>_system</td>
21 
            <td valign='top'>
22 
                configuration of system, similar to an INI file structure. Contains columns group_name, key_name and theValue, corresponding to<br />
23 
                [group_name]<br />
24 
                key_name=theValue<br />
25 
                This is modified programmatically any time a module is added or removed, or if system wide configurations need to be made.
26 
            </td>
27 
         </tr>
28 
         <tr>
29 
            <td valign='top'>_menu</td>
30 
            <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>
31 
         </tr>
32 
         <tr>
33 
            <td valign='top'>_user</td>
34 
            <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>
35 
         </tr>
36 
      </table>
37 
      <p>
38 
         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:
39 
         mark "deleted" row with removed_date = a non-null value
40 
         copy the newly removed row's data to new row, modifying the fields necessary (added_date always)
41 
      </p>
42 
      <p>
43 
         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.
44 
      </p>
45 
      <p>
46 
         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.
47 
      </p>
48 
      <table border='1'>
49 
         <tr>
50 
            <th>Linkage Table</th>
51 
            <th>Connection 1</th>
52 
            <th>Connection 2</th>
53 
            <th>Notes</th>
54 
         </tr>
55 
         <tr>
56 
            <td valign='top'>client_device</td>
57 
            <td valign='top'>client</td>
58 
            <td valign='top'>device</td>
59 
            <td valign='top'>A client "owns" a device, but this can change.</td>
60 
         </tr>
61 
         <tr>
62 
            <td valign='top'>client_site</td>
63 
            <td valign='top'>client</td>
64 
            <td valign='top'>site</td>
65 
            <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>
66 
         </tr>
67 
         <tr>
68 
            <td valign='top'>site_device</td>
69 
            <td valign='top'>site</td>
70 
            <td valign='top'>device</td>
71 
            <td valign='top'>A device is at a particular site (which may not be the owners site)</td>
72 
         </tr>
73 
         <tr>
74 
            <td valign='top'>device_device</td>
75 
            <td valign='top'>device</td>
76 
            <td valign='top'>device (parent)</td>
77 
            <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>
78 
         </tr>
79 
      </table>
80 
      <p>
81 
         The current (2017) plan is that linkage tables will only be one-to-one.
82 
      </p>
83 
      <p>
84 
         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.
85 
      </p>
86 
      <p>
87 
         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.
88 
      </p>
89 
      <p>
90 
         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.
91 
      </p>
92 
      <p>
93 
         See database.png for a diagram of the basic database.
94 
      </p>
95 
      <p>
96 
         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.
97 
      </p>
98 
      <table border='1'>
99 
         <tr>
100 
            <th>
101 
               Table
102 
            </th>
103 
            <th>
104 
               Description
105 
            </th>
106 
         </tr>
107 
         <tr>
108 
            <td valign='top'>
109 
               device
110 
            </td>
111 
            <td valign='top'>
112 
               Holds some basic information about a device; name, device_type and any notes you may want
113 
            </td>
114 
         </tr>
115 
         <tr>
116 
            <td valign='top'>
117 
               client
118 
            </td>
119 
            <td valign='top'>
120 
               Holds information about a particular client, such as name, notes, and private notes which are available to limited visitors
121 
            </td>
122 
         </tr>
123 
         <tr>
124 
            <td valign='top'>
125 
               site
126 
            </td>
127 
            <td valign='top'>
128 
               Physical sites which may house devices. Table holds information such as name and any special notes.
129 
            </td>
130 
         </tr>
131 
         <tr>
132 
            <td valign='top'>
133 
               alias
134 
            </td>
135 
            <td valign='top'>
136 
               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.
137 
               <br />while aliases mainly concern devices, the table is set up to also handle site and client, by the field <b>source</b> which holds the name of the table for which this alias refers.
138 
            </td>
139 
         </tr>
140 
      </table>
141 
   </body>
142 
</html>