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?

Activity

Show:
Greg Logan
September 30, 2013, 8:36 PM

We generally work from the Java to the DDL, so I went ahead and checked against the auto-generated tables. Your question about mh_organization_property is quite correct, I'm going to add that to the DDL, which means it also needs to be ported to both the upgrade scripts. The question about mh_job is an interesting one. The autogenerated tables use the text data type in MySQL, wheras the DDLs create a mediumtext field. This means that changing to that VARCHAR means also changing the codebase, something I'm leery of doing unless we have a driving need to at this point. Maybe file that as a bug against 1.5 and we'll do it then since I'm sure we'll have lots of other things moving about at the same time. Into trunk with rev 15835, merged to 1.4.x with rev 15836.

If you can get the mh_organization_property table added to the postgres upgrade script I think we can tie this issue off for 1.4.1.

Alexander Bias
October 1, 2013, 6:26 AM

Greg,

  • as you added NOT NULL to mh_organization_property.name in the MySQL DDL and the MySQL Upgrade script, there's nothing to do for the Postgres side as there is already NOT NULL defined in the DDL.

So, from my point of view, this is done.

Greg Logan
October 1, 2013, 4:21 PM

Awesome, thanks Alexander. Resolving it then.

Alexander Bias
October 1, 2013, 5:48 PM

Greg,

you're welcome.

One last comment: As I said, I couldn't check the scripts on Postgres 8.x as I don't have a server with this version here anymore. I tested it on Postgres 9.1 Perhaps it would make more sense to deliver the DDL and the Upgrade script with a filename "postgres91.sql" instead of "postgres84.sql".

Greg Logan
October 10, 2013, 6:23 PM

Good idea. Done in trunk with rev 15913, merged to 1.4.x with rev 15913.

Assignee

Greg Logan

Reporter

Alexander Bias

Severity

Non Functioning

Tags (folksonomy)

None

Components

Fix versions

Affects versions

Priority

Blocker
Configure