glc.nwl at gmail
Oct 29, 2009, 2:36 PM
Post #1 of 2
(66 views)
Permalink
|
|
TIP : Use a single report for tickets by one user, all users or current user
|
|
Hi,
While passing a URL variable (like /trac/report/1?MYVAR=john_doe) to a
report, you can use $MYVAR on the SQL code of your report to show
john_doe 's tickets.
But obviously, the SQL code of your report would be different to
display exactly the same kind information for :
- all users,
- or for the current user (whoever is),
thus making necessary to duplicate the report for those 3 cases :-(
Here is a tip for displaying (on a same report) tickets owned or
reported by one user, all users or the current user, hope this helps,
this tip could figure in some other places, like a blog or other site
dedicated to Trac, but I don't have a blog at the moment and didn't
find any relevant site that could show this post. Fell free to paste
it somewhere else.
The 2 methods described below works at least with MySQL (didn't tested
to check if it's standard SQL compatible with most database servers,
but I think it is), I'll use examples for ticket owners but this also
works for ticket reporters.
Let's say we want to use report {1} to show open tickets by passing 3
kind of values :
http://....../trac/report/1?BY=john_doe => Display open
tickets for john_doe user
http://....../trac/report/1?BY=ALL => Display open
tickets for all users
http://....../trac/report/1?BY=USER => Display open
tickets for the current user
The problem is that Trac replaces your custom URL vars by your value
WITH SINGLE QUOTES, in other terms, the expression $BY will be
replaced in your SQL by 'john_doe' instead of just john_doe, so that
you cannot do what you want so easily in your SQL code.
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
METHOD 1 (using REGEXP and CONCAT functions) :
You can first use the following SQL code in your report to solve the
problem :
-------------------------------------------------------------------
SELECT FROM ......
WHERE status <> 'closed'
AND (
owner REGEXP REPLACE(lower(CONCAT('^',$BY,'$')), 'ALL', '.*')
OR
owner REGEXP REPLACE(lower(CONCAT('^',$BY,'$')), 'USER', CONCAT
('^',$USER,'$'))
)
AND owner <> ''
-------------------------------------------------------------------
This example works fine, generating a regular expression by
concatenation and replacement, you can now specify ?BY=ALL or ?BY=USER
or BY=john_doe in your URL
But as you can see, the SQL code is a bit complicate and quite large,
not optimized, especially if we want to duplicate the same conditions
for owners AND / OR reporters.
Furthermore, if you pass the name of a specific user like john_doe in
URL, it will generate the following SQL code with the same condition
twice :
AND (
owner REGEXP '^john_doe$'
OR
owner REGEXP '^john_doe$'
)
These 2 problems are solved in this second example, declaring a
function that will do the job in your database :
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
METHOD 2 (using custom function) :
1. First access your MySql database (or try with a DB server other
than MySQL) in console or terminal mode and enter in the database of
your trac project.
2. Paste and run the following code to declare a new function in your
database :
# -------------------------------------------------
DROP FUNCTION IF EXISTS get_trac_user_regexp;
DELIMITER //
CREATE FUNCTION get_trac_user_regexp(user BLOB,TRAC_USER_VAR BLOB)
RETURNS BLOB
BEGIN
SET user=LOWER(user);
IF (user='ALL') THEN RETURN '.*';
ELSEIF (user='USER') THEN RETURN CONCAT('^',TRAC_USER_VAR,'$');
ELSE RETURN CONCAT('^',user,'$');
END IF;
END
//
# -------------------------------------------------
(Don't forget to run this code in each database if you have multiple
Trac projects)
3. Now you can use your function in the SQL code of your Trac reports,
using some code like :
SELECT ....
WHERE status <> 'closed'
AND owner REGEXP get_trac_user_regexp($BY,$USER)
In the code above, $BY variable refers to a ?BY= variable in URL to
specify the users you want, just change it in your report if you want
to use another var in URL, for example, if you want to use something
like :
/report/1?MYOWVAR=john_doe
Use :
AND owner REGEXP get_trac_user_regexp($MYOWVAR,$USER)
The $USER variable (passed a a second parameter) refers to Trac $USER
reserved variable, that will be automatically replaced in your SQL by
the login name of the user showing the report, so you DON'T need to
pass it in your URL.
(see :
http://trac.edgewall.org/wiki/TracReports#SpecialConstantVariables
for more details on this variable)
NOTE :
* Values for BY=john_doe or any specific user is case INsensitive
(you can write BY=JOHN_DOE, it will show the results)
* while ALL and USER values are case SENSITIVE, altough I convert
these values to lower case in the SQL function, there's an issue I
didn't solved, so :
BY=all
BY=user
in your URL will NOT work (use always BY=ALL and BY=USER in upper
case)
Now you can still use your URL vars in your wiki pages or your browser
bookmarks ;-)
Bookmarks (examples) :
http://tracproject.domain.com/report/1?BY=john_doe
http://tracproject.domain.com/report/1?BY=ALL
http://tracproject.domain.com/report/1?BY=USER
or wiki pages :
[report:1?BY=USER View my open tickets]
This is usefull, especially when you paste this same kind of wiki
links in the description field of all your reports, where
WikiFormatting is also supported, so that you can make a kind of
custom nav bar to navigate from one report to another (especially
using the Include macro, see http://trac-hacks.org/wiki/IncludeMacro
for more details), in this case you create a wiki page (i.e. named
ReportLinks) that just contains all your report links, like :
[report:1?BY=USER My open tickets] | [report:2?BY=USER My closed
tickets]
and then implement :
[[Include(ReportLinks)]]
in each description of all your reports.
(better when using html code on the included wiki page that contains
all the links, for a better looking nav bar with small font links
within a table with hidden borders)
Good luck ;-)
Ghislain LE COZ
Web developer
glc.nwl[at]gmail.com
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Trac Users" group.
To post to this group, send email to trac-users[at]googlegroups.com
To unsubscribe from this group, send email to trac-users+unsubscribe[at]googlegroups.com
For more options, visit this group at http://groups.google.com/group/trac-users?hl=en
-~----------~----~----~----~------~----~------~--~---
|
