views:

818

answers:

3

The 'open for business project' is an enterprise framework.

It so happens Jira uses this, and I was pretty shocked at how much work is involved to pull data for a particular entity (say a issue/bug in Jira's case).

Imagine getting a list of all the issues, it has to first get all the columns (or properties) to display for the table column, then pull in the values for each. For an enterprise solution this sounds like a sub-optimal solution (but I understand how it adds flexibility).

You can read how its used in Jira practically: http://confluence.atlassian.com/display/JIRA/Database+Schema

main site: http://ofbiz.apache.org/docs/entity.html

I'm just confused as to how to list all issues. Meaning, what would the sql queries look like?

Its one thing to pull a single issue, but to get a list you have to do allot of work to get the values. I don't think it can be done with a singl query using joins now can it?

A: 

The entity engine used in jira is a database abstraction layer ( with a very rich and easy to use API ) that connects your application with one or more datasources. But the databases are still relational, so you can use SQL if you want to. About the issue info you want to pull I'd say it wouldn't be very easy only with joins. I'd recommend you use the scripting language of the RDBMS ( i.e. PL/SQL, pgPL/SQL ).

Ismael
+1  A: 

(Disclaimer: I work for Atlassian, but I'm not on the JIRA team)

OFBiz EE is just an abstraction layer for moving between database tables and fancy maps called GenericValues. It has no influence over the database schema itself. Your real issue here seems to be that JIRA's database schema is complicated.

The reason it's complicated is because it has to support a data model where an issue is an arbitrary collection of arbitrary fields, at some point in an arbitrary workflow. The fields themselves can be defined by third-party plugins. It's very hard to produce a friendly-looking RDBMS schema to fit this kind of dynamic data model, and JIRA tries as best it can.

You can get information directly out of the database if you want, the database schema is documented in the link above, or you can go up a layer or twelve of abstraction and talk through one of JIRAs many APIs.

A good place to ask questions about getting data out of JIRA is the forums on http://forums.atlassian.com/

Charles Miller
A: 
SELECT * FROM jiraissue;
Evgeny