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 ) engine=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 ) engine=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 ) engine=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 ) engine=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)
158 ) engine=innodb; -- fs
160 -- vobstorage: Contains a snapshot of a vob's storage pools at a given date
162 create table vobstorage (
163 tag varchar(255) not null,
164 region varchar(255) not null,
165 timestamp datetime not null,
168 cleartext decimal(10,1),
169 derivedobj decimal(10,1),
170 source decimal(10,1),
173 key vobtagIndex (tag),
174 primary key (tag, region, timestamp)
175 ) engine=innodb; -- vobstorage
177 -- viewstorage: Contains a snapshot of a view's storage pools at a given date
179 create table viewstorage (
180 tag varchar(255) not null,
181 region varchar(255) not null,
182 timestamp datetime not null,
183 private decimal(10,1),
188 key viewtagIndex (tag),
189 primary key (tag, region, timestamp)
190 ) engine=innodb; -- viewstorage
192 -- loadavg: Contains a snapshot reading of a system's load average
193 create table loadavg (
194 system varchar(255) not null,
195 timestamp datetime not null,
200 primary key (system, timestamp),
201 foreign key systemLink (system) references system (name)
204 ) engine=innodb; -- loadavg
206 -- vobs: Describe a system's vobs
208 system varchar (255) not null,
209 tag varchar (255) not null,
211 key systemIndex (system),
212 foreign key systemLink (system) references system (name)
216 ) engine=innodb; -- vob
218 -- view: Describe views
220 system varchar (255) not null,
221 region varchar (255) not null,
222 tag varchar (255) not null,
230 ) not null default 'dynamic',
237 key systemIndex (system),
238 foreign key systemLink (system) references system (name)
241 key regionIndex (region),
242 primary key (region, tag)
243 ) engine=innodb; -- view
246 name varchar (255) not null,
247 system varchar (255),
249 command text not null,
253 ) not null default 'true',
256 -- primary key (name),
257 -- foreign key systemLink (system) references system (name)
260 ) engine=innodb; -- task
262 create table runlog (
263 id int not null auto_increment,
264 task varchar (255) not null,
265 system varchar (255),
271 ) not null default 'false',
275 primary key (id, task, system),
276 foreign key taskLink (task) references task (name)
279 foreign key systemLink (system) references system (name)
282 ) engine=innodb; -- runlog
285 name varchar (255) not null,
290 ) not null default 'email',
294 ) engine=innodb; -- alert
296 create table notification (
297 name varchar (255) not null,
298 alert varchar (255) not null,
299 cond tinytext not null,
305 ) not null default 'Once a day',
308 foreign key alertLink (alert) references alert (name)
311 ) engine=innodb; -- notification
313 create table schedule (
314 name varchar (255) not null,
315 task varchar (255) not null,
316 notification varchar (255) not null,
321 ) not null default 'true',
325 foreign key taskLink (task) references task (name)
328 foreign key notificationLink (notification) references notification (name)
331 ) engine=innodb; -- schedule
333 create table alertlog (
334 id int not null auto_increment,
335 alert varchar (255) not null,
336 system varchar (255) not null,
337 notification varchar (255) not null,
342 primary key (id, alert),
344 foreign key alertLink (alert) references alert (name)
347 foreign key notificationLink (notification) references notification (name)
350 foreign key runlogLink (runlog) references runlog (id)
353 ) engine=innodb; -- alertlog