thekid/rfc — Issue #131: RFC 0131: Query class and operation
Author: thekid
Created: 2011-10-09 15:18:10+00:00
State: open
Labels: implemented, rfc

Scope of Change
====
Extends the rdbms critertia api
- Query class that stores complete querys
- set operations like union, intercept and except

Rationale
====
- Store complete queries.
- Provide a tool to represent set operations in criteria / OO world.

Functionality
====

```
intercept and except will not work on mysql databases

```

- new interfaces
- rdbms.query.QueryExecutable
- rdbms.query.SelectQueryExecutable
- the api provides the following classes:
- rdbms.query.SetOperation
- rdbms.query.SelectQuery
- rdbms.query.UpdateQuery
- rdbms.query.DeleteQuery
- Rdbms.Criteria needs to be extended by a method getSelectQueryString(),
which will fetch the sql statement as string.
- The method rdbms.Criteria::executeJoin() will be removed.
- The class rdbms.Query will be moved into the package rdbms.query.Query.
- The class rdbms.Query will be declared abstract.

UML diagram
----
[[img:uml.png]]

code examples
----

```php
setPeer(Job::getPeer());
$selectQueryA->setCriteria(
create(new Criteria(Job::column('job_id')->equal(5)))->setProjection(
Projections::ProjectionList()
->add(Job::column('job_id'))
->add(Job::column('title'))
)
);

$selectQueryB= new SelectQuery();
$selectQueryB->setPeer(Person::getPeer());
$selectQueryB->setCriteria(
create(new Criteria())->setProjection(
Projections::ProjectionList()
->add(Person::column('job_id'))
->add(Person::column('name'))
)
);

Console::writeLine('QueryA: '.$selectQueryA->getQueryString());
Console::writeLine('QueryB: '.$selectQueryB->getQueryString());
Console::writeLine('QueryA union QueryB: '.SetOperation::union(
$selectQueryA, $selectQueryB)->getQueryString()
);
?>
```

Would print:

```
QueryA: select job_id, title from JOBS.job where job_id = 5
QueryB: select job_id, name from JOBS.Person
QueryA union QueryB: select job_id, title from JOBS.job where job_id = 5
union select job_id, name from JOBS.Person

```

Security considerations
====
n/a

Speed impact
====
n/a

Dependencies
====
criteria api

Related documents
====
- patch against skeleton
http://xp-framework.net/rfc/contrib/rfc0131/skeleton.diff
- patch against ports
http://xp-framework.net/rfc/contrib/rfc0131/ports.diff
- uml diagram as dia file
http://xp-framework.net/rfc/media/0131/uml.dia

Comments
====
- wagner (Di 3 Jul 2007 18:00:41 CEST):
Changed inheritances to interfaces for clearer structure

- friebe, Tue Jul 3 21:06:40 2007
API seems to be limited to two QueryExecutables, but SQL supports
select ... union all select ... union all select ... [etc.]
for example.

- wagner (Mi 4 Jul 2007 08:49:59 CEST)
SetOperation::union, SetOperation::intercept and SetOperation::except accept
two rdbms.query.SelectQueryExecutables as arguments. So you can realise the
SQL code above like:

SetOperation::union(
$selectQueryA, SetOperation::union(
$selectQueryB, SetOperation::union(
$selectQueryC, ... [etc.]
), TRUE
), TRUE
);