/**
* @covers Zend\Db\Sql\Insert::values
*/
public function testValues()
{
$this->insert->values(array('foo' => 'bar'));
$this->assertEquals(array('foo'), $this->readAttribute($this->insert, 'columns'));
$this->assertEquals(array('bar'), $this->readAttribute($this->insert, 'values'));
// test will merge cols and values of previously set stuff
$this->insert->values(array('foo' => 'bax'), Insert::VALUES_MERGE);
$this->insert->values(array('boom' => 'bam'), Insert::VALUES_MERGE);
$this->assertEquals(array('foo', 'boom'), $this->readAttribute($this->insert, 'columns'));
$this->assertEquals(array('bax', 'bam'), $this->readAttribute($this->insert, 'values'));
$this->insert->values(array('foo' => 'bax'));
$this->assertEquals(array('foo'), $this->readAttribute($this->insert, 'columns'));
$this->assertEquals(array('bax'), $this->readAttribute($this->insert, 'values'));
}
public function insertBookmark($payload)
{
$insert = new Insert($this->table);
$insert->values($payload);
$this->insertWith($insert);
return $this->lastInsertValue;
}
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');
}
/**
* {@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 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;
}
/**
* Exports insert SQL.
*
* @param array $data
* @return string
*/
protected function exportInsert(array $data)
{
$insertSql = '';
$insert = new Insert(self::TABLE_NAME);
foreach ($data as $id => $value) {
$insertSql .= @$insert->values(array('id' => $id, 'value' => $value))->getSqlString($this->getPlatform()) . ';' . PHP_EOL;
}
return $insertSql;
}
public function install($config)
{
$dbinstall = new Dbinstall($config);
$table_account = new CreateTable('account');
$table_account->addColumn(new Column\Integer('id', FALSE, NULL, array('autoincrement' => true)))->addConstraint(new Constraint\PrimaryKey('id'))->addColumn(new Column\Varchar('username', 50, false))->addColumn(new Column\Varchar('password', 50, false))->addColumn(new Column\Varchar('email', 50, false))->addConstraint(new Constraint\UniqueKey('email'))->addColumn(new Column\Varchar('openid_qq', 100, true))->addConstraint(new Constraint\UniqueKey('openid_qq'))->addColumn(new Column\Varchar('openid_sina', 100, true))->addConstraint(new Constraint\UniqueKey('openid_sina'))->addColumn(new Column\Varchar('openid_wechat', 100, true))->addConstraint(new Constraint\UniqueKey('openid_wechat'))->addColumn(new Column\Integer('status', false, 0));
$dbinstall->addCreateTable($table_account);
$insert_account = new Insert('account');
$insert_account->values(array('username' => 'admin', 'password' => 'admin', 'email' => '*****@*****.**', 'status' => 1));
$dbinstall->addInsert($insert_account);
$dbinstall->install();
}
/**
* @param Insert $insert
*/
public function preInsert(Insert $insert)
{
$metaColumns = $this->tableGateway->getColumns();
if (count($metaColumns)) {
$metaColumns = array_flip($metaColumns);
$columns = array_flip($insert->getRawState('columns'));
$columns = array_flip(array_intersect_key($columns, $metaColumns));
$values = $insert->getRawState('values');
$values = array_intersect_key($values, $columns);
$insert->values(array_values($values));
$insert->columns(array_values($columns));
}
}
public function saveProduct($productData)
{
$action = new Insert('products');
$action->values($productData);
$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');
}
/**
* @param Insert $insert
*/
public function preInsert(Insert $insert)
{
$columns = $insert->getRawState('columns');
$values = $insert->getRawState('values');
$key = array_search($this->primaryKeyField, $columns);
if ($key !== false) {
$this->sequenceValue = $values[$key];
return $insert;
}
$this->sequenceValue = $this->nextSequenceId();
if ($this->sequenceValue === null) {
return $insert;
}
$insert->values(array($this->primaryKeyField => $this->sequenceValue), Insert::VALUES_MERGE);
return $insert;
}
public function save(RFollow $followObject)
{
$postData = $this->hydrator->extract($followObject);
// Debug::dump($postData);
/*
* 下面这两行一定记得自己加上去
*/
$postData['userID'] = $followObject->getUser()->getUserID();
// unset($postData['secname']);//等到更改成类的时候需要利用php的特性改变成员变量的类型，class变成id
// unset($postData['username']);//虽然还是比较麻烦，但是只要写一个类就够了，不需要再unset这么多了。
$action = new Insert('rfollow');
$action->values($postData);
$sql = new Sql($this->dbAdapter);
$stmt = $sql->prepareStatementForSqlObject($action);
$result = $stmt->execute();
}
public function save(\Api\Entity\User $user)
{
$hydrator = $this->getHydrator();
$postData = array('display_name' => $user->getEmail(), 'password' => $user->getPassword());
$postData = array_merge($postData, array('email' => $user->getEmail(), 'username' => $user->getUsername()));
$insert = new Insert('user');
$insert->values($postData);
$sql = new Sql($this->getAdaptor());
$statement = $sql->prepareStatementForSqlObject($insert);
$result = $statement->execute();
if ($result instanceof ResultInterface) {
if ($pk = $result->getGeneratedValue()) {
$user->setUserId($pk);
}
return $hydrator->extract($user);
}
throw new \Exception('something went wrong.Please try again later');
}
/**
* Method to Insert a new User into the Database
*
* @param array $array
* @return array
*/
public function registerUserByLoginName($array)
{
$array["password"] = $this->passwordService->create($array["password"], $array["timestamp"]);
$values["password"] = $array["password"];
$values["loginName"] = $array["loginName"];
$values["ingameName"] = $array["ingameName"];
$values["timestamp"] = $array["timestamp"];
$action = new Insert('tbluser');
$action->values($values);
$sql = new Sql($this->dbAdapter);
$stmt = $sql->prepareStatementForSqlObject($action);
$result = $stmt->execute();
if ($result instanceof ResultInterface) {
if ($newID = $result->getGeneratedValue()) {
return array('registerSuccess' => true);
}
}
return array('registerSuccess' => false, 'errors' => array('errorMessage' => 'Database error'));
}
public function preInsert(Insert $insert)
{
$columns = $insert->getRawState('columns');
$values = $insert->getRawState('values');
$key = array_search($this->primaryKeyField, $columns);
if ($key !== false) {
$this->sequenceValue = $values[$key];
return $insert;
}
$this->sequenceValue = $this->nextSequenceId();
if ($this->sequenceValue === null) {
return $insert;
}
array_push($columns, $this->primaryKeyField);
array_push($values, $this->sequenceValue);
$insert->columns($columns);
$insert->values($values);
return $insert;
}
public function save(Comment $comment)
{
$hydrator = $this->getHydrator();
$hydrator->addFilter("inputFilter", new MethodMatchFilter("getInputFilter"), FilterComposite::CONDITION_AND);
$hydrator->addFilter("array_copy", new MethodMatchFilter("getArrayCopy"), FilterComposite::CONDITION_AND);
$hydrator->addFilter("getAuthor", new MethodMatchFilter("getAuthor"), FilterComposite::CONDITION_AND);
$postData = $hydrator->extract($comment);
$insert = new Insert('comments');
$insert->values($postData);
$sql = new Sql($this->getAdaptor());
$statement = $sql->prepareStatementForSqlObject($insert);
$result = $statement->execute();
if ($result instanceof ResultInterface) {
if ($pk = $result->getGeneratedValue()) {
$comment->setId($pk);
}
return $this->getComment($comment->getId());
}
throw new \Exception('something went wrong.Please try again later');
}
/**
* {@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.");
}
/**
* Pass true as $name to return to last generated id.
* Pass string and it will be passed to the id retriever method.
* Pass false to skip retrieving the last generated id and return true.
*
* @throws Exception\RecordNotSavedException
*/
public function insert(array $columnsValuesPairs, $name = true)
{
$adapter = $this->sql->getAdapter();
$insert = new ZfSql\Insert($this->tableName);
$insert->values($columnsValuesPairs);
$sqlString = $this->sql->getSqlStringForSqlObject($insert);
try {
$result = $adapter->query($sqlString, $adapter::QUERY_MODE_EXECUTE);
} catch (AdapterException\ExceptionInterface $e) {
throw new Exception\RecordNotSavedException($e->getMessage(), 0, $e);
}
if (true === $name) {
$value = $adapter->getDriver()->getLastGeneratedValue();
if ($value === '0') {
return true;
} else {
return $value;
}
} elseif (is_string($name)) {
return $adapter->getDriver()->getLastGeneratedValue($name);
} else {
return true;
}
}
public function userPermissionSet($user_id, $permissionArray = array())
{
if (count($permissionArray) != 0) {
$this->deletePermissionByUserId($user_id);
foreach ($permissionArray as $key => $value) {
$sql = new Sql($this->adapter);
$insert = new Insert($this->user_permission);
$insert->values(array('user_id' => (int) $user_id, 'permission_id' => (int) $value));
$selectString = $sql->getSqlStringForSqlObject($insert);
$results = $this->adapter->query($selectString, Adapter::QUERY_MODE_EXECUTE);
}
}
}
/**
* @covers Zend\Db\Sql\Insert::values
*/
public function testValues()
{
$this->insert->values(array('foo' => 'bar'));
$this->assertEquals(array('foo'), $this->readAttribute($this->insert, 'columns'));
$this->assertEquals(array('bar'), $this->readAttribute($this->insert, 'values'));
}
public function saveSkillByTopic($topic_id = null, $skill_id = null)
{
$sql = new Sql($this->adapter);
$insert = new Insert($this->topicSkill);
$insert->values(array('topic_id' => (int) $topic_id, 'skill_id' => (int) $skill_id));
$selectString = $sql->getSqlStringForSqlObject($insert);
$results = $this->adapter->query($selectString, Adapter::QUERY_MODE_EXECUTE);
}
public function recordMessage($data, $userId)
{
if (isset($data['response_to']) && $data['response_to'] > 0) {
$action = 'REPLY';
} else {
$action = 'ADD';
}
$logData = ['type' => self::TYPE_MESSAGE, 'table_name' => 'directus_messages', 'action' => $action, 'user' => $userId, 'datetime' => DateUtils::now(), 'parent_id' => null, 'data' => json_encode($data), 'delta' => '[]', 'identifier' => $data['subject'], 'row_id' => $data['id'], 'logged_ip' => isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : '', 'user_agent' => isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : ''];
$insert = new Insert($this->getTable());
$insert->values($logData);
$this->insertWith($insert);
}
/**
* @throws Exception\InvalidArgumentException on tracking unit already having an ID
* @throws Exception\RuntimeException on insert fail
*
* @inheritdoc
*/
public function insert(TrackingUnit $trackingUnit)
{
if ($trackingUnit->hasId() === true) {
$msg = 'Cannot insert tracking unit whth an existing ID';
throw new Exception\InvalidArgumentException($msg);
}
$dbAdapter = $this->getDbAdapter();
$hydrator = $this->getTrackingUnitHydrator();
$data = $hydrator->extract($trackingUnit);
$insert = new Insert(Utils::getTrackingTableName());
$insert->values($data);
$sql = $insert->getSqlString($dbAdapter->getPlatform());
$dbAdapter->query($sql, DbAdapter::QUERY_MODE_EXECUTE);
$lastVal = $dbAdapter->driver->getLastGeneratedValue();
if ((bool) $lastVal === false) {
$msg = 'Insert did not succeed';
throw new Exception\RuntimeException($msg);
}
$trackingUnit->setId($lastVal);
return $trackingUnit;
}
public function insertValues($values)
{
$insert = new Insert($this->getTable());
$insert->values($values);
$this->insertWith($insert);
}
/**
* @covers Zend\Db\Sql\Insert::values
* @group ZF2-4926
*/
public function testEmptyArrayValues()
{
$this->insert->values(array());
$this->assertEquals(array(), $this->readAttribute($this->insert, 'columns'));
}
/**
* {@inheritDoc}
*/
public function setRefreshToken($refreshToken, $clientId, $userId, $expires, $scope = null)
{
$expires = date('Y-m-d H:i:s', $expires);
if ($this->getRefreshToken($refreshToken)) {
$update = new Update($this->config['refresh_token_table']);
$update->set(array('client_id' => $clientId, 'user_id' => $userId, 'expires' => $expires, 'scope' => $scope))->where(array('refresh_token' => $refreshToken));
return $this->execute($update);
} else {
$insert = new Insert($this->config['refresh_token_table']);
$insert->values(array('refresh_token' => $refreshToken, 'client_id' => $clientId, 'user_id' => $userId, 'expires' => $expires, 'scope' => $scope));
return $this->execute($insert);
}
}
/**
* Save properties
*
* @return boolean
*/
public function save()
{
$this->events()->trigger(__CLASS__, 'before.save', $this);
if (!empty($this->data['document_type_id'])) {
$this->delete();
$insert = new Insert();
$insert->into('document_type_view');
foreach ($this->getElements() as $view) {
$insert->values(array('document_type_id' => $this->getDocumentTypeId(), 'view_id' => $view->getId()));
$this->execute($insert);
}
$this->events()->trigger(__CLASS__, 'after.save', $this);
return true;
}
$this->events()->trigger(__CLASS__, 'after.save.failed', $this);
return false;
}
/**
* @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 recordMessage($data, $userId)
{
if (isset($data['response_to']) && $data['response_to'] > 0) {
$action = "REPLY";
} else {
$action = "ADD";
}
$logData = array('type' => self::TYPE_MESSAGE, 'table_name' => 'directus_messages', 'action' => $action, 'user' => $userId, 'datetime' => gmdate('Y-m-d H:i:s'), 'parent_id' => null, 'data' => json_encode($data), 'delta' => "[]", 'identifier' => $data['subject'], 'row_id' => $data['id'], 'logged_ip' => $_SERVER['REMOTE_ADDR'], 'user_agent' => $_SERVER['HTTP_USER_AGENT']);
$insert = new Insert($this->getTable());
$insert->values($logData);
$this->insertWith($insert);
}
public function save(Page $pageObject)
{
$postData = $this->hydrator->extract($pageObject);
// Debug::dump($postData);
/*
* 下面这两行一定记得自己加上去
*/
$postData['userID'] = $pageObject->getUser()->getUserID();
unset($postData['secname']);
//等到更改成类的时候需要利用php的特性改变成员变量的类型，class变成id
// unset($postData['username']);//虽然还是比较麻烦，但是只要写一个类就够了，不需要再unset这么多了。
$action = new Insert('page');
$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
// $pageObject->setId($newId);
// }
// return $pageObject;
// }
// throw new \Exception("Database error");
}
public function constructPreferences($user_id, $table, $preferences = null, $title = null)
{
if ($preferences) {
$newPreferencesData = false;
// @todo enforce non-empty set
if (empty($preferences['columns_visible'])) {
$newPreferencesData = true;
$columns_visible = TableSchema::getTableColumns($table, 6);
$preferences['columns_visible'] = implode(',', $columns_visible);
}
$preferencesDefaultsApplied = $this->applyDefaultPreferences($table, $preferences);
if (count(array_diff($preferences, $preferencesDefaultsApplied))) {
$newPreferencesData = true;
}
$preferences = $preferencesDefaultsApplied;
if ($newPreferencesData) {
$id = $this->addOrUpdateRecordByArray($preferences);
}
return $preferences;
}
$insert = new Insert($this->table);
// User doesn't have any preferences for this table yet. Please create!
$columns_visible = TableSchema::getTableColumns($table, 6);
$data = array('user' => $user_id, 'columns_visible' => implode(',', $columns_visible), 'table_name' => $table, 'title' => $title);
if (TableSchema::hasTableColumn($table, 'sort')) {
$data['sort'] = 'sort';
}
$data = $this->applyDefaultPreferences($table, $data);
$insert->values($data);
$this->insertWith($insert);
return $data;
}
