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); } }
public function __construct(AdapterInterface $adapter) { $this->adapter = $adapter; $platform = $adapter->getPlatform(); switch (strtolower($platform->getName())) { case 'mysql': $platform = new Mysql\Mysql(); $this->decorators = $platform->decorators; break; case 'sqlserver': $platform = new SqlServer\SqlServer(); $this->decorators = $platform->decorators; break; case 'oracle': $platform = new Oracle\Oracle(); $this->decorators = $platform->decorators; break; case 'ibm db2': case 'ibm_db2': case 'ibmdb2': $platform = new IbmDb2\IbmDb2(); $this->decorators = $platform->decorators; default: } }
public function industries(AdapterInterface $adapter) { $sql = "SELECT industries_id,industry_name from industries where status=1 ORDER by xorder ASC"; $statement = $adapter->query($sql); $result = $statement->execute(); return $result; }
public function getNewRFollowID() { $sql = "select max(rfollowID) from rfollow"; // echo $sql; $statement = $this->dbAdapter->query($sql); $result = $statement->execute(); return $result->current()['max(rfollowID)'] + 1; }
/** * @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); } }
/** * {@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; }
public function UnsubscribeList(AdapterInterface $adapter, $from = null, $to = null) { $sql = 'SELECT * FROM user_unsubscribes'; $array_data = array(); if (!empty($from) && !empty($to)) { $sql .= ' where created_at >= ? and created_at <= ?'; $array_data = array($from, $to); } $statement = $adapter->createStatement($sql, $array_data); $result = $statement->execute(); return $result; }
/** * @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(); } }
public function getNewPageID() { $sql1 = 'select max(pageID) from page'; $statement = $this->dbAdapter->query($sql1); $result = $statement->execute(); $row = $result->current(); // Debug::dump($row ); return $row['max(pageID)'] + 1; }
/** * Get sql string using platform or sql object * * @param SqlInterface $sqlObject * @param PlatformInterface $platform * * @return string */ public function getSqlStringForSqlObject(SqlInterface $sqlObject, PlatformInterface $platform = null) { $platform = $platform ?: $this->adapter->getPlatform(); if ($this->sqlPlatform) { $this->sqlPlatform->setSubject($sqlObject); return $this->sqlPlatform->getSqlString($platform); } return $sqlObject->getSqlString($platform); }
/** * 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); } }
public function __construct(AdapterInterface $adapter) { $this->adapter = $adapter; $platform = $adapter->getPlatform(); switch (strtolower($platform->getName())) { case 'mysql': $platform = new ZfPlatform\Mysql\Mysql(); $this->decorators = $platform->decorators; $this->setTypeDecorator('Zend\\Db\\Sql\\Select', new Mysql\SelectDecorator()); break; case 'sqlserver': $platform = new ZfPlatform\SqlServer\SqlServer(); $this->decorators = $platform->decorators; $this->setTypeDecorator('Zend\\Db\\Sql\\Select', new SqlServer\SelectDecorator()); break; case 'oracle': $platform = new ZfPlatform\Oracle\Oracle(); $this->decorators = $platform->decorators; $this->setTypeDecorator('Zend\\Db\\Sql\\Select', new Oracle\SelectDecorator()); break; default: } }
public function getCategories($categoryId = null) { $sql = 'SELECT * FROM blog.view_posts_categories'; if ($categoryId != null) { $sql .= ' WHERE id = :id LIMIT 1'; } $stmt = $this->dbAdapter->createStatement($sql); $params = array(); if ($categoryId != null) { $params['id'] = $categoryId; } $result = $stmt->execute($params); return iterator_to_array($result); }
/** * @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(); }
/** * Returns result of a query if it's successful and false otherwise * @param Sql $sql * @param Select $select * @param int $offset * @param int $limit * @return boolean|ResultInterface */ protected function fetch(Sql $sql, Select $select, $offset = 0, $limit = 0) { $this->logQuery($select); if ($offset > 0) { $select->offset($offset); } if ($limit > 0) { $select->limit($limit); } $stmt = $sql->prepareStatementForSqlObject($select); $profiler = $this->dbAdapter->getProfiler(); if ($profiler) { $profiler->profilerStart($stmt); $result = $stmt->execute(); $profile = $profiler->getLastProfile(); $this->logQuery($profile); } else { $result = $stmt->execute(); } if ($result instanceof ResultInterface && $result->isQueryResult()) { return $result; } return false; }
/** * 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; }
public function findRec() { if (WAuthUtil::get_auth() !== null) { $userId = WAuthUtil::get_auth()->userID; $sql = "select page.*,predictRating,user.* from (recs join page on recs.pageId = page.pageID)join user on recs.userId = user.userId where \n recs.userId = {$userId} and predictRating <> 0 order by predictRating desc"; $statement = $this->dbAdapter->query($sql); $result = $statement->execute(); if ($result instanceof ResultInterface && $result->isQueryResult()) { $resultSet = new WHydrateResultset($this->hydrator, $this->pagePrototype, $this->prototypeArr); $tmp = $resultSet->initialize($result); // foreach ($resultSet as $row){ // Debug::dump($row); // } return $tmp; } throw new \InvalidArgumentException("Forum with given ID:{$id} not found."); } else { return null; } }
/** * @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; }
/** * @param SqlInterface $sqlObject * @param AdapterInterface $adapter * * @return string * * @throws Exception\InvalidArgumentException */ public function buildSqlString(SqlInterface $sqlObject, AdapterInterface $adapter = null) { return $this->sqlPlatform->setSubject($sqlObject)->getSqlString($adapter ? $adapter->getPlatform() : $this->adapter->getPlatform()); }
/** * 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 null|PlatformInterface|AdapterInterface $adapterOrPlatform * * @return PlatformInterface * * @throws Exception\InvalidArgumentException */ protected function resolvePlatform($adapterOrPlatform) { if (!$adapterOrPlatform) { return $this->getDefaultPlatform(); } if ($adapterOrPlatform instanceof AdapterInterface) { return $adapterOrPlatform->getPlatform(); } if ($adapterOrPlatform instanceof PlatformInterface) { return $adapterOrPlatform; } throw new Exception\InvalidArgumentException(sprintf('$adapterOrPlatform should be null, %s, or %s', 'Zend\\Db\\Adapter\\AdapterInterface', 'Zend\\Db\\Adapter\\Platform\\PlatformInterface')); }
public function __construct(AdapterInterface $adapter) { $this->adapter = $adapter; $this->defaultSchema = $adapter->getCurrentSchema() ?: self::DEFAULT_SCHEMA; }
public function __invoke($value = null) { return 'Platform name ' . $this->dbAdapter->getPlatform()->getName(); }
/** * 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 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); }
public function childModuleAccessRights(AdapterInterface $adapter, $module_id, $function_name) { //Get user credentials $session = new Container('base'); $user = $session->offsetGet('user'); $this->adapter = $adapter; $statement = $adapter->query("SELECT `module_name`, function_name\n FROM\n `app_sub_accesses`\n LEFT JOIN\n `app_modules`\n ON\n `app_modules`.`app_modules_id` = `app_sub_accesses`.`module_id`\n LEFT JOIN\n `app_roles`\n ON\n `app_sub_accesses`.`role_id` = `app_roles`.`app_roles_id`\n WHERE\n `app_roles`.`app_roles_id`= " . $user['role_id'] . "\n AND `app_modules`.`app_modules_id` = {$module_id}\n AND `app_roles`.`status`=1\n AND function_name='" . $function_name . "'\n "); $result = $statement->execute(); //Count number of records if (count($result) > 0) { return true; } else { return false; } }
/** * Set the database adapter. * * @param AdapterInterface $adapter * @return AbstractMapper */ public function setAdapter(AdapterInterface $adapter) { $this->adapter = $adapter; if ($adapter instanceof MasterSlaveAdapterInterface) { $this->slaveAdapter = $adapter->getSlaveAdapter(); } return $this; }
/** * @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); }