path: root/docs/scire.sql

CREATE DATABASE IF NOT EXISTS scire;
GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON scire.* TO 'scire' IDENTIFIED BY 'scire';
GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON scire.* TO 'scire'@'localhost' IDENTIFIED BY 'scire';
ALTER DATABASE scire DEFAULT CHARACTER SET latin1;
USE scire;

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  userid INT NOT NULL 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;

DROP TABLE IF EXISTS clients;
CREATE TABLE clients (
  clientid INT NOT NULL PRIMARY KEY,
  assetid VARCHAR(64) UNIQUE,
  digest VARCHAR(128) UNIQUE,
  hostname VARCHAR(64) NOT NULL,
  mac VARCHAR(17) NOT NULL,
  ip VARCHAR(15) NOT NULL,
  gli_profile INT,
  osid INT,
  status INT,
  contact INT,
  installtime TIMESTAMP NOT NULL DEFAULT NOW(), # date the clients first was set up
  FOREIGN KEY (osid) REFERENCES os.osid,
  FOREIGN KEY (status) REFERENCES client_status.statusid,
  FOREIGN KEY (gli_profile) REFERENCES GLI_profiles.profileid,
  FOREIGN KEY (contact) REFERENCES users.userid
) ENGINE = MyISAM;

DROP TABLE IF EXISTS client_status;
CREATE TABLE client_status (
  statusid INT NOT NULL,
  statusname VARCHAR(30),
  PRIMARY KEY (statusid)
) ENGINE = MyISAM;


DROP TABLE IF EXISTS permissions;
CREATE TABLE permissions (
  permid INT NOT NULL PRIMARY KEY,
  name VARCHAR(128) NOT NULL UNIQUE,
  description VARCHAR(255),
  permcategory VARCHAR(128) NOT NULL DEFAULT 'Default',
  creator INT NOT NULL,
  created TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (creator) REFERENCES users.userid
) ENGINE = MyISAM;

DROP TABLE IF EXISTS jobs;
CREATE TABLE jobs (
  jobid INT NOT NULL 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;

DROP TABLE IF EXISTS job_history;
CREATE TABLE job_history (
  jobid INT NOT NULL,
  clientid INT NOT NULL,
  eventtime TIMESTAMP NOT NULL DEFAULT NOW(),
  statusid INT NOT NULL,
  eventmsg VARCHAR(255),
  PRIMARY KEY (jobid, clientid, eventtime,statusid),
  FOREIGN KEY (jobid) REFERENCES jobs.jobid,
  FOREIGN KEY (clientid) REFERENCES clients.clientid,
  FOREIGN KEY (statusid) REFERENCES jobs_status.statusid
) ENGINE = MyISAM;

DROP TABLE IF EXISTS jobs_status;
CREATE TABLE jobs_status (
  statusid INT NOT NULL,
  statusname VARCHAR(30),
  PRIMARY KEY (statusid)
) 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 recurring_jobs_clients;
CREATE TABLE recurring_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,
  job_dependency INT,
  deploy_time DATETIME,
  expiration_time DATETIME,
  run_schedule VARCHAR(255),   #a cron-like string showing the schedule to run. min interval of 1 minute.
  validity_period INT, # how many minutes will the job be available
  last_run INT,
  last_run_date DATETIME,
# conditions here (TBD)
# other types of dependencies: 
# right now time and proccess (other jobs)
# might also include data (partition full?) 

  PRIMARY KEY (jobid),
  FOREIGN KEY (jobid) REFERENCES jobs.jobid
) ENGINE = MyISAM;

DROP TABLE IF EXISTS scripts;
CREATE TABLE scripts (
  scriptid INT NOT NULL 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),
  priority INT,
  permission INT,
  pp_location VARCHAR(255),
  pp_script_data TEXT,
  return_output INT DEFAULT 0,
  FOREIGN KEY (permission) REFERENCES permissions.permid
) ENGINE = MyISAM;

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;

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 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;

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
# partitions: data about the partitions
  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
  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;

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;



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 dyn_tags;
CREATE TABLE dyn_tags (
  scriptid INT NOT NULL,
  tag VARCHAR(30) NOT NULL,
  tag_value VARCHAR(255),
  PRIMARY KEY (scriptid, tag),
  FOREIGN KEY (scriptid) REFERENCES scripts.scriptid
) ENGINE = MyISAM;

drop table if exists modules;
create table modules ( 
id INT NOT NULL default '0',
name VARCHAR(50),
long_name VARCHAR(150),
description VARCHAR(250),
path VARCHAR(90),
author VARCHAR(50),
distribution VARCHAR(50),
category VARCHAR(30),
homepage VARCHAR(90),           
PRIMARY KEY (id)
) TYPE=MyISAM;


#########PHPGACL CODE##########
DROP TABLE IF EXISTS gacl_phpgacl;
CREATE TABLE gacl_phpgacl (
	name VARCHAR(230) NOT NULL,
	value VARCHAR(230) NOT NULL,
	PRIMARY KEY (name)
);

DELETE FROM gacl_phpgacl WHERE name='version';

INSERT INTO gacl_phpgacl (name,value) VALUES ('version','3.3.6');

DELETE FROM gacl_phpgacl WHERE name='schema_version';

INSERT INTO gacl_phpgacl (name,value) VALUES ('schema_version','2.1');

DROP TABLE IF EXISTS gacl_acl;
CREATE TABLE gacl_acl (
	id INTEGER NOT NULL DEFAULT 0,
	section_value VARCHAR(230) NOT NULL DEFAULT 'system',
	allow INTEGER NOT NULL DEFAULT 0,
	enabled INTEGER NOT NULL DEFAULT 0,
	return_value TEXT,
	note TEXT,
	updated_date INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (id)
);

ALTER TABLE gacl_acl ADD INDEX gacl_enabled_acl (enabled);

ALTER TABLE gacl_acl ADD INDEX gacl_section_value_acl (section_value);

ALTER TABLE gacl_acl ADD INDEX gacl_updated_date_acl (updated_date);

DROP TABLE IF EXISTS gacl_acl_sections;
CREATE TABLE gacl_acl_sections (
	id INTEGER NOT NULL DEFAULT 0,
	value VARCHAR(230) NOT NULL,
	order_value INTEGER NOT NULL DEFAULT 0,
	name VARCHAR(230) NOT NULL,
	hidden INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (id)
);

ALTER TABLE gacl_acl_sections ADD UNIQUE INDEX gacl_value_acl_sections (value);

ALTER TABLE gacl_acl_sections ADD INDEX gacl_hidden_acl_sections (hidden);

DELETE FROM gacl_acl_sections WHERE id=1 AND value='system';

INSERT INTO gacl_acl_sections (id,value,order_value,name) VALUES (1,'system',1,'System');

DELETE FROM gacl_acl_sections WHERE id=2 AND value='user';

INSERT INTO gacl_acl_sections (id,value,order_value,name) VALUES (2,'user',2,'User');

DROP TABLE IF EXISTS gacl_aco;
CREATE TABLE gacl_aco (
	id INTEGER NOT NULL DEFAULT 0,
	section_value VARCHAR(240) NOT NULL DEFAULT '0',
	value VARCHAR(240) NOT NULL,
	order_value INTEGER NOT NULL DEFAULT 0,
	name VARCHAR(255) NOT NULL,
	hidden INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (id)
);

ALTER TABLE gacl_aco ADD UNIQUE INDEX gacl_section_value_value_aco (section_value, value);

ALTER TABLE gacl_aco ADD INDEX gacl_hidden_aco (hidden);

DROP TABLE IF EXISTS gacl_aco_map;
CREATE TABLE gacl_aco_map (
	acl_id INTEGER NOT NULL DEFAULT 0,
	section_value VARCHAR(230) NOT NULL DEFAULT '0',
	value VARCHAR(230) NOT NULL,
	PRIMARY KEY (acl_id, section_value, value)
);

DROP TABLE IF EXISTS gacl_aco_sections;
CREATE TABLE gacl_aco_sections (
	id INTEGER NOT NULL DEFAULT 0,
	value VARCHAR(230) NOT NULL,
	order_value INTEGER NOT NULL DEFAULT 0,
	name VARCHAR(230) NOT NULL,
	hidden INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (id)
);

ALTER TABLE gacl_aco_sections ADD UNIQUE INDEX gacl_value_aco_sections (value);

ALTER TABLE gacl_aco_sections ADD INDEX gacl_hidden_aco_sections (hidden);

DROP TABLE IF EXISTS gacl_aro;
CREATE TABLE gacl_aro (
	id INTEGER NOT NULL DEFAULT 0,
	section_value VARCHAR(240) NOT NULL DEFAULT '0',
	value VARCHAR(240) NOT NULL,
	order_value INTEGER NOT NULL DEFAULT 0,
	name VARCHAR(255) NOT NULL,
	hidden INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (id)
);

ALTER TABLE gacl_aro ADD UNIQUE INDEX gacl_section_value_value_aro (section_value, value);

ALTER TABLE gacl_aro ADD INDEX gacl_hidden_aro (hidden);

DROP TABLE IF EXISTS gacl_aro_map;
CREATE TABLE gacl_aro_map (
	acl_id INTEGER NOT NULL DEFAULT 0,
	section_value VARCHAR(230) NOT NULL DEFAULT '0',
	value VARCHAR(230) NOT NULL,
	PRIMARY KEY (acl_id, section_value, value)
);

DROP TABLE IF EXISTS gacl_aro_sections;
CREATE TABLE gacl_aro_sections (
	id INTEGER NOT NULL DEFAULT 0,
	value VARCHAR(230) NOT NULL,
	order_value INTEGER NOT NULL DEFAULT 0,
	name VARCHAR(230) NOT NULL,
	hidden INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (id)
);

ALTER TABLE gacl_aro_sections ADD UNIQUE INDEX gacl_value_aro_sections (value);

ALTER TABLE gacl_aro_sections ADD INDEX gacl_hidden_aro_sections (hidden);

DROP TABLE IF EXISTS gacl_axo;
CREATE TABLE gacl_axo (
	id INTEGER NOT NULL DEFAULT 0,
	section_value VARCHAR(240) NOT NULL DEFAULT '0',
	value VARCHAR(240) NOT NULL,
	order_value INTEGER NOT NULL DEFAULT 0,
	name VARCHAR(255) NOT NULL,
	hidden INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (id)
);

ALTER TABLE gacl_axo ADD UNIQUE INDEX gacl_section_value_value_axo (section_value, value);

ALTER TABLE gacl_axo ADD INDEX gacl_hidden_axo (hidden);

DROP TABLE IF EXISTS gacl_axo_map;
CREATE TABLE gacl_axo_map (
	acl_id INTEGER NOT NULL DEFAULT 0,
	section_value VARCHAR(230) NOT NULL DEFAULT '0',
	value VARCHAR(230) NOT NULL,
	PRIMARY KEY (acl_id, section_value, value)
);

DROP TABLE IF EXISTS gacl_axo_sections;
CREATE TABLE gacl_axo_sections (
	id INTEGER NOT NULL DEFAULT 0,
	value VARCHAR(230) NOT NULL,
	order_value INTEGER NOT NULL DEFAULT 0,
	name VARCHAR(230) NOT NULL,
	hidden INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (id)
);

ALTER TABLE gacl_axo_sections ADD UNIQUE INDEX gacl_value_axo_sections (value);

ALTER TABLE gacl_axo_sections ADD INDEX gacl_hidden_axo_sections (hidden);

DROP TABLE IF EXISTS gacl_aro_groups;
CREATE TABLE gacl_aro_groups (
	id INTEGER NOT NULL DEFAULT 0,
	parent_id INTEGER NOT NULL DEFAULT 0,
	lft INTEGER NOT NULL DEFAULT 0,
	rgt INTEGER NOT NULL DEFAULT 0,
	name VARCHAR(255) NOT NULL,
	value VARCHAR(255) NOT NULL,
	PRIMARY KEY (id, value)
);

ALTER TABLE gacl_aro_groups ADD INDEX gacl_parent_id_aro_groups (parent_id);

ALTER TABLE gacl_aro_groups ADD UNIQUE INDEX gacl_value_aro_groups (value);

ALTER TABLE gacl_aro_groups ADD INDEX gacl_lft_rgt_aro_groups (lft, rgt);

DROP TABLE IF EXISTS gacl_groups_aro_map;
CREATE TABLE gacl_groups_aro_map (
	group_id INTEGER NOT NULL DEFAULT 0,
	aro_id INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (group_id, aro_id)
);

ALTER TABLE gacl_groups_aro_map ADD INDEX gacl_aro_id (aro_id);

DROP TABLE IF EXISTS gacl_aro_groups_map;
CREATE TABLE gacl_aro_groups_map (
	acl_id INTEGER NOT NULL DEFAULT 0,
	group_id INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (acl_id, group_id)
);

DROP TABLE IF EXISTS gacl_axo_groups;
CREATE TABLE gacl_axo_groups (
	id INTEGER NOT NULL DEFAULT 0,
	parent_id INTEGER NOT NULL DEFAULT 0,
	lft INTEGER NOT NULL DEFAULT 0,
	rgt INTEGER NOT NULL DEFAULT 0,
	name VARCHAR(255) NOT NULL,
	value VARCHAR(255) NOT NULL,
	PRIMARY KEY (id, value)
);

ALTER TABLE gacl_axo_groups ADD INDEX gacl_parent_id_axo_groups (parent_id);

ALTER TABLE gacl_axo_groups ADD UNIQUE INDEX gacl_value_axo_groups (value);

ALTER TABLE gacl_axo_groups ADD INDEX gacl_lft_rgt_axo_groups (lft, rgt);

DROP TABLE IF EXISTS gacl_groups_axo_map;
CREATE TABLE gacl_groups_axo_map (
	group_id INTEGER NOT NULL DEFAULT 0,
	axo_id INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (group_id, axo_id)
);

ALTER TABLE gacl_groups_axo_map ADD INDEX gacl_axo_id (axo_id);

DROP TABLE IF EXISTS gacl_axo_groups_map;
CREATE TABLE gacl_axo_groups_map (
	acl_id INTEGER NOT NULL DEFAULT 0,
	group_id INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (acl_id, group_id)
);


#########SEQUENCES (both PHPGACL and scire)##########
DROP TABLE IF EXISTS `gacl_acl_seq`;
CREATE TABLE `gacl_acl_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `gacl_aco_sections_seq`;
CREATE TABLE `gacl_aco_sections_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `gacl_aco_seq`;
CREATE TABLE `gacl_aco_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `gacl_aro_groups_id_seq`;
CREATE TABLE `gacl_aro_groups_id_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `gacl_aro_sections_seq`;
CREATE TABLE `gacl_aro_sections_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `gacl_aro_seq`;
CREATE TABLE `gacl_aro_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `gacl_axo_groups_id_seq`;
CREATE TABLE `gacl_axo_groups_id_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `gacl_axo_sections_seq`;
CREATE TABLE `gacl_axo_sections_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `gacl_axo_seq`;
CREATE TABLE `gacl_axo_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `jobs_seq`;
CREATE TABLE `jobs_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `scripts_seq`;
CREATE TABLE `scripts_seq` (
  `id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;