Postgres Upgrade Script not working / Improve Postgres DDL Script

Steps to reproduce

Hi Greg,

the up-to-now disfunctional 1.4.0 -> 1.4.1 Postgres upgrade script should look like this:

DROP INDEX "IX_mh_annotation_user";
DROP INDEX "IX_mh_user_action_user";

ALTER TABLE "mh_annotation"
RENAME COLUMN "user" TO "user_id";
ALTER TABLE "mh_user_action"
RENAME COLUMN "user" TO "user_id";

CREATE INDEX "IX_mh_annotation_user" ON "mh_annotation" ("user_id");
CREATE INDEX "IX_mh_user_action_user" ON "mh_user_action" ("user_id");

ALTER TABLE "mh_job_argument"
DROP CONSTRAINT IF EXISTS "UNQ_mh_job_argument_0";

This has been tested on a PostgreSQL 9.1.1 server with a virgin MH 1.4.0 database.

===========================

Futhermore, I compared https://opencast.jira.com/svn/MH/branches/1.4.x/docs/scripts/ddl/mysql5.sql to https://opencast.jira.com/svn/MH/branches/1.4.x/docs/scripts/ddl/postgres84.sql (Revision 15831) to see if there are differences between both files.

Assuming that the MySQL DDL Script is the master version, I propose these changes to the Postgres DDL Script:

Change

CREATE TABLE "mh_annotation" (
"id" bigint NOT NULL,
"inpoint" bigint,
"outpoint" bigint,
"mediapackage" character varying(128),
"session" character varying(128),
"created" timestamp,
"user_id" character varying(255),
"length" bigint,
"type" character varying(128),
"value" text,
"private" boolean,
PRIMARY KEY ("id")
);

to

CREATE TABLE "mh_annotation" (
"id" bigint NOT NULL,
"inpoint" integer,
"outpoint" integer,
"mediapackage" character varying(128),
"session" character varying(128),
"created" timestamp,
"user_id" character varying(255),
"length" integer,
"type" character varying(128),
"value" text,
"private" boolean,
PRIMARY KEY ("id")
);

----------------------

Change

CREATE TABLE "mh_host_registration" (
"id" bigint NOT NULL,
"host" character varying(255) NOT NULL,
"maintenance" boolean NOT NULL,
"online" boolean NOT NULL DEFAULT TRUE,
"active" boolean NOT NULL DEFAULT TRUE,
"max_jobs" bigint NOT NULL,
PRIMARY KEY ("id"),
CONSTRAINT "UNQ_mh_host_registration_0" UNIQUE ("host")
);

to

CREATE TABLE "mh_host_registration" (
"id" bigint NOT NULL,
"host" character varying(255) NOT NULL,
"maintenance" boolean NOT NULL DEFAULT FALSE,
"online" boolean NOT NULL DEFAULT TRUE,
"active" boolean NOT NULL DEFAULT TRUE,
"max_jobs" integer NOT NULL,
PRIMARY KEY ("id"),
CONSTRAINT "UNQ_mh_host_registration_0" UNIQUE ("host")
);

-------------------

Change

CREATE TABLE "mh_service_registration" (
"id" bigint NOT NULL,
"path" character varying(255) NOT NULL,
"job_producer" boolean NOT NULL,
"service_type" character varying(255) NOT NULL,
"online" boolean NOT NULL DEFAULT TRUE,
"active" boolean NOT NULL DEFAULT TRUE,
"online_from" timestamp,
"service_state" integer NOT NULL,
"state_changed" timestamp,
"warning_state_trigger" bigint,
"error_state_trigger" bigint,
"host_registration" bigint NOT NULL,
PRIMARY KEY ("id"),
CONSTRAINT "UNQ_mh_service_registration_0" UNIQUE ("host_registration", "service_type"),
CONSTRAINT "FK_service_registration_host_registration" FOREIGN KEY ("host_registration") REFERENCES "mh_host_registration" ("id") ON DELETE CASCADE
);

to

CREATE TABLE "mh_service_registration" (
"id" bigint NOT NULL,
"path" character varying(255) NOT NULL,
"job_producer" boolean NOT NULL DEFAULT FALSE,
"service_type" character varying(255) NOT NULL,
"online" boolean NOT NULL DEFAULT TRUE,
"active" boolean NOT NULL DEFAULT TRUE,
"online_from" timestamp,
"service_state" integer NOT NULL,
"state_changed" timestamp,
"warning_state_trigger" bigint,
"error_state_trigger" bigint,
"host_registration" bigint,
PRIMARY KEY ("id"),
CONSTRAINT "UNQ_mh_service_registration_0" UNIQUE ("host_registration", "service_type"),
CONSTRAINT "FK_service_registration_host_registration" FOREIGN KEY ("host_registration") REFERENCES "mh_host_registration" ("id") ON DELETE CASCADE
);

------------------

Change

CREATE TABLE "mh_job_argument" (
"id" bigint NOT NULL,
"argument" text,
"argument_index" bigint,
CONSTRAINT "FK_mh_job_argument_id" FOREIGN KEY ("id") REFERENCES "mh_job" ("id") ON DELETE CASCADE
);

to

CREATE TABLE "mh_job_argument" (
"id" bigint NOT NULL,
"argument" text,
"argument_index" integer,
CONSTRAINT "FK_mh_job_argument_id" FOREIGN KEY ("id") REFERENCES "mh_job" ("id") ON DELETE CASCADE
);

-----------------------

Change

CREATE TABLE "mh_user_action" (
"id" bigint NOT NULL,
"user_ip" text,
"inpoint" bigint,
"outpoint" bigint,
"mediapackage" character varying(128),
"session" character varying(128),
"created" timestamp,
"user_id" character varying(255),
"length" bigint,
"type" character varying(128),
"playing" boolean DEFAULT FALSE,
PRIMARY KEY ("id")
);

to

CREATE TABLE "mh_user_action" (
"id" bigint NOT NULL,
"user_ip" character varying(255),
"inpoint" integer,
"outpoint" integer,
"mediapackage" character varying(128),
"session" character varying(128),
"created" timestamp,
"user_id" character varying(255),
"length" integer,
"type" character varying(128),
"playing" boolean DEFAULT FALSE,
PRIMARY KEY ("id")
);

----------------------

Based on these DDL changes, I propose to add the following lines to the 1.4.0 -> 1.4.1 upgrade Postgres script:

ALTER TABLE "mh_annotation"
ALTER COLUMN "inpoint" TYPE integer,
ALTER COLUMN "outpoint" TYPE integer,
ALTER COLUMN "length" TYPE integer;

ALTER TABLE "mh_host_registration"
ALTER COLUMN "maintenance" SET DEFAULT FALSE,
ALTER COLUMN "max_jobs" TYPE integer;

ALTER TABLE "mh_service_registration"
ALTER COLUMN "job_producer" SET DEFAULT FALSE,
ALTER COLUMN "host_registration" DROP NOT NULL;

ALTER TABLE "mh_job_argument"
ALTER COLUMN "argument_index" TYPE integer;

ALTER TABLE "mh_user_action"
ALTER COLUMN "user_ip" TYPE character varying(255),
ALTER COLUMN "inpoint" TYPE integer,
ALTER COLUMN "outpoint" TYPE integer,
ALTER COLUMN "length" TYPE integer;

This has been tested on a PostgreSQL 9.1.1 server with a virgin MH 1.4.0 database after running the above mentioned 1.4.0 -> 1.4.1 script.

------------------------------

Summing up,
I have attached the whole DDL script and the whole upgrade script as files.

The only thing I don't know if it works on Postgres 8.x flawlessly as I don't have a 8.x server here anymore.

===========================

As a sidenode, I'd like to ask if in the MySQL DDL Script, should

CREATE TABLE mh_organization_property (
organization VARCHAR(128) NOT NULL,
name VARCHAR(255),
value VARCHAR(255),
PRIMARY KEY (organization, name),
CONSTRAINT FK_mh_organization_property_organization FOREIGN KEY (organization) REFERENCES mh_organization (id) ON DELETE CASCADE
) ENGINE=InnoDB;

be changed to

CREATE TABLE mh_organization_property (
organization VARCHAR(128) NOT NULL,
name VARCHAR(255) NOT NULL,
value VARCHAR(255),
PRIMARY KEY (organization, name),
CONSTRAINT FK_mh_organization_property_organization FOREIGN KEY (organization) REFERENCES mh_organization (id) ON DELETE CASCADE
) ENGINE=InnoDB;

as "name" is used in the table's primary key and in the Postgres DDL Script, there is already a "NOT NULL" added to the "name" column?

===========================

As a sidenode, I'd like to ask if in the MySQL DDL Script, should

CREATE TABLE mh_job (
id BIGINT NOT NULL,
status INTEGER,
payload TEXT(65535),
date_started DATETIME,
run_time BIGINT,
creator TEXT(65535) NOT NULL,
instance_version BIGINT,
date_completed DATETIME,
operation VARCHAR(128),
dispatchable TINYINT(1) DEFAULT 1,
organization VARCHAR(128) NOT NULL,
date_created DATETIME,
queue_time BIGINT,
creator_service BIGINT,
processor_service BIGINT,
parent BIGINT,
root BIGINT,
PRIMARY KEY (id),
CONSTRAINT FK_mh_job_creator_service FOREIGN KEY (creator_service) REFERENCES mh_service_registration (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_processor_service FOREIGN KEY (processor_service) REFERENCES mh_service_registration (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_parent FOREIGN KEY (parent) REFERENCES mh_job (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_root FOREIGN KEY (root) REFERENCES mh_job (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_organization FOREIGN KEY (organization) REFERENCES mh_organization (id) ON DELETE CASCADE
) ENGINE=InnoDB;

be changed to

CREATE TABLE mh_job (
id BIGINT NOT NULL,
status INTEGER,
payload TEXT(65535),
date_started DATETIME,
run_time BIGINT,
creator VARCHAR(128) NOT NULL,
instance_version BIGINT,
date_completed DATETIME,
operation VARCHAR(128),
dispatchable TINYINT(1) DEFAULT 1,
organization VARCHAR(128) NOT NULL,
date_created DATETIME,
queue_time BIGINT,
creator_service BIGINT,
processor_service BIGINT,
parent BIGINT,
root BIGINT,
PRIMARY KEY (id),
CONSTRAINT FK_mh_job_creator_service FOREIGN KEY (creator_service) REFERENCES mh_service_registration (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_processor_service FOREIGN KEY (processor_service) REFERENCES mh_service_registration (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_parent FOREIGN KEY (parent) REFERENCES mh_job (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_root FOREIGN KEY (root) REFERENCES mh_job (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_organization FOREIGN KEY (organization) REFERENCES mh_organization (id) ON DELETE CASCADE
) ENGINE=InnoDB;

as the "creator" column contains only a username (data type "text" is saved off the table IIRC, so this might be impractical here) and in the Postgres DDL Script, the "creator" column is already a character varying(128) type?

Status

Assignee

Greg Logan

Reporter

Alexander Bias

Severity

Non Functioning

Tags (folksonomy)

None

Components

Fix versions

Affects versions

Priority

Blocker
Configure