Project Summary
Automator provides an excellent foundation for non-programmer level automation. To use PostgreSQL from that today requires using shell scripts, which not many non-developers are comfortable with. The goal here is to provide an interface for doing this without the need to use shell scripts. These actions are a part of the Druware Database Automator Action Pack which is due to be released late in 2007. For more information, review the product pages.
Project Details
There are several actions that will be need to be written to make this an effective tool that can be used to generate effective output workflows. Some of these are generic in nature, while others are very specific to PostgreSQL. The needed actions fall into 4 categories; Administration, Data Gathering, Flow Control and Output.
Administration
The administration actions are designed to be run on the server itself, and can be scheduled to run via either launchd or iCal if you use an interactive session on your server. These actions are Backup Database and Vacuum Database. Each of these actions should be one step actions, with the each of them taking a parameter in the form of a string representing the database.
Data Gathering
Like administration, there are just two actions, however these are meant to be run anywhere, and should embed the pgCocoaDB.framework so that they may be easily deployed. Run Static Query and Run Dynamic Query are both actions for gathering data, and returning them to the next action as an array of recordset dictionaries.
Flow Control
In order to make truly useful workflows, it will be necessary provide some flow control options that do not currently exist in Automator. While not specific to PostgreSQL, these are important actions for data operations. Basic flow-control sets of For-Each, If-Then, and While need to be implemented. Each set would take an array of dictionaries, and provide user editable values for the variables and value options. In each case, the action would then call an external workflow passing in the dictionary at hand from the action.
Output
These actions are designed to provide methods for taking the results of a query and putting them out in a format that is readily consumable. There are three common formats that should be supported, each by it's own action: Save Results to CSV, Save Results to XML, Save Results to Text. These actions take the array of dictionaries and save them to a folder as <name>-<datetime>.<format> and pass the file references to the next action in the workflow.