still totally a work in progress but here's an

updated db design

svn path=/; revision=86

1 files changed, 143 insertions, 66 deletions

diff --git a/docs/scire.sql b/docs/scire.sql
index 89c7d5c..c752fd4 100644
--- a/docs/scire.sql
+++ b/docs/scire.sql
@@ -1,83 +1,160 @@
-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 `clients`;
-CREATE TABLE `clients` (
-  `clientid` VARCHAR(128) NOT NULL PRIMARY KEY,
-  `key` TEXT,
-  `hostname` VARCHAR(64) NOT NULL,
-  `mac` VARCHAR(17) NOT NULL,
-  `ip` VARCHAR(15) NOT NULL,
-  `profile_name` VARCHAR(255),
-  `status` VARCHAR(10)
+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,
+  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,
+  digest VARCHAR(128) NOT NULL UNIQUE,
+  key TEXT,
+  hostname VARCHAR(64) NOT NULL,
+  mac VARCHAR(17) NOT NULL,
+  ip VARCHAR(15) NOT NULL,
+  gli_profile INT,
+  os 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,
+  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 `client_groups`;
-CREATE TABLE `client_groups` (
-  `group_name` VARCHAR(128) NOT NULL,
-  `clientid` VARCHAR(128) NOT NULL,
-  INDEX (`group_name`, `clientid`)
+
+
+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 `profiles`;
-CREATE TABLE `profiles` (
-  `profile_name` VARCHAR(255) NOT NULL PRIMARY KEY,
-  `path` VARCHAR(255) NOT NULL,
-  `description` VARCHAR(255)
+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,
+  description VARCHAR(255),
+  INDEX (status, creator, perm_name, clientid),
+  FOREIGN KEY (creator) REFERENCES users.userid,
+  FOREIGN KEY (permission) REFERENCES permissions.permid,
+  FOREIGN KEY (scriptid) REFERENCES scripts.scriptid,
+  FOREIGN KEY (clientid) REFERENCES clients.clientid
 ) ENGINE = MyISAM;
 
-DROP TABLE IF EXISTS `users`;
-CREATE TABLE `users` (
-  `username` VARCHAR(64) NOT NULL PRIMARY KEY,
-  `password` VARCHAR(255) NOT NULL,
-  `real_name` VARCHAR(255),
-  `comment` VARCHAR(255)
+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,
+  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,
+  clients INT NOT NULL -- LIST!!!
+  FOREIGN KEY (permission) REFERENCES permissions.permid,
+  FOREIGN KEY (scriptid) REFERENCES scripts.scriptid,
+  FOREIGN KEY (creator) REFERENCES users.userid
 ) ENGINE = MyISAM;
 
-DROP TABLE IF EXISTS `user_groups`;
-CREATE TABLE `user_groups` (
-  `group_name` VARCHAR(64) NOT NULL,
-  `username` VARCHAR(64) NOT NULL,
-  INDEX (`group_name`, `username`)
+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 `permissions`;
-CREATE TABLE `permissions` (
-  `perm_name` VARCHAR(128) NOT NULL PRIMARY KEY,
-  `on` VARCHAR(128) NOT NULL,
-  `for` VARCHAR(64) NOT NULL,
-  `description` VARCHAR(255),
-  `creator` VARCHAR(64),
-  INDEX (`on`, `for`)
+DROP TABLE IF EXISTS os;
+CREATE TABLE os (
+  osname VARCHAR(128) NOT NULL PRIMARY KEY,
+  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 `jobs`;
-CREATE TABLE `jobs` (
-  `jobid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-  `priority` INT NOT NULL DEFAULT 0,
-  `status` VARCHAR(10) NOT NULL,
-  `created` TIMESTAMP NOT NULL,
-  `creator` VARCHAR(64) NOT NULL,
-  `perm_name` VARCHAR(128) NOT NULL,
-  `clientid` VARCHAR(128) NOT NULL,
-  `jobdata` MEDIUMTEXT,
-  `description` VARCHAR(255),
-  INDEX (`status`, `creator`, `perm_name`, `clientid`)
+DROP TABLE IF EXISTS settings;
+CREATE TABLE settings (
+  userid INT NOT NULL,
+  setting_name VARCHAR(128),
+  setting_value VARCHAR(255),
+  FOREIGN KEY (userid) REFERENCES users.userid
+  INDEX (setting_name),
+
 ) 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
+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
+# partitions: data about the partitions
+# cpu: type of cpu
+# MHz: 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 `settings`;
-CREATE TABLE `settings` (
-  `username` VARCHAR(64) NOT NULL,
-  `setting_name` VARCHAR(128),
-  `setting_value` TEXT,
-  INDEX (`username`, `setting_name`)
+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;