6 |
rodolico |
1 |
/*
|
|
|
2 |
mySQL Database for Computer Asset Management Program
|
|
|
3 |
No records are deleted by default. If record is to be deleted
|
|
|
4 |
it is marked as removed (removed_date not null)
|
18 |
rodolico |
5 |
When joining tables (ie client_device) is modified, the old record is
|
6 |
rodolico |
6 |
marked removed and a new record added, to give history of device
|
|
|
7 |
*/
|
|
|
8 |
|
|
|
9 |
/* used as a configuration file for the app as a whole */
|
18 |
rodolico |
10 |
drop table if exists _system;
|
|
|
11 |
create table _system (
|
6 |
rodolico |
12 |
_system_id int unsigned not null auto_increment,
|
|
|
13 |
group_name varchar(64) NOT NULL COMMENT 'used to group keys together',
|
|
|
14 |
key_name varchar(64) NOT NULL COMMENT 'key into this value',
|
|
|
15 |
theValue text null COMMENT 'the actual value of this entry',
|
18 |
rodolico |
16 |
added_date date not null COMMENT 'date record was added',
|
|
|
17 |
removed_date date default NULL COMMENT 'date record was closed',
|
6 |
rodolico |
18 |
PRIMARY KEY (_system_id )
|
|
|
19 |
) COMMENT='Basically a configuration file equivilent to a windows INI ';
|
|
|
20 |
|
|
|
21 |
/* used by the auth class */
|
18 |
rodolico |
22 |
drop table if exists _user;
|
|
|
23 |
create table _user (
|
|
|
24 |
_user_id int unsigned not null auto_increment,
|
|
|
25 |
username varchar(32) comment 'user name for logging in',
|
6 |
rodolico |
26 |
name varchar(64) comment 'common name of user',
|
|
|
27 |
email varchar(64) comment 'email address of user',
|
|
|
28 |
notes text comment 'any notes about user',
|
11 |
rodolico |
29 |
pass varchar(256) comment 'encrypted password of user',
|
6 |
rodolico |
30 |
access text comment 'sql to determine what records user can view',
|
18 |
rodolico |
31 |
added_date date not null comment 'Date record added to database',
|
|
|
32 |
removed_date date default null comment 'Date record marked as removed',
|
|
|
33 |
primary key ( _user_id )
|
6 |
rodolico |
34 |
) comment 'user access to program';
|
|
|
35 |
|
|
|
36 |
/* used by the menu class */
|
18 |
rodolico |
37 |
drop table if exists _menu;
|
|
|
38 |
create table _menu (
|
|
|
39 |
_menu_id int unsigned not null auto_increment,
|
|
|
40 |
parent_id int unsigned null comment 'If this is a submenu the id of the parent' REFERENCES _menu(_menu_id),
|
6 |
rodolico |
41 |
caption varchar(20) not null comment 'The actual caption displayed',
|
|
|
42 |
url varchar(120) null comment 'the url of the page/script to call or null if this contains sub-options',
|
18 |
rodolico |
43 |
primary key (_menu_id)
|
6 |
rodolico |
44 |
) comment 'We keep the entire menu structure here so modules can modify it';
|
|
|
45 |
|
|
|
46 |
|
18 |
rodolico |
47 |
/* beginning of the actual tables used by the app, client, site and device */
|
6 |
rodolico |
48 |
|
18 |
rodolico |
49 |
/*
|
|
|
50 |
A device is owned by a client (entity). This is a list of available
|
|
|
51 |
clients who can own things.
|
|
|
52 |
*/
|
|
|
53 |
drop table if exists client;
|
|
|
54 |
create table client (
|
6 |
rodolico |
55 |
client_id int unsigned not null auto_increment,
|
|
|
56 |
name varchar(64) comment 'Name of the client',
|
|
|
57 |
notes text comment 'Any notes you want to enter',
|
18 |
rodolico |
58 |
added_date date not null comment 'Date record added to database',
|
|
|
59 |
removed_date date default null comment 'Date record marked as removed',
|
6 |
rodolico |
60 |
primary key (client_id)
|
|
|
61 |
) comment 'Stores information about a particlar client/owner';
|
|
|
62 |
|
18 |
rodolico |
63 |
/*
|
|
|
64 |
A device can be on a site. This is a list of sites which can house
|
|
|
65 |
devices
|
|
|
66 |
*/
|
|
|
67 |
drop table if exists site;
|
|
|
68 |
create table site (
|
6 |
rodolico |
69 |
site_id int unsigned not null auto_increment,
|
|
|
70 |
name varchar(64) comment 'Name of the site',
|
|
|
71 |
notes text comment 'Any notes you want to enter',
|
18 |
rodolico |
72 |
added_date date not null comment 'Date record added to database',
|
|
|
73 |
removed_date date default null comment 'Date record marked as removed',
|
6 |
rodolico |
74 |
primary key (site_id)
|
|
|
75 |
) comment 'Stores information about a particlar physical site';
|
|
|
76 |
|
18 |
rodolico |
77 |
/*
|
|
|
78 |
The basis for the program. In our context, an asset is a computer or
|
|
|
79 |
other device.
|
|
|
80 |
*/
|
|
|
81 |
drop table if exists device;
|
|
|
82 |
create table device (
|
|
|
83 |
device_id int unsigned not null auto_increment,
|
|
|
84 |
name varchar(64) comment 'name of the device or device',
|
6 |
rodolico |
85 |
notes text comment 'any notes we want to store',
|
18 |
rodolico |
86 |
device_type_id int unsigned not null references device_type( device_type_id ),
|
|
|
87 |
added_date date not null comment 'Date record added to database',
|
|
|
88 |
removed_date date default null comment 'Date record marked as removed',
|
|
|
89 |
primary key (device_id)
|
|
|
90 |
) comment 'stores information about an individual device or other device';
|
6 |
rodolico |
91 |
|
18 |
rodolico |
92 |
/*
|
|
|
93 |
allows devices to be related to each other, ie a "part of" scheme, where
|
|
|
94 |
for example, a virtualized server may be 'part of' a physical machine.
|
|
|
95 |
By using a child table, we can track movement across this relationship
|
|
|
96 |
*/
|
|
|
97 |
drop table if exists device_device;
|
|
|
98 |
create table device_device (
|
|
|
99 |
device_device_id int unsigned not null auto_increment,
|
|
|
100 |
source int unsigned not null references device( device_id ),
|
|
|
101 |
target int unsigned not null references device( device_id ),
|
|
|
102 |
added_date date not null comment 'Date record added to database',
|
|
|
103 |
removed_date date default null comment 'Date record marked as removed',
|
|
|
104 |
primary key (device_device_id)
|
|
|
105 |
) comment 'joins device to another device';
|
6 |
rodolico |
106 |
|
|
|
107 |
/*
|
18 |
rodolico |
108 |
device_type is a child table of device, determining what type of device it is]
|
6 |
rodolico |
109 |
such as computer, printer, router, whatever.
|
|
|
110 |
flags currently used as 0 is non system and 1 is system, though this could
|
|
|
111 |
be expanded in the future
|
|
|
112 |
*/
|
18 |
rodolico |
113 |
drop table if exists device_type;
|
|
|
114 |
create table device_type (
|
|
|
115 |
device_type_id int(10) unsigned NOT NULL auto_increment,
|
6 |
rodolico |
116 |
name varchar(64) not null COMMENT 'the visible displayed name',
|
18 |
rodolico |
117 |
flags int unsigned default 1 comment 'flags for this device type',
|
6 |
rodolico |
118 |
added_date date not null COMMENT 'date record was added',
|
|
|
119 |
removed_date date default NULL COMMENT 'date record was deleted/supserceded',
|
18 |
rodolico |
120 |
primary key (device_type_id)
|
6 |
rodolico |
121 |
) comment='simply a list of device types ie computer printer whatever';
|
|
|
122 |
|
|
|
123 |
|
|
|
124 |
/*
|
|
|
125 |
this is actually a join with multiple tables, depending on what the
|
18 |
rodolico |
126 |
name is associated with, client, site or device
|
6 |
rodolico |
127 |
for example, if 'id' is client_id from the client table, then 'source'
|
|
|
128 |
would be 'client' (name of the table);
|
|
|
129 |
*/
|
18 |
rodolico |
130 |
drop table if exists alias;
|
|
|
131 |
create table alias (
|
6 |
rodolico |
132 |
alias_id int unsigned not null auto_increment,
|
|
|
133 |
source varchar(64) comment 'the table this alias comes from',
|
18 |
rodolico |
134 |
id int unsigned not null comment 'the client, site or device id',
|
|
|
135 |
name varchar(64) comment 'the alias for the device',
|
|
|
136 |
primary key (alias_id)
|
|
|
137 |
) comment 'Allows client, site and device to have multiple names';
|
6 |
rodolico |
138 |
|
18 |
rodolico |
139 |
/*
|
|
|
140 |
links a site to a client. A site is "owned" by a client
|
|
|
141 |
*/
|
|
|
142 |
drop table if exists client_site;
|
|
|
143 |
create table client_site (
|
6 |
rodolico |
144 |
client_site_id int unsigned not null auto_increment,
|
|
|
145 |
client_id int unsigned not null references client( client_id ),
|
|
|
146 |
site_id int unsigned not null references site( site_id ),
|
18 |
rodolico |
147 |
added_date date not null comment 'Date record added to database',
|
|
|
148 |
removed_date date default null comment 'Date record marked as removed',
|
|
|
149 |
primary key (client_site_id)
|
6 |
rodolico |
150 |
) comment 'A client owns a site';
|
|
|
151 |
|
18 |
rodolico |
152 |
/*
|
|
|
153 |
A device is owned by a client. Ownership can be tracked by removing
|
|
|
154 |
a device from one client and adding it to another.
|
|
|
155 |
*/
|
|
|
156 |
drop table if exists client_device;
|
|
|
157 |
create table client_device (
|
|
|
158 |
client_device_id int unsigned not null auto_increment,
|
6 |
rodolico |
159 |
client_id int unsigned not null references client( client_id ),
|
18 |
rodolico |
160 |
device_id int unsigned not null references device( device_id ),
|
|
|
161 |
added_date date not null comment 'Date record added to database',
|
|
|
162 |
removed_date date default null comment 'Date record marked as removed',
|
|
|
163 |
primary key (client_device_id)
|
|
|
164 |
) comment 'Links client and device tables';
|
6 |
rodolico |
165 |
|
18 |
rodolico |
166 |
/*
|
|
|
167 |
A device is at a location. Location history can be tracked by removing
|
|
|
168 |
and adding a device to a new location
|
|
|
169 |
*/
|
|
|
170 |
drop table if exists site_device;
|
|
|
171 |
create table site_device (
|
|
|
172 |
site_device_id int unsigned not null auto_increment,
|
6 |
rodolico |
173 |
site_id int unsigned not null references site( site_id ),
|
18 |
rodolico |
174 |
device_id int unsigned not null references device( device_id ),
|
|
|
175 |
added_date date not null comment 'Date record added to database',
|
|
|
176 |
removed_date date default null comment 'Date record marked as removed',
|
|
|
177 |
primary key ( site_device_id )
|
|
|
178 |
) comment 'Links site and device tables';
|
6 |
rodolico |
179 |
|
|
|
180 |
/* add some indexes */
|
18 |
rodolico |
181 |
alter table device add index (added_date,removed_date);
|
|
|
182 |
alter table device add index (removed_date);
|
|
|
183 |
alter table device add index (name);
|
|
|
184 |
alter table device add index (device_type_id);
|
6 |
rodolico |
185 |
|
18 |
rodolico |
186 |
alter table client_site add index ( client_id,site_id );
|
|
|
187 |
|
6 |
rodolico |
188 |
alter table site add index (removed_date);
|
|
|
189 |
|
|
|
190 |
alter table client add index (removed_date);
|
|
|
191 |
|
18 |
rodolico |
192 |
/* preload some data */
|
6 |
rodolico |
193 |
|
18 |
rodolico |
194 |
/* basic menu; home and logout */
|
|
|
195 |
insert into _menu values ( null, null, 'Home Page', 'index.html' );
|
|
|
196 |
insert into _menu values ( null, null, 'Log Out', 'logout.html' );
|
6 |
rodolico |
197 |
|
18 |
rodolico |
198 |
/* one user, admin, with no password for initial access */
|
|
|
199 |
insert into _user( username,added_date) values ( 'admin', now() );
|
6 |
rodolico |
200 |
|
18 |
rodolico |
201 |
insert into device_type (name,flags, added_date ) values ( 'Computer',1,now() );
|