[usit-rt.git] / etc / schema.SQLite

--- {{{ Attachments

CREATE TABLE Attachments (
  id INTEGER PRIMARY KEY  ,
  TransactionId INTEGER  ,
  Parent integer NULL DEFAULT 0 ,
  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 DEFAULT 0 ,
  Created DATETIME NULL 
  
) ;

CREATE INDEX Attachments1 ON Attachments (Parent) ;
CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
--- }}}

--- {{{ Queues
CREATE TABLE Queues (
  id INTEGER PRIMARY KEY  ,
  Name varchar(200) NOT NULL  ,
  Description varchar(255) NULL  ,
  CorrespondAddress varchar(120) NULL  ,
  CommentAddress varchar(120) 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 DEFAULT 0 ,
  LastUpdated DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0 
 
) ;
CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;

--- }}}

--- {{{ Links

CREATE TABLE Links (
  id INTEGER PRIMARY KEY  ,
  Base varchar(240) NULL  ,
  Target varchar(240) NULL  ,
  Type varchar(20) NOT NULL  ,
  LocalTarget integer NULL DEFAULT 0 ,
  LocalBase integer NULL DEFAULT 0 ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  
  
) ;
CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
CREATE INDEX Links4 ON Links(Type,LocalBase);

--- }}}

--- {{{ Principals

CREATE TABLE Principals (
        id INTEGER PRIMARY KEY,
        PrincipalType VARCHAR(16) not null,
        ObjectId integer,
        Disabled int2 NOT NULL DEFAULT 0 
        
) ;

--- }}}

--- {{{ Groups

CREATE TABLE Groups (
  id INTEGER ,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  Domain varchar(64),
  Type varchar(64),
  Instance integer,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL
  
) ;

CREATE UNIQUE INDEX Groups1 ON Groups (Name,Domain,Type,Instance) ;

--- }}}

--- {{{ ScripConditions

CREATE TABLE ScripConditions (
  id INTEGER PRIMARY KEY  ,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) NULL  ,
  Argument varchar(255) NULL  ,
  ApplicableTransTypes varchar(60) NULL  ,

  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  
  
) ;

--- }}}

--- {{{ Transactions
CREATE TABLE Transactions (
  id INTEGER PRIMARY KEY  ,
  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 DEFAULT 0 ,
  Created DATETIME NULL  
  
) ;
CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);

--- }}}

--- {{{ Scrips 

CREATE TABLE Scrips (
  id INTEGER PRIMARY KEY  ,
  Description varchar(255),
  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 DEFAULT 0 ,
  Template integer NULL DEFAULT 0 ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  
  
) ;

--- }}}

--- {{{ ACL
CREATE TABLE ACL (
  id INTEGER PRIMARY KEY  ,
  PrincipalType varchar(25) NOT NULL,

  PrincipalId INTEGER DEFAULT 0,
  RightName varchar(25) NOT NULL  ,
  ObjectType varchar(25) NOT NULL  ,
  ObjectId INTEGER default 0,
  Creator integer NOT NULL DEFAULT 0  ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NOT NULL DEFAULT 0  ,
  LastUpdated DATETIME NULL
  
) ;


--- }}}

--- {{{ GroupMembers 

CREATE TABLE GroupMembers (
  id INTEGER PRIMARY KEY  ,
  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
  
) ;

--- }}}

--- {{{ CachedGroupMembers

create table CachedGroupMembers (
        id integer primary key ,
        GroupId int, 
        MemberId int, 
        Via int, 
        ImmediateParentId int,
        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

        
) ;

--- }}}

--- {{{ Users

CREATE TABLE Users (
  id INTEGER ,
  Name varchar(200) NOT NULL  ,
  Password varchar(256) NULL  ,
  AuthToken varchar(16) NULL  ,
  Comments blob NULL  ,
  Signature blob NULL  ,
  EmailAddress varchar(120) NULL  ,
  FreeformContactInfo blob NULL  ,
  Organization varchar(200) 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  ,
  Timezone char(50) NULL  ,
  PGPKey text NULL,

  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  
  
) ;


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);


--- }}}

--- {{{ Tickets

CREATE TABLE Tickets (
  id INTEGER PRIMARY KEY  ,
  EffectiveId integer NULL DEFAULT 0 ,
  Queue integer NULL DEFAULT 0 ,
  Type varchar(16) 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 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  ,
  Due DATETIME NULL  ,
  Resolved DATETIME NULL  ,


  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  Disabled int2 NOT NULL DEFAULT 0
  
) ;

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) ;

--- }}}

--- {{{ ScripActions

CREATE TABLE ScripActions (
  id INTEGER PRIMARY KEY  ,
  Name varchar(200) NULL  ,
  Description varchar(255) NULL  ,
  ExecModule varchar(60) NULL  ,
  Argument varchar(255) NULL  ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  LastUpdated DATETIME NULL  
  
) ;

--- }}}

--- {{{ Templates

CREATE TABLE Templates (
  id INTEGER PRIMARY KEY  ,
  Queue integer NOT NULL DEFAULT 0 ,
  Name varchar(200) NOT NULL  ,
  Description varchar(255) NULL  ,
  Type varchar(16) NULL  ,
  Language varchar(16) NULL  ,
  TranslationOf integer NULL DEFAULT 0 ,
  Content blob NULL  ,
  LastUpdated DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME 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 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)
) ;

CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content); 
CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId); 


CREATE TABLE CustomFields (
  id INTEGER NOT NULL  ,
  Name varchar(200) 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 NOT NULL DEFAULT 0  ,
  LookupType varchar(255) 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)
) ;


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)
) ;


CREATE TABLE CustomFieldValues (
  id INTEGER NOT NULL  ,
  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  ,
  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 ,
  Creator integer NULL DEFAULT 0 ,
  Created DATETIME NULL  ,
  LastUpdatedBy integer NULL DEFAULT 0 ,
  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 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
);
Commit	Line	Data
84fb5b46 MKG	1	--- {{{ Attachments
	2
	3	CREATE TABLE Attachments (
	4	id INTEGER PRIMARY KEY ,
	5	TransactionId INTEGER ,
dab09ea8	6	Parent integer NULL DEFAULT 0 ,
84fb5b46 MKG	7	MessageId varchar(160) NULL ,
	8	Subject varchar(255) NULL ,
	9	Filename varchar(255) NULL ,
	10	ContentType varchar(80) NULL ,
	11	ContentEncoding varchar(80) NULL ,
	12	Content LONGTEXT NULL ,
	13	Headers LONGTEXT NULL ,
dab09ea8	14	Creator integer NULL DEFAULT 0 ,
84fb5b46 MKG	15	Created DATETIME NULL
	16
	17	) ;
	18
	19	CREATE INDEX Attachments1 ON Attachments (Parent) ;
	20	CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
	21	CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
	22	--- }}}
	23
	24	--- {{{ Queues
	25	CREATE TABLE Queues (
	26	id INTEGER PRIMARY KEY ,
	27	Name varchar(200) NOT NULL ,
	28	Description varchar(255) NULL ,
	29	CorrespondAddress varchar(120) NULL ,
	30	CommentAddress varchar(120) NULL ,
	31	Lifecycle varchar(32) NULL ,
	32	SubjectTag varchar(120) NULL ,
dab09ea8 MKG	33	InitialPriority integer NULL DEFAULT 0 ,
	34	FinalPriority integer NULL DEFAULT 0 ,
	35	DefaultDueIn integer NULL DEFAULT 0 ,
	36	Creator integer NULL DEFAULT 0 ,
84fb5b46	37	Created DATETIME NULL ,
dab09ea8	38	LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46 MKG	39	LastUpdated DATETIME NULL ,
	40	Disabled int2 NOT NULL DEFAULT 0
	41
	42	) ;
	43	CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
	44
	45	--- }}}
	46
	47	--- {{{ Links
	48
	49	CREATE TABLE Links (
	50	id INTEGER PRIMARY KEY ,
	51	Base varchar(240) NULL ,
	52	Target varchar(240) NULL ,
	53	Type varchar(20) NOT NULL ,
dab09ea8 MKG	54	LocalTarget integer NULL DEFAULT 0 ,
	55	LocalBase integer NULL DEFAULT 0 ,
	56	LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46	57	LastUpdated DATETIME NULL ,
dab09ea8	58	Creator integer NULL DEFAULT 0 ,
84fb5b46 MKG	59	Created DATETIME NULL
	60
	61	) ;
	62	CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
	63	CREATE INDEX Links4 ON Links(Type,LocalBase);
	64
	65	--- }}}
	66
	67	--- {{{ Principals
	68
	69	CREATE TABLE Principals (
	70	id INTEGER PRIMARY KEY,
	71	PrincipalType VARCHAR(16) not null,
	72	ObjectId integer,
	73	Disabled int2 NOT NULL DEFAULT 0
	74
	75	) ;
	76
	77	--- }}}
	78
	79	--- {{{ Groups
	80
	81	CREATE TABLE Groups (
	82	id INTEGER ,
	83	Name varchar(200) NULL ,
	84	Description varchar(255) NULL ,
	85	Domain varchar(64),
	86	Type varchar(64),
	87	Instance integer,
	88	Creator integer NOT NULL DEFAULT 0 ,
	89	Created DATETIME NULL ,
	90	LastUpdatedBy integer NOT NULL DEFAULT 0 ,
	91	LastUpdated DATETIME NULL
	92
	93	) ;
	94
	95	CREATE UNIQUE INDEX Groups1 ON Groups (Name,Domain,Type,Instance) ;
	96
	97	--- }}}
	98
	99	--- {{{ ScripConditions
	100
	101	CREATE TABLE ScripConditions (
	102	id INTEGER PRIMARY KEY ,
	103	Name varchar(200) NULL ,
	104	Description varchar(255) NULL ,
	105	ExecModule varchar(60) NULL ,
	106	Argument varchar(255) NULL ,
	107	ApplicableTransTypes varchar(60) NULL ,
	108
dab09ea8	109	Creator integer NULL DEFAULT 0 ,
84fb5b46	110	Created DATETIME NULL ,
dab09ea8	111	LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46 MKG	112	LastUpdated DATETIME NULL
	113
	114	) ;
	115
	116	--- }}}
	117
	118	--- {{{ Transactions
	119	CREATE TABLE Transactions (
	120	id INTEGER PRIMARY KEY ,
	121	ObjectType varchar(255) NULL ,
dab09ea8 MKG	122	ObjectId integer NULL DEFAULT 0 ,
dab09ea8 MKG	123	TimeTaken integer NULL DEFAULT 0 ,
84fb5b46 MKG	124	Type varchar(20) NULL ,
	125	Field varchar(40) NULL ,
	126	OldValue varchar(255) NULL ,
	127	NewValue varchar(255) NULL ,
	128	ReferenceType varchar(255) NULL ,
	129	OldReference integer NULL ,
	130	NewReference integer NULL ,
	131	Data varchar(255) NULL ,
	132
dab09ea8	133	Creator integer NULL DEFAULT 0 ,
84fb5b46 MKG	134	Created DATETIME NULL
	135
	136	) ;
	137	CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
	138
	139	--- }}}
	140
	141	--- {{{ Scrips
	142
	143	CREATE TABLE Scrips (
	144	id INTEGER PRIMARY KEY ,
	145	Description varchar(255),
dab09ea8 MKG	146	ScripCondition integer NULL DEFAULT 0 ,
dab09ea8 MKG	147	ScripAction integer NULL DEFAULT 0 ,
84fb5b46 MKG	148	ConditionRules text NULL ,
	149	ActionRules text NULL ,
	150	CustomIsApplicableCode text NULL ,
	151	CustomPrepareCode text NULL ,
	152	CustomCommitCode text NULL ,
	153	Stage varchar(32) NULL ,
dab09ea8 MKG	154	Queue integer NULL DEFAULT 0 ,
	155	Template integer NULL DEFAULT 0 ,
	156	Creator integer NULL DEFAULT 0 ,
84fb5b46	157	Created DATETIME NULL ,
dab09ea8	158	LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46 MKG	159	LastUpdated DATETIME NULL
	160
	161	) ;
	162
	163	--- }}}
	164
	165	--- {{{ ACL
	166	CREATE TABLE ACL (
	167	id INTEGER PRIMARY KEY ,
	168	PrincipalType varchar(25) NOT NULL,
	169
dab09ea8	170	PrincipalId INTEGER DEFAULT 0,
84fb5b46 MKG	171	RightName varchar(25) NOT NULL ,
	172	ObjectType varchar(25) NOT NULL ,
	173	ObjectId INTEGER default 0,
	174	Creator integer NOT NULL DEFAULT 0 ,
	175	Created DATETIME NULL ,
	176	LastUpdatedBy integer NOT NULL DEFAULT 0 ,
	177	LastUpdated DATETIME NULL
	178
	179	) ;
	180
	181
	182	--- }}}
	183
	184	--- {{{ GroupMembers
	185
	186	CREATE TABLE GroupMembers (
	187	id INTEGER PRIMARY KEY ,
dab09ea8 MKG	188	GroupId integer NULL DEFAULT 0,
dab09ea8 MKG	189	MemberId integer NULL DEFAULT 0,
84fb5b46 MKG	190	Creator integer NOT NULL DEFAULT 0 ,
	191	Created DATETIME NULL ,
	192	LastUpdatedBy integer NOT NULL DEFAULT 0 ,
	193	LastUpdated DATETIME NULL
	194
	195	) ;
	196
	197	--- }}}
	198
	199	--- {{{ CachedGroupMembers
	200
	201	create table CachedGroupMembers (
	202	id integer primary key ,
	203	GroupId int,
	204	MemberId int,
	205	Via int,
	206	ImmediateParentId int,
	207	Disabled int2 NOT NULL DEFAULT 0 # if this cached group member is a member of this group by way of a disabled
	208	# group or this group is disabled, this will be set to 1
	209	# this allows us to not find members of disabled subgroups when listing off
	210	# group members recursively.
	211	# Also, this allows us to have the ACL system elide members of disabled groups
	212
	213
	214	) ;
	215
	216	--- }}}
	217
	218	--- {{{ Users
	219
	220	CREATE TABLE Users (
	221	id INTEGER ,
	222	Name varchar(200) NOT NULL ,
	223	Password varchar(256) NULL ,
	224	AuthToken varchar(16) NULL ,
	225	Comments blob NULL ,
	226	Signature blob NULL ,
	227	EmailAddress varchar(120) NULL ,
	228	FreeformContactInfo blob NULL ,
	229	Organization varchar(200) NULL ,
	230	RealName varchar(120) NULL ,
	231	NickName varchar(16) NULL ,
	232	Lang varchar(16) NULL ,
	233	EmailEncoding varchar(16) NULL ,
	234	WebEncoding varchar(16) NULL ,
	235	ExternalContactInfoId varchar(100) NULL ,
	236	ContactInfoSystem varchar(30) NULL ,
	237	ExternalAuthId varchar(100) NULL ,
	238	AuthSystem varchar(30) NULL ,
	239	Gecos varchar(16) NULL ,
	240	HomePhone varchar(30) NULL ,
	241	WorkPhone varchar(30) NULL ,
	242	MobilePhone varchar(30) NULL ,
	243	PagerPhone varchar(30) NULL ,
	244	Address1 varchar(200) NULL ,
	245	Address2 varchar(200) NULL ,
	246	City varchar(100) NULL ,
	247	State varchar(100) NULL ,
	248	Zip varchar(16) NULL ,
	249	Country varchar(50) NULL ,
	250	Timezone char(50) NULL ,
	251	PGPKey text NULL,
	252
dab09ea8	253	Creator integer NULL DEFAULT 0 ,
84fb5b46	254	Created DATETIME NULL ,
dab09ea8	255	LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46 MKG	256	LastUpdated DATETIME NULL
	257
	258	) ;
	259
	260
	261	CREATE UNIQUE INDEX Users1 ON Users (Name) ;
	262	CREATE INDEX Users2 ON Users (Name);
	263	CREATE INDEX Users3 ON Users (id, EmailAddress);
	264	CREATE INDEX Users4 ON Users (EmailAddress);
	265
	266
	267	--- }}}
	268
	269	--- {{{ Tickets
	270
	271	CREATE TABLE Tickets (
	272	id INTEGER PRIMARY KEY ,
dab09ea8 MKG	273	EffectiveId integer NULL DEFAULT 0 ,
dab09ea8 MKG	274	Queue integer NULL DEFAULT 0 ,
84fb5b46	275	Type varchar(16) NULL ,
dab09ea8 MKG	276	IssueStatement integer NULL DEFAULT 0 ,
	277	Resolution integer NULL DEFAULT 0 ,
	278	Owner integer NULL DEFAULT 0 ,
84fb5b46	279	Subject varchar(200) NULL DEFAULT '[no subject]' ,
dab09ea8 MKG	280	InitialPriority integer NULL DEFAULT 0 ,
	281	FinalPriority integer NULL DEFAULt 0 ,
	282	Priority integer NULL DEFAULT 0 ,
	283	TimeEstimated integer NULL DEFAULT 0 ,
	284	TimeWorked integer NULL DEFAULT 0 ,
84fb5b46	285	Status varchar(64) NULL ,
dab09ea8	286	TimeLeft integer NULL DEFAULT 0 ,
84fb5b46 MKG	287	Told DATETIME NULL ,
	288	Starts DATETIME NULL ,
	289	Started DATETIME NULL ,
	290	Due DATETIME NULL ,
	291	Resolved DATETIME NULL ,
	292
	293
dab09ea8	294	LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46	295	LastUpdated DATETIME NULL ,
dab09ea8	296	Creator integer NULL DEFAULT 0 ,
84fb5b46 MKG	297	Created DATETIME NULL ,
	298	Disabled int2 NOT NULL DEFAULT 0
	299
	300	) ;
	301
	302	CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
	303	CREATE INDEX Tickets2 ON Tickets (Owner) ;
	304	CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
	305	CREATE INDEX Tickets4 ON Tickets (id, Status) ;
	306	CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;
	307
	308	--- }}}
	309
	310	--- {{{ ScripActions
	311
	312	CREATE TABLE ScripActions (
	313	id INTEGER PRIMARY KEY ,
	314	Name varchar(200) NULL ,
	315	Description varchar(255) NULL ,
	316	ExecModule varchar(60) NULL ,
	317	Argument varchar(255) NULL ,
dab09ea8	318	Creator integer NULL DEFAULT 0 ,
84fb5b46	319	Created DATETIME NULL ,
dab09ea8	320	LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46 MKG	321	LastUpdated DATETIME NULL
	322
	323	) ;
	324
	325	--- }}}
	326
	327	--- {{{ Templates
	328
	329	CREATE TABLE Templates (
	330	id INTEGER PRIMARY KEY ,
	331	Queue integer NOT NULL DEFAULT 0 ,
	332	Name varchar(200) NOT NULL ,
	333	Description varchar(255) NULL ,
	334	Type varchar(16) NULL ,
	335	Language varchar(16) NULL ,
dab09ea8	336	TranslationOf integer NULL DEFAULT 0 ,
84fb5b46 MKG	337	Content blob NULL ,
84fb5b46 MKG	338	LastUpdated DATETIME NULL ,
dab09ea8 MKG	339	LastUpdatedBy integer NULL DEFAULT 0 ,
dab09ea8 MKG	340	Creator integer NULL DEFAULT 0 ,
84fb5b46 MKG	341	Created DATETIME NULL
	342
	343	) ;
	344
	345	--- }}}
	346
	347
	348
	349
	350	CREATE TABLE ObjectCustomFieldValues (
	351	id INTEGER NOT NULL ,
	352	CustomField int NOT NULL ,
	353	ObjectType varchar(255) NOT NULL, # Final target of the Object
	354	ObjectId int NOT NULL , # New -- Replaces Ticket
	355	SortOrder integer NOT NULL DEFAULT 0 ,
	356
	357	Content varchar(255) NULL ,
	358	LargeContent LONGTEXT NULL, # New -- to hold 255+ strings
	359	ContentType varchar(80) NULL, # New -- only text/* gets searched
	360	ContentEncoding varchar(80) NULL , # New -- for binary Content
	361
	362	Creator integer NOT NULL DEFAULT 0 ,
	363	Created DATETIME NULL ,
	364	LastUpdatedBy integer NOT NULL DEFAULT 0 ,
	365	LastUpdated DATETIME NULL ,
	366	Disabled int2 NOT NULL DEFAULT 0 ,
	367	PRIMARY KEY (id)
	368	) ;
	369
	370	CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
	371	CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
	372
	373
	374
	375	CREATE TABLE CustomFields (
	376	id INTEGER NOT NULL ,
	377	Name varchar(200) NULL ,
	378	Type varchar(200) NULL , # Changed -- 'Single' and 'Multiple' is moved out
	379	RenderType varchar(64) NULL ,
	380	MaxValues integer, # New -- was 'Single'(1) and 'Multiple'(0)
	381	Pattern varchar(65536) NULL , # New -- Must validate against this
	382	Repeated int2 NOT NULL DEFAULT 0 , # New -- repeated table entry
	383	BasedOn INTEGER NULL,
	384	ValuesClass varchar(64) NULL ,
	385	Description varchar(255) NULL ,
	386	SortOrder integer NOT NULL DEFAULT 0 ,
	387	LookupType varchar(255) NOT NULL,
	388
	389	Creator integer NOT NULL DEFAULT 0 ,
	390	Created DATETIME NULL ,
	391	LastUpdatedBy integer NOT NULL DEFAULT 0 ,
	392	LastUpdated DATETIME NULL ,
	393	Disabled int2 NOT NULL DEFAULT 0 ,
	394	PRIMARY KEY (id)
	395	) ;
	396
	397
	398
	399	CREATE TABLE ObjectCustomFields (
	400	id INTEGER NOT NULL ,
	401	CustomField int NOT NULL ,
	402	ObjectId integer NOT NULL,
	403	SortOrder integer NOT NULL DEFAULT 0 ,
	404
405	Creator integer NOT NULL DEFAULT 0 ,
406	Created DATETIME NULL ,
407	LastUpdatedBy integer NOT NULL DEFAULT 0 ,
408	LastUpdated DATETIME NULL ,
409	PRIMARY KEY (id)
410	) ;
411
412
413
414	CREATE TABLE CustomFieldValues (
415	id INTEGER NOT NULL ,
416	CustomField int NOT NULL ,
417	Name varchar(200) NULL ,
418	Description varchar(255) NULL ,
419	SortOrder integer NOT NULL DEFAULT 0 ,
420	Category varchar(255) NULL ,
421
422	Creator integer NOT NULL DEFAULT 0 ,
423	Created DATETIME NULL ,
424	LastUpdatedBy integer NOT NULL DEFAULT 0 ,
425	LastUpdated DATETIME NULL ,
426	PRIMARY KEY (id)
427	) ;
428
429	CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
430
431
432	--- {{{ Attributes
433	CREATE TABLE Attributes (
434	id INTEGER PRIMARY KEY ,
435	Name varchar(255) NOT NULL ,
436	Description varchar(255) NULL ,
437	Content LONGTEXT NULL ,
438	ContentType varchar(16),
439	ObjectType varchar(25) NOT NULL ,
dab09ea8 MKG	440	ObjectId INTEGER ,
dab09ea8 MKG	441	Creator integer NULL DEFAULT 0 ,
84fb5b46	442	Created DATETIME NULL ,
dab09ea8	443	LastUpdatedBy integer NULL DEFAULT 0 ,
84fb5b46 MKG	444	LastUpdated DATETIME NULL
	445
	446	) ;
	447	CREATE INDEX Attributes1 on Attributes(Name);
	448	CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
	449
	450	--- }}}
	451
	452	CREATE TABLE Classes (
	453	id INTEGER PRIMARY KEY,
	454	Name varchar(255) NOT NULL DEFAULT '',
	455	Description varchar(255) NOT NULL DEFAULT '',
	456	SortOrder integer NOT NULL DEFAULT 0,
	457	Disabled smallint NOT NULL DEFAULT 0,
	458	Creator integer NOT NULL DEFAULT 0,
	459	Created TIMESTAMP NULL,
	460	LastUpdatedBy integer NOT NULL DEFAULT 0,
	461	LastUpdated TIMESTAMP NULL,
	462	HotList smallint NOT NULL DEFAULT 0
	463	);
	464
	465	CREATE TABLE Articles (
	466	id INTEGER PRIMARY KEY,
	467	Name varchar(255) NOT NULL DEFAULT '',
	468	Summary varchar(255) NOT NULL DEFAULT '',
	469	SortOrder integer NOT NULL DEFAULT 0,
	470	Class integer NOT NULL DEFAULT 0,
	471	Parent integer NOT NULL DEFAULT 0,
	472	URI varchar(255),
	473	Creator integer NOT NULL DEFAULT 0,
	474	Created TIMESTAMP NULL,
	475	LastUpdatedBy integer NOT NULL DEFAULT 0,
	476	LastUpdated TIMESTAMP NULL
	477	);
	478
	479
	480	CREATE TABLE Topics (
	481	id INTEGER PRIMARY KEY,
	482	Parent integer NOT NULL DEFAULT 0,
	483	Name varchar(255) NOT NULL DEFAULT '',
	484	Description varchar(255) NOT NULL DEFAULT '',
	485	ObjectType varchar(64) NOT NULL DEFAULT '',
dab09ea8	486	ObjectId integer NOT NULL DEFAULT 0
84fb5b46 MKG	487	);
	488
	489
	490	CREATE TABLE ObjectTopics (
	491	id INTEGER PRIMARY KEY,
dab09ea8	492	Topic integer NOT NULL DEFAULT 0,
84fb5b46	493	ObjectType varchar(64) NOT NULL DEFAULT '',
dab09ea8	494	ObjectId integer NOT NULL DEFAULT 0
84fb5b46 MKG	495	);
	496
	497	CREATE TABLE ObjectClasses (
	498	id INTEGER PRIMARY KEY,
dab09ea8	499	Class integer NOT NULL DEFAULT 0,
84fb5b46	500	ObjectType varchar(64) NOT NULL DEFAULT '',
dab09ea8	501	ObjectId integer NOT NULL DEFAULT 0,
84fb5b46 MKG	502	Creator integer NOT NULL DEFAULT 0,
	503	Created TIMESTAMP NULL,
	504	LastUpdatedBy integer NOT NULL DEFAULT 0,
	505	LastUpdated TIMESTAMP NULL
	506	);