1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
|
CREATE DATABASE IF NOT EXISTS scire;
GRANT SELECT, INSERT, UPDATE, DELETE ON scire.* TO 'hobbit' IDENTIFIED BY 'moria';
GRANT SELECT, INSERT, UPDATE, DELETE ON scire.* TO 'hobbit'@'localhost' IDENTIFIED BY 'moria';
USE scire;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
userid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(64) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
email VARCHAR(128) NOT NULL,
phone VARCHAR(128) NULL,
pager VARCHAR(128) NULL,
real_name VARCHAR(255),
comment VARCHAR(255)
) ENGINE = MyISAM;
DROP TABLE IF EXISTS clients;
CREATE TABLE clients (
clientid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
assetid VARCHAR(64) UNIQUE,
digest VARCHAR(128) NOT NULL UNIQUE,
cert TEXT,
hostname VARCHAR(64) NOT NULL,
mac VARCHAR(17) NOT NULL,
ip VARCHAR(15) NOT NULL,
gli_profile INT,
osid INT,
status VARCHAR(20),
contact INT,
installtime TIMESTAMP NOT NULL DEFAULT NOW(), # date the clients first was set up
FOREIGN KEY (osid) REFERENCES os.osid,
FOREIGN KEY (gli_profile) REFERENCES GLI_profiles.profileid,
FOREIGN KEY (contact) REFERENCES users.userid
) ENGINE = MyISAM;
DROP TABLE IF EXISTS permissions;
CREATE TABLE permissions (
permid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(128) NOT NULL UNIQUE,
description VARCHAR(255),
creator INT NOT NULL,
FOREIGN KEY (creator) REFERENCES users.userid
) ENGINE = MyISAM;
DROP TABLE IF EXISTS jobs;
CREATE TABLE jobs (
jobid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
priority INT NOT NULL DEFAULT 0,
created TIMESTAMP NOT NULL DEFAULT NOW(),
creator INT NOT NULL,
permission INT NOT NULL,
script INT NOT NULL,
description VARCHAR(255),
pending INT,
failed INT,
INDEX (creator),
FOREIGN KEY (creator) REFERENCES users.userid,
FOREIGN KEY (permission) REFERENCES permissions.permid,
FOREIGN KEY (script) REFERENCES scripts.scriptid
) ENGINE = MyISAM;
DROP TABLE IF EXISTS job_history;
CREATE TABLE job_history (
jobid INT NOT NULL,
clientid INT NOT NULL,
eventtime TIMESTAMP NOT NULL DEFAULT NOW(),
status VARCHAR(20) NOT NULL,
eventmsg VARCHAR(255),
PRIMARY KEY (jobid, clientid, eventtime),
FOREIGN KEY (jobid) REFERENCES jobs.jobid,
FOREIGN KEY (clientid) REFERENCES clients.clientid
) ENGINE = MyISAM;
DROP TABLE IF EXISTS jobs_clients;
CREATE TABLE jobs_clients (
jobid INT NOT NULL,
clientid INT,
groupid INT,
PRIMARY KEY (jobid, clientid, groupid),
FOREIGN KEY (jobid) REFERENCES jobs.jobid,
FOREIGN KEY (groupid) REFERENCES groups.gropuid,
FOREIGN KEY (clientid) REFERENCES clients.clientid
) ENGINE = MyISAM;
# Either clienid or groupid is required, if 1 is provided the other MUST be NULL
DROP TABLE IF EXISTS job_conditions;
CREATE TABLE job_conditions (
jobid INT NOT NULL,
clientid INT NOT NULL,
job_dependency INT NOT NULL,
start_time TIMESTAMP,
start_period TIMESTAMP,
end_period TIMESTAMP,
run_interval INT, #in seconds?
# conditions here (TBD)
# other types of dependencies:
# right now time and proccess (other jobs)
# might also include data (partition full?)
PRIMARY KEY (jobid,clientid),
FOREIGN KEY (jobid) REFERENCES jobs.jobid,
FOREIGN KEY (clientid) REFERENCES clients.clientid
) ENGINE = MyISAM;
DROP TABLE IF EXISTS scripts;
CREATE TABLE scripts (
scriptid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(128) NOT NULL,
description VARCHAR(255),
location VARCHAR(255),
data TEXT,
log_location VARCHAR(255),
success_code VARCHAR(32),
run_as VARCHAR(255)
) ENGINE = MyISAM;
DROP TABLE IF EXISTS os;
CREATE TABLE os (
osid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
osname VARCHAR(128) NOT NULL,
update_script INT,
install_script INT,
uninstall_script INT,
rollback_script INT,
packagelist_script INT,
FOREIGN KEY (update_script) REFERENCES scripts.scriptid,
FOREIGN KEY (install_script) REFERENCES scripts.scriptid,
FOREIGN KEY (uninstall_script) REFERENCES scripts.scriptid,
FOREIGN KEY (rollback_script) REFERENCES scripts.scriptid,
FOREIGN KEY (packagelist_script) REFERENCES scripts.scriptid
) ENGINE = MYISAM;
DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions (
sessionid VARCHAR(255) NOT NULL DEFAULT '' PRIMARY KEY,
expiration INT(10) UNSIGNED NOT NULL DEFAULT '0',
data TEXT
) ENGINE = MyISAM;
DROP TABLE IF EXISTS settings;
CREATE TABLE settings (
userid INT NOT NULL,
setting_name VARCHAR(64),
setting_value VARCHAR(255),
PRIMARY KEY (userid, setting_name),
FOREIGN KEY (userid) REFERENCES users.userid
) ENGINE = MyISAM;
DROP TABLE IF EXISTS hardware;
CREATE TABLE hardware (
clientid INT NOT NULL PRIMARY KEY,
processor VARCHAR(32),
memory VARCHAR(32), # size of the installed memory in MB
hd VARCHAR(32), # size of harddisk in MB
# partitions: data about the partitions
cpu VARCHAR(64), # type of cpu
mhz VARCHAR(32), # speed of the cpu
# netcards: product names of the installed network cards
# graficcard: information about the grafic card
# soundcard: name of the sound card
# isa: information about ISA components
# dmi: DMI information
# ram ,
#.. steal the rest from Zen or m23
FOREIGN KEY (clientid) REFERENCES clients.clientid
) ENGINE = MyISAM;
DROP TABLE IF EXISTS hardware_history;
CREATE TABLE hardware_history (
clientid INT NOT NULL,
changedate TIMESTAMP NOT NULL DEFAULT NOW(),
field_name VARCHAR(30),
oldvalue VARCHAR(255),
newvalue VARCHAR(255),
PRIMARY KEY (clientid,changedate),
FOREIGN KEY (clientid) REFERENCES clients.clientid
) ENGINE = MyISAM;
DROP TABLE IF EXISTS software;
CREATE TABLE software (
clientid INT NOT NULL,
package VARCHAR(128) NOT NULL PRIMARY KEY,
current_ver VARCHAR(64),
rollback_ver VARCHAR(64),
#dependencies
FOREIGN KEY (clientid) REFERENCES clients.clientid
) ENGINE = MyISAM;
DROP TABLE IF EXISTS GLI_profiles;
CREATE TABLE GLI_profiles (
profileid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
profile_name VARCHAR(255) NOT NULL UNIQUE,
location VARCHAR(255) NOT NULL,
description VARCHAR(255)
) ENGINE = MyISAM;
|
GitWeb