-- --------------------------------------- --
--  RT 3.3 Schema redesign v7: 2004-11-08  --
-- --------------------------------------- --

TABLE CustomFields (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(200) NULL  ,
  Type varchar(200) NULL  ,                 -- Changed: see MaxValues below
  MaxValues integer,                        -- New: 1 = Single, 0 = Multiple
  Pattern varchar(255) NULL  ,              -- New: regex to validate against
  Repeated int2 NOT NULL DEFAULT 0 ,        -- New: repeated table entry
  LookupType varchar(255) NOT NULL,         -- New: "RT::Queue-RT::Ticket"
  Description varchar(255) NULL  ,
  SortOrder integer NOT NULL DEFAULT 0  ,   -- only used on "pick CF" screen
)

-- This table replaces the "Queue" field in CustomFields
TABLE ObjectCustomFields (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  CustomField int NOT NULL  ,               -- CustomField ID
  ObjectId integer NOT NULL,                -- Final id of toplevel parent, or 
                                            -- the object itself if ParentType
                                            -- is empty; 0 means global as usual
  SortOrder integer NOT NULL DEFAULT 0  ,   -- this is used to sort the CFs
);

-- This table replaces TicketCustomFieldValues
TABLE ObjectCustomFieldValues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  CustomField int NOT NULL  ,
  ObjectType varchar(255) NOT NULL,         -- Final target of the Object
  ObjectId int NOT NULL  ,                  -- New: replaces the "Ticket" field
  SortOrder integer NOT NULL DEFAULT 0  ,   -- New: for Repeated fields

  Content varchar(255) NULL  ,
  LargeContent LONGTEXT NULL,               -- New: data longer than 255 bytes
  ContentType varchar(80) NULL,             -- New: MIME type of LargeContent
  ContentEncoding varchar(80) NULL  ,       -- New: for binary LargeContent
  Disabled int2 NOT NULL DEFAULT 0 ,        -- New: whether this is deleted
)

TABLE Transactions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  ObjectType varchar(255) NULL,             -- Final target of the Object
  ObjectId integer NOT NULL DEFAULT 0  ,    -- New: replaces the "Ticket" field
  TimeTaken integer NOT NULL DEFAULT 0  ,
  Type varchar(20) NULL  ,
  Field varchar(40) NULL  ,
  OldValue varchar(255) NULL  ,
  NewValue varchar(255) NULL  ,
  ReferenceType varchar(255) NULL,          -- NeW: Currently "RT::OCFV" only
  OldReference integer NULL  ,              -- New: Id of ReferenceType
  NewReference integer NULL  ,              -- New: Id of ReferenceType
  Data varchar(255) NULL  ,
)

-- vim: filetype=mysql shiftwidth=4 expandtab