/**
* @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'])));
}
