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 -- 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 ) engine=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 ) engine=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 ) engine=innodb; -- view
214 name varchar (255) not null,
215 system varchar (255),
217 command text not null,
221 ) not null default 'true',
224 -- primary key (name),
225 -- foreign key systemLink (system) references system (name)
228 ) engine=innodb; -- task
230 create table runlog (
231 id int not null auto_increment,
232 task varchar (255) not null,
233 system varchar (255),
239 ) not null default 'false',
243 primary key (id, task, system),
244 foreign key taskLink (task) references task (name)
247 foreign key systemLink (system) references system (name)
250 ) engine=innodb; -- runlog
253 name varchar (255) not null,
258 ) not null default 'email',
262 ) engine=innodb; -- alert
264 create table notification (
265 name varchar (255) not null,
266 alert varchar (255) not null,
267 cond tinytext not null,
273 ) not null default 'Once a day',
276 foreign key alertLink (alert) references alert (name)
279 ) engine=innodb; -- notification
281 create table schedule (
282 name varchar (255) not null,
283 task varchar (255) not null,
284 notification varchar (255) not null,
289 ) not null default 'true',
293 foreign key taskLink (task) references task (name)
296 foreign key notificationLink (notification) references notification (name)
299 ) engine=innodb; -- schedule
301 create table alertlog (
302 id int not null auto_increment,
303 alert varchar (255) not null,
304 system varchar (255) not null,
305 notification varchar (255) not null,
310 primary key (id, alert),
312 foreign key alertLink (alert) references alert (name)
315 foreign key notificationLink (notification) references notification (name)
318 foreign key runlogLink (runlog) references runlog (id)
321 ) engine=innodb; -- alertlog