Upgrade 4.0.17 clean.
[usit-rt.git] / etc / upgrade / 3.3.0 / schema.Pg
CommitLineData
01e3b242
MKG
1alter Table Transactions ADD Column ObjectType varchar(64);
2update Transactions set ObjectType = 'RT::Ticket';
3ALTER TABLE Transactions ALTER COLUMN ObjectType SET NOT NULL;
4alter table Transactions drop column EffectiveTicket;
5alter table Transactions add column ReferenceType varchar(255) NULL;
6alter table Transactions add column OldReference integer NULL;
7alter table Transactions add column NewReference integer NULL;
8drop index transactions1;
9alter table Transactions rename column Ticket to ObjectId;
10
11
12CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
13
14alter table TicketCustomFieldValues rename to ObjectCustomFieldValues;
15
16alter table ObjectCustomFieldValues rename column Ticket to ObjectId;
17
18alter table objectcustomfieldvalues add column ObjectType varchar(255);
19
20update objectcustomfieldvalues set ObjectType = 'RT::Ticket';
21
22ALTER TABLE objectcustomfieldvalues ALTER COLUMN ObjectType SET NOT NULL;
23
24alter table objectcustomfieldvalues add column Current int;
25
26alter table objectcustomfieldvalues alter column Current SET default 1;
27
28UPDATE objectcustomfieldvalues SET Current = 1;
29
30alter table objectcustomfieldvalues add column LargeContent TEXT NULL;
31
32alter table objectcustomfieldvalues add column ContentType varchar(80) NULL;
33
34alter table objectcustomfieldvalues add column ContentEncoding varchar(80) NULL;
35
36create index ObjectCustomFieldValues1 on objectcustomfieldvalues (CustomField,ObjectType,ObjectId,Content);
37
38create index ObjectCustomFieldValues2 on objectcustomfieldvalues (CustomField,ObjectType,ObjectId);
39
40
41CREATE SEQUENCE objectcustomfields_id_s;
42
43CREATE TABLE ObjectCustomFields (
44 id INTEGER DEFAULT nextval('objectcustomfields_id_s'),
45 CustomField integer NOT NULL,
46 ObjectId integer NOT NULL,
47 SortOrder integer NOT NULL DEFAULT 0 ,
48
49 Creator integer NOT NULL DEFAULT 0 ,
50 Created TIMESTAMP NULL ,
51 LastUpdatedBy integer NOT NULL DEFAULT 0 ,
52 LastUpdated TIMESTAMP NULL ,
53 PRIMARY KEY (id)
54
55);
56
57
58INSERT into ObjectCustomFields (CustomField, ObjectId, SortOrder, Creator, LastUpdatedBy) SELECT id, Queue, SortOrder, Creator, LastUpdatedBy from CustomFields;
59
60alter table CustomFields add column LookupType varchar(255);
61alter table CustomFields add column Repeated int2;
62alter table CustomFields add column Pattern varchar(255) NULL;
63alter table CustomFields add column MaxValues integer;
64
65UPDATE CustomFields SET MaxValues = 0 WHERE Type LIKE '%Multiple';
66UPDATE CustomFields SET MaxValues = 1 WHERE Type LIKE '%Single';
67UPDATE CustomFields SET Type = 'Select' WHERE Type LIKE 'Select%';
68UPDATE CustomFields SET Type = 'Freeform' WHERE Type LIKE 'Freeform%';
69UPDATE CustomFields Set LookupType = 'RT::Queue-RT::Ticket';
70ALTER TABLE CustomFields ALTER COLUMN LookupType SET NOT NULL;
71UPDATE CustomFields Set Repeated = 0;
72ALTER TABLE CustomFields ALTER COLUMN Repeated SET DEFAULT 0;
73ALTER TABLE CustomFields ALTER COLUMN Repeated SET NOT NULL;
74alter table CustomFields drop column Queue;