X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=rt%2Fetc%2Fschema.Oracle;h=569d80cc4be34f8af8d76bb4d7612a529333658f;hp=9b53cf7ac03bf693f8ee354382c64a48668a4c55;hb=8103c1fc1b2c27a6855feadf26f91b980a54bc52;hpb=d39d52aac8f38ea9115628039f0df5aa3ac826de

diff --git a/rt/etc/schema.Oracle b/rt/etc/schema.Oracle
index 9b53cf7ac..569d80cc4 100644
--- a/rt/etc/schema.Oracle
+++ b/rt/etc/schema.Oracle
@@ -1,3 +1,4 @@
+
 CREATE SEQUENCE ATTACHMENTS_seq;
 CREATE TABLE Attachments (
   	id 		NUMBER(11,0) 
@@ -35,7 +36,7 @@ CREATE TABLE Queues (
 	LastUpdated		DATE,
 	Disabled 		NUMBER(11,0) DEFAULT 0 NOT NULL
 );
- CREATE  INDEX Queues1 ON Queues (lower(Name));
+ CREATE  INDEX Queues1 ON Queues (LOWER('Name'));
 CREATE INDEX Queues2 ON Queues (Disabled);
 
 
@@ -81,8 +82,8 @@ CREATE TABLE Groups (
 	Instance	NUMBER(11,0) DEFAULT 0 -- NOT NULL
 --	Instance	VARCHAR2(64)
 );
-CREATE INDEX Groups1 ON Groups (lower( Domain), Instance, lower(Type), id);
-CREATE INDEX Groups2 ON Groups (lower(Type), Instance, lower(Domain));
+CREATE INDEX Groups1 ON Groups (LOWER('Domain'), Instance, LOWER('Type'), id);
+CREATE INDEX Groups2 ON Groups (LOWER('Type'), Instance, LOWER('Domain'));
 
 
 CREATE SEQUENCE SCRIPCONDITIONS_seq;
@@ -105,19 +106,21 @@ CREATE SEQUENCE TRANSACTIONS_seq;
 CREATE TABLE Transactions (
   	id 			NUMBER(11,0) 
 		CONSTRAINT Transactions_Key PRIMARY KEY,
-  	EffectiveTicket 	NUMBER(11,0) DEFAULT 0 NOT NULL,
-  	Ticket 			NUMBER(11,0) DEFAULT 0 NOT NULL,
+  	ObjectType 		VARCHAR2(255),
+  	ObjectId		NUMBER(11,0) DEFAULT 0 NOT NULL,
   	TimeTaken 		NUMBER(11,0) DEFAULT 0 NOT NULL,
   	Type 			VARCHAR2(20),
   	Field 			VARCHAR2(40),
   	OldValue 		VARCHAR2(255),
   	NewValue 		VARCHAR2(255),
+  	ReferenceType 		VARCHAR2(255),
+  	OldReference 		NUMBER(11,0),
+  	NewReference 		NUMBER(11,0),
   	Data 			VARCHAR2(255),
   	Creator 		NUMBER(11,0) DEFAULT 0 NOT NULL,
   	Created 		DATE
 );
-CREATE INDEX Transactions1 ON Transactions (Ticket);
-CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
+CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
 
 
 CREATE SEQUENCE SCRIPS_seq;
@@ -222,8 +225,8 @@ CREATE TABLE Users (
 );
 -- CREATE UNIQUE INDEX Users1 ON Users (Name);
 
-CREATE INDEX Users2 ON Users( LOWER(name));
-CREATE INDEX Users4 ON Users (lower(EmailAddress));
+CREATE INDEX Users2 ON Users( LOWER('Name'));
+CREATE INDEX Users4 ON Users (LOWER('EmailAddress'));
 
 
 CREATE SEQUENCE TICKETS_seq;
@@ -295,21 +298,41 @@ CREATE TABLE Templates (
 );
 
 
-CREATE SEQUENCE TICKETCUSTOMFIELDVALUES_seq;
-CREATE TABLE TicketCustomFieldValues (
+CREATE SEQUENCE OBJECTCUSTOMFIELDS_seq;
+CREATE TABLE ObjectCustomFields (
+	id		NUMBER(11,0)
+                 CONSTRAINT ObjectCustomFields_Key PRIMARY KEY,
+        CustomField       NUMBER(11,0)  NOT NULL,
+        ObjectId              NUMBER(11,0)  NOT NULL,
+	SortOrder	NUMBER(11,0) DEFAULT 0 NOT NULL,
+	Creator		NUMBER(11,0) DEFAULT 0 NOT NULL,
+	Created		DATE,
+	LastUpdatedBy	NUMBER(11,0) DEFAULT 0 NOT NULL,
+	LastUpdated	DATE
+);
+
+
+CREATE SEQUENCE OBJECTCUSTOMFIELDVALUES_seq;
+CREATE TABLE ObjectCustomFieldValues (
 	id		NUMBER(11,0) 
-		CONSTRAINT TicketCustomFieldValues_Key PRIMARY KEY,
-	Ticket		NUMBER(11,0),
+		CONSTRAINT ObjectCustomFieldValues_Key PRIMARY KEY,
 	CustomField	NUMBER(11,0) NOT NULL,
+	ObjectType	VARCHAR2(25) NOT NULL,
+	ObjectId	NUMBER(11,0) DEFAULT 0 NOT NULL,
+	SortOrder	NUMBER(11,0) DEFAULT 0 NOT NULL,
 	Content		VARCHAR2(255),
+  	LargeContent 	CLOB,
+  	ContentType 	VARCHAR2(80),
+  	ContentEncoding VARCHAR2(80),
 	Creator		NUMBER(11,0) DEFAULT 0 NOT NULL,
 	Created		DATE,
 	LastUpdatedBy	NUMBER(11,0) DEFAULT 0 NOT NULL,
-	LastUpdated	DATE
+	LastUpdated	DATE,
+	Disabled	NUMBER(11,0) DEFAULT 0 NOT NULL
 );
 
-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); 
 
 CREATE SEQUENCE CUSTOMFIELDS_seq;
 CREATE TABLE CustomFields (
@@ -317,16 +340,18 @@ CREATE TABLE CustomFields (
 		CONSTRAINT CustomFields_Key PRIMARY KEY,
 	Name		VARCHAR2(200),
 	Type		VARCHAR2(200),
-	Queue		NUMBER(11,0) DEFAULT 0 NOT NULL,
+	MaxValues	NUMBER(11,0) DEFAULT 0 NOT NULL,
+	Pattern		VARCHAR2(255),
+        Repeated        NUMBER(11,0) DEFAULT 0 NOT NULL,
 	Description	VARCHAR2(255),
 	SortOrder	NUMBER(11,0) DEFAULT 0 NOT NULL,
+	LookupType	VARCHAR2(255),
 	Creator		NUMBER(11,0) DEFAULT 0 NOT NULL,
 	Created		DATE,
 	LastUpdatedBy	NUMBER(11,0) DEFAULT 0 NOT NULL,
 	LastUpdated	DATE,
 	Disabled	NUMBER(11,0) DEFAULT 0 NOT NULL
 );
-CREATE INDEX CustomFields1 ON CustomFields (Disabled, Queue);
 
 
 CREATE SEQUENCE CUSTOMFIELDVALUES_seq;