public static function getAssociations(ezcDbHandler $db, $url)
{
$options = new ezcAuthenticationOpenidDbStoreOptions();
$table = $options->tableAssociations;
$query = new ezcQuerySelect($db);
$e = $query->expr;
$query->select('*')->from($db->quoteIdentifier($table['name']))->where($e->eq($db->quoteIdentifier($table['fields']['url']), $query->bindValue($url)));
$query = $query->prepare();
$query->execute();
$rows = $query->fetchAll();
if (count($rows) > 0) {
$rows = $rows[0];
$data = $rows[$table['fields']['association']];
return $data;
}
}
static function execOptionsQuery(ezcQuerySelect $query, $keyName, $valueName, array $options = array())
{
$defaultOptions = array('skip_empty' => false, 'allow_empty' => false, 'empty_text' => _('-- Selezionare --'));
$opt = array_merge($defaultOptions, $options);
$stmt = $query->prepare();
$stmt->execute();
$optionsList = array();
if ($opt['allow_empty']) {
$optionsList[''] = $opt['empty_text'];
}
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
if ($opt['skip_empty'] !== true || trim($row[$valueName]) != '') {
$optionsList[$row[$keyName]] = $row[$valueName];
}
}
return $optionsList;
}
/**
* Returns the SQL string for the subselect.
*
* Example:
* <code>
* <?php
* $subSelect = $q->subSelect();
* $subSelect->select( name )->from( 'table2' );
* $q->select( '*' )
* ->from( 'table1' )
* ->where ( $q->expr->eq( 'name', $subSelect ) );
* $stmt = $q->prepare();
* $stmt->execute();
* ?>
* </code>
*
* @return string
*/
public function getQuery()
{
return '( ' . parent::getQuery() . ' )';
}
/**
* Resets the query object for reuse.
*
* @return void
*/
public function reset()
{
parent::reset();
$this->fromTables = array();
$this->rightJoins = array(null);
}
/**
* Returns the SQL to check if a value is one in a set of
* given values.
*
* in() accepts an arbitrary number of parameters. The first parameter
* must always specify the value that should be matched against. Successive
* parameters must contain a logical expression or an array with logical
* expressions. These expressions will be matched against the first
* parameter.
*
* Example:
* <code>
* $q->select( '*' )->from( 'table' )
* ->where( $q->expr->in( 'id', 1, 2, 3 ) );
* </code>
*
* Oracle limits the number of values in a single IN() to 1000. This
* implementation creates a list of combined IN() expressions to bypass
* this limitation.
*
* @throws ezcQueryVariableParameterException if called with less than two
* parameters.
* @throws ezcQueryInvalidParameterException if the 2nd parameter is an
* empty array.
* @param string|array(string) values that will be matched against $column
* @return string logical expression
*/
public function in($column)
{
$args = func_get_args();
if (count($args) < 2) {
throw new ezcQueryVariableParameterException('in', count($args), 2);
}
if (is_array($args[1]) && count($args[1]) == 0) {
throw new ezcQueryInvalidParameterException('in', 2, 'empty array', 'non-empty array');
}
$values = ezcQuerySelect::arrayFlatten(array_slice($args, 1));
$values = $this->getIdentifiers($values);
$column = $this->getIdentifier($column);
if (count($values) == 0) {
throw new ezcQueryVariableParameterException('in', count($args), 2);
}
if ($this->quoteValues) {
foreach ($values as $key => $value) {
switch (true) {
case is_int($value):
case is_float($value):
case $value instanceof ezcQuerySubSelect:
$values[$key] = (string) $value;
break;
default:
$values[$key] = $this->db->quote($value);
}
}
}
if (count($values) <= 1000) {
return "{$column} IN ( " . join(', ', $values) . ' )';
} else {
$expression = '( ';
do {
$bunch = array_slice($values, 0, 1000);
$values = array_slice($values, 1000);
$expression .= "{$column} IN ( " . join(', ', $bunch) . ' ) OR ';
} while (count($values) > 1000);
$expression .= "{$column} IN ( " . join(', ', $values) . ' ) )';
return $expression;
}
}
/**
* Handles preparing query.
*
* Overrides ezcQuery->prepare()
*
* Adds "FROM dual" to the select if no FROM clause specified
* i.e. fixes queries like "SELECT 1+1" to work in Oracle.
*
* @return PDOStatement
*/
public function prepare()
{
if ($this->fromString == null || $this->fromString == '') {
$this->from($this->getDummyTableName());
}
return parent::prepare();
}
/**
* Creates a simple JOIN to fetch the objects defined by $relation.
*
* Creates a simple LEFT JOIN using the aliases defined in $relation and
* the $srcTableAlias, to fetch all objects defined by $relation, which are
* related to the source object, fetched by $srcTableAlias.
*
* @param ezcQuerySelect $q
* @param string $srcTableAlias
* @param string $dstTableAlias
* @param ezcPersistentRelationFindDefinition $relation
*/
protected function createSimpleJoin(ezcQuerySelect $q, $srcTableAlias, $dstTableAlias, ezcPersistentRelationFindDefinition $relation)
{
$relationDefinition = $relation->relationDefinition;
$first = true;
$joinCond = null;
foreach ($relationDefinition->columnMap as $mapping) {
$srcColumn = $this->getColumnName($srcTableAlias, $mapping->sourceColumn);
$destColumn = $this->getColumnName($dstTableAlias, $mapping->destinationColumn);
if ($first) {
$joinCond = $q->expr->eq($srcColumn, $destColumn);
$first = false;
} else {
$joinCond = $q->expr->and($joinCond, $q->expr->eq($srcColumn, $destColumn));
}
}
$q->leftJoin($q->alias($this->db->quoteIdentifier($relationDefinition->destinationTable), $this->db->quoteIdentifier($dstTableAlias)), $joinCond);
}
/**
* Returns the unserialized association linked to the OpenID provider URL.
*
* Returns false if the association could not be retrieved or if it expired.
*
* @param string $url The URL of the OpenID provider
* @return ezcAuthenticationOpenidAssociation
*/
public function getAssociation($url)
{
$table = $this->options->tableAssociations;
$query = new ezcQuerySelect($this->instance);
$e = $query->expr;
$query->select('*')->from($this->instance->quoteIdentifier($table['name']))->where($e->eq($this->instance->quoteIdentifier($table['fields']['url']), $query->bindValue($url)));
$query = $query->prepare();
$query->execute();
$rows = $query->fetchAll();
if (count($rows) > 0) {
$rows = $rows[0];
$data = unserialize($rows[$table['fields']['association']]);
return $data;
}
// no association was found for $url
return false;
}
public function testSubSubSelect()
{
$name = 'IBM';
$name2 = 'company';
$q = new ezcQuerySelect(ezcDbInstance::get());
$q->expr->setValuesQuoting(false);
// subselect
$q2 = $q->subSelect();
// sub subselect
$q3 = $q2->subSelect();
$q3->expr->setValuesQuoting(false);
$q3->select('*')->from('query_test2')->where($q3->expr->in('company', 'IBM', 'eZ systems'));
// bind values
$q2->select('company')->from('query_test')->where($q2->expr->eq('company', $q2->bindParam($name)), ' id > 2 ');
$q->select('*')->from('query_test')->where(' id >= 1 ', $q->expr->in('company', $q2->getQuery()))->orderBy('id');
$stmt = $q->prepare();
$stmt->execute();
$result = $stmt->fetchAll();
$this->assertEquals('IBM', $result[0]['company']);
$this->assertEquals('Norway', $result[0]['section']);
$this->assertEquals('IBM', $result[1]['company']);
$this->assertEquals('Germany', $result[1]['section']);
}
/**
* Returns a series of strings concatinated
*
* concat() accepts an arbitrary number of parameters. Each parameter
* must contain an expression or an array with expressions.
*
* @param string|array(string) $... strings that will be concatinated.
*/
public function concat()
{
$args = func_get_args();
$cols = ezcQuerySelect::arrayFlatten($args);
if (count($cols) < 1) {
throw new ezcQueryVariableParameterException('concat', count($args), 1);
}
$cols = $this->getIdentifiers($cols);
return "CONCAT( " . join(', ', $cols) . ' )';
}
/**
* Helper for {@see listVersions()} and {@see listVersionsForUser()} that filters duplicates
* that are the result of the cartesian product performed by createVersionInfoFindQuery()
*
* @param \ezcQuerySelect $query
* @return string[][]
*/
private function listVersionsHelper($query)
{
$query->orderBy($this->dbHandler->quoteColumn('id', 'ezcontentobject_version'));
$statement = $query->prepare();
$statement->execute();
$results = array();
$previousId = null;
foreach ($statement->fetchAll(\PDO::FETCH_ASSOC) as $row) {
if ($row["ezcontentobject_version_id"] == $previousId) {
continue;
}
$previousId = $row["ezcontentobject_version_id"];
$results[] = $row;
}
return $results;
}
/**
* Transforms the query from the parent to provide LIMIT functionality.
*
* Note: doesn't work exactly like the MySQL equivalent; it will always return
* $limit rows even if $offset + $limit exceeds the total number of rows.
*
* @throws ezcQueryInvalidException if offset is used and orderBy is not.
* @return string
*/
public function getQuery()
{
$query = parent::getQuery();
if ($this->hasLimit) {
if ($this->offset) {
if (!$this->orderString) {
// Uh ow. We need some columns to sort in the oposite order to make this work
throw new ezcQueryInvalidException("LIMIT workaround for MS SQL", "orderBy() was not called before getQuery().");
}
return 'SELECT * FROM ( SELECT TOP ' . $this->limit . ' * FROM ( ' . self::top($this->offset + $this->limit, $query) . ' ) AS ezcDummyTable1 ' . $this->invertedOrderString . ' ) AS ezcDummyTable2 ' . $this->orderString;
}
return self::top($this->limit, $query);
}
return $query;
}
<?php
require_once __DIR__ . '/tutorial_example_01.php';
$name = 'IBM';
$q = new ezcQuerySelect(ezcDbInstance::get());
// Creating subselect object
$q2 = $q->subSelect();
// $q2 will build the subquery "SELECT company FROM query_test WHERE
// company = :ezcValue1 AND id > 2". This query will be used inside the SQL for
// $q.
$q2->select('company')->from('query_test')->where($q2->expr->eq('company', $q2->bindParam($name)), 'id > 2');
// $q the resulting query. It produces the following SQL:
// SELECT * FROM query_test
// WHERE id >= 1 AND
// company IN ( (
// SELECT company FROM query_test
// WHERE company = :ezcValue1 AND id > 2
// ) )
$q->select('*')->from('query_test')->where(' id >= 1 ', $q->expr->in('company', $q2));
$stmt = $q->prepare();
echo $stmt->queryString;
//$stmt->execute();
/**
* Runs the filter and returns a status code when finished.
*
* @param ezcAuthenticationPasswordCredentials $credentials Authentication credentials
* @return int
*/
public function run($credentials)
{
$db = $this->database;
// see if username exists
$query = new ezcQuerySelect($db->instance);
$e = $query->expr;
$query->select('COUNT( ' . $db->instance->quoteIdentifier($db->fields[0]) . ' )')->from($db->instance->quoteIdentifier($db->table))->where($e->eq($db->instance->quoteIdentifier($db->fields[0]), $query->bindValue($credentials->id)));
$rows = $query->prepare();
$rows->execute();
$count = (int) $rows->fetchColumn(0);
if ($count === 0) {
return self::STATUS_USERNAME_INCORRECT;
}
$rows->closeCursor();
if (count($this->requestedData) > 0) {
// fetch extra data from the database
$query = new ezcQuerySelect($db->instance);
$e = $query->expr;
$params = array();
foreach ($this->requestedData as $param) {
$params[] = $db->instance->quoteIdentifier($param);
}
$query->select(implode(', ', $params))->from($db->instance->quoteIdentifier($db->table))->where($e->lAnd($e->eq($db->instance->quoteIdentifier($db->fields[0]), $query->bindValue($credentials->id))));
$rows = $query->prepare();
$rows->execute();
$data = $rows->fetchAll();
$data = $data[0];
foreach ($this->requestedData as $attribute) {
$this->data[$attribute] = array($data[$attribute]);
}
}
return self::STATUS_OK;
}
