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, region),
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, region),
197 primary key (tag, region, timestamp),
198 foreign key viewLink (tag, region)
199 references view (tag, region)
202 ) engine=innodb; -- viewstorage
204 -- loadavg: Contains a snapshot reading of a system's load average
205 create table loadavg (
206 system varchar(255) not null,
207 timestamp datetime not null,
212 primary key (system, timestamp),
213 foreign key systemLink (system) references system (name)
216 ) engine=innodb; -- loadavg
218 -- vob: Describe a system's vobs
220 tag varchar (255) not null,
221 region varchar (255) not null,
225 derivedobjsmall blob,
231 derivedobjlarge blob,
235 key vobTagIndex (tag),
236 primary key (tag, region)
237 ) engine=innodb; -- vob
239 -- view: Describe views
241 tag varchar (255) not null,
242 region varchar (255) not null,
250 ) not null default 'dynamic',
265 key viewTagIndex (tag),
266 primary key (tag, region)
267 ) engine=innodb; -- view
270 name varchar (255) not null,
271 system varchar (255),
273 command text not null,
277 ) not null default 'true',
280 -- primary key (name),
281 -- foreign key systemLink (system) references system (name)
284 ) engine=innodb; -- task
286 create table runlog (
287 id int not null auto_increment,
288 task varchar (255) not null,
289 system varchar (255),
295 ) not null default 'false',
299 primary key (id, task, system),
300 foreign key taskLink (task) references task (name)
303 foreign key systemLink (system) references system (name)
306 ) engine=innodb; -- runlog
309 name varchar (255) not null,
314 ) not null default 'email',
318 ) engine=innodb; -- alert
320 create table notification (
321 name varchar (255) not null,
322 alert varchar (255) not null,
323 cond tinytext not null,
329 ) not null default 'Once a day',
332 foreign key alertLink (alert) references alert (name)
335 ) engine=innodb; -- notification
337 create table schedule (
338 name varchar (255) not null,
339 task varchar (255) not null,
340 notification varchar (255) not null,
345 ) not null default 'true',
349 foreign key taskLink (task) references task (name)
352 foreign key notificationLink (notification) references notification (name)
355 ) engine=innodb; -- schedule
357 create table alertlog (
358 id int not null auto_increment,
359 alert varchar (255) not null,
360 system varchar (255) not null,
361 notification varchar (255) not null,
366 primary key (id, alert),
368 foreign key alertLink (alert) references alert (name)
371 foreign key notificationLink (notification) references notification (name)
374 foreign key runlogLink (runlog) references runlog (id)
377 ) engine=innodb; -- alertlog