Wednesday 3 December 2014

(CFML) It's time to get query of query improved

G'day:
I'm sure all this has done the rounds before, but I cannot find the tickets. So I have raised them again:


Improve joining in query of query (ColdFusion | Railo)


CFML's QoQ is very limited in how it can join queries:

  • only two queries
  • only old-school filter joins (eg: from q1, q2 where q1.pk=q2.fk)
  • no outer joins


This implementation is lovely for a proof of concept, but it's not really production-ready. And it's been like this since forever.

It's about time QoQ got a second tranche of development.


Improve scalar functions in query of query (ColdFusion | Railo)


QoQ has, I think, only these functions:

  • avg()
  • count()
  • max()
  • min()
  • sum()
  • upper()
  • lower()


Not bad for a first try (although the casing ones didn't even make it in the first try, I think?), but there are really a lot of scalar functions an SQL implementation ought to support.

Obviously Adobe are not in the business of creating a DB platform, but as a minimum a few ought to be added:
* time part extraction like year() / month() / day() / hour() / minute() / second()
* datediff() / dateadd()
* substring() / replace() / trim()


Add CASE construct to query of query (ColdFusion | Railo)


As per the ANSI standard, eg: http://en.wikipedia.org/wiki/SQL#Conditional_.28CASE.29_expressions



These are non-theoretical requirements: I've needed them all in the past, and due to QoQ's lack of them, it's invalidated it as part of a solution.

As I state in the first ticket, I think query of query is a fine proof of concept, but it's never really moved on from there. And it's been around for over a decade now and has received very little attention. It's time that was changed.

Thoughts / votes / comments?

--
Adam