Transactions handling in LIZ DB

In today's article I will present transaction handling in LIZ DB.

There are two ways developers may handle their transactions:

• automatic - managed by LIZ DB
• programmatic - developers must commit, rollback all transactions themselves
  

1. LIZ DB managed transactions

LIZ DB supports two automatic behaviours, those can be turn on/off by passing true or false values to one of following methods:

• public function SetAutoCommit($commit = true) - data will be committed after each INSERT, UPDATE or DELETE statement
• public function SetCommitOnClose($commit = true) - all data will be committed at the end of current session
  

Default MySQLimp settings are:

private $CommitOnClose = true;
private $AutoCommit = false;

INFO
These two behaviours are mutually explicit! If both set to true, auto commit will work.
INFO
MySQL supports transactions only when InnoDB engine is being used!
MyISAM or any other engines do not support transactions.

Examples of usage.

Example 1. LIZ DB managed transactions.

$db->SetAutoCommit(); // default value true will be used
$db->SetAutoCommit(true); // same as above
$db->SetAutoCommit(false); // no auto commits after DML statements
// if auto commit is set to false, below code will work
// all data will be committed on connection close
$db->SetCommitOnClose(true); // default value true will be used
$db->SetCommitOnClose(); // same as above
// now, data won't be committed on connection close
$db->SetCommitOnClose(false) 

2. User managed transactions

LIZ DB offers following methods for managing transactions programmatic way:

• public function StartTransaction() - starts transaction
  
• public function Commit() - commits all DML statements executed in current transaction
  
• public function AbortTransaction() - rollbacks all changes made to database in current transaction
  
• public function SetSavePoint($name) - sets a new logical savepoint within current transaction
  
• public function RollbackToSavePoint($name) - rollbacks all changes made to database to given logical savepoint
  

Examples of usage.

Example 2. Basic transaction handling.

$db = new MySQLimp($config);

// start the transaction
$db->StartTransaction();

$chars = "some text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$newId = $db->RunQuery();

echo 'new record id: ' . $newId . "\n";

// rollback current transaction
// all changes will be rejected
$db->Rollback();

// explicitly begin new transaction
$db->StartTransaction();

$chars = "some other text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$newId = $db->RunQuery();

echo 'second new record id: ' . $newId . "\n";

// after executing Commit method, all changes
// made within current transaction will be
// visible to other users
$db->Commit();

Example 3. Transaction savepoints handling.

$db = new MySQLimp($config);

// explicitly start new transaction
$db->StartTransaction();

// will store IDs of newly inserted records
$ids = array();

$chars = "some text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$ids[] = $db->RunQuery();

// dirty record
echo 'first new record id: ' . $ids[0] . "\n";

$chars = "some other text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

// everything that will be executed from now on
// can be rollbacked to this point
$db->SetSavePoint('beforeInsertingSecondRecord');

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$ids[] = $db->RunQuery();

// second dirty record
echo 'second new record id: ' . $ids[1] . "\n";

// rollback to 'beforeInsertingSecondRecord' savepoint
// second record will be rejected
$db->RollbackToSavePoint('beforeInsertingSecondRecord');

// record was rejected, this will return empty set
$db->Query = "SELECT `id` FROM `lizdb_test_table` WHERE `id` = '{$ids[1]}'";
$second = $db->SelectOne();

echo 'Is rec no ' . $ids[1] .  ' in db now? ' . !empty($second) . '';

// the first one is still dirty (either not committed or not rollbacked)
// abort transaction and rollback all uncommitted data
$db->AbortTransaction();

// verify if both records do not exist in database
$ids = implode(',', $ids);
$db->Query = "SELECT * FROM `lizdb_test_table` WHERE `id` IN ({$ids})";
$added  = $db->Select();

// both records are 'wiped out' without any trace, count($added) == 0
echo 'No of added records: ' . count($added) . '';

3. Live examples

Live examples are available at LIZ DB - Transactions API Examples

INFO
Currently live examples server does not support MySQL InnoDB storage and transactions.

Executing queries

Today I will show how to execute queries in LIZ DB.

1. LIZ DB RunQuery API

All non-SELECT statements are executed by calling only one method. The method is:

• public function RunQuery() - executes any valid DDL (CREATE, ALTER, DROP) or DML (INSERT, UPDATE, DELETE) statement
  

INFO
Before invoking this functions, public field Query should be set to valid SQL statement otherwise LIZ DB will throw LIZDBException.
LIZDBException exception will be also thrown if Query will contain SELECT statement.

The returned value differs according to the type of executed statement.
Juxtaposition of types of statements and returned values is as follows:

• INSERT - returns ID of newly created record (if table has an auto increment primary key, otherwise returns null)
• UPDATE/DELETE - returns number of updated/deleted records
• CREATE, ALTER, DROP, GRANT, REVOKE - returns true or false whether the statement was successfully executed

Examples of usage.

Example 1. Inserting new records.

$ids = array();

$chars = "some text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$ids[] = $db->RunQuery();

echo 'First new record id: ' . $ids[0] . "\n";

$chars = "some other text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$ids[] = $db->RunQuery();

echo 'Second new record id: ' . $ids[1] . "\n";

Example 2. Updating and deleting records.

$db->Query = "UPDATE `lizdb_test_table` SET `double_column` = 0.0 WHERE `id` = '{$ids[0]}'";
$updatedRows = $db->RunQuery();

echo 'No of updated rows: ' . $updatedRows . "\n";

$ids = implode(',', $ids);

$db->Query = "DELETE FROM `lizdb_test_table` WHERE `id` IN ({$ids})";
$deletedRows = $db->RunQuery();

echo 'No of deleted rows: ' . $deletedRows . "\n";

Example 3. Executing DDL statements.

$db->Query = "CREATE TABLE `t` (`f` int)";

if ($db->RunQuery()) {
    echo "New table successfully created\n";
}

$db->Query = "DROP TABLE IF EXISTS `t`";

if ($db->RunQuery()) {
    echo "table deleted successfully\n";
}

2. Live examples

Live examples are available at LIZ DB - RunQuery API Examples

Fetching data from database

In today's post I will present basic ways of fetching data from database in LIZ DB.

1. LIZ DB SELECT API

For fetching data from database there are three different functions, these are:

• public function SelectOne() - fetches a record, returns an associative array that represents given record
• public function Select() - fetches a set of records, returns an array of associative arrays (records)
• public function SelectColumn() - fetches a set of given column's values, returns an array of column's values
  

INFO
Before invoking any of the Select functions, public field Query should be set to valid SELECT statement otherwise LIZ DB will throw LIZDBException.
LIZDBException exception will be also thrown if Query will contain other statements than SELECT (like INSERT, CREATE, DROP, etc.).

Examples of usage.

Example 1. Fetching single records.

$db->Query = 'SELECT * FROM `lizdb_test_table` ORDER BY id DESC LIMIT 1';
$record = $db->SelectOne();

// $record is an associative array
echo 'Last record is: ';
echo $record['id'] . ', ' . $record['char_column'] . ', ' . $record['int_column'] . ', ' .$record['double_column'];

Example 2. Fetching sets of records.

$db->Query = 'SELECT * FROM `lizdb_test_table`';
$records = $db->Select();

// $records is an array of associative arrays
foreach($records as $r) {
echo $r['id'] . ', ' . $r['char_column'] . ', ' . $r['int_column'] . ', ' .$r['double_column'];
}

Example 3. Fetching sets of given column's values.

$db->Query = 'SELECT `double_column` FROM `lizdb_test_table` WHERE `double_column` < negativedoubles =" $db-">SelectColumn();

// $negativeDoubles is an ordinary array
echo 'negative doubles are:' . implode(',', $negativeDoubles);

2. Live examples

Live examples are available at LIZ DB - SELECT API Examples

First steps with LIZ DB

This article introduces LIZ DB basics.

LIZ DB is very easy to install. All You have to do is simply download the newest version and unzip downloaded archive to Your working directory.

1. Defining LIZDB_PATH constant

First, in order to start working with LIZ DB we need to define LIZDB_PATH that points to the directory in which we have installed (unzipped) our archive.

Example 1. Defining LIZDB_PATH.

// LIZDB_PATH must point to directory where LIZ DB is installed
define('LIZDB_PATH',  '/home/xh/public_html/lizdb_test/LIZDB');

LIZDB_PATH can be also set dynamically, the below code will work
assuming LIZ DB was installed into current directory.

Example 2. Automatic defining LIZDB_PATH.

if (false == is_dir(LIZDB_PATH)) {
define('LIZDB_PATH',  getcwd() . '/LIZDB');
}

Now we're ready to create DB objects and connect to our database.

2. Creating DB object

Currently LIZ DB 2.0 provides implementation only for MySQL5.
We need to include class definition and create its instance.

There is MySQLimp class (imp stands for improved, not implementation) in
LIZDB_PATH . '/classes/MySQLimp.inc.php' file.

MySQLimp's constructor expects associative array that must define following:

• server - name or IP of MySQL server
• user - username for authorisation
• passwd - password for authorisation
• database - database to connect to

All MySQLimp code should be surrounded by try/catch blocks.
There are two types of exceptions that can be thrown during script's execution:

• Exception - all generic PHP5 exceptions
  
• LIZDBException - thrown explicitly by LIZ DB, these exceptions are strictly linked with database errors
  

Example 3. Creating DB objects.

require LIZDB_PATH . '/classes/MySQLimp.inc.php';
// change Your settings!
// or run sql/create_user.sql to create lizdb user
$config = array();
$config['server'] = 'localhost';
$config['user'] = 'lizdb';
$config['passwd'] = 'lizdb';
$config['database'] = 'test';

try {
    $db = new MySQLimp($config);

    $db->CloseConnection();
} catch (LIZDBException $e) {
    echo $e->getMessage() . "\n";
    echo $e->GetQuery();
} catch (Exception $e) {
    echo $e->getMessage();
}

3. Basic fields and methods

There is only one public field in MySQLimp class:

• public $Query - this field stores SQL statement to be executed (any valid MySQL statement!)

There are few methods that execute queries and handle their results, these are:

• public Select() - returns array of records
• public SelectOne() - returns single record
• public SelectColumn() - return array of given column's values
• public RunQuery() - runs INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, and more...
  

Usage of these methods will be covered by upcoming articles.

Example 4. The very basic usage of SelectOne and RunQuery methods.

$db->Query = 'SELECT * FROM `lizdb_test_table` ORDER BY id DESC LIMIT 1';
$record = $db->SelectOne();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$newId = $db->RunQuery();

Stay tuned for more examples!

Contribute to the LIZ DB project!

LIZ DB is a non-commercial open-source project, developed under the PHP License that can be used and modified freely by everyone.

LIZ DB currently provides implementation of its interfaces only for MySQL5 database.

I encourage every developer that finds LIZ DB database connectivity approach attractive to write implementations for other vendors like PostgreSQL, Oracle, IBM DB2, and other.

I'm looking forward to hear from You!

Project's summary and features!

LIZ DB is highly advanced set of interfaces of high abstraction level that allows programmer to create easily rich database applications (including stored procedures, sophisticated transactions, and more) in PHP5.

LIZ DB comes with a full MySQL5 implementation of all interfaces, and can be extended to support any database.

Built-in MySQL implementation of LIZ DB allows programmer to:

• fetch data in various ways: single record, set of records, set of column's values
• prepare and execute statements in Java-like way
• prepare and call stored procedures and functions with IN, INOUT, OUT parameters support!
• manage sophisticated transactions, supports two LIZ DB managed modes: autocommit and commit on close. Also, there are means to manage transactions by developers, those are: commits, rollbacks, savepoints, and rollbacks to savepoints.

Important links:

• LIZ DB Project's Summary
• LIZ DB Downloads
• LIZ DB Live Examples!