/** * @covers Zend\Db\Sql\Update::where */ public function testWhere() { $this->update->where('x = y'); $this->update->where(array('foo > ?' => 5)); $this->update->where(array('id' => 2)); $this->update->where(array('a = b'), Where::OP_OR); $this->update->where(array('c1' => null)); $this->update->where(array('c2' => array(1, 2, 3))); $this->update->where(array(new \Zend\Db\Sql\Predicate\IsNotNull('c3'))); $where = $this->update->where; $predicates = $this->readAttribute($where, 'predicates'); $this->assertEquals('AND', $predicates[0][0]); $this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\Expression', $predicates[0][1]); $this->assertEquals('AND', $predicates[1][0]); $this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\Expression', $predicates[1][1]); $this->assertEquals('AND', $predicates[2][0]); $this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\Operator', $predicates[2][1]); $this->assertEquals('OR', $predicates[3][0]); $this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\Expression', $predicates[3][1]); $this->assertEquals('AND', $predicates[4][0]); $this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\IsNull', $predicates[4][1]); $this->assertEquals('AND', $predicates[5][0]); $this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\In', $predicates[5][1]); $this->assertEquals('AND', $predicates[6][0]); $this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\IsNotNull', $predicates[6][1]); $where = new Where(); $this->update->where($where); $this->assertSame($where, $this->update->where); $test = $this; $this->update->where(function ($what) use($test, $where) { $test->assertSame($where, $what); }); }
/** * {@inheritDoc} */ public function save(PostInterface $postObject) { $postData = $this->hydrator->extract($postObject); unset($postData['id']); // Neither Insert nor Update needs the ID in the array if ($postObject->getId()) { // ID present, it's an Update $action = new Update('post'); $action->set($postData); $action->where(array('id = ?' => $postObject->getId())); } else { // ID NOT present, it's an Insert $action = new Insert('post'); $action->values($postData); } $sql = new Sql($this->dbAdapter); $stmt = $sql->prepareStatementForSqlObject($action); $result = $stmt->execute(); if ($result instanceof ResultInterface) { if ($newId = $result->getGeneratedValue()) { // When a value has been generated, set it on the object $postObject->setId($newId); } return $postObject; } throw new \Exception("Database error"); }
public function save(\Api\Entity\Post $post) { $hydrator = $this->getHydrator(); $action = null; $postData = array('title' => $post->getTitle(), 'description' => $post->getDescription()); if ($post->getId()) { $action = new Update('posts'); $action->set($postData); $action->where(array('id = ?' => $post->getId())); } else { $postData['author_id'] = $post->getAuthorId(); $action = new Insert('posts'); $action->values($postData); } $sql = new Sql($this->getAdaptor()); $statement = $sql->prepareStatementForSqlObject($action); $result = $statement->execute(); if ($result instanceof ResultInterface) { if ($pk = $result->getGeneratedValue()) { $post->setId($pk); } return $this->getPost($post->getId()); } throw new \Exception('something went wrong.Please try again later'); }
public function setRequest($id) { if ($id == $this->user_id) { return true; } $user = $this->getUserById($id); if (is_array($user)) { if ($user["friendship"] == -1) { //insert $insert = new Insert('fg_friends'); $newData = array('user_one' => $this->user_id, 'user_two' => $id, 'state' => '0'); $insert->values($newData); $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($insert); $resultSet = $statement->execute(); } else { if (!$user["i_am_adder"] && $user["friendship"] == 0) { //update $update = new Update('fg_friends'); $newData = array('state' => '1'); $update->set($newData); $update->where(array('user_one' => $id, 'user_two' => $this->user_id)); $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($update); $resultSet = $statement->execute(); } } return true; } return false; }
/** * * @param string $userID * @return User */ public function updateUser(User $userObject) { $postData = $this->hydrator->extract($userObject); $action = new Update('user'); $action->set($postData); $action->where(array('userID = ?' => $userObject->getUserID())); $sql = new Sql($this->dbAdapter); $stmt = $sql->prepareStatementForSqlObject($action); $result = $stmt->execute(); if ($result instanceof ResultInterface) { // if ($newId = $result->getGeneratedValue()) { // // When a value has been generated, set it on the object // $postObject->setId($newId); // } return true; } }
public function update(\Api\Entity\User $user) { $hydrator = $this->getHydrator(); $postData = array('display_name' => $user->getDisplayName(), 'password' => $user->getPassword()); $update = new Update('user'); $update->set($postData); $update->where(array('user_id = ?' => $user->getUserId())); $sql = new Sql($this->getAdaptor()); $statement = $sql->prepareStatementForSqlObject($update); $result = $statement->execute(); if ($result instanceof ResultInterface) { if ($pk = $result->getGeneratedValue()) { $user->setUserId($pk); } return $this->getUser($user->getUserId()); } throw new \Exception('something went wrong.Please try again later'); }
/** * @throws Exception\RecordNotSavedException */ public function update(array $columnsValuesPairs) { $update = new ZfSql\Update($this->tableName); $update->set($columnsValuesPairs); $adapter = $this->sql->getAdapter(); if ($this->select->where) { $update->where($this->select->where); } $sqlString = $this->sql->getSqlStringForSqlObject($update); try { $result = $adapter->query($sqlString, $adapter::QUERY_MODE_EXECUTE); } catch (AdapterException\ExceptionInterface $e) { throw new Exception\RecordNotSavedException($e->getMessage(), 0, $e); } if (!$result->count()) { throw new Exception\RecordNotSavedException("No rows were affected"); } return true; }
/** * {@inheritdoc} */ public function save(PostInterface $postObject) { $postData = $this->hydrator->extract($postObject); unset($postData['id']); if ($postObject->getId()) { $action = new Update('posts'); $action->set($postData); $action->where(['id = ?' => $postObject->getId()]); } else { $action = new Insert('posts'); $action->values($postData); } $sql = new Sql($this->dbAdapter); $stmt = $sql->prepareStatementForSqlObject($action); $result = $stmt->execute(); if ($result instanceof ResultInterface) { if ($newId = $result->getGeneratedValue()) { $postObject->setId($newId); } return $postObject; } throw new \Exception("Database error."); }
/** * @param Route $model * * @return Route * @throws \Exception */ public function save(Route $model) : Route { $modelData = $this->hydrator->extract($model); if ($model->getId()) { $action = new Update($this->table); $action->set($modelData); $action->where(['id = ?' => $model->getId()]); } else { $action = new Insert($this->table); $action->values($modelData); } $sql = new Sql($this->dbAdapter); $statement = $sql->prepareStatementForSqlObject($action); $result = $statement->execute(); if (!$result instanceof ResultInterface) { throw new \Exception('Database Error'); } if ($newId = $result->getGeneratedValue()) { $model->setId($newId); } return $model; }
public function addOrUpdateRecordByArray(array $recordData, $tableName = null) { foreach ($recordData as $columnName => $columnValue) { if (is_array($columnValue)) { $table = is_null($tableName) ? $this->table : $tableName; throw new SuppliedArrayAsColumnValue("Attempting to write an array as the value for column `{$table}`.`{$columnName}`."); } } $tableName = is_null($tableName) ? $this->table : $tableName; $TableGateway = new self($this->acl, $tableName, $this->adapter); $rowExists = isset($recordData[$TableGateway->primaryKeyFieldName]); if ($rowExists) { $Update = new Update($tableName); $Update->set($recordData); $Update->where(array($TableGateway->primaryKeyFieldName => $recordData[$TableGateway->primaryKeyFieldName])); $TableGateway->updateWith($Update); Hooks::runHook('postUpdate', array($TableGateway, $recordData, $this->adapter, $this->acl)); } else { $d = $recordData; unset($d['data']); $TableGateway->insert($d); $recordData[$TableGateway->primaryKeyFieldName] = $TableGateway->getLastInsertValue(); if ($tableName == "directus_files") { $Files = new \Directus\Files\Files(); $ext = pathinfo($recordData['name'], PATHINFO_EXTENSION); $thumbnailPath = 'thumbs/THUMB_' . $recordData['name']; if ($Files->exists($thumbnailPath)) { $Files->rename($thumbnailPath, 'thumbs/' . $recordData[$this->primaryKeyFieldName] . '.' . $ext); } $updateArray = array(); if ($Files->getSettings('file_naming') == 'file_id') { $Files->rename($recordData['name'], str_pad($recordData[$this->primaryKeyFieldName], 11, "0", STR_PAD_LEFT) . '.' . $ext); $updateArray['name'] = str_pad($recordData[$this->primaryKeyFieldName], 11, "0", STR_PAD_LEFT) . '.' . $ext; $recordData['name'] = $updateArray['name']; } if (!empty($updateArray)) { $Update = new Update($tableName); $Update->set($updateArray); $Update->where(array($TableGateway->primaryKeyFieldName => $recordData[$TableGateway->primaryKeyFieldName])); $TableGateway->updateWith($Update); } } Hooks::runHook('postInsert', array($TableGateway, $recordData, $this->adapter, $this->acl)); } $columns = TableSchema::getAllNonAliasTableColumnNames($tableName); $recordData = $TableGateway->fetchAll(function ($select) use($recordData, $columns, $TableGateway) { $select->columns($columns)->limit(1); $select->where->equalTo($TableGateway->primaryKeyFieldName, $recordData[$TableGateway->primaryKeyFieldName]); })->current(); return $recordData; }
public function addOrUpdateRecordByArray(array $recordData, $tableName = null) { $tableName = is_null($tableName) ? $this->table : $tableName; foreach ($recordData as $columnName => $columnValue) { if (is_array($columnValue)) { // $table = is_null($tableName) ? $this->table : $tableName; throw new SuppliedArrayAsColumnValue('Attempting to write an array as the value for column `' . $tableName . '`.`' . $columnName . '.'); } } $columns = TableSchema::getAllNonAliasTableColumns($tableName); $recordData = SchemaManager::parseRecordValuesByType($recordData, $columns); $TableGateway = $this->makeTable($tableName); $rowExists = isset($recordData[$TableGateway->primaryKeyFieldName]); if ($rowExists) { $Update = new Update($tableName); $Update->set($recordData); $Update->where([$TableGateway->primaryKeyFieldName => $recordData[$TableGateway->primaryKeyFieldName]]); $TableGateway->updateWith($Update); $this->runHook('postUpdate', [$TableGateway, $recordData, $this->adapter, null]); } else { $d = $this->applyHook('table.insert:before', [$tableName, $recordData]); $TableGateway->insert($d); $recordData[$TableGateway->primaryKeyFieldName] = $TableGateway->getLastInsertValue(); if ($tableName == 'directus_files') { $Files = new \Directus\Files\Files(); $ext = pathinfo($recordData['name'], PATHINFO_EXTENSION); $thumbnailPath = 'thumbs/THUMB_' . $recordData['name']; if ($Files->exists($thumbnailPath)) { $Files->rename($thumbnailPath, 'thumbs/' . $recordData[$this->primaryKeyFieldName] . '.' . $ext); } $updateArray = []; if ($Files->getSettings('file_naming') == 'file_id') { $Files->rename($recordData['name'], str_pad($recordData[$this->primaryKeyFieldName], 11, '0', STR_PAD_LEFT) . '.' . $ext); $updateArray['name'] = str_pad($recordData[$this->primaryKeyFieldName], 11, '0', STR_PAD_LEFT) . '.' . $ext; $recordData['name'] = $updateArray['name']; } if (!empty($updateArray)) { $Update = new Update($tableName); $Update->set($updateArray); $Update->where([$TableGateway->primaryKeyFieldName => $recordData[$TableGateway->primaryKeyFieldName]]); $TableGateway->updateWith($Update); } } $this->runHook('postInsert', [$TableGateway, $recordData, $this->adapter, null]); } $columns = TableSchema::getAllNonAliasTableColumnNames($tableName); $recordData = $TableGateway->fetchAll(function ($select) use($recordData, $columns, $TableGateway) { $select->columns($columns)->limit(1); $select->where->equalTo($TableGateway->primaryKeyFieldName, $recordData[$TableGateway->primaryKeyFieldName]); })->current(); return $recordData; }
/** * This method basically replaces all occurrences of $from->id (UserRow id) in tables * comments, user_resource, user_list & search with $into->id in user_id column. * * @param UserRow $from * @param UserRow $into * @throws AuthException * @return void */ public function mergeUserInAllTables(UserRow $from, UserRow $into) { if (empty($into->id) || empty($from->id)) { throw new AuthException("Cannot merge two UserRows without knowlegde of both UserRow ids"); } $this->mergeUserRows($from, $into); /** * Table names which contain user_id as a relation to user.id foreign key */ $tablesToUpdateUserId = ["comments", "user_resource", "user_list", "search"]; // This will prevent autocommit to Db $this->getDbConnection()->beginTransaction(); foreach ($tablesToUpdateUserId as $table) { $update = new Update($table); $update->set(['user_id' => $into->id]); $update->where(['user_id' => $from->id]); $this->executeAnyZendSQLUpdate($update); } // Now commit whole transaction $this->getDbConnection()->commit(); // Perform User deletion $from->delete(); }
/** * Update super admin details * * @access public * @author Arvind Singh * @param array $data * // Data * @param array $where * // Conditions * * @return integer */ public function updateSuperAdmin($data, $where) { $sql = new Sql($this->getAdapter()); $update = new Update($this->table); $update->set($data); $update->where($where); $statement = $sql->prepareStatementForSqlObject($update); $results = $statement->execute(); $affectedRows = $results->getAffectedRows(); return $affectedRows; }
public function update_token_status($tokenKey = null) { if ($tokenKey) { $sql = new Sql($this->adapter); $update = new Update($this->token); $update->set(array('status' => 0)); $update->where(array("token" => $tokenKey)); $selectString = $sql->getSqlStringForSqlObject($update); $results = $this->adapter->query($selectString, Adapter::QUERY_MODE_EXECUTE); } }
public function updateProduct($productData) { $id = $productData['id']; if (!empty($id)) { $action = new Update('products'); $action->set($productData); $action->where(array('id=?' => $id)); } $sql = new Sql($this->dbAdapter); $stmt = $sql->prepareStatementForSqlObject($action); $result = $stmt->execute(); if ($result instanceof ResultInterface) { if ($newId = $result->getGeneratedValue()) { return $newId; } return true; } throw new \Exception('Database Error'); }
public function addOrUpdateRecordByArray(array $recordData, $tableName = null) { foreach ($recordData as $columnName => $columnValue) { if (is_array($columnValue)) { $table = is_null($tableName) ? $this->table : $tableName; throw new SuppliedArrayAsColumnValue("Attempting to write an array as the value for column `{$table}`.`{$columnName}`."); } } $tableName = is_null($tableName) ? $this->table : $tableName; $rowExists = isset($recordData['id']); $TableGateway = new self($this->acl, $tableName, $this->adapter); if ($rowExists) { $Update = new Update($tableName); $Update->set($recordData); $Update->where(array('id' => $recordData['id'])); $TableGateway->updateWith($Update); Hooks::runHook('postUpdate', array($TableGateway, $recordData, $this->adapter, $this->acl)); } else { //If we are adding a new directus_files Item, We need to do that logic if ($tableName == "directus_files") { $Storage = new \Directus\Files\Storage\Storage(); //If trying to save to temp, force to default if (!isset($recordData['storage_adapter']) || $recordData['storage_adapter'] == '' || $Storage->storageAdaptersByRole['TEMP']['id'] == $recordData['storage_adapter']) { $recordData['storage_adapter'] = $Storage->storageAdaptersByRole['DEFAULT']['id']; } //Save Temp Thumbnail name for use after files record save $info = pathinfo($recordData['name']); if (in_array($info['extension'], $this->imagickExtensions)) { $thumbnailName = "THUMB_" . $info['filename'] . '.jpg'; } else { $thumbnailName = "THUMB_" . $recordData['name']; } //If we are using files ID, Dont save until after insert if ($Storage->getFilesSettings()['file_file_naming'] != "file_id") { //Save the file in TEMP Storage Adapter to Designated StorageAdapter $recordData['name'] = $Storage->saveFile($recordData['name'], $recordData['storage_adapter']); } } $TableGateway->insert($recordData); $recordData['id'] = $TableGateway->getLastInsertValue(); if ($tableName == "directus_files") { $ext = pathinfo($recordData['name'], PATHINFO_EXTENSION); $updateArray = array(); //If using file_id saving, then update record and set name to id if ($Storage->getFilesSettings()['file_file_naming'] == "file_id") { $newName = $Storage->saveFile($recordData['name'], $recordData['storage_adapter'], str_pad($recordData['id'], 11, "0", STR_PAD_LEFT) . '.' . $ext); $updateArray['name'] = str_pad($recordData['id'], 11, "0", STR_PAD_LEFT) . '.' . $ext; $recordData['name'] = $updateArray['name']; } //If we are using file_id titles, then set title to id if ($Storage->getFilesSettings()['file_title_naming'] == "file_id") { $updateArray['title'] = str_pad($recordData['id'], 11, "0", STR_PAD_LEFT); $recordData['title'] = $updateArray['title']; } if (!empty($updateArray)) { $Update = new Update($tableName); $Update->set($updateArray); $Update->where(array('id' => $recordData['id'])); $TableGateway->updateWith($Update); } //Save Temp Thumbnail to Thumbnail SA using file id: $params['id'] $tempLocation = $Storage->storageAdaptersByRole['TEMP']['destination']; if (file_exists($tempLocation . $thumbnailName)) { $thumbnailDestination = $Storage->storageAdaptersByRole['THUMBNAIL']['destination']; if (in_array($ext, $this->imagickExtensions)) { $ext = 'jpg'; } $Storage->ThumbnailStorage->acceptFile($tempLocation . $thumbnailName, $recordData['id'] . "." . $ext, $thumbnailDestination); unlink($tempLocation . $thumbnailName); } } Hooks::runHook('postInsert', array($TableGateway, $recordData, $this->adapter, $this->acl)); } $columns = TableSchema::getAllNonAliasTableColumnNames($tableName); $recordData = $TableGateway->fetchAll(function ($select) use($recordData, $columns) { $select->columns($columns)->limit(1); $select->where->equalTo('id', $recordData['id']); })->current(); return $recordData; }
public function addOrUpdateRecordByArray(array $recordData, $tableName = null) { foreach ($recordData as $columnName => $columnValue) { if (is_array($columnValue)) { $table = is_null($tableName) ? $this->table : $tableName; throw new SuppliedArrayAsColumnValue("Attempting to write an array as the value for column `{$table}`.`{$columnName}`."); } } $tableName = is_null($tableName) ? $this->table : $tableName; $rowExists = isset($recordData['id']); $TableGateway = new self($this->acl, $tableName, $this->adapter); if ($rowExists) { $Update = new Update($tableName); $Update->set($recordData); $Update->where(array('id' => $recordData['id'])); $TableGateway->updateWith($Update); Hooks::runHook('postUpdate', array($TableGateway, $recordData, $this->adapter, $this->acl)); } else { //If we are adding a new directus_files Item, We need to do that logic // @todo: clean up/refactor saving file process if ($tableName == "directus_files") { $Storage = new \Directus\Files\Storage\Storage(); //If trying to save to temp, force to default if (!isset($recordData['storage_adapter']) || $recordData['storage_adapter'] == '' || $Storage->storageAdaptersByRole['TEMP']['id'] == $recordData['storage_adapter']) { $recordData['storage_adapter'] = $Storage->storageAdaptersByRole['DEFAULT']['id']; } $StorageAdapters = new DirectusStorageAdaptersTableGateway($this->acl, $this->adapter); //If desired Storage Adapter Exists... $filesAdapter = $StorageAdapters->fetchOneById($recordData['storage_adapter']); //Save Temp Thumbnail name for use after files record save // @todo: make file name format sanatize by default // same as uniqueName by the adapter // replacing space with underscore $originalFile = $recordData['file_name']; // we do not need it part of our records Data unset($recordData['file_name']); $recordData['name'] = str_replace(' ', '_', $recordData['name']); $info = pathinfo($recordData['name']); if (in_array($info['extension'], $this->imagickExtensions)) { $thumbnailName = "THUMB_" . $info['filename'] . '.jpg'; } else { $thumbnailName = "THUMB_" . $recordData['name']; } //If we are using files ID, Dont save until after insert if ($Storage->getFilesSettings()['file_naming'] == "file_name") { //Save the file in TEMP Storage Adapter to Designated StorageAdapter $recordData['name'] = $Storage->saveFile($recordData['name'], $recordData['storage_adapter']); // $fileData = $Storage->saveData($recordData['data'], $recordData['name'], $filesAdapter['destination']); // $recordData['name'] = $fileData['name']; } else { if ($Storage->getFilesSettings()['file_naming'] == "file_hash") { //Save the file in TEMP Storage Adapter to Designated StorageAdapter $ext = pathinfo($recordData['name'], PATHINFO_EXTENSION); $fileHashName = md5(microtime() . $recordData['name']); $newName = $Storage->saveFile($recordData['name'], $recordData['storage_adapter'], $fileHashName . '.' . $ext); $updateArray['name'] = $fileHashName . '.' . $ext; $recordData['name'] = $updateArray['name']; } } } $d = $recordData; unset($d['data']); $TableGateway->insert($d); $recordData['id'] = $TableGateway->getLastInsertValue(); if ($tableName == "directus_files") { $ext = pathinfo($recordData['name'], PATHINFO_EXTENSION); $updateArray = array(); //If using file_id saving, then update record and set name to id if ($Storage->getFilesSettings()['file_naming'] == "file_id") { $newName = $Storage->saveFile($recordData['name'], $recordData['storage_adapter'], str_pad($recordData['id'], 11, "0", STR_PAD_LEFT) . '.' . $ext); $updateArray['name'] = str_pad($recordData['id'], 11, "0", STR_PAD_LEFT) . '.' . $ext; $recordData['name'] = $updateArray['name']; } // @todo: do not make this file create twice. // file should be copied to temp and then work from there. // but is copied on "media" also on "temp" then copied back to "media" if (file_exists($filesAdapter['destination'] . $originalFile)) { unlink($filesAdapter['destination'] . $originalFile); } if (!empty($updateArray)) { $Update = new Update($tableName); $Update->set($updateArray); $Update->where(array('id' => $recordData['id'])); $TableGateway->updateWith($Update); } //Save Temp Thumbnail to Thumbnail SA using file id: $params['id'] $tempLocation = $Storage->storageAdaptersByRole['TEMP']['destination']; if (file_exists($tempLocation . $thumbnailName)) { $thumbnailDestination = $Storage->storageAdaptersByRole['THUMBNAIL']['destination']; if (in_array($ext, $this->imagickExtensions)) { $ext = 'jpg'; } $Storage->ThumbnailStorage->acceptFile($tempLocation . $thumbnailName, $recordData['id'] . "." . $ext, $thumbnailDestination); unlink($tempLocation . $thumbnailName); } } Hooks::runHook('postInsert', array($TableGateway, $recordData, $this->adapter, $this->acl)); } $columns = TableSchema::getAllNonAliasTableColumnNames($tableName); $recordData = $TableGateway->fetchAll(function ($select) use($recordData, $columns) { $select->columns($columns)->limit(1); $select->where->equalTo('id', $recordData['id']); })->current(); return $recordData; }