diff options
author | 2006-06-03 17:41:25 +0000 | |
---|---|---|
committer | 2006-06-03 17:41:25 +0000 | |
commit | d48777a24891e3115aba5b1ee07d9cc24ca05eaf (patch) | |
tree | f95a3f7d4e013113bb1fc9726b1ca9860af4eb16 /docs | |
parent | small update (diff) | |
download | scire-d48777a24891e3115aba5b1ee07d9cc24ca05eaf.tar.gz scire-d48777a24891e3115aba5b1ee07d9cc24ca05eaf.tar.bz2 scire-d48777a24891e3115aba5b1ee07d9cc24ca05eaf.zip |
updating svn with current copy from wiki
svn path=/; revision=88
Diffstat (limited to 'docs')
-rw-r--r-- | docs/scire.sql | 130 |
1 files changed, 85 insertions, 45 deletions
diff --git a/docs/scire.sql b/docs/scire.sql index 076de02..abbc4a3 100644 --- a/docs/scire.sql +++ b/docs/scire.sql @@ -8,6 +8,9 @@ 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; @@ -15,32 +18,23 @@ CREATE TABLE users ( 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, - key TEXT, + cert TEXT, hostname VARCHAR(64) NOT NULL, mac VARCHAR(17) NOT NULL, ip VARCHAR(15) NOT NULL, gli_profile INT, - os INT, + osid INT, status VARCHAR(20), contact INT, - installtime TIMESTAMP NOT NULL DEFAULT NOW(), --: date the clients first was set up - FOREIGN KEY (os) REFERENCES os.osname, + 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 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; - - - DROP TABLE IF EXISTS permissions; CREATE TABLE permissions ( permid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, @@ -54,36 +48,60 @@ DROP TABLE IF EXISTS jobs; CREATE TABLE jobs ( jobid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, priority INT NOT NULL DEFAULT 0, - status VARCHAR(20) NOT NULL, created TIMESTAMP NOT NULL DEFAULT NOW(), creator INT NOT NULL, permission INT NOT NULL, - scriptid INT NOT NULL, - clientid INT NOT NULL, + script INT NOT NULL, description VARCHAR(255), - INDEX (status, creator, perm_name, clientid), + pending INT, + failed INT, + INDEX (creator), FOREIGN KEY (creator) REFERENCES users.userid, FOREIGN KEY (permission) REFERENCES permissions.permid, - FOREIGN KEY (scriptid) REFERENCES scripts.scriptid, - FOREIGN KEY (clientid) REFERENCES clients.clientid + FOREIGN KEY (script) REFERENCES scripts.scriptid ) ENGINE = MyISAM; -DROP TABLE IF EXISTS rec_jobs; -CREATE TABLE rec_jobs ( - rec_jobid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, - priority INT NOT NULL DEFAULT 5, +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, - created TIMESTAMP NOT NULL DEFAULT NOW(), - creator INT NOT NULL, - period INT NOT NULL, - start_period TIMESTAMP NOT NULL DEFAULT NOW(), - end_period TIMESTAMP, - permission INT NOT NULL, - scriptid INT 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, - FOREIGN KEY (permission) REFERENCES permissions.permid, - FOREIGN KEY (scriptid) REFERENCES scripts.scriptid, - FOREIGN KEY (creator) REFERENCES users.userid, + 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; @@ -101,7 +119,8 @@ CREATE TABLE scripts ( DROP TABLE IF EXISTS os; CREATE TABLE os ( - osname VARCHAR(128) NOT NULL PRIMARY KEY, + osid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + osname VARCHAR(128) NOT NULL, update_script INT, install_script INT, uninstall_script INT, @@ -118,16 +137,16 @@ 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, + data TEXT ) ENGINE = MyISAM; DROP TABLE IF EXISTS settings; CREATE TABLE settings ( userid INT NOT NULL, - setting_name VARCHAR(128), + setting_name VARCHAR(64), setting_value VARCHAR(255), + PRIMARY KEY (userid, setting_name), FOREIGN KEY (userid) REFERENCES users.userid - INDEX (setting_name), ) ENGINE = MyISAM; @@ -135,18 +154,29 @@ DROP TABLE IF EXISTS hardware; CREATE TABLE hardware ( clientid INT NOT NULL PRIMARY KEY, processor VARCHAR(32), -# memory: size of the installed memory in MB -# hd: size of harddisk in MB + memory VARCHAR(32), # size of the installed memory in MB + hd VARCHAR(32), # size of harddisk in MB # partitions: data about the partitions -# cpu: type of cpu -# MHz: speed of the cpu + 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 +# 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; @@ -156,6 +186,16 @@ CREATE TABLE software ( package VARCHAR(128) NOT NULL PRIMARY KEY, current_ver VARCHAR(64), rollback_ver VARCHAR(64), - --dependencies + #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; |