Fixed charset + runaway <div>s

[usit-rt.git] / etc / schema.mysql

CREATE TABLE Attachments (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  TransactionId integer NOT NULL  ,
  Parent integer NOT NULL DEFAULT 0  ,
  MessageId varchar(160) CHARACTER SET ascii NULL  ,
  Subject varchar(255) NULL  ,
  Filename varchar(255) NULL  ,
  ContentType varchar(80) CHARACTER SET ascii NULL  ,
  ContentEncoding varchar(80) CHARACTER SET ascii NULL  ,
  Content LONGBLOB NULL  ,
  Headers LONGTEXT NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB  CHARACTER SET utf8;

CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;

CREATE TABLE Queues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NOT NULL  ,
  Description varchar(255) NULL  ,
  CorrespondAddress varchar(120) NULL,
  CommentAddress varchar(120) NULL,
  Lifecycle varchar(32) NULL,
  SubjectTag varchar(120) NULL,
  InitialPriority integer NOT NULL DEFAULT 0  ,
  FinalPriority integer NOT NULL DEFAULT 0  ,
  DefaultDueIn integer NOT NULL DEFAULT 0  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
CREATE INDEX Queues2 ON Queues (Disabled) ;



CREATE TABLE Links (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Base varchar(240) NULL,
  Target varchar(240) NULL,
  Type varchar(20) CHARACTER SET ascii NOT NULL ,
  LocalTarget integer NOT NULL DEFAULT 0  ,
  LocalBase integer NOT NULL DEFAULT 0  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX Links2 ON Links (Base,  Type) ;
CREATE INDEX Links3 ON Links (Target,  Type) ;
CREATE INDEX Links4 ON Links (Type,LocalBase);



CREATE TABLE Principals (
        id INTEGER  AUTO_INCREMENT not null,
        PrincipalType VARCHAR(16) not null,
        ObjectId integer, # foreign key to Users or Groups, depending
        Disabled int2 NOT NULL DEFAULT 0 ,
        PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET ascii;

CREATE INDEX Principals2 ON Principals (ObjectId);



CREATE TABLE Groups (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  Domain varchar(64) CHARACTER SET ascii NULL,
  Type varchar(64) CHARACTER SET ascii NULL,
  Instance integer,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX Groups1 ON Groups (Domain, Type, Instance);
CREATE INDEX Groups2 ON Groups (Domain, Name, Instance);
CREATE INDEX Groups3 On Groups (Instance);

CREATE TABLE ScripConditions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) CHARACTER SET ascii NULL,
  Argument VARBINARY(255) NULL  ,
  ApplicableTransTypes varchar(60) CHARACTER SET ascii NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;


CREATE TABLE Transactions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  ObjectType varchar(64) CHARACTER SET ascii NOT NULL,
  ObjectId integer NOT NULL DEFAULT 0  ,
  TimeTaken integer NOT NULL DEFAULT 0  ,
  Type varchar(20) CHARACTER SET ascii NULL,
  Field varchar(40) CHARACTER SET ascii NULL,
  OldValue varchar(255) NULL  ,
  NewValue varchar(255) NULL  ,
  ReferenceType varchar(255) CHARACTER SET ascii NULL,
  OldReference integer NULL  ,
  NewReference integer NULL  ,
  Data varchar(255) NULL  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);



CREATE TABLE Scrips (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Description varchar(255),
  ScripCondition integer NOT NULL DEFAULT 0  ,
  ScripAction integer NOT NULL DEFAULT 0  ,
  CustomIsApplicableCode text NULL  ,
  CustomPrepareCode text NULL  ,
  CustomCommitCode text NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  Template varchar(200) NOT NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE TABLE ObjectScrips (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Scrip integer NOT NULL  ,
  Stage varchar(32) CHARACTER SET ascii NOT NULL DEFAULT 'TransactionCreate',
  ObjectId integer NOT NULL,
  SortOrder integer NOT NULL DEFAULT 0  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE UNIQUE INDEX ObjectScrips1 ON ObjectScrips (ObjectId, Scrip);

CREATE TABLE ACL (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  PrincipalType varchar(25) CHARACTER SET ascii NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor"

  PrincipalId integer NOT NULL DEFAULT 0 , #Foreign key to principals
  RightName varchar(25) CHARACTER SET ascii NOT NULL,
  ObjectType varchar(25) CHARACTER SET ascii NOT NULL,
  ObjectId integer NOT NULL default 0,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX  ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);



CREATE TABLE GroupMembers (
  id INTEGER NOT NULL AUTO_INCREMENT,
  GroupId integer NOT NULL DEFAULT 0,
  MemberId integer NOT NULL DEFAULT 0,  #Foreign key to Principals
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);




create table CachedGroupMembers (
        id int auto_increment,
        GroupId int, # foreign key to Principals
        MemberId int, # foreign key to Principals
        Via int, #foreign key to CachedGroupMembers. (may point to $self->id)
        ImmediateParentId int, #foreign key to prinicpals.
                               # this points to the group that the member is
                               # a member of, for ease of deletes.
        Disabled int2 NOT NULL DEFAULT 0 , # if this cached group member is a member of this group by way of a disabled
                                           # group or this group is disabled, this will be set to 1
                                           # this allows us to not find members of disabled subgroups when listing off
                                           # group members recursively.
                                           # Also, this allows us to have the ACL system elide members of disabled groups
        PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX DisGrouMem  on CachedGroupMembers (GroupId,MemberId,Disabled);
CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled);
CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);



CREATE TABLE Users (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NOT NULL  ,
  Password VARCHAR(256) NULL  ,
  AuthToken VARCHAR(16) CHARACTER SET ascii NULL  ,
  Comments TEXT NULL  ,
  Signature TEXT NULL  ,
  EmailAddress varchar(120) NULL  ,
  FreeformContactInfo TEXT NULL  ,
  Organization varchar(200) NULL  ,
  RealName varchar(120) NULL  ,
  NickName varchar(16) NULL  ,
  Lang varchar(16) NULL  ,
  EmailEncoding varchar(16) NULL  ,
  WebEncoding varchar(16) NULL  ,
  ExternalContactInfoId varchar(100) NULL  ,
  ContactInfoSystem varchar(30) NULL  ,
  ExternalAuthId varchar(100) NULL  ,
  AuthSystem varchar(30) NULL  ,
  Gecos varchar(16) NULL  ,
  HomePhone varchar(30) NULL  ,
  WorkPhone varchar(30) NULL  ,
  MobilePhone varchar(30) NULL  ,
  PagerPhone varchar(30) NULL  ,
  Address1 varchar(200) NULL  ,
  Address2 varchar(200) NULL  ,
  City varchar(100) NULL  ,
  State varchar(100) NULL  ,
  Zip varchar(16) NULL  ,
  Country varchar(50) NULL  ,
  Timezone varchar(50) NULL  ,
  PGPKey text NULL,
  SMIMECertificate text NULL,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;


CREATE UNIQUE INDEX Users1 ON Users (Name) ;
CREATE INDEX Users4 ON Users (EmailAddress);




CREATE TABLE Tickets (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  EffectiveId integer NOT NULL DEFAULT 0  ,
  IsMerged int2 NULL DEFAULT NULL,
  Queue integer NOT NULL DEFAULT 0  ,
  Type varchar(16) CHARACTER SET ascii NULL  ,
  IssueStatement integer NOT NULL DEFAULT 0  ,
  Resolution integer NOT NULL DEFAULT 0  ,
  Owner integer NOT NULL DEFAULT 0  ,
  Subject varchar(200) NULL DEFAULT '[no subject]' ,
  InitialPriority integer NOT NULL DEFAULT 0  ,
  FinalPriority integer NOT NULL DEFAULT 0  ,
  Priority integer NOT NULL DEFAULT 0  ,
  TimeEstimated integer NOT NULL DEFAULT 0  ,
  TimeWorked integer NOT NULL DEFAULT 0  ,
  Status varchar(64) NULL  ,
  TimeLeft integer NOT NULL DEFAULT 0  ,
  Told DATETIME NULL  ,
  Starts DATETIME NULL  ,
  Started DATETIME NULL  ,
  Due DATETIME NULL  ,
  Resolved DATETIME NULL  ,


  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
CREATE INDEX Tickets2 ON Tickets (Owner) ;
CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type) ;



CREATE TABLE ScripActions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) CHARACTER SET ascii NULL,
  Argument VARBINARY(255) NULL  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;



CREATE TABLE Templates (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Queue integer NOT NULL DEFAULT 0 ,
  Name varchar(200) NOT NULL  ,
  Description varchar(255) NULL  ,
  Type varchar(16) CHARACTER SET ascii NULL  ,
  Content TEXT NULL  ,
  LastUpdated DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;



CREATE TABLE ObjectCustomFieldValues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  CustomField int NOT NULL  ,
  ObjectType varchar(255) CHARACTER SET ascii NOT NULL,     # Final target of the Object
  ObjectId int NOT NULL  ,                  # New -- Replaces Ticket
  SortOrder integer NOT NULL DEFAULT 0  ,   # New -- ordering for multiple values

  Content varchar(255) NULL  ,
  LargeContent LONGBLOB NULL,               # New -- to hold 255+ strings
  ContentType varchar(80) CHARACTER SET ascii NULL,                 # New -- only text/* gets searched
  ContentEncoding varchar(80) CHARACTER SET ascii NULL  ,           # New -- for binary Content

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,        # New -- whether the value was current
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);



CREATE TABLE CustomFields (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Type varchar(200) CHARACTER SET ascii NULL  , # Changed -- 'Single' and 'Multiple' is moved out
  RenderType varchar(64) CHARACTER SET ascii NULL  ,
  MaxValues integer,            # New -- was 'Single'(1) and 'Multiple'(0)
  Pattern TEXT NULL  ,  # New -- Must validate against this
  BasedOn INTEGER NULL,
  ValuesClass varchar(64) CHARACTER SET ascii NULL  ,
  Description varchar(255) NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,
  LookupType varchar(255) CHARACTER SET ascii NOT NULL,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;



CREATE TABLE ObjectCustomFields (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  CustomField integer NOT NULL  ,
  ObjectId integer NOT NULL,
  SortOrder integer NOT NULL DEFAULT 0  ,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;



CREATE TABLE CustomFieldValues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  CustomField int NOT NULL  ,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,
  Category VARCHAR(255) NULL,

  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);




CREATE TABLE Attributes (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(255) NULL  ,
  Description varchar(255) NULL  ,
  Content LONGBLOB,
  ContentType varchar(16) CHARACTER SET ascii,
  ObjectType varchar(64) CHARACTER SET ascii,
  ObjectId integer, # foreign key to anything
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;

CREATE INDEX Attributes1 on Attributes(Name);
CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);



# sessions is used by Apache::Session to keep sessions in the database.
# We should have a reaper script somewhere.

CREATE TABLE sessions (
    id char(32) NOT NULL,
    a_session LONGBLOB,
    LastUpdated TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET ascii;

CREATE TABLE Classes (
  id int(11) NOT NULL auto_increment,
  Name varchar(255) NOT NULL default '',
  Description varchar(255) NOT NULL default '',
  SortOrder int(11) NOT NULL default '0',
  Disabled int(2) NOT NULL default '0',
  Creator int(11) NOT NULL default '0',
  Created datetime default NULL,
  LastUpdatedBy int(11) NOT NULL default '0',
  LastUpdated datetime default NULL,
  HotList int(2) NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Articles (
  id int(11) NOT NULL auto_increment,
  Name varchar(255) NOT NULL default '',
  Summary varchar(255) NOT NULL default '',
  SortOrder int(11) NOT NULL default '0',
  Class int(11) NOT NULL default '0',
  Parent int(11) NOT NULL default '0',
  URI varchar(255) character set ascii default NULL,
  Creator int(11) NOT NULL default '0',
  Created datetime default NULL,
  LastUpdatedBy int(11) NOT NULL default '0',
  LastUpdated datetime default NULL,
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Topics (
  id int(11) NOT NULL auto_increment,
  Parent int(11) NOT NULL default '0',
  Name varchar(255) NOT NULL default '',
  Description varchar(255) NOT NULL default '',
  ObjectType varchar(64) character set ascii NOT NULL default '',
  ObjectId int(11) NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE ObjectTopics (
  id int(11) NOT NULL auto_increment,
  Topic int(11) NOT NULL default '0',
  ObjectType varchar(64) character set ascii NOT NULL default '',
  ObjectId int(11) NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE ObjectClasses (
  id int(11) NOT NULL auto_increment,
  Class int(11) NOT NULL default '0',
  ObjectType varchar(255) character set ascii NOT NULL default '',
  ObjectId int(11) NOT NULL default '0',
  Creator int(11) NOT NULL default '0',
  Created datetime default NULL,
  LastUpdatedBy int(11) NOT NULL default '0',
  LastUpdated datetime default NULL,
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;