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);
}
