Query class and operation

  Status: implemented, Mi 11 Jul 2007 17:45:09 CEST
  • Created: 2007-07-03 13:26:05+0200
  • Categories: rdbms
  • Author: wagner

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

  uses(
    'rdbms.query.SelectQuery',
    'rdbms.query.SetOperation',
    'rdbms.Criteria',
    'net.xp_framework.unittest.rdbms.dataset.Job',
  );

  $selectQueryA= new SelectQuery();
  $selectQueryA->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
    );

<EOF>


Table of contents