Project Open 3.5.x

Project Open Custom E-Mail Notifications – Part 6

I designed PONotificationsBuddy to install with a minimal impact on the Project Open server. Because of that, you won’t need to adjust Project Open’s configuration files, AOL Server’s configuration files, or PostgreSQL’s configurations files to make PONotificationsBuddy work. However, you will need to install Java.

I recommend installing the latest version from Oracle (it still seems weird to say Oracle — I’m so used to saying Sun!). The Standard Edition JDK will be enough. Every operating system has its own quirks, but in general, you should follow these steps to install Java and PONotificationsBuddy:

  1. Download source: Download the installation binary for the Java SE JDK from Oracle.
  2. Setup the environment: Set the environment variables so Java knows where to find its binaries.
  3. Test Java: Make sure Java’s installed correctly.
  4. Install PONotificationsBuddy: Install the PONotificationsBuddy binaries and configuration files.
  5. Customize PONotificationsBuddy: Tweak PONotificationsBuddy’s properties files for your environment.

Download Source

There are enough instructions on the Oracle site and around the web to guide you through this step. Just for point of reference, though, I’m using Linux, and I install Java into the /usr directory (for example, /usr/jdk1.6.0_22). Then, I create a symbolic link caled /usr/java that points to the real directory. I point JAVA_HOME to /usr/java and add /usr/java/bin to the PATH (both are described below). That way, I can have multiple versions of Java installed and switch quickly and easily between them without impacting the environmental variables (see below).

Setup the Environment

Java needs two main environmental variables to work correctly:

  1. Home: I set JAVA_HOME to /usr/java; of course, you should set it to whatever matches your environment.
  2. Path: I add /usr/java/bin to my PATH statement. Again, you should match it to your environment.

Under my CentOS instalation, I added JAVA_HOME and my PATH addition to /etc/profile. I think SUSE uses /etc/profile.local. Your OS may vary.

Test Java

I like to test installations at logical steps to isolate possible failures, and post Java install seems like a logical step to me. You can make sure Java’s installed okay with this command:

java -version

It should return something that looks like this:

java version "1.6.0_22"
Java(TM) SE Runtime Environment (build 1.6.0_22-b04)
Java HotSpot(TM) Client VM (build 17.1-b03, mixed mode, sharing)

Of course, your exact version may vary. The important point is that Java responds with something other than this:

-bash: java: command not found

That error means that either Java’s not installed correctly or that the PATH is not setup right.

Install PONotificationsBuddy

The previous installment in this series included a link to PONotificationsBuddy installation source. You can also download it from its project site on SourceForge. In either event, I installed them to this directory on my CentOS Linux box:


I installed it under Project Open’s filestorage directory tree so that any Project Open backup jobs I have will also catch the notification assistant.

Note that you should make sure that the Project Open user ID (projop under Linux) owns the files. For example, if you installed these files as root under CentOS Linux, you could change the ownership to the Project Open ID with this command:

chown projop:projop /web/projop/filestorage/apps/PONotificationsBuddy/*

You could use Windows Explorer to do the same thing under Windows.

Customize PONotificationsBuddy

There is one minor change that I decided to make (for reasons I discussed earlier) to Project Open’s PostgreSQL schema itself: I added a small table. You can do that by running a script I included in the binary and source code distributions. You need to run the script in the context of the Project Open user. For example, to do that under CentOS Linux, you could:

  1. Log into a terminal session as a low power ID (like tcrow).
  2. Become root: su -l
  3. Become the Project Open user: su -l projop
  4. Change into the installation directory: cd /web/projop/filestorage/apps/PONotificationsBuddy
  5. Run the script: psql -f pona_notifications_sent.sql

After that, all you need to do is set the properties, as described below.

Most of the properties you should review are self-explanatory. Just in case what I think is self-explanatory isn’t, here are the properties that PONotificationsBuddy uses. They are stored in two separate files:

This file contains most of the general properties that define how PONotificationsBuddy behaves. Here’s a detailed discussion of each property:

  1. e_mail_from: When PONotificationsBuddy sends an e-mail, it’ll include a “from” e-mail address. This property defines it. It defaults to poadmins@interstell.home, which probably won’t work in your environment.
  2. e_mail_host: This property refers to the SMTP server in your environment. In mine, it’s ohcmhsrv026.interstell.home; yours might be with Road Runner, another ISP, or your own company. If you don’t know, you should check with your IT team. Note that they may have to configure their SMTP server to accept traffic from your server if they use filters to stop spam. For example, if they use a white list to determine which hosts can send mail, they will need to add your Project Open’s server’s hostname.
  3. e_mail_subject: For new workflow/step notifications, PONotificationsBuddy gets the e-mail subject from this property. The default, “Ticket Waiting in Project Open”, might work in your environment.
  4. open_items_file: If you remember the previous discussions, you might recall that PONotificationsBuddy externalizes its queries. This property defines what file to use as the source for the new workflow step query. By default, it is /web/projop/filestorage/apps/PONotificationsBuddy/stage1.output, which should work in your environment if you’re running Linux.
  5. closed_items_file: The program also externalizes the file containing the closed item query. The default, /web/projop/filestorage/apps/PONotificationsBuddy/stage1b.output, should work just fine under Linux.
  6. update_file: Neither the binary nor the source distribution will contain this file. PONotificationsBuddy will generate the file as it runs. The PostgreSQL statements in this file will record the task identifiers of the items for which it sent e-mail. Those items will be inserted into the table we created in PostgreSQL (the table’s called pona_notifications_sent). The default, which should work find for Linux, is /web/projop/filestorage/apps/PONotificationsBuddy/stage2.sql.
  7. column_delimiter: The output from the queries that extract the workflow step and the closed items goes into a file, and our Java application parses it. To know how to parse the file, Java needs to know what character separates the columns. I chose the tilde (~) because it’s seldom otherwise used. The two queries files (stage1.sql and stage1b.sql) tell PostreSQL what delimiter to put into the file, and this property tells Java what property to use for parsing. Obviously, they need to match.
  8. po_server_host: The open workflow step e-mails, closed e-mails, and spectator e-mails include a link to the ticket. PONotificationsBuddy uses this property to define the server on which Project Open is running. The default,http://ohcmhsrv020.interstell.home:8000, works for me; it will probably not work for you. At least, I hope it doesn’t, because if it does, I need to check my network security (or wonder why you’re copying my development domain name and host names).
  9. e_mail_closed_subject: This property represents the subject line for the e-mails announcing a closed item. The default, “Ticket Completed in Project Open”, might work fine for you.
  10. spectator_properties_file: In order to keep the impact on PostgreSQL and Project Open to a minimum, PONotificationsBuddy externalizes the definitions for what workflow name and step might have a spectator e-mail associated with it. This file is probably called (at least it is unless you change it here), and the default is /web/projop/filestorage/apps/PONotificationsBuddy/ Of course, you’ll probably have to change it if you’re running Windows.

This file is like a flat representation of a PostgreSQL table. Each line represents a combination of workflow name plus individual workflow step name that includes a spectator. The line then goes on to define the e-mail’s subject, its body, and the target e-mail addresses (separated by commas).

The file that comes with PONotificationsBuddy includes this helpful comment that describes the format of the line:

<workflow>~<step>~<Subject>~<Body>~<Comma-delimited recepients>

You can get the workflow name from Project Open. Follow these steps:

  1. Log into Project Open as a PO Administrator.
  2. Click on the Admin tab.
  3. Click on the Workflow URL.
  4. Find the workflow in question. Look under its friendly name for the system name; the system name is what goes into For example, for the tutorial I presented on this site (you can see part 1 here), I created a workflow named “Sample 001.” Its system name was “sample_001_wf”.
  5. Now, there might be a better way to do this, but I couldn’t find an easy way via the UI. I had to use a query to find the step names. You can use the query below to get the names of the workflow steps. Ordinarily, the name will be the friendly description in lower case with spaces replaced by underlines (for example, Assign Ticket becomes assign_ticket). However, if you change the friendly name, it won’t change the key, so I think it’s best to use the query.

This query should show you all of the workflow Tasks associated with a workflow:

Note: Of course, you should substitute your workflow’s name for “sample_001_wf.”

FROM wf_transitions
WHERE workflow_key = 'sample_001_wf'

The column transition_key will hold the value you want for the step name.

Here’s the example line from that ships with the binary code:

sample_001_wf~assign_ticket~Sample Spectator E-Mail~An example of the simple workflow’s ticket assignment step is waiting for pickup.~myboss@interstell.home

You should be able to enter as many spectator events as you like. Try to enter only one workflow name/step combination, because if you don’t, PONotificationsBuddy will ignore the second one (it’ll go with the first match it finds).

Once you adjust the property files, you can setup a cron job that’ll run PONotificationsBuddy for you (or a Windows Scheduled Task). If you run it under Linux, you should be sure to load the environment and run the job in the context of the Project Open user (projop). For example, my cron job looks like:

*/5 * * * * su -l projop -c “/web/projop/filestorage/apps/PONotificationsBuddy/”

This runs PONotificationsBuddy every 5 minutes. You can adjust the timing to suit your needs.

At this point, you should have a fully operational installation of PONotificationsBuddy. If for some reason it doesn’t work, stop by and post a question at the SourceForge discussion forum for the project.

Terrance A. Crow is the Senior Security Engineer at a global library services company. He holds a CISSP and has been writing applications since the days of dBASE III and Lotus 1-2-3 2.01.