| | 69 | * The core multi-project query logic is nontrivial. You want to factor it into an SQL function so you don't repeat it in every query. |
| | 70 | * Functions' return types need to be declared. In this case, the return type is a table just like Trac's `ticket` table, but with one extra column |
| | 71 | * We don't want our SQL to be dependent on the format of the `ticket` table, since that could change when you upgrade Trac or even when you install a plugin. |
| | 72 | |
| | 73 | We ended up doing a bit of SQL metaprogramming: we wrote a function called `declare_multitrac_query` that declares the actual multiproject query function. If you call `declare_multitrac_query()` at the beginning of every query, you can then use `multitrac_query(` ''WHERE-condition-as-string'' `)` to get the actual table. Just make sure to add the project column to the beginning of the result. I know this sounds complicated, but it's actually a simple transformation of any of your existing queries. For example, here's what happened to report:1: |
| | 74 | |
| | 75 | {{{ |
| | 76 | #!diff |
| | 77 | --- report1.sql 2007-07-07 22:29:18.876195073 -0400 |
| | 78 | +++ report1a.sql 2007-07-07 22:32:31.104486588 -0400 |
| | 79 | @@ -1,9 +1,12 @@ |
| | 80 | +set search_path to trac_instance_master,public; |
| | 81 | +SELECT declare_multitrac_query(); |
| | 82 | + |
| | 83 | SELECT p.value AS __color__, |
| | 84 | - id AS ticket, summary, component, version, milestone, t.type AS type, |
| | 85 | + project, id AS ticket, summary, component, version, milestone, t.type AS type, |
| | 86 | (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner, |
| | 87 | time AS created, |
| | 88 | changetime AS _changetime, description AS _description, |
| | 89 | reporter AS _reporter |
| | 90 | - FROM ticket t WHERE status <> 'closed' |
| | 91 | + FROM multitrac_query('status <> ''closed''') t |
| | 92 | LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' |
| | 93 | ORDER BY p.value, milestone, t.type, time |
| | 94 | }}} |
| | 95 | |
| | 96 | Notes: |
| | 97 | * '''Line 1''': we set the PostgreSQL schema search path so that `declare_multitrac_query`, which is in the `public` schema, can be found. `trac_instance_master` is the schema I used for the !TracForge master trac instance. |
| | 98 | * '''Line 5''': we prepend the project name to the results |
| | 99 | * '''Line 10''': Notice that the `WHERE` condition has been string-ized. Single quotes are embedded in strings by doubling them. |
