3 INSERT INTO config.upgrade_log (version) VALUES ('0264'); -- Scott McKellar
5 -- Add a message_type column
7 -- WARNING: because the new column is NOT NULL, this upgrade script must
8 -- initialize it with something if the table is not empty. The initial
9 -- value, 'ORDERS', may not always be appropriate. Massage as needed.
11 -- For example, if you have already processed responses, this fixes them:
12 -- update acq.edi_message set message_type='ORDRSP' where edi LIKE '%ORDRSP%';
14 ALTER TABLE acq.edi_message
15 ADD COLUMN message_type TEXT;
17 UPDATE acq.edi_message
18 SET message_type = 'ORDERS';
20 ALTER TABLE acq.edi_message
21 ALTER COLUMN message_type SET NOT NULL;
23 ALTER TABLE acq.edi_message
24 ADD CONSTRAINT valid_message_type CHECK
33 -- Add a new valid value for status: 'retry'
35 ALTER TABLE acq.edi_message
36 DROP CONSTRAINT status_value;
38 ALTER TABLE acq.edi_message
39 ADD CONSTRAINT status_value CHECK
41 'new', -- needs to be translated
42 'translated', -- needs to be processed
43 'trans_error', -- error in translation step
44 'processed', -- needs to have remote_file deleted
45 'proc_error', -- error in processing step
46 'delete_error', -- error in deletion
47 'retry', -- need to retry