Table of Contents

• Database query preparing and fetching (DaBase_Db)
• Connect to database
• Query preparing
• Query result fetching
• Quick SQL builder based on PHP OOP magics (DaBase_Collection, DaBase_Object)
• DaBase_Collection initialization
• Association with sub-class of DaBase_Object
• Association with table name
• DaBase_Collection basic features
• Selectors
• Filters
• Orders and limits
• Update
• Delete
• DaBase_Collection appenders(pseudo-JOINS)
• Filters and orders in appenders
• Join fields names convention
• Appending by custom properties names
• Short appendings syntax
• Creating custom collections classes extended from DaBase_Collection
• Database objects definition by class models with autovalidation (DaBase_Object, Validator)
• DaBase_Object basics
• Create
• Set properties
• Get properties
• Insert
• Update
• Delete
• Data models based on DaBase_Object
• Defining object properties
• Defining custom table name
• Defining properties validation rules

Everybody who use this library, don't forget to subscribe on updates by RSS: http://code.google.com/feeds/p/dabase/updates/basic

Database query preparing and fetching (DaBase_Db)

Connect to database

$db = new DaBase_Db('someHost', 'someUser', 'userPassword', 'some_database');

Query preparing

$isActive = 1;
$sets = array('isModerator' => true, 'isRoot' => false);
$usersIds = array(432, 12314, 32182);

$result = $db->query("UPDATE `users` SET ,= WHERE `id` IN (,?) AND `isActive`=?", $sets, $usersIds, $isActive);
// equals
$result = mysql_query("UPDATE `users` SET `isModerator`='1', `isRoot`='0' WHERE `id` IN ('432', '12314', '32182') AND `isActive`='1'");

There is list of all preparing replacers:

You can always rename this replacers in:

class DaBase_Db {	
	// ...	
	const PRE_VALUE = '?';
	const PRE_VALUES = ',?';
	const PRE_NAME = '#';
	const PRE_NAMES = ',#';
	const PRE_EQUALS = ',=';
	const PRE_AS_IS = '$';

So... you can use:

$sql = $db->sql($prepareSql); // to just get prepared SQL string
$result = $db->query($prepareSql); // to prepare and exec SQL query
$result = $db->exec($sql); // to just exec SQL query without preparing filter

Query result fetching

To get result of SQL query as already fetched data, use:

$rows = $db->fetch($prepareSql); // to get result as array of all rows and columns
$row = $db->fetchRow($prepareSql); // to get associative array of first row
$column = $db->fetchColumn($prepareSql); // to get array of first column of all rows
$cell = $db->fetchCell($prepareSql); // to get value of first column of first row  

For example:

$userData = $db->fetchRow('SELECT * FROM `users` WHERE `login`=? AND `password`=?', $login, $password);
$usersCount = $db->fetchCell('SELECT COUNT(*) FROM `users`');

Quick SQL builder based on PHP OOP magics (DaBase_Collection, DaBase_Object)

DaBase_Collection provides CRUD(Create, Read, Update, Delete) interface for database table, includes supporting of appenders(like MySQL JOINS). DaBase_Object provides CRUD and validation interfaces for table row.

DaBase_Collection initialization

To access collection of some table by DaBase_Collection you should call:

$tableCollection = $db->tableCollectionName;

In this case "tableCollectionName" is not exactly real table name.

Association with sub-class of DaBase_Object

First DaBase_Collection try to find class instance of DaBase_Object for this collection Name by this pattern:

$db->users; // DaBase_Collection will look for class User
$db->usersPhotos; // UserPhoto
$db->usersPhotosComments; // UserPhotoComment

Association with table name

If this class found, so DaBase_Collection will ask for DaBase_Object::getTable() for table name of this collection. DaBase_Object first check constant DaBase_Object::table, and if it's null, it generate table name by collection name this pattern:

$db->users; // `users`
$db->usersPermissions; // `users_permissions`
$db->usersPhotosComments;  // `users_photos_comments`

If DaBase_Collection can't found existed class by collection name it uses standart DaBase_Object class, with defining table name by method described above.

DaBase_Collection basic features

Selectors

$users = $db->users->get(); // get array of all rows like objects
$users = $db->users->getByQuery('SELECT * FROM # WHERE `isActive`=?', $db->users->getTable(), $isActive); // get array of all rows like objects
$usersIds = $db->users->getColumn('login'); // get associative array of id => property 
$usersCount = $db->users->count(); // get count of all rows in table `users`
$user = $db->users->getObjectById($userId); // get one object by id
$user = $db->users($userId); // get one object by id

By default all objects in result are the objects of class DaBase_Object with public properties as table fields.

Filters

$users = $db->users->isActive(true)->isModerator(true)->posts(50, '>')->get(); // get users with `isActive`='1' AND `isModerator`='1' AND `posts` > '50'

Orders and limits

$users = $db->users->order('rank')->order('last_visit', true)->get(); // get all users with ORDER BY `rank` ASC, `last_visit` DESC
$users = $db->users->order('id')->limit(5, 20)->get(); // get users with ORDER BY `id` LIMIT 5, 20
$users = $db->users->limitPage(10, 3)->get(); // get users with LIMIT 10, 20
$users = $db->users->orderRand()->limit(5)->get(); // get 5 random users
$user = $db->users->limit(1)->get(true); // ...->get(true) means getting just on object (not array of objects)

Update

$db->users->posts(50, '<')->limit(5)->update(array('isModerator' => true)); // UPDATE `users` SET `isModerator`='1' WHERE `posts`<'50' LIMIT 5

Delete

$db->users->login('sergey')->delete(); // DELETE FROM `users` WHERE `login`='sergey'

DaBase_Collection appenders(pseudo-JOINS)

It's all about tables relations matched by foreign keys. For example, you have tables:

users: id, login, password
videos: id, userId, file, name
photos: id, userId, file, name
photos_comments: id, userId, photoId, text

For example, you need to recieve compound objects of users that contains as a properties list of videos, photos, and also photos contains comments:

$users = $db->users
->append($db->videos)
->append($db->photos
  ->append($db->photosComments))
->get();

There is result we have:

array
  1 => 
    object(DaBase_Object)[8]
      public 'id' => string '1' (length=1)
      public 'login' => string 'andrey' (length=6)
      public 'password' => string '8aaffd2c9c0341ec6fb91a8bc7d194f8' (length=32)
      public 'videos' => 
        array
          1 => 
            object(DaBase_Object)[11]
              public 'id' => string '1' (length=1)
              public 'userId' => string '1' (length=1)
              public 'file' => string '0544f790141c422d970663e85be39f54' (length=32)
              public 'name' => string 'costa rico' (length=10)
      public 'photos' => 
        array
          1 => 
            object(DaBase_Object)[17]
              public 'id' => string '1' (length=1)
              public 'userId' => string '1' (length=1)
              public 'file' => string '0544f790141c422d970663e85be39f54' (length=32)
              public 'name' => string 'me and mike' (length=11)
              public 'photosComments' => 
                array
                  5 => 
                    object(DaBase_Object)[27]
                      public 'id' => string '5' (length=1)
                      public 'userId' => string '2' (length=1)
                      public 'photoId' => string '1' (length=1)
                      public 'text' => string 'LOL' (length=3)
                  6 => 
                    object(DaBase_Object)[28]
                      public 'id' => string '6' (length=1)
                      public 'userId' => string '3' (length=1)
                      public 'photoId' => string '1' (length=1)
                      public 'text' => string 'WTF does LOL ever means???' (length=26)
  2 => 
    object(DaBase_Object)[9]
      public 'id' => string '2' (length=1)
      public 'login' => string 'sergey' (length=6)
      ...

Filters and orders in appenders

$users = $db->users
->append($db->videos->order('name'))
->append($db->photos
  ->append($db->photosComments->userId(3)->order('id'))
->get();

Join fields names convention

How does DaBase_Collection understand by what field he should join tables? It's simple:

users will be joined by userId
photos will be joined by photoId
photos_comments by photoCommentId 

Appending by custom properties names

$users = $db->users
->append($db->videos->orderRand(), 'randomVideos', 'userId')
->get();

Short appendings syntax

$users = $db->users->photos->videos->get();
// equals
$users = $db->users->append($db->videos)->append($db->photos)->get();

Database objects definition by class models with autovalidation (DaBase_Object, Validator)

By default objects returned by DaBase_Collection are instance of DaBase_Object. Let see what methods we have to manipulate with this objects.

Creating custom collections classes extended from DaBase_Collection

IMPORTANT: This feature is available in version of DaBase that is currently in SVN/trunk (http://code.google.com/p/dabase/source/checkout), DaBase_1.0 does not support it.

So, there is example of this feature. First creating the class extended from DaBase_Collection. Setting $tableName is required, because if you don't do this, then collection RootUsers will try to work with table root_users that of course does not exists. $objectsClass is optional to set, you can don't define it, and in this case RootUsers collection will first check if class RootUser extended from DaBase_Object exists, if does not so all objects of this collection will be from default class DaBase_Object.

class RootUsers  extends DaBase_Collection {
	
	protected $tableName = 'users';
	protected $objectsClass = 'User';
	
	protected function postInit() {
		$this->isRoot(true);
	}
}

Method postInit is always called in construct, so you can set default filters/orders/limits/appenders in this method. So, now you can call:

$db->goldUsers->get();

And you will recieve all users with filter by isRoot='1'. Of course you extend collections not only for setting default filters/orders/limits/appenders, but also for overloading some collection methods ;)

DaBase_Object basics

Create

$user = $db->users->getNew(); // get clear object

OR

$user = $db->users->getNew(array('name' => 'Patrick', 'sirName' => 'Johnson')); // get object with some properties values

Set properties

$user->name = 'Patrick';
$user->sirName = 'Johnson';

OR

$user->name('Patrick')->sirName('Johnson');

OR

$user->setByArray(array('name' => 'Patrick', 'sirName' => 'Johnson'));

Get properties

echo $user->name;

OR

print_r($user->asArray());

Insert

$user = $db->users->getNew();
$user->name = 'Patrick';
$user->sirName = 'Johnson';
$user->insert(); 

OR

$user = $db->users->getNew()->name('Patrick')->sirName('Johnson')->insert();

After insert $user->id will be set to id of new row.

Update

$user->name = 'Patrick';
$user->sirName = 'Johnson';
$user->update();

OR

$user->name('Patrick')->sirName('Johnson')->update();

Delete

$user->delete(); // in this moment $user->id will be reset to NULL 

Data models based on DaBase_Object

As we know, by default all objects returned by DaBase_Collection are instances of class DaBase_Object. One bad thing of this is that we have not speciefed data-models of tables our database. Sometime it's very important to determine what set of properties has some data-model, and for this case and otherwise for case validation this properties we can extend DaBase_Object to our model classes.

Defining object properties

class User extends DaBase_Object {
	
	public $login;
	public $password;
	public $name;
	public $email;
}

We should not define $id property, because it's already defined in DaBase_Object. It means that all tables(data models) we accessing by DaBase_Collection should have field id;

Defining custom table name

As we are talking above, to define custom table name for this model we can set:

class User extends DaBase_Object {
	...
	const table = 'adminpanel_users';
	...
}

Defining properties validation rules

class User extends DaBase_Object {
	...
	const validateOnInsert = true;
	const validateOnUpdate = 'login,email';
	...

	protected function initValidator() {
	$validator = new Validator_Set();
	
	$validator->add('login', array(
	new Validator_Rule_Required(), 
	new Validator_Rule_Length(6, 20), 
	new Validator_Rule_Regexp('/^[a-z\d]*$/ui'), 
	new Validator_Rule_Callback(array(UsersHandler::getInstance(), 'isLoginExists'), 'User with this login is already registered', false)));
	
	$validator->add('password', array(
	new Validator_Rule_Required(),
	new Validator_Rule_Regexp('/^[a-z\d]*$/ui'), 
	new Validator_Rule_Length(6, 50)));
	
	$validator->add('email', array(
	new Validator_Rule_Required(),
	new Validator_Rule_Email()));
	
	return $validator;
	}
	...
}

As we see there is configuration of rules for some properties and configuration validateOnInsert and validateOnUpdate. If validation fails, then we recieve Validator_Exception that contains associative array of properties => errorTexts.

We can also call custom model validation by this way:

$user->validate(); // validate by all properties
$user->validate(array('login', 'email')); // validate custom properties
$user->validate('login,email'); // validate custom properties

To handle validation errors, we can use:

try {
	$user->validate('login,email');
}
catch(Validator_Exception $e) {
	print_r($e->getErrors());
}

To disable Validator_Exception throwing, call $user->validate with second attribute as false

if(!$user->validate('login,email', false)) {
	print_r($user->getValidationErrors());
}