Project Open 3.5.x

Project Open Custom E-Mail Notifications – Part 4

Now that we have the queries to extract the workflow items and the table to track what e-mails we’ve already sent, it’s time to think about the application to actually generate the e-mails. The application should have the following characteristics:

  1. Portability: The application should run wherever Project Open runs. That means at a minimum it needs to run on Linux and Windows.
  2. Ease of maintenance: The application should be easy to maintain. Information that may need to change should be in text files that are easy for a non-programmer to maintain.
  3. Low Impact: The application should have as little an impact on the Project Open server as possible. That means minimal/no reconfiguration of any element on the server.

Here are the options I considered:

  1. Bash-only: I considered writing a solution entirely in Bash. That would run (in theory) under Windows using Cygwin, but that would mean an additional and relatively uncommon installation to maintain. This is nothing against Cygwin; it’s more that I don’t think it’s as widespread as I’d like. That means support could be difficult, and I want this solution to be easy.
  2. Java-only: I’m a Java developer (or so I tell myself), so to me, everything looks like a good candidate for a Java solution. Unfortunately, using Java to access PosgreSQL means I’d a) need to install the JDBC drivers in addition to just installing Java and b) I’d need to adjust the PostgreSQL configuration files to allow access to the Java application. That struck me as something I didn’t really want to do.*
  3. TCL-only: This is probably a good solution, given that Project Open’s native language is TCL. Unfortunately, I don’t know TCL, and I didn’t want to take the time to learn it. After all, I have enough research projects waiting for my attention that if I devoted all of my time to those projects, well, I’d be older than I am now — a lot older. So, I need to conserve my time.
  4. Combination of batch and Java: I settled on a hybrid approach. I’ll use either Bash or BAT files to invoke the PostgreSQL client to export the data into delimited text files, then I’ll use Java to parse those files and create the e-mails. It’s not a perfect solution, but it seems to hit all of my goals while imposing the least overhead.

Of course, I also have to pick a name. PONotificationsBuddy sounded reasonable if mundane. It has the benefit of not being taken, so if I get ambitious and put this on SourgeForge, I should be able to.

My plan is to build an application that will do these things:

  1. Use cron to kick off a small script/batch file that’ll invoke pgsql to run the SELECT statement to extract all open workflow steps.
  2. Pipe those open items (in delimited format) to a file.
  3. From the same cron script/batch file, invoke pgsql again to execute the SELECT statement to pull out all closed items.
  4. Invoke a stand-alone Java Main program and pass it the location of its properties file.
  5. Use Java to walk through the output file containing all open items. For each item, compose and send an e-mail and build a StringBuffer to hold an INSERT statement that’ll eventually mark the e-mails as having been sent.
  6. Use that same Java program to walk through the closed item output file. Create an e-mail and tracking INSERT statement for each closed item.
  7. Pass control back to the calling script/bat file, which will then execute the INSERT statements the Java program just created.

Note: I realize I’m referring to cron, which is (more or less) a Linux thing. On Windows, the tool to use would be the built-in Task Scheduler.

To keep things simple and easy to maintain, I’ll put the queries into separate text files. That way, if I need to tweak them, I just edit the text file. Here’s what the contents of the first query file (stage1.sql) looks like:

\pset format unaligned
\pset fieldsep '~'
\pset footer
\o stage1.output
SELECT a.task_id, a.workflow_key, a.transition_key, c.description,
d.transition_name, acs_object__name(e.user_id) AS thename,, b.object_id AS ticketnumber
FROM wf_tasks a, wf_cases b, wf_workflows c, wf_transitions d, wf_user_tasks e, parties f
WHERE b.case_id = a.case_id
AND c.workflow_key = a.workflow_key
AND d.workflow_key = a.workflow_key
AND d.transition_key = a.transition_key
AND e.task_id = a.task_id
AND f.party_id = e.user_id
AND a.state = 'enabled'
AND a.task_id NOT IN (SELECT pns_task_id FROM pona_notifications_sent)

You should recognize the query from Part 3. The only new part is \pset tuples_only. That command tells PostgreSQL to output just the row data and column delimiters. That’ll make it easier for Java to parse.

Here’re the contents of stage1b.sql, which will extract all closed items:

\pset format unaligned
\pset fieldsep '~'
\pset footer
\o stage1b.output
SELECT a.task_id, a.case_id, b.object_id, b.state, c.description, d.transition_name,
FROM wf_tasks a, wf_cases b, wf_workflows c, wf_transitions d, im_tickets e, parties f
WHERE b.case_id = a.case_id
AND c.workflow_key = a.workflow_key
AND d.workflow_key = a.workflow_key
AND d.transition_key = a.transition_key
AND a.transition_key LIKE '%close%'
AND e.ticket_id = b.object_id
AND f.party_id = e.ticket_customer_contact_id
AND a.task_id NOT IN (SELECT pns_task_id FROM pona_notifications_sent)
ORDER BY task_id DESC(SELECT pns_task_id FROM pona_notifications_sent)

Again, you should recognize the SELECT statement from Part 3.

Finally, here’re the contents of the Bash script called

# PONotificationsBuddy initial script
# Version 1.0.0
# Should be run in the context of the projop user.
# Full environment including PATH should be present.

cd $strHomeDir
echo "`date`: About to run" > $strHomeDir/PONotificationsBuddy.log
psql -f stage1.sql
psql -f stage1b.sql

There’s really nothing fancy here. The script notes the home directory (i.e., the directory into which we’ll install the application), changes into that directory, logs the start, and invokes the two queries using the pgsql command. The script passes that command two parameters: -f tells it what query files to use and -o specifies the output file.

This file could just as easily have been a Windows batch program.

In Part 5, I’ll present the Java code to interpret the two output files (stage1.output and stage1b.output).

* I realize I could have coded the JDBC URL to go to, but I prefer to use DNS names, and that means the JDBC connection would come in on the public IP address. PostgreSQL doesn’t accept incoming connections on even its own public address unless you adjust the configuration files to allow it.

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.