/[scire]/docs/scire.sql
Gentoo

Contents of /docs/scire.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 230 - (show annotations) (download)
Thu Jul 19 01:21:32 2007 UTC (11 years ago) by codeman
File size: 15621 byte(s)
adding a comma

1 CREATE DATABASE IF NOT EXISTS scire;
2 GRANT SELECT, INSERT, UPDATE, DELETE ON scire.* TO 'hobbit' IDENTIFIED BY 'moria';
3 GRANT SELECT, INSERT, UPDATE, DELETE ON scire.* TO 'hobbit'@'localhost' IDENTIFIED BY 'moria';
4 ALTER DATABASE scire DEFAULT CHARACTER SET latin1;
5 USE scire;
6
7 DROP TABLE IF EXISTS users;
8 CREATE TABLE users (
9 userid INT NOT NULL PRIMARY KEY,
10 username VARCHAR(64) NOT NULL UNIQUE,
11 password VARCHAR(255) NOT NULL,
12 email VARCHAR(128) NOT NULL,
13 phone VARCHAR(128) NULL,
14 pager VARCHAR(128) NULL,
15 real_name VARCHAR(255),
16 comment VARCHAR(255)
17 ) ENGINE = MyISAM;
18
19 DROP TABLE IF EXISTS clients;
20 CREATE TABLE clients (
21 clientid INT NOT NULL PRIMARY KEY,
22 assetid VARCHAR(64) UNIQUE,
23 digest VARCHAR(128) UNIQUE,
24 cert TEXT,
25 hostname VARCHAR(64) NOT NULL,
26 mac VARCHAR(17) NOT NULL,
27 ip VARCHAR(15) NOT NULL,
28 gli_profile INT,
29 osid INT,
30 status INT,
31 contact INT,
32 installtime TIMESTAMP NOT NULL DEFAULT NOW(), # date the clients first was set up
33 FOREIGN KEY (osid) REFERENCES os.osid,
34 FOREIGN KEY (status) REFERENCES client_status.statusid,
35 FOREIGN KEY (gli_profile) REFERENCES GLI_profiles.profileid,
36 FOREIGN KEY (contact) REFERENCES users.userid
37 ) ENGINE = MyISAM;
38
39 DROP TABLE IF EXISTS client_status;
40 CREATE TABLE client_status (
41 statusid INT NOT NULL,
42 statusname VARCHAR(30),
43 PRIMARY KEY (statusid)
44 ) ENGINE = MyISAM;
45
46
47 DROP TABLE IF EXISTS permissions;
48 CREATE TABLE permissions (
49 permid INT NOT NULL PRIMARY KEY,
50 name VARCHAR(128) NOT NULL UNIQUE,
51 description VARCHAR(255),
52 permcategory VARCHAR(128) NOT NULL DEFAULT 'Default',
53 creator INT NOT NULL,
54 created TIMESTAMP NOT NULL DEFAULT NOW(),
55 FOREIGN KEY (creator) REFERENCES users.userid
56 ) ENGINE = MyISAM;
57
58 DROP TABLE IF EXISTS jobs;
59 CREATE TABLE jobs (
60 jobid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
61 priority INT NOT NULL DEFAULT 0,
62 created TIMESTAMP NOT NULL DEFAULT NOW(),
63 creator INT NOT NULL,
64 permission INT NOT NULL,
65 script INT NOT NULL,
66 description VARCHAR(255),
67 pending INT,
68 failed INT,
69 INDEX (creator),
70 FOREIGN KEY (creator) REFERENCES users.userid,
71 FOREIGN KEY (permission) REFERENCES permissions.permid,
72 FOREIGN KEY (script) REFERENCES scripts.scriptid
73 ) ENGINE = MyISAM;
74
75 DROP TABLE IF EXISTS job_history;
76 CREATE TABLE job_history (
77 jobid INT NOT NULL,
78 clientid INT NOT NULL,
79 eventtime TIMESTAMP NOT NULL DEFAULT NOW(),
80 statusid INT NOT NULL,
81 eventmsg VARCHAR(255),
82 PRIMARY KEY (jobid, clientid, eventtime),
83 FOREIGN KEY (jobid) REFERENCES jobs.jobid,
84 FOREIGN KEY (clientid) REFERENCES clients.clientid,
85 FOREIGN KEY (statusid) REFERENCES jobs_status.statusid
86 ) ENGINE = MyISAM;
87
88 DROP TABLE IF EXISTS jobs_status;
89 CREATE TABLE jobs_status (
90 statusid INT NOT NULL,
91 statusname VARCHAR(30),
92 PRIMARY KEY (statusid)
93 ) ENGINE = MyISAM;
94
95
96 DROP TABLE IF EXISTS jobs_clients;
97 CREATE TABLE jobs_clients (
98 jobid INT NOT NULL,
99 clientid INT,
100 groupid INT,
101 PRIMARY KEY (jobid, clientid, groupid),
102 FOREIGN KEY (jobid) REFERENCES jobs.jobid,
103 FOREIGN KEY (groupid) REFERENCES groups.gropuid,
104 FOREIGN KEY (clientid) REFERENCES clients.clientid
105 ) ENGINE = MyISAM;
106 # Either clienid or groupid is required, if 1 is provided the other MUST be NULL
107
108 DROP TABLE IF EXISTS job_conditions;
109 CREATE TABLE job_conditions (
110 jobid INT NOT NULL,
111 job_dependency INT NOT NULL,
112 deploy_time TIMESTAMP,
113 expiration_time TIMESTAMP,
114 run_schedule VARCHAR(255), #a cron-like string showing the schedule to run. min interval of 1 minute.
115 validity_period INT, # how many minutes will the job be available
116 last_run INT,
117 last_run_date TIMESTAMP,
118 # conditions here (TBD)
119 # other types of dependencies:
120 # right now time and proccess (other jobs)
121 # might also include data (partition full?)
122
123 PRIMARY KEY (jobid),
124 FOREIGN KEY (jobid) REFERENCES jobs.jobid,
125 ) ENGINE = MyISAM;
126
127 DROP TABLE IF EXISTS scripts;
128 CREATE TABLE scripts (
129 scriptid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
130 name VARCHAR(128) NOT NULL,
131 description VARCHAR(255),
132 location VARCHAR(255),
133 script_data TEXT,
134 log_location VARCHAR(255),
135 success_code VARCHAR(32),
136 run_as VARCHAR(255),
137 priority INT,
138 permission INT,
139 pp_location VARCHAR(255),
140 pp_script_data TEXT,
141 return_output INT DEFAULT 0,
142 FOREIGN KEY (permission) REFERENCES permissions.permid
143 ) ENGINE = MyISAM;
144
145 DROP TABLE IF EXISTS os;
146 CREATE TABLE os (
147 osid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
148 osname VARCHAR(128) NOT NULL,
149 update_script INT,
150 install_script INT,
151 uninstall_script INT,
152 rollback_script INT,
153 packagelist_script INT,
154 FOREIGN KEY (update_script) REFERENCES scripts.scriptid,
155 FOREIGN KEY (install_script) REFERENCES scripts.scriptid,
156 FOREIGN KEY (uninstall_script) REFERENCES scripts.scriptid,
157 FOREIGN KEY (rollback_script) REFERENCES scripts.scriptid,
158 FOREIGN KEY (packagelist_script) REFERENCES scripts.scriptid
159 ) ENGINE = MyISAM;
160
161 DROP TABLE IF EXISTS sessions;
162 CREATE TABLE sessions (
163 sessionid VARCHAR(255) NOT NULL DEFAULT '' PRIMARY KEY,
164 expiration INT(10) UNSIGNED NOT NULL DEFAULT '0',
165 data TEXT
166 ) ENGINE = MyISAM;
167
168 DROP TABLE IF EXISTS settings;
169 CREATE TABLE settings (
170 userid INT NOT NULL,
171 setting_name VARCHAR(64),
172 setting_value VARCHAR(255),
173 PRIMARY KEY (userid, setting_name),
174 FOREIGN KEY (userid) REFERENCES users.userid
175
176 ) ENGINE = MyISAM;
177
178 DROP TABLE IF EXISTS hardware;
179 CREATE TABLE hardware (
180 clientid INT NOT NULL PRIMARY KEY,
181 processor VARCHAR(32),
182 memory VARCHAR(32), # size of the installed memory in MB
183 hd VARCHAR(32), # size of harddisk in MB
184 # partitions: data about the partitions
185 cpu VARCHAR(64), # type of cpu
186 mhz VARCHAR(32), # speed of the cpu
187 # netcards: product names of the installed network cards
188 # graficcard: information about the grafic card
189 # soundcard: name of the sound card
190 # isa: information about ISA components
191 # dmi: DMI information
192 # ram ,
193 #.. steal the rest from Zen or m23
194 FOREIGN KEY (clientid) REFERENCES clients.clientid
195 ) ENGINE = MyISAM;
196
197 DROP TABLE IF EXISTS hardware_history;
198 CREATE TABLE hardware_history (
199 clientid INT NOT NULL,
200 changedate TIMESTAMP NOT NULL DEFAULT NOW(),
201 field_name VARCHAR(30),
202 oldvalue VARCHAR(255),
203 newvalue VARCHAR(255),
204 PRIMARY KEY (clientid,changedate),
205 FOREIGN KEY (clientid) REFERENCES clients.clientid
206 ) ENGINE = MyISAM;
207
208 DROP TABLE IF EXISTS software;
209 CREATE TABLE software (
210 clientid INT NOT NULL,
211 package VARCHAR(128) NOT NULL PRIMARY KEY,
212 current_ver VARCHAR(64),
213 rollback_ver VARCHAR(64),
214 #dependencies
215 FOREIGN KEY (clientid) REFERENCES clients.clientid
216 ) ENGINE = MyISAM;
217
218
219
220 DROP TABLE IF EXISTS GLI_profiles;
221 CREATE TABLE GLI_profiles (
222 profileid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
223 profile_name VARCHAR(255) NOT NULL UNIQUE,
224 location VARCHAR(255) NOT NULL,
225 description VARCHAR(255)
226 ) ENGINE = MyISAM;
227
228 DROP TABLE IF EXISTS dyn_tags;
229 CREATE TABLE dyn_tags (
230 jobid INT NOT NULL,
231 tag VARCHAR(30) NOT NULL,
232 tag_value VARCHAR(255),
233 PRIMARY KEY (jobid, tag),
234 FOREIGN KEY (jobid) REFERENCES jobs.jobid,
235 ) ENGINE = MyISAM;
236
237 drop table if exists modules;
238 create table modules (
239 id INT NOT NULL default '0',
240 name VARCHAR(50),
241 long_name VARCHAR(150),
242 description VARCHAR(250),
243 path VARCHAR(90),
244 author VARCHAR(50),
245 distribution VARCHAR(50),
246 category VARCHAR(30),
247 homepage VARCHAR(90),
248 PRIMARY KEY (id)
249 ) TYPE=MyISAM;
250
251
252 #########PHPGACL CODE##########
253 DROP TABLE IF EXISTS gacl_phpgacl;
254 CREATE TABLE gacl_phpgacl (
255 name VARCHAR(230) NOT NULL,
256 value VARCHAR(230) NOT NULL,
257 PRIMARY KEY (name)
258 );
259
260 DELETE FROM gacl_phpgacl WHERE name='version';
261
262 INSERT INTO gacl_phpgacl (name,value) VALUES ('version','3.3.6');
263
264 DELETE FROM gacl_phpgacl WHERE name='schema_version';
265
266 INSERT INTO gacl_phpgacl (name,value) VALUES ('schema_version','2.1');
267
268 DROP TABLE IF EXISTS gacl_acl;
269 CREATE TABLE gacl_acl (
270 id INTEGER NOT NULL DEFAULT 0,
271 section_value VARCHAR(230) NOT NULL DEFAULT 'system',
272 allow INTEGER NOT NULL DEFAULT 0,
273 enabled INTEGER NOT NULL DEFAULT 0,
274 return_value TEXT,
275 note TEXT,
276 updated_date INTEGER NOT NULL DEFAULT 0,
277 PRIMARY KEY (id)
278 );
279
280 ALTER TABLE gacl_acl ADD INDEX gacl_enabled_acl (enabled);
281
282 ALTER TABLE gacl_acl ADD INDEX gacl_section_value_acl (section_value);
283
284 ALTER TABLE gacl_acl ADD INDEX gacl_updated_date_acl (updated_date);
285
286 DROP TABLE IF EXISTS gacl_acl_sections;
287 CREATE TABLE gacl_acl_sections (
288 id INTEGER NOT NULL DEFAULT 0,
289 value VARCHAR(230) NOT NULL,
290 order_value INTEGER NOT NULL DEFAULT 0,
291 name VARCHAR(230) NOT NULL,
292 hidden INTEGER NOT NULL DEFAULT 0,
293 PRIMARY KEY (id)
294 );
295
296 ALTER TABLE gacl_acl_sections ADD UNIQUE INDEX gacl_value_acl_sections (value);
297
298 ALTER TABLE gacl_acl_sections ADD INDEX gacl_hidden_acl_sections (hidden);
299
300 DELETE FROM gacl_acl_sections WHERE id=1 AND value='system';
301
302 INSERT INTO gacl_acl_sections (id,value,order_value,name) VALUES (1,'system',1,'System');
303
304 DELETE FROM gacl_acl_sections WHERE id=2 AND value='user';
305
306 INSERT INTO gacl_acl_sections (id,value,order_value,name) VALUES (2,'user',2,'User');
307
308 DROP TABLE IF EXISTS gacl_aco;
309 CREATE TABLE gacl_aco (
310 id INTEGER NOT NULL DEFAULT 0,
311 section_value VARCHAR(240) NOT NULL DEFAULT '0',
312 value VARCHAR(240) NOT NULL,
313 order_value INTEGER NOT NULL DEFAULT 0,
314 name VARCHAR(255) NOT NULL,
315 hidden INTEGER NOT NULL DEFAULT 0,
316 PRIMARY KEY (id)
317 );
318
319 ALTER TABLE gacl_aco ADD UNIQUE INDEX gacl_section_value_value_aco (section_value, value);
320
321 ALTER TABLE gacl_aco ADD INDEX gacl_hidden_aco (hidden);
322
323 DROP TABLE IF EXISTS gacl_aco_map;
324 CREATE TABLE gacl_aco_map (
325 acl_id INTEGER NOT NULL DEFAULT 0,
326 section_value VARCHAR(230) NOT NULL DEFAULT '0',
327 value VARCHAR(230) NOT NULL,
328 PRIMARY KEY (acl_id, section_value, value)
329 );
330
331 DROP TABLE IF EXISTS gacl_aco_sections;
332 CREATE TABLE gacl_aco_sections (
333 id INTEGER NOT NULL DEFAULT 0,
334 value VARCHAR(230) NOT NULL,
335 order_value INTEGER NOT NULL DEFAULT 0,
336 name VARCHAR(230) NOT NULL,
337 hidden INTEGER NOT NULL DEFAULT 0,
338 PRIMARY KEY (id)
339 );
340
341 ALTER TABLE gacl_aco_sections ADD UNIQUE INDEX gacl_value_aco_sections (value);
342
343 ALTER TABLE gacl_aco_sections ADD INDEX gacl_hidden_aco_sections (hidden);
344
345 DROP TABLE IF EXISTS gacl_aro;
346 CREATE TABLE gacl_aro (
347 id INTEGER NOT NULL DEFAULT 0,
348 section_value VARCHAR(240) NOT NULL DEFAULT '0',
349 value VARCHAR(240) NOT NULL,
350 order_value INTEGER NOT NULL DEFAULT 0,
351 name VARCHAR(255) NOT NULL,
352 hidden INTEGER NOT NULL DEFAULT 0,
353 PRIMARY KEY (id)
354 );
355
356 ALTER TABLE gacl_aro ADD UNIQUE INDEX gacl_section_value_value_aro (section_value, value);
357
358 ALTER TABLE gacl_aro ADD INDEX gacl_hidden_aro (hidden);
359
360 DROP TABLE IF EXISTS gacl_aro_map;
361 CREATE TABLE gacl_aro_map (
362 acl_id INTEGER NOT NULL DEFAULT 0,
363 section_value VARCHAR(230) NOT NULL DEFAULT '0',
364 value VARCHAR(230) NOT NULL,
365 PRIMARY KEY (acl_id, section_value, value)
366 );
367
368 DROP TABLE IF EXISTS gacl_aro_sections;
369 CREATE TABLE gacl_aro_sections (
370 id INTEGER NOT NULL DEFAULT 0,
371 value VARCHAR(230) NOT NULL,
372 order_value INTEGER NOT NULL DEFAULT 0,
373 name VARCHAR(230) NOT NULL,
374 hidden INTEGER NOT NULL DEFAULT 0,
375 PRIMARY KEY (id)
376 );
377
378 ALTER TABLE gacl_aro_sections ADD UNIQUE INDEX gacl_value_aro_sections (value);
379
380 ALTER TABLE gacl_aro_sections ADD INDEX gacl_hidden_aro_sections (hidden);
381
382 DROP TABLE IF EXISTS gacl_axo;
383 CREATE TABLE gacl_axo (
384 id INTEGER NOT NULL DEFAULT 0,
385 section_value VARCHAR(240) NOT NULL DEFAULT '0',
386 value VARCHAR(240) NOT NULL,
387 order_value INTEGER NOT NULL DEFAULT 0,
388 name VARCHAR(255) NOT NULL,
389 hidden INTEGER NOT NULL DEFAULT 0,
390 PRIMARY KEY (id)
391 );
392
393 ALTER TABLE gacl_axo ADD UNIQUE INDEX gacl_section_value_value_axo (section_value, value);
394
395 ALTER TABLE gacl_axo ADD INDEX gacl_hidden_axo (hidden);
396
397 DROP TABLE IF EXISTS gacl_axo_map;
398 CREATE TABLE gacl_axo_map (
399 acl_id INTEGER NOT NULL DEFAULT 0,
400 section_value VARCHAR(230) NOT NULL DEFAULT '0',
401 value VARCHAR(230) NOT NULL,
402 PRIMARY KEY (acl_id, section_value, value)
403 );
404
405 DROP TABLE IF EXISTS gacl_axo_sections;
406 CREATE TABLE gacl_axo_sections (
407 id INTEGER NOT NULL DEFAULT 0,
408 value VARCHAR(230) NOT NULL,
409 order_value INTEGER NOT NULL DEFAULT 0,
410 name VARCHAR(230) NOT NULL,
411 hidden INTEGER NOT NULL DEFAULT 0,
412 PRIMARY KEY (id)
413 );
414
415 ALTER TABLE gacl_axo_sections ADD UNIQUE INDEX gacl_value_axo_sections (value);
416
417 ALTER TABLE gacl_axo_sections ADD INDEX gacl_hidden_axo_sections (hidden);
418
419 DROP TABLE IF EXISTS gacl_aro_groups;
420 CREATE TABLE gacl_aro_groups (
421 id INTEGER NOT NULL DEFAULT 0,
422 parent_id INTEGER NOT NULL DEFAULT 0,
423 lft INTEGER NOT NULL DEFAULT 0,
424 rgt INTEGER NOT NULL DEFAULT 0,
425 name VARCHAR(255) NOT NULL,
426 value VARCHAR(255) NOT NULL,
427 PRIMARY KEY (id, value)
428 );
429
430 ALTER TABLE gacl_aro_groups ADD INDEX gacl_parent_id_aro_groups (parent_id);
431
432 ALTER TABLE gacl_aro_groups ADD UNIQUE INDEX gacl_value_aro_groups (value);
433
434 ALTER TABLE gacl_aro_groups ADD INDEX gacl_lft_rgt_aro_groups (lft, rgt);
435
436 DROP TABLE IF EXISTS gacl_groups_aro_map;
437 CREATE TABLE gacl_groups_aro_map (
438 group_id INTEGER NOT NULL DEFAULT 0,
439 aro_id INTEGER NOT NULL DEFAULT 0,
440 PRIMARY KEY (group_id, aro_id)
441 );
442
443 ALTER TABLE gacl_groups_aro_map ADD INDEX gacl_aro_id (aro_id);
444
445 DROP TABLE IF EXISTS gacl_aro_groups_map;
446 CREATE TABLE gacl_aro_groups_map (
447 acl_id INTEGER NOT NULL DEFAULT 0,
448 group_id INTEGER NOT NULL DEFAULT 0,
449 PRIMARY KEY (acl_id, group_id)
450 );
451
452 DROP TABLE IF EXISTS gacl_axo_groups;
453 CREATE TABLE gacl_axo_groups (
454 id INTEGER NOT NULL DEFAULT 0,
455 parent_id INTEGER NOT NULL DEFAULT 0,
456 lft INTEGER NOT NULL DEFAULT 0,
457 rgt INTEGER NOT NULL DEFAULT 0,
458 name VARCHAR(255) NOT NULL,
459 value VARCHAR(255) NOT NULL,
460 PRIMARY KEY (id, value)
461 );
462
463 ALTER TABLE gacl_axo_groups ADD INDEX gacl_parent_id_axo_groups (parent_id);
464
465 ALTER TABLE gacl_axo_groups ADD UNIQUE INDEX gacl_value_axo_groups (value);
466
467 ALTER TABLE gacl_axo_groups ADD INDEX gacl_lft_rgt_axo_groups (lft, rgt);
468
469 DROP TABLE IF EXISTS gacl_groups_axo_map;
470 CREATE TABLE gacl_groups_axo_map (
471 group_id INTEGER NOT NULL DEFAULT 0,
472 axo_id INTEGER NOT NULL DEFAULT 0,
473 PRIMARY KEY (group_id, axo_id)
474 );
475
476 ALTER TABLE gacl_groups_axo_map ADD INDEX gacl_axo_id (axo_id);
477
478 DROP TABLE IF EXISTS gacl_axo_groups_map;
479 CREATE TABLE gacl_axo_groups_map (
480 acl_id INTEGER NOT NULL DEFAULT 0,
481 group_id INTEGER NOT NULL DEFAULT 0,
482 PRIMARY KEY (acl_id, group_id)
483 );
484
485 /*
486 DROP TABLE IF EXISTS `gacl_acl_seq`;
487 CREATE TABLE `gacl_acl_seq` (
488 `id` int(11) NOT NULL default '0'
489 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
490
491
492 DROP TABLE IF EXISTS `gacl_aco_sections_seq`;
493 CREATE TABLE `gacl_aco_sections_seq` (
494 `id` int(11) NOT NULL default '0'
495 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
496
497 DROP TABLE IF EXISTS `gacl_aco_seq`;
498 CREATE TABLE `gacl_aco_seq` (
499 `id` int(11) NOT NULL default '0'
500 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
501
502 DROP TABLE IF EXISTS `gacl_aro_groups_id_seq`;
503 CREATE TABLE `gacl_aro_groups_id_seq` (
504 `id` int(11) NOT NULL default '0'
505 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
506
507 DROP TABLE IF EXISTS `gacl_aro_sections_seq`;
508 CREATE TABLE `gacl_aro_sections_seq` (
509 `id` int(11) NOT NULL default '0'
510 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
511
512 DROP TABLE IF EXISTS `gacl_aro_seq`;
513 CREATE TABLE `gacl_aro_seq` (
514 `id` int(11) NOT NULL default '0'
515 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
516
517 DROP TABLE IF EXISTS `gacl_axo_groups_id_seq`;
518 CREATE TABLE `gacl_axo_groups_id_seq` (
519 `id` int(11) NOT NULL default '0'
520 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
521
522 DROP TABLE IF EXISTS `gacl_axo_sections_seq`;
523 CREATE TABLE `gacl_axo_sections_seq` (
524 `id` int(11) NOT NULL default '0'
525 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
526
527 DROP TABLE IF EXISTS `gacl_axo_seq`;
528 CREATE TABLE `gacl_axo_seq` (
529 `id` int(11) NOT NULL default '0'
530 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
531 */
532

  ViewVC Help
Powered by ViewVC 1.1.20