1 system-- -----------------------------------------------------------------------------
3 -- File: $RCSfile: machines.sql,v $
4 -- Revision: $Revision: 1.0 $
5 -- Description: Create the machines database
6 -- Author: Andrew@DeFaria.com
7 -- Created: Fri Apr 4 10:31:11 PDT 2014
11 -- Copyright (c) 2014, ClearSCM, Inc., all rights reserved
13 -- -----------------------------------------------------------------------------
14 -- Warning: The following line will delete the old database!
15 drop database if exists machines;
17 -- Create a new database
18 create database machines;
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 lastheardfrom datetime,
55 ) not null default '6 months',
56 loadavgThreshold float (4,2) default 5.00,
59 ) engine=innodb; -- system
61 -- package: A package is any software package that we wish to keep track of
62 create table package (
63 system varchar (255) not null,
64 name varchar (255) not null,
65 version tinytext not null,
69 key packageIndex (name),
70 key systemIndex (system),
71 foreign key systemLink (system) references system (name)
74 primary key (system, name)
75 ) engine=innodb; -- package
77 -- filesystem: A systems file systems that we are monitoring
78 create table filesystem (
79 system varchar (255) not null,
80 filesystem varchar (255) not null,
81 fstype tinytext not null,
83 threshold int default 90,
84 notification varchar (255),
98 ) not null default '6 months',
100 key filesystemIndex (filesystem),
101 foreign key systemLink (system) references system (name)
104 primary key (system, filesystem)
105 ) engine=innodb; -- filesystem
107 -- fs: Contains a snapshot reading of a filesystem at a given date and time
109 system varchar(255) not null,
110 filesystem varchar(255) not null,
111 mount varchar(255) not null,
112 timestamp datetime not null,
118 key mountIndex (mount),
119 primary key (system, filesystem, timestamp),
120 foreign key filesystemLink (system, filesystem)
121 references filesystem (system, filesystem)
124 ) engine=innodb; -- fs
126 -- loadavg: Contains a snapshot reading of a system's load average
127 create table loadavg (
128 system varchar(255) not null,
129 timestamp datetime not null,
134 primary key (system, timestamp),
135 foreign key systemLink (system) references system (name)
138 ) engine=innodb; -- loadavg