Hi Jonathan, thanks a lot for this contribution! I will make sure it's being added to the codebase.
Tobias On 11.01.2012, at 23:42, Jonathan Felder <[email protected]> wrote: > 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); > <pg.sql> > _______________________________________________ > Matterhorn-users mailing list > [email protected] > http://lists.opencastproject.org/mailman/listinfo/matterhorn-users _______________________________________________ Matterhorn-users mailing list [email protected] http://lists.opencastproject.org/mailman/listinfo/matterhorn-users
