require dirname(__FILE__) . '/Nette/Debugger.php';
require dirname(__FILE__) . '/../dibi/dibi.php';
dibi::connect(array('driver' => 'sqlite3', 'database' => 'data/sample.s3db'));
// some variables
$cond1 = TRUE;
$cond2 = FALSE;
$foo = -1;
$bar = 2;
// conditional variable
$name = $cond1 ? 'K%' : NULL;
// if & end
dibi::test('
SELECT *
FROM customers
%if', isset($name), 'WHERE name LIKE ?', $name, '%end');
// -> SELECT * FROM customers WHERE name LIKE 'K%'
// if & else & (optional) end
dibi::test("\r\n\tSELECT *\r\n\tFROM people\r\n\tWHERE id > 0\r\n\t\t%if", $foo > 0, "AND foo=?", $foo, "\r\n\t\t%else %if", $bar > 0, "AND bar=?", $bar, "\r\n");
// -> SELECT * FROM people WHERE id > 0 AND bar=2
// nested condition
dibi::test('
SELECT *
FROM customers
WHERE
%if', isset($name), 'name LIKE ?', $name, '
%if', $cond2, 'AND admin=1 %end
%else 1 LIMIT 10 %end');
// -> SELECT * FROM customers WHERE LIMIT 10
// IF()
dibi::test('UPDATE products SET', array('price' => array('IF(price_fixed, price, ?)', 123)));
// -> SELECT * FROM customers WHERE LIMIT 10
pre.dibi { padding-bottom: 10px; }
</style>
<h1>dibi conditional SQL example</h1>
<pre>
<?php
require_once 'Nette/Debug.php';
require_once '../dibi/dibi.php';
dibi::connect(array('driver' => 'sqlite', 'database' => 'sample.sdb'));
$cond1 = TRUE;
$cond2 = FALSE;
$foo = -1;
$bar = 2;
$name = $cond1 ? 'K%' : NULL;
// if & end
dibi::test('
SELECT *
FROM [customers]
%if', isset($name), 'WHERE [name] LIKE %s', $name, '%end');
// -> SELECT * FROM [customers] WHERE [name] LIKE 'K%'
// if & else & (optional) end
dibi::test("\nSELECT *\nFROM [people]\nWHERE [id] > 0\n\t%if", $foo > 0, "AND [foo]=%i", $foo, "\n\t%else %if", $bar > 0, "AND [bar]=%i", $bar, "\n");
// -> SELECT * FROM [people] WHERE [id] > 0 AND [bar]=2
// nested condition
dibi::test('
SELECT *
FROM [customers]
WHERE
%if', isset($name), '[name] LIKE %s', $name, '
%if', $cond2, 'AND [admin]=1 %end
%else 1 LIMIT 10 %end');
// -> SELECT * FROM [customers] WHERE LIMIT 10
<!DOCTYPE html><link rel="stylesheet" href="data/style.css">
<h1>Using Limit & Offset | dibi</h1>
<?php
require __DIR__ . '/../dibi/dibi.php';
dibi::connect(array('driver' => 'sqlite3', 'database' => 'data/sample.s3db'));
// no limit
dibi::test('SELECT * FROM [products]');
// -> SELECT * FROM [products]
// with limit = 2
dibi::test('SELECT * FROM [products] %lmt', 2);
// -> SELECT * FROM [products] LIMIT 2
// with limit = 2, offset = 1
dibi::test('SELECT * FROM [products] %lmt %ofs', 2, 1);
// -> SELECT * FROM [products] LIMIT 2 OFFSET 1
<!DOCTYPE html><link rel="stylesheet" href="data/style.css">
<h1>Query Language Named Arguments Examples | dibi</h1>
<?php
require __DIR__ . '/../dibi/dibi.php';
date_default_timezone_set('Europe/Prague');
dibi::connect(array('driver' => 'sqlite3', 'database' => 'data/sample.s3db'));
dibi::getConnection()->getSubstitutes()->test = 'test_';
dibi::getConnection()->getSubstitutes()->{''} = 'testtoo_';
// SELECT
$name = 'K%';
$timestamp = mktime(0, 0, 0, 10, 13, 1997);
$id_list = array(1, 2, 3);
// If the argument implements IDibiArguments, it is removed from the arguments
// and added to the named argument collections.
// If the %nmd modifier is used for a positional argument, it is also added
// to the named argument collections. It should be an array or object implementing
// the ArrayAccess interface.
// Any named argument is a colon with identifier with optional percent sign and
// value modifier.
// Original substitutions are still present.
dibi::test('
SELECT COUNT(*) as [count]
%nmd
FROM [:test:customers]
WHERE [name] LIKE :name
AND [added] > :timestamp%d
OR [customer_id] IN :id_list%in
ORDER BY [name]', new ArrayObject(['timestamp' => new DibiDateTime($timestamp)]), new DibiArguments(['name' => $name, 'id_list' => $id_list]));
<h1>IDibiVariable example</h1>
<?php
require_once 'Nette/Debug.php';
require_once '../dibi/dibi.php';
date_default_timezone_set('Europe/Prague');
// CHANGE TO REAL PARAMETERS!
dibi::connect(array('driver' => 'sqlite', 'database' => 'sample.sdb', 'formatDate' => "'Y-m-d'", 'formatDateTime' => "'Y-m-d H-i-s'"));
// generate and dump SQL
dibi::test("\nINSERT INTO [mytable]", array('id' => 123, 'date' => dibi::date('12.3.2007'), 'stamp' => dibi::dateTime('23.1.2007 10:23')));
// -> INSERT INTO [mytable] ([id], [date], [stamp]) VALUES (123, '2007-03-12', '2007-01-23 10-23-00')
<h1>Using Substitutions | dibi</h1>
<?php
require_once 'Nette/Debug.php';
require_once '../dibi/dibi.php';
dibi::connect(array('driver' => 'sqlite', 'database' => 'data/sample.sdb'));
// create new substitution :blog: ==> wp_
dibi::addSubst('blog', 'wp_');
dibi::test("SELECT * FROM [:blog:items]");
// -> SELECT * FROM [wp_items]
// create new substitution :: (empty) ==> my_
dibi::addSubst('', 'my_');
dibi::test("UPDATE ::table SET [text]='Hello World'");
// -> UPDATE my_table SET [text]='Hello World'
// create substitutions using fallback callback
function substFallBack($expr)
{
$const = 'SUBST_' . strtoupper($expr);
if (defined($const)) {
return constant($const);
} else {
throw new Exception("Undefined substitution :{$expr}:");
}
}
// define callback
dibi::setSubstFallBack('substFallBack');
// define substitutes as constants
define('SUBST_ACCOUNT', 'eshop_');
define('SUBST_ACTIVE', 7);
dibi::test("\r\n\tUPDATE :account:user\r\n\tSET name='John Doe', status=:active:\r\n\tWHERE id=", 7);
// -> UPDATE eshop_user SET name='John Doe', status=7 WHERE id= 7
// SELECT
$ipMask = '192.168.%';
$timestamp = mktime(0, 0, 0, 10, 13, 1997);
dibi::test('
SELECT COUNT(*) as [count]
FROM [comments]
WHERE [ip] LIKE %s', $ipMask, '
AND [date] > ', dibi::date($timestamp));
// -> SELECT COUNT(*) as [count] FROM [comments] WHERE [ip] LIKE '192.168.%' AND [date] > 876693600
// dibi detects INSERT or REPLACE command
dibi::test('
REPLACE INTO products', array('title' => 'Super product', 'price' => 318, 'active' => TRUE));
// -> REPLACE INTO products ([title], [price], [active]) VALUES ('Super product', 318, 1)
// multiple INSERT command
$array = array('title' => 'Super Product', 'price' => 12, 'brand' => NULL, 'created' => new DateTime());
dibi::test("INSERT INTO products", $array, $array, $array);
// -> INSERT INTO products ([title], [price], [brand], [created]) VALUES ('Super Product', ...) , (...) , (...)
// dibi detects UPDATE command
dibi::test("\n\tUPDATE colors SET", array('color' => 'blue', 'order' => 12), "\n\tWHERE id=%i", 123);
// -> UPDATE colors SET [color]='blue', [order]=12 WHERE id=123
// modifier applied to array
$array = array(1, 2, 3);
dibi::test("\n\tSELECT *\n\tFROM people\n\tWHERE id IN (%i)", $array);
// -> SELECT * FROM people WHERE id IN ( 1, 2, 3 )
// modifier %by for ORDER BY
$order = array('field1' => 'asc', 'field2' => 'desc');
dibi::test("\n\tSELECT *\n\tFROM people\n\tORDER BY %by", $order, "\n");
// -> SELECT * FROM people ORDER BY [field1] ASC, [field2] DESC
// indentifiers and strings syntax mix
dibi::test('UPDATE [table] SET `item` = "5 1/4"" diskette"');
// -> UPDATE [table] SET [item] = '5 1/4" diskette'
public function save()
{
$this->created = $this->created ? new DibiDateTime($this->created) : new DibiDateTime();
$this->changed = $this->changed ? new DibiDateTime($this->changed) : NULL;
// prevede data objektu na pole
$data = get_object_vars($this);
unset($data['simulation']);
unset($data['force']);
unset($data['file_prefix']);
unset($data['file_sufix']);
if ($data['user_id'] === '0') {
unset($data['user_id']);
}
dibi::begin();
// zkontroluje, zda j*z neni ulozen stejny soubor - podle code
$res = dibi::query('SELECT * FROM [file] WHERE [code] = %s', $this->code);
if ($res->getRowCount() > 0) {
throw new DibiException("File already exists with this name. Please, try upload file with different name.", 0);
}
if ($this->id > 0 && !$this->force) {
foreach ($data as $key => $value) {
if ($value == null) {
unset($data[$key]);
}
}
if ($this->simulation) {
$res = dibi::test('UPDATE [file] SET', $data, 'WHERE [id]=%i', $this->id);
} else {
$res = dibi::query('UPDATE [file] SET', $data, 'WHERE [id]=%i', $this->id);
}
} else {
if ($this->simulation) {
$res = dibi::test('INSERT INTO file', $data);
$this->id = 999999999;
} else {
if ($this->force) {
$res = dibi::query('INSERT IGNORE INTO file', $data);
} else {
$res = dibi::query('INSERT INTO file', $data);
}
$this->id = dibi::getInsertId();
}
}
dibi::commit();
}
$foo = -1;
$bar = 2;
// conditional variable
$name = $cond1 ? 'K%' : NULL;
// if & end
dibi::test('
SELECT *
FROM customers
%if', isset($name), 'WHERE name LIKE ?', $name, '%end');
// -> SELECT * FROM customers WHERE name LIKE 'K%'
// if & else & (optional) end
dibi::test('
SELECT *
FROM people
WHERE id > 0
%if', $foo > 0, 'AND foo=?', $foo, '
%else %if', $bar > 0, 'AND bar=?', $bar, '
');
// -> SELECT * FROM people WHERE id > 0 AND bar=2
// nested condition
dibi::test('
SELECT *
FROM customers
WHERE
%if', isset($name), 'name LIKE ?', $name, '
%if', $cond2, 'AND admin=1 %end
%else 1 LIMIT 10 %end');
// -> SELECT * FROM customers WHERE LIMIT 10
// IF()
dibi::test('UPDATE products SET', ['price' => ['IF(price_fixed, price, ?)', 123]]);
// -> SELECT * FROM customers WHERE LIMIT 10
<h1>dibi prefix & substitute example</h1>
<?php
require_once 'Nette/Debug.php';
require_once '../dibi/dibi.php';
dibi::connect(array('driver' => 'sqlite', 'database' => 'sample.sdb'));
// create new substitution :blog: ==> wp_
dibi::addSubst('blog', 'wp_');
dibi::test("UPDATE :blog:items SET [text]='Hello World'");
// -> UPDATE wp_items SET [text]='Hello World'
// create new substitution :: (empty) ==> my_
dibi::addSubst('', 'my_');
dibi::test("UPDATE [database.::table] SET [text]='Hello World'");
// -> UPDATE [database].[my_table] SET [text]='Hello World'
// create substitution fallback
function substFallBack($expr)
{
if (defined($expr)) {
return constant($expr);
} else {
return 'the_' . $expr;
}
}
dibi::setSubstFallBack('substFallBack');
dibi::test("UPDATE [:account:user] SET [name]='John Doe', [active]=:true:");
// -> UPDATE [the_accountuser] SET [name]='John Doe', [active]=1
<h1>Query Language & Conditions | dibi</h1>
<?php
require_once 'Nette/Debug.php';
require_once '../dibi/dibi.php';
dibi::connect(array('driver' => 'sqlite', 'database' => 'data/sample.sdb'));
// some variables
$cond1 = TRUE;
$cond2 = FALSE;
$foo = -1;
$bar = 2;
// conditional variable
$name = $cond1 ? 'K%' : NULL;
// if & end
dibi::test('
SELECT *
FROM customers
%if', isset($name), 'WHERE name LIKE %s', $name, '%end');
// -> SELECT * FROM customers WHERE name LIKE 'K%'
// if & else & (optional) end
dibi::test("\n\tSELECT *\n\tFROM people\n\tWHERE id > 0\n\t\t%if", $foo > 0, "AND foo=%i", $foo, "\n\t\t%else %if", $bar > 0, "AND bar=%i", $bar, "\n");
// -> SELECT * FROM people WHERE id > 0 AND bar=2
// nested condition
dibi::test('
SELECT *
FROM customers
WHERE
%if', isset($name), 'name LIKE %s', $name, '
%if', $cond2, 'AND admin=1 %end
%else 1 LIMIT 10 %end');
// -> SELECT * FROM customers WHERE LIMIT 10
<!DOCTYPE html><link rel="stylesheet" href="data/style.css">
<h1>Using DateTime | dibi</h1>
<?php
require __DIR__ . '/../src/loader.php';
date_default_timezone_set('Europe/Prague');
// CHANGE TO REAL PARAMETERS!
dibi::connect(['driver' => 'sqlite3', 'database' => 'data/sample.s3db', 'formatDate' => "'Y-m-d'", 'formatDateTime' => "'Y-m-d H-i-s'"]);
// generate and dump SQL
dibi::test('
INSERT INTO [mytable]', ['id' => 123, 'date' => new DateTime('12.3.2007'), 'stamp' => new DateTime('23.1.2007 10:23')]);
// -> INSERT INTO [mytable] ([id], [date], [stamp]) VALUES (123, '2007-03-12', '2007-01-23 10-23-00')
/**
* determine signatures with the same context
* currently we only check if the class we detected matches the signature
*
* @param array $candidates Array of DibiRows
* @return array
*/
protected function _filterByContext($candidates)
{
if (false === array_key_exists('class', $this->_context) && 0 < strlen($this->_context['class']) && Method::TYPE_MIXED !== $this->_context['class'] && 0 < count($candidates)) {
$classIds = array();
$query = 'SELECT name, id FROM [classes] WHERE id IN (%s) AND name=%s';
foreach ($candidates as $key => $candidate) {
if ($candidate->class_id) {
$classIds[$key] = $candidate->class_id;
}
}
try {
$result = dibi::fetchPairs($query, $classIds, $this->_context['class']);
} catch (\DibiDriverException $e) {
dibi::test($query, $classIds, $this->_context['class']);
throw $e;
}
$contextMatchingCandidates = $candidates;
foreach ($candidates as $key => $candidate) {
if (false == in_array($candidate->class_id, $classIds)) {
unset($contextMatchingCandidates[$key]);
}
}
return count($contextMatchingCandidates) ? $contextMatchingCandidates : $candidates;
}
return $candidates;
}
require_once '../dibi/dibi.php';
date_default_timezone_set('Europe/Prague');
dibi::connect(array('driver' => 'sqlite', 'database' => 'sample.sdb'));
// dibi detects INSERT or REPLACE command
dibi::test('
REPLACE INTO [products]', array('title' => 'Super product', 'price' => 318, 'active' => TRUE));
// -> REPLACE INTO [products] ([title], [price], [active]) VALUES ('Super product', 318, 1)
// multiple INSERT command
$array = array('title' => 'Super Product', 'price' => 12, 'brand' => NULL, 'created' => dibi::datetime());
dibi::test("INSERT INTO [products]", $array, $array, $array);
// -> INSERT INTO [products] ([title], [price], [brand], [created]) VALUES ('Super Product', ...) , (...) , (...)
// dibi detects UPDATE command
dibi::test("\n\tUPDATE [colors] SET", array('color' => 'blue', 'order' => 12), "\n\tWHERE [id]=%i", 123);
// -> UPDATE [colors] SET [color]='blue', [order]=12 WHERE [id]=123
// SELECT
$ipMask = '192.168.%';
$timestamp = mktime(0, 0, 0, 10, 13, 1997);
dibi::test('
SELECT COUNT(*) as [count]
FROM [comments]
WHERE [ip] LIKE %s', $ipMask, '
AND [date] > ', dibi::date($timestamp));
// -> SELECT COUNT(*) as [count] FROM [comments] WHERE [ip] LIKE '192.168.%' AND [date] > 876693600
// IN array
$array = array(1, 2, 3);
dibi::test("\n\tSELECT *\n\tFROM [people]\n\tWHERE [id] IN (", $array, ")\n");
// -> SELECT * FROM [people] WHERE [id] IN ( 1, 2, 3 )
// ORDER BY array
$order = array('field1' => 'asc', 'field2' => 'desc');
dibi::test("\nSELECT *\nFROM [people]\nORDER BY %by", $order, "\n");
// -> SELECT * FROM [people] ORDER BY [field1] ASC, [field2] DESC
