Set up the Geoportal Database

Note: Page in progress!

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] [geoportal]

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

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.

1.1 Open a command window. Start>Run>cmd

1.2 Change the directory to the \Database Scripts\SQL Server folder

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

create_schema_mssql [database server machine] [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
• [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

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