DB

Database component of AttwFramework.

##Composer ###Download

{
    "require": {
        "attwframework/db": "dev-master"
    }
}

##Support ###Database

• MySQL

###Driver

• PDO
• MySQLi

##How to use ###Connecting with a relational database Create an object to be the connector, passing the instance of connector configurations in the constructor

use Attw\Db\Connection\PDOConnector;

$connector = new PDOConnector('mysql:host=localhost;dbname=test', 'root', 'pass');

Connections collection

use Attw\Db\Connection\Connector\Config\MySQLConnectionConfig;
use Attw\Db\Connection\PDOConnector;
use Attw\Db\Collection as DBCollection;

$connector = new PDOConnector('mysql:host=localhost;dbname=test', 'root', 'pass');

$connections = DBCollection::getInstance();
$connections->add('ConnectionName', $connector);

The default methods of a connector are:

• Attw\Db\Connection\ConnectorInterface::getConnection() Returns the connection
• Attw\Db\Connection\ConnectorInterface::getDriver() Returns the connected driver
• Attw\Db\Connection\ConnectorInterface::query($sql) Execute a SQL query
• Attw\Db\Connection\ConnectorInterface::prepare($sql) Prepares a statement for execution and returns a statement object
• Attw\Db\Connection\ConnectorInterface::exec($sql) Execute an SQL statement and return the number of affected rows
• Attw\Db\Connection\ConnectorInterface::lastInsertId([ string $name ]) Returns the ID of the last inserted row or sequence value
• Attw\Db\Connection\ConnectorInterface::getStatement( $sql ) Returns statement class

###Storage methods Methods useds for interaction with database

Crud: ####Inserting something

use Attw\Db\Connection\PDOConnector;
use Attw\Db\Storage\Storage;
use Attw\Db\Sql\MySQL;

$connector = new PDOConnector('mysql:host=localhost;dbname=test', 'root', 'pass');
$storage = new Storage($connector, new MySQL());
$storage->create('users', array(
    'name' => 'Gabriel Jacinto', 
    'email' => 'gamjj74@hotmail.com',
    'age' => 15,
    'gender' => 'male'
))->execute();

####Updating something

$storage->update('users', array('name' => 'Gabriel Jacinto'), array('id' => 17))
    ->execute();

####Deleting something

$storage->remove('users', array('id' => 17))
    ->execute();

####Selecting somethig

$stmt = $storage->select('users')->where(array('id' => 17));
$stmt->execute();
print_r($stmt->fetch());

####Counting results

$stmt = $storage->select('users')->where(array('id' => 17));
$stmt->execute();
$total = $stmt->rowCount();

####Executing a SQL query

$connector->query("DELETE FROM `users` WHERE `id` = '20'");

####Prepared statements

use Attw\Db\Connection\PDOConnector;
use Attw\Db\Storage\Storage;
use Attw\Db\Sql\MySQL;

$connector = new PDOConnector('mysql:host=localhost;dbname=test', 'root', 'pass');

$stmt = $connector->getStatement("SELECT * FROM `users` WHERE `id` = ?");
$stmt->execute(array(20));
$userData = $stmt->fetch();

###Entities Entities are classes that represent tables. To create an entity, create a class that extends Attw\Db\Storage\Entity\AbstractEntity. The configurations of an entity must be on a property called $_configs as an array. If a column of the table represents a registry of other table, create an index entities like the example. And if a column represents a datetime column, create an index datetime:

namespace Your\Namespace\Entity;

use Attw\Db\Storage\Entity\AbstractEntity;

class User extends AbstractEntity
{
    protected $_configs = array(
        'table' => 'users',
        'primary_key' => 'id',
        'entities' => array(
            'category' => 'Your\Namespace\Entity\Category'
        ),
        'datetime' => array(
            'created_at',
            'updated_at'
        )
    );
    
    protected $id;
    protected $username;
    protected $email;
    protected $category;
    protected $created_at;
    protected $updated_at;
}

####Inserting and updataing If you indicate the primary key and it exists, an update will be make.

Insert

use Attw\Db\Connection\PDOConnector;
use Attw\Db\Storage\Storage;
use Attw\Db\Sql\MySQL;
use Attw\Db\Entity\EntityStorage;

$connector = new PDOConnector('mysql:host=localhost;dbname=test', 'root', 'pass');
$storage = new Storage($connector, new MySQL());
$entityStorage = new EntityStorage($storage);

$user = new User();
$user->username = 'Gabriel';
$user->email = 'gamjj74@hotmail.com';

$entityStorage->persist($user);

Update

$user = new User(17);//Id on contructor
$user->email = 'other@email.com';

$entityStorage->persist($user);

####Removing

$user = new User(17);

$entityStorage->remove($user);

####Fetching Fetch one

$user = new User(21);

$data = $entityStorage->fetch($user);

Fetch all

$user = new User();

$data = $entityStorage->fetchAll($user);