Development/Database Cleanup

From Sit
Jump to: navigation, search
Broom icon32.png This article (or section) needs work.
Please help us and our readers by clicking the edit link above and expanding/improving this text.
Database Cleanup

Highlighted in bold are fields that I believer are no longer used.

CREATE TABLE billingmatrix (
  id int(11) NOT NULL,
  `hour` smallint(6) NOT NULL,
  mon float NOT NULL,
  tue float NOT NULL,
  wed float NOT NULL,
  thu float NOT NULL,
  fri float NOT NULL,
  sat float NOT NULL,
  sun float NOT NULL,
  holiday float NOT NULL,
  PRIMARY KEY  (id,`hour`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'billing_periods'
--

CREATE TABLE billing_periods (
  servicelevelid int(5) NOT NULL,
  engineerperiod int(11) NOT NULL COMMENT 'In minutes',
  customerperiod int(11) NOT NULL COMMENT 'In minutes',
  priority int(4) NOT NULL,
  tag varchar(10) NOT NULL,
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  `limit` float NOT NULL default '0',
  PRIMARY KEY  (servicelevelid,priority)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'closingstatus'
--

CREATE TABLE closingstatus (
  id int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'config'
--

CREATE TABLE config (
  config varchar(255) NOT NULL,
  `value` text,
  PRIMARY KEY  (config)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='SiT configuration';

-- --------------------------------------------------------

--
-- Table structure for table 'contacts'
--

CREATE TABLE contacts (
  id int(11) NOT NULL auto_increment,
  notify_contactid int(11) NOT NULL default '0',
  username varchar(50) default NULL,
  `password` varchar(50) default NULL,
  forenames varchar(100) NOT NULL default '',
  surname varchar(100) NOT NULL default '',
  jobtitle varchar(255) NOT NULL default '',
  courtesytitle varchar(50) NOT NULL default '',
  siteid int(11) NOT NULL default '0',
  email varchar(100) default NULL,
  phone varchar(50) default NULL,
  mobile varchar(50) NOT NULL default '',
  fax varchar(50) default NULL,
  department varchar(255) default NULL,
  address1 varchar(255) default NULL,
  address2 varchar(255) NOT NULL default '',
  city varchar(255) NOT NULL default '',
  county varchar(255) NOT NULL default '',
  country varchar(255) NOT NULL default '',
  postcode varchar(255) NOT NULL default '',
  dataprotection_email enum('No','Yes') default 'No',
  dataprotection_phone enum('No','Yes') default 'No',
  dataprotection_address enum('No','Yes') default 'No',
  timestamp_added int(11) default NULL,
  timestamp_modified int(11) default NULL,
  notes blob NOT NULL, **this shouldn't be a blob**
  active enum('true','false') NOT NULL default 'true',
  created datetime default NULL,
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id),
  KEY siteid (siteid),
  KEY username (username),
  KEY forenames (forenames),
  KEY surname (surname),
  KEY notify_contactid (notify_contactid)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'dashboard'
--

CREATE TABLE dashboard (
  id int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  version mediumint(9) NOT NULL default '1',
  enabled enum('true','false') NOT NULL default 'false',
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'dashboard_rss'
--

CREATE TABLE dashboard_rss (
  owner tinyint(4) NOT NULL,
  url varchar(255) NOT NULL,
  items int(5) default NULL,
  enabled enum('true','false') NOT NULL,
  KEY owner (owner,url)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'dashboard_watch_incidents'
--

CREATE TABLE dashboard_watch_incidents (
  userid tinyint(4) NOT NULL,
  `type` tinyint(4) NOT NULL,
  id int(11) NOT NULL,
  PRIMARY KEY  (userid,`type`,id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'drafts'
--

CREATE TABLE drafts (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL,
  incidentid int(11) NOT NULL,
  `type` enum('update','email') NOT NULL,
  content text NOT NULL,
  meta text NOT NULL,
  lastupdate int(11) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'emailsig'
--

CREATE TABLE emailsig (
  id int(11) NOT NULL auto_increment,
  signature text NOT NULL,
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Global Email Signature';

-- --------------------------------------------------------

--
-- Table structure for table 'emailtemplates'
--

CREATE TABLE emailtemplates (
  id int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `type` enum('usertemplate','system','contact','site','incident','kb','user') NOT NULL default 'user' COMMENT 'usertemplate is personal template owned by a user, user is a template relating to a user',
  description text NOT NULL,
  tofield varchar(100) default NULL,
  fromfield varchar(100) default NULL,
  replytofield varchar(100) default NULL,
  ccfield varchar(100) default NULL,
  bccfield varchar(100) default NULL,
  subjectfield varchar(255) default NULL,
  body text,
  customervisibility enum('show','hide') NOT NULL default 'show',
  storeinlog enum('No','Yes') NOT NULL default 'Yes',
  created datetime default NULL,
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'escalationpaths'
--

CREATE TABLE escalationpaths (
  id int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  track_url varchar(255) default NULL,
  home_url varchar(255) NOT NULL default '',
  url_title varchar(255) default NULL,
  email_domain varchar(255) default NULL,
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'feedbackforms'
--

CREATE TABLE feedbackforms (
  id int(5) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  introduction text NOT NULL,
  thanks text NOT NULL,
  description text NOT NULL,
  multi enum('yes','no') NOT NULL default 'no',
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id),
  KEY multi (multi)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'feedbackquestions'
--

CREATE TABLE feedbackquestions (
  id int(5) NOT NULL auto_increment,
  formid int(5) NOT NULL default '0',
  question varchar(255) NOT NULL default '',
  questiontext text NOT NULL,
  sectiontext text NOT NULL,
  taborder int(5) NOT NULL default '0',
  `type` varchar(255) NOT NULL default 'text',
  required enum('true','false') NOT NULL default 'false',
  options text NOT NULL,
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id),
  KEY taborder (taborder),
  KEY `type` (`type`),
  KEY formid (formid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'feedbackreport'
--

CREATE TABLE feedbackreport (
  id int(5) NOT NULL default '0',
  formid int(5) NOT NULL default '0',
  respondent int(11) NOT NULL default '0',
  responseref varchar(255) NOT NULL default '',
  email varchar(255) NOT NULL default '',
  completed enum('yes','no') NOT NULL default 'no',
  created timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  incidentid int(5) NOT NULL default '0',
  contactid int(5) NOT NULL default '0',
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id),
  KEY responseref (responseref),
  KEY formid (formid),
  KEY respondant (respondent),
  KEY completed (completed),
  KEY incidentid (incidentid),
  KEY contactid (contactid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'feedbackrespondents'
--

CREATE TABLE feedbackrespondents (
  id int(5) NOT NULL auto_increment,
  formid int(5) NOT NULL default '0',
  contactid int(11) NOT NULL default '0',
  incidentid int(11) NOT NULL default '0',
  email varchar(255) NOT NULL default '',
  completed enum('yes','no') NOT NULL default 'no',
  created timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY responseref (incidentid),
  KEY formid (formid),
  KEY contactid (contactid),
  KEY completed (completed)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'feedbackresults'
--

CREATE TABLE feedbackresults (
  id int(5) NOT NULL auto_increment,
  respondentid int(5) NOT NULL default '0',
  questionid int(5) NOT NULL default '0',
  result varchar(255) NOT NULL default '',
  resulttext text,
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id),
  KEY questionid (questionid),
  KEY respondentid (respondentid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'files'
--

CREATE TABLE files (
  id int(11) NOT NULL auto_increment,
  category enum('public','private','protected') NOT NULL default 'public',
  filename varchar(255) default '',
  size bigint(11) NOT NULL default '0',
  userid int(11) NOT NULL default '0',
  usertype enum('user','contact') NOT NULL default 'user',
  shortdescription varchar(255) default '',
  longdescription text,
  webcategory varchar(255) default '',
  path varchar(255) default '',
  downloads int(11) NOT NULL default '0',
  filedate datetime NOT NULL,
  expiry datetime default NULL,
  fileversion varchar(50) default '',
  published enum('yes','no') NOT NULL default 'no',
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id),
  KEY userid (userid),
  KEY category (category),
  KEY filename (filename),
  KEY published (published),
  KEY webcategory (webcategory)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'groups'
--

CREATE TABLE groups (
  id int(5) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  imageurl varchar(255) NOT NULL default '', **do we use this?**
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='List of user groups';

-- --------------------------------------------------------

--
-- Table structure for table 'holidays'
--

CREATE TABLE holidays (
  id int(11) NOT NULL auto_increment,
  userid int(5) NOT NULL default '0',
  `type` int(11) NOT NULL default '1',
  startdate int(11) NOT NULL default '0',
  length enum('am','pm','day') NOT NULL default 'day',
  approved tinyint(1) NOT NULL default '0',
  approvedby int(5) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY userid (userid),
  KEY startdate (startdate),
  KEY `type` (`type`),
  KEY approved (approved)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'incidentpools'
--

CREATE TABLE incidentpools (
  id int(11) NOT NULL auto_increment,
  maintenanceid int(11) NOT NULL default '0',
  siteid int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  incidentsremaining int(5) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY maintenanceid (maintenanceid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'incidentproductinfo'
--

CREATE TABLE incidentproductinfo (
  id int(11) NOT NULL auto_increment,
  incidentid int(11) default NULL,
  productinfoid int(11) default NULL,
  information text,
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'incidents'
--

CREATE TABLE incidents (
  id int(11) NOT NULL auto_increment,
  escalationpath int(11) default NULL,
  externalid varchar(50) default NULL,
  externalengineer varchar(80) NOT NULL default '',
  externalemail varchar(255) NOT NULL default '',
  ccemail varchar(255) default NULL,
  title varchar(150) default NULL,
  owner tinyint(4) default NULL,
  towner tinyint(4) NOT NULL default '0',
  contact int(11) default '0',
  priority tinyint(4) default NULL,
  servicelevel varchar(10) default NULL,
  `status` tinyint(4) default NULL,
  `type` enum('Support','Sales','Other','Free') default 'Support',
  maintenanceid int(11) NOT NULL default '0',
  product int(11) default NULL,
  softwareid int(5) NOT NULL default '0',
  productversion varchar(50) default NULL,
  productservicepacks varchar(100) default NULL,
  opened int(11) default NULL,
  lastupdated int(11) default NULL,
  timeofnextaction int(11) default '0',
  closed int(11) default '0',
  closingstatus tinyint(4) default NULL,
  slaemail tinyint(1) NOT NULL default '0',
  slanotice tinyint(1) NOT NULL default '0',
  locked tinyint(4) NOT NULL default '0', **we dont use this**
  locktime int(11) NOT NULL default '0', **we dont use this**
  createdby int(11) default NULL,
  modified datetime default NULL,
  modifiedby int(11) default NULL,
  PRIMARY KEY  (id),
  KEY `type` (`type`),
  KEY owner (owner),
  KEY `status` (`status`),
  KEY priority (priority),
  KEY timeofnextaction (timeofnextaction),
  KEY maintenanceid (maintenanceid),
  KEY softwareid (softwareid),
  KEY contact (contact),
  KEY title (title),
  KEY opened (opened),
  KEY closed (closed),
  KEY servicelevel (servicelevel)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'incidentstatus' **is this used at all?**
--

CREATE TABLE incidentstatus (
  id int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  ext_name varchar(50) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'interfacestyles'
--

CREATE TABLE interfacestyles (
  id int(5) NOT NULL,
  `name` varchar(50) NOT NULL default '',
  cssurl varchar(255) NOT NULL default '',
  iconset varchar(255) NOT NULL default 'sit',
  headerhtml text NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'inventory'
--

CREATE TABLE inventory (
  id int(11) NOT NULL auto_increment,
  identifier varchar(255) default NULL,
  `name` varchar(255) NOT NULL,
  siteid int(11) NOT NULL,
  contactid int(11) NOT NULL,
  address varchar(255) NOT NULL,
  url varchar(255) default NULL,
  username varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  notes text,
  createdby int(11) NOT NULL,
  created datetime NOT NULL,
  modified datetime NOT NULL,
  modifiedby int(11) NOT NULL,
  active tinyint(1) NOT NULL default '1',
  privacy enum('none','adminonly','private') NOT NULL default 'none',
  PRIMARY KEY  (id),
  KEY siteid (siteid,contactid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'journal'
--

CREATE TABLE journal (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL default '0',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  event varchar(40) NOT NULL default '',
  bodytext text NOT NULL,
  journaltype int(11) NOT NULL default '0',
  refid int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY refid (refid),
  KEY userid (userid)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'kbarticles'
--

CREATE TABLE kbarticles (
  docid int(5) NOT NULL auto_increment,
  doctype int(5) NOT NULL default '0',
  title varchar(255) NOT NULL default '',
  distribution enum('public','private','restricted') NOT NULL default 'public' COMMENT 'public appears in the portal, private is info never to be released to the public,\n  restricted is info that is sensitive but could be mentioned if asked, for example',
  published datetime NOT NULL default '0000-00-00 00:00:00',
  author varchar(255) NOT NULL default '',
  reviewed datetime NOT NULL default '0000-00-00 00:00:00',
  reviewer tinyint(4) NOT NULL default '0',
  keywords varchar(255) NOT NULL default '',
  PRIMARY KEY  (docid),
  KEY distribution (distribution),
  KEY title (title)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Knowledge base articles';

-- --------------------------------------------------------

--
-- Table structure for table 'kbcontent'
--

CREATE TABLE kbcontent (
  docid int(5) NOT NULL default '0',
  id int(7) NOT NULL auto_increment,
  ownerid int(5) NOT NULL default '0',
  headerstyle char(2) NOT NULL default 'h1',
  header varchar(255) NOT NULL default '',
  contenttype int(5) NOT NULL default '1',
  content mediumtext NOT NULL,
  distribution enum('public','private','restricted') NOT NULL default 'private',
  PRIMARY KEY  (id),
  KEY distribution (distribution),
  KEY ownerid (ownerid),
  KEY docid (docid),
  FULLTEXT KEY c_index (content)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'kbsoftware'
--

CREATE TABLE kbsoftware (
  docid int(5) NOT NULL default '0',
  softwareid int(5) NOT NULL default '0',
  PRIMARY KEY  (docid,softwareid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Links kb articles with software';

-- --------------------------------------------------------

--
-- Table structure for table 'licencetypes'
--

CREATE TABLE licencetypes (
  id int(11) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'links'
--

CREATE TABLE links (
  linktype int(11) NOT NULL default '0',
  origcolref int(11) NOT NULL default '0',
  linkcolref int(11) NOT NULL default '0',
  direction enum('left','right','bi') NOT NULL default 'left',
  userid tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (linktype,origcolref,linkcolref),
  KEY userid (userid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'linktypes'
--

CREATE TABLE linktypes (
  id int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  lrname varchar(255) NOT NULL default '',
  rlname varchar(255) NOT NULL default '',
  origtab varchar(255) NOT NULL default '',
  origcol varchar(255) NOT NULL default '',
  linktab varchar(255) NOT NULL default '',
  linkcol varchar(255) NOT NULL default 'id',
  selectionsql varchar(255) NOT NULL default '',
  filtersql varchar(255) NOT NULL default '',
  viewurl varchar(255) NOT NULL default '',
  PRIMARY KEY  (id),
  KEY origtab (origtab),
  KEY linktab (linktab)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'maintenance'
--

CREATE TABLE maintenance (
  id int(11) NOT NULL auto_increment,
  site int(11) default NULL,
  product int(11) default NULL,
  reseller int(11) default NULL,
  expirydate int(11) default NULL,
  licence_quantity int(11) default NULL,
  licence_type int(11) default NULL,
  incident_quantity int(5) NOT NULL default '0',
  incidents_used int(5) NOT NULL default '0',
  notes text,
  admincontact int(11) default NULL,
  productonly enum('yes','no') NOT NULL default 'no',
  term enum('no','yes') default 'no',
  servicelevelid int(11) NOT NULL default '1',
  incidentpoolid int(11) NOT NULL default '0',
  supportedcontacts int(255) NOT NULL default '0',
  allcontactssupported enum('no','yes') NOT NULL default 'no',
  var_incident_visible_contacts enum('yes','no') NOT NULL default 'no',
  var_incident_visible_all enum('yes','no') NOT NULL default 'no',
  PRIMARY KEY  (id),
  KEY site (site),
  KEY productonly (productonly)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'notes'
--

CREATE TABLE notes (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL default '0',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  bodytext text NOT NULL,
  link int(11) NOT NULL default '0',
  refid int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY refid (refid),
  KEY userid (userid),
  KEY link (link)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'notices'
--

CREATE TABLE notices (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL,
  template varchar(255) default NULL,
  `type` tinyint(4) NOT NULL,
  `text` tinytext NOT NULL,
  linktext varchar(50) default NULL,
  link varchar(100) NOT NULL,
  referenceid int(11) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  durability enum('sticky','session') NOT NULL default 'sticky',
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'noticetemplates'
--

CREATE TABLE noticetemplates (
  id int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `type` tinyint(4) NOT NULL,
  description varchar(255) NOT NULL,
  `text` tinytext NOT NULL,
  linktext varchar(50) default NULL,
  link varchar(100) default NULL,
  durability enum('sticky','session') NOT NULL default 'sticky',
  refid int(11) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'permissions'
--

CREATE TABLE permissions (
  id int(5) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'priority'
--

CREATE TABLE priority (
  id int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Used in incidents.php';

-- --------------------------------------------------------

--
-- Table structure for table 'productinfo'
--

CREATE TABLE productinfo (
  id int(11) NOT NULL auto_increment,
  productid int(11) default NULL,
  information text,
  moreinformation varchar(255) NOT NULL default '',
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'products'
--

CREATE TABLE products (
  id int(11) NOT NULL auto_increment,
  vendorid int(5) NOT NULL default '0',
  `name` varchar(50) default NULL,
  description text NOT NULL,
  PRIMARY KEY  (id),
  KEY vendorid (vendorid),
  KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Current List of Products';

-- --------------------------------------------------------

--
-- Table structure for table 'relatedincidents'
--

CREATE TABLE relatedincidents (
  id int(5) NOT NULL auto_increment,
  incidentid int(5) NOT NULL,
  relation enum('child','sibling') NOT NULL default 'child',
  relatedid int(5) NOT NULL,
  owner int(5) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY incidentid (incidentid,relatedid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'resellers'
--

CREATE TABLE resellers (
  id tinyint(4) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'rolepermissions'
--

CREATE TABLE rolepermissions (
  roleid tinyint(4) NOT NULL default '0',
  permissionid int(5) NOT NULL default '0',
  granted enum('true','false') NOT NULL default 'false',
  PRIMARY KEY  (roleid,permissionid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'roles'
--

CREATE TABLE roles (
  id int(5) NOT NULL auto_increment,
  rolename varchar(255) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'scheduler'
--

CREATE TABLE scheduler (
  id int(11) NOT NULL auto_increment,
  `action` varchar(50) NOT NULL,
  params varchar(255) NOT NULL,
  paramslabel varchar(255) default NULL,
  description tinytext NOT NULL,
  `status` enum('enabled','disabled') NOT NULL default 'enabled',
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  `type` enum('interval','date') NOT NULL default 'interval',
  `interval` int(11) NOT NULL,
  date_type enum('month','year') NOT NULL COMMENT 'For type date the type',
  date_offset int(11) NOT NULL COMMENT 'off set into the period',
  date_time time NOT NULL COMMENT 'Time to perform action',
  laststarted datetime NOT NULL,
  lastran datetime NOT NULL,
  success tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (id),
  KEY job (`action`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'service'
--

CREATE TABLE service (
  serviceid int(11) NOT NULL auto_increment,
  contractid int(11) NOT NULL,
  startdate date NOT NULL,
  enddate date NOT NULL,
  lastbilled datetime NOT NULL,
  creditamount float NOT NULL default '0',
  balance float NOT NULL default '0',
  unitrate float NOT NULL default '0',
  incidentrate float NOT NULL default '0',
  dailyrate float NOT NULL default '0',
  billingmatrix int(11) NOT NULL default '1',
  priority smallint(6) NOT NULL default '0',
  notes text NOT NULL,
  foc enum('yes','no') NOT NULL default 'no' COMMENT 'Free of charge (customer not charged)',
  PRIMARY KEY  (serviceid)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'servicelevels'
--

CREATE TABLE servicelevels (
  id int(5) NOT NULL default '0',
  tag varchar(10) NOT NULL default '',
  priority int(5) NOT NULL default '0',
  initial_response_mins int(11) NOT NULL default '0',
  prob_determ_mins int(11) NOT NULL default '0',
  action_plan_mins int(11) NOT NULL default '0',
  resolution_days float(5,2) NOT NULL default '0.00',
  contact_days int(11) NOT NULL default '0',
  review_days int(11) NOT NULL default '365',
  timed enum('yes','no') NOT NULL default 'no',
  allow_reopen enum('yes','no') NOT NULL default 'yes' COMMENT 'Allow incidents to be reopened?',
  PRIMARY KEY  (tag,priority),
  KEY id (id),
  KEY review_days (review_days)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'set_tags'
--

CREATE TABLE set_tags (
  id int(11) NOT NULL,
  `type` mediumint(9) NOT NULL,
  tagid int(11) NOT NULL,
  PRIMARY KEY  (id,`type`,tagid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'sitecontacts'
--

CREATE TABLE sitecontacts (
  siteid int(11) NOT NULL default '0',
  contactid int(11) NOT NULL default '0',
  PRIMARY KEY  (siteid,contactid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'sites'
--

CREATE TABLE sites (
  id int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  department varchar(255) NOT NULL default '',
  address1 varchar(255) NOT NULL default '',
  address2 varchar(255) NOT NULL default '',
  city varchar(255) NOT NULL default '',
  county varchar(255) NOT NULL default '',
  country varchar(255) NOT NULL default '',
  postcode varchar(255) NOT NULL default '',
  telephone varchar(255) NOT NULL default '',
  fax varchar(255) NOT NULL default '',
  email varchar(255) NOT NULL default '',
  websiteurl varchar(255) default NULL,
  notes blob NOT NULL,
  typeid int(5) NOT NULL default '1',
  freesupport int(5) NOT NULL default '0',
  licenserx int(5) NOT NULL default '0',
  ftnpassword varchar(40) NOT NULL default '',
  owner tinyint(4) NOT NULL default '0',
  active enum('true','false') NOT NULL default 'true',
  PRIMARY KEY  (id),
  KEY typeid (typeid),
  KEY owner (owner)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'sitetypes'
--

CREATE TABLE sitetypes (
  typeid int(5) NOT NULL auto_increment,
  typename varchar(255) NOT NULL default '',
  PRIMARY KEY  (typeid)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'software'
--

CREATE TABLE software (
  id int(5) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  vendorid int(5) NOT NULL default '0',
  software int(5) NOT NULL default '0',
  lifetime_start date default NULL,
  lifetime_end date default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Individual software products as they are supported';

-- --------------------------------------------------------

--
-- Table structure for table 'softwareproducts'
--

CREATE TABLE softwareproducts (
  productid int(5) NOT NULL default '0',
  softwareid int(5) NOT NULL default '0',
  PRIMARY KEY  (productid,softwareid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Table to link products with software';

-- --------------------------------------------------------

--
-- Table structure for table 'spellcheck'
--

CREATE TABLE spellcheck (
  id int(11) NOT NULL auto_increment,
  updateid int(11) NOT NULL default '0',
  bodytext text NOT NULL,
  newincidentstatus int(11) default NULL,
  timetonextaction_none varchar(50) default NULL,
  timetonextaction_days int(11) default NULL,
  timetonextaction_hours int(11) default NULL,
  timetonextaction_minutes int(11) default NULL,
  `day` int(11) default NULL,
  `month` int(11) default NULL,
  `year` int(11) default NULL,
  fromfield varchar(255) default NULL,
  replytofield varchar(255) default NULL,
  ccfield varchar(255) default NULL,
  bccfield varchar(255) default NULL,
  tofield varchar(255) default NULL,
  subjectfield varchar(255) default NULL,
  attachmenttype varchar(255) default NULL,
  filename varchar(255) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY updateid (updateid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Temporary table used during spellcheck';

-- --------------------------------------------------------

--
-- Table structure for table 'supportcontacts'
--

CREATE TABLE supportcontacts (
  id int(11) NOT NULL auto_increment,
  maintenanceid int(11) default NULL,
  contactid int(11) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'system'
--

CREATE TABLE system (
  id int(1) NOT NULL default '0',
  version float(3,2) NOT NULL default '0.00',
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'tags'
--

CREATE TABLE tags (
  tagid int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (tagid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'tasks'
--

CREATE TABLE tasks (
  id int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  description text NOT NULL,
  priority tinyint(4) default NULL,
  owner tinyint(4) NOT NULL default '0',
  duedate datetime default NULL,
  startdate datetime default NULL,
  enddate datetime default NULL,
  completion tinyint(4) default NULL,
  `value` float(6,2) default NULL,
  distribution enum('public','private','incident','event') NOT NULL default 'public',
  created datetime NOT NULL default '0000-00-00 00:00:00',
  lastupdated timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY owner (owner)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'tempassigns'
--

CREATE TABLE tempassigns (
  incidentid int(5) NOT NULL default '0',
  originalowner int(5) NOT NULL default '0',
  userstatus tinyint(4) NOT NULL default '1',
  assigned enum('yes','no') NOT NULL default 'no',
  PRIMARY KEY  (incidentid,originalowner),
  KEY assigned (assigned)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'tempincoming'
--

CREATE TABLE tempincoming (
  id int(11) NOT NULL auto_increment,
  updateid int(11) NOT NULL default '0',
  path varchar(255) NOT NULL default '',
  incidentid int(11) NOT NULL default '0',
  `from` varchar(255) default NULL,
  `subject` varchar(255) default NULL,
  emailfrom varchar(255) default NULL,
  locked tinyint(4) default NULL,
  lockeduntil datetime default NULL,
  reason varchar(255) default NULL,
  reason_user int(11) NOT NULL,
  reason_time datetime NOT NULL,
  contactid int(11) default NULL,
  PRIMARY KEY  (id),
  KEY updateid (updateid)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Temporary store for incoming attachment paths';

-- --------------------------------------------------------

--
-- Table structure for table 'transactions'
--

CREATE TABLE transactions (
  transactionid int(11) NOT NULL auto_increment,
  serviceid int(11) NOT NULL,
  amount float NOT NULL,
  description varchar(255) NOT NULL,
  userid tinyint(4) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (transactionid)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'triggers'
--

CREATE TABLE `triggers` (
  id int(11) NOT NULL auto_increment,
  triggerid varchar(50) NOT NULL,
  userid tinyint(4) NOT NULL,
  `action` enum('ACTION_NONE','ACTION_EMAIL','ACTION_NOTICE','ACTION_JOURNAL','ACTION_CREATE_INCIDENT') NOT NULL default 'ACTION_NONE',
  template varchar(255) default NULL,
  parameters varchar(255) default NULL,
  checks varchar(255) default NULL,
  PRIMARY KEY  (id),
  KEY triggerid (triggerid),
  KEY userid (userid)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'updates'
--

CREATE TABLE updates (
  id int(11) NOT NULL auto_increment,
  incidentid int(11) default NULL,
  userid int(11) default NULL,
  `type` enum('default','editing','opening','email','reassigning','closing','reopening','auto','phonecallout','phonecallin','research','webupdate','emailout','emailin','externalinfo','probdef','solution','actionplan','slamet','reviewmet','tempassigning','auto_chase_email','auto_chase_phone','auto_chase_manager','auto_chased_phone','auto_chased_manager','auto_chase_managers_manager','customerclosurerequest','fromtask') default 'default',
  currentowner tinyint(4) NOT NULL default '0',
  currentstatus int(11) NOT NULL default '0',
  bodytext text,
  `timestamp` int(11) default NULL,
  nextaction varchar(50) NOT NULL default '',
  customervisibility enum('show','hide','unset') default 'unset',
  sla enum('opened','initialresponse','probdef','actionplan','solution','closed') default NULL,
  duration int(11) default NULL,
  PRIMARY KEY  (id),
  KEY currentowner (currentowner,currentstatus),
  KEY incidentid (incidentid),
  KEY `timestamp` (`timestamp`),
  KEY `type` (`type`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'usergroups'
--

CREATE TABLE usergroups (
  userid int(5) NOT NULL default '0',
  groupid int(5) NOT NULL default '0',
  PRIMARY KEY  (userid,groupid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Links users with groups';

-- --------------------------------------------------------

--
-- Table structure for table 'userpermissions'
--

CREATE TABLE userpermissions (
  userid tinyint(4) NOT NULL default '0',
  permissionid int(5) NOT NULL default '0',
  granted enum('true','false') NOT NULL default 'false',
  PRIMARY KEY  (userid,permissionid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'users'
--

CREATE TABLE users (
  id tinyint(4) NOT NULL auto_increment,
  username varchar(50) default NULL,
  `password` varchar(50) default NULL,
  realname varchar(50) default NULL,
  roleid int(5) NOT NULL default '3',
  groupid int(5) default NULL,
  title varchar(50) default NULL,
  signature text,
  email varchar(50) default NULL,
  icq varchar(15) NOT NULL default '',
  aim varchar(25) NOT NULL default '',
  msn varchar(70) NOT NULL default '',
  phone varchar(50) default NULL,
  mobile varchar(50) NOT NULL default '',
  fax varchar(50) default NULL,
  `status` tinyint(4) default NULL,
  message varchar(150) default NULL,
  accepting enum('No','Yes') default 'Yes',
  var_incident_refresh int(11) default '60',
  var_update_order enum('desc','asc') default 'desc',
  var_num_updates_view int(11) NOT NULL default '15',
  var_style int(11) default '1',
  var_hideautoupdates enum('true','false') NOT NULL default 'false',
  var_hideheader enum('true','false') NOT NULL default 'false',
  var_monitor enum('true','false') NOT NULL default 'true',
  var_i18n varchar(20) default NULL,
  var_utc_offset int(11) NOT NULL default '0' COMMENT 'Offset from UTC (timezone)',
  listadmin tinytext,
  holiday_entitlement float NOT NULL default '0',
  qualifications tinytext,
  dashboard varchar(255) NOT NULL default '0-3,1-1,1-2,2-4',
  lastseen datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY username (username),
  KEY accepting (accepting),
  KEY `status` (`status`),
  KEY groupid (groupid)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'usersoftware'
--

CREATE TABLE usersoftware (
  userid tinyint(4) NOT NULL default '0',
  softwareid int(5) NOT NULL default '0',
  backupid tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (userid,softwareid),
  KEY backupid (backupid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Defines which software users have expertise with';

-- --------------------------------------------------------

--
-- Table structure for table 'userstatus'
--

CREATE TABLE userstatus (
  id int(11) NOT NULL,
  `name` varchar(50) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table 'vendors'
--

CREATE TABLE vendors (
  id int(5) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

</code>
Personal tools
project