This library was born to improve the abstraction in the conversion of an Expression to a SQL statement, but it is completely Expression and SQL agnostic, so it can be used for simlar use-cases.
Let's say you have a type FieldConverter that converts a FieldExpression object to a prepared statement. That is basically a function type
FieldConverter: FieldExpression => PreparedStatement
For the sake of the argument, let's assume that PreparedStatement is just a string with placeholders, without the PDO values bindings.
Let's assume you use it in the following code
$fieldConverter = new FieldConverter;
$field1 = new FieldExpression("field1", "=", "value1");
$field2 = new FieldExpression("field2", "=", "value2");
// This gives the statement "field1 = :param1"
$statement1 = $fieldConverter->convert($field1);
// And this gives "field2 = :param1"
$statement2 = $fieldConverter->convert($field1);As you can see, since the converter is completely side-effects free, as it should be, it is returning the same placeholder for the two statements. And that's fine, since they are completely different statements. The problem arises when we try to define a CompositeExpressionConverter, i.e. something that convert a CompositeExpression (like an AND) to a prepared statement.
A CompositeExpression is defined like this:
$and = new CompositeExpression("and", [$field1, $field2]);Now when we try to convert it using a FieldConverter for children, we are in trouble.
If we do like this
class CompositeExpressionConverter
{
public function __constructor(FieldConverter $fieldConverter) { ... }
public function convert(CompositeFieldExpression $compositeExpr)
{
list($field1, $field2) = $compositeExpr->getChildren();
return sprintf(
"%s AND %s",
$this->fieldConverter->convert($field1),
$this->fieldConverter->convert($field2)
);
}
Then the code
$compositeConverter = new CompositeExpressionConverter;
$compositeConverter->convert($and);gives you, in the last line,
"field1 = :param1 AND field2 = :param1"
The placeholders are the same!
Here you can see what the problem is: since the FieldConverter is completely immutable, he can't understand that he is converting expressions that are part of a composite one, and he is not able to give different placeholders.
If you want to preserve immutability, there is no way of solving this without passing the placeholder name from the outside. But this would just move the problem from the FieldConverter to the CompositeExpressionConverter, making the latter mutable. And we don't want that, since we do want to use the same converter in all around our application, without caring about its state.
Let's extract the placeholder generation from the FieldConverter in a function of type
Binding: (string, int) => string where the first argument is the value of the field and the second one is its PDO Type. The function will care of the placeholder generation and value bindings. Having moved the placeholder generation inside that function type, we can now free the FieldConverter from the placeholders simply changing its signature:
FieldConverter: FieldExpression => (Binding => PreparedStatement)
and the same thing for the CompositeExpressionConverter:
CompositeExpressionConverter: CompositeExpression => (Binding => PreparedStatement)
So we are not more returning a string, but a function that given a Binding function returns a PreparedStatement. In other words, instead of returning directly a PreparedStatement we are returning a way of building it.
Let's abstract the things a bit and define them both as a Statement type:
Binding: Something => String
Statement: Something => (Binding => String)
// Or, equivalently, expanding the Binding type
Statement: Something => ((Something => String) => String)
Where Something in our case is the type of the couples ($value, $pdoType), and in general is the input type of the binding function.
Assuming that we have defined a PDOBindingFunction somewhere, and adapted the converters to the new signature, the code that generates PDO statements will be
$statement1 = $fieldConverter->convert($field1);
$statement2 = $fieldConverter->convert($field2);
$andStatement = $compositeConverter->convert($and);
// Prints "field1 = :param1", as before
echo $statement1(new PDOBindingFunction);
// Prints "field2 = :param1", as before
echo $statement2(new PDOBindingFunction);
// Prints "field1 = :param1 AND field2 = :param2", Correct!
echo $andStatement(new PDOBindingFunction);The and statement is now converted correctly, and the converters are still side-effects free!
The side-effects, like maintaining the counter for place-holders suffixes or putting bindings somewhere, have been moved to the binding function. So the dirty stuff has been completed extracted from the statements conversion.
Another benefit we got from this type transition is that we are now able to choose the SQL conversion strategy we want, without affecting the statement generation. For example, maybe we don't want prepared statements with placeholders, but we want a plain old SQL statement, with all the values escaped safely accordingly to their types and the db connection. Now that is just a matter of using a different Binding function:
$binding = new PlainSQLBindingFunction($connection);
// Prints "field1 = 'value1'"
echo $statement1($binding);
// Prints "field2 = 'value2'"
echo $statement2($binding);
// Prints "field1 = 'value1' AND field2 = 'value2'"
echo $andStatement($binding);The statement is defined as an interface with one method:
namespace Dxi\Core\StatementBuilder\Statement;
interface Statement
{
/**
* @param callable $bindingFunction
* @return string
*/
public function __invoke($bindingFunction);
}There is only one implementation of the interface in the library, the CallbackStatement class, that take a callback and set it as the implementation of the __invoke method.
For example, to get a statement that returns you the current time every time it is binded, you would do
use Dxi\Core\StatementBuilder\Statement\Statement;
$time = new CallbackStatement(function ($binding) {
return "The time is " . $binding();
});
$binding = 'time'; //The time PHP built-in function
// Prints 1432213140
echo $time($binding);
sleep(5);
// Prints 1432213145
echo $time($binding);The Builder gives you a set of higher-order functions to combine statements in other statements.
use Dxi\Core\StatementBuilder\Builder\Builder;
$binding = 'rand';
$builder = new Builder;
// Value statement on binding will call the binding function with the given args:
$numberFrom1To100 = $builder->valueStatement(0, 100);
$numberFrom1To100($binding); // Returns 45
$numberFrom1To100($binding); // Returns 87
// Simple statement combine an existing statement into a string using sprintf
$stringWithNumber = $builder->simpleStatement("The next number is %s", $numberFrom1To100);
// Returns "The next number is 12";
$stringWithNumber($binding);
// Composite Statement: combine different statements using a sprintf-like template
$composite = $builder->compositeStatement(
"The numbers are %s and %s",
$numberFrom1To100,
$numberFrom1To100
);
// Returns "The numbers are 1 and 56"
$composite($binding);
$implode = $builder->implode(", ", [$numberFrom1To100, $numberFrom1To100, numberFrom1To100]);
// Returns "89, 43, 67"
$implode($binding);The array binding function returns placeholders with an inrementing numeric suffix and put the bindings in an array as a side-effect:
use Dxi\Core\StatementBuilder\Binding\ArrayBindingFunction;
$bindings = [];
$arrayBinding = new ArrayBindingFunction($bindings);
$simpleCondition = $builder->simpleStatement("table.column = %s", 1234, \PDO::PARAM_INT);
// $prepared will be "table.column = :param1"
$prepared = $simpleCondition($arrayBinding);
// bindings will be
// [ "param1" => new ValueWithType(1234, \PDO::PARAM_INT)]This builder uses the createNamedParameter method of the given DBAL DoctrineQueryBuilder
use Dxi\Core\StatementBuilder\Binding\DoctrineQueryBuilderBindingFunction;
$doctrineBinding = new DoctrineQueryBuilderBindingFunction($doctrineQueryBuilder);
$simpleCondition($doctrineBinding);This is a simple builder used for testing. It escapes values using addslashes, so it is not SQL injection safe.
use Dxi\Core\StatementBuilder\Binding\SimpleBindingFunction;
$simpleBinding = new SimpleBindingFunction;
// Returns "table.column = 1234
$simpleCondition($simpleBinding);