[rt-devel] Changes for bigint tickets

Rich Lafferty rich+rt at lafferty.ca
Tue Feb 5 16:14:50 EST 2002


Hi --

A local requirement for migration to RT is to use our existing ticket
format, which is essentially YYYYMMDDsss where sss is a serial. It
will be easy to update the sequence/auto_increment in the database to
ensure that each day's tickets are numbered correctly, but a number of
that magnitude is a long, not an int.

I've attached my new schema. When I go to create a new ticket now, I
get an empty error -- through the web UI, it's an "RT error" table
with no text in, and rt-mailgate returns

  Create failed:  /  /  

I also noticed that EffectiveId in the Tickets table remains NULL
rather than matching the id of the ticket as it does for tickets
created prior to the change.

I'm pretty much stumped as to where to go from here, and would
appreciate suggestions on figuring out exactly where things are going
pear-shaped.

Thanks,

   -Rich

-- 
Rich Lafferty --------------+-----------------------------------------------
 Ottawa, Ontario, Canada    |  Save the Pacific Northwest Tree Octopus!
 http://www.lafferty.ca/    |    http://zapatopi.net/treeoctopus.html
rich at lafferty.ca -----------+-----------------------------------------------
-------------- next part --------------
CREATE TABLE KeywordSelects (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(255) NULL  ,
  Keyword integer NULL  ,
  Single integer NULL  ,
  Depth integer NOT NULL DEFAULT 0 ,
  ObjectType varchar(32) NOT NULL  ,
  ObjectField varchar(32) NULL  ,
  ObjectValue varchar(255) NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword);
CREATE INDEX KeywordSelects2 ON KeywordSelects (ObjectType, ObjectField, ObjectValue);
CREATE TABLE Attachments (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  TransactionId integer NOT NULL  ,
  Parent integer NULL  ,
  MessageId varchar(160) NULL  ,
  Subject varchar(255) NULL  ,
  Filename varchar(255) NULL  ,
  ContentType varchar(80) NULL  ,
  ContentEncoding varchar(80) NULL  ,
  Content LONGTEXT NULL  ,
  Headers LONGTEXT NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE INDEX Attachments1 ON Attachments (Parent);
CREATE INDEX Attachments2 ON Attachments (TransactionId);
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
CREATE TABLE Queues (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(120) NOT NULL  ,
  Description varchar(120) NULL  ,
  CorrespondAddress varchar(120) NULL  ,
  CommentAddress varchar(120) NULL  ,
  InitialPriority integer NULL  ,
  FinalPriority integer NULL  ,
  DefaultDueIn integer NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Queues1 ON Queues (Name);
CREATE TABLE Links (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Base varchar(240) NULL  ,
  Target varchar(240) NULL  ,
  Type varchar(20) NOT NULL  ,
  LocalTarget bigint NULL  ,
  LocalBase bigint NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
CREATE TABLE Groups (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(16) NULL  ,
  Description varchar(64) NULL  ,
  Pseudo integer NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Groups1 ON Groups (Name);
CREATE TABLE Watchers (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Type varchar(16) NULL  ,
  Scope varchar(16) NULL  ,
  Value bigint NULL  ,
  Email varchar(255) NULL  ,
  Quiet integer NULL  ,
  Owner integer NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner);
CREATE TABLE ScripConditions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(255) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) NULL  ,
  Argument varchar(255) NULL  ,
  ApplicableTransTypes varchar(60) NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE TABLE Transactions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  EffectiveTicket bigint NULL  ,
  Ticket bigint NULL  ,
  TimeTaken integer NULL  ,
  Type varchar(20) NULL  ,
  Field varchar(40) NULL  ,
  OldValue varchar(255) NULL  ,
  NewValue varchar(255) NULL  ,
  Data varchar(100) NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE INDEX Transactions1 ON Transactions (Ticket);
CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
CREATE TABLE Scrips (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  ScripCondition integer NULL  ,
  ScripAction integer NULL  ,
  Stage varchar(32) NULL  ,
  Queue integer NULL  ,
  Template integer NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE TABLE ACL (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  PrincipalId integer NULL  ,
  PrincipalType varchar(25) NULL  ,
  RightName varchar(25) NULL  ,
  RightScope varchar(25) NULL  ,
  RightAppliesTo integer NULL  ,
  PRIMARY KEY (id)
);
CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId);
CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId);
CREATE TABLE GroupMembers (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  GroupId integer NULL  ,
  UserId integer NULL  ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId);
CREATE TABLE ObjectKeywords (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Keyword integer NOT NULL  ,
  KeywordSelect integer NOT NULL  ,
  ObjectType varchar(32) NOT NULL  ,
  ObjectId integer NOT NULL  ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords (ObjectId, ObjectType, KeywordSelect, Keyword);
CREATE INDEX ObjectKeywords2 ON ObjectKeywords (ObjectId, ObjectType);
CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword);
CREATE TABLE Keywords (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(255) NOT NULL  ,
  Description varchar(255) NULL  ,
  Parent integer NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent);
CREATE INDEX Keywords2 ON Keywords (Name);
CREATE INDEX Keywords3 ON Keywords (Parent);
CREATE TABLE Users (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(120) NOT NULL  ,
  Password varchar(40) NULL  ,
  Comments blob NULL  ,
  Signature blob NULL  ,
  EmailAddress varchar(120) NULL  ,
  FreeformContactInfo blob NULL  ,
  Organization varchar(200) NULL  ,
  Privileged integer 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  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX Users1 ON Users (Name);
CREATE INDEX Users2 ON Users (Name);
CREATE INDEX Users3 ON Users (id, EmailAddress);
CREATE INDEX Users4 ON Users (EmailAddress);
CREATE TABLE Tickets (
  id BIGINT NOT NULL  AUTO_INCREMENT,
  EffectiveId bigint NULL  ,
  Queue integer NULL  ,
  Type varchar(16) NULL  ,
  IssueStatement integer NULL  ,
  Resolution integer NULL  ,
  Owner integer NULL  ,
  Subject varchar(200) NULL DEFAULT '[no subject]' ,
  InitialPriority integer NULL  ,
  FinalPriority integer NULL  ,
  Priority integer NULL  ,
  Status varchar(10) NULL  ,
  TimeWorked integer NULL  ,
  TimeLeft integer NULL  ,
  Told DATETIME NULL  ,
  Starts DATETIME NULL  ,
  Started DATETIME NULL  ,
  Due DATETIME NULL  ,
  Resolved DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id)
);
CREATE INDEX Tickets1 ON Tickets (Queue, Status);
CREATE INDEX Tickets2 ON Tickets (Owner);
CREATE INDEX Tickets3 ON Tickets (EffectiveId);
CREATE INDEX Tickets4 ON Tickets (id, Status);
CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
CREATE TABLE ScripActions (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Name varchar(255) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) NULL  ,
  Argument varchar(255) NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  LastUpdated DATETIME NULL  ,
  PRIMARY KEY (id)
);
CREATE TABLE Templates (
  id INTEGER NOT NULL  AUTO_INCREMENT,
  Queue integer NOT NULL DEFAULT 0 ,
  Name varchar(40) NOT NULL  ,
  Description varchar(120) NULL  ,
  Type varchar(16) NULL  ,
  Language varchar(16) NULL  ,
  TranslationOf integer NULL  ,
  Content blob NULL  ,
  LastUpdated DATETIME NULL  ,
  LastUpdatedBy integer NULL  ,
  Creator integer NULL  ,
  Created DATETIME NULL  ,
  PRIMARY KEY (id)
);


More information about the Rt-devel mailing list