X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=rt%2Fetc%2Fschema.mysql;h=9ed0337aa05526a0f4a1d0cbd8e57267bea25172;hb=a72a10f754f7465121d6137bb3dcee0a21ea6443;hp=7e715c2011605e24ed52cd78b41bc5a8e0fde503;hpb=3ef62a0570055da710328937e7f65dbb2c027c62;p=freeside.git

diff --git a/rt/etc/schema.mysql b/rt/etc/schema.mysql
old mode 100755
new mode 100644
index 7e715c201..9ed0337aa
--- a/rt/etc/schema.mysql
+++ b/rt/etc/schema.mysql
@@ -1,183 +1,225 @@
-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  ,
+  Parent integer NOT NULL DEFAULT 0  ,
+  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 NULL  ,
+  Creator integer NOT NULL DEFAULT 0  ,
   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);
+) 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(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  ,
+  Name varchar(200) NOT NULL  ,
+  Description varchar(255) NULL  ,
+  CorrespondAddress varchar(120) CHARACTER SET ascii NULL,
+  CommentAddress varchar(120) CHARACTER SET ascii NULL,
+  Lifecycle varchar(32) CHARACTER SET ascii 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 NULL  ,
+  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   Disabled int2 NOT NULL DEFAULT 0 ,
   PRIMARY KEY (id)
-);
-CREATE UNIQUE INDEX Queues1 ON Queues (Name);
+) 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) NOT NULL  ,
-  LocalTarget integer NULL  ,
-  LocalBase integer NULL  ,
-  LastUpdatedBy integer 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  ,
   LastUpdated DATETIME NULL  ,
-  Creator integer NULL  ,
+  Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
   PRIMARY KEY (id)
-);
-CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
+) ENGINE=InnoDB CHARACTER SET ascii;
+
+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(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 integer NULL  ,
-  Email varchar(255) NULL  ,
-  Quiet integer NULL  ,
-  Owner integer NULL  ,
-  Creator integer NULL  ,
+  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 NULL  ,
+  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   PRIMARY KEY (id)
-);
-CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner);
+) ENGINE=InnoDB CHARACTER SET utf8;
+
+CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
+CREATE INDEX Groups2 On Groups (Type, Instance);
+
+
+
 CREATE TABLE ScripConditions (
   id INTEGER NOT NULL  AUTO_INCREMENT,
-  Name varchar(255) NULL  ,
+  Name varchar(200) NULL  ,
   Description varchar(255) NULL  ,
-  ExecModule varchar(60) NULL  ,
-  Argument varchar(255) NULL  ,
-  ApplicableTransTypes varchar(60) NULL  ,
-  Creator integer 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 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,
-  EffectiveTicket integer NULL  ,
-  Ticket integer NULL  ,
-  TimeTaken integer NULL  ,
-  Type varchar(20) NULL  ,
-  Field varchar(40) NULL  ,
+  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  ,
-  Data varchar(100) NULL  ,
-  Creator integer 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)
-);
-CREATE INDEX Transactions1 ON Transactions (Ticket);
-CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
+) ENGINE=InnoDB CHARACTER SET utf8;
+
+CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
+
+
+
 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  ,
+  Description varchar(255),
+  ScripCondition integer NOT NULL DEFAULT 0  ,
+  ScripAction integer NOT NULL DEFAULT 0  ,
+  ConditionRules text NULL  ,
+  ActionRules text NULL  ,
+  CustomIsApplicableCode text NULL  ,
+  CustomPrepareCode text NULL  ,
+  CustomCommitCode text 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  ,
   Created DATETIME NULL  ,
-  LastUpdatedBy integer NULL  ,
+  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
   PRIMARY KEY (id)
-);
+) ENGINE=InnoDB CHARACTER SET utf8;
+
+
 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  ,
+  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)
-);
-CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId);
-CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId);
+) 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 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 ,
+  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)
-);
-CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent);
-CREATE INDEX Keywords2 ON Keywords (Name);
-CREATE INDEX Keywords3 ON Keywords (Parent);
+) 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 CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
+
+
+
 CREATE TABLE Users (
   id INTEGER NOT NULL  AUTO_INCREMENT,
-  Name varchar(120) NOT NULL  ,
-  Password varchar(40) NULL  ,
-  Comments blob NULL  ,
-  Signature blob NULL  ,
+  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 blob NULL  ,
+  FreeformContactInfo TEXT NULL  ,
   Organization varchar(200) NULL  ,
-  Privileged integer NULL  ,
   RealName varchar(120) NULL  ,
-  Nickname varchar(16) NULL  ,
+  NickName varchar(16) NULL  ,
   Lang varchar(16) NULL  ,
   EmailEncoding varchar(16) NULL  ,
   WebEncoding varchar(16) NULL  ,
@@ -196,72 +238,262 @@ CREATE TABLE Users (
   State varchar(100) NULL  ,
   Zip varchar(16) NULL  ,
   Country varchar(50) NULL  ,
-  Creator integer NULL  ,
+  Timezone varchar(50) NULL  ,
+  PGPKey text NULL,
+
+  Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
-  LastUpdatedBy integer NULL  ,
+  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
-  Disabled int2 NOT NULL DEFAULT 0 ,
   PRIMARY KEY (id)
-);
-CREATE UNIQUE INDEX Users1 ON Users (Name);
-CREATE INDEX Users3 ON Users (id, EmailAddress);
+) 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 NULL  ,
-  Queue integer NULL  ,
-  Type varchar(16) NULL  ,
-  IssueStatement integer NULL  ,
-  Resolution integer NULL  ,
-  Owner integer NULL  ,
+  EffectiveId integer NOT NULL DEFAULT 0  ,
+  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 NULL  ,
-  FinalPriority integer NULL  ,
-  Priority integer NULL  ,
-  Status varchar(10) NULL  ,
-  TimeWorked integer NULL  ,
-  TimeLeft integer NULL  ,
+  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 NULL  ,
+
+
+  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
   LastUpdated DATETIME NULL  ,
-  Creator integer NULL  ,
+  Creator integer NOT NULL DEFAULT 0  ,
   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);
+) 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(255) NULL  ,
+  Name varchar(200) NULL  ,
   Description varchar(255) NULL  ,
-  ExecModule varchar(60) NULL  ,
-  Argument varchar(255) NULL  ,
-  Creator integer NULL  ,
+  ExecModule varchar(60) CHARACTER SET ascii NULL,
+  Argument VARBINARY(255) NULL  ,
+  Creator integer NOT NULL DEFAULT 0  ,
   Created DATETIME NULL  ,
-  LastUpdatedBy integer 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(40) NOT NULL  ,
-  Description varchar(120) NULL  ,
-  Type varchar(16) NULL  ,
-  Language varchar(16) NULL  ,
-  TranslationOf integer NULL  ,
-  Content blob NULL  ,
+  Name varchar(200) NOT NULL  ,
+  Description varchar(255) NULL  ,
+  Type varchar(16) CHARACTER SET ascii NULL  ,
+  Language varchar(16) CHARACTER SET ascii NULL  ,
+  TranslationOf integer NOT NULL DEFAULT 0  ,
+  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
+  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  ,
+  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  ,
-  LastUpdatedBy integer NULL  ,
-  Creator integer 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;