Upgrade 1.x to 1.2 database

Table of Contents IMPORTANT! Overview Instructions PostgreSQL SQL Server Oracle

IMPORTANT!

The only supported use case for upgrading a Geoportal 1.x database to a 1.2 database is if you are also deploying a Geoportal 1.2 web application and you want the Collections functionality. If you don't want the Collections functionality, then you can deploy the Geoportal version 1.2 and connect to your existing Geoportal 1.x database without making any changes to the database.

However, if you have a Geoportal Server 1.0, 1.1, or 1.1.1 deployment and you'd like to deploy version 1.2 to take advantage of the Collections feature, then this set of instructions is for you. With the database change described here, you will still need to deploy the Geoportal Server version 1.2; Collections are not supported at all in the UI of previous geoportal versions.

Overview

To support Collections at Geoportal version 1.2, there are two additional database tables that need to be present and indexed: GPT_COLLECTION and GPT_COLLECTION_MEMBER. The database scripts distributed with Geoportal Server 1.2 contain commands to create the tables and their corresponding indices. To support collections in your Geoportal 1.2 instance without having to rerun the database scripts for 1.2, you can manually create these tables through SQL commands. The tables are then added to your existing Geoportal database.

Instructions

These instructions assume familiarity with running SQL commands in your database environment. You should work with your DBA when making changes to the geoportal database. If your database is a production instance, we strongly recommend backing up the database before running any SQL commands. These commands should not alter any of the existing geoportal tables, but backing up the database protects you in case you make a mistake.

Next, connect to your database as the user who owns the other geoportal tables.

Now, follow the set of instructions below that corresponds to the RDBMS your organization is using. After you run the commands, you should see two new tables in your geoportal database, and with the Geoportal 1.2 start using Collections functionality.

PostgreSQL

For PostgreSQL, run the following SQL Commands:

CREATE TABLE GPT_COLLECTION_MEMBER (
    DOCUUID character varying(38) NOT NULL,
    COLUUID character varying(38) NOT NULL
) WITHOUT OIDS;
CREATE INDEX GPT_COLL_MEMBER_IDX1 ON GPT_COLLECTION_MEMBER USING btree(DOCUUID);
CREATE INDEX GPT_COLL_MEMBER_IDX2 ON GPT_COLLECTION_MEMBER USING btree(COLUUID);

SQL Server

For SQL Server, run the following SQL Commands:

CREATE TABLE GPT_COLLECTION_MEMBER (
    DOCUUID VARCHAR(38) NOT NULL,
    COLUUID VARCHAR(38) NOT NULL
);
GO
CREATE INDEX GPT_COLL_MEMBER_IDX1 ON GPT_COLLECTION_MEMBER(DOCUUID);
GO
CREATE INDEX GPT_COLL_MEMBER_IDX2 ON GPT_COLLECTION_MEMBER(COLUUID);
GO

Oracle

For Oracle, run the following SQL Commands:

CREATE TABLE GPT_COLLECTION_MEMBER (
    DOCUUID VARCHAR2(38) NOT NULL,
    COLUUID VARCHAR2(38) NOT NULL
);
CREATE INDEX GPT_COLL_MEMBER_IDX1 ON GPT_COLLECTION_MEMBER(DOCUUID);
CREATE INDEX GPT_COLL_MEMBER_IDX2 ON GPT_COLLECTION_MEMBER(COLUUID);

QUIT;

---

Back to Installation or Collections