-# {{{ Attachments
CREATE TABLE Attachments (
id INTEGER NOT NULL AUTO_INCREMENT,
TransactionId integer NOT NULL ,
Parent integer NOT NULL DEFAULT 0 ,
- MessageId varchar(160) NULL ,
+ MessageId varchar(160) CHARACTER SET ascii NULL ,
Subject varchar(255) NULL ,
Filename varchar(255) NULL ,
- ContentType varchar(80) NULL ,
- ContentEncoding varchar(80) NULL ,
- Content LONGTEXT 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)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
-# }}}
-# {{{ Queues
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 ,
+ 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 ,
LastUpdated DATETIME NULL ,
Disabled int2 NOT NULL DEFAULT 0 ,
PRIMARY KEY (id)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
+
CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
CREATE INDEX Queues2 ON Queues (Disabled) ;
-# }}}
-# {{{ Links
CREATE TABLE Links (
id INTEGER NOT NULL AUTO_INCREMENT,
- Base varchar(240) NULL ,
- Target varchar(240) NULL ,
- Type varchar(20) NOT NULL ,
+ Base varchar(240) NULL,
+ Target varchar(240) NULL,
+ Type varchar(20) NOT NULL,
LocalTarget integer NOT NULL DEFAULT 0 ,
LocalBase integer NOT NULL DEFAULT 0 ,
LastUpdatedBy integer NOT NULL DEFAULT 0 ,
Creator integer NOT NULL DEFAULT 0 ,
Created DATETIME NULL ,
PRIMARY KEY (id)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET ascii;
-CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
CREATE INDEX Links2 ON Links (Base, Type) ;
CREATE INDEX Links3 ON Links (Target, Type) ;
CREATE INDEX Links4 ON Links (Type,LocalBase);
-# }}}
-# {{{ Principals
CREATE TABLE Principals (
id INTEGER AUTO_INCREMENT not null,
ObjectId integer, # foreign key to Users or Groups, depending
Disabled int2 NOT NULL DEFAULT 0 ,
PRIMARY KEY (id)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET ascii;
CREATE INDEX Principals2 ON Principals (ObjectId);
-# }}}
-# {{{ Groups
CREATE TABLE Groups (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(200) NULL ,
Description varchar(255) NULL ,
- Domain varchar(64),
- Type varchar(64),
+ 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)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
-CREATE INDEX Groups2 On Groups (Type, Instance);
+CREATE INDEX Groups2 On Groups (Type, Instance);
-# }}}
-# {{{ ScripConditions
CREATE TABLE ScripConditions (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(200) NULL ,
Description varchar(255) NULL ,
- ExecModule varchar(60) NULL ,
- Argument varchar(255) NULL ,
- ApplicableTransTypes varchar(60) 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)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
-# }}}
-# {{{ Transactions
CREATE TABLE Transactions (
id INTEGER NOT NULL AUTO_INCREMENT,
- EffectiveTicket integer NOT NULL DEFAULT 0 ,
- Ticket integer NOT NULL DEFAULT 0 ,
+ ObjectType varchar(64) CHARACTER SET ascii NOT NULL,
+ ObjectId integer NOT NULL DEFAULT 0 ,
TimeTaken integer NOT NULL DEFAULT 0 ,
- Type varchar(20) NULL ,
- Field varchar(40) NULL ,
+ 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)
-) TYPE=InnoDB;
-CREATE INDEX Transactions1 ON Transactions (Ticket);
-CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
+) ENGINE=InnoDB CHARACTER SET utf8;
+
+CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
-# }}}
-# {{{ Scrips
CREATE TABLE Scrips (
id INTEGER NOT NULL AUTO_INCREMENT,
CustomIsApplicableCode text NULL ,
CustomPrepareCode text NULL ,
CustomCommitCode text NULL ,
- Stage varchar(32) NULL ,
+ Stage varchar(32) CHARACTER SET ascii NULL ,
Queue integer NOT NULL DEFAULT 0 ,
Template integer NOT NULL DEFAULT 0 ,
Creator integer NOT NULL DEFAULT 0 ,
LastUpdatedBy integer NOT NULL DEFAULT 0 ,
LastUpdated DATETIME NULL ,
PRIMARY KEY (id)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
-# }}}
-# {{{ ACL
CREATE TABLE ACL (
id INTEGER NOT NULL AUTO_INCREMENT,
- PrincipalType varchar(25) NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor", "Requestor"
+ PrincipalType varchar(25) CHARACTER SET ascii NOT NULL, #"User" "Group", "Owner", "Cc" "AdminCc", "Requestor"
- PrincipalId integer NOT NULL , #Foreign key to principals
- RightName varchar(25) NOT NULL ,
- ObjectType varchar(25) NOT NULL ,
+ 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,
- DelegatedBy integer NOT NULL default 0, #foreign key to principals with a userid
- DelegatedFrom integer NOT NULL default 0, #foreign key to ACL
+ Creator integer NOT NULL DEFAULT 0 ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NOT NULL DEFAULT 0 ,
+ LastUpdated DATETIME NULL ,
PRIMARY KEY (id)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
-# }}}
-# {{{ GroupMembers
CREATE TABLE GroupMembers (
- id INTEGER NOT NULL AUTO_INCREMENT,
+ 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)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);
-# }}}
-# {{{ GroupMembersCache
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.
+ 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 members recursively.
# Also, this allows us to have the ACL system elide members of disabled groups
PRIMARY KEY (id)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
+CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
-# }}}
-# {{{ Users
CREATE TABLE Users (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(200) NOT NULL ,
- Password varchar(40) NULL ,
- Comments blob NULL ,
- Signature blob NULL ,
+ Password VARCHAR(256) NULL ,
+ AuthToken VARCHAR(16) CHARACTER SET ascii NULL ,
+ Comments TEXT NULL ,
+ Signature TEXT NULL ,
EmailAddress varchar(120) NULL ,
- FreeformContactInfo blob NULL ,
+ FreeformContactInfo TEXT NULL ,
Organization varchar(200) NULL ,
RealName varchar(120) NULL ,
NickName varchar(16) NULL ,
LastUpdatedBy integer NOT NULL DEFAULT 0 ,
LastUpdated DATETIME NULL ,
PRIMARY KEY (id)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX Users1 ON Users (Name) ;
CREATE INDEX Users4 ON Users (EmailAddress);
-# }}}
-# {{{ Tickets
CREATE TABLE Tickets (
id INTEGER NOT NULL AUTO_INCREMENT,
EffectiveId integer NOT NULL DEFAULT 0 ,
Queue integer NOT NULL DEFAULT 0 ,
- Type varchar(16) NULL ,
+ 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 ,
Priority integer NOT NULL DEFAULT 0 ,
TimeEstimated integer NOT NULL DEFAULT 0 ,
TimeWorked integer NOT NULL DEFAULT 0 ,
- Status varchar(10) NULL ,
+ Status varchar(64) NULL ,
TimeLeft integer NOT NULL DEFAULT 0 ,
Told DATETIME NULL ,
Starts DATETIME NULL ,
Created DATETIME NULL ,
Disabled int2 NOT NULL DEFAULT 0 ,
PRIMARY KEY (id)
-) TYPE=InnoDB;
+) 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) ;
-# }}}
-# {{{ ScripActions
CREATE TABLE ScripActions (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(200) NULL ,
Description varchar(255) NULL ,
- ExecModule varchar(60) NULL ,
- Argument 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)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
-# }}}
-# {{{ Templates
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) NULL ,
- Language varchar(16) NULL ,
+ Type varchar(16) CHARACTER SET ascii NULL ,
+ Language varchar(16) CHARACTER SET ascii NULL ,
TranslationOf integer NOT NULL DEFAULT 0 ,
- Content blob 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)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
-# }}}
-# {{{ TicketCustomFieldValues
-CREATE TABLE TicketCustomFieldValues (
+CREATE TABLE ObjectCustomFieldValues (
id INTEGER NOT NULL AUTO_INCREMENT,
- Ticket int NOT NULL ,
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)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
-CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
+CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
+CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
-# }}}
-# {{{ CustomFields
CREATE TABLE CustomFields (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(200) NULL ,
- Type varchar(200) NULL ,
- Queue integer NOT NULL DEFAULT 0 ,
+ 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
+ Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
+ 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 ,
LastUpdated DATETIME NULL ,
Disabled int2 NOT NULL DEFAULT 0 ,
PRIMARY KEY (id)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
+
-CREATE INDEX CustomFields1 on CustomFields (Disabled, Queue);
+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;
-# }}}
-# {{{ CustomFieldValues
CREATE TABLE CustomFieldValues (
id INTEGER NOT NULL AUTO_INCREMENT,
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)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
-
-# }}}
-# {{{ Attributes
+
CREATE TABLE Attributes (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(255) NULL ,
Description varchar(255) NULL ,
- Content text,
- ContentType varchar(16),
- ObjectType varchar(64),
+ 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)
-) TYPE=InnoDB;
+) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX Attributes1 on Attributes(Name);
CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
-# }}}
-# {{{ Sessions
# 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 LONGTEXT,
+ 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;
projects / freeside.git / blobdiff