Describes the DB updates prior to v11.8.9

Introduction

This shows the DB updates we did in earlier releases as they were otherwise not documented in an open manner. This document is obsolete for all new deployments.

Note that now each release that has DB updates will include a folder/directory in the postgresql/ddl folder. When present, a 'pre' script should be run before 'rundbsetup' is run; a 'post' script should be run after 'rundbsetup' is run to do whatever updates it needs to do; while just an 'update' script can be run without any need for 'rundbsetup' updates.

Details

For 11.8.1

ALTER TABLE esf_package_version ADD COLUMN programming_blob_id UUID NULL;

For 1.6

ALTER TABLE esf_package_version_party_template ADD COLUMN allow_delete_tran_if_unsigned CHAR DEFAULT 'N';

For 1.5

ALTER TABLE esf_stats ADD COLUMN num_transactions INTEGER;
UPDATE esf_stats SET num_transactions = 0;
ALTER TABLE esf_stats ALTER COLUMN num_transactions SET NOT NULL;

ALTER TABLE esf_transaction_party_assignment ALTER COLUMN email_address TYPE VARCHAR(100) USING rtrim(email_address);

(dbsetup conversion)

For 1.4

(dbsetup conversion)

For 1.3

ALTER TABLE esf_field_template ADD COLUMN auto_complete CHAR DEFAULT 'Y';

For 1.2

Rename template library StandardPackage to StandardPackageDisclosures.
Change display name: Standard Package & Disclosures
Change description: Standard layout for the package+disclosures page

ALTER TABLE esf_package_version_party_template ADD COLUMN landing_page CHAR DEFAULT 'P';

For 0.9.8

CREATE TABLE esf_transaction_file
(
	id   	 					UUID NOT NULL, /* id of file */
 	transaction_id		 		UUID NOT NULL, /* transaction it's associated with */
	transaction_document_id	 	UUID NOT NULL, /* document in the transaction it was uploaded to */
	transaction_party_id	 	UUID NOT NULL, /* the id of the transaction party who uploaded it */
	upload_timestamp	 		TIMESTAMP WITH TIME ZONE NOT NULL, /* when it was uploaded */
	field_name	 				VARCHAR(50) NOT NULL, /* the field's esfname on the document that is associated with the file */
	file_name		 			VARCHAR(256) NOT NULL, /* file name as uploaded */
	file_mime_type	 			VARCHAR(100) NOT NULL, /* file's mime type */
	file_blob_id	 			UUID NOT NULL, /* contains the compressed, encrypted file */
	PRIMARY KEY (id) 
);
CREATE INDEX esfidx_transaction_file_tid ON esf_transaction_file (transaction_id);

For 0.9.5:

ALTER TABLE esf_field_template ALTER COLUMN ignore_width_on_output SET DEFAULT 'N';
ALTER TABLE esf_field_template ADD COLUMN tooltip VARCHAR(100) NULL;
ALTER TABLE esf_field_template ADD COLUMN input_format_spec VARCHAR(100) NULL;
ALTER TABLE esf_field_template ADD COLUMN output_format_spec VARCHAR(100) NULL;

ALTER TABLE esf_transaction_party_document ADD COLUMN esign_ip_host_addr VARCHAR(200) NULL;
ALTER TABLE esf_transaction_party_document ADD COLUMN esign_timestamp TIMESTAMP WITH TIME ZONE NULL;

For 0.9.2:

CREATE INDEX esfidx_transaction_party_assignment_todo ON esf_transaction_party_assignment (user_id,email_address);
CREATE INDEX esfidx_transaction_party_todo ON esf_transaction_party (transaction_status,status);

For 0.9.1:

ALTER TABLE esf_transaction add column status_text VARCHAR(50) NULL;

ALTER TABLE esf_transaction_party ADD COLUMN todo_group_id UUID NULL;

ALTER TABLE esf_party_template ADD COLUMN display_name VARCHAR(100) NULL;
UPDATE esf_party_template SET display_name = esfname;
ALTER TABLE esf_party_template ALTER COLUMN display_name SET NOT NULL;
ALTER TABLE esf_party_template ADD COLUMN todo_group_id UUID NULL;

ALTER TABLE esf_package_version_party_template ADD COLUMN display_name VARCHAR(100) NULL;
UPDATE esf_package_version_party_template SET display_name = esfname;
ALTER TABLE esf_package_version_party_template ALTER COLUMN display_name SET NOT NULL;

CREATE TABLE esf_transaction_party_renotify
(
 	notify_timestamp  TIMESTAMP WITH TIME ZONE NOT NULL,
 	transaction_party_id  UUID NOT NULL,
 	PRIMARY KEY (notify_timestamp,transaction_party_id)
);
CREATE INDEX esfidx_transaction_party_renotify_tpid ON esf_transaction_party_renotify(transaction_party_id);

ALTER TABLE esf_transaction_party ADD COLUMN transaction_status CHAR DEFAULT 'S';
UPDATE esf_transaction_party SET transaction_status = (SELECT esf_transaction.status FROM esf_transaction WHERE esf_transaction.id = esf_transaction_party.transaction_id);
ALTER TABLE esf_transaction_party ADD COLUMN library_email_template_name VARCHAR(50) NULL;