Sharing custom queries

44 views
Skip to first unread message

Mo

unread,
Jun 25, 2015, 10:20:24 AM6/25/15
to trac-...@googlegroups.com
Hi,

is it possible to share custom queries or reports with users, user groups or globally? From the users perspective that would mean to have an overlay of inherited queries and user-owned custom queries.

Best regards.

RjOllos

unread,
Jun 26, 2015, 12:00:28 AM6/26/15
to trac-...@googlegroups.com
On Thursday, June 25, 2015 at 7:20:24 AM UTC-7, Mo wrote:
Hi,

is it possible to share custom queries or reports with users, user groups or globally? From the users perspective that would mean to have an overlay of inherited queries and user-owned custom queries.

From what I can see the saved custom queries are available to all users. You could restrict access to certain reports (saved queries) using TracFineGrainedPermissions (1). See comments about that (2).

Mo

unread,
Jun 30, 2015, 3:10:48 AM6/30/15
to trac-...@googlegroups.com


Am Freitag, 26. Juni 2015 06:00:28 UTC+2 schrieb RjOllos:
On Thursday, June 25, 2015 at 7:20:24 AM UTC-7, Mo wrote:

is it possible to share custom queries or reports with users, user groups or globally? From the users perspective that would mean to have an overlay of inherited queries and user-owned custom queries.

From what I can see the saved custom queries are available to all users.

So did I get this right?
Users with REPORT_ADMIN permission  can create reports and all these reports are available to all users?
Where can I see the default set of reports that are available after plain install, or can those be modified and deleted just like any other report?

What is the difference between reports and custom queries? Or can any query be created from a query dialog (listboxes, editboxes...) OR from SQL commands (with the higher SQL flexibility)?

Best regards.

RjOllos

unread,
Jun 30, 2015, 9:37:46 AM6/30/15
to trac-...@googlegroups.com
On Tuesday, June 30, 2015 at 12:10:48 AM UTC-7, Mo wrote:
So did I get this right?
Users with REPORT_ADMIN permission  can create reports and all these reports are available to all users?

Yes, that should be the case. REPORT_CREATE should be sufficient for creating reports.
 
Where can I see the default set of reports that are available after plain install, or can those be modified and deleted just like any other report?

Reports 1 - 8, or 1 - 9 (I can't remember the exact number) are the default reports. They can be modified and deleted like any other report.
 
What is the difference between reports and custom queries? Or can any query be created from a query dialog (listboxes, editboxes...) OR from SQL commands (with the higher SQL flexibility)?

Right, a report can contain SQL or query syntax. Try saving a query as a report and then edit the report for that query. You will see that the text box that normally contains SQL instead contains a query string.

SQL has the downside that it may not be portable across databases. This is a concern if you are, for instance, on SQLite now and later move to MySQL and PostgreSQL, in which case your reports may need to be modified when you migrate. It's also a concern because some SQL for reports that users have posted, for instance to this mailing list, may be specific to a database. So beware when copying and pasting.

You should see a //Save query// button on the Query page.

- Ryan

Mo

unread,
Nov 20, 2015, 5:39:18 AM11/20/15
to Trac Users
Am Freitag, 26. Juni 2015 06:00:28 UTC+2 schrieb RjOllos:

is it possible to share custom queries or reports with users, user groups or globally? From the users perspective that would mean to have an overlay of inherited queries and user-owned custom queries.

From what I can see the saved custom queries are available to all users. You could restrict access to certain reports (saved queries) using TracFineGrainedPermissions (1). See comments about that (2).


Hi, taking this topic up again... are there plans to rework the whole custom query section? Currently it is not very flexible, if I don't miss some things:

What is the difference between custom queries and reports? AFAIK queries can be created with the gui while reports are plain SQL? But sometimes editing a custom query I end up with SQL as well. And the "View tickets" view shows reports and queries without notes which entry is a query or a report.

Is it possible to clone a query/report? I tried creating a new report and copy the SQL from another report, but even then I need to adapt report= which feels very lowlevel.

According to the initial question there is no difference between user specific custom queries and shared queries. Currently all queries that are created by report admins are visible to all.
Most users coming from Bugzilla like to have their own queries for their own projects. Then creating all available queries for all projects globally will increase the list a lot. Then having fixed ids in the report list will lead to wholes after removing reports.

Any idea about the future on that topic?
Best regards,
Mo

RjOllos

unread,
Dec 2, 2015, 12:14:03 AM12/2/15
to Trac Users


On Friday, November 20, 2015 at 2:39:18 AM UTC-8, Mo wrote:
Am Freitag, 26. Juni 2015 06:00:28 UTC+2 schrieb RjOllos:

is it possible to share custom queries or reports with users, user groups or globally? From the users perspective that would mean to have an overlay of inherited queries and user-owned custom queries.

From what I can see the saved custom queries are available to all users. You could restrict access to certain reports (saved queries) using TracFineGrainedPermissions (1). See comments about that (2).


Hi, taking this topic up again... are there plans to rework the whole custom query section? Currently it is not very flexible, if I don't miss some things:

No plans that I know of. You may find it discussed in some tickets, but they aren't currently scheduled.
 
What is the difference between custom queries and reports? AFAIK queries can be created with the gui while reports are plain SQL? But sometimes editing a custom query I end up with SQL as well. And the "View tickets" view shows reports and queries without notes which entry is a query or a report.

Custom queries have a special syntax that is portable across database backends. The main downside to reports containing SQL is that it is difficult to make them portable across SQLite, PostgreSQL and MySQL.

Saving a custom query will put query syntax in the report SQL box, which you might be confusing with having SQL.

The other advantage to custom queries is that a saved custom query can be further refined by the user. Custom queries are preferred. It is recommended to only use reports if you can't accomplish your aims with custom queries.
 
Is it possible to clone a query/report? I tried creating a new report and copy the SQL from another report, but even then I need to adapt report= which feels very lowlevel.

On the page for any report/custom-query you should find a //Copy Report// button. It should be present for any users with REPORT_CREATE.
 
According to the initial question there is no difference between user specific custom queries and shared queries. Currently all queries that are created by report admins are visible to all.
Most users coming from Bugzilla like to have their own queries for their own projects. Then creating all available queries for all projects globally will increase the list a lot. Then having fixed ids in the report list will lead to wholes after removing reports.

I agree it is a valuable feature, however we are really lacking contributors these days and the few of us that are working on the projects can't implement every nice-to-have feature in the limited time we have available. I'd like to get more people involved in the project, but that is difficult even someone commits to working some patches. Hopefully things will pickup in the coming year.


- Ryan

Roger Oberholtzer

unread,
Dec 2, 2015, 1:54:41 AM12/2/15
to trac-...@googlegroups.com
Perhaps slightly off topic, but related to your comment about not
having the queries in SQL:

I have a trac site that started life with SQL search queries. As I add
new queries, they are in a more TracQuery language. But the old
original queries stay as SQL. I would like to have all the queries in
the non-SQL syntax. What is the easiest way to do this? Editing the
search seems not to do this. Do I have to delete the query and then
add it again new?

My queries are all a mess. I really need to do a bit of housekeeping.
Maybe I could dump all the defined queries somehow, edit this, and
then re-import the queries?
> --
> You received this message because you are subscribed to the Google Groups
> "Trac Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to trac-users+...@googlegroups.com.
> To post to this group, send email to trac-...@googlegroups.com.
> Visit this group at http://groups.google.com/group/trac-users.
> For more options, visit https://groups.google.com/d/optout.



--
Roger Oberholtzer

RjOllos

unread,
Dec 2, 2015, 8:52:14 AM12/2/15
to Trac Users
On Tuesday, December 1, 2015 at 10:54:41 PM UTC-8, Roger Oberholtzer wrote:
Perhaps slightly off topic, but related to your comment about not
having the queries in SQL:

I have a trac site that started life with SQL search queries. As I add
new queries, they are in a more TracQuery language. But the old
original queries stay as SQL. I would like to have all the queries in
the non-SQL syntax. What is the easiest way to do this? Editing the
search seems not to do this. Do I have to delete the query and then
add it again new?

My queries are all a mess. I really need to do a bit of housekeeping.
Maybe I could dump all the defined queries somehow, edit this, and
then re-import the queries?

By "SQL search queries", are you referring to reports/save queries, or something else entirely, such as a feature provided by a plugin? I'm unsure what you mean by "editing the search".

Roger Oberholtzer

unread,
Dec 2, 2015, 1:20:15 PM12/2/15
to trac-...@googlegroups.com
Yes. Reports. They are a mix of SQL and TracQuery. I want to move them
all to TracQuery.

But what I would like to do is get them somewhere where I can clean
them up, reorder them, and all that fun housekeeping.And then reload
all reports into a fresh table.

Doing this in the web interface would be tedious.

Ryan Ollos

unread,
Dec 2, 2015, 4:05:01 PM12/2/15
to Trac Users
On Wed, Dec 2, 2015 at 10:20 AM, Roger Oberholtzer <roger.ob...@gmail.com> wrote:
Yes. Reports. They are a mix of SQL and TracQuery. I want to move them
all to TracQuery.

But what I would like to do is get them somewhere where I can clean
them up, reorder them, and all that fun housekeeping.And then reload
all reports into a fresh table.

Doing this in the web interface would be tedious.

There isn't a trac-admin command to export/import, so the only option I see is to write scripts with SQL to do the job.

Mo

unread,
Dec 8, 2015, 2:48:54 AM12/8/15
to Trac Users
Am Mittwoch, 2. Dezember 2015 06:14:03 UTC+1 schrieb RjOllos:

I agree it is a valuable feature, however we are really lacking contributors these days and the few of us that are working on the projects can't implement every nice-to-have feature in the limited time we have available. I'd like to get more people involved in the project, but that is difficult even someone commits to working some patches. Hopefully things will pickup in the coming year.


Thanks for that ticket. I understand that new features require the resources to implement that.
However I would give the custom query topic a high priority as Trac is a ticket system in the first place. And increasing number of tickets we encounter now that it's hard to maintain custom views on these tickets as known from the very early bugzilla and friends.

The difference between reports and queries gets even more confusing if I change a custom query and save it, which leads to a "Edit Report" dialog and "Save Report" button, where I can review the SQL behind my custom query and save it. So behind the queries engine there is always a SQL report as well?

What about if I like to re-arrange the queries? For now they have fixed index and inserting another custom query in the list is not possible easily, for example if I like to have the list sorted by project.

Best regards.

RjOllos

unread,
Dec 8, 2015, 1:26:32 PM12/8/15
to Trac Users


On Monday, December 7, 2015 at 11:48:54 PM UTC-8, Mo wrote:
The difference between reports and queries gets even more confusing if I change a custom query and save it, which leads to a "Edit Report" dialog and "Save Report" button, where I can review the SQL behind my custom query and save it. So behind the queries engine there is always a SQL report as well?

For a custom query (1), you'll find that it's not SQL in the report, but rather Query Language (2).

For report 36 on t.e.o (3), the following is saved:

query:?status=closed
&
group=version
&
max=150
&
order=priority
&
col=id
&
col=summary
&
col=owner
&
col=type
&
col=priority
&
col=component
&
col=version
&
col=changetime
&
milestone=
&
report=36
&
resolution=fixed
 

Note the label above the SQL/TracQuery box:










We can probably change the labels to make this slightly less confusing, see #12266 (4).

What about if I like to re-arrange the queries? For now they have fixed index and inserting another custom query in the list is not possible easily, for example if I like to have the list sorted by project.

Reply all
Reply to author
Forward
0 new messages