/** * @return int */ public function getTotalCount() { $statement = $this->adapter->createStatement('SELECT COUNT(id) as cnt FROM ' . $this->table); $results = $statement->execute(); $row = $results->current(); return is_array($row) && isset($row['cnt']) ? (int) $row['cnt'] : 0; }
/** * Executes query with prepared statement * @param $query * @param array $params * @return array|bool|int * @deprecated */ public function executePreparedQuery($query, array $params = []) { $this->logQuery($query, $params); try { $statement = $this->_adapter->createStatement($this->fixTableName($query)); $res = $statement->execute($params); $this->safeDropCache($query); } catch (\Exception $e) { $this->result = new ResultSet(ResultSet::TYPE_ARRAY, []); error_log("ERROR: ADB : executePreparedQuery"); error_log("query: " . $this->getFullQuery($query, $params)); error_log($e->getTraceAsString()); return false; } $this->result = $res; if ($res->isQueryResult()) { $res->rewind(); $returnArray = []; while ($res->valid()) { $returnArray[] = $res->current(); $res->next(); } } else { $returnArray = $res->getAffectedRows(); } return $returnArray; }
/** * @param int $offset * @param int $limit * @param string $order * @return TagCollection */ public function findTags($offset = 0, $limit = PHP_INT_MAX, $order = 'id ASC') { $sql = new Sql($this->adapter); $select = $sql->select(); $select->from('tag'); $select->offset($offset); $select->limit($limit); $select->order($order); $statement = $sql->prepareStatementForSqlObject($select); $result = $statement->execute(); $selectStatement = $this->adapter->createStatement('SELECT FOUND_ROWS() as total;'); $total = $selectStatement->execute(); $total = (int) $total->current(); $collection = new TagCollection(); $collection->setTotalCount($total); foreach ($result as $row) { $entity = $this->createEntity($row); $collection->addElement($entity, $entity->getId()); } return $collection; }
/** * _authenticateQuerySelect() - This method accepts a Zend\Db\Sql\Select object and * performs a query against the database with that object. * * @param DbSelect $dbSelect * @throws Exception\RuntimeException when an invalid select object is encountered * @return array */ protected function _authenticateQuerySelect(DbSelect $dbSelect) { $statement = $this->zendDb->createStatement(); $dbSelect->prepareStatement($this->zendDb, $statement); $resultSet = new ResultSet(); try { $resultSet->initialize($statement->execute(array($this->credential, $this->identity))); $resultIdentities = $resultSet->toArray(); } catch (\Exception $e) { throw new Exception\RuntimeException('The supplied parameters to DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.', 0, $e); } return $resultIdentities; }
/** * Delete * * @param Closure $where * @return type */ public function delete($where) { $delete = clone $this->sqlDelete; $delete->from($this->tableName, $this->databaseSchema); if ($where instanceof \Closure) { $where($delete); } else { $delete->where($where); } $statement = $this->adapter->createStatement(); $delete->prepareStatement($this->adapter, $statement); $result = $statement->execute(); return $result->getAffectedRows(); }
/** * @param $table - table name * @param $colName - column name * @return array|bool - return list of constrains or false, if constrains not exist */ protected function getConstraintForColumn($table, $colName) { $rows = $this->db->createStatement("select database() as dbname")->execute(); $row = $rows->current(); $dbName = $row['dbname']; $sql = "SELECT k.CONSTRAINT_SCHEMA,k.CONSTRAINT_NAME," . "k.TABLE_NAME,k.COLUMN_NAME,k.REFERENCED_TABLE_NAME," . "k.REFERENCED_COLUMN_NAME, r.UPDATE_RULE, r.DELETE_RULE FROM " . "information_schema.key_column_usage k LEFT JOIN " . "information_schema.referential_constraints r ON " . "r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND " . " k.REFERENCED_TABLE_NAME=r.REFERENCED_TABLE_NAME " . "LEFT JOIN information_schema.table_constraints t ON " . "t.CONSTRAINT_SCHEMA = r.CONSTRAINT_SCHEMA WHERE " . " k.constraint_schema='{$dbName}' AND t.CONSTRAINT_TYPE='FOREIGN KEY' " . "AND k.TABLE_NAME='{$table}' AND r.TABLE_NAME='{$table}' " . " AND t.TABLE_NAME='{$table}' AND k.COLUMN_NAME='{$colName}'"; $rows = $this->db->createStatement($sql)->execute(); $row = $rows->current(); if (!count($row)) { return false; } $constraint = array('table' => $table, 'name' => $row['CONSTRAINT_NAME'], 'column' => $row['COLUMN_NAME'], 'reference' => array('table' => $row['REFERENCED_TABLE_NAME'], 'column' => $row['REFERENCED_COLUMN_NAME'], 'update' => $row['UPDATE_RULE'], 'delete' => $row['DELETE_RULE'])); return $constraint; }
public function reinstall($config) { $adapter = new Adapter($config); $adapter->createStatement('DROP DATABASE IF EXISTS `' . $config['database'] . '`')->execute(); $config_file = 'config/autoload/local.php'; $old_config = array(); if (file_exists($config_file)) { $old_config = (include $config_file); unset($old_config['db']); unlink($config_file); $writer = new ConfigWriter(); $writer->toFile($config_file, new Config($old_config)); } $this->install($config); }
/** * @testdox unit test: Test createStatement() produces a statement object * @covers Zend\Db\Adapter\Adapter::createStatement */ public function testCreateStatement() { $this->assertSame($this->mockStatement, $this->adapter->createStatement()); }
private function dbQuery($sql) { return $this->adapter->createStatement($sql)->execute(); }
/** * * @param Options $options * @throws Exception\EmptyQueryException * @throws Exception\ErrorException * @return ResultSet */ public function getData(Options $options = null) { if ($options === null) { $options = $this->getOptions(); } $select = $this->assignOptions(clone $this->getSelect(), $options); $sql = new Sql($this->adapter); $sql_string = (string) $sql->getSqlStringForSqlObject($select); //echo $this->select->getSqlString($this->adapter->getPlatform()); //echo "----" . var_dump($sql_string) . "----\n"; // In ZF 2.3.0 an empty query will return SELECT .* // In ZF 2.4.0 and empty query will return SELECT * if (in_array($sql_string, array('', 'SELECT .*', 'SELECT *'))) { throw new Exception\EmptyQueryException(__METHOD__ . ': Cannot return data of an empty query'); } $this->query_string = $sql_string; // In case of unbuffered results (default on mysqli) !!! // Seems to not be needed anymore in ZF 2.3+ // Uncomment if necessary, see also below is_mysqli /* $is_mysqli = false; $driver = $this->adapter->getDriver(); if (false && $driver instanceof \Zend\Db\Adapter\Driver\Mysqli\Mysqli) { $stmt_prototype_backup = $driver->getStatementPrototype(); if (self::$cache_stmt_prototype === null) { // With buffer results self::$cache_stmt_prototype = new \Zend\Db\Adapter\Driver\Mysqli\Statement($buffer=true); } $driver->registerStatementPrototype(self::$cache_stmt_prototype); $is_mysqli = true; } */ /** * Check whether there's a column model */ /* $limit_columns = false; $renderers = false; if ($this->columnModel !== null) { // TODO: optimize when the column model haven't been modified. $limit_columns = $this->columnModel->getColumns(); $renderers = $this->columnModel->getRowRenderers(); } */ //$cm = $this->getColumnModel(); //$cm->setExcluded(array('user_id')); //$this->columns = $cm->getColumns(); //var_dump($this->columns); //die(); try { $results = $this->adapter->query($sql_string, Adapter::QUERY_MODE_EXECUTE); //$stmt = $sql->prepareStatementForSqlObject( $select ); //$results = $stmt->execute(); //var_dump(get_class($results)); $r = new ResultSet($results); $r->setSource($this); $r->setHydrationOptions($options->getHydrationOptions()); if ($options->hasLimit()) { //$row = $this->adapter->query('select FOUND_ROWS() as total_count')->execute()->current(); $row = $this->adapter->createStatement('select FOUND_ROWS() as total_count')->execute()->current(); $r->setTotalRows($row['total_count']); } else { $r->setTotalRows($r->count()); } // restore result prototype // $this->adapter->getDriver()->registerResultPrototype($result_prototype_backup); // restore statement prototype // seems not needed in zf 2.3 /* if ($is_mysqli) { $this->adapter->getDriver()->registerStatementPrototype($stmt_prototype_backup); } */ } catch (\Exception $e) { // restore result prototype //$this->adapter->getDriver()->registerResultPrototype($result_prototype_backup); // seems not needed in zf 2.3 /* if ($is_mysqli) { $this->adapter->getDriver()->registerStatementPrototype($stmt_prototype_backup); } */ throw new Exception\ErrorException(__METHOD__ . ': Cannot retrieve data (' . $e->getMessage() . ')'); } return $r; }
/** * Obtain a Zend\DB connection using an option array. * * @param array $options Options for building adapter * * @return Adapter */ public function getAdapterFromOptions($options) { // Set up custom options by database type: $driver = strtolower($options['driver']); switch ($driver) { case 'mysqli': $options['charset'] = isset($this->config->Database->charset) ? $this->config->Database->charset : 'utf8'; $options['options'] = ['buffer_results' => true]; break; } // Set up database connection: $adapter = new Adapter($options); // Special-case setup: if ($driver == 'pdo_pgsql' && isset($this->config->Database->schema)) { // Set schema $statement = $adapter->createStatement('SET search_path TO ' . $this->config->Database->schema); $statement->execute(); } return $adapter; }
/** * Create users and roles * * @param \Zend\Db\Adapter\Adapter $dbAdapter Database adapter * @param array $configuration Configuration * @param string $sqlType Sql database type * * @return \Zend\View\Model\JsonModel|null */ protected function createUsersAndRoles($dbAdapter, $configuration, $sqlType) { //Create role $roles = (include GC_APPLICATION_PATH . '/data/install/acl/roles.php'); try { foreach ($roles['role'] as $key => $value) { $statement = $dbAdapter->createStatement("INSERT INTO user_acl_role (name) VALUES ('" . $value . "')"); $result = $statement->execute(); } } catch (Exception $e) { return $this->returnJson(array('messages' => $e->getMessage())); } //resources $resources = (include GC_APPLICATION_PATH . '/data/install/acl/resources.php'); try { foreach ($resources as $key => $value) { $statement = $dbAdapter->createStatement("INSERT INTO user_acl_resource (resource) VALUES ('" . $key . "')"); $result = $statement->execute(); $statement = $dbAdapter->createStatement("SELECT id FROM user_acl_resource WHERE resource = '" . $key . "'"); $result = $statement->execute(); $lastInsertId = $result->current(); $lastInsertId = $lastInsertId['id']; $permissions = array(); foreach ($value as $k => $v) { if (!in_array($k, $permissions)) { $statement = $dbAdapter->createStatement("INSERT INTO user_acl_permission\n (\n permission,\n user_acl_resource_id\n )\n VALUES ('" . $k . "', '" . $lastInsertId . "')"); $result = $statement->execute(); $permissions[] = $k; } } } foreach ($resources as $key => $value) { $statement = $dbAdapter->createStatement("SELECT id FROM user_acl_resource WHERE resource = '" . $key . "'"); $result = $statement->execute(); $lastResourceInsertId = $result->current(); $lastResourceInsertId = $lastResourceInsertId['id']; foreach ($value as $k => $v) { $statement = $dbAdapter->createStatement("SELECT id\n FROM user_acl_permission\n WHERE permission = '" . $k . "'\n AND user_acl_resource_id = '" . $lastResourceInsertId . "'"); $result = $statement->execute(); $lastInsertId = $result->current(); $lastInsertId = $lastInsertId['id']; $statement = $dbAdapter->createStatement("SELECT id FROM user_acl_role WHERE name = '" . $v . "'"); $result = $statement->execute(); $role = $result->current(); if (!empty($role['id'])) { $statement = $dbAdapter->createStatement("INSERT INTO user_acl\n (\n user_acl_role_id,\n user_acl_permission_id\n )\n VALUES ('" . $role['id'] . "', " . $lastInsertId . ')'); $result = $statement->execute(); } } } } catch (Exception $e) { return $this->returnJson(array('messages' => $e->getMessage())); } //Add admin user if ($sqlType == 'mysql') { $sqlString = 'INSERT INTO `user` ( created_at, updated_at, lastname, firstname, email, login, password, user_acl_role_id ) VALUES (NOW(), NOW(), ?, ?, ?, ?, ?, 1)'; } else { $sqlString = 'INSERT INTO "user" ( created_at, updated_at, lastname, firstname, email, login, password, user_acl_role_id ) VALUES (NOW(), NOW(), ?, ?, ?, ?, ?, 1)'; } $dbAdapter->query($sqlString, array($configuration['admin_lastname'], $configuration['admin_firstname'], $configuration['admin_email'], $configuration['admin_login'], sha1($configuration['admin_password']))); }