Reports: PostgreSQL Join/Sum Help

[dkl13]

I can do some basic SQL, but my overall SQL knowledge is very light. What SQL would return the desired results for the scenario below?

• Trac 0.12
• PostgreSQL 8.3
• ticket-custom fields:
• parent: e.g., If ticket #22 is a child ticket of ticket #14, ticket #22 will have 14 in this field.
• backlog_points: Backlog points as numbers (e.g., 11), although this field is a text field (so to_number() needed?).

 

Desired report:

• Display only parent tickets in the report. These parent tickets are identified by parent = blank (ticket_custom table).
• Sum the backlog_points (ticket_custom table) for any child tickets for each parent and display that on each row of the parent tickets report (Total Backlog Points)
• Would also be great to have a column that lists all the child tickets as clickable ticket links, but that may not be possible.

 

Sample data:

• #14 = parent ticket
• #22 (backlog_points = 5) = child of #14, so has 14 in parent field
• #24 (backlog_points = 1) = child of #14, so has 14 in parent field
• #25 (backlog_points = 7) = child of #14, so has 14 in parent field
• #12 = parent ticket
• #15 (backlog_points = 1) = child of #12, so has 12 in parent field
• #17 (backlog_points = 5) = child of #12, so has 12 in parent field

Report:

 

ID  Total Backlog Points  Child Tickets

14          13            22, 24, 25

12           6            15, 17

 

Most of the scrum/agile plugins we've tried, by the way, have problems. (We are on an out-dated version of Python, which is a situation 1) I have no control over, and 2) probably won't change for a year or so. This tends to limit what plugins we can use.) Thanks!

 

D

[Cooke, Mark]

> -----Original Message-----
> From: trac-...@googlegroups.com On Behalf Of dkl13
> Sent: 17 May 2013 16:20
> To: trac-...@googlegroups.com
> Subject: [Trac] Reports: PostgreSQL Join/Sum Help
>
> I can do some basic SQL, but my overall SQL knowledge is very
> light. What SQL would return the desired results for the
> scenario below?
>

> * Trac 0.12
> * PostgreSQL 8.3
> * ticket-custom fields:
>
> * parent: e.g., If ticket #22 is a child ticket

> of ticket #14, ticket #22 will have 14 in this field.

> * backlog_points: Backlog points as numbers

> (e.g., 11), although this field is a text field (so
> to_number() needed?).
>
>
> Desired report:
>

> * Display only parent tickets in the report. These parent

> tickets are identified by parent = blank (ticket_custom table).

> * Sum the backlog_points (ticket_custom table) for any

> child tickets for each parent and display that on each row of
> the parent tickets report (Total Backlog Points)

> * Would also be great to have a column that lists all the

> child tickets as clickable ticket links, but that may not be possible.
>
>
> Sample data:
>

> * #14 = parent ticket
>
> * #22 (backlog_points = 5) = child of #14, so has
> 14 in parent field
> * #24 (backlog_points = 1) = child of #14, so has
> 14 in parent field
> * #25 (backlog_points = 7) = child of #14, so has
> 14 in parent field
>
> * #12 = parent ticket
>
> * #15 (backlog_points = 1) = child of #12, so has
> 12 in parent field
> * #17 (backlog_points = 5) = child of #12, so has

> 12 in parent field
>
> Report:
>
> ID Total Backlog Points Child Tickets
> 14 13 22, 24, 25
> 12 6 15, 17
>
> Most of the scrum/agile plugins we've tried, by the way, have
> problems. (We are on an out-dated version of Python, which is
> a situation 1) I have no control over, and 2) probably won't
> change for a year or so. This tends to limit what plugins we
> can use.) Thanks!

AFAIK you cannot get what you want from SQL, and if you can I suspect it would not be a quick query! If you can I would love to see how...

Personally I would do this in Python, read the data in and (relatively easily) parse it in memory. You don't say which version of python you use? Also, how many tickets are you looking at and how many levels of `grand` child are allowed? I assume you only want open tickets? Do you need to filter by milestone or keyword or anything else?

I would probably start with one of the macros like `UserStatsMacro` [1] which reads data from the dB, does some sifting and displays on a wiki page the results as a table...

~ Mark C

[1] http://trac-hacks.org/wiki/UserStatsMacro

[Steffen Hoffmann]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Following Mark's answer with more definitive statements.

On 17.05.2013 17:19, dkl13 wrote:
> I can do some basic SQL, but my overall SQL knowledge is very light.
> What SQL would return the desired results for the scenario below?
>

> * Trac 0.12
> * PostgreSQL 8.3
> * ticket-custom fields:

> o *parent*: e.g., If ticket #22 is a child ticket of ticket #14,

> ticket #22 will have 14 in this field.

> o *backlog_points*: Backlog points as numbers (e.g., 11), although

> this field is a text field (so to_number() needed?).

Trac SQL statement design principle: Do not use db back-end-specific
expressions. Ideally back-end type an version shouldn't matter at all.

> *Desired report:*
>
> * Display *only parent tickets* in the report. These parent tickets
> are identified by *parent = blank* (ticket_custom table).

Simple.

> * Sum the backlog_points (ticket_custom table) for any child tickets

> for each parent and display that on each row of the parent tickets
> report (Total Backlog Points)

Not hard.

> * Would also be great to have a column that lists all the child

> tickets as clickable ticket links, but that may not be possible.

Could be hacked by string-concatenation (not cross-db) for you
individual Trac instance. A generic approach is using the
`get_resource_url` from `trac.resource.ResourceSystem`, that does not
rely on any hard-coded URL parts.

> *Sample data:*

>
> * #14 = parent ticket

> o #22 (backlog_points = 5) = child of #14, so has 14 in parent field
> o #24 (backlog_points = 1) = child of #14, so has 14 in parent field
> o #25 (backlog_points = 7) = child of #14, so has 14 in parent field

> * #12 = parent ticket

> o #15 (backlog_points = 1) = child of #12, so has 12 in parent field
> o #17 (backlog_points = 5) = child of #12, so has 12 in parent field
>
> Report:
>
> *ID Total Backlog Points Child Tickets*

> 14 13 22, 24, 25
> 12 6 15, 17
>
> Most of the scrum/agile plugins we've tried, by the way, have
> problems. (We are on an out-dated version of Python, which is a
> situation 1) I have no control over, and 2) probably won't change for a
> year or so. This tends to limit what plugins we can use.) Thanks!

Depends on how outdated is 'outdated' in your case. Trac is always
running on an old Python version from a Python developers point. For new
projects Python3.2 or later might be fine, while Trac cut Python
compatibility to 'only' Python2.5, with some of the currently maintained
Trac plugins still caring for Python2.4. Anything older will certainly
cause issues, and you'll get sub-optimal results, if not worse.
While I know your situation only too good, it doesn't pay-off at all to
keep backwards-compatibility at all cost. As soon as developers us newer
versions themselves, support for these older versions will generally
degrade over time. And the OS is meant to serve (well) for supporting
application software, not the other way round, at least its not meant to
be like that.
Even Debian, that is often referenced for shipping rather aged software
packages, had Python2.6.6, 2.5.5 und 3.1.3 in version 6.0 ('squeeze'),
2.7.3 and 3.2.3 in current stable 1.0 (code-named 'wheezy'). Only RHEL
seems to lag behind, what becomes a real pain at times. You get the
distro you deserve. No, joking here. Better nudge you admins etc. to get
you a more recent system at any rate.

Steffen Hoffmann
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlGZ25kACgkQ31DJeiZFuHcNAQCcDtiB4wH3GvH6hTeNKP7aGkEf
xVEAmwb+87aCSYXKocr2xVf/oUU0F4A9
=CBHT
-----END PGP SIGNATURE-----

[dkl13]

On Friday, May 17, 2013 10:19:42 AM UTC-5, dkl13 wrote:

I can do some basic SQL, but my overall SQL knowledge is very light. What SQL would return the desired results for the scenario below?

• Trac 0.12
• PostgreSQL 8.3
• ticket-custom fields:
• parent: e.g., If ticket #22 is a child ticket of ticket #14, ticket #22 will have 14 in this field.
• backlog_points: Backlog points as numbers (e.g., 11), although this field is a text field (so to_number() needed?).

Thanks for the replies. We are on Python 2.4.3. We have many Tracs (think in terms of hundreds, if not thousands), some of which are interconnected (via plugins), all on the same version of Linux, Python, Postgres, etc. So the upgrade (not my call, although I can voice an opinion) will take several days if not longer. Not sure we can take that much time off in the near future to get 'er done.

And I don't know Python at all, but I'm thinking I might have to learn it. For now, since my duties do not include developing Trac plugins, I will tell the person who is requesting this info that it's just not available.

Thanks, again!

D