Migration for Geonetwork 2.2 to 2.6

geonetwork-db-postgis-migration.sql

-- Geonetwork migration

CREATE TABLE CustomElementSet
(
  xpath varchar(1000) not null
);

-- Fix languages table
ALTER TABLE Languages
  ADD COLUMN isocode varchar(3),
  ADD COLUMN isInspire char(1) DEFAULT 'n',
  ADD COLUMN isDefault char(1) DEFAULT 'n';

UPDATE Languages SET isocode = 'eng', isInspire = 'y', isDefault = 'y' WHERE id = 'en';
UPDATE Languages SET isocode = 'fre', isInspire = 'y', isDefault = 'n' WHERE id = 'fr';
UPDATE Languages SET isocode = 'spa', isInspire = 'y', isDefault = 'n' WHERE id = 'es';
UPDATE Languages SET isocode = 'chi', isInspire = 'n', isDefault = 'n' WHERE id = 'cn';

INSERT INTO Languages VALUES ('ru','Russian', 'rus', 'n', 'n');
INSERT INTO Languages VALUES ('ar','Arabic', 'ara', 'n', 'n');
INSERT INTO Languages VALUES ('de','Deutsch', 'ger', 'y', 'n');
INSERT INTO Languages VALUES ('nl','Nederlands', 'dut', 'y', 'n');
INSERT INTO Languages VALUES ('pt','Português', 'por', 'y', 'n');
INSERT INTO Languages VALUES ('ca','Català', 'cat', 'n', 'n');
INSERT INTO Languages VALUES ('tr','Türkçe', 'tur', 'n', 'n');

-- Fix Users
ALTER TABLE Users
  ADD COLUMN city varchar(255);

-- Add MetadataNotifiers
CREATE TABLE MetadataNotifiers
  (
    id         int,
    name       varchar(32)    not null,
    url        varchar(255)   not null,
    enabled    char(1)        default 'n' not null,
    username       varchar(32),
    password       varchar(32),

    primary key(id)
  );

-- Add MetadataNotifications
CREATE TABLE MetadataNotifications
  (
    metadataId         int,
    notifierId         int,
    notified           char(1)        default 'n' not null,
    metadataUuid       varchar(250)   not null,
    action             char(1)        not null,
    errormsg           text,

    primary key(metadataId,notifierId),

    foreign key(notifierId) references MetadataNotifiers(id)
  );

-- Add MetadataNotifications
CREATE TABLE CswServerCapabilitiesInfo
  (
    idField   int,
    langId    varchar(5)    not null,
    field     varchar(32)   not null,
    label     text,

    primary key(idField),

    foreign key(langId) references Languages(id)
  );

INSERT INTO CswServerCapabilitiesInfo VALUES (1, 'en', 'title', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (2, 'en', 'abstract', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (3, 'en', 'fees', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (4, 'en', 'accessConstraints', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (5, 'es', 'title', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (6, 'es', 'abstract', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (7, 'es', 'fees', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (8, 'es', 'accessConstraints', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (9, 'nl', 'title', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (10, 'nl', 'abstract', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (11, 'nl', 'fees', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (12, 'nl', 'accessConstraints', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (13, 'cn', 'title', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (14, 'cn', 'abstract', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (15, 'cn', 'fees', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (16, 'cn', 'accessConstraints', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (17, 'de', 'title', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (18, 'de', 'abstract', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (19, 'de', 'fees', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (20, 'de', 'accessConstraints', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (21, 'fr', 'title', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (22, 'fr', 'abstract', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (23, 'fr', 'fees', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (24, 'fr', 'accessConstraints', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (25, 'pt', 'title', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (26, 'pt', 'abstract', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (27, 'pt', 'fees', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (28, 'pt', 'accessConstraints', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (29, 'ru', 'title', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (30, 'ru', 'abstract', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (31, 'ru', 'fees', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (32, 'ru', 'accessConstraints', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (33, 'ca', 'title', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (34, 'ca', 'abstract', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (35, 'ca', 'fees', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (36, 'ca', 'accessConstraints', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (37, 'tr', 'title', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (38, 'tr', 'abstract', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (39, 'tr', 'fees', '');
INSERT INTO CswServerCapabilitiesInfo VALUES (40, 'tr', 'accessConstraints', '');

-- create IndexLanguages
CREATE TABLE IndexLanguages
  (
    id            int,
    languageName  varchar(32)   not null,
    selected      char(1)       default 'n' not null,

    primary key(id, languageName)

  );
INSERT INTO IndexLanguages VALUES (1, 'danish', 'n');
INSERT INTO IndexLanguages VALUES (2, 'dutch', 'n');
INSERT INTO IndexLanguages VALUES (3, 'english', 'y');
INSERT INTO IndexLanguages VALUES (4, 'finnish', 'n');
INSERT INTO IndexLanguages VALUES (5, 'french', 'n');
INSERT INTO IndexLanguages VALUES (6, 'german', 'n');
INSERT INTO IndexLanguages VALUES (7, 'hungarian', 'n');
INSERT INTO IndexLanguages VALUES (8, 'italian', 'n');
INSERT INTO IndexLanguages VALUES (9, 'norwegian', 'n');
INSERT INTO IndexLanguages VALUES (10, 'portuguese', 'n');
INSERT INTO IndexLanguages VALUES (11, 'russian', 'n');
INSERT INTO IndexLanguages VALUES (12, 'spanish', 'n');
INSERT INTO IndexLanguages VALUES (13, 'swedish', 'n');s
INSERT INTO IndexLanguages VALUES (14, 'catalan', 'n');
INSERT INTO IndexLanguages VALUES (15, 'turkish', 'n');

-- create spatialIndex
CREATE TABLE spatialIndex
  (
    fid int,
    id  varchar(250),

    primary key(fid)
  );

CREATE INDEX spatialIndexNDX1 ON spatialIndex(id);
SELECT AddGeometryColumn('spatialindex', 'the_geom', 4326, 'MULTIPOLYGON', 2 );
CREATE INDEX spatialIndexNDX2 on spatialIndex USING GIST(the_geom);

Just a note, you need to create the plpgsql language on the database

createlang plpgsql yourdatabase

before installing the sql functions (e.g. /usr/local/postgis/share/lwpostgis.sql or /usr/local/postgis/share/postgis.sql)