We're using postgresql 9.1.2 and for whatever reason auto ddl generation
is not working. So I converted the mysql5.sql file in docs/scripts/ddl
for use with postgresql.
I did this by installing mysql, creating a matterhorn db, and then
importing the mysql5.sql file:
mysql -p -u YOURUSER matterhorn < mysql5.sql
Then I dumped the database using mysqldump and telling it to only dump
tables:
mysqldump -u YOURUSER -p -d matterhorn > matterhornout.sql
Then I used a perl module named SQL-Translator to convert it:
http://search.cpan.org/~jrobinson/SQL-Translator-0.11010/
sqlt -f MySQL -t PostgreSQL matterhornout.sql > pg.sql
From there I edited pg.sql and changed all references to "smallint
DEFAULT 0" to "boolean"
I also made the table "SEQUENCE" and all of its members lowercase. The
uppercase names were causing issues. I'm not sure why, it could be
because SEQUENCE is a reserved word?
I added the INSERT statement to populate the sequence table.
Finally I disabled auto ddl in config.properties.
I haven't done extensive testing, but matterhorn appears to fire up and
populate the tables just fine. I see no db errors in the postgres logs
or in the opencast logs.
The result is below:
--
-- Created by SQL::Translator::Producer::PostgreSQL
-- Created on Wed Jan 11 14:13:05 2012
--
--
-- Table: sequence
--
CREATE TABLE "sequence" (
"SEQ_NAME" character varying(50) NOT NULL,
"SEQ_COUNT" numeric(38,0) DEFAULT NULL,
PRIMARY KEY ("SEQ_NAME")
);
--
-- Table: annotation
--
CREATE TABLE "annotation" (
"id" bigint NOT NULL,
"outpoint" bigint DEFAULT NULL,
"inpoint" bigint DEFAULT NULL,
"mediapackage_id" character varying(36) DEFAULT NULL,
"session_id" text,
"created" timestamp DEFAULT NULL,
"user_id" text,
"length" bigint DEFAULT NULL,
"annotation_value" text,
"annotation_type" text,
PRIMARY KEY ("id")
);
--
-- Table: capture_agent_role
--
CREATE TABLE "capture_agent_role" (
"id" character varying(255) NOT NULL,
"organization" character varying(255) NOT NULL,
"role" character varying(255) DEFAULT NULL
);
--
-- Table: capture_agent_state
--
CREATE TABLE "capture_agent_state" (
"organization" character varying(255) NOT NULL,
"id" character varying(255) NOT NULL,
"configuration" text,
"state" text NOT NULL,
"last_heard_from" bigint NOT NULL,
"url" text,
PRIMARY KEY ("organization", "id")
);
--
-- Table: dictionary
--
CREATE TABLE "dictionary" (
"text" character varying(255) NOT NULL,
"language" character varying(255) NOT NULL,
"weight" numeric(8,2) DEFAULT NULL,
"count" bigint DEFAULT NULL,
"stop_word" boolean,
PRIMARY KEY ("text", "language")
);
--
-- Table: host_registration
--
CREATE TABLE "host_registration" (
"id" bigint NOT NULL,
"host" character varying(255) NOT NULL,
"maintenance" boolean NOT NULL,
"max_jobs" bigint NOT NULL,
"online" boolean NOT NULL,
PRIMARY KEY ("id"),
CONSTRAINT "UNQ_host_registration_0" UNIQUE ("host")
);
CREATE INDEX "IX_host_registration_UNQ_host_registration_0" on
"host_registration" ("host");
--
-- Table: job
--
CREATE TABLE "job" (
"id" bigint NOT NULL,
"status" bigint DEFAULT NULL,
"payload" text,
"date_started" timestamp DEFAULT NULL,
"run_time" bigint DEFAULT NULL,
"creator" text NOT NULL,
"instance_version" bigint DEFAULT NULL,
"date_completed" timestamp DEFAULT NULL,
"operation" text,
"is_dispatchable" boolean,
"organization" text NOT NULL,
"date_created" timestamp DEFAULT NULL,
"queue_time" bigint DEFAULT NULL,
"creator_service" bigint DEFAULT NULL,
"parent_id" bigint DEFAULT NULL,
"processor_svc" bigint DEFAULT NULL,
"root_id" bigint DEFAULT NULL,
PRIMARY KEY ("id")
);
CREATE INDEX "FK_job_creator_service" on "job" ("creator_service");
CREATE INDEX "FK_job_parent_id" on "job" ("parent_id");
CREATE INDEX "FK_job_processor_svc" on "job" ("processor_svc");
CREATE INDEX "FK_job_root_id" on "job" ("root_id");
--
-- Table: job_arguments
--
CREATE TABLE "job_arguments" (
"id" bigint NOT NULL,
"argument" text,
"list_index" bigint DEFAULT NULL,
CONSTRAINT "UNQ_job_arguments_0" UNIQUE ("id", "list_index")
);
CREATE INDEX "IX_job_arguments_UNQ_job_arguments_0" on "job_arguments"
("id", "list_index");
--
-- Table: matterhorn_role
--
CREATE TABLE "matterhorn_role" (
"username" character varying(255) NOT NULL,
"organization" character varying(255) NOT NULL,
"role" text
);
--
-- Table: matterhorn_user
--
CREATE TABLE "matterhorn_user" (
"username" character varying(255) NOT NULL,
"organization" character varying(255) NOT NULL,
"password" text,
PRIMARY KEY ("username", "organization")
);
--
-- Table: scheduled_event
--
CREATE TABLE "scheduled_event" (
"event_id" bigint NOT NULL,
"capture_agent_metadata" text,
"dublin_core" text,
PRIMARY KEY ("event_id")
);
--
-- Table: series
--
CREATE TABLE "series" (
"organization_id" character varying(128) NOT NULL,
"series_id" character varying(128) NOT NULL,
"access_control" text,
"dublin_core" text,
PRIMARY KEY ("organization_id", "series_id")
);
--
-- Table: service_registration
--
CREATE TABLE "service_registration" (
"id" bigint NOT NULL,
"job_producer" boolean NOT NULL,
"path" text NOT NULL,
"service_type" character varying(255) NOT NULL,
"online" boolean NOT NULL,
"host_registration" bigint DEFAULT NULL,
PRIMARY KEY ("id"),
CONSTRAINT "UNQ_service_registration_0" UNIQUE ("host_registration",
"service_type")
);
CREATE INDEX "IX_service_registration_UNQ_service_registration_0" on
"service_registration" ("host_registration", "service_type");
--
-- Table: upload
--
CREATE TABLE "upload" (
"id" character varying(255) NOT NULL,
"total" bigint NOT NULL,
"received" bigint NOT NULL,
"filename" text NOT NULL,
PRIMARY KEY ("id")
);
--
-- Table: user_action
--
CREATE TABLE "user_action" (
"id" bigint NOT NULL,
"user_ip" text,
"outpoint" bigint DEFAULT NULL,
"inpoint" bigint DEFAULT NULL,
"mediapackage_id" text,
"session_id" text,
"created" timestamp DEFAULT NULL,
"user_id" text,
"length" bigint DEFAULT NULL,
"type" text,
"is_playing" boolean,
PRIMARY KEY ("id")
);
INSERT INTO sequence (seq_name, seq_count) values ('SEQ_GEN',0);
--
-- Created by SQL::Translator::Producer::PostgreSQL
-- Created on Wed Jan 11 14:13:05 2012
--
--
-- Table: sequence
--
CREATE TABLE "sequence" (
"seq_name" character varying(50) NOT NULL,
"seq_count" numeric(38,0) DEFAULT NULL,
PRIMARY KEY ("seq_name")
);
--
-- Table: annotation
--
CREATE TABLE "annotation" (
"id" bigint NOT NULL,
"outpoint" bigint DEFAULT NULL,
"inpoint" bigint DEFAULT NULL,
"mediapackage_id" character varying(36) DEFAULT NULL,
"session_id" text,
"created" timestamp DEFAULT NULL,
"user_id" text,
"length" bigint DEFAULT NULL,
"annotation_value" text,
"annotation_type" text,
PRIMARY KEY ("id")
);
--
-- Table: capture_agent_role
--
CREATE TABLE "capture_agent_role" (
"id" character varying(255) NOT NULL,
"organization" character varying(255) NOT NULL,
"role" character varying(255) DEFAULT NULL
);
--
-- Table: capture_agent_state
--
CREATE TABLE "capture_agent_state" (
"organization" character varying(255) NOT NULL,
"id" character varying(255) NOT NULL,
"configuration" text,
"state" text NOT NULL,
"last_heard_from" bigint NOT NULL,
"url" text,
PRIMARY KEY ("organization", "id")
);
--
-- Table: dictionary
--
CREATE TABLE "dictionary" (
"text" character varying(255) NOT NULL,
"language" character varying(255) NOT NULL,
"weight" numeric(8,2) DEFAULT NULL,
"count" bigint DEFAULT NULL,
"stop_word" boolean,
PRIMARY KEY ("text", "language")
);
--
-- Table: host_registration
--
CREATE TABLE "host_registration" (
"id" bigint NOT NULL,
"host" character varying(255) NOT NULL,
"maintenance" boolean NOT NULL,
"max_jobs" bigint NOT NULL,
"online" boolean NOT NULL,
PRIMARY KEY ("id"),
CONSTRAINT "UNQ_host_registration_0" UNIQUE ("host")
);
CREATE INDEX "IX_host_registration_UNQ_host_registration_0" on
"host_registration" ("host");
--
-- Table: job
--
CREATE TABLE "job" (
"id" bigint NOT NULL,
"status" bigint DEFAULT NULL,
"payload" text,
"date_started" timestamp DEFAULT NULL,
"run_time" bigint DEFAULT NULL,
"creator" text NOT NULL,
"instance_version" bigint DEFAULT NULL,
"date_completed" timestamp DEFAULT NULL,
"operation" text,
"is_dispatchable" boolean,
"organization" text NOT NULL,
"date_created" timestamp DEFAULT NULL,
"queue_time" bigint DEFAULT NULL,
"creator_service" bigint DEFAULT NULL,
"parent_id" bigint DEFAULT NULL,
"processor_svc" bigint DEFAULT NULL,
"root_id" bigint DEFAULT NULL,
PRIMARY KEY ("id")
);
CREATE INDEX "FK_job_creator_service" on "job" ("creator_service");
CREATE INDEX "FK_job_parent_id" on "job" ("parent_id");
CREATE INDEX "FK_job_processor_svc" on "job" ("processor_svc");
CREATE INDEX "FK_job_root_id" on "job" ("root_id");
--
-- Table: job_arguments
--
CREATE TABLE "job_arguments" (
"id" bigint NOT NULL,
"argument" text,
"list_index" bigint DEFAULT NULL,
CONSTRAINT "UNQ_job_arguments_0" UNIQUE ("id", "list_index")
);
CREATE INDEX "IX_job_arguments_UNQ_job_arguments_0" on "job_arguments" ("id",
"list_index");
--
-- Table: matterhorn_role
--
CREATE TABLE "matterhorn_role" (
"username" character varying(255) NOT NULL,
"organization" character varying(255) NOT NULL,
"role" text
);
--
-- Table: matterhorn_user
--
CREATE TABLE "matterhorn_user" (
"username" character varying(255) NOT NULL,
"organization" character varying(255) NOT NULL,
"password" text,
PRIMARY KEY ("username", "organization")
);
--
-- Table: scheduled_event
--
CREATE TABLE "scheduled_event" (
"event_id" bigint NOT NULL,
"capture_agent_metadata" text,
"dublin_core" text,
PRIMARY KEY ("event_id")
);
--
-- Table: series
--
CREATE TABLE "series" (
"organization_id" character varying(128) NOT NULL,
"series_id" character varying(128) NOT NULL,
"access_control" text,
"dublin_core" text,
PRIMARY KEY ("organization_id", "series_id")
);
--
-- Table: service_registration
--
CREATE TABLE "service_registration" (
"id" bigint NOT NULL,
"job_producer" boolean NOT NULL,
"path" text NOT NULL,
"service_type" character varying(255) NOT NULL,
"online" boolean NOT NULL,
"host_registration" bigint DEFAULT NULL,
PRIMARY KEY ("id"),
CONSTRAINT "UNQ_service_registration_0" UNIQUE ("host_registration",
"service_type")
);
CREATE INDEX "IX_service_registration_UNQ_service_registration_0" on
"service_registration" ("host_registration", "service_type");
--
-- Table: upload
--
CREATE TABLE "upload" (
"id" character varying(255) NOT NULL,
"total" bigint NOT NULL,
"received" bigint NOT NULL,
"filename" text NOT NULL,
PRIMARY KEY ("id")
);
--
-- Table: user_action
--
CREATE TABLE "user_action" (
"id" bigint NOT NULL,
"user_ip" text,
"outpoint" bigint DEFAULT NULL,
"inpoint" bigint DEFAULT NULL,
"mediapackage_id" text,
"session_id" text,
"created" timestamp DEFAULT NULL,
"user_id" text,
"length" bigint DEFAULT NULL,
"type" text,
"is_playing" boolean,
PRIMARY KEY ("id")
);
INSERT INTO sequence (seq_name, seq_count) values ('SEQ_GEN',0);_______________________________________________
Matterhorn-users mailing list
[email protected]
http://lists.opencastproject.org/mailman/listinfo/matterhorn-users
