Current DB Structure

From Scire Wiki

Contents 1 Database Structure 2 Users 3 Clients 4 Permissions 5 Jobs 6 Job_History 7 Jobs-Clients Link Table 8 Job_conditions 9 Scripts 10 OS 11 Sessions 12 Settings 13 Hardware 14 Hardware History 15 Software 16 Notes 17 GLI Profiles

Database Structure

THIS SCHEMA IS NOW OUT OF DATE AND NOT IN SYNC WITH THE REPOSITORY. SEE [1]

This is by far still a work in progress. But please edit it and make it better. Major work remaining: Notifications. Multiple network devices. Virtual Hosts? Services? MODULES! (aka Plugins)

To use with this schema is some Sample DB Startup Code

Users

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;

Clients

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;

Permissions

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;

1. Possible statuses till date:
2. Active: client is available for normal operation
3. Inactive: client is unavailable, check comment
4. Pending: client has not been approved yet
5. Retired: client is no longer in service, kept for historical purposes
6. Rejected: client was pending and not accepted (warn if they try again?)

Jobs

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;

Job_History

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;

Jobs-Clients Link Table

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;

1. Either clienid or groupid is required, if 1 is provided the other MUST be NULL

Job_conditions

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?

1. conditions here (TBD)
2. other types of dependencies:
3. right now time and proccess (other jobs)
4. might also include data (partition full?)

 PRIMARY KEY (jobid,clientid),
 FOREIGN KEY (jobid) REFERENCES jobs.jobid,
 FOREIGN KEY (clientid) REFERENCES clients.clientid

) ENGINE = MyISAM;

Scripts

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),
 script_data TEXT,
 log_location VARCHAR(255),
 success_code VARCHAR(32),
 run_as VARCHAR(255)

) ENGINE = MyISAM;

OS

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;

Sessions

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;

Settings

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;

Hardware

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

1. partitions: data about the partitions

 cpu VARCHAR(64),      # type of cpu
 mhz VARCHAR(32),      # speed of the cpu

1. netcards: product names of the installed network cards
2. graficcard: information about the grafic card
3. soundcard: name of the sound card
4. isa: information about ISA components
5. dmi: DMI information
6. ram ,
7. .. steal the rest from Zen or m23

 FOREIGN KEY (clientid) REFERENCES clients.clientid

) ENGINE = MyISAM;

Hardware History

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;

Software

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;

Notes

DROP TABLE IF EXISTS notes; CREATE TABLE notes (

 type CHAR(20) NOT NULL, 
 fieldname CHAR(20) NOT NULL, 
 date TIMESTAMP, 
 comment TEXT, 
 PRIMARY KEY (type, fieldname,date)

) ENGINE = MyISAM;

1. type = table name.
2. fieldname = field within the table.

GLI Profiles

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;