queries

Running the built queries, integration with pg_wrapper

While pg_builder can be used on its own, the main goal of building an SQL statement is to eventually execute it on the database server. pg_wrapper package provides a means to execute queries and to convert PHP variables to Postgres representation. pg_builder on the other hand is able to infer a proper database type for such conversions directly from SQL.

converters\ParserAwareTypeConverterFactory class

This class extends \sad_spirit\pg_wrapper\converters\DefaultTypeConverterFactory providing additional functionality:

• A Parser instance may be set by setParser(Parser $parser): $this method. It will be used to parse type names provided as strings so that Factory will understand any type name Postgres itself can (DefaultTypeConverterFactory implements a far simpler parser).
• getConverterForTypeSpecification() also accepts instances of nodes\TypeName. ParameterWalker collects these from SQL typecasts.
• createTypeNameNodeForOID(int|numeric-string $oid): nodes\TypeName will create a type name node representing a given type oid that can later be added to the AST.

An instance of this should be set via setTypeConverterFactory() method of \sad_spirit\pg_wrapper\Connection instance if you execute queries returned by StatementFactory::createFromAST().

NativeStatement class

This class wraps the results of query building process, instances of it are returned by StatementFactory::createFromAST(). It can be serialized (implements a magic __sleep() method) and stored in cache.

NativeStatement contains methods to execute the generated query using Connection:

• executeParams(Connection $connection, array $params, array $paramTypes = [], array $resultTypes = []): ResultSet - Executes the query with the ability to pass parameters separately. $params, $inputTypes, and $outputTypes have the same meanings as those of Connection::executeParams().
• prepare(Connection $connection, array $paramTypes = []): PreparedStatement - Prepares the query for execution. Type specifications from $types override those received from builder.
• executePrepared(array $params = [], array $resultTypes = []): ResultSet - Executes the prepared statement (requires prepare() to be called first)

$params in the above methods can be keyed with parameter names and it is not necessary to specify types for parameters if those were specified in the query itself:

$native = $factory->createFromAST($factory->createFromString(
    'select typname from pg_catalog.pg_type where oid = any(:oid::integer[]) order by typname'
));
$result = $native->executeParams($connection, ['oid' => [21, 23]]);

Additional public methods of the class:

• getSql(): string - Returns the generated query string.
• getNamedParameterMap(): array - Returns mapping 'parameter name' => 'parameter position' from named parameters to positional ones.
• getParameterTypes(): array - Returns known types for parameters 'parameter position' => 'parameter type' if explicit typecasts were used for parameters. Parameter types are represented by instances of TypeName.
• mapNamedParameters(array $parameters): array - Converts parameters array keyed with parameters' names to positional array. Will throw InvalidArgumentException in case of missing or unknown parameter names.
• mergeParameterTypes(array $paramTypes): array - Merges the types array received from builder with additional types info. $inputTypes can be keyed by either names or positions, type specifications from this array take precedence over types received from builder. Will throw InvalidArgumentException in case of invalid key.

use sad_spirit\pg_builder\StatementFactory;

$factory = new StatementFactory();
$native  = $factory->createFromAST($factory->createFromString(
    'select typname from pg_catalog.pg_type where oid = any(:oid::integer[]) order by typname'
));

echo $native->getSql() . "\n";
var_dump($native->getNamedParameterMap());
var_dump($native->getParameterTypes());

will output something like

select typname
from pg_catalog.pg_type
where oid = any($1::pg_catalog.int4[])
order by typname
array(1) {
  'oid' =>
  int(0)
}
array(1) {
  [0] =>
  class sad_spirit\pg_builder\nodes\TypeName#70 (2) {
    ...
  }
}