test() public static method

Generates and prints SQL query - Monostate for Dibi\Connection::test().
public static test ( $args ) : boolean
return boolean
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
Esempio n. 2
0
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
Esempio n. 3
0
<!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]));
Esempio n. 5
0
<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')
Esempio n. 6
0
<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'
Esempio n. 8
0
File: File.php Progetto: soundake/pd
 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
Esempio n. 10
0
<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
Esempio n. 12
0
<!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')
Esempio n. 13
0
 /**
  * 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;
 }
Esempio n. 14
0
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