1 -- -----------------------------------------------------------------------------
3 -- File: $RCSfile: clearadm.sql,v $
4 -- Revision: $Revision: 1.23 $
5 -- Description: Create the clearadm database
6 -- Author: Andrew@DeFaria.com
7 -- Created: Tue Nov 30 08:46:42 EST 2010
8 -- Modified: $Date: 2011/02/09 13:28:33 $
11 -- Copyright (c) 2010, ClearSCM, Inc., all rights reserved
13 -- -----------------------------------------------------------------------------
14 -- Warning: The following line will delete the old database!
15 -- drop database if exists clearadm;
17 -- Create a new database
18 create database clearadm;
20 -- Now let's focus on this new database
23 -- system: Define what makes up a system or machine
25 name varchar (255) not null,
30 ) not null default 'true',
40 port int default 25327,
41 lastheardfrom datetime,
42 notification varchar (255),
57 ) not null default '6 months',
58 loadavgThreshold float (4,2) default 5.00,
61 ) type=innodb; -- system
63 -- clearcase: Information about a Clearcase system
64 create table clearcase (
65 system varchar (255) not null,
69 registryHost tinytext,
70 mvfsBlocksPerDirectory int,
71 mvfsCleartextMnodes int,
72 mvfsDirectoryNames int,
75 mvfsInitialMnodeTableSize int,
76 mvfsMinCleartextMnodes int,
77 mvfsMinFreeMnodes int,
78 mvfsNamesNotFound int,
82 cleartextIdleLifetime int,
84 cleartextHashTableSize int,
86 threadHashTableSize int,
87 processHashTableSize int,
89 foreign key systemLink (system) references system (name)
93 ) type=innodb; -- clearcase
95 -- package: A package is any software package that we wish to keep track of
96 create table package (
97 system varchar (255) not null,
98 name varchar (255) not null,
99 version tinytext not null,
103 key packageIndex (name),
104 key systemIndex (system),
105 foreign key systemLink (system) references system (name)
108 primary key (system, name)
109 ) type=innodb; -- package
111 -- filesystem: A systems file systems that we are monitoring
112 create table filesystem (
113 system varchar (255) not null,
114 filesystem varchar (255) not null,
115 fstype tinytext not null,
117 threshold int default 90,
118 notification varchar (255),
119 filesystemHist enum (
132 ) not null default '6 months',
134 key filesystemIndex (filesystem),
135 foreign key systemLink (system) references system (name)
138 primary key (system, filesystem)
139 ) type=innodb; -- filesystem
141 -- fs: Contains a snapshot reading of a filesystem at a given date and time
143 system varchar(255) not null,
144 filesystem varchar(255) not null,
145 mount varchar(255) not null,
146 timestamp datetime not null,
152 key mountIndex (mount),
153 primary key (system, filesystem, timestamp),
154 foreign key filesystemLink (system, filesystem)
155 references filesystem (system, filesystem)
160 -- loadavg: Contains a snapshot reading of a system's load average
161 create table loadavg (
162 system varchar(255) not null,
163 timestamp datetime not null,
168 primary key (system, timestamp).
169 foreign key systemLink (system) references system (name)
172 ) type=innodb; -- loadavg
174 -- vobs: Describe a system's vobs
176 system varchar (255) not null,
177 tag varchar (255) not null,
179 key systemIndex (system),
180 foreign key systemLink (system) references system (name)
184 ) type=innodb; -- vob
186 -- view: Describe views
188 system varchar (255) not null,
189 region varchar (255) not null,
190 tag varchar (255) not null,
198 ) not null default 'dynamic',
205 key systemIndex (system),
206 foreign key systemLink (system) references system (name)
209 key regionIndex (region),
210 primary key (region, tag)
211 ) type=innodb; -- view
214 name varchar (255) not null,
215 system varchar (255),
217 command text not null,
221 ) not null default 'true',
224 foreign key systemLink (system) references system (name)
227 ) type=innodb; -- task
229 create table runlog (
230 id int not null auto_increment,
231 task varchar (255) not null,
232 system varchar (255),
238 ) not null default 'false',
242 primary key (id, task, system),
243 foreign key taskLink (task) references task (name)
246 foreign key systemLink (system) references system (name)
249 ) type=innodb; -- runlog
251 create table schedule (
252 name varchar (255) not null,
253 task varchar (255) not null,
254 notification varchar (255) not null,
259 ) not null default 'true',
263 foreign key taskLink (task) references task (name)
266 foreign key notificationLink (notification) references notification (name)
269 ) type=innodb; -- schedule
272 name varchar (255) not null,
277 ) not null default 'email',
281 ) type=innodb; -- alert
283 create table notification (
284 name varchar (255) not null,
285 alert varchar (255) not null,
286 cond tinytext not null,
292 ) not null default 'Once a day',
295 foreign key alertLink (alert) references alert (name)
298 ) type=innodb; -- notification
300 create table alertlog (
301 id int not null auto_increment,
302 alert varchar (255) not null,
303 system varchar (255) not null,
304 notification varchar (255) not null,
309 primary key (id, alert),
311 foreign key alertLink (alert) references alert (name)
314 foreign key notificationLink (notification) references notification (name)
317 foreigh key runlogLink (runlog) references runlog (id)
320 ) type=innodb; -- alertlog