# Set up PostgreSQL to Store Tracking Information

This section describes how to set up the PostgreSQL back end.

The example actions and commands are for IDOL components running on a Microsoft Windows platform, and a PostgreSQL server running on a Linux platform. Other combinations of platforms are possible.

## Install the SQL Database

The following Linux command example installs the latest stable PostgreSQL server, using the default port (`5432`). You can use any recent, stable version (on any port).

    sudo apt-get install postgresql

You can test that your server is up with `psql`.

For Windows, you can install PostgreSQL from the following Web site:

http://www.postgresql.org/download/windows/

For more detailed installation instructions, refer to the PostgreSQL wiki.

https://wiki.postgresql.org/wiki/Main_Page

## Set up the Database and Table

This section describes how to set up the database in PostgreSQL on Linux. On Windows, you can complete the tasks by using `Pgadmin`.

The PostgreSQL installation creates a user for you.

To set up the database and table on Linux

1. Create a database with an arbitrary name. For example:

       sudo -u postgres createdb mydoctrackdb

   You can test it by using the following example command:

       sudo -u postgres psql -d mydoctrackdb

2. Create the tables to store document tracking events, by running the following commands from `psql` or your SQL client:

       CREATE TABLE type(
         typeid serial PRIMARY KEY,
         type varchar(64) NOT NULL UNIQUE,
         is_error smallint,
         is_terminal smallint
       );

       -- Broken into several INSERTs here for clarity only
       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(0,'Unknown',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(10,'Committed',0,1),
                      (20,'Deleted',0,1),
                      (30,'Indexed',0,0),
                      (40,'Received',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(50,'Updated',0,1),
                      (-10,'Warning',1,0),
                      (-20,'Error',1,1),
                      (-30,'Rejected',1,1);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(90,'Added',0,0),
                      (100,'Delete received.',0,0),
                      (110,'Update received.',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(120,'Non-importing add received.',0,0),
                      (130,'Import:Queue',0,0),
                      (140,'Import:Importing',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES(150,'Import:Pre',0,0),
                      (160,'Import:Post',0,0),
                      (170,'Import:Finished',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (180,'Import:Cancel',0,1);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (190,'Import:Extracting metadata',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (200,'Import:Extracting metadata finished',0,0);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (210,'Import:ExtractMetaAbort',1,1);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (220,'Import:Abort',1,1);

       INSERT INTO type(typeid, type, is_error, is_terminal)
                VALUES (230,'Replaced',0,0);

       CREATE TABLE source(
         sourceid serial PRIMARY KEY,
         source varchar(128) NOT NULL UNIQUE
       );

       CREATE TABLE event(
         eventid serial PRIMARY KEY,
         docuid varchar(128) NOT NULL,
         typeid int NOT NULL,
         sourceid int NOT NULL,
         timestamp bigint NOT NULL,

         CONSTRAINT type_fk FOREIGN KEY(typeid)
            REFERENCES type(typeid)
            ON DELETE CASCADE ON UPDATE CASCADE,

         CONSTRAINT source_fk FOREIGN KEY(sourceid)
            REFERENCES source(sourceid)
            ON DELETE CASCADE ON UPDATE CASCADE
       );

       CREATE TABLE metadata(
         metadataid serial PRIMARY KEY,
         key varchar(32) NOT NULL,
         value varchar(1024) NOT NULL
       );

       CREATE TABLE event_metadata(
         eventid int NOT NULL,
         metadataid int NOT NULL,

         CONSTRAINT event_fk FOREIGN KEY(eventid)
                       REFERENCES event(eventid)
                       ON DELETE CASCADE ON UPDATE CASCADE,

         CONSTRAINT metadata_fk FOREIGN KEY(metadataid)
            REFERENCES metadata(metadataid)
            ON DELETE CASCADE ON UPDATE CASCADE
       );

       CREATE TABLE docuid_reference(
         docuid varchar(128) NOT NULL,
         ref varchar(4096) NOT NULL,

         UNIQUE (docuid, ref)
       );

       CREATE TABLE doctrack_schema_version(
         key varchar(64) NOT NULL,
         value varchar(128) NOT NULL
       );

       INSERT INTO doctrack_schema_version(key,value)
            VALUES('major_version', '1'),
                  ('minor_version','1'),
                  ('IDOL_version','10.9');

       CREATE RULE no_schema_version_insert AS
         ON INSERT TO doctrack_schema_version
         DO INSTEAD NOTHING;

       CREATE RULE no_schema_version_delete AS
         ON DELETE TO doctrack_schema_version
         DO INSTEAD NOTHING;

NOTE: If you want to set up document tracking in an existing IDOL installation that uses the deprecated IndexTasks component, you must also add the following statements for the `type` table:

    INSERT INTO type(typeid, type, is_error, is_terminal)
             VALUES(60,'IndexTasksCompleted',0,0),
                   (70,'IndexTasksStarted',0,0),
                   (80,'IndexTask',0,0);

To set up the database and table on Windows

1. Use `Pgadmin` to run the SQL Create Table command. Right-click databases/mydoctrackdb (or the name of the database that you created) and select Create script.

## Database Access Permissions

On Windows, you can modify the configuration in `Pgadmin` by selecting the appropriate item on the left pane, and then clicking Tools/Server Configuration.

To modify the database access permissions

1. Find the `pg_hba.conf` host-based authentication file by inspecting the PostgreSQL configuration file. The following lines in the `postgresql.conf` file identify the location:

       hba_file = '/etc/postgresql/9.1/main/pg_hba.conf'  # host-based authentication file

   NOTE: The location of the `postgresql.conf` file can vary, depending on your version and operating system. On Linux, you can run the following command to find the path to the configuration file:

       ps -ef | grep postgres

2. Modify the `pg_hba.conf` configuration file, which you located in Step 1, to allow your IDOL components to access the database. Find the following section, and add appropriate lines for your client IP addresses.

       # TYPE  DATABASE    USER           ADDRESS             METHOD
       host    all         my.user.name   10.2.123.123/32     trust

   For simplicity, you can set the `USER` field to `all`. Micro Focus recommends that you use a secure `METHOD`, such as `md5`, after you have tested the system.

3. Allow PostgreSQL to accept connections. In the `postgresql.conf` file, find the `listen_addresses` parameter and uncomment or modify it:

       listen_addresses = '*'

4. Save and close the `pg_hba.conf` and `postgresql.conf` files.

5. Restart PostgreSQL, by using the following command (on Linux):

       sudo -u postgres /etc/init.d/postgresql restart

_FT_HTML5_bannerTitle.htm