Syntax error when executing postgresql schema

[Sathish Thangathurai]

Hi All,

I need to connect matterhorn with backend postgresql db. I just followed the below link to create new user and matterhorn as database from the below url.

https://opencast.jira.com/wiki/display/MHTRUNK/PostgreSQL+Database+Configuration

I just find the postgresql schema from the below url.

https://opencast.jira.com/wiki/display/MHTRUNK/Postgres+DDL

I didn't see any download link to download the schema as *.sql in the above url yet. So i just copy and paste the schema to my  newly created postgresql.sql file.

I'm facing the below "syntax error" when i try to execute the schema from the below command.,

psql -U matterhorn < postgresql.sql

CREATE TABLE

INSERT 0 1

CREATE TABLE

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE TABLE

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE TABLE

ERROR:  syntax error at or near "CONSTRAINT"

LINE 12:   CONSTRAINT "FK_mh_acl_episode_transition_managed_acl_fk" F...

           ^

ERROR:  syntax error at or near "CONSTRAINT"

LINE 13:   CONSTRAINT "FK_mh_acl_series_transition_managed_acl_fk" FO...

           ^

CREATE TABLE

ERROR:  syntax error at or near "CONSTRAINT"

LINE 10:   CONSTRAINT "FK_mh_group_organization" FOREIGN KEY ("organi...

           ^

CREATE TABLE

ERROR:  syntax error at or near "CONSTRAINT"

LINE 6:   CONSTRAINT "FK_mh_group_role_group_id" FOREIGN KEY ("group...

          ^

CREATE TABLE

CREATE INDEX

ERROR:  syntax error at or near "CONSTRAINT"

LINE 6:   CONSTRAINT "FK_mh_user_role_role_id" FOREIGN KEY ("role_id...

          ^

ERROR:  syntax error at or near "CONSTRAINT"

LINE 11:   CONSTRAINT "FK_mh_user_ref_organization" FOREIGN KEY ("org...

Can anyone help me? Is there anything i made mistake? Is there any download link to directly download the schema?

Thanks

Sathish Thangathurai.

[Tobias Wunden]

Hi Sathish,

due to a lack of adoption over the past years and months, official support for PostgreSQL has been stopped and I don't think there are current PostgreSQL DDL scripts available for the 1.6 version (and later). Therefore the only way to get it to work will be to use the current MySQL scheme from the checkout at /docs/scripts/ddl and convert it to PostgreSQL.

In case you are intending to keep using PostgreSQL and are willing to do the QA work for updated versions of it, the community may be willing to reestablish support for PostgreSQL, even though I obviously can't speak for the community as a whole.

Tobias

To unsubscribe from this group and stop receiving emails from it, send an email to matterhorn-use...@opencast.org.

[Mikey Orr]

Hey Sathish-

Fellow PostgreSQL/Matterhorn user here. =)

There are a few missing/additional commas on the DDL for PostgreSQL. I tried editing the one online, but don't seem to have access. I have only tested this with Matterhorn 1.5.x, so I don't know how different the schema is with 1.6. I'll spend some time looking at the MySQL one and translating it to Postgres.

Here's my Matterhorn 1.5 PostgreSQL DDL for now:

/*****************************************************************************

Database Schema for Matterhorn 1.5 and PostgreSQL 8.4

You can find the latest DDL here:
   https://opencast.jira.com/wiki/display/MHTRUNK/Postgres+DDL

To setup Postgres with Matterhorn, look here:
   https://opencast.jira.com/wiki/display/MHTRUNK/PostgreSQL+Database+Configuration
  
*****************************************************************************/

CREATE TABLE "sequence" (
  "seq_name" character varying(50) NOT NULL,
  "seq_count" numeric(38,0),
  PRIMARY KEY ("seq_name")
);
 
INSERT INTO sequence (seq_name, seq_count) values ('SEQ_GEN', 0);
 
CREATE TABLE "mh_organization" (
  "id" character varying(128) NOT NULL,
  "anonymous_role" character varying(255),
  "name" character varying(255),
  "admin_role" character varying(255),
  PRIMARY KEY ("id")
);
 
CREATE TABLE "mh_organization_node" (
  "organization" character varying(128) NOT NULL,
  "port" integer,
  "name" character varying(255),
  PRIMARY KEY ("organization", "port", "name"),
  CONSTRAINT "FK_mh_organization_node_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id") ON DELETE CASCADE
);
 
CREATE INDEX "IX_mh_organization_node_pk" ON "mh_organization_node" ("organization");
CREATE INDEX "IX_mh_organization_node_name" ON "mh_organization_node" ("name");
CREATE INDEX "IX_mh_organization_node_port" ON "mh_organization_node" ("port");
 
CREATE TABLE "mh_organization_property" (
  "organization" character varying(128) NOT NULL,
  "name" character varying(255) NOT NULL,
  "value" character varying(255),
  PRIMARY KEY ("organization", "name"),
  CONSTRAINT "FK_mh_organization_property_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id") ON DELETE CASCADE
);
 
CREATE INDEX "IX_mh_organization_property_pk" ON "mh_organization_property" ("organization");
 
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")
);
 
CREATE INDEX "IX_mh_annotation_created" ON "mh_annotation" ("created");
CREATE INDEX "IX_mh_annotation_inpoint" ON "mh_annotation" ("inpoint");
CREATE INDEX "IX_mh_annotation_outpoint" ON "mh_annotation" ("outpoint");
CREATE INDEX "IX_mh_annotation_mediapackage" ON "mh_annotation" ("mediapackage");
CREATE INDEX "IX_mh_annotation_private" ON "mh_annotation" ("private");
CREATE INDEX "IX_mh_annotation_user" ON "mh_annotation" ("user_id");
CREATE INDEX "IX_mh_annotation_session" ON "mh_annotation" ("session");
CREATE INDEX "IX_mh_annotation_type" ON "mh_annotation" ("type");
 
CREATE TABLE "mh_capture_agent_role" (
  "id" character varying(128) NOT NULL,
  "organization" character varying(128) NOT NULL,
  "role" character varying(255),
  PRIMARY KEY ("id", "organization", "role"),
  CONSTRAINT "FK_mh_capture_agent_role_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id") ON DELETE CASCADE
);
 
CREATE INDEX "IX_mh_capture_agent_role" ON "mh_capture_agent_role" ("id", "organization");
 
CREATE TABLE "mh_capture_agent_state" (
  "id" character varying(128) NOT NULL,
  "organization" character varying(128) NOT NULL,
  "configuration" text,
  "state" text NOT NULL,
  "last_heard_from" bigint NOT NULL,
  "url" text,
  PRIMARY KEY ("id", "organization"),
  CONSTRAINT "FK_mh_capture_agent_state_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id") ON DELETE CASCADE
);
 
CREATE TABLE "mh_dictionary" (
  "text" character varying(255) NOT NULL,
  "language" character varying(5) NOT NULL,
  "weight" numeric(8,2),
  "count" bigint,
  "stop_word" boolean,
  PRIMARY KEY ("text", "language")
);
 
CREATE INDEX "IX_mh_dictionary_weight" ON "mh_dictionary" ("weight");
 
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")
);
 
CREATE INDEX "IX_mh_host_registration_online" ON "mh_host_registration" ("online");
CREATE INDEX "IX_mh_host_registration_active" ON "mh_host_registration" ("active");
 
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
);
 
CREATE INDEX "IX_mh_service_registration_service_type" ON "mh_service_registration" ("service_type");
CREATE INDEX "IX_mh_service_registration_service_state" ON "mh_service_registration" ("service_state");
CREATE INDEX "IX_mh_service_registration_active" ON "mh_service_registration" ("active");
CREATE INDEX "IX_mh_service_registration_host_registration" ON "mh_service_registration" ("host_registration");
 
CREATE TABLE "mh_job" (
  "id" bigint NOT NULL,
  "status" integer,
  "payload" text,
  "date_started" timestamp,
  "run_time" bigint,
  "creator" text NOT NULL,
  "instance_version" bigint,
  "date_completed" timestamp,
  "operation" character varying(128),
  "dispatchable" boolean DEFAULT TRUE,
  "organization" character varying(128) NOT NULL,
  "date_created" timestamp,
  "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
);
 
CREATE INDEX "IX_mh_job_parent" ON "mh_job" ("parent");
CREATE INDEX "IX_mh_job_root" ON "mh_job" ("root");
CREATE INDEX "IX_mh_job_creator_service" ON "mh_job" ("creator_service");
CREATE INDEX "IX_mh_job_processor_service" ON "mh_job" ("processor_service");
CREATE INDEX "IX_mh_job_status" ON "mh_job" ("status");
CREATE INDEX "IX_mh_job_date_created" ON "mh_job" ("date_created");
CREATE INDEX "IX_mh_job_date_completed" ON "mh_job" ("date_completed");
CREATE INDEX "IX_mh_job_dispatchable" ON "mh_job" ("dispatchable");
CREATE INDEX "IX_mh_job_operation" ON "mh_job" ("operation");
CREATE INDEX "IX_mh_job_statistics" ON "mh_job" ("processor_service", "status", "queue_time", "run_time");
 
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
);
 
CREATE INDEX "IX_mh_job_argument_id" ON "mh_job_argument" ("id");
 
CREATE TABLE "mh_job_context" (
  "id" bigint NOT NULL,
  "name" character varying(255) NOT NULL,
  "value" text,
  CONSTRAINT "UNQ_mh_job_context_name" UNIQUE ("id", "name"),
  CONSTRAINT "FK_mh_job_context_id" FOREIGN KEY ("id") REFERENCES "mh_job" ("id") ON DELETE CASCADE
);
 
CREATE INDEX "IX_mh_job_context_id" ON "mh_job_context" ("id");
 
CREATE TABLE "mh_scheduled_event" (
  "id" bigint NOT NULL,
  "capture_agent_metadata" text,
  "dublin_core" text,
  PRIMARY KEY ("id")
);
 
CREATE TABLE "mh_search" (
  "id" character varying(128) NOT NULL,
  "organization" character varying(128),
  "deletion_date" timestamp,
  "access_control" text,
  "mediapackage_xml" text,
  "modification_date" timestamp,
  PRIMARY KEY ("id"),
  CONSTRAINT "FK_mh_search_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id") ON DELETE CASCADE
);
 
CREATE INDEX "IX_mh_search_organization" ON "mh_search" ("organization");
 
CREATE TABLE "mh_series" (
  "id" character varying(128) NOT NULL,
  "organization" character varying(128) NOT NULL,
  "access_control" text,
  "dublin_core" text,
  PRIMARY KEY ("id", "organization"),
  CONSTRAINT "FK_mh_series_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id") ON DELETE CASCADE
);
 
CREATE TABLE "mh_upload" (
  "id" character varying(128) NOT NULL,
  "total" bigint NOT NULL,
  "received" bigint NOT NULL,
  "filename" text NOT NULL,
  PRIMARY KEY ("id")
);
 
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")
);
 
CREATE INDEX "IX_mh_user_action_created" ON "mh_user_action" ("created");
CREATE INDEX "IX_mh_user_action_inpoint" ON "mh_user_action" ("inpoint");
CREATE INDEX "IX_mh_user_action_outpoint" ON "mh_user_action" ("outpoint");
CREATE INDEX "IX_mh_user_action_mediapackage" ON "mh_user_action" ("mediapackage");
CREATE INDEX "IX_mh_user_action_user" ON "mh_user_action" ("user_id");
CREATE INDEX "IX_mh_user_action_session" ON "mh_user_action" ("session");
CREATE INDEX "IX_mh_user_action_type" ON "mh_user_action" ("type");
 
CREATE TABLE "mh_oaipmh_harvesting" (
  "url" character varying(255) NOT NULL,
  "last_harvested" timestamp,
  PRIMARY KEY (url)
);
 
CREATE TABLE "mh_episode_episode" (
  "id" character varying(128) NOT NULL,
  "version" bigint NOT NULL,
  "organization" character varying(128),
  "deletion_date" timestamp,
  "access_control" text,
  "mediapackage_xml" text,
  "modification_date" timestamp,
  PRIMARY KEY ("id", "version", "organization"),
  CONSTRAINT "FK_mh_episode_episode_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id") ON DELETE CASCADE
);
 
CREATE INDEX "IX_mh_episode_episode_mediapackage" ON "mh_episode_episode" ("id");
CREATE INDEX "IX_mh_episode_episode_version" ON "mh_episode_episode" ("version");
 
CREATE TABLE "mh_episode_asset" (
  "id" bigint NOT NULL,
  "mediapackageelement" character varying(128) NOT NULL,
  "mediapackage" character varying(128) NOT NULL,
  "organization" character varying(128) NOT NULL,
  "checksum" character varying(255) NOT NULL,
  "uri" character varying(255) NOT NULL,
  "version" bigint NOT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "UNQ_mh_episode_asset_0" UNIQUE ("organization", "mediapackage", "mediapackageelement", "version"),
  CONSTRAINT "FK_mh_episode_asset_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id") ON DELETE CASCADE
);
 
CREATE INDEX "IX_mh_episode_asset_mediapackage" ON "mh_episode_asset" ("mediapackage");
CREATE INDEX "IX_mh_episode_asset_checksum" ON "mh_episode_asset" ("checksum");
CREATE INDEX "IX_mh_episode_asset_uri" ON "mh_episode_asset" ("uri");
 
CREATE TABLE "mh_episode_version_claim" (
 "mediapackage" character varying(128) NOT NULL,
 "last_claimed" bigint NOT NULL,
 PRIMARY KEY ("mediapackage")
);
 
CREATE INDEX "IX_mh_episode_version_claim_mediapackage" ON "mh_episode_version_claim" ("mediapackage");
CREATE INDEX "IX_mh_episode_version_claim_last_claimed" ON "mh_episode_version_claim" ("last_claimed");
 
--
-- ACL manager
--
CREATE TABLE "mh_acl_managed_acl" (
  "pk" bigint NOT NULL,
  "acl" text NOT NULL,
  "name" character varying(128) NOT NULL,
  "organization_id" character varying(128) NOT NULL,
  PRIMARY KEY ("pk"),
  CONSTRAINT "UNQ_mh_acl_managed_acl_0" UNIQUE ("name","organization_id")
);
 
CREATE TABLE "mh_acl_episode_transition" (
  "pk" bigint NOT NULL,
  "workflow_params" character varying(255) DEFAULT NULL,
  "application_date" timestamp DEFAULT NULL,
  "workflow_id" character varying(128) DEFAULT NULL,
  "done" boolean,
  "episode_id" character varying(128) DEFAULT NULL,
  "organization_id" character varying(128) DEFAULT NULL,
  "managed_acl_fk" bigint DEFAULT NULL,
  PRIMARY KEY ("pk"),
  CONSTRAINT "UNQ_mh_acl_episode_transition_0" UNIQUE ("episode_id","organization_id","application_date"),
  CONSTRAINT "FK_mh_acl_episode_transition_managed_acl_fk" FOREIGN KEY ("managed_acl_fk") REFERENCES "mh_acl_managed_acl" ("pk")
);
 
CREATE TABLE "mh_acl_series_transition" (
  "pk" bigint NOT NULL,
  "workflow_params" character varying(255) DEFAULT NULL,
  "application_date" timestamp DEFAULT NULL,
  "workflow_id" character varying(128) DEFAULT NULL,
  "override" boolean,
  "done" boolean,
  "organization_id" character varying(128) DEFAULT NULL,
  "series_id" character varying(128) DEFAULT NULL,
  "managed_acl_fk" bigint DEFAULT NULL,
  PRIMARY KEY ("pk"),
  CONSTRAINT "UNQ_mh_acl_series_transition_0" UNIQUE ("series_id","organization_id","application_date"),
  CONSTRAINT "FK_mh_acl_series_transition_managed_acl_fk" FOREIGN KEY ("managed_acl_fk") REFERENCES "mh_acl_managed_acl" ("pk")
);
 
--
-- groups, roles and users
--
CREATE TABLE "mh_role" (
  "id" bigint NOT NULL,
  "description" character varying(255) DEFAULT NULL,
  "name" character varying(128) DEFAULT NULL,
  "organization" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "UNQ_mh_role_0" UNIQUE ("name", "organization"),
  CONSTRAINT "FK_mh_role_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id") ON DELETE CASCADE
);
 
CREATE TABLE "mh_group" (
  "id" bigint NOT NULL,
  "group_id" character varying(128) DEFAULT NULL,
  "description" character varying(255) DEFAULT NULL,
  "name" character varying(128) DEFAULT NULL,
  "role" character varying(255) DEFAULT NULL,
  "organization" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "UNQ_mh_group_0" UNIQUE ("group_id", "organization"),
  CONSTRAINT "FK_mh_group_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id")
);
 
CREATE TABLE "mh_group_member" (
  "jpagroup_id" bigint NOT NULL,
  "members" character varying(255) DEFAULT NULL
);
 
CREATE TABLE "mh_group_role" (
  "group_id" bigint NOT NULL,
  "role_id" bigint NOT NULL,
  PRIMARY KEY ("group_id", "role_id"),
  CONSTRAINT "UNQ_mh_group_role_0" UNIQUE ("group_id", "role_id"),
  CONSTRAINT "FK_mh_group_role_group_id" FOREIGN KEY ("group_id") REFERENCES "mh_group" ("id"),
  CONSTRAINT "FK_mh_group_role_role_id" FOREIGN KEY ("role_id") REFERENCES "mh_role" ("id")
);
 
CREATE TABLE "mh_user" (
  "id" bigint NOT NULL,
  "username" character varying(128) DEFAULT NULL,
  "password" text,
  "organization" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "UNQ_mh_user_0" UNIQUE ("username", "organization"),
  CONSTRAINT "FK_mh_user_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id") ON DELETE CASCADE
);
 
CREATE INDEX "IX_mh_role_pk" ON "mh_role" ("name", "organization");
 
CREATE TABLE "mh_user_role" (
  "user_id" bigint NOT NULL,
  "role_id" bigint NOT NULL,
  PRIMARY KEY ("user_id", "role_id"),
  CONSTRAINT "UNQ_mh_user_role_0" UNIQUE ("user_id", "role_id"),
  CONSTRAINT "FK_mh_user_role_role_id" FOREIGN KEY ("role_id") REFERENCES "mh_role" ("id"),
  CONSTRAINT "FK_mh_user_role_user_id" FOREIGN KEY ("user_id") REFERENCES "mh_user" ("id")
);
 
CREATE TABLE "mh_user_ref" (
  "id" bigint NOT NULL,
  "username" character varying(128) DEFAULT NULL,
  "last_login" timestamp DEFAULT NULL,
  "email" character varying(255) DEFAULT NULL,
  "name" character varying(255) DEFAULT NULL,
  "login_mechanism" character varying(255) DEFAULT NULL,
  "organization" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "UNQ_mh_user_ref_0" UNIQUE ("username", "organization"),
  CONSTRAINT "FK_mh_user_ref_organization" FOREIGN KEY ("organization") REFERENCES "mh_organization" ("id")
);
 
CREATE TABLE "mh_user_ref_role" (
  "user_id" bigint NOT NULL,
  "role_id" bigint NOT NULL,
  PRIMARY KEY ("user_id", "role_id"),
  CONSTRAINT "UNQ_mh_user_ref_role_0" UNIQUE ("user_id", "role_id"),
  CONSTRAINT "FK_mh_user_ref_role_role_id" FOREIGN KEY ("role_id") REFERENCES "mh_role" ("id"),
  CONSTRAINT "FK_mh_user_ref_role_user_id" FOREIGN KEY ("user_id") REFERENCES "mh_user_ref" ("id")
);

Hope that helps! =)

-Mikey

--

Mikey Orr

Learning Technologies Mac/Unix Systems Integrator

ITS @ University of California, Santa Cruz

[Sathish Thangathurai]

Hi Tobias & Orr,

Thanks for your suggestion.

For the time being, I've decided to use mysql rather than postgres in 1.6.

Thanks

Sathish Thangathurai.

[Alexander Bias]

Dear Sathish, dear Mikey,

since the MH core has dropped Postgres support, university of Ulm has been supporting and updating the Postgres DDLs and update scripts and posted them in the wiki. At the moment, the Postgres DDLs and update scripts which can be found in the wiki and outdated and will not work for 1.6, we haven’t worked on 1.6 yet due to the christmas holidays.

Please expect updated Postgres scripts from us in the wiki within the next few days. I will keep you informed.

Best regards
Alexander Bias

kiz - Media Department
Team Web & Teaching Support
University of Ulm
89069 Ulm, Germany

[Mikey Orr]

Hi Alexander-

That's excellent news! My Postgres skills are a little rusty, but I'm more than happy to help test/refine any DDL's you create. We're currently using Matterhorn 1.5.1 in production and will probably move to 1.6 over the Summer. Plenty of time for testing.

Thanks again! =)

-Mikey

[Sathish Thangathurai]

Hi Alexander,

Good news!. We are very keen on to use postgres as backend rather than mysql.

thanks Alexander.

Best regards,

Sathish Thangathurai.

[Alexander Bias]

Dear Sathish, dear Mikey,

today, we managed to transfer the MySQL DDL and Upgrade Scripts to 1.6.

Please find them on https://opencast.jira.com/wiki/display/mh16/Postgres+DDL and on https://opencast.jira.com/wiki/display/mh16/Postgres+DDL+Update.

@Mikey: I didn’t check our work against the DDL you have posted in this thread. I just verified that our new DDL and the DDL Update runs correctly, if you have time to do more checks and have change requests, please let me know.