[Patch] Add ability to set a field to the result of a given SQL query

[Jim Harkins]

At first look trac.ini [ticket-workflow] seems very flexible. You can freely create actions and states, but if you want any behaviors like "assign back to resolver", generally you must write Python code. The AdvancedTicketWorkflowPlugin helps a lot. But once you create custom ticket workflow actions and states even that's not enough. There is no way for a plug-in to know about an action you invented.

Still, AdvancedTicketWorkflowPlugin is a useful example of how to add custom ticket workflow operations. But changing the trac.ini file is much more convenient than implementing an plugin which requires:

• editing a .py file
• creating an egg
• installing an egg into the plugins directory
• registering a component in trac.ini
• adding a ticket workflow in trac.ini

And, of course, you need to know at least a little Python.

AdvancedTicketWorkflowPlugin offers a nice additional set of operations, but they are very specific. If it could provide a more generic operation, configured in trac.ini, then the need for custom Python code might be avoided. One generic approach that can handle a lot of cases would be to allow for a workflow operation to specify a field that needs to be modified along with a SQL query to generate the value.

The biggest drawback to allowing generic SQL processing is it may be too powerful for a naive user, who might easily get himself into trouble. The risk is offset by two things:

• The power of SQL can be used to implement many custom workflow features
• Workflow plugins apparently operate inside a transaction that's always rolled back

Attempts to modify the database do not persist.

I have implemented this feature, working around a few problems along the way. Please find a patch (to rev 7774) attached.

Here is a first cut at the documentation updates for this feature:

Currently provided operations are:

• set_field_to_sql

Documentation

• set_field_to_sql (TicketWorkflowOpFieldSql)

Sets the value of a ticket field to the results of executing a SQL statement. Only the first record returned is used so order is important. And using SQL to directly modify the database is not supported.

Here’s an example that is equivalent to the built-in del_owner operation:

<someaction> = somestatus -> *
<someaction>.operations = set_field_to_sql
<someaction>.set_field_owner = SELECT ''

The set_field* attribute(s) of the action specify the field to modify and the SQL statement to execute. The field name is parsed from the attribute itself, as the characters after the second "_". This allows for a convenient way for a single action to set multiple fields. All ticket fields are available, including any custom fields.

Of course, the built-in operations should be preferred because they are clearer and have custom text for the hints which are displayed with the action. For illustration only, here are how several operations could be implemented with set_field_to_sql.

del_resolution

<someaction> = somestatus -> *
<someaction>.operations = set_field_to_sql
<someaction>.set_field_resolution = SELECT ''

set_owner_to_self

<someaction> = somestatus -> *
<someaction>.operations = set_field_to_sql
<someaction>.set_field_owner = SELECT % (self)s

Here "% (self)s" is a reference to the special value "self". The space between the percent and the open parenthesis is required by the configuration parser used by trac. If you forget the space, trac will raise an exception trying to read the trac.ini file. The trac team is looking into switching to a better configuration parser, so in the future, the space may not be required (but at that time the single "%" may need to be replaced with "%%").

All fields, including custom fields, are accessible using "% (field)s", and the ticket number special value can be accessed using "% (id)s".

Some of this plugin's other operations could also be implemented using set_field_to_sql.

set_owner_to_reporter

<someaction> = somestatus -> *
<someaction>.operations = set_field_to_sql
<someaction>.set_field_owner = SELECT % (reporter)s

This could be easily modified to allow any ticket field to be set to the value of any other ticket field.

set_owner_to_previous

<someaction> = somestatus -> *
<someaction>.operations = set_field_to_sql
<someaction>.set_field_owner = SELECT oldvalue FROM ticket_change
    WHERE ticket=% (id)s
        AND field='owner'
    ORDER BY -time

This provides a glimpse of the power of SQL. Also note the formatting. Trac treats lines that begin with whitespace as continuation lines, so a long SQL statement can be formatted nicely. The config parser preserves newlines but removes leading spaces on the continuation lines, which should not cause any problem for SQL.

Multiple fields can be set:

<someaction> = somestatus -> *
<someaction>.operations = set_field_to_sql
<someaction>.set_field_owner = SELECT ''
<someaction>.set_field_resolution = SELECT ''

Trac does not maintain any order to the values it reads from trac.ini. The order in which the set_field* properties will be processed is not guaranteed. If for some reason you need to force a specific ordering, you should number the fields like this:

<someaction> = somestatus -> *
<someaction>.operations = set_field_to_sql
<someaction>.set_field1_owner = SELECT ''
<someaction>.set_field2_resolution = SELECT ''

The fields are sorted in string order so in the unlikely case where you have more than 9 fields (or more than 10 if you start your numbering at 0), remember to include leading 0's to pad all the field numbers to the same length.

[Jim Harkins]

Patch to implement set_field_to_sql operation

[Jim Harkins]

Added missing [Patch] tag to Summary.

[anonymous]

Where the documentation says:

The order in which the set_field* properties will be processed is not guaranteed.

It would be better (correct) to say:

set_field* properties are sorted by attribute (e.g set_field_owner) then executed in the resulting order.

[Jim Harkins]

I forgot to login before leaving this comment. Sorry for the duplication.

Where the documentation says:

The order in which the set_field* properties will be processed is not guaranteed.

It would be better (correct) to say:

set_field* properties are sorted by attribute (e.g set_field_owner) then executed in the resulting order.

[Eli Carter]

While I agree that this is rather powerful, I don't particularly care for the approach of embedding sql into the .ini. I do agree that the current situation of writing a new mini plugin for each set_<blah>_to_<foo> operation stinks, and do want to address that. Just... not this way.

I suggest that you create a new plugin here on trac-hacks for this sql operation. (Feel free to use or adapt my TicketWorkflowOpBase class in your plugin, no strings attached.)

[Eli Carter]

(I should probably also mention that part of my delay in responding to this was seeing if the idea would "grow on me" with time. After coming back to it multiple times... it hadn't.)

[Jim Harkins]

Thanks for the feedback and encouragement. When I get some time, I'll probably create a new plugin called SqlWorkflow.

Jim