Set up the Geoportal Database

Geoportal Server requires a database to store information and content (e.g. metadata, resources), currently it supports Oracle, SQL Server, PostgreSQL and MySQL, this page contains instruction on how to setup Geoportal database tables in one of the supported databases.

Table of Contents Notes Assumptions Set Up Geoportal Database in Oracle Set Up Geoportal Database in SQL Server Set Up Geoportal Database in PostgreSQL Set Up Geoportal Database in MySQL

Notes

• If you already have a Geoportal Server 1.2.5 database, it will continue to work with Geoportal Server 1.2.6.

Assumptions

The following sections assume that:

• You already have a working database instance, if not please refer to specific database sites to install and configure the database.
• You have downloaded and unzipped the Geoportal Server Installation zip files. Files within those zip files are required for the next steps.
• The geoportal database scripts will create a schema in the default database in your system. If you would like the geoportal tables to exist in its own database, you will want to create a new database upon which you will run the geoportal database scripts. This is recommended for requirements on backing up or restoring your geoportal database.
• You are creating a new geoportal schema by running database scripts. If you run the database scripts on top of an existing geoportal installation, you will overwrite your existing geoportal database.
• For Linux users, use the Geoportal Server Linux Installation Guide, available from the How-to-Set-Up-an-Esri-Geoportal-Server-on-Linux page.

Set Up Geoportal Database in Oracle

In this section you will set up the tablespace and schema that will be used for the geoportal. The geoportal should run in its own tablespace and schema. Creating the geoportal database schema involves running two scripts:

• A “grants” script that sets the user permissions for creating the geoportal schema.
• A “create schema” script that creates the table structure, procedures and triggers and populates tables in the geoportal schema.

1. Setup the Geoportal tablespace

Open the command console (Start>Run>cmd) and run the following commands:

 CMD> sqlplus /nolog
 SQL> connect sys/sys as sysdba; 
 SQL> create tablespace geoportal datafile 'C:\oracle\oradata\geoportal.dbf' size 500M AUTOEXTEND ON;
 SQL> create user geoportal identified by geoportalpwd default tablespace geoportal temporary tablespace temp;
 SQL> commit;
 SQL> quit

2. Set up privileges for geoportal user

2.1. Open a command prompt window. Start>Run>cmd

2.2. Change directories to point to \Database Scripts\Oracle.

2.3. Run the grants_oracle.cmd file from the command prompt window as described below:

Usage:

grants_oracle [sys username] [sys password] [geoportal username]

Where:

• [sys] is the username of the sys user in Oracle
• [sys] is the password of the sys user in Oracle
• [geoportal] is the geoportal user you are creating

Sample Input: grants_oracle sys sys geoportal

When the script finishes executing you will be returned back to the command prompt and a text file (grants.txt) will open. Check the grants.txt file for error messages.

Important: You must fix errors appearing in the grants.txt file; do not continue until the script runs without errors.

3. Create geoportal schema

3.1. Open a command window. Start>Run>cmd

3.2. Change the directory to the {Geoportal Dir}\Database Scripts\Oracle folder

3.3. Run the create_schema_oracle.cmd file from the command prompt window as described below:

Usage:

create_schema_oracle [geoportal username] [geoportal password]

Where:

• [geoportal] is the geoportal user you created earlier.
• [geoportal] is the password of the geoportal user.

Sample Input: create_schema_oracle geoportal geoportalpwd

When the script finishes executing you will be returned back to the command prompt and a text file (GPT_Schema.txt) will open. Check the GPT_Schema.txt file for error messages. Error messages and warnings that state a table or view does not exist can be ignored. It simply means that the script was trying to delete a nonexistent table. Verify the following tables are created:

• gpt_collection
• gpt_collection_member
• gpt_harvesting_history
• gpt_harvesting_jobs_completed
• gpt_harvesting_jobs_pending
• gpt_resource
• gpt_resource_data
• gpt_search
• gpt_user

Set Up Geoportal Database in SQL Server

In this section you will use a script to set up the SQL Server database that will be used for the geoportal web application.

IMPORTANT PREREQUISITES:

1) If you have the “Enforce Password Policy” option enabled by default for all new users, then you will need to choose a user password in keeping with your organization’s password policy. This is usually a complex password. If you do not select a password in keeping with the password policy, the script may fail.

2) Verify that your server allows both Windows authentication and SQL Server authentication. If your server only allows Windows authentication, then the user that the script creates will not be able to logon to create the tables. The error in the build_schema.log file will read 'Login failed for user'. To check and possibly change the security authentication mode (as per Microsoft, article http://msdn.microsoft.com/en-us/library/ms188670.aspx), do the following:

• Login to SQL Server Management Studio.
• In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
• On the Security page, under Server authentication, select the "SQL Server and Windows Authentication Mode" radial if it is not already selected, and then click OK.
• In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

1. Create database schema

1.1. Open a command window. Start>Run>cmd

1.2. Change the directory to the {Geoportal Dir}\Database Scripts\SQL Server folder

1.3. Run the create_schema_mssql script –or the create_schema_mssql_unicode script as described below - by typing the following:

create_schema_mssql "[database server machine,port]" [Geoportal database name] [Geoportal database user] [Geoportal database user password]

Where:

• [database] is the name of the machine on which SQL Server is installed, or the named SQL Server Instance (e.g. <machinename></machinename>\instance) if applicable, optionally you can include the port # if the SQL server instance is configured to use a fixed port (http://msdn.microsoft.com/en-us/library/ms177440(v=sql.105).aspx)
• [Geoportal] is the name you designate for the Geoportal database
• [Geoportal] is the name of the login and user that will have access to the Geoportal database. This script creates a user if one does not already exist
• [Geoportal] is the password for the login and user of the Geoportal database

Sample Input: create_schema_mssql "mymachine" geoportal geoportal geoportalpwd

Note: To support multi-lingual geoportals, the SQL Server database must be able to support Unicode characters. If your geoportal will be in a language other than English, you should run the create_schema_mssql_unicode script for this step. If not, then use the create_schema_mssql script as shown below. Input parameters are the same for the Unicode version of the script.

After running the script, verify that a new database and tables were created. If the tables were not created, consult the build_schema.log file for any potential errors. The log file can be found in the same folder as the scripts that you ran. The file should not contain any errors, except possibly warnings on the ‘maximum key length’; these you can safely ignore.

• gpt_collection
• gpt_collection_member
• gpt_harvesting_history
• gpt_harvesting_jobs_completed
• gpt_harvesting_jobs_pending
• gpt_resource
• gpt_resource_data
• gpt_search
• gpt_user

Set Up Geoportal Database in PostgreSQL

Setting up a PostgreSQL database for the geoportal consists of two steps – setting up database permissions, and creating the database schema. Each of these steps is accomplished by running a script, found in the {Geoportal Dir}\Database Scripts\PostgreSQL folder:

• A “grants” scripts that sets the user permissions for creating the geoportal schema
• A “create schema” script that creates the table structure, procedures, and triggers and populates tables in the geoportal schema.

1. Setup priviledges for Geoportal user

1.1. Open a command prompt window. Start>Run>cmd

1.2. Change directories to point to {Geoportal Dir}\Database Scripts\PostgreSQL.

1.3. Run the grants_pg.cmd file from the command prompt window as described below:

Usage:

grants_pg [host] [port] [database] [geoportal schema] [userToConnect] [geoportalUser]

Where

• [host] is the machine name hosting PostgreSQL
• [port] is the port number of PostgreSQL. Default = 5432
• [database] is the database name for the geoportal. Default = postgres
• [geoportal] is the name for the geoportal schema. Default=geoportal
• [userToConnect] is the name of the user to connect to the database as. Default=postgres
• [geoportalUser] is the name for the geoportal schema owner. Default=geoportal

Sample Input: grants_proxy_pg localhost 5432 postgres geoportal postgres geoportal

1.4. When prompted with the message “Enter password for new role:”, input the password for the geoportal user.

1.5. When prompted with the message “Enter it again:” input the password for the geoportal user again.

1.6. When the script finishes executing you will be returned back to the command prompt and a text file (grants_pg.txt) will open. Check the grants_pg.txt file for error messages.

Important: You must fix errors appearing in the grants_pg.txt file; do not continue until the script runs without errors.

2. Create Geoportal schema

2.1. Run the create_schema_pg.cmd file from the command prompt window as described below:

Usage:

create_schema_pg [host] [port] [geoportal database] [geoportal user]

Where:

• [host] is the machine name hosting PostgreSQL
• [port] is the port number of PostgreSQL. Default = 5432
• [geoportal] is the geoportal database name. Default = postgres
• [geoportal] is the name for the geoportal schema owner. Default = geoportal

Sample Input: create_schema_pg machineName 5432 postgres geoportal

2.2. When prompted with the message “Enter password for geoportal user:”, input the password for the geoportal user.

2.3. When the script finishes executing you will be returned back to the command prompt and a text file (Geoportal_Schema.txt) will open. Check the Geoportal_Schema.txt file for error messages. Error messages and warnings which state that Table or view does not exist can be ignored. It simply means that the script was trying to delete a nonexistent table.

2.4. Open the PostgreSQL Administrator tool, and verify that a new schema and following tables were as created.

• gpt_collection
• gpt_collection_member
• gpt_harvesting_history
• gpt_harvesting_jobs_completed
• gpt_harvesting_jobs_pending
• gpt_resource
• gpt_resource_data
• gpt_search
• gpt_user

Set Up Geoportal Database in MySQL

Setting up a MySQL database for the geoportal consists of two steps – setting up database permissions, and creating the database schema. Each of these steps is accomplished by running two scripts, found in the \Database Scripts\MySQL folder:

• A “grants” scripts that sets the user permissions for creating the geoportal schema
• A “create schema” script that creates the table structure, procedures, and triggers and populates tables in the geoportal schema.

IMPORTANT: It is assumed that you already have MySQL database software installed and have mapped the MySQL binaries (e.g., \bin) to the PATH environment variable before running the geoportal database scripts.

1. Setup priviledges for Geoportal user

1.1. Open a command prompt window. Start>Run>cmd

1.2. Change directories to point to \Database Scripts\MySQL.

1.3. Run the grants_mysql.cmd file from the command prompt window, as described below.

Usage:

grants_mysql [dbserver] [port] [Geoportal database] [sys username] [sys password] [geoportal username] [geoportal server] [geoportal password]

Where:

• [dbserver] is the machine hosting MySQL
• [port] is the port number of MySQL
• [Geoportal] is the name of the database that will be created and contain the Geoportal schema
• [sys] is the username of the sys user in MySQL
• [sys] is the password of the sys user in MySQL
• [geoportal] is the geoportal user that will be created and will access the geoportal database
• [geoportal] is the name of the geoportal web application server machine
• [geoportal] is the geoportal user password

Sample Input (here, the database and geoportal web app are on machine ‘localhost’): grants_mysql localhost 3306 geoportal root sys geoportal localhost geoportalpwd

1.4. When the script finishes executing you will be returned back to the command prompt and a text file (grants_mysql.txt) will open.

IMPORTANT: Check the grants_mysql.txt file and the command interface for errors. You must fix these errors and rerun the script before continuing to the next step. Note that if you rerun the script after the user was successfully created, you may receive “ERROR 1396 (HY000): Operation CREAT USER failed…” You can ignore this error - see MySQL documentation for explanation http://bugs.mysql.com/bug.php?id=28331 .

2. Create geoportal schema

2.1. Run the create_schema_mysql.cmd file from the command prompt window as described below:

Usage :

create_schema_mysql.cmd [host] [port] [Geoportal database] [geoportal user] [geoportal password]

Where

• [host] is the machine hosting MySQL
• [port] is the port number of MySQL
• [Geoportal] is the database that contains the Geoportal Schema; use the same as in the grants script
• [geoportal] is the geoportal user created in the grants script
• [geoportal] is the geoportal user password

Sample Input: create_schema_mysql localhost 3306 geoportal geoportal geoportalpwd

2.2. When the script finishes executing you will be returned back to the command prompt and a text file (Geoportal_Schema.txt) will open. Check the Geoportal_Schema.txt file for any possible error messages. Error messages and warnings that a table or view does not exist can be ignored as these are checks for existing tables.

2.3. After running the grants and create_schema scripts, verify that a new database and following tables were created.

• gpt_collection
• gpt_collection_member
• gpt_harvesting_history
• gpt_harvesting_jobs_completed
• gpt_harvesting_jobs_pending
• gpt_resource
• gpt_resource_data
• gpt_search
• gpt_user