X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=rt%2Fetc%2Fschema.SQLite;h=138971cfbb3d1fcb5e71fe27722e7fefa6eb9c32;hb=85e677b86fc37c54e6de2b06340351a28f5a5916;hp=b10ff46d10f055f4162a7602079fb2ce1f5fb65c;hpb=289340780927b5bac2c7604d7317c3063c6dd8cc;p=freeside.git diff --git a/rt/etc/schema.SQLite b/rt/etc/schema.SQLite index b10ff46d1..138971cfb 100644 --- a/rt/etc/schema.SQLite +++ b/rt/etc/schema.SQLite @@ -28,6 +28,8 @@ CREATE TABLE Queues ( Description varchar(255) NULL , CorrespondAddress varchar(120) NULL , CommentAddress varchar(120) NULL , + Lifecycle varchar(32) NULL , + SubjectTag varchar(120) NULL , InitialPriority integer NULL , FinalPriority integer NULL , DefaultDueIn integer NULL , @@ -82,7 +84,11 @@ CREATE TABLE Groups ( Description varchar(255) NULL , Domain varchar(64), Type varchar(64), - Instance integer + Instance integer, + Creator integer NOT NULL DEFAULT 0 , + Created DATETIME NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated DATETIME NULL ) ; @@ -112,21 +118,23 @@ CREATE TABLE ScripConditions ( --- {{{ 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); --- }}} @@ -163,8 +171,10 @@ CREATE TABLE ACL ( RightName varchar(25) NOT NULL , ObjectType varchar(25) NOT NULL , ObjectId INTEGER default 0, - DelegatedBy integer NOT NULL default 0, - DelegatedFrom integer NOT NULL default 0 + Creator integer NOT NULL DEFAULT 0 , + Created DATETIME NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated DATETIME NULL ) ; @@ -176,7 +186,11 @@ CREATE TABLE ACL ( CREATE TABLE GroupMembers ( id INTEGER PRIMARY KEY , GroupId integer NULL, - MemberId integer NULL + MemberId integer NULL, + Creator integer NOT NULL DEFAULT 0 , + Created DATETIME NULL , + LastUpdatedBy integer NOT NULL DEFAULT 0 , + LastUpdated DATETIME NULL ) ; @@ -206,7 +220,8 @@ create table CachedGroupMembers ( CREATE TABLE Users ( id INTEGER , Name varchar(200) NOT NULL , - Password varchar(40) NULL , + Password varchar(256) NULL , + AuthToken varchar(16) NULL , Comments blob NULL , Signature blob NULL , EmailAddress varchar(120) NULL , @@ -267,7 +282,7 @@ CREATE TABLE Tickets ( Priority integer NULL , TimeEstimated integer NULL , TimeWorked integer NULL , - Status varchar(10) NULL , + Status varchar(64) NULL , TimeLeft integer NULL , Told DATETIME NULL , Starts DATETIME NULL , @@ -329,62 +344,163 @@ CREATE TABLE Templates ( --- }}} ---- {{{ TicketCustomFieldValues -CREATE TABLE TicketCustomFieldValues ( - id INTEGER PRIMARY KEY , - Ticket int NOT NULL , + + +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 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 + RenderType varchar(64) NULL , + 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 + BasedOn INTEGER NULL, + ValuesClass varchar(64) NULL , 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) +) ; + + + +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 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 , + Category varchar(255) NULL , - 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); --- }}} + +CREATE TABLE Classes ( +id INTEGER PRIMARY KEY, +Name varchar(255) NOT NULL DEFAULT '', +Description varchar(255) NOT NULL DEFAULT '', +SortOrder integer NOT NULL DEFAULT 0, +Disabled smallint NOT NULL DEFAULT 0, +Creator integer NOT NULL DEFAULT 0, +Created TIMESTAMP NULL, +LastUpdatedBy integer NOT NULL DEFAULT 0, +LastUpdated TIMESTAMP NULL, +HotList smallint NOT NULL DEFAULT 0 +); + +CREATE TABLE Articles ( +id INTEGER PRIMARY KEY, +Name varchar(255) NOT NULL DEFAULT '', +Summary varchar(255) NOT NULL DEFAULT '', +SortOrder integer NOT NULL DEFAULT 0, +Class integer NOT NULL DEFAULT 0, +Parent integer NOT NULL DEFAULT 0, +URI varchar(255), +Creator integer NOT NULL DEFAULT 0, +Created TIMESTAMP NULL, +LastUpdatedBy integer NOT NULL DEFAULT 0, +LastUpdated TIMESTAMP NULL +); + + +CREATE TABLE Topics ( +id INTEGER PRIMARY KEY, +Parent integer NOT NULL DEFAULT 0, +Name varchar(255) NOT NULL DEFAULT '', +Description varchar(255) NOT NULL DEFAULT '', +ObjectType varchar(64) NOT NULL DEFAULT '', +ObjectId integer NOT NULL +); + + +CREATE TABLE ObjectTopics ( +id INTEGER PRIMARY KEY, +Topic integer NOT NULL, +ObjectType varchar(64) NOT NULL DEFAULT '', +ObjectId integer NOT NULL +); + +CREATE TABLE ObjectClasses ( +id INTEGER PRIMARY KEY, +Class integer NOT NULL, +ObjectType varchar(64) NOT NULL DEFAULT '', +ObjectId integer NOT NULL, +Creator integer NOT NULL DEFAULT 0, +Created TIMESTAMP NULL, +LastUpdatedBy integer NOT NULL DEFAULT 0, +LastUpdated TIMESTAMP NULL +);