db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Fwd: [jira] [Created] (INFRA-4026) Jira no longer allows export of more than 1000 issues. Gives HTTP Status 403 - You are not allowed to get a result set of more than 1000 results.
Date Fri, 14 Oct 2011 13:45:07 GMT
On 10/14/11 6:13 AM, Kristian Waagan wrote:
> On 14.10.11 14:28, Rick Hillegas wrote:
>> On 10/14/11 1:38 AM, Kristian Waagan wrote:
>>> On 10/13/11 02:47 PM, Rick Hillegas wrote:
>>>> On 10/13/11 3:12 AM, Kristian Waagan wrote:
>>>>> On 10/13/11 12:48 AM, Kathey Marsden wrote:
>>>>>> FYI, I filed this IFRA issue which has some impact on the Derby
>>>>>> Jira VTI in our demos.
>>>>>> I suppose the demo will still work but now Jira export is limited
>>>>>> to 1000 issues. Is a DERBY issue in order as well?
>>>>> Working around that limit is possible in this case [1] - the
>>>>> question is if we want to do that?
>>>>> If people are using this demo extensively as their own private
>>>>> "Derby JIRA cache", I think some effort should be invested into
>>>>> writing something that imposes less load on the ASF JIRA instance.
>>>>> Writing something solid here may require quite a bit of effort.
>>>>> Without having given this extensive thought, I think parsing emails
>>>>> as suggested on infra is the kindest option. This would probably
>>>>> suit committers well.
>>>>> Maybe the activity stream (as an RSS feed) could be used too, but in
>>>>> that case it may be harder to make sure no updates are missed.
>>>>> A third option is to use the SOAP API. One has to be able to define
>>>>> a suitable search (for instance 'give me all updates on Derby from
>>>>> date X to date Y'), but I suspect you'd have to download all the
>>>>> issue information instead of just what changed in that period. The
>>>>> upside with this approach compared to the RSS one, is that you
>>>>> decide when you want to update your copy.
>>>> Now that we (by which I mean Kristian) know how to use the SOAP API,
>>>> it may be possible to write a restricted vti that wraps the SOAP API.
>>>> I believe that the existing vti is used to dramatically reduce the
>>>> set of issues you have to look at by applying restrictions which
>>>> cannot be expressed in the JIRA gui.
>>> What are examples of such restrictions?
>> Hi Kristian,
>> Here is a sample query which uses the existing jira xml vti (from
>> http://wiki.apache.org/db-derby/HowToRunJiraVtiReports):
>> select s."key", s."assignee", s."updated" , s."title"
>> from table( "apacheVanillaJiraReport"(
>> 'file:///kmarsden/projects/jirareports/all_JIRA_ISSUES.xml' ) ) s where
>> "resolution" = 'Unresolved' and "assignee" != 'Unassigned' and NOT
>> "updated" like '%2008%' order by "assignee";
> Thanks, Rick.
> This translates roughly to the following JQL (I changed the date):
> project = 'DERBY' AND resolution = 'Unresolved' AND assignee is not 
> NULL AND updated < '2011-01-01'
> (returns 25 issues by the way)
>> There are two interesting things about this query:
>> 1) Projection (fancy term for "SELECT list")- The SELECT list is just a
>> few of the columns in the vti. That is, the width of the query result is
>> considerably narrower than the full width of the vti.
>> 2) Restriction (fancy term for pieces of the WHERE clause) - A couple
>> terms in the WHERE clause could be pushed into the Store if the query
>> were directed against a Derby table rather than a vti. These terms
>> reduce the length of the result set coming back to the SQL interpreter.
>> These terms are:
>> "resolution" = 'Unresolved'
>> "assignee" != 'Unassigned'
>> If the query were issued against a restricted vti which wrapped the SOAP
>> api, then the projection and the restriction could be pushed into the
>> vti. That is, Derby would tell the vti to fetch only 4 columns and to
>> throw away any rows which didn't match the two WHERE clause terms above.
>> Presumably, this information could be passed through the SOAP api to
>> limit the width and length of the result set being requested from JIRA.
> The projection cannot be used to download less data with the SOAP API.
> The restrictions would be helpful though, but then we need a SQL -> 
> JQL translator, or we can cheat and accept the JQL as an argument to 
> the VTI.
> I haven't looked at how the VTI restrictions are represented, but for 
> the sake of a demo it should be feasible to write some code that 
> converts the VTI restrictions into JQL.
Hi Kristian,

The restriction is represented as a org.apache.derby.vti.Restriction. 
That class has a toSQL() method which turns the restriction into SQL 
suitable for sending to a foreign rdbms. JQL may be enough like SQL that 
the existing toSQL() output will be good enough. If not, turning a 
Restriction into a JQL WHERE clause should be straightforward (tm).

> But if you can express your query using JQL, why not go directly to JIRA?
I think that the problem is that there are some queries (particularly 
those involving NOT) which can't be constructed using the existing JIRA 
gui. You can say "I want all issues with the following fields turned on" 
but you can't say "I want all issues with the following field NOT turned 

> I see the point of caching and dataset rip-offs (i.e. you populate a 
> table and continue to refine the results/searches locally), are there 
> other use-cases where people think they would prefer Derby+VTI over 

View raw message