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,
63 ) engine=innodb; -- system
65 -- clearcase: Information about a Clearcase system
66 create table clearcase (
67 system varchar (255) not null,
71 registryHost tinytext,
72 mvfsBlocksPerDirectory int,
73 mvfsCleartextMnodes int,
74 mvfsDirectoryNames int,
77 mvfsInitialMnodeTableSize int,
78 mvfsMinCleartextMnodes int,
79 mvfsMinFreeMnodes int,
80 mvfsNamesNotFound int,
84 cleartextIdleLifetime int,
86 cleartextHashTableSize int,
88 threadHashTableSize int,
89 processHashTableSize int,
91 foreign key systemLink (system) references system (name)
95 ) engine=innodb; -- clearcase
97 -- package: A package is any software package that we wish to keep track of
98 create table package (
99 system varchar (255) not null,
100 name varchar (255) not null,
101 version tinytext not null,
105 key packageIndex (name),
106 key systemIndex (system),
107 foreign key systemLink (system) references system (name)
110 primary key (system, name)
111 ) engine=innodb; -- package
113 -- filesystem: A systems file systems that we are monitoring
114 create table filesystem (
115 system varchar (255) not null,
116 filesystem varchar (255) not null,
117 fstype tinytext not null,
119 threshold int default 90,
120 notification varchar (255),
121 filesystemHist enum (
134 ) not null default '6 months',
138 key filesystemIndex (filesystem),
139 foreign key systemLink (system) references system (name)
142 primary key (system, filesystem)
143 ) engine=innodb; -- filesystem
145 -- fs: Contains a snapshot reading of a filesystem at a given date and time
147 system varchar(255) not null,
148 filesystem varchar(255) not null,
149 mount varchar(255) not null,
150 timestamp datetime not null,
156 key mountIndex (mount),
157 primary key (system, filesystem, timestamp),
158 foreign key filesystemLink (system, filesystem)
159 references filesystem (system, filesystem)
162 ) engine=innodb; -- fs
164 -- vobstorage: Contains a snapshot of a vob's storage pools at a given date
166 create table vobstorage (
167 tag varchar(255) not null,
168 region varchar(255) not null,
169 timestamp datetime not null,
172 cleartext decimal(10,1),
173 derivedobj decimal(10,1),
174 source decimal(10,1),
177 key vobtagIndex (tag),
178 primary key (tag, region, timestamp)
179 foreign key vobLink (tag, region)
180 references vob (tag, region)
183 ) engine=innodb; -- vobstorage
185 -- viewstorage: Contains a snapshot of a view's storage pools at a given date
187 create table viewstorage (
188 tag varchar(255) not null,
189 region varchar(255) not null,
190 timestamp datetime not null,
191 private decimal(10,1),
196 key viewtagIndex (tag),
197 primary key (tag, region, timestamp)
198 ) engine=innodb; -- viewstorage
200 -- loadavg: Contains a snapshot reading of a system's load average
201 create table loadavg (
202 system varchar(255) not null,
203 timestamp datetime not null,
208 primary key (system, timestamp),
209 foreign key systemLink (system) references system (name)
212 ) engine=innodb; -- loadavg
214 -- vob: Describe a system's vobs
216 tag varchar (255) not null,
217 region varchar (255) not null,
221 derivedobjsmall blob,
227 derivedobjlarge blob,
231 key vobTagIndex (tag),
232 primary key (tag, region)
233 ) engine=innodb; -- vob
235 -- view: Describe views
237 tag varchar (255) not null,
238 region varchar (255) not null,
246 ) not null default 'dynamic',
261 key viewTagIndex (tag),
262 primary key (tag, region)
263 ) engine=innodb; -- view
266 name varchar (255) not null,
267 system varchar (255),
269 command text not null,
273 ) not null default 'true',
276 -- primary key (name),
277 -- foreign key systemLink (system) references system (name)
280 ) engine=innodb; -- task
282 create table runlog (
283 id int not null auto_increment,
284 task varchar (255) not null,
285 system varchar (255),
291 ) not null default 'false',
295 primary key (id, task, system),
296 foreign key taskLink (task) references task (name)
299 foreign key systemLink (system) references system (name)
302 ) engine=innodb; -- runlog
305 name varchar (255) not null,
310 ) not null default 'email',
314 ) engine=innodb; -- alert
316 create table notification (
317 name varchar (255) not null,
318 alert varchar (255) not null,
319 cond tinytext not null,
325 ) not null default 'Once a day',
328 foreign key alertLink (alert) references alert (name)
331 ) engine=innodb; -- notification
333 create table schedule (
334 name varchar (255) not null,
335 task varchar (255) not null,
336 notification varchar (255) not null,
341 ) not null default 'true',
345 foreign key taskLink (task) references task (name)
348 foreign key notificationLink (notification) references notification (name)
351 ) engine=innodb; -- schedule
353 create table alertlog (
354 id int not null auto_increment,
355 alert varchar (255) not null,
356 system varchar (255) not null,
357 notification varchar (255) not null,
362 primary key (id, alert),
364 foreign key alertLink (alert) references alert (name)
367 foreign key notificationLink (notification) references notification (name)
370 foreign key runlogLink (runlog) references runlog (id)
373 ) engine=innodb; -- alertlog