forked from reaperzn/auter-checkmate
-
Notifications
You must be signed in to change notification settings - Fork 0
/
auter-checkmate.sql
22 lines (16 loc) · 1.67 KB
/
auter-checkmate.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# Create DB
CREATE DATABASE auter_checkmate;
# Create servers table
CREATE TABLE auter_checkmate.servers (serverid VARCHAR(30) NOT NULL PRIMARY KEY, servername VARCHAR(60) NOT NULL,IP VARCHAR(15) NOT NULL) DEFAULT CHARACTER SET utf8;
# Create statuses table
CREATE TABLE auter_checkmate.statuses (statusid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, statusname VARCHAR(30) NOT NULL, statustext VARCHAR(60) NOT NULL, statusimage VARCHAR(100) NOT NULL) DEFAULT CHARACTER SET utf8;
# Create serverstatus table
create table auter_checkmate.serverstatus (serverid VARCHAR(30) NOT NULL, statusid INT NOT NULL, FOREIGN KEY (serverid) REFERENCES servers(serverid), FOREIGN KEY (statusid) REFERENCES statuses(statusid)) DEFAULT CHARACTER SET utf8;
#Insert default statuses in the statuses table
INSERT INTO auter_checkmate.statuses (statusname, statustext, statusimage) VALUES ('REGISTERED','REGISTERED','/var/www/auter-checkmate/statuses/registered.img'),('PENDING','PENDING','/var/www/auter-checkmate/statuses/pending.img'),('PATCHED','PATCHED','/var/www/auter-checkmate/statuses/patched.img'),('RUNNING','RUNNING','/var/www/auter-checkmate/statuses/running.img'),('REBOOTING','REBOOTING','/var/www/auter-checkmate/statuses/rebooting.img'),('FINALIZING','FINALIZING','/var/www/auter-checkmate/statuses/finalizing.img'),('FAIL','FAIL','/var/www/auter-checkmate/statuses/fail.img');
# Create the mysql user for the application
GRANT SELECT,INSERT,UPDATE ON auter_checkmate.* TO 'auter-checkmate'@'localhost' IDENTIFIED BY 'access';
FLUSH PRIVILEGES;
# Test Data
insert into auter_checkmate.servers values ('test','paolo-auter-checkmate7','192.168.0.1');
insert into auter_checkmate.serverstatus values ('test','1');