--- {{{ Transactions
CREATE TABLE Transactions (
id INTEGER PRIMARY KEY ,
- EffectiveTicket integer NULL ,
- Ticket integer NULL ,
+ ObjectType varchar(255) NULL ,
+ ObjectId integer NULL ,
TimeTaken integer NULL ,
Type varchar(20) NULL ,
Field varchar(40) NULL ,
OldValue varchar(255) NULL ,
NewValue varchar(255) NULL ,
+ ReferenceType varchar(255) NULL ,
+ OldReference integer NULL ,
+ NewReference integer NULL ,
Data varchar(255) NULL ,
Creator integer NULL ,
Created DATETIME NULL
) ;
-CREATE INDEX Transactions1 ON Transactions (Ticket);
-CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
+CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
--- }}}
--- }}}
---- {{{ TicketCustomFieldValues
-CREATE TABLE TicketCustomFieldValues (
- id INTEGER PRIMARY KEY ,
- Ticket int NOT NULL ,
+
+# {{{ ObjectCustomFieldValues
+
+CREATE TABLE ObjectCustomFieldValues (
+ id INTEGER NOT NULL ,
CustomField int NOT NULL ,
+ ObjectType varchar(255) NOT NULL, # Final target of the Object
+ ObjectId int NOT NULL , # New -- Replaces Ticket
+ SortOrder integer NOT NULL DEFAULT 0 ,
+
Content varchar(255) NULL ,
+ LargeContent LONGTEXT NULL, # New -- to hold 255+ strings
+ ContentType varchar(80) NULL, # New -- only text/* gets searched
+ ContentEncoding varchar(80) NULL , # New -- for binary Content
- Creator integer NULL ,
+ Creator integer NOT NULL DEFAULT 0 ,
Created DATETIME NULL ,
- LastUpdatedBy integer NULL ,
- LastUpdated DATETIME NULL
-
+ LastUpdatedBy integer NOT NULL DEFAULT 0 ,
+ LastUpdated DATETIME NULL ,
+ Disabled int2 NOT NULL DEFAULT 0 ,
+ PRIMARY KEY (id)
) ;
-CREATE INDEX TicketCustomFieldValues1 ON TicketCustomFieldValues (CustomField,Ticket,Content);
-CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues (CustomField,Ticket);
+CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
+CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
---- }}}
+# }}}
---- {{{ CustomFields
+# {{{ CustomFields
CREATE TABLE CustomFields (
- id INTEGER PRIMARY KEY ,
+ id INTEGER NOT NULL ,
Name varchar(200) NULL ,
- Type varchar(200) NULL ,
- Queue int NULL ,
+ Type varchar(200) NULL , # Changed -- 'Single' and 'Multiple' is moved out
+ MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
+ Pattern varchar(65536) NULL , # New -- Must validate against this
+ Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
Description varchar(255) NULL ,
- SortOrder integer NULL ,
+ SortOrder integer NOT NULL DEFAULT 0 ,
+ LookupType varchar(255) NOT NULL,
- Creator integer NULL ,
+ Creator integer NOT NULL DEFAULT 0 ,
Created DATETIME NULL ,
- LastUpdatedBy integer NULL ,
- LastUpdated DATETIME NULL ,
- Disabled int2 NOT NULL DEFAULT 0
-
+ LastUpdatedBy integer NOT NULL DEFAULT 0 ,
+ LastUpdated DATETIME NULL ,
+ Disabled int2 NOT NULL DEFAULT 0 ,
+ PRIMARY KEY (id)
) ;
---- }}}
+# }}}
+
+# {{{ ObjectCustomFields
+
+CREATE TABLE ObjectCustomFields (
+ id INTEGER NOT NULL ,
+ CustomField int 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)
+) ;
+
+# }}}
---- {{{ CustomFieldValues
+# {{{ CustomFieldValues
CREATE TABLE CustomFieldValues (
- id INTEGER PRIMARY KEY ,
+ id INTEGER NOT NULL ,
CustomField int NOT NULL ,
Name varchar(200) NULL ,
Description varchar(255) NULL ,
- SortOrder integer NULL ,
+ SortOrder integer NOT NULL DEFAULT 0 ,
- Creator integer NULL ,
+ Creator integer NOT NULL DEFAULT 0 ,
Created DATETIME NULL ,
- LastUpdatedBy integer NULL ,
- LastUpdated DATETIME NULL
-
+ LastUpdatedBy integer NOT NULL DEFAULT 0 ,
+ LastUpdated DATETIME NULL ,
+ PRIMARY KEY (id)
) ;
CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
+
+# }}}
+
+--- {{{ Attributes
+CREATE TABLE Attributes (
+ id INTEGER PRIMARY KEY ,
+ Name varchar(255) NOT NULL ,
+ Description varchar(255) NULL ,
+ Content LONGTEXT NULL ,
+ ContentType varchar(16),
+ ObjectType varchar(25) NOT NULL ,
+ ObjectId INTEGER default 0,
+ Creator integer NULL ,
+ Created DATETIME NULL ,
+ LastUpdatedBy integer NULL ,
+ LastUpdated DATETIME NULL
+
+) ;
+CREATE INDEX Attributes1 on Attributes(Name);
+CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
--- }}}
+