public function getRandomUser($used = 1) { $usedCount = $this->getUnusedCount(); if (intval($usedCount) == 0) { return false; } $userData = $this->getOrderByRand($this->table, array("is_used" => 0), 1); if (!$userData) { return false; } $uid = $userData[0]["id"]; $where = array("id" => $uid); $connection = $this->dbAdapter->getDriver()->getConnection(); $connection->beginTransaction(); $sql = "select * from " . $this->table . " where id =? for update"; $result = $this->dbAdapter->query($sql, array($uid)); $userInfo = $result->toArray(); // $updateData = array("is_used" => $used, "used_time" => time()); $updateData = array("is_used" => $used); if ($userInfo[0]["is_used"] == 0) { $this->updateData($this->table, $where, $updateData); $connection->commit(); if (!empty($userInfo)) { return $userInfo[0]; } } else { $connection->commit(); return $this->getRandomUser($used); } }
/** * @todo add $columns support * * @param Insert $insert * @return int * @throws Exception\RuntimeException */ protected function executeInsert(Insert $insert) { $insertState = $insert->getRawState(); if ($insertState['table'] != $this->table) { throw new Exception\RuntimeException('The table name of the provided Insert object must match that of the table'); } // apply preInsert features $this->featureSet->apply(EventFeatureEventsInterface::EVENT_PRE_INSERT, [$insert]); // Most RDBMS solutions do not allow using table aliases in INSERTs // See https://github.com/zendframework/zf2/issues/7311 $unaliasedTable = false; if (is_array($insertState['table'])) { $tableData = array_values($insertState['table']); $unaliasedTable = array_shift($tableData); $insert->into($unaliasedTable); } $statement = $this->sql->prepareStatementForSqlObject($insert); $result = $statement->execute(); $this->lastInsertValue = $this->adapter->getDriver()->getConnection()->getLastGeneratedValue(); // apply postInsert features $this->featureSet->apply(EventFeatureEventsInterface::EVENT_POST_INSERT, [$statement, $result]); // Reset original table information in Insert instance, if necessary if ($unaliasedTable) { $insert->into($insertState['table']); } return $result->getAffectedRows(); }
public function rollbackTransaction() { self::$transactionCounter--; if (!self::$transactionCounter) { $connection = $this->adapter->getDriver()->getConnection(); $connection->rollback(); } }
/** * {@inheritDoc} * * @return StatementContainerInterface */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $statementContainer->setSql($this->buildSqlString($adapter->getPlatform(), $adapter->getDriver(), $parameterContainer)); return $statementContainer; }
/** * @param AdapterInterface $adapter * @param DebugBar $debugbar */ protected function prepareDbAdapter(AdapterInterface $adapter, DebugBar $debugbar) { $driver = $adapter->getDriver(); if ($driver instanceof Pdo) { $pdo = $driver->getConnection()->getResource(); $traceablePdo = new TraceablePDO($pdo); $pdoCollector = new PDOCollector($traceablePdo); $debugbar->addCollector($pdoCollector); } }
/** * @param PreparableSqlInterface $sqlObject * @param StatementInterface|null $statement * @return StatementInterface */ public function prepareStatementForSqlObject(PreparableSqlInterface $sqlObject, StatementInterface $statement = null) { $statement = $statement ?: $this->adapter->getDriver()->createStatement(); if ($this->sqlPlatform) { $this->sqlPlatform->setSubject($sqlObject); $this->sqlPlatform->prepareStatement($this->adapter, $statement); } else { $sqlObject->prepareStatement($this->adapter, $statement); } return $statement; }
/** * Generates PHP files with field names as constants */ public function generateDbConstants() { $metadata = new \Zend\Db\Metadata\Metadata($this->dbAdapter); $schema = $this->dbAdapter->getDriver()->getConnection()->getCurrentSchema(); $tables = $metadata->getTableNames($schema, true); foreach ($tables as $table) { $words = explode('_', $table); $class = 'Db'; foreach ($words as $word) { $word[0] = strtoupper($word[0]); $class = $class . $word; } $filename = __DIR__ . '/../Utils/DbConsts/' . $class . '.php'; if (file_exists($filename)) { unlink($filename); } $writer = new \Zend\Log\Writer\Stream($filename); $writer->setFormatter(new \Zend\Log\Formatter\Simple('%message%')); $logger = new \Zend\Log\Logger(); $logger->addWriter($writer); $logger->info('<?php'); $logger->info(''); $logger->info('namespace Application\\Utils\\DbConsts;'); $logger->info(''); $logger->info("class {$class}"); $logger->info('{'); $logger->info(" const TABLE = '{$table}';"); $columns = $metadata->getColumnNames($table, $schema); foreach ($columns as $column) { $logger->info(vsprintf(" const %s = '%s';", array(strtoupper($column), $column))); } $logger->info(''); $hasConst = ' static public function hasField($field) { if (!is_string($field)) { return false; } $field = strtoupper($field); $reflect = new \\ReflectionClass(__CLASS__); foreach ($reflect->getConstants() as $name => $value) { if (strtoupper($value) === $field) { return true; } }; return false; }'; $logger->info($hasConst); $logger->info('}'); $logger = null; chmod($filename, 0777); } }
/** * @todo add $columns support * * @param Insert $insert * @return mixed * @throws Exception\RuntimeException */ protected function executeInsert(Insert $insert) { $insertState = $insert->getRawState(); if ($insertState['table'] != $this->table) { throw new Exception\RuntimeException('The table name of the provided Insert object must match that of the table'); } // apply preInsert features $this->featureSet->apply('preInsert', array($insert)); $statement = $this->sql->prepareStatementForSqlObject($insert); $result = $statement->execute(); $this->lastInsertValue = $this->adapter->getDriver()->getConnection()->getLastGeneratedValue(); // apply postInsert features $this->featureSet->apply('postInsert', array($statement, $result)); return $result->getAffectedRows(); }
/** * @param array $fields * @param array $orders * @param null|int $limit * @param null|int $offset * * @return ResultSet|ResultSetInterface * @throws RuntimeException * @throws \Exception */ public function find($fields = [], $orders = [], $limit = null, $offset = null) { if (!is_array($fields) || !is_array($orders)) { throw new \Exception("Wrong input type of parameters !"); } if ($this->profiler) { $this->profiler->profilerStart($this); } // apply preSelect features // $this -> featureSet -> apply( 'preSelect', array( $select ) ); $return = $this->collection->find($this->_where($fields)); foreach ($orders as $_k => $_v) { if (strtolower($_v) == 'desc' || $_v < 0) { $orders[$_k] = -1; } else { $orders[$_k] = 1; } } if (!empty($orders)) { $return->sort($this->_orders($orders)); } if ($limit !== null) { $return->limit($limit); } if ($offset !== null) { $return->skip($offset); } if ($this->profiler) { $this->profiler->profilerFinish(); } if ($return === false) { throw new RuntimeException($this->getDriver()->getConnection()->getDB()->lastError()); } $result = $this->adapter->getDriver()->createResult($return, $this->getTable()); $resultSet = clone $this->resultSetPrototype; $resultSet->initialize($result); // apply postSelect features // $this -> featureSet -> apply( 'postSelect', array( $result, $resultSet ) ); return $resultSet; }
/** * Prepare statement * * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer * @return void */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $table = $this->table; $schema = null; // create quoted table name to use in insert processing if ($table instanceof TableIdentifier) { list($table, $schema) = $table->getTableAndSchema(); } $table = $platform->quoteIdentifier($table); if ($schema) { $table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table; } $columns = array(); $values = array(); foreach ($this->columns as $cIndex => $column) { $columns[$cIndex] = $platform->quoteIdentifier($column); if (isset($this->values[$cIndex]) && $this->values[$cIndex] instanceof Expression) { $exprData = $this->processExpression($this->values[$cIndex], $platform, $driver); $values[$cIndex] = $exprData->getSql(); $parameterContainer->merge($exprData->getParameterContainer()); } else { $values[$cIndex] = $driver->formatParameterName($column); if (isset($this->values[$cIndex])) { $parameterContainer->offsetSet($column, $this->values[$cIndex]); } else { $parameterContainer->offsetSet($column, null); } } } $sql = sprintf($this->specifications[self::SPECIFICATION_INSERT], $table, implode(', ', $columns), implode(', ', $values)); $statementContainer->setSql($sql); }
/** * Prepare statement * * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer * @return void */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $table = $this->table; $schema = null; // create quoted table name to use in update processing if ($table instanceof TableIdentifier) { list($table, $schema) = $table->getTableAndSchema(); } $table = $platform->quoteIdentifier($table); if ($schema) { $table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table; } $set = $this->set; if (is_array($set)) { $setSql = array(); foreach ($set as $column => $value) { if ($value instanceof Expression) { $exprData = $this->processExpression($value, $platform, $driver); $setSql[] = $platform->quoteIdentifier($column) . ' = ' . $exprData->getSql(); $parameterContainer->merge($exprData->getParameterContainer()); } else { $setSql[] = $platform->quoteIdentifier($column) . ' = ' . $driver->formatParameterName($column); $parameterContainer->offsetSet($column, $value); } } $set = implode(', ', $setSql); } $sql = sprintf($this->specifications[static::SPECIFICATION_UPDATE], $table, $set); // process where if ($this->where->count() > 0) { $whereParts = $this->processExpression($this->where, $platform, $driver, 'where'); $parameterContainer->merge($whereParts->getParameterContainer()); $sql .= ' ' . sprintf($this->specifications[static::SPECIFICATION_WHERE], $whereParts->getSql()); } $statementContainer->setSql($sql); }
/** * Prepare the delete statement * * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer * @return void */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $table = $this->table; $schema = null; // create quoted table name to use in delete processing if ($table instanceof TableIdentifier) { list($table, $schema) = $table->getTableAndSchema(); } $table = $platform->quoteIdentifier($table); if ($schema) { $table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table; } $sql = sprintf($this->specifications[static::SPECIFICATION_DELETE], $table); // process where if ($this->where->count() > 0) { $whereParts = $this->processExpression($this->where, $platform, $driver, 'where'); $parameterContainer->merge($whereParts->getParameterContainer()); $sql .= ' ' . sprintf($this->specifications[static::SPECIFICATION_WHERE], $whereParts->getSql()); } $statementContainer->setSql($sql); }
/** * Prepare statement * * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer * @return void */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { // ensure statement has a ParameterContainer $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $sqls = array(); $parameters = array(); $platform = $adapter->getPlatform(); $driver = $adapter->getDriver(); foreach ($this->specifications as $name => $specification) { $parameters[$name] = $this->{'process' . $name}($platform, $driver, $parameterContainer, $sqls, $parameters); if ($specification && is_array($parameters[$name])) { $sqls[$name] = $this->createSqlFromSpecificationAndParameters($specification, $parameters[$name]); } } $sql = implode(' ', $sqls); $statementContainer->setSql($sql); return; }
/** * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer * @return void */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $function = $platform->quoteIdentifierChain($this->function); $resultKey = $platform->quoteIdentifier($this->resultKey); $arguments = array(); $i = 0; foreach ($this->arguments as $argument) { if ($argument instanceof Expression) { $exprData = $this->processExpression($argument, $platform, $driver); $arguments[] = $exprData->getSql(); $parameterContainer->merge($exprData->getParameterContainer()); } else { $parameterName = 'func_arg_' . $i++; $arguments[] = $driver->formatParameterName($parameterName); $parameterContainer->offsetSet($parameterName, $argument); } } $sql = sprintf($this->specifications[$this->mode], $function, implode(', ', $arguments), $resultKey); $statementContainer->setSql($sql); }
/** * Prepare statement * * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer * @return void */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { // ensure statement has a ParameterContainer $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $sqls = array(); $sqls[self::SHOW] = sprintf($this->specifications[static::SHOW], $this->show); $likePart = $this->processLike($adapter->getPlatform(), $adapter->getDriver(), $parameterContainer); if (is_array($likePart)) { $sqls[self::LIKE] = $this->createSqlFromSpecificationAndParameters($this->specifications[static::LIKE], $likePart); } $sql = implode(' ', $sqls); $statementContainer->setSql($sql); return; }
/** * Prepare statement * * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer * @return void */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $table = $this->table; $table = $platform->quoteIdentifier($table); $set = $this->set; $setSql = array(); foreach ($set as $column => $value) { if ($value instanceof Predicate\Expression) { $exprData = $this->processExpression($value, $platform, $driver); $setSql[] = $platform->quoteIdentifier($column) . ' = ' . $exprData->getSql(); $parameterContainer->merge($exprData->getParameterContainer()); } else { $setSql[] = $platform->quoteIdentifier($column) . ' = ' . $driver->formatParameterName($column); $parameterContainer->offsetSet($column, $value); } } $set = implode(', ', $setSql); $sql = sprintf($this->specifications[self::SPECIFICATION_UPDATE], $table, $set); // Process where if ($this->where->count() > 0) { $whereParts = $this->processExpression($this->where, $platform, $driver, 'where'); $parameterContainer->merge($whereParts->getParameterContainer()); $sql .= ' ' . sprintf($this->specifications[self::SPECIFICATION_WHERE], $whereParts->getSql()); } // Process option $optionParts = $this->processOption($platform, $driver, $parameterContainer); if (is_array($optionParts)) { $sql .= ' ' . $this->createSqlFromSpecificationAndParameters($this->specifications[self::SPECIFICATION_OPTION], $optionParts); } $statementContainer->setSql($sql); }