Create a flexible query builder with relationship support and CRUD operations.
- php >= 7.1
- PostgreSQL >=9.5
- MySQL >= 5.7
- MongoDB (planned)
composer require vivace/db
Initialize driver for your database. In this example, the driver for postgresql.
$pdo = new \PDO('dsn', 'user', 'pass');
$driver = new \vivace\db\sql\PostrgeSQL\Driver($pdo);
Initialize storage objects.
$userStorage = new \vivace\db\sql\Storage($driver, 'users');
Now you can use created storages for data manipulation.
Save the data to your storage.
$ok = $userStorage->save(['name' => 'Zoe Saldana', 'career' => 'actor', 'rating' => 4.95]);
Let's try fetch saved data from storage.
$user = $userStorage->filter(['name' => 'Zoe Saldana'])->fetch()->one();
// $user is simple assoc array.
var_dump($user);
Now it's time to change the data
$user['age'] = 39;
And save changes in storage.
$ok = $userStorage->save($user);
$users = $userStorage
->limit(100)
// equalent SQL condition "career IN ('actor', 'producer') OR age >= 40"
->filter(['or', ['in', 'career', ['actor', 'producer'], ['>=', 'age', 40]])
->fetch();
Insert one row
$ok = $userStorage->save(['name' => 'Mark Rufallo']);
Multiple rows
$ok = $userStorage->save([
['name' => 'Mark Rufallo'],
['name' => 'Chris Evans', 'rating' => 4.95],
]);
Update if exists, otherwise insert
$ok = $userStorage->save([
['id' => 6, 'name' => 'Mark Ruffalo'],// This data will be updated, because 'id' is the primary key
['name' => 'Chris Hemsworth'] // This data will be inserted as new row
]);
$numberOfupdated = $userStorage
->sort(['id' => -1])// Sorting by `id` in descending order
->skip(100)// skip first 100 found rows
->update(['career' => 'actor']);
$numberOfDeleted = $userStorage
->filter(['age' => 18, 'rating' => 5])
->and(['in', 'career', ['actor', 'producer'])
// Delete all users by condition "age = 18 AND rating = 5 AND career IN ('actor', 'producer')"
->delete();
$userStorage = $userStorage->projection([
// OneToOne
'country' => new \vivace\Relation\Single($countryStorage, ['country_id' => 'id']),
// OneToMany
'rewards' => new \vivace\Relation\Many($rewardsStorage, ['id' => 'user_id'])
]);
$users = $userStorage->fetch()->all();
foreach($users as $user){
if(isset($user['country'])) {
var_dump($user['country']);
}
foreach($user['rewards'] as $reward){
var_dump($reward);
}
}
$userStorage = $userStorage->projection([
'rank' => 'rating'
]);
// Aliases are available for use in the condition.
$user = $userStorage->filter(['between', 'rank', 4, 5])->fetch()->one();
For tests, you need to connect to a database. If you use a docker, then you can raise the database with the following command:
docker-compose up -d mysql pgsql
And run tests:
docker-compose run --rm php codecept run --env pgsql --env mysql