eznio/db

DB Collections library inspired by Laravel

ezn.io\db

DB Entity Collections library inspired by Laravel, _(*1)

Quickstart

// Initializing database-specific driver
$driver = new \eznio\db\drivers\Sqlite('db/test.sqlite3');

// Creating Entity Manager
$em = new \eznio\db\EntityManager($driver);

// Getting query repository by table name
$repo = $em->getRepository('test');

// Getting ActiveRecord entity of table row with id = 1
$entity =$repo->findOneById(1);

// Updating and saving entity
$entity->field = 'new value';
$entity->save();

More details

Supported RDBMS

SQLite and MySQL for now., _(*2)

Error handling

Especially designed to be silent, no internal exceptions. Functions return null/[] if anything happens., _(*3)

Placeholders basics

Several driver's functions accept second parameter called $args. This is list of placeholder values. Library is using PDO placeholders syntax, so we have 2 options:, _(*4)

• use ? as placeholder name and provide simple list of substitutions:, _(*5)

  $driver->query(
     'SELECT * FROM USERS WHERE name = ? AND age = ?',
     ['John', 26]
  );
  

  In this case, substitutions order should be the same as in query., _(*6)

• use :named: placeholders:, _(*7)

  $driver->query(
     'SELECT * FROM USERS WHERE name = :name: AND age = :age:',
     ['age' => 26, 'name' => 'John']
  );
  

  Named substitutions can be passed in any order., _(*8)

Reference

Driver

Driver is DB-specific request handling class. Implements the \eznio\db\drivers\Driver interface., _(*9)

query()

public function query($sql, array $args = []);

Runs SQL query and doesn't return anything. Useful for system queries like "SET NAMES" or similar., _(*10)

$driver->query("SET NAMES :encoding:", ['encoding' => 'UTF-8'];

select()

public function select($sql, array $args = []);

Runs SQL query and returns produced result as array., _(*11)

$result => $driver->select("SELECT * FROM users WHERE name = ?", ['John']);

/*  $result = [
 *      ['id' => 1, 'name' => 'John', 'surname' => 'Smith'],
 *      ['id' => 2, 'name' => 'John', 'surname' => 'Doe']
 */ ];

If ARRAY_KEY alias exists in resulting set - it's value will be added as array keys (and removed from resulting rows):, _(*12)

$result => $driver->select("SELECT id AS ARRAY_KEY, * FROM users WHERE name = ?", ['John']);

/*  $result = [
 *      1 => ['name' => 'John', 'surname' => 'Smith'],
 *      2 => ['name' => 'John', 'surname' => 'Doe']
 */ ];

getRow()

public function getRow($sql, array $args = []);

Runs SQL query and returns produced its first row as array., _(*13)

$result => $driver->getRow("SELECT * FROM users WHERE name = ?", ['John']);

getColumn()

public function getColumn($sql, array $args = []);

Runs SQL query and returns produced its first column as array of one-element arrays., _(*14)

$result => $driver->getColumn("SELECT id FROM users WHERE name = ?", ['John']);

/*  $result = [
 *      1,
 *      2
 */ ];

ARRAY_KEY alias also works here:, _(*15)

$result => $driver->getColumn("SELECT id AS ARRAY_KEY, surname FROM users WHERE name = ?", ['John']);

/*  $result = [
 *      1 => 'Smith',
 *      2 => 'Doe'
 */ ];

getCell()

public function getCell($sql, array $args = []);

Runs SQL query and returns produced its first column of its first row:, _(*16)

$result => $driver->getCell("SELECT COUNT(*) FROM users WHERE name = ?", ['John']);

//  $result = 2;

load()

public function load($table, $id);

Shortcut to get values of a single row from given table by id:, _(*17)

$result => $driver->load('users', 1);

//  $result = ['id' => 1, name' => 'John', 'surname' => 'Smith'];

insert()

public function insert($table, array $data);

Inserts data into the table and returns inserted ID, _(*18)

$result => $driver->insert('users', [
    'name' => 'John',
    'surname' => 'McKey'
]);

//  $result = 3;

update()

public function update($table, $id, $data);

Updates existing data by row ID:, _(*19)

$driver->update('users', [
    'name' => 'Mike',
], 1);

delete()

public function delete($table, $id);

Deletes row with given ID:, _(*20)

$driver->delete('users', 3);

Entity

Collection

EntityManager