Main page Log in

Rabbit/Db

From Dionyziz

The Rabbit Database layer (RabbitDb) provides an Object-Oriented approach to database mapper. Like PDO, it supports many database systems, including MySQL. For MySQL, only the mysql extension is required (not mysqli or PDO). It can also be easily extended to support any database system that is not natively supported. It offers many features commons to database layers and some less common.

Contents

[edit] Security

Rabbit offers a type-safe system to validate user input, which automatically solves one of the two most important SQL Injection problems: that of unexpected input type provided by the user (e.g. the user gave a string instead of an integer). In addition to that, the Rabbit database system provides a query preparation system. Unlike other prepared queries system, it relies on strongly typed arguments, assuming that user input has been filtered using the type-safe system and that the programmer is type-aware (i.e. that you use integers and not strings when you want integers and not strings).

Rabbit forces magic_quotes_gpc off. Prepared queries ensure that the appropriate escaping is made. No further code is required to be written by the end programmer to strip or add slashes in any way.

[edit] Database Drivers

Most web applications use databases for data storage. The RabbitDb system is designed to assist in that to allow the programmer to write the minimum amount of code required for database access. Most small web applications use a single database. Larger projects might use multiple databases. Every database server is of a particular type. Common database server types include:

  • MySQL
  • Firebird
  • MSSQL

Rabbit communicates with each server type using a Rabbit Database Driver. You can define your own drivers for uncommon or unsupported database types. Each driver is identified by a string identifier. For example, for mysql, the string identifier is "mysql".

[edit] Defining database settings

Database settings are defined in your settings file. Use the "databases" directive to define your databases. Its value should be an array, each item of which refers to a database. If you have no databases, you must still define it and set it to the empty array.

Each database should consist of a key and a value pair. The key defines an alias for the database, a friendly name that you will be able to use throughout your code to refer to that database. For clarity it is preferred to make it begin with "db", for example "dbmain". If you only have one database, it is common to name it simply "db". The value defines the details about the database. Database aliases must not conflict with other global names and must be valid PHP variable names.

The details about each database are defined as a dictionary specifying several settings relevant to your database. The following settings are available:

  • name: Required. A string specifying your database name, as it is available on your database server. For example, "mydb".
  • driver: Required. A string specifying the database driver to use. For example, "mysql".
  • hostname: Required. A string specifying which host or IP address to connect to. For example, "localhost".
  • username: Required. A string used as the username for authentication with the database. For example, "mydbuser".
  • password: Required. A string used as the password for authentication with the database. For example, "secret28code".
  • charset: Required. Which character set to use with the database. Use "DEFAULT" to use the default charset.
  • prefix: Optional. If your database table names are prefixed with a particular string, specify it here.
  • tables: Optional. An array with database tables available on the database. It is strongly recommended that you specify these. (You must specify them explicitly later in your code manually if you don't do it here). This array consists of key/value pairs, database table aliases and actual names. This is useful if you want to change a table name but don't want to modify the table name throughout your codebase. Usually, they are the same. If you don't want to specify different aliases, you can use a simple array of table names.

[edit] Database objects

Databases are automatically made available as variables named with the aliases specified in your settings. For simple applications, it might not necessary to directly access these database objects. For example, some database objects might be named: $db, $dbbackup, $dbsecondary if your aliases are "db", "dbbackup" and "dbsecondary" respectively.

These database objects can be used by globalizing them within functions: 

<?php function Sample() {
    global $db;
    
    // do something with $db
}
?>

[edit] Preparing Queries

Queries must be prepared before they can be used. Prepared queries help in enforcing security, avoiding syntax errors, and making your code shorter. To prepare a query, use the Prepare method on a database object, passing the SQL query as a string: 

<?php
    $query = $db->Prepare( 'SELECT * FROM :users LIMIT 10' );
?>

[edit] Binding Tables

You will notice that table names are not directly specified. Instead, the table alias is specified, preceded by a colon. Notice that the table alias is not enclosed within `backticks`. Table aliases are used instead of actual table names. This allows the database layer to be table-aware, which is important for several reasons:

  • For scalability reasons, if you're planning to do horizontal partitioning in the future.
  • For optimization reasons, as it will allow you to quickly see which queries use which tables, benchmark, and optimize accordingly.
  • To enable the ability to use hooks, i.e. PHP-based triggers.
  • To enable the ability to use aliases, allowing you to change your table names without modifying your source code.

After you have prepared a query, you need to bind the relevant table to it. This lets the database system know which tables you're going to use. To bind a table, use the BindTable() method on your query object, passing the table alias as a parameter: 

<?php
    $query->BindTable( 'users' );
?>

You cannot operate on tables without binding them. Although you can, you should not use unbound direct table names, as it hurts maintainability. Notice that only tables are bound; fields are specified normally, and can be enclosed in backticks for clarity if you desire.

[edit] Binding Values

Values, much like tables, are also not directly specified within your queries, but should be bound as well. Instead of your actual value, use a binding placeholder within your query. This placeholder can then be used to indicate what value you want to bind at which position. Placeholders are named and are preceded with a colon. Named bound arguments instead of position-based bound arguments are used for clarity. Care must be taken to avoid conflicts between your placeholder names and table aliases. Notice that placeholders are specified directly within your query. No quotes must be used around placeholders, even if they are placeholders for string values.

No values should be used directly. All values should be bound to placeholders, to avoid syntax errors, to increase code clarity, and to maintain security.

Values can be bound to placeholders using the Bind() method. The method accepts the name of the placeholder as its first parameter, and the value as its second: 

<?php
    $query = $db->Prepare(
         'SELECT * FROM :users WHERE `userid` = :userid'
    );
    $query->BindTable( 'users' );
    $query->Bind( 'userid', 5 );
?>

The end-result query sent to the database server would look somewhat similar to this: 

SELECT * FROM `users` WHERE `userid` = 5;

[edit] Executing Queries

After binding your tables and values, you can execute the query using the Execute() method on your query object: 

<?php
    $query->Execute();
?>

The Execute method returns either a Rabbit Database Resource object (hereforth referred-to as a resource object), or a Rabbit Database Change object (hereforth referred-to as a change object), depending on the type of query you are executing. Queries that can return a resultset cause a resource object to be returned, while all other queries cause a change object to be returned.

For instance, the following queries will all cause a resource object to be returned:

  • SELECT queries
  • SHOW queries
  • DESCRIBE queries
  • EXPLAIN queries

On the other hand, the following queries will all cause a change object to be returned:

  • UPDATE queries
  • DELETE queries
  • INSERT INTO queries
  • ALTER queries
  • CREATE queries
  • USE queries
  • DROP queries

The return value can then be used to gather information about the query execution, such as the resultset produced.

[edit] Processing Resultsets

Resultsets from read queries are accessible through resource objects.

[edit] Testing against the empty set

It is possible to test if the resultset is nonempty by using the ->Results() function. It will return true if the resultset is nonempty, or false if it is empty: 

<?php
function Sample() {
    global $db;
 
    $query = $db->Prepare(
        'SELECT
            `val1`, `val2`
        FROM
            :mytable
        WHERE
            `condition` = :value'
    );
    $query->BindTable( 'mytable' );
    $query->Bind( 'value', 1 );
    $res = $query->Execute();
    if ( $res->Results() ) {
        ?>There were some results!<?php
        return;
    }
    ?>No results found!<?php
}
?>

[edit] Determining the number of rows

To determine the number of rows in a (empty or nonempty) resultset, use the ->NumRows() function. It will return a nonnegative integer with the number of rows in the resultset: 

<?php
function Sample() {
    global $db;
 
    $query = $db->Prepare(
        'SELECT
            `val1`, `val2`
        FROM
            :mytable
        WHERE
            `condition` = :value'
    );
    $query->BindTable( 'mytable' );
    $query->Bind( 'value', 1 );
    $res = $query->Execute();
    if ( $res->Results() ) {
        echo $res->NumRows();
        ?> row<?php
        if ( $res->NumRows() != 1 ) {
            ?>s<?php
        }
        ?> found!<?php
        return;
    }
    ?>No results found!<?php
}
?>

[edit] Determining the number of fields

To determine the number of fields in a resultset, use the ->NumFields() function. It will return a nonnegative integer with the number of fields in the resultset.

[edit] Grabbing the resultset

There are two ways to fetch the data inside a resultset; FetchArray and MakeArray.

[edit] Using FetchArray()

->FetchArray(), which works similar to mysql_fetch_array, allows fetching one row at a time. It returns an associative array in which the key of each item is the field name and the value of each item is the row value. It will return the first row of the resultset the first time it is called and the next row every next time. If no more rows are left, it will return boolean false: 

<?php
function Sample() {
    global $db;
 
    $query = $db->Prepare(
        'SELECT
            `val1`, `val2`
        FROM
            :mytable
        WHERE
            `condition` = :value'
    );
    $query->BindTable( 'mytable' );
    $query->Bind( 'value', 1 );
    $res = $query->Execute();
    if ( $res->Results() ) {
        ?><table><?php
        while ( $row = $res->FetchArray() ) {
            ?><tr><?php
            foreach ( $row as $fieldvalue ) {
                ?><td><?php
                echo $fieldvalue;
                ?></td><?php
            }
            ?></tr><?php
        }
        ?></table><?php
        return;
    }
    ?>No results found!<?php
}
?>

[edit] Using MakeArray()

->MakeArray() allows fetching all the rows in a resultset at once. It constructs a 2-dimensional array in which every row represents a resultset row and each column represents a resultset column. The keys of the columns allow fetching the field names. The code below produces identical results as the code just above: 

<?php
function Sample() {
    global $db;
 
    $query = $db->Prepare(
        'SELECT
            `val1`, `val2`
        FROM
            :mytable
        WHERE
            `condition` = :value'
    );
    $query->BindTable( 'mytable' );
    $query->Bind( 'value', 1 );
    $res = $query->Execute();
    if ( $res->Results() ) {
        $set = $res->MakeArray();
        ?><table><?php
        foreach ( $set as $row ) {
            ?><tr><?php
            foreach ( $row as $fieldvalue ) {
                ?><td><?php
                echo $fieldvalue;
                ?></td><?php
            }
            ?></tr><?php
        }
        ?></table><?php
        return;
    }
    ?>No results found!<?php
}
?>

[edit] Processing Changes

The changes made by a write query can be examined using change objects.

[edit] Checking for impact

To test if a write query affected the database, you can use the ->Impact() function. This will return true if the query had an impact on the database, or false else: 

<?php
function Sample() {
    global $db;
 
    $query = $db->Prepare(
        'UPDATE
            :mytable
        SET
            `val1` = `val1` + 1
        WHERE
            `condition` = :value
        LIMIT 1;'
    );
    $query->BindTable( 'mytable' );
    $query->Bind( 'value', 1 );
    $change = $query->Execute();
    if ( $change->Impact() ) {
        ?>Table entry modified successfully.<?php
        return;
    }
    ?>The requested table entry was not found!<?php
}
?>

[edit] Determining the number of affected rows

The ->AffectedRows() function of the change object allows getting the number of rows affected by a write query: 

<?php
function Sample() {
    global $db;
 
    $query = $db->Prepare(
        'UPDATE
            :mytable
        SET
            `val1` = `val1` + 1
        WHERE
            `condition` = :value
        LIMIT 1;'
    );
    $query->BindTable( 'mytable' );
    $query->Bind( 'value', 1 );
    $change = $query->Execute();
    if ( $change->Impact() ) {
        echo $change->AffectedRows();
        if ( $change->AffectedRows() == 1 ) { 
            ?> entry<?php
        }
        else {
            ?> entries<?php
        }
        ?> were modified successfully.<?php
        return;
    }
    ?>No entries that match your criteria were found!<?php
}
?>

[edit] Determining the Insert Id

For single INSERT INTO queries that are executed on tables with an auto-increment field, it is possible to determine the new auto-increment value of the insert row using the ->InsertId() function on the change object: 

<?php
function Sample() {
    global $db;
 
    $query = $db->Prepare(
        'INSERT INTO
            :mytable
        (`val1`, `val2`) VALUES
        (:val1 , :val2);'
    );
    $query->BindTable( 'mytable' );
    $query->Bind( 'val1', 1 );
    $query->Bind( 'val2', 'hello' );
    $change = $query->Execute();
    if ( $change->Impact() ) {
        ?>A new row was inserted and auto-increment id <?php
        echo $change->InsertId();
        ?> was associated with it!<?php
        return;
    }
    ?>The row could not be inserted!<?php
}
?>

[edit] Accessing database tables

Database tables can be accessed through a number of ways, to allow fetching meta-information about tables, or to operate on them in a number of ways. Tables are accessed using a DBTable object.

[edit] Fetching a table object by a table alias

You can fetch a DBTable object for a particular table if you know its alias. Simply execute the method ->TableByAlias() on your database object, passing the table alias as a string parameter: 

<?php
    $table = $db->TableByAlias( 'users' );
    // operate on $table
?>

[edit] Fetching all tables of a particular database

You can fetch DBTable objects for all the tables in a particular database using the ->Tables() method on your database object. It will return an array of DBTable objects: 

<?php
    $tables = $db->Tables();
    foreach ( $tables as $table ) {
        // operate on $table
    }
?>

[edit] Using smart inserts

In case you want to perform a simple insert query with nothing extremely sophisticated, the Rabbit Database layer offers an easier method to perform it. This can be done using the ->Insert() function on the table object. It accepts one parameter: the data to insert. You can insert one row or more rows using it. To insert one row, simply provide an array similar to the one returned by ->FetchArray() as the data to be inserted. To insert multiple rows, simply provide an array similar to the one returned by ->MakeArray() as the data to be inserted. The values in your arrays can be either strings or integers, and should not be escaped. The function returns a change object in case of one row insertion, or an array of change objects in case of multiple row insertions. 

<?php
function Sample() {
    global $db;
 
    $table = $db->TableByAlias( 'mytable' );
    // insert one entry
    $change = $mytable->Insert( array(
        'username' => 'dionyziz',
        'password' => '1234',
        'realname' => 'Dionysis Zindros',
        'age'      => 19
    ) );
 
    // insert multiple entries
    $changes = $mytable->Insert( array( 
        array(
            'username' => 'dionyziz',
            'password' => '1234',
            'realname' => 'Dionysis Zindros',
            'age'      => 19
        ),
        array(
            'username' => 'abresas',
            'password' => 'sheep8',
            'realname' => 'Aleksis Bresas',
            'age'      => 16
        ),
        array(
            'username' => 'izual',
            'password' => 'underage',
            'realname' => 'Chris Pappas',
            'age'      => 18
        )
    ) );
}
?>

[edit] TODO

If you feel like expanding this documentation article, these need explanation:

  • Accessing table names
  • Altering tables
  • Writing unit tests that modify the database temporarily
  • Accessing and modifying column information
  • Accessing and modifying index information
  • Truncating tables
  • Argue for and against the rabbit database layer
    • Describe how it is associated with water and profiling
    • Argue about speed
    • Talk about ease-of-use
    • Talk about the Object-oriented approach and how it helps with insert ids and affected rows.
  • How to smart insert delayed.
  • How to smart insert ignore.
  • How to set smart insert quotas.
  • How to write your own database drivers.
Retrieved from "http://www.dionyziz.com/Rabbit/Db"
©2010 dionyziz
Attribution-Share Alike 3.0