(this page is translated by Google; We're working hard on a human translation)

Quick Start

Objectives layer dibi:

  • up to facilitate the work of programmers. How?
    • simplify the registration of SQL, it only goes
    • easy access to the methods, even without global variables
    • function for some routine tasks
  • eliminate the occurrence of errors. How?
    • clear write SQL
  • portability between database systems
    • automatic support for the convention (escaping / slashování, uvozování identifiers)
    • automatic formatting spec. types, such as date, string
    • Unification of Fundamental FCI (connection to db, command execution, the results obtained)
  • and especially KISS (Keep It Simple, Stupid)
    • maintain maximum simplicity
    • a rather ingenious than 10,000 lines of code stupid

Connecting to database

Each connection is represented by objects DibiConnection. It communicates with the database via the driver (class implements IDibiDriver). Choose which driver to use when creating the object: 

 $options = array ( 
 'driver'   => 'mysql' , 
 'host'     => 'localhost' , 
 'username' => 'root' , 
 'password' => '***' , 
 'database' => 'table' , 
 ); 
 
 // v případě chyby vyhodí DibiException 
 $connection = new DibiConnection( $options ); 
 $connection ->query( 'TRUNCATE `table`' );

But this way you can forget about :-) There is a static register dibi . He has the task of maintaining a globally accessible repository in connection object and call upon him the necessary features: 

 dibi::connect( array ( 
 'driver'   => 'mysql' , 
 'host'     => 'localhost' , 
 'username' => 'root' , 
 'password' => '***' , 
 'database' => 'test' , 
 'charset'  => 'utf8' , 
 ));

Static class dibi has a tremendous advantage - it is everywhere at hand. You do not get an instance of the connection, simply write dibi:: and you're done.

Note: The DSN connection style, where the description is stored in a chain-like URI, has in practice proved to be impractical. Prefer to use a field, it is also acceptable chain, in a standardized format of the HTTP query.

SQL commands - this is the bomb!

I admit that the way to write SQL statements, I searched madly for a long time. I finally found a technique that is extremely simple, intuitive and fairly addictive: 

 dibi::query( 'SELECT * FROM [table] WHERE [id] = %i' , $id ); 
 
 $arr = array ( 
 'pole' => 'hodnota' , 
 'bit'  => TRUE , 
 ); 
 dibi::query( 'INSERT INTO [table]' , $arr ); 
 
 dibi::query( 'UPDATE `table` SET ' , $arr , 'WHERE `id`=%i' , $x );

As you can see, the SQL statement is recorded as a series of parameters and variables we will insert the modifier (eg %i ). If it fails to automatically determine the type (of course not possible to determine types such as dates, etc).

Variable formats for the resulting SQL according to the rules of active databases. So will be TRUE in SQL as -1, as elsewhere, 1 '. Likewise formatted strings, dates, etc.

Modifiers are as follows:

% S string
% Sn Strings,''but is translated as NULL
% Bin binary data
% B boolean
% I% u integer
% IN integer, but 0 is translated as NULL
% F float
% D date (expected string or integer)
% T Date & Time (a string or integer)
% N identifier (ie a table or column name)
Sql% SQL - the string will remain unchanged
LMT% Special - determines the limit
% Ofs Special - defines offset
% Ex Special - expanding field

If a modifier followed by NULL, inserted into a database NULL. If you followed the field, so the modifier is applied to all its constituents. These are then placed in a separate SQL čárkama.

Always use a modifier %s variable with the string. Dibi would not distinguish what is the SQL command (using the embedded SQL) as a string. In this example, the function dibi::query called with two arguments, the first is a string representing the (embedded) SQL, the second is a string representing the string. Modifier %s to distinguish: 

 $text = "I'm fine" ; 
 dibi::query( 'UPDATE `table` SET `text`=%s' , $text ); 
 // MySQL: UPDATE `table` SET `text`='I\'m fine' 
 // ODBC:  UPDATE [table] SET [text]='I''m fine'

Why use the term embedded SQL? Because apparently, and that SQL goes through processing to suit the conventions of the database. Identifiers (names of tables and columns) in square brackets surround or back quotes (this one), then you are flagging chain single or double quotes, but the output is always gets what the database calls. Example 

 dibi::query( "UPDATE `table` SET [text]='I''m fine'" ); 
 
 // MySQL: UPDATE `table` SET `text`='I\'m fine' 
 // ODBC:  UPDATE [table] SET [text]='I''m fine'

Will fulfill even the quote inside the string in an embedded SQL writes duplication. Slash has a PHP string of particular importance, it would therefore apply a double, which complicates the life of ice and dibi aim is the opposite.

Formatting fields

As I was writing, the modifier can also be applied to all elements of the array, which are then separated by a comma inserted into SQL. But we also use two special modifiers %a or %v .

% And [key]=val AND [key2]="val2" AND ...
% Or [key]=val OR [key2]="val2" OR ...
% And assoc [key]=val, [key2]="val2", ...
% L% in sheet (val, "val2", ...)
% In values ([key], [key2], ...) VALUES (val, "val2", ...)
% M multivalues ([key], [key2], ...) VALUES (val, "val2", ...), (val, "val2", ...), ...
% Would Ordering [key] ASC, [key2] DESC ...
% N Identifiers [key], [key2] AS alias, ...
other - val, val2, ...

Also, we can afford the luxury of no modifier before a field not to show. Dibi In that case, apply this deduction: the case of INSERT or REPLACE command, choose% in, and otherwise% (for associative arrays).

So an example: 

 $arr = array ( 
 'a' => 'hello' , 
 'b'  => TRUE , 
 ); 
 dibi::query( 'INSERT INTO [table]' , $arr ); 
 // INSERT INTO `table` (`a`, `b`) VALUES ('hello', 1) 
 
 dibi::query( 'UPDATE `table` SET ' , $arr ); 
 // UPDATE `table` SET `a`='hello', `b`=1

More complex expressions in fields

Directly in the fields (the key) you can use modifiers: 

 dibi::query( 'UPDATE `table` SET ' , array ( 
 'number%sql' => 'RAND()' , // %sql means SQL ;) 
 )); 
 // UPDATE `table` SET 'number' = RAND()

Also, the values may be more complex expressions: 

 dibi::query( 'UPDATE `table` SET ' , array ( 
 'title' => array ( 'SHA1(%s)' , 'tajneheslo' ), 
 )); 
 // UPDATE `table` SET 'title' = SHA1('tajneheslo')

Keys can be completely omitted: 

 dibi::query( 'SELECT * FROM `table` WHERE %and' , array ( 
 array ( 'number > %i' , 10 ), 
 array ( 'number < %i' , 100 ), 
 )); 
 // SELECT * FROM `table` WHERE (number > 10) AND (number < 100)

Special type for date and time

Parameters can also be an object DateTime . 

 dibi::query( 'UPDATE `table` SET ' , array ( 
 'time' => new DateTime, 
 )); 
 // UPDATE `table` SET ('2008-01-01 01:08:10')

Successive folding query

Dibi also has support for progressive folding SQL query: 

 $query [] = 'SELECT * FROM [table]' ; 
 if ( $where ){ 
 array_push ( $query , 'WHERE [id]=%d' , $where ); 
 } 
 
 // a nyní předáme pole 
 $result = dibi::query( $query );

Or you can use the expansion of the field through a special modifier %ex .

Conditional SQL commands

Conditional SQL commands are very powerful tool. Controlled with three keywords %if , %else , and %end . The first of these %if must be, like modifier, wholly located at the end of the string representing the SQL: 

 $user = ??? 
 
 dibi::query( ' 
 SELECT * 
 FROM [table] 
 %if' , isset ( $user ), 'WHERE [user]=%s' , $user 
 );

Final %end can be omitted (or is it better to take it?).

Condition can be extended to part %else : 

 dibi::query( ' 
 SELECT * 
 FROM %if' , $cond , '[one_table] %else [second_table]' 
 );

Conditions can nest to any depth!

Prefixes & substitutions

Names of tables and columns can contain variable parts. You Define first: 

 // create new substitution :blog:  ==>  wp_ 
 dibi::addSubst( 'blog' , 'wp_' );

and then use the SQL. Note that in SQL are denoted dvojtečkama: 

 dibi::test( "UPDATE [:blog:items] SET [text]='Hello World'" ); 
 // UPDATE `wp_items` SET `text`='Hello World'

Testing query ()

In order to play a bit with dibi, there is ready to function dibi::test() that you give as parameters dibi::query() , but the place where the SQL command to the color displays on the screen.

Maybe you wonder what all this parsing and composing query costs. I wrote these functions as nejoptimálněji and the situation is such that it takes only a fraction of the time they bite the very execution of SQL statement. You can check.

Getting results

The easiest way is through a classical iteration 

 $result = dibi::query( 'SELECT * FROM table' ); 
 
 foreach ( $result as $n => $row ) { 
    print_r( $row ); 
 } 
 
 unset ( $result );

Note that the resources are released automatically upon lifting the object.

It is also possible to adjust the offset and possibly even limit 

 $result = dibi::query( 'SELECT * FROM table' ); 
 
 $offset = 10 ; 
 $limit = 3 ; 
 
 foreach ( $result ->getIterator( $offset , $limit ) 
 as $n => $row ) { 
    print_r( $row ); 
 }

We can only get the first field results 

 $value = $result ->fetchSingle();

Or a table to an indexed array: 

 $all = $result ->fetchAll();

And here we have a powerful function: 

 $assoc = $result ->fetchAssoc( 'id' );

Gets the entire table into an associative key and the box, id. 'The main strength of function is apparent when done by the association under more boxes. This may very elegantly to obtain data from queries, which combine multiple tables. Example you leave the next time .

Function is also useful for retrieving data in the form of an associative array key ⇒ value 

 $pairs = $result ->fetchPairs( 'customerID' , 'name' );

Number of lines we find the call: 

 $rows = count ( $result ); 
 
 // přesun kurzoru: 
 $result ->seek( $row );

Data types

Still it is not everything going on. When seeking records can specify the data type of each column and dibi will be converted automatically. 

 $result -> setType ( 'id' , Dibi::FIELD_INTEGER); 
 $record = $res ->fetch(); 
 
 if ( is_int ( $record [ 'id' ])) 
 echo 'yes, it is integer' ;

Or you can have all types of auto-detect: 

 $result ->detectTypes(); 
 ...

Alternatively, set the method connect() parameter 'resultDetectTypes' => TRUE and types will always be detected.

Returned record

Dibi returns records as objects DibiRow. They have the property that the values of attributes can be accessed in two ways - either the values or objects such as the associative array.

Exceptions, and error logging profiler

Any errors during the operation of the database server throw an exception or a descendant DibiException DibiDriverException. If an error occurs during execution of the SQL command, and this is passed as the exception.

A useful feature is the logging operation: 

 dibi::getProfiler()->setFile( 'log.sql' );

If you use Firefox, Firebug and FirePHP , will be running to log into the Firebug console. This is useful when debugging. Then apply a very simple profiler: 

 echo dibi:: $sql ; // poslední SQL příklaz 
 echo dibi:: $elapsedTime ; // jeho doba trvání v sec 
 echo dibi:: $numOfQueries ; // celkem SQL příkazů 
 echo dibi:: $totalTime ; // celkový čas v sec

Dibi has its own interface for connecting or logging profiler libraries. API releasing later.