Requesting rpc ticket.query advice

[Mark Abbate <ab...@gmail.com>]

Hi All

I'm in a situation where I need to perform queries via RPC but it looks 
like I'm limited by the simple query language.

For example, I need to query for a range of values in a custom field I 
added that holds an epoch millisec value.

I *could* use the report methods to create a report that uses SQL, and 
then execute that report on the fly, but afaict I cannot then delete 
that report which makes this approach impractical because this will be a 
heavily used activity.

I did try to populate the ticket.query with SQL such as:

{"params": ["SELECT id FROM ticket WHERE  id='1000'"] , "method": 
"ticket.query"}

but I get back a list of all ticket ids. If I execute that same SELECT 
in a report in BH I get ticket 1000 back.


(1) Am I missing something in the query language?

(2) Would it be a modest task for me to modify ticket.query to accept 
SQL? I haven't looked into the BH code at all. If yes, pointers are welcome.

(3) Would it be easier to add a report.delete?

(4) Or shall I just make my own custom rest interface and access the BH 
DB directly for read only queries?


As always, thanks for the effort put into this tool, really useful.
Best
Mark

Re: Requesting rpc ticket.query advice

[Mark Abbate <ab...@gmail.com>]

Hi Olemis

No problem, appreciate the advice.

I think I finally realize why I was having trouble with the query 
language. I think I missed a painfully simple part of the syntax, namely 
the use of the "or" operator. I referenced the "Query Language" section 
at the bottom of http://trac.edgewall.org/wiki/TracQuery.

For example:

query:?owner=~analytics & loc=Millis & time=Feb+15%2C+2015..Mar+18%2C+2015
&
or
&
owner=~analytics & loc=Easton & time=Feb+11%2C+2015..Mar+18%2C+2015

etc

So that was a "duh" moment for me. I would much prefer to use the built 
in bloodhound query language instead of the SQL.

Two additional questions, please.

(1) Can I add a custom field that can be queried like creation and 
modification time? According to 
http://trac.edgewall.org/wiki/TracTicketsCustomFields seems like the 
answer is no.

I ask because I am generating tickets for conditions that have a 
beginning and end which do not always match the creation or modification 
time of the ticket. I store those as epoch time in a pair of custom fields.

I would like to display tickets constrained by the condition begin and 
or end. I do have the hack-ish option of using a "starts with" on an 
epoch time to narrow the results and then further sort the results in 
the code that fetched the query results.

Am I missing a simple alternative?

(2) Is there a way to access manually entered comments from a 
bloodhound  (not sql) query?

I ask because I need to allow retrieval and modification of ticket 
comments from a different UI talking RPC with BH. I could certainly add 
a free form text field, but it seems like the built in comment mechanism 
might make more sense.


The parameterized report was a good reminder, if I still need to go to 
the report execute option that will be very helpful.

thanks again
Mark


On 03/14/2015 05:50 PM, Olemis Lang wrote:
> Hi Mark .
>
> Sorry for the delay to reply . That RPC method only supports TracQuery
> syntax [1]_ . I honestly do not think supporting SQL syntax in there
> is appropriate in this case . That's what SQL reports are for , and
> they are meant to be eventually phased out considering the tightly
> coupling with DB structure .
>
> Hint : Have you tried creating the report once and parameterizing your
> SQL query [2]_ ?
>
> .. [1] http://trac.edgewall.org/wiki/TracQuery
>
> .. [2] http://trac.edgewall.org/wiki/TracReports#AdvancedReports:DynamicVariables
>
>
> On 2/27/15, Mark Abbate <ab...@gmail.com> wrote:
>> Hi All
>>
>> I'm in a situation where I need to perform queries via RPC but it looks
>> like I'm limited by the simple query language.
>>
>> For example, I need to query for a range of values in a custom field I
>> added that holds an epoch millisec value.
>>
>> I *could* use the report methods to create a report that uses SQL, and
>> then execute that report on the fly, but afaict I cannot then delete
>> that report which makes this approach impractical because this will be a
>> heavily used activity.
>>
>> I did try to populate the ticket.query with SQL such as:
>>
>> {"params": ["SELECT id FROM ticket WHERE  id='1000'"] , "method":
>> "ticket.query"}
>>
>> but I get back a list of all ticket ids. If I execute that same SELECT
>> in a report in BH I get ticket 1000 back.
>>
>>
>> (1) Am I missing something in the query language?
>>
>> (2) Would it be a modest task for me to modify ticket.query to accept
>> SQL? I haven't looked into the BH code at all. If yes, pointers are
>> welcome.
>>
>> (3) Would it be easier to add a report.delete?
>>
>> (4) Or shall I just make my own custom rest interface and access the BH
>> DB directly for read only queries?
>>
>>
>> As always, thanks for the effort put into this tool, really useful.
>> Best
>> Mark
>>
>

Re: Requesting rpc ticket.query advice

[Olemis Lang <ol...@gmail.com>]

Hi Mark .

Sorry for the delay to reply . That RPC method only supports TracQuery
syntax [1]_ . I honestly do not think supporting SQL syntax in there
is appropriate in this case . That's what SQL reports are for , and
they are meant to be eventually phased out considering the tightly
coupling with DB structure .

Hint : Have you tried creating the report once and parameterizing your
SQL query [2]_ ?

.. [1] http://trac.edgewall.org/wiki/TracQuery

.. [2] http://trac.edgewall.org/wiki/TracReports#AdvancedReports:DynamicVariables


On 2/27/15, Mark Abbate <ab...@gmail.com> wrote:
> Hi All
>
> I'm in a situation where I need to perform queries via RPC but it looks
> like I'm limited by the simple query language.
>
> For example, I need to query for a range of values in a custom field I
> added that holds an epoch millisec value.
>
> I *could* use the report methods to create a report that uses SQL, and
> then execute that report on the fly, but afaict I cannot then delete
> that report which makes this approach impractical because this will be a
> heavily used activity.
>
> I did try to populate the ticket.query with SQL such as:
>
> {"params": ["SELECT id FROM ticket WHERE  id='1000'"] , "method":
> "ticket.query"}
>
> but I get back a list of all ticket ids. If I execute that same SELECT
> in a report in BH I get ticket 1000 back.
>
>
> (1) Am I missing something in the query language?
>
> (2) Would it be a modest task for me to modify ticket.query to accept
> SQL? I haven't looked into the BH code at all. If yes, pointers are
> welcome.
>
> (3) Would it be easier to add a report.delete?
>
> (4) Or shall I just make my own custom rest interface and access the BH
> DB directly for read only queries?
>
>
> As always, thanks for the effort put into this tool, really useful.
> Best
> Mark
>


-- 
Regards,

Olemis - @olemislc

Apache(tm) Bloodhound contributor
http://issues.apache.org/bloodhound
http://blood-hound.net

Blog ES: http://simelo-es.blogspot.com/
Blog EN: http://simelo-en.blogspot.com/

Featured article: