public function insertBookmark($payload)
{
$insert = new Insert($this->table);
$insert->values($payload);
$this->insertWith($insert);
return $this->lastInsertValue;
}
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;
}
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 insertSession($username, $password, $session_id)
{
$insert = new Insert('sessions');
$adapter = $this->table_gateway->getAdapter();
$insert->columns(array('username', 'password', 'active', 'session_id'))->values(array('username' => $username, 'password' => $password, 'active' => 1, 'session_id' => $session_id));
$adapter->query($this->sql->buildSqlString($insert), $adapter::QUERY_MODE_EXECUTE);
return true;
}
/**
* 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 insertPrivilege($attributes)
{
$attributes = $this->verifyPrivilege($attributes);
$status_id = isset($attributes['status_id']) ? $attributes['status_id'] : 0;
$insert = new Insert($this->getTable());
$insert->columns(array('table_name', 'permissions', 'group_id'))->values(array('table_name' => $attributes['table_name'], 'permissions' => $attributes['permissions'], 'group_id' => $attributes['group_id'], 'status_id' => $status_id));
$this->insertWith($insert);
$privilegeId = $this->lastInsertValue;
return $this->fetchById($privilegeId);
}
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 insertPrivilege($attributes)
{
$attributes = $this->verifyPrivilege($attributes);
// @todo: this should fallback on field default value
if (!isset($attributes['status_id'])) {
$attributes['status_id'] = 0;
}
$attributes = $this->getFillableFields($attributes);
$insert = new Insert($this->getTable());
$insert->columns(array_keys($attributes))->values($attributes);
$this->insertWith($insert);
$privilegeId = $this->lastInsertValue;
return $this->fetchById($privilegeId);
}
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');
}
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
/* init */
$this->hasAutoincrement = false;
/* parent prep */
$result = parent::prepareStatement($adapter, $statementContainer);
/* oci8 with autoincrement */
if ($statementContainer instanceof \Zend\Db\Adapter\Driver\Oci8\Statement && $this->autoincrement !== null) {
/* get sequence */
if ($this->sequence === null) {
$this->sequence = 'SEQ_' . $this->table;
}
/* replace ai field with sequence & move ai field binding to the end with returning */
$count = 0;
$sql = preg_replace('/:' . $this->autoincrement . '\\s*/', $this->sequence . '.NEXTVAL', $statementContainer->getSql(), 1, $count) . ' RETURNING "' . $this->autoincrement . '" INTO :' . $this->autoincrement;
/* anything replaced? */
if ($count > 0) {
/* prep statement to prep resource */
$statementContainer->setSql($sql);
$statementContainer->prepare();
/* unset ai field */
$statementContainer->getParameterContainer()->offsetUnset($this->autoincrement);
/* get ai field position on values */
$position = array_search($this->autoincrement, $this->columns);
$this->values[$position] = 0;
$this->hasAutoincrement = true;
oci_bind_by_name($statementContainer->getResource(), $this->autoincrement, $this->values[$position], -1, SQLT_INT);
}
}
//oci8 AI
return $result;
}
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 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');
}
/**
* Sets up the fixture, for example, opens a network connection.
* This method is called before a test is executed.
*
* @return void
*/
protected function setUp()
{
$this->view = ViewModel::fromArray(array('name' => 'View Name', 'identifier' => 'View identifier', 'description' => 'View Description', 'content' => 'View Content'));
$this->view->save();
$this->layout = LayoutModel::fromArray(array('name' => 'Layout Name', 'identifier' => 'Layout identifier', 'description' => 'Layout Description', 'content' => 'Layout Content'));
$this->layout->save();
$this->user = UserModel::fromArray(array('lastname' => 'User test', 'firstname' => 'User test', 'email' => '*****@*****.**', 'login' => 'test', 'user_acl_role_id' => 1));
$this->user->setPassword('test');
$this->user->save();
$this->object = Model::fromArray(array('name' => 'Document Type Name', 'description' => 'Document Type description', 'icon_id' => 1, 'defaultview_id' => $this->view->getId(), 'user_id' => $this->user->getId()));
$this->object->save();
$this->documentTypeChildren = Model::fromArray(array('name' => 'Document Type children Name', 'description' => 'Document Type children description', 'icon_id' => 1, 'defaultview_id' => $this->view->getId(), 'user_id' => $this->user->getId()));
$this->documentTypeChildren->save();
$insert = new Insert();
$insert->into('document_type_dependency')->values(array('parent_id' => $this->object->getId(), 'children_id' => $this->documentTypeChildren->getId()));
$this->object->execute($insert);
$insert = new Insert();
$insert->into('document_type_view')->values(array('view_id' => $this->view->getId(), 'document_type_id' => $this->object->getId()));
$this->object->execute($insert);
}
public function sendMessage($payload, $recipients, $from)
{
$defaultValues = array('response_to' => null);
$payload = array_merge($defaultValues, $payload);
$insert = new Insert($this->getTable());
$insert->columns(array('from', 'subject', 'message'))->values(array('from' => $from, 'subject' => $payload['subject'], 'message' => $payload['message'], 'datetime' => gmdate("Y-m-d H:i:s"), 'response_to' => $payload['response_to']));
$rows = $this->insertWith($insert);
$messageId = $this->lastInsertValue;
// Insert recipients
$values = array();
foreach ($recipients as $recipient) {
$read = 0;
if ((int) $recipient == (int) $from) {
$read = 1;
}
$values[] = "({$messageId}, {$recipient}, {$read})";
}
$valuesString = implode(',', $values);
//@todo sanitize and implement ACL
$sql = "INSERT INTO directus_messages_recipients (`message_id`, `recipient`, `read`) VALUES {$valuesString}";
$result = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
return $messageId;
}
/**
* {@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.");
}
public function sendMessage($payload, $recipients, $from)
{
$defaultValues = ['response_to' => null];
$payload = array_merge($defaultValues, $payload);
$insert = new Insert($this->getTable());
$insert->columns(['from', 'subject', 'message'])->values(['from' => $from, 'subject' => $payload['subject'], 'message' => $payload['message'], 'datetime' => DateUtils::now(), 'response_to' => $payload['response_to']]);
$rows = $this->insertWith($insert);
$messageId = $this->lastInsertValue;
// Insert recipients
$values = [];
foreach ($recipients as $recipient) {
$read = 0;
if ((int) $recipient == (int) $from) {
$read = 1;
}
$values[] = '(' . $messageId . ', ' . $recipient . ', ' . $read . ')';
}
$valuesString = implode(',', $values);
//@todo sanitize and implement ACL
$sql = 'INSERT INTO directus_messages_recipients (`message_id`, `recipient`, `read`) VALUES ' . $valuesString;
$result = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
return $messageId;
}
/**
* 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 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;
}
/**
* @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 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);
}
/**
* @param Insert $insert
* @return mixed
* @throws \Directus\Acl\Exception\UnauthorizedTableAddException
* @throws \Directus\Acl\Exception\UnauthorizedFieldWriteException
*/
protected function executeInsert(Insert $insert)
{
/**
* ACL Enforcement
*/
$insertState = $insert->getRawState();
$insertTable = $this->getRawTableNameFromQueryStateTable($insertState['table']);
if (!$this->acl->hasTablePrivilege($insertTable, 'add')) {
$aclErrorPrefix = $this->acl->getErrorMessagePrefix();
throw new UnauthorizedTableAddException($aclErrorPrefix . "Table add access forbidden on table {$insertTable}");
}
// Enforce write field blacklist (if user lacks bigedit privileges on this table)
if (!$this->acl->hasTablePrivilege($insertTable, 'bigedit')) {
$this->acl->enforceBlacklist($insertTable, $insertState['columns'], Acl::FIELD_WRITE_BLACKLIST);
}
try {
return parent::executeInsert($insert);
} catch (\Zend\Db\Adapter\Exception\InvalidQueryException $e) {
if ('production' !== DIRECTUS_ENV) {
if (strpos(strtolower($e->getMessage()), 'duplicate entry') !== FALSE) {
throw new DuplicateEntryException($e->getMessage());
}
throw new \RuntimeException("This query failed: " . $this->dumpSql($insert), 0, $e);
}
// @todo send developer warning
throw $e;
}
}
/**
* @covers Zend\Db\Sql\Insert::getSqlString
* @todo Implement testGetSqlString().
*/
public function testGetSqlString()
{
$this->insert->into('foo')->values(array('bar' => 'baz', 'boo' => new Expression('NOW()')));
$this->assertEquals('INSERT INTO "foo" ("bar", "boo") VALUES (\'baz\', NOW())', $this->insert->getSqlString());
}
/**
* @todo add $columns support
*
* @param Insert $insert
* @return mixed
* @throws Exception\RuntimeException
*/
protected function executeInsert(Insert $insert)
{
$insertState = $insert->getRawState();
if ($insertState['table'] != $this->table) {
throw new Exception\RuntimeException('The table name of the provided Insert object must match that of the table');
}
// apply preInsert features
$this->featureSet->apply('preInsert', array($insert));
$statement = $this->sql->prepareStatementForSqlObject($insert);
$result = $statement->execute();
$this->lastInsertValue = $this->adapter->getDriver()->getConnection()->getLastGeneratedValue();
// apply postInsert features
$this->featureSet->apply('postInsert', array($statement, $result));
return $result->getAffectedRows();
}
/**
* @todo add $columns support
*
* @param Insert $insert
* @return int
* @throws Exception\RuntimeException
*/
protected function executeInsert(Insert $insert)
{
$insertState = $insert->getRawState();
if ($insertState['table'] != $this->table) {
throw new Exception\RuntimeException('The table name of the provided Insert object must match that of the table');
}
// apply preInsert features
$this->featureSet->apply(EventFeatureEventsInterface::EVENT_PRE_INSERT, [$insert]);
// Most RDBMS solutions do not allow using table aliases in INSERTs
// See https://github.com/zendframework/zf2/issues/7311
$unaliasedTable = false;
if (is_array($insertState['table'])) {
$tableData = array_values($insertState['table']);
$unaliasedTable = array_shift($tableData);
$insert->into($unaliasedTable);
}
$statement = $this->sql->prepareStatementForSqlObject($insert);
$result = $statement->execute();
$this->lastInsertValue = $this->adapter->getDriver()->getConnection()->getLastGeneratedValue();
// apply postInsert features
$this->featureSet->apply(EventFeatureEventsInterface::EVENT_POST_INSERT, [$statement, $result]);
// Reset original table information in Insert instance, if necessary
if ($unaliasedTable) {
$insert->into($insertState['table']);
}
return $result->getAffectedRows();
}
/**
* @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 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;
}