public function find($params) { $sql = new Sql($this->getAdapter()); $select = new Select(); $select->from($params['from']); if (!empty($params['columns'])) { $select->columns($params['columns']); } foreach ($params['where'] as $where) { $select->where($where); } foreach ($params['joins'] as $join) { if (empty($join['columns'])) { $join['columns'] = Select::SQL_STAR; } if (empty($join['type'])) { $join['type'] = Select::JOIN_INNER; } $select->join($join['name'], $join['on'], $join['columns'], $join['type']); } $query = $sql->getSqlStringForSqlObject($select); $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE); $data = $results->toArray(); if (empty($data)) { return false; } else { if (count($data) == 1) { return $data[0]; } else { return $data; } } }
public function onDispatch(MvcEvent $e) { if (!$e->getRequest() instanceof ConsoleRequest) { throw new RuntimeException('You can only use this action from a console!'); } $table = new Ddl\CreateTable('queue_messages'); $table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true])); $table->addColumn(new Ddl\Column\Varchar('queue_name', 100)); $table->addColumn(new Ddl\Column\Integer('status', false)); $table->addColumn(new Ddl\Column\Varchar('options', 250)); $table->addColumn(new Ddl\Column\Text('message', null, true)); $table->addColumn(new Ddl\Column\Text('output', null, true)); $table->addColumn(new Ddl\Column\Datetime('started_dt', true)); $table->addColumn(new Ddl\Column\Datetime('finished_dt', true)); $table->addColumn(new Ddl\Column\Datetime('created_dt', false)); $table->addColumn(new Ddl\Column\Datetime('updated_dt', true)); $table->addConstraint(new Ddl\Constraint\PrimaryKey('id')); $sql = new Sql($this->dbAdapter); try { $this->dbAdapter->query($sql->buildSqlString($table), DbAdapter::QUERY_MODE_EXECUTE); } catch (\Exception $e) { // currently there are no db-independent way to check if table exists // so we assume that table exists when we catch exception } }
private function create(SqlInterface $table) { try { $sql = new Sql($this->dbAdapter); $this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE); } catch (PDOException $e) { $message = $e->getMessage() . PHP_EOL; } }
public function executeQuery($sql) { if (!$this->dbAdapter) { /** @var Adapter dbAdapter */ $this->dbAdapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter'); } /** @var ResultSet $photos */ return $this->dbAdapter->query($sql, Adapter::QUERY_MODE_EXECUTE); }
/** * * @param string $tableName * @param array $data * @return bool */ public function insert($tableName, array $data) { $sql = new Sql($this->adapter); $insert = $sql->insert($tableName); $insert->values($data); $sqlString = $sql->getSqlStringForSqlObject($insert); $results = $this->adapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE); return $results; }
/** * Load config from db-adapter * * @param \Zend\Db\Adapter\Adapter $db * @return array */ protected function loadConfig() { if (empty($this->dbAdapter)) { return $this->config; } $platform = $this->dbAdapter->getPlatform(); $driver = $this->dbAdapter->getDriver(); $query = $this->dbAdapter->query(' SELECT ' . $platform->quoteIdentifier('value') . ' FROM ' . $platform->quoteIdentifier('settings') . ' WHERE ' . $platform->quoteIdentifier('key') . ' = ' . $platform->quoteValue('ini-cache') . ' AND ' . $platform->quoteIdentifier('type') . ' = ' . $platform->quoteValue('ini-cache') . ' LIMIT 1 '); $this->config = array(); $result = $query->execute(); if ($result->getAffectedRows() > 0) { foreach ($result as $cache) { $this->config = ArrayUtils::merge($this->config, (array) unserialize($cache['value'])); } } else { $query = $this->dbAdapter->query(' SELECT ' . $platform->quoteIdentifier('key') . ', ' . $platform->quoteIdentifier('value') . ' FROM ' . $platform->quoteIdentifier('settings') . ' WHERE ' . $platform->quoteIdentifier('type') . ' = ' . $platform->quoteValue('ini') . ' '); foreach ($query->execute() as $pair) { $key = (string) $pair['key']; $value = (string) $pair['value']; $entry = array(); $curr =& $entry; foreach (explode('.', $key) as $sub) { $curr[$sub] = null; $curr =& $curr[$sub]; } $curr = $value; $this->config = ArrayUtils::merge($this->config, $entry); } $query = $this->dbAdapter->query(' INSERT INTO ' . $platform->quoteIdentifier('settings') . ' ( ' . $platform->quoteIdentifier('key') . ', ' . $platform->quoteIdentifier('value') . ', ' . $platform->quoteIdentifier('type') . ' ) VALUES ( ' . $driver->formatParameterName('key') . ', ' . $driver->formatParameterName('value') . ', ' . $driver->formatParameterName('type') . ' ) '); $query->execute(array('key' => 'ini-cache', 'value' => serialize($this->config), 'type' => 'ini-cache')); } $this->dbAdapter = null; return $this->config; }
public function save(Queue $queue) { if ($queue->getId()) { $query = $this->dbAdapter->query('UPDATE `queues` SET `name` = :name WHERE `id = :id`'); $query->execute($queue->getArrayCopy()); } else { $query = $this->dbAdapter->query("INSERT INTO `queues` (`name`) VALUES (:name)"); $query->execute(['name' => $queue->getName()]); $queue->setId($this->dbAdapter->getDriver()->getLastGeneratedValue()); } return $queue; }
/** * Clear datas with $uid key * @param mixed $uid * @return void */ public function clear($uid = null) { $options = $this->getOptions(); if ($uid) { if (!$this->has($uid)) { return false; } $stmt = $this->adapter->query(sprintf('DELETE FROM %s WHERE %s = "%s"', $options['table'], $options['column_key'], $uid), Adapter::QUERY_MODE_EXECUTE); return true; } $stmt = $this->adapter->query(sprintf('TRUNCATE TABLE %s', $options['table']), Adapter::QUERY_MODE_EXECUTE); return true; }
private function createTable($query) { try { $this->dbAdapter->query($query, DbAdapter::QUERY_MODE_EXECUTE); } catch (\PDOException $e) { if (strpos($e->getMessage(), 'table or view already exists') === false) { echo $e->getMessage() . PHP_EOL; exit(1); } } catch (\Exception $e) { echo $e->getMessage() . PHP_EOL; exit(1); } }
public function save(User $user) { //select $sql1 = 'select max(userID) from user'; $statement = $this->dbAdapter->query($sql1); $result = $statement->execute(); $row = $result->current(); // Debug::dump($row ); $user->setUserID($row['max(userID)'] + 1); //insert $defaultfaceimgpath = '/data/face/defaultfaceimg.png'; $sql = 'insert into user(userID,username,upassword,schoolID,faceimgpath) values(' . $user->getUserID() . ',"' . $user->getUsername() . '","' . $user->getUpassword() . '","' . $user->getSchoolID() . '","' . $defaultfaceimgpath . '")'; // echo $sql; $this->dbAdapter->query($sql, Adapter::QUERY_MODE_EXECUTE); }
/** * Autenticates the user * @return \Zend\Authentication\Result */ public function authenticate() { extract($this->db->query(' SELECT COUNT(`username`) as isAuthenticated FROM `login` WHERE (SELECT `active` FROM `profile` WHERE `login`.`profile_id`=`profile`.`id`) = 1 AND `username`="' . addslashes($this->username) . '" AND `password`="' . addslashes(md5($this->password)) . '" ', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE)->current()->getArrayCopy()); if ($isAuthenticated > 0) { $code = \Zend\Authentication\Result::SUCCESS; } else { $code = \Zend\Authentication\Result::FAILURE; } return new \Zend\Authentication\Result($code, $_REQUEST['username']); }
/** * Drops the database table for session data * * @return void */ protected function dropTable() { if (!$this->adapter) { return; } $this->adapter->query('DROP TABLE sessions', Adapter::QUERY_MODE_EXECUTE); }
public function query($sql, $parametersOrQueryMode = self::QUERY_MODE_PREPARE) { $this->getProfiler()->startQuery($sql); $return = parent::query($sql, $parametersOrQueryMode); $this->getProfiler()->endQuery(); return $return; }
public function query($sql, $parametersOrQueryMode = self::QUERY_MODE_PREPARE, ResultSet\ResultSetInterface $resultPrototype = null) { $this->getProfiler()->startQuery($sql); $return = parent::query($sql, $parametersOrQueryMode, $resultPrototype); $this->getProfiler()->endQuery(); return $return; }
private function createTableLanguages() { $table = new Ddl\CreateTable('languages'); $id = new Column\Integer('id'); $id->setOption('autoincrement', true); $table->addColumn($id); $table->addColumn(new Column\Varchar('code', 2)); $table->addColumn(new Column\Varchar('locale', 6)); $table->addColumn(new Column\Varchar('name', 50)); $table->addColumn(new Column\Integer('default', false, 0)); $table->addConstraint(new Constraint\PrimaryKey('id')); $table->addConstraint(new Constraint\UniqueKey('code')); $table->addConstraint(new Constraint\UniqueKey('locale')); $sql = new Sql($this->dbAdapter); $this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE); }
/** * Load constraint references * * @param string $schema * @param string $database * @return type */ protected function loadConstraintReferences($schema, $database) { /** @var $platform \Zend\Db\Adapter\PlatformInterface */ $platform = $this->adapter->getPlatform(); $quoteIdentifierForWalk = function (&$c) use($platform) { $c = $platform->quoteIdentifierInFragment($c); }; $quoteSelectList = function (array $identifierList) use($platform, $quoteIdentifierForWalk) { array_walk($identifierList, $quoteIdentifierForWalk); return implode(', ', $identifierList); }; // target: CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE, REFERENCE_CONSTRAINT_NAME if ($platform->getName() == 'MySQL') { $sql = 'SELECT ' . $quoteSelectList(array('RC.CONSTRAINT_NAME', 'RC.UPDATE_RULE', 'RC.DELETE_RULE', 'RC.TABLE_NAME', 'CK.REFERENCED_TABLE_NAME', 'CK.REFERENCED_COLUMN_NAME')) . ' FROM ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC') . ' INNER JOIN ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.KEY_COLUMN_USAGE CK') . ' ON ' . $platform->quoteIdentifierInFragment('RC.CONSTRAINT_NAME') . ' = ' . $platform->quoteIdentifierInFragment('CK.CONSTRAINT_NAME'); } else { $sql = 'SELECT ' . $quoteSelectList(array('RC.CONSTRAINT_NAME', 'RC.UPDATE_RULE', 'RC.DELETE_RULE', 'TC1.TABLE_NAME', 'CK.TABLE_NAME AS REFERENCED_TABLE_NAME', 'CK.COLUMN_NAME AS REFERENCED_COLUMN_NAME')) . ' FROM ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC') . ' INNER JOIN ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC1') . ' ON ' . $platform->quoteIdentifierInFragment('RC.CONSTRAINT_NAME') . ' = ' . $platform->quoteIdentifierInFragment('TC1.CONSTRAINT_NAME') . ' INNER JOIN ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2') . ' ON ' . $platform->quoteIdentifierInFragment('RC.UNIQUE_CONSTRAINT_NAME') . ' = ' . $platform->quoteIdentifierInFragment('TC2.CONSTRAINT_NAME') . ' INNER JOIN ' . $platform->quoteIdentifierInFragment('INFORMATION_SCHEMA.KEY_COLUMN_USAGE CK') . ' ON ' . $platform->quoteIdentifierInFragment('TC2.CONSTRAINT_NAME') . ' = ' . $platform->quoteIdentifierInFragment('CK.CONSTRAINT_NAME'); } if ($schema != '__DEFAULT_SCHEMA__') { $sql .= ' AND ' . $platform->quoteIdentifierInFragment('RC.CONSTRAINT_SCHEMA') . ' = ' . $platform->quoteValue($schema); } $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE); $constraintRefData = array(); foreach ($results->toArray() as $row) { $constraintRefData[] = array_change_key_case($row, CASE_LOWER); } $this->prepareDataHeirarchy($database, $schema, array('constraints', 'keys')); $this->data[$database][$schema]['constraints']['references'] = $constraintRefData; }
protected function _setupDbAdapter($optionalParams = array()) { $params = array('driver' => 'pdo_sqlite', 'dbname' => TESTS_ZEND_AUTH_ADAPTER_DBTABLE_PDO_SQLITE_DATABASE); if (!empty($optionalParams)) { $params['options'] = $optionalParams; } $this->_db = new DbAdapter($params); $sqlCreate = 'CREATE TABLE IF NOT EXISTS [users] ( ' . '[id] INTEGER NOT NULL PRIMARY KEY, ' . '[username] VARCHAR(50) NOT NULL, ' . '[password] VARCHAR(32) NULL, ' . '[real_name] VARCHAR(150) NULL)'; $this->_db->query($sqlCreate, DbAdapter::QUERY_MODE_EXECUTE); $sqlDelete = 'DELETE FROM users'; $this->_db->query($sqlDelete, DbAdapter::QUERY_MODE_EXECUTE); $sqlInsert = 'INSERT INTO users (username, password, real_name) ' . 'VALUES ("my_username", "my_password", "My Real Name")'; $this->_db->query($sqlInsert, DbAdapter::QUERY_MODE_EXECUTE); }
/** * Load constraint data */ protected function loadConstraintData() { if ($this->tableData == null) { $this->loadTableColumnData(); } foreach ($this->tableData as $tableName => $columns) { $this->constraintData[$tableName] = array(); foreach ($columns as $column) { if ($column['pk'] == '1') { $constraint = array('name' => 'PRIMARY', 'type' => 'PRIMARY KEY', 'keys' => array(0 => array('column' => $column['name']))); $this->constraintData[$tableName][] = $constraint; break; } } $indexSql = 'PRAGMA index_list("' . $tableName . '")'; $indexes = $this->adapter->query($indexSql, Adapter::QUERY_MODE_EXECUTE); foreach ($indexes as $index) { $constraint = array('name' => $index['name'], 'type' => 'UNIQUE KEY', 'keys' => array()); $indexInfoSql = 'PRAGMA index_info("' . $index['name'] . '")'; $indexInfos = $this->adapter->query($indexInfoSql, Adapter::QUERY_MODE_EXECUTE); foreach ($indexInfos as $indexInfo) { $constraint['keys'][] = array('column' => $indexInfo['name']); } $this->constraintData[$tableName][] = $constraint; } $foreignSql = 'PRAGMA foreign_key_list("' . $tableName . '");'; $foreignKeys = $this->adapter->query($foreignSql, Adapter::QUERY_MODE_EXECUTE); foreach ($foreignKeys as $fkIndex => $foreignKey) { $constraint = array('name' => 'fk_' . $tableName . '_' . ($fkIndex + 1), 'type' => 'FOREIGN KEY', 'keys' => array(0 => array('column' => $foreignKey['from'], 'referenced_table' => $foreignKey['table'], 'referenced_column' => $foreignKey['to'], 'update_rule' => $foreignKey['on_update'], 'delete_rule' => $foreignKey['on_delete']))); $this->constraintData[$tableName][] = $constraint; } } }
/** * Push data into table with multi-insert query. * Return affected rows count * @param array $data * @param bool $ignore * @return int */ public function multiInsert($data, $ignore = false) { $count = 0; if (count($data)) { $ignore = $ignore ? 'IGNORE' : ''; $columns = (array) current($data); $columns = array_keys($columns); $columnsCount = count($columns); $platform = $this->adapter->platform; foreach ($columns as &$column) { $column = $platform->quoteIdentifier($column); } $columns = "(" . implode(',', $columns) . ")"; $placeholder = array_fill(0, $columnsCount, '?'); $placeholder = "(" . implode(',', $placeholder) . ")"; $placeholder = implode(',', array_fill(0, count($data), $placeholder)); $values = array(); foreach ($data as $row) { foreach ($row as $key => $value) { $values[] = $value; } } $table = $platform->quoteIdentifier($this->getTable()); $q = "INSERT {$ignore} INTO {$table} {$columns} VALUES {$placeholder}"; $result = $this->adapter->query($q)->execute($values); $count = $result->count(); } return $count; }
/** * Simple query executor, expects results and returns them as an assoc array * @param $query * @param array $params * @return array * @throws \Exception */ public function executeRawQuery($query, array $params = array()) { $this->logQuery($this->fixTableName($query), $params); try { $this->result = $this->_adapter->query($this->fixTableName($query), $params); } catch (\Exception $e) { $this->result = new ResultSet(ResultSet::TYPE_ARRAY, []); $mysqli = $this->_adapter->getDriver()->getConnection()->getResource(); error_log("ERROR: ADB : executeRawQuery"); error_log("Engine message: " . isset($mysqli->error) ? $mysqli->error : "No message available"); error_log($e->getMessage()); error_log("query: " . $this->getFullQuery($query, $params)); error_log($e->getTraceAsString()); $throw = true; if (isset($this->_conf['suppress-exceptions'])) { if ($this->_conf['suppress-exceptions']) { $throw = false; } } if (!isset($this->_conf['throw-engine-message'])) { // throw engine message by default $this->_conf['throw-engine-message'] = true; } if ($throw) { if (isset($mysqli->error) && $this->_conf['throw-engine-message'] && !empty($mysqli->error)) { throw new \Exception($mysqli->error); } else { throw $e; } } } $this->safeDropCache($query); return $this->result; }
/** * Create Schema * * @return AdapterInterface */ public function createSchema() { $ddl = new CreateTable($this->tableName); $ddl->addColumn(new Varchar('version', 255)); $sql = new Sql($this->adapter); $this->adapter->query($sql->buildSqlString($ddl), Adapter::QUERY_MODE_EXECUTE); return $this; }
/** * Restore old innodbstats variable * @return void */ protected function restoreInnoDbStats() { $value = $this->mysql_innodbstats_value; if ($value !== null) { // restoring old variable $this->adapter->query("set global innodb_stats_on_metadata='{$value}'", Adapter::QUERY_MODE_EXECUTE); } }
public function fetchAllForPlace($placeId) { $sql = new Sql($this->adapter); $select = $sql->select(); $select->from(['pt' => 'place_types']); $select->columns(['id', 'name']); $select->join(['ppt' => 'places_place_types'], 'ppt.place_type_id = pt.id', []); $select->where(['ppt.place_id' => $placeId]); $query = $sql->getSqlStringForSqlObject($select); $resultSet = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE); $results = $resultSet->toArray(); return array_map(function ($rec) { $entity = new PlaceTypeEntity(); $entity->exchangeArray($rec); return $entity; }, $results); }
public function onDispatch(MvcEvent $e) { if (!$e->getRequest() instanceof ConsoleRequest) { throw new RuntimeException('You can only use this action from a console!'); } $table = new Ddl\CreateTable(Version::TABLE_NAME); $table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true])); $table->addColumn(new Ddl\Column\BigInteger('version')); $table->addConstraint(new Ddl\Constraint\PrimaryKey('id')); $table->addConstraint(new Ddl\Constraint\UniqueKey('version')); $sql = new Sql($this->dbAdapter); try { $this->dbAdapter->query($sql->buildSqlString($table), DbAdapter::QUERY_MODE_EXECUTE); } catch (\Exception $e) { // currently there are no db-independent way to check if table exists // so we assume that table exists when we catch exception } }
/** * @testdox unit test: Test query() in execute mode produces a resultset object * @covers Zend\Db\Adapter\Adapter::query */ public function testQueryWhenExecutedProducesAResultSetObjectWhenResultIsQuery() { $sql = 'SELECT foo'; $result = $this->getMock('Zend\\Db\\Adapter\\Driver\\ResultInterface'); $this->mockConnection->expects($this->any())->method('execute')->with($sql)->will($this->returnValue($result)); $result->expects($this->any())->method('isQueryResult')->will($this->returnValue(true)); $r = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE); $this->assertInstanceOf('Zend\\Db\\ResultSet\\ResultSet', $r); }
/** * Prepares, binds params and executes query * @param string $sql SQL query with placeholders * @param array $vars Array of variables * @throws BaseException * @return ResultSet */ public function query($sql, $vars = array()) { try { $result = $this->adapter->query($sql, $vars); } catch (ExceptionInterface $e) { throw new BaseException("Query error", 0, $e); } return $result; }
private function createTablePages() { $table = new Ddl\CreateTable('pages'); $id = new Column\Integer('id'); $id->setOption('AUTO_INCREMENT', 1); $table->addColumn($id); $table->addColumn(new Column\Varchar('title', 255)); $table->addColumn(new Column\Text('body')); $table->addColumn(new Column\Datetime('dt_created')); $table->addColumn(new Column\Datetime('dt_updated', true)); $table->addConstraint(new Constraint\PrimaryKey('id')); $sql = new Sql($this->dbAdapter); try { $this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE); } catch (PDOException $e) { return $e->getMessage() . PHP_EOL; } }
public function create(array $data) { $sql = new Sql($this->adapter); $this->adapter->getDriver()->getConnection()->beginTransaction(); $insert = $sql->insert('places'); $insert->values(['name' => $data['name'], 'latitude' => $data['latitude'], 'longitude' => $data['longitude']]); $query = $sql->getSqlStringForSqlObject($insert); /** @var Result $results */ $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE); $placeId = $results->getGeneratedValue(); foreach ($data['types'] as $typeId) { $insert = $sql->insert('places_place_types'); $insert->values(['place_id' => $placeId, 'place_type_id' => $typeId]); $q = $sql->getSqlStringForSqlObject($insert); $this->adapter->query($q, Adapter::QUERY_MODE_EXECUTE); } $this->adapter->getDriver()->getConnection()->commit(); return $placeId; }
/** * Import dump in database * * @param $name * @param null $module * @return StatementInterface|\Zend\Db\ResultSet\ResultSet * @throws DumpNotFound */ public function import($name, $module = null) { $path = $this->getDumpsDirectoryPath($module); if (is_file($path . DIRECTORY_SEPARATOR . $name)) { $dump = file_get_contents($path . DIRECTORY_SEPARATOR . $name); return $this->db->query($dump, Adapter::QUERY_MODE_EXECUTE); } else { throw new DumpNotFound("Dump file not found!"); } }
/** * @depends testCreateDumpSuccess */ public function testImportDumpSuccess() { $dumpFullPath = self::$manager->getDumpsDirectoryPath() . DIRECTORY_SEPARATOR . self::DUMP_FILE_NAME; if (is_file($dumpFullPath)) { // dispatch url $this->dispatch('import dump ' . self::DUMP_FILE_NAME); $this->assertResponseStatusCode(0); $this->assertActionName('import'); $this->assertControllerName('ZFCTool\\Controller\\Dump'); $this->assertControllerClass('DumpController'); $this->assertMatchedRouteName('import-dump'); $result = self::$db->query("SHOW TABLES LIKE '" . self::TABLE_NAME . "';", Adapter::QUERY_MODE_EXECUTE); $this->assertEquals(1, $result->count()); $db = new Mysql(self::$db); $db->dropTable(self::TABLE_NAME); } else { $this->fail('Dump file not exist!'); } }