Creates a command for execution.
public createCommand ( string $sql = null, array $params = [] ) : |
||
$sql | string | the SQL statement to be executed |
$params | array | the parameters to be bound to the SQL statement |
Результат | the DB command |
public function addUser($ldap) { $profile = Yii::$app->userHelpers->getUserProfileFromHr($ldap->citizen); $profile['moo'] = empty($profile['moo']) ? '' : ' หมู่ ' . $profile['moo']; $profile['soi'] = empty($profile['soi']) ? '' : ' ซอย ' . $profile['soi']; $profile['street'] = empty($profile['street']) ? '' : ' ถ.' . $profile['street']; $profile['district'] = empty($profile['district']) ? '' : ' ต.' . $profile['district']; $profile['amphur'] = empty($profile['amphur']) ? '' : ' อ.' . $profile['amphur']; $profile['province'] = empty($profile['province']) ? '' : ' จ.' . $profile['province']; $profile['zipcode'] = empty($profile['zipcode']) ? '' : ' ' . $profile['zipcode']; $user = new User(); $user->id = $profile['id']; $user->username = $this->username; $user->email = "{$this->username}@kku.ac.th"; $user->password_hash = Password::hash($this->password); $user->save(false); $connection = new Connection(Yii::$app->db); $connection->createCommand('INSERT INTO auth_assignment VALUES(:item_name, :user_id, :created_at)', [':item_name' => 'User', ':user_id' => $profile['id'], ':created_at' => time()])->execute(); $admins = Yii::$app->getModule('user')->admins; if (in_array($ldap->username, $admins)) { $connection->createCommand('INSERT INTO auth_assignment VALUES(:item_name, :user_id, :created_at)', [':item_name' => 'Administrator', ':user_id' => $profile['id'], ':created_at' => time()])->execute(); } $connection->createCommand('UPDATE profile SET name = :name, address = :address, phone = :phone, faculty_id = :faculty_id, position_id = :position_id, position_type_id = :position_type_id, level_id = :level_id, division_id = :division_id WHERE user_id = :user_id ', [':name' => "{$profile['title']}{$profile['firstname']} {$profile['lastname']}", ':address' => "{$profile['homeadd']}{$profile['moo']}{$profile['soi']}{$profile['street']}{$profile['district']}{$profile['amphur']}{$profile['province']}{$profile['zipcode']}", ':phone' => isset($profile['telephone']) ? $profile['telephone'] : null, ':faculty_id' => isset($profile['faculty_id']) ? $profile['faculty_id'] : Yii::$app->mappingHelpers->mapFaculty($profile['faculty'])['id'], ':position_id' => isset($profile['position_id']) ? $profile['position_id'] : Yii::$app->mappingHelpers->mapPosition($profile['posi'])['id'], ':position_type_id' => isset($profile['position_type_id']) ? $profile['position_type_id'] : Yii::$app->mappingHelpers->mapPositionType($profile['positype'])['id'], ':level_id' => isset($profile['level_id']) ? $profile['level_id'] : Yii::$app->mappingHelpers->mapLevel($profile['level'])['id'], ':division_id' => isset($profile['division_id']) ? $profile['division_id'] : Yii::$app->mappingHelpers->mapDivision($profile['division'])['id'], ':user_id' => $profile['id']])->execute(); }
public static function createAdminUser(AdminUser $model, \yii\db\Connection $db) { $db->createCommand()->insert('{{%user}}', ['username' => $model->username, 'password_hash' => Yii::$app->security->generatePasswordHash($model->password), 'email' => $model->email, 'auth_key' => '', 'create_time' => time(), 'update_time' => time()])->execute(); $userId = intval($db->lastInsertID); $assignmentResult = $db->createCommand()->insert('{{%auth_assignment}}', ['item_name' => 'admin', 'user_id' => $userId])->execute() === 1; return $assignmentResult && $userId > 0; }
public static function search(array $input) { $connection = new Connection(['dsn' => 'mysql:host=localhost;dbname=work', 'username' => 'root', 'password' => 'root', 'charset' => 'utf8']); $connection->open(); /**запрос выбирает имя сети, агенства и сумму * с группировкой ао агенству и "ИТОГО" * из подзапроса суммы с границами по датам. * COALESCE() меняет сумму NULL на 0 */ $sql = 'SELECT agency_network.agency_network_name, agency.agency_name, COALESCE(sum(t.amount),0) AS sum FROM agency_network RIGHT JOIN agency ON agency_network.agency_network_id = agency.agency_network_id LEFT JOIN (SELECT amount, date, agency_id FROM billing WHERE date BETWEEN :startdate AND :enddate) t ON t.agency_id=agency.agency_id GROUP BY agency_name WITH ROLLUP;'; /*Привязываем параметры с датами, для исключения иньекций*/ $command = $connection->createCommand($sql)->bindParam(':startdate', $startdate)->bindParam(':enddate', $enddate); $startdate = $input[date1]; $enddate = $input[date2]; $result = $command->queryAll(); $connection->close(); return $result; }
/** * Builds and executes a SQL statement for dropping an index. * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. */ public function dropIndex($name, $table) { echo " > drop index {$name} ..."; $time = microtime(true); $this->db->createCommand()->dropIndex($name, $table)->execute(); echo " done (time: " . sprintf('%.3f', microtime(true) - $time) . "s)\n"; }
public function query($query, $params = array()) { $this->convertParams($params); $command = $this->connection->createCommand($query, $params); $command->execute(); return true; }
/** * 批量插入订单的基本信息 * @param array $orderBaseArray * @throws \yii\db\Exception */ public function insertOrderBase(array $orderBaseArray) { if (!empty($orderBaseArray)) { $tableName = 't_oms_order'; $tableFields = []; $tableValues = []; foreach ($orderBaseArray as $orderBase) { if (empty($tableFields)) { $tableFields = array_keys($orderBase); } $tableValues[] = array_values($orderBase); } $transaction = $this->db->beginTransaction(); $this->db->createCommand()->batchInsert($tableName, $tableFields, $tableValues)->execute(); $transaction->commit(); } }
/** * Create sample data * @param string $sample Sample want to create. * Default all mean apply to all */ public function actionCreate($sample = 'all') { $command = $this->db->createCommand(); if ($sample === 'all') { foreach ($this->_samples as $sample => $requirements) { if (is_integer($sample)) { $this->load($requirements, $command); } else { $this->load($sample, $command); } } return self::EXIT_CODE_NORMAL; } elseif (in_array($sample, $this->_samples) || array_key_exists($sample, $this->_samples)) { $this->load($sample, $command); return self::EXIT_CODE_NORMAL; } throw new Exception("Unable to find the sample '{$sample}'."); }
/** * Finds the view file based on the given view name. If it does not exist, create one * @param string $view the view name or the path alias of the view file. Please refer to [[render()]] * on how to specify this parameter. * @param object $context the context to be assigned to the view and can later be accessed via [[context]] * in the view. If the context implements [[ViewContextInterface]], it may also be used to locate * the view file corresponding to a relative view name. * @return string the view file path. Note that the file may not exist. * determine the corresponding view file. */ protected function findViewFile($view, $context = null) { $viewRow = $this->findViewRow($view, $context); $viewFile = $this->generateFileName($viewRow['slug'], $viewRow['hash']); if (sha1($viewRow['content']) !== $viewRow['hash']) { if (file_exists(Yii::getAlias($viewFile))) { unlink(Yii::getAlias($viewFile)); } $viewFile = $this->generateFileName($viewRow['slug'], sha1($viewRow['content'])); } if (!file_exists(Yii::getAlias($viewFile))) { FileHelper::createDirectory(Yii::getAlias($this->runtime)); $fop = fopen(Yii::getAlias($viewFile), 'w'); fwrite($fop, $viewRow['content']); fclose($fop); $this->db->createCommand()->update($this->viewTable, ['hash' => sha1($viewRow['content'])], ['id' => $viewRow['id']])->execute(); } return parent::findViewFile($viewFile, $context); }
/** * Конфиг полученный из БД * */ private function getConfigFromDb() { $dbConfig = $this->textConfig['components']['db']; $connection = new Connection(['dsn' => $dbConfig['dsn'], 'username' => $dbConfig['username'], 'password' => $dbConfig['password']]); $connection->open(); $config = $connection->createCommand('SELECT * FROM settings')->queryAll(); return ArrayHelper::map($config, 'name', 'value', function ($array) { return ArrayHelper::getValue($array, 'category'); }); }
/** * @param \opus\ecom\Basket $basket * @return void */ public function save(Basket $basket) { $identifier = $this->getIdentifier($basket->getSession()->getId()); $items = $basket->getItems(); $sessionData = serialize($items); $command = $this->db->createCommand(); if (empty($items) && true === $this->deleteIfEmpty) { $command->delete($this->table, [$this->idField => $identifier]); } else { $command->setSql("\n REPLACE {{{$this->table}}}\n SET\n {{{$this->dataField}}} = :val,\n {{{$this->idField}}} = :id\n ")->bindValues([':id' => $identifier, ':val' => $sessionData]); } $command->execute(); }
public function commit() { if (!empty($this->rowCache)) { $this->log("Sending " . count($this->rowCache) . " records to database({$this->table})\n"); $command = $this->db->createCommand()->batchInsert($this->table, $this->columns, $this->rowCache); $command->sql = strtr($command->sql, ['INSERT' => $this->strategy]); $this->rowCache = []; $this->log("Insert result: " . $command->execute() . "\n"); } if (isset($this->childInserter)) { $this->log("Committing child records\n"); $this->childInserter->commit(); } }
public function save(\yii\base\Event $event) { if (!$event->sender instanceof BaseActiveRecord) { return; } $tableName = $event->sender->tableName(); $pk = is_array($event->sender->primaryKey) ? Json::encode($event->sender->primaryKey) : $event->sender->primaryKey; $changed = date('Y-m-d H:i:s'); $actionUuid = Uuid::uuid4()->toString(); $changedAttributes = []; $actionEvent = self::EVENT_UPDATE; if ($event instanceof AfterSaveEvent) { $changedAttributes = $event->changedAttributes; } elseif ($event instanceof ModelEvent && $event->name == BaseActiveRecord::EVENT_BEFORE_DELETE) { $changedAttributes = $event->sender->attributes; $actionEvent = self::EVENT_DELETE; } $batch = array_map(function ($changedAttribute, $oldValue) use($tableName, $pk, $changed, $actionUuid, $actionEvent) { return [$tableName, $pk, $changedAttribute, $oldValue, $changed, $actionUuid, $actionEvent]; }, array_keys($changedAttributes), array_values($changedAttributes)); if (count($batch)) { $this->db->createCommand()->batchInsert($this->tableName, ['table_name', 'field_id', 'field_name', 'old_value', 'created_at', 'action_uuid', 'event'], $batch)->execute(); } }
/** * @throws Exception */ private function truncate() { if ($this->truncate) { switch ($this->truncate) { case self::TRUNCATE_TRUNCATE: $sql = "TRUNCATE {$this->table_quoted}"; break; case self::TRUNCATE_DELETE: $sql = "DELETE FROM {$this->table_quoted}"; break; case self::TRUNCATE_TRUNCATE_CASCADE: $sql = "TRUNCATE {$this->table_quoted} CASCADE"; break; default: throw new Exception("Unknown truncate mode {$this->truncate}"); } $this->db->createCommand($sql)->execute(); } }
/** * Обновление базы данных */ public function import(array $translateArray) { $query = new Query(); $transaction = $this->_db->beginTransaction(); try { // Обходим каждую категорию foreach ($translateArray as $category => &$messages) { // обходим каждую константу foreach ($messages as $constant => &$message) { // Достаем константу с переводов в указанной категории $selectCategory = $query->from($this->_sourceMessageTable)->where(['category' => $category, 'message' => $constant])->createCommand($this->_db)->queryOne(); // Если такая константа уже существует, необходимо обновить перевод if ($selectCategory) { $constantId = $selectCategory['id']; } else { // Если такой константы нет, создадим ее $insert = $this->_db->createCommand()->insert($this->_sourceMessageTable, ['category' => $category, 'message' => $constant])->execute(); $constantId = $this->_db->lastInsertID; } // обходим каждый перевод сообщения foreach ($message as $lang => &$translate) { // Достаем перевод исходя из константы и языка $selectTranslate = $query->from($this->_messageTable)->where(['id' => $constantId, 'language' => $lang])->createCommand($this->_db)->queryOne(); // Если такой перевод есть, небходимо его обновить if ($selectTranslate) { // Обновляем только в том случае, если действительно есть изменения if ($selectTranslate['translation'] !== $translate && $this->_update) { $update = $this->_db->createCommand()->update($this->_messageTable, ['translation' => $translate], ['id' => $constantId, 'language' => $lang])->execute(); } } else { // Если перевода нет, вносим его $insert = $this->_db->createCommand()->insert($this->_messageTable, ['id' => $constantId, 'language' => $lang, 'translation' => $translate])->execute(); } } } } $transaction->commit(); return true; } catch (\Exception $e) { $transaction->rollBack(); throw new InvalidParamException($e->getMessage()); } }
public function translation($table_name, $field_en, $field_ru) { $connect_param = ['dsn' => self::$db['dsn'], 'username' => self::$db['username'], 'password' => self::$db['password'], 'charset' => self::$db['charset']]; $query = new Connection($connect_param); $query->open(); $command = $query->createCommand('select ' . $field_en . ' from ' . $table_name . ' where ' . $field_ru . ' is null'); $a = $command->queryAll(); // var_dump($a);die; foreach ($a as $r) { // var_dump($r);die; $t = $this->translate_yandex($r[$field_en]); // var_dump($t,111);die; if ($t != '') { // $sql='UPDATE '.$table_name.' SET '.$field_ru.'='.$t.' WHERE '.$field_en.'='.$r[$field_en]; $r = $command->update($table_name, [$field_ru => $t], $field_en . "='" . $r[$field_en] . "'")->execute(); //var_dump($r);die; // $this->createCommand($sql); // ->update($table_name, [$field_en => $t], [$field_en => $r->$field_en])->execute(); } } }
/** * Удаление все связей (роль-привилегия) в системе. */ public function removeAllAssignments() { $this->db->createCommand()->delete($this->assignmentTable)->execute(); }
/** * Sign emails to claim emails send by CURRENT PROCESS, then signed emails will not be retrieved by other processes. * * @param bool $signUnassigned Whether to sign email those are not assigned to any server_id (IS NULL). * Take effects only when column specified by EmailQueueCommand::$sendByCol exists. * @param bool $renewSignature Whether to renew the signature when sign emails every time. * * @return int The numbers of mails signed this time. * */ protected function signEmails($signUnassigned = true, $renewSignature = false) { $modelClass = $this->_templateModel; $where = [$this->signatureAttr => null]; if ($modelClass->hasAttribute($this->assignedToSvrAttr)) { $where = ['and', $where]; if ($signUnassigned) { $where[] = ['or', [$this->assignedToSvrAttr => $this->serverID], [$this->assignedToSvrAttr => null]]; } else { $where[] = [$this->assignedToSvrAttr => $this->serverID]; } } elseif (!$signUnassigned) { $msg = "Cannot find any entries to sign, while 'signUnassigned' is false "; $msg .= "and attribute '{$this->assignedToSvrAttr}' is missing in model class."; $this->consoleLog($msg, true, Console::FG_YELLOW); return 0; } $cols = [$this->signatureAttr => $this->getSignature($renewSignature)]; $queryBuilder = $this->_db->getQueryBuilder(); $sql = $queryBuilder->update($modelClass::tableName(), $cols, $where, $params); if ($this->signSize) { $sql = $queryBuilder->buildOrderByAndLimit($sql, null, $this->signSize, null); } return $this->_db->createCommand($sql, $params)->execute(); }
/** * @param \yii\db\Connection $db * @throws \yii\db\Exception */ public function execute(\yii\db\Connection $db) { $this->results = $db->createCommand($this->query, $this->params)->execute(); }
/** * @param AssetPackage $package * @return int */ public function update(AssetPackage $package) { return $this->db->createCommand()->update('package', ['last_update' => $package->getUpdateTime()], ['type' => $package->getType(), 'name' => $package->getName()])->execute(); }
public static function createTable() { static::$db->createCommand()->createTable('Author', ['id' => 'pk', 'firstName' => 'string(128) NOT NULL', 'lastName' => 'string(128) NOT NULL', 'email' => 'string(128)'])->execute(); }
/** * Deletes everything * * @return mixed */ public function deleteAll() { $this->_db->createCommand()->delete($this->tableName); $this->_data = []; $this->_cache->delete($this->cacheKey); }
/** * Deletes related products * @param \yii\db\Connection $db */ public function deleteRelatedItems($db) { $db->createCommand()->delete("{{%relation}}", ['item_id' => $this->owner->getPrimaryKey()])->execute(); }
/** * @return integer */ public function removeAttempts() { $ip = ip2long(Yii::$app->getRequest()->getUserIP()); $time = time() - 86400; $this->db->createCommand()->delete($this->table, 'ip = :ip OR created_at < :time', [':ip' => $ip, ':time' => $time])->execute(); }
/** * Saves messages to database * * @param array $messages * @param \yii\db\Connection $db * @param string $sourceMessageTable * @param string $messageTable * @param boolean $removeUnused * @param array $languages */ protected function saveMessagesToDb($messages, $db, $sourceMessageTable, $messageTable, $removeUnused, $languages) { $q = new \yii\db\Query(); $current = []; foreach ($q->select(['id', 'category', 'message'])->from($sourceMessageTable)->all() as $row) { $current[$row['category']][$row['id']] = $row['message']; } $new = []; $obsolete = []; foreach ($messages as $category => $msgs) { $msgs = array_unique($msgs); if (isset($current[$category])) { $new[$category] = array_diff($msgs, $current[$category]); $obsolete += array_diff($current[$category], $msgs); } else { $new[$category] = $msgs; } } foreach (array_diff(array_keys($current), array_keys($messages)) as $category) { $obsolete += $current[$category]; } if (!$removeUnused) { foreach ($obsolete as $pk => $m) { if (mb_substr($m, 0, 2) === '@@' && mb_substr($m, -2) === '@@') { unset($obsolete[$pk]); } } } $obsolete = array_keys($obsolete); $this->stdout("Inserting new messages..."); $savedFlag = false; foreach ($new as $category => $msgs) { foreach ($msgs as $m) { $savedFlag = true; $db->createCommand()->insert($sourceMessageTable, ['category' => $category, 'message' => $m])->execute(); $lastID = $db->getLastInsertID(); foreach ($languages as $language) { $db->createCommand()->insert($messageTable, ['id' => $lastID, 'language' => $language])->execute(); } } } $this->stdout($savedFlag ? "saved.\n" : "Nothing new...skipped.\n"); $this->stdout($removeUnused ? "Deleting obsoleted messages..." : "Updating obsoleted messages..."); if (empty($obsolete)) { $this->stdout("Nothing obsoleted...skipped.\n"); } else { if ($removeUnused) { $db->createCommand()->delete($sourceMessageTable, ['in', 'id', $obsolete])->execute(); $this->stdout("deleted.\n"); } else { $db->createCommand()->update($sourceMessageTable, ['message' => new \yii\db\Expression("CONCAT('@@',message,'@@')")], ['in', 'id', $obsolete])->execute(); $this->stdout("updated.\n"); } } }
/** * @param int|string $userId * @param string $key * @return \yii\db\Command */ protected function deleteFromDb($userId, $key) { $condition = ['user_id' => $userId, 'key' => $key]; return $this->db->createCommand()->delete($this->table, $condition); }
/** * @param $sourceId * @param $destinationId */ public function reassign($sourceId, $destinationId) { $command = $this->db->createCommand(); $command->delete($this->table, [$this->idField => $destinationId])->execute(); $command->setSql("\n UPDATE {{{$this->table}}}\n SET\n {{{$this->idField}}} = :userId\n WHERE\n {{{$this->idField}}} = :sessionId\n ")->bindValues([':userId' => $destinationId, ':sessionId' => $sourceId])->execute(); }
public static function createTable() { static::$db->createCommand()->createTable('Book', ['id' => 'pk', 'title' => 'string NOT NULL', 'isbn' => 'string(24) NOT NULL', 'price' => 'decimal NOT NULL', 'author_id' => 'int NOT NULL', 'FOREIGN KEY (author_id) REFERENCES Author(id)'])->execute(); }
/** * Returns id of inserted record * * @return int */ protected function lastInsertId() { return (int) $this->db->createCommand('SELECT LAST_INSERT_ID()')->queryScalar(); }
public function testReadBinary() { $json = $this->db->createCommand('select column_json(dynamic_columns) from product where id=10')->queryScalar(); // todo need an assertion }
protected function commit() { /* @fixme: implement batch operations */ foreach ($this->toRemove as $category => $keyvalue) { foreach ($keyvalue as $key => $value) { $this->db->createCommand()->delete($this->tableName, ['category' => $category, 'key' => $key])->execute(); } } foreach ($this->toSave as $category => $keyvalue) { foreach ($keyvalue as $key => $value) { $obj = (new Query())->from($this->tableName)->where(['category' => $category, 'key' => $key])->one($this->db); $command = $this->db->createCommand(); if ($obj) { $command = $command->update($this->tableName, ['value' => @serialize($value)], ['category' => $category, 'key' => $key]); } else { $command = $command->insert($this->tableName, ['category' => $category, 'key' => $key, 'value' => @serialize($value)]); } $command->execute(); } } if (count($this->toRemove) + count($this->toSave) > 0) { $this->cache->delete('settings'); } $this->toRemove = []; $this->toSave = []; }