Example of usage
use Bluz\Proxy\Db;
Db::fetchAll('SELECT * FROM users');
/** * */ public function testCreateMusician() { $this->dispatchRouter('/pages/crud/', ['title' => 'splinter', 'alias' => 'test', 'content' => 'qqqqqqqqqqqq', 'keywords' => 'qqq', 'description' => 'test', 'created' => '2014-09-02', 'updated' => '2014-09-02', 'userId' => 1], Http\Request::METHOD_POST); // $this->assertOk(); $count = Db::fetchOne('SELECT count(*) FROM pages WHERE title = ?', ['splinter']); $this->assertEquals($count, 1); }
/** * {@inheritdoc} * * @param null $sequence * @return integer|string|array */ public function execute($sequence = null) { $result = Db::query($this->getSQL(), $this->params, $this->types); if ($result) { return Db::handler()->lastInsertId($sequence); } return $result; }
/** * {@inheritdoc} * * @param integer|string|object $fetchType * @return integer|string|array */ public function execute($fetchType = null) { if (!$fetchType) { $fetchType = $this->fetchType; } switch ($fetchType) { case !is_int($fetchType): return Db::fetchObjects($this->getSQL(), $this->params, $fetchType); case \PDO::FETCH_CLASS: return Db::fetchObjects($this->getSQL(), $this->params); case \PDO::FETCH_ASSOC: default: return Db::fetchAll($this->getSQL(), $this->params); } }
/** * generate action with token * * @param int $userId * @param string $action * @param int $expired in days * @param array $params * @return Row */ public function generate($userId, $action, $expired = 5, $params = []) { // remove previously generated tokens Db::delete($this->table)->where('userId = ?', $userId)->andWhere('action = ?', $action)->execute(); // create new row $actionRow = new Row(); $actionRow->userId = $userId; $actionRow->action = $action; $random = range('a', 'z', rand(1, 5)); shuffle($random); $actionRow->code = md5($userId . $action . join('', $random) . time()); $actionRow->expired = date('Y-m-d H:i:s', strtotime("+{$expired} day")); $actionRow->params = $params; $actionRow->save(); return $actionRow; }
/** * Process * * @param array $settings * @return \Bluz\Grid\Data */ public function process(array $settings = []) { // process filters if (!empty($settings['filters'])) { foreach ($settings['filters'] as $column => $filters) { foreach ($filters as $filter => $value) { if ($filter == Grid\Grid::FILTER_LIKE) { $value = '%' . $value . '%'; } $this->source->andWhere($column . ' ' . $this->filters[$filter] . ' ?', $value); } } } // process orders if (!empty($settings['orders'])) { // Obtain a list of columns foreach ($settings['orders'] as $column => $order) { $this->source->addOrderBy($column, $order); } } // process pages $this->source->setLimit($settings['limit']); $this->source->setPage($settings['page']); // prepare query $connect = Proxy\Config::getData('db', 'connect'); if (strtolower($connect['type']) == 'mysql') { // MySQL $select = $this->source->getQueryPart('select'); $this->source->select('SQL_CALC_FOUND_ROWS ' . current($select)); // run queries $data = $this->source->execute(); $total = Proxy\Db::fetchOne('SELECT FOUND_ROWS()'); } else { // other $totalSource = clone $this->source; $totalSource->select('COUNT(*)'); // run queries $data = $this->source->execute(); $total = $totalSource->execute(); } $gridData = new Grid\Data($data); $gridData->setTotal($total); return $gridData; }
/** * {@inheritdoc} * * @param int $offset * @param int $limit * @param array $params * @return array|int|mixed */ public function readSet($offset = 0, $limit = 10, $params = array()) { $select = Db::select('*')->from('test', 't'); if ($limit) { $selectPart = $select->getQueryPart('select'); $selectPart = 'SQL_CALC_FOUND_ROWS ' . current($selectPart); $select->select($selectPart); $select->setLimit($limit); $select->setOffset($offset); } $result = $select->execute('\\Application\\Test\\Row'); if ($limit) { $total = Db::fetchOne('SELECT FOUND_ROWS()'); } else { $total = sizeof($result); } if (sizeof($result) < $total && Request::METHOD_GET == Request::getMethod()) { Response::setStatusCode(206); Response::setHeader('Content-Range', 'items ' . $offset . '-' . ($offset + sizeof($result)) . '/' . $total); } return $result; }
/** * Drop photo after the test */ public static function tearDownAfterClass() { Db::delete('media')->where('userId', [1])->execute(); $path = Config::getModuleData('media', 'upload_path') . '/1'; Tools\Cleaner::delete($path); }
/** * Set key-value pair * * Sets a new value for a column in a insert/update query * $ub = new UpdateBuilder(); * $ub * ->update('users') * ->set('password', md5('password')) * ->where('id = ?'); * * @param string $key The column to set * @param string $value The value, expression, placeholder, etc * @return Insert|Update */ public function set($key, $value) { $this->setParameter(null, $value, \PDO::PARAM_STR); $key = Db::quoteIdentifier($key); return $this->addQueryPart('set', $key . ' = ?', true); }
/** * Get user privileges * * @param integer $roleId * @return array */ public function getRolePrivileges($roleId) { $cacheKey = 'privileges:role:' . $roleId; if (!($data = Cache::get($cacheKey))) { $data = Db::fetchColumn("SELECT DISTINCT CONCAT(p.module, ':', p.privilege)\n FROM acl_privileges AS p, acl_roles AS r\n WHERE p.roleId = r.id AND r.id = ?\n ORDER BY CONCAT(p.module, ':', p.privilege)", array((int) $roleId)); Cache::set($cacheKey, $data, Cache::TTL_NO_EXPIRY); Cache::addTag($cacheKey, 'privileges'); } return $data; }
<?php /** * Grid of users * * @author Anton Shevchuk * @created 02.08.12 18:39 * @return closure */ namespace Application; use Application\Users; use Bluz\Proxy\Db; use Bluz\Proxy\Layout; return function () use($view, $module, $controller) { /** * @var Bootstrap $this * @var \Bluz\View\View $view */ Layout::setTemplate('dashboard.phtml'); Layout::breadCrumbs([$view->ahref('Dashboard', ['dashboard', 'index']), __('Users')]); $grid = new Users\Grid(); $grid->setModule($module); $grid->setController($controller); $view->roles = Db::fetchAll('SELECT * FROM acl_roles'); $view->grid = $grid; };
/** * DELETE request with PRIMARY */ public function testDelete() { $this->dispatchRouter('/test/rest/1', null, Http\Request::METHOD_DELETE); $this->assertResponseCode(204); $count = Db::fetchOne('SELECT count(*) FROM `test` WHERE `id` = ?', [1]); $this->assertEquals($count, 0); }
/** * Get set of records * * @param int $offset * @param int $limit * @param array $params * @param int $total * @return array|int|mixed * @throws ApplicationException */ public function readSet($offset = 0, $limit = 10, $params = [], &$total = null) { $select = $this->getTable()->select(); // switch statement for DB type $type = Proxy\Db::getOption('connect', 'type'); switch ($type) { case 'mysql': $selectPart = $select->getQueryPart('select'); $selectPart = 'SQL_CALC_FOUND_ROWS ' . current($selectPart); $select->select($selectPart); $totalSQL = 'SELECT FOUND_ROWS()'; break; case 'pgsql': default: $selectTotal = clone $select; $selectTotal->select('COUNT(*)'); $totalSQL = $selectTotal->getSql(); break; } $select->setLimit($limit); $select->setOffset($offset); // run queries // use transaction to avoid errors Proxy\Db::transaction(function () use(&$result, &$total, $select, $totalSQL) { $result = $select->execute(); if (!is_null($total)) { $total = Proxy\Db::fetchOne($totalSQL); } }); return $result; }
/** * DELETE request should remove record */ public function testDelete() { Request::setMethod(Request::METHOD_DELETE); Request::setParams(['id' => 3]); $result = $this->processCrud(); $this->assertEquals(1, $result); $count = Db::fetchOne('SELECT count(*) FROM test WHERE id = ?', [3]); $this->assertEquals(0, $count); }
<?php /** * Grid of users * * @author Anton Shevchuk * @created 02.08.12 18:39 * @return closure */ namespace Application; use Application\Users; use Bluz\Controller\Controller; use Bluz\Proxy\Db; use Bluz\Proxy\Layout; /** * @privilege Management * @return \closure */ return function () { /** * @var Controller $this */ Layout::setTemplate('dashboard.phtml'); Layout::breadCrumbs([Layout::ahref('Dashboard', ['dashboard', 'index']), __('Users')]); $grid = new Users\Grid(); $grid->setModule($this->module); $grid->setController($this->controller); $this->assign('roles', Db::fetchAll('SELECT * FROM acl_roles')); $this->assign('grid', $grid); };
/** * tearDown */ public function tearDown() { parent::tearDown(); Proxy\Db::delete('test')->where('email = ?', '*****@*****.**')->execute(); }
/** * DELETE request with PRIMARY */ public function testDelete() { Request::setMethod(Request::METHOD_DELETE); Request::setRawParams([1]); $result = $this->processRest(); $this->assertFalse($result); $count = Db::fetchOne('SELECT count(*) FROM test WHERE id = ?', [1]); $this->assertEquals($count, 0); }
* @accept HTML * @accept JSON * @privilege Management * * @param int $id * @return bool * @throws Exception */ return function ($id) { /** * @var Controller $this */ $user = Users\Table::findRow($id); if (!$user) { throw new Exception('User ID is incorrect'); } if (Request::isPost()) { $roles = Request::getParam('roles'); // update roles Db::delete('acl_users_roles')->where('userId = ?', $user->id)->execute(); foreach ($roles as $role) { Db::insert('acl_users_roles')->set('userId', $user->id)->set('roleId', $role)->execute(); } // clean cache Cache::delete('user:'******'User roles was updated'); return false; } $this->assign('user', $user); $this->assign('roles', Roles\Table::getInstance()->getRoles()); };
/** * Can not update row because of validation problem */ public function testUpdateValidationError() { Db::insert('musician')->setArray(['id' => 3, 'nickname' => 'testupdate', 'group' => 'test2', 'concertDate' => '2015-11-10', 'alias' => 'test3', 'image' => '56kj898f3f39.jpg'])->execute(); Db::insert('musician')->setArray(['id' => 9, 'nickname' => 'testUpdate', 'group' => 'test9', 'concertDate' => '2015-11-10', 'alias' => 'test9', 'image' => '5sdgf3f39.jpg'])->execute(); $this->dispatchRouter('/musician/crud/', ['id' => 3, 'alias' => 'test9'], Http\Request::METHOD_PUT); $row = Table::getInstance()->getByAlias('test9'); // $count = Db::fetchOne('SELECT count(*) FROM musician WHERE alias = ?', ['test9']); $this->assertEquals($row ? 1 : 0, 1); }
/** * @return array */ public function prepareTree() { $result = Db::fetchGroup('SELECT id, categories.* FROM categories ORDER BY `order`'); $result = array_map('reset', $result); return $result; }
/** * create dish */ private function createTestDish() { return Db::insert('dishes')->setArray($this->validData)->execute(); }
/** * Get all user roles in system * * @param integer $userId * @return array of identity */ public function getUserRolesIdentity($userId) { $cacheKey = 'roles:user:'******'roles'); Cache::addTag($cacheKey, 'user:' . $userId); } return $data; }
public static function getUnusedMedia() { $select = "select m.* from media m where id NOT IN (SELECT mediaId FROM dishes_media)"; return Db::fetchAll($select); }
/** * setFromQueryPart * * @param string $table * @return $this */ protected function setFromQueryPart($table) { $table = Db::quoteIdentifier($table); return $this->addQueryPart('from', ['table' => $table], false); }
/** * Deletes existing rows * * <code> * Table::delete(['login' => 'Man']) * </code> * * @param array $where An array of SQL WHERE clause(s) * @return integer The number of rows deleted * @throws Exception\DbException */ public static function delete(array $where) { if (!sizeof($where)) { throw new DbException("Method `Table::delete()` can't delete all records in table,\n" . "please use `Db::query()` instead (of cause if you know what are you doing)"); } $self = static::getInstance(); $where = $self->filterColumns($where); if (!sizeof($where)) { throw new DbException("Invalid field names of table `{$self->table}`. Please check use of `delete()` method"); } $table = DbProxy::quoteIdentifier($self->table); $sql = "DELETE FROM {$table}" . " WHERE " . join(' AND ', self::prepareStatement($where)); return DbProxy::query($sql, array_values($where)); }
<?php /** * Delete image from redactor * * @author Anton Shevchuk * @created 19.06.13 18:18 */ /** * @namespace */ namespace Application; use Bluz\Proxy\Db; return function ($path) { $this->useJson(); /** * @var Users\Row $user */ if (!$this->user()) { throw new Exception('User not found'); } $userId = $this->user()->id; $result = Db::delete('media')->where('type LIKE (?)', 'image/%')->andWhere('file = ?', $path)->andWhere('userId = ?', $userId)->execute(); return $result; };
use Bluz\Proxy\Cache; use Bluz\Proxy\Db; use Bluz\Proxy\Messages; return function ($acl) use($view) { /** * @var Bootstrap $this * @var \Bluz\View\View $view */ $callback = function () use($acl) { /** * @var Bootstrap $this */ Db::query('DELETE FROM acl_privileges'); foreach ($acl as $roleId => $modules) { foreach ($modules as $module => $privileges) { foreach ($privileges as $privilege => $flag) { Db::query('INSERT INTO acl_privileges SET roleId = ?, module = ?, privilege = ?', array($roleId, $module, $privilege)); } } } }; if (empty($acl)) { Messages::addError('Privileges set is empty. You can\'t remove all of them'); } elseif (Db::transaction($callback)) { Cache::deleteByTag('privileges'); Messages::addSuccess('All data was saved'); } else { Messages::addError('Internal Server Error'); } $this->redirectTo('acl', 'index'); };
protected function tearDown() { Db::delete('users')->where('id IN (?)', [1, 2])->execute(); Messages::popAll(); }
* @created 14.11.13 10:45 */ namespace Application; use Bluz\Db\Relations; use Bluz\Proxy\Db; use Bluz\Proxy\Layout; return function () use($view) { /** * @var Bootstrap $this * @var \Bluz\View\View $view */ Layout::breadCrumbs([$view->ahref('Test', ['test', 'index']), 'DB Relations']); /* @var Pages\Row */ $page = Pages\Table::findRow(5); $user = $page->getRelation('Users'); echo "<h2>Page Owner</h2>"; var_dump($user); $pages = $user->getRelations('Pages'); echo "<h2>User pages</h2>"; var_dump(sizeof($pages)); $roles = $user->getRelations('Roles'); echo "<h2>User roles</h2>"; var_dump($roles); echo "<h2>User with all relations</h2>"; var_dump($user); $result = Db::fetchRelations("SELECT '__users', u.*, '__pages', p.* FROM users u LEFT JOIN pages p ON p.userId = u.id"); echo "<h2>User - Page relation</h2>"; var_dump($result); return false; };
/** * Process * * @param array $settings * @return \Bluz\Grid\Data */ public function process(array $settings = []) { // process filters $where = []; if (!empty($settings['filters'])) { foreach ($settings['filters'] as $column => $filters) { foreach ($filters as $filter => $value) { if ($filter == Grid\Grid::FILTER_LIKE) { $value = '%' . $value . '%'; } $where[] = $column . ' ' . $this->filters[$filter] . ' ' . Proxy\Db::quote($value); } } } // process orders $orders = []; if (!empty($settings['orders'])) { // Obtain a list of columns foreach ($settings['orders'] as $column => $order) { $column = Proxy\Db::quoteIdentifier($column); $orders[] = $column . ' ' . $order; } } // process pages $limit = ' LIMIT ' . ($settings['page'] - 1) * $settings['limit'] . ', ' . $settings['limit']; // prepare query $connect = Proxy\Config::getData('db', 'connect'); if (strtolower($connect['type']) == 'mysql') { // MySQL $dataSql = preg_replace('/SELECT\\s(.*?)\\sFROM/is', 'SELECT SQL_CALC_FOUND_ROWS $1 FROM', $this->source, 1); $countSql = 'SELECT FOUND_ROWS()'; } else { // other $dataSql = $this->source; $countSql = preg_replace('/SELECT\\s(.*?)\\sFROM/is', 'SELECT COUNT(*) FROM', $this->source, 1); if (sizeof($where)) { $countSql .= ' WHERE ' . join(' AND ', $where); } } if (sizeof($where)) { $dataSql .= ' WHERE ' . join(' AND ', $where); } if (sizeof($orders)) { $dataSql .= ' ORDER BY ' . join(', ', $orders); } $dataSql .= $limit; // run queries $data = Proxy\Db::fetchAll($dataSql); $total = Proxy\Db::fetchOne($countSql); $gridData = new Grid\Data($data); $gridData->setTotal($total); return $gridData; }
/** * DELETE request should remove record */ public function testDelete() { $this->dispatchRouter('/test/crud/', ['id' => 3], Http\Request::METHOD_DELETE); $this->assertOk(); $count = Db::fetchOne('SELECT count(*) FROM `test` WHERE `email` = ?', ['*****@*****.**']); $this->assertEquals($count, 0); }