Discussions on the API:
First observations/requirements:
- Identifiers are NEVER quoted by the SQL Query Object
- A query object cannot ever be as efficient as our Persisters building SQL in a custom way
- A SQL Query instance is only an Object with an internal stack of SQL parts, it should not have any logic whatsoever
- A SQL Query instance is a stack for bound params and values, i.e. it should work towards prepared statements and not inline and quote values (like Zend_Db_Select)
Questions:
- Should we allow methods to accept either array or string? Or streamline API to allow only one? select(string) + selectAll(array)?
Binding values:
$sql->bindValue(1); $sql->bindParam($foo, PDO::PARAM_INT);
Building a Select clause A:
$sql->select(array('foo', 'bar', 'baz')); $sql->select(array('foo' => 'foo1', 'bar' => 'bar2', 'baz' => 'baz3'));
Building a Select clause B:
$sql->select('foo')->select('bar')->select('baz'); $sql->select('foo', 'f');
Building the From clause A:
$sql->from('foo'); $sql->from('foo', 'f'); $sql->from('foo', 'f', array('foo'));
Building the From Clause B:
$sql->from('foo'); $sql->from(array('foo')); $sql->from(array('f' => 'foo')); $sql->from(array('f' => 'foo'), array('foo'));
Building a Join clause (obviously depends on the From syntax also for a consistent approach):
public function join($table, $alias, $onClause, $cols);
public function join($table, $onClause, $cols);
Join Method Names: joinInner, joinLeft, joinRight (Zend) OR innerJoin, leftJoin, rightJoin (Zeta)?
Where Syntax:
This is pretty difficult and i prefer the approach from ORM\QueryBuilder:
$sql->where("foo = ?");
$sql->where("foo = ?", $foo); $sql->where("foo = " . $sql->bindParam($foo));
$sql->where($sql->expr()->eq("foo", $sql->bindParam($foo));
For an OR on the main level:
$sql->where("foo = ?")->orWhere("bar = ?");
For a nested condition:
$sql->where("foo = ? OR bar = ? OR baz = ?");
$sql->where($sql->expr()->or(
$sql->expr()->eq("foo", $sql->bindParam($foo)),
$sql->expr()->eq("bar", $sql->bindParam($bar)),
$sql->expr()->eq("baz", $sql->bindParam($baz)),
));
There is an Expression class that has control flow and sql functions, using a platform internally for vendor specific sql.
Execution or Preparing the SQL:
$stmt = $sql->prepare();
$sql->execute();
Comments?
New Feature
Resolved
Major
Discussions on the API:
First observations/requirements:
Questions:
Binding values:
Building a Select clause A:
$sql->select(array('foo', 'bar', 'baz')); // SELECT foo, bar, baz $sql->select(array('foo' => 'foo1', 'bar' => 'bar2', 'baz' => 'baz3')); // SELECT foo AS foo1, bar AS bar2, baz AS baz3Building a Select clause B:
$sql->select('foo')->select('bar')->select('baz'); // SELECT foo, bar, baz $sql->select('foo', 'f'); // SELECT foo AS fBuilding the From clause A:
$sql->from('foo'); // FROM foo $sql->from('foo', 'f'); // FROM foo f $sql->from('foo', 'f', array('foo')); // SELECT foo FROM foo fBuilding the From Clause B:
$sql->from('foo'); // FROM foo $sql->from(array('foo')); // FROM foo $sql->from(array('f' => 'foo')); // FROM foo f $sql->from(array('f' => 'foo'), array('foo')); // SELECT foo FROM foo fBuilding a Join clause (obviously depends on the From syntax also for a consistent approach):
Join Method Names: joinInner, joinLeft, joinRight (Zend) OR innerJoin, leftJoin, rightJoin (Zeta)?
Where Syntax:
This is pretty difficult and i prefer the approach from ORM\QueryBuilder:
For an OR on the main level:
For a nested condition:
There is an Expression class that has control flow and sql functions, using a platform internally for vendor specific sql.
Execution or Preparing the SQL:
Comments?