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:

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
>>
>