X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=rt%2Fetc%2Fschema.SQLite;h=6897be2d69e84b75a7589a1d4c69bf55b004cc8b;hb=refs%2Fheads%2Frt_28256;hp=174664662958591a4763d21023a90c54f8bb7c92;hpb=f7fd2a3e34da751cbc02bbf215e99c6dc89adc15;p=freeside.git diff --git a/rt/etc/schema.SQLite b/rt/etc/schema.SQLite index 174664662..6897be2d6 100644 --- a/rt/etc/schema.SQLite +++ b/rt/etc/schema.SQLite @@ -3,7 +3,7 @@ CREATE TABLE Attachments ( id INTEGER PRIMARY KEY , TransactionId INTEGER , - Parent integer NULL , + Parent integer NULL DEFAULT 0 , MessageId varchar(160) NULL , Subject varchar(255) NULL , Filename varchar(255) NULL , @@ -11,7 +11,7 @@ CREATE TABLE Attachments ( ContentEncoding varchar(80) NULL , Content LONGTEXT NULL , Headers LONGTEXT NULL , - Creator integer NULL , + Creator integer NULL DEFAULT 0 , Created DATETIME NULL ) ; @@ -28,12 +28,14 @@ CREATE TABLE Queues ( Description varchar(255) NULL , CorrespondAddress varchar(120) NULL , CommentAddress varchar(120) NULL , - InitialPriority integer NULL , - FinalPriority integer NULL , - DefaultDueIn integer NULL , - Creator integer NULL , + Lifecycle varchar(32) NULL , + SubjectTag varchar(120) NULL , + InitialPriority integer NULL DEFAULT 0 , + FinalPriority integer NULL DEFAULT 0 , + DefaultDueIn integer NULL DEFAULT 0 , + Creator integer NULL DEFAULT 0 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NULL DEFAULT 0 , LastUpdated DATETIME NULL , Disabled int2 NOT NULL DEFAULT 0 @@ -49,11 +51,11 @@ CREATE TABLE Links ( Base varchar(240) NULL , Target varchar(240) NULL , Type varchar(20) NOT NULL , - LocalTarget integer NULL , - LocalBase integer NULL , - LastUpdatedBy integer NULL , + LocalTarget integer NULL DEFAULT 0 , + LocalBase integer NULL DEFAULT 0 , + LastUpdatedBy integer NULL DEFAULT 0 , LastUpdated DATETIME NULL , - Creator integer NULL , + Creator integer NULL DEFAULT 0 , Created DATETIME 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 ) ; @@ -100,9 +106,9 @@ CREATE TABLE ScripConditions ( Argument varchar(255) NULL , ApplicableTransTypes varchar(60) NULL , - Creator integer NULL , + Creator integer NULL DEFAULT 0 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer 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 , - TimeTaken integer NULL , + ObjectType varchar(255) NULL , + ObjectId integer NULL DEFAULT 0 , + TimeTaken integer NULL DEFAULT 0 , 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 , + Creator integer NULL DEFAULT 0 , Created DATETIME NULL ) ; -CREATE INDEX Transactions1 ON Transactions (Ticket); -CREATE INDEX Transactions2 ON Transactions (EffectiveTicket); +CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId); --- }}} @@ -135,19 +143,19 @@ CREATE INDEX Transactions2 ON Transactions (EffectiveTicket); CREATE TABLE Scrips ( id INTEGER PRIMARY KEY , Description varchar(255), - ScripCondition integer NULL , - ScripAction integer NULL , + ScripCondition integer NULL DEFAULT 0 , + ScripAction integer NULL DEFAULT 0 , ConditionRules text NULL , ActionRules text NULL , CustomIsApplicableCode text NULL , CustomPrepareCode text NULL , CustomCommitCode text NULL , Stage varchar(32) NULL , - Queue integer NULL , - Template integer NULL , - Creator integer NULL , + Queue integer NULL DEFAULT 0 , + Template integer NULL DEFAULT 0 , + Creator integer NULL DEFAULT 0 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NULL DEFAULT 0 , LastUpdated DATETIME NULL ) ; @@ -159,12 +167,14 @@ CREATE TABLE ACL ( id INTEGER PRIMARY KEY , PrincipalType varchar(25) NOT NULL, - PrincipalId INTEGER, + PrincipalId INTEGER DEFAULT 0, 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 ) ; @@ -175,8 +185,12 @@ CREATE TABLE ACL ( CREATE TABLE GroupMembers ( id INTEGER PRIMARY KEY , - GroupId integer NULL, - MemberId integer NULL + GroupId integer NULL DEFAULT 0, + MemberId integer NULL DEFAULT 0, + 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 , @@ -235,9 +250,9 @@ CREATE TABLE Users ( Timezone char(50) NULL , PGPKey text NULL, - Creator integer NULL , + Creator integer NULL DEFAULT 0 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NULL DEFAULT 0 , LastUpdated DATETIME NULL ) ; @@ -255,20 +270,20 @@ CREATE INDEX Users4 ON Users (EmailAddress); CREATE TABLE Tickets ( id INTEGER PRIMARY KEY , - EffectiveId integer NULL , - Queue integer NULL , + EffectiveId integer NULL DEFAULT 0 , + Queue integer NULL DEFAULT 0 , Type varchar(16) NULL , - IssueStatement integer NULL , - Resolution integer NULL , - Owner integer NULL , + IssueStatement integer NULL DEFAULT 0 , + Resolution integer NULL DEFAULT 0 , + Owner integer NULL DEFAULT 0 , Subject varchar(200) NULL DEFAULT '[no subject]' , - InitialPriority integer NULL , - FinalPriority integer NULL , - Priority integer NULL , - TimeEstimated integer NULL , - TimeWorked integer NULL , - Status varchar(10) NULL , - TimeLeft integer NULL , + InitialPriority integer NULL DEFAULT 0 , + FinalPriority integer NULL DEFAULt 0 , + Priority integer NULL DEFAULT 0 , + TimeEstimated integer NULL DEFAULT 0 , + TimeWorked integer NULL DEFAULT 0 , + Status varchar(64) NULL , + TimeLeft integer NULL DEFAULT 0 , Told DATETIME NULL , Starts DATETIME NULL , Started DATETIME NULL , @@ -276,9 +291,9 @@ CREATE TABLE Tickets ( Resolved DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NULL DEFAULT 0 , LastUpdated DATETIME NULL , - Creator integer NULL , + Creator integer NULL DEFAULT 0 , Created DATETIME NULL , Disabled int2 NOT NULL DEFAULT 0 @@ -300,9 +315,9 @@ CREATE TABLE ScripActions ( Description varchar(255) NULL , ExecModule varchar(60) NULL , Argument varchar(255) NULL , - Creator integer NULL , + Creator integer NULL DEFAULT 0 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NULL DEFAULT 0 , LastUpdated DATETIME NULL ) ; @@ -318,76 +333,101 @@ CREATE TABLE Templates ( Description varchar(255) NULL , Type varchar(16) NULL , Language varchar(16) NULL , - TranslationOf integer NULL , + TranslationOf integer NULL DEFAULT 0 , Content blob NULL , LastUpdated DATETIME NULL , - LastUpdatedBy integer NULL , - Creator integer NULL , + LastUpdatedBy integer NULL DEFAULT 0 , + Creator integer NULL DEFAULT 0 , Created DATETIME NULL ) ; --- }}} ---- {{{ 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 ( @@ -397,10 +437,10 @@ CREATE TABLE Attributes ( Content LONGTEXT NULL , ContentType varchar(16), ObjectType varchar(25) NOT NULL , - ObjectId INTEGER default 0, - Creator integer NULL , + ObjectId INTEGER , + Creator integer NULL DEFAULT 0 , Created DATETIME NULL , - LastUpdatedBy integer NULL , + LastUpdatedBy integer NULL DEFAULT 0 , LastUpdated DATETIME NULL ) ; @@ -409,3 +449,58 @@ 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 DEFAULT 0 +); + + +CREATE TABLE ObjectTopics ( +id INTEGER PRIMARY KEY, +Topic integer NOT NULL DEFAULT 0, +ObjectType varchar(64) NOT NULL DEFAULT '', +ObjectId integer NOT NULL DEFAULT 0 +); + +CREATE TABLE ObjectClasses ( +id INTEGER PRIMARY KEY, +Class integer NOT NULL DEFAULT 0, +ObjectType varchar(64) NOT NULL DEFAULT '', +ObjectId integer NOT NULL DEFAULT 0, +Creator integer NOT NULL DEFAULT 0, +Created TIMESTAMP NULL, +LastUpdatedBy integer NOT NULL DEFAULT 0, +LastUpdated TIMESTAMP NULL +);