library oppa

k-gun/oppa

Oppa: Database abstraction, query building and active record implementation with PHP.

Oppa

Providing an easy interface, aims to simplify database CRUD operations/transactions that you tire. Oppa has also an Active Record implementation interface that sometimes make the things easier for you., _(*1)

Secures user inputs sharply, carries results of your queries gently, handles errors smoothly, makes batch transactions/commits carefully and profiles your all query processes optionally for you. Oppa also provides a powerful logging mechanizm to report events that you may wonder about., _(*2)

You will be enjoying while using it, promise.. :), _(*3)

Before beginning;, _(*4)

• Set your autoloader properly
• Use PHP >= 7.1 (older versions here: v1, v2 (7.0))
• Use try/catch blocks
• You can use test.sql in test folder
• Wiki updated for v2, v3
• Supports MySQLi and PgSQL

You can see wiki pages for more doc: https://github.com/k-gun/oppa/wiki, _(*5)

Autoloading / Using Libraries

# composer
~$ composer require k-gun/oppa

// manual
$autoload = require('<path to oppa>/src/Autoload.php');
$autoload->register();

Config

// simply for single databases, see wiki for more
$cfg = [
   'agent'    => 'mysql',
   'database' => [
      'host'     => 'localhost',  'name'     => 'test',
      'username' => 'test',       'password' => '********',
      'charset'  => 'utf8',       'timezone' => '+00:00',
   ]
];

Simple Usage

$db = new Oppa\Database($cfg);
$db->connect();

$agent = $db->getLink()->getAgent();
$agent->query('select * from `users` where `old` > ?', [25]);
dump $agent->rowsCount();

Holy CRUD Stuffs

// raw queries
$result = $agent->query('select * from `users`');
if ($result->hasData())
// if ($result->count() > 0)
   foreach ($result as $user)
      dump $user->name;

// or
if ($agent->rowsCount())
   foreach ($agent->getResult() as $user)
   // or foreach ($agent->getResult()->getData() as $user)
      dump $user->name;

// fetch one
$user = $agent->get('select * from `users` where `old` > ?', [50]);
dump $user->name;
// fetch all
$users = $agent->getAll('select * from `users` where `old` > ?', [50]);
foreach ($users as $user) {
   dump $user->name;
}

// or shorcut methods

// get one user
$result = $agent->select('users');
// get one users if old greater than 50
$result = $agent->select('users', '*', 'old > ?', [50]);
// get many users
$result = $agent->selectAll('users');
// get many users if old greater than 50
$result = $agent->selectAll('users', '*', 'old > ?', [50]);

// insert a user
$result = $agent->insert('user', ['name' => 'Ali', 'old' => 30]); // int: last insert id
// insert many users
$result = $agent->insertAll('user', [['name' => 'Ali', 'old' => 30], ...]); // int[]: last insert ids

// update a user
$result = $agent->update('user', ['old' => 30], 'id = ?', [123]); // int: affected rows
// update many users
$result = $agent->updateAll('user', ['old' => 30], 'id > ?', [123]); // int: affected rows

// delete a user
$result = $agent->delete('user', 'id = ?', [123]); // int: affected rows
// delete many users
$result = $agent->deleteAll('user', 'id > ?', [123]); // int: affected rows

Query Builder

// use and init with exists $db
use Oppa\Query\Builder as Query;

$query = new Query($db->getLink());
// set target table
$query->setTable('users u');

// build query
$query->select('u.*')
    ->aggregate('sum', 'us.score', 'sum_score')
    ->join('users_score us', 'us.user_id=u.id')
        ->selectMore('us.score')
    ->joinLeft('users_login ul', 'ul.user_id=u.id')
        ->selectMore('ul.login')
    ->whereIn('u.id', [1,2,3])
    ->whereBetween('u.old', [30,50])
    ->whereNotNull('ul.login')
    ->groupBy('u.id')
    ->orderBy('u.old')
    ->having('sum_score <= ?', [30])
    ->limit(0,10)
;

Gives the result below., _(*6)

SELECT
   u.*
   , us.score
   , ul.login
   , sum(us.score) AS sum_score
FROM users u
JOIN users_score us ON (us.user_id=u.id)
LEFT JOIN users_login ul ON (ul.user_id=u.id)
WHERE (u.id IN(1,2,3) AND u.old BETWEEN 30 AND 50 AND ul.login IS NOT NULL)
GROUP BY u.id
HAVING (sum_score <= 30)
ORDER BY old
LIMIT 0,10

Batch Actions (also Transactions)

Single Transaction, _(*7)

// get batch object
$batch = $agent->getBatch();

// set autocommit=0 (not needed for pgsql)
$batch->lock();
try {
    // commit
    $batch->doQuery('insert into `users` values(null,?,?)', ['John', 25]);
} catch (\Throwable $e) {
    // rollback
    $batch->undo();
}
// set autocommit=1 (not needed for pgsql)
$batch->unlock();

// get last insert id if success
$result = $batch->getResult();
if ($result) {
    dump $result->getId();
}

// remove query queue and empty result array
$batch->reset();

Bulk Transaction, _(*8)

// get batch object
$batch = $agent->getBatch();

// set autocommit=0 (not needed for pgsql)
$batch->lock();
try {
    $batch->queue('insert into `users` values(null,?,?)', ['John', 25]);
    $batch->queue('insert into `users` values(null,?,?)', ['Boby', 35]);
    $batch->queue('insert into `uzerz` values(null,?,?)', ['Eric', 15]); // boom!
    // commit
    $batch->do();
} catch (\Throwable $e) {
    // rollback
    $batch->undo();
}
// set autocommit=1 (not needed for pgsql)
$batch->unlock();

// get insert ids if success
foreach ($batch->getResults() as $result) {
    dump $result->getId();
}

// remove query queue and empty result array
$batch->reset();

Active Record

class Users extends Oppa\ActiveRecord\ActiveRecord {
   protected $table = 'users';
   protected $tablePrimary = 'id';
}

// init active record object
$users = new Users($db);

// find one that id=1
$user = $users->find(1);
dump $user;

// check user found?
if ($user->isFound()) {
   dump $user->name;
}

// find all
$users = $users->findAll();
// find many (id=1,2,3)
$users = $users->findAll([1,2,3]);
$users = $users->findAll('id in(?)', [[1,2,3]]);
$users = $users->findAll('id in(?,?,?)', [1,2,3]);
dump $users;

foreach ($users as $user) {
   dump $user->name;
}

$users = $users->findAll([-1,null,'foo']);
dump $users->hasData(); // false

// insert a user
$user = $users->entity();
$user->name = 'Ali';
$user->old  = 40;
dump $user->save();
// or $user = $users->save($user);
// here we see "id" will be filled with last insert id
dump $user;

// update a user (id=1)
$user = $users->entity();
$user->id  = 1;
$user->old = 55;
dump $user->save();
// or $users->save($user);

// update a user that already exists (id=1)
$user = $users->find(1);
if ($user->isFound()) {
    $user->old = 100;
    dump $user->save();
}

// remove a user (id=1)
$user = $users->entity();
$user->id = 1;
dump $users->remove(1);
// or $users->remove($user);

// remove a user that already exists (id=1)
$user = $users->find(1);
if ($user->isFound()) {
    dump $user->remove();
}

// remove users (id=1,2,3)
dump $users->removeAll([1,2,3]);

See wiki pages for more doc: https://github.com/k-gun/oppa/wiki, _(*9)

Name (Oppa)

Actually, I did not know what it means but some after doing some search for its meaining, I found this and must say apriciated by naming this open source project with a honorific word that means older brother.. :), _(*10)