updating svn with current copy from wiki

svn path=/; revision=88

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;