QuEasy PHP Framework - Database
Database access classes. Some the most usual queries can be built automatically, more complex queries can be added into database and/or tables config.
- PHP version 5.3 or higher
composer require v-dem/queasy-db:master-dev
It will also install v-dem/queasy-helper
.
queasy\db\Db
class inheritsPDO
class, so anyPDO
methods can be called with it- You can use
setLogger()
method which acceptsPsr\Log\LoggerInterface
to log all queries
- For MySQL Server need to set option
PDO::MYSQL_ATTR_INIT_COMMAND
toSET GLOBAL SQL_MODE=ANSI_QUOTES
. - For MS SQL Server similar:
SET QUOTED_IDENTIFIER O
.
Sample:
$db = new queasy\db\Db(
[
'connection' => [
'driver' => 'mysql',
'host' => 'localhost',
'name' => 'test',
'user' => 'test_user',
'password' => 'test_password'
],
'options' => [ // Optional. Driver options
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET GLOBAL SQL_MODE=ANSI_QUOTES' // Required for MySQL
]
]
);
Or
$db = new queasy\db\Db(
[
'connection' => [
'dsn' => 'pgsql:host=localhost;dbname=test',
'user' => 'test_user',
'password' => 'test_password'
]
]
);
Or PDO-way:
$db = new queasy\db\Db('pgsql:host=localhost;dbname=test', 'test_user', 'test_password');
- By default error mode is set to
PDO::ERRMODE_EXCEPTION
$users = $db->users->all();
Resulting SQL:
SELECT *
FROM "users"
$user = $db->users->id[$userId];
Resulting SQL:
SELECT *
FROM "users"
WHERE "id" = :id
It's possible to use select()
method to pass PDO options; select()
returns array of rows:
$users = $db->users->id->select($userId, $options);
$users = $db->users->id[[$userId1, $userId2]];
Resulting SQL:
SELECT *
FROM "users"
WHERE "id" IN (:id_1, :id_2)
$db->users[] = [
'email' => 'john.doe@example.com',
'password_hash' => sha1('myverystrongpassword')
];
Resulting SQL:
INSERT INTO "users" ("email", "password_hash")
VALUES (:email, :password_hash)
$db->users[] = [
'john.doe@example.com',
sha1('myverystrongpassword')
];
Insert many records into users
table using associative array (it will generate single INSERT
statement)
$db->users[] = [
[
'email' => 'john.doe@example.com',
'password_hash' => sha1('myverystrongpassword')
], [
'email' => 'mary.joe@example.com',
'password_hash' => sha1('herverystrongpassword')
]
];
Resulting SQL:
INSERT INTO "users" ("email", "password_hash")
VALUES (:email_1, :password_hash_1),
(:email_2, :password_hash_2)
$db->users[] = [
[
'john.doe@example.com',
sha1('myverystrongpassword')
], [
'mary.joe@example.com',
sha1('herverystrongpassword')
]
];
$db->users[] = [
[
'email',
'password_hash'
], [
[
'john.doe@example.com',
sha1('myverystrongpassword')
], [
'mary.joe@example.com',
sha1('herverystrongpassword')
]
]
];
Also it's possible to use insert()
method (in the same way as above) when need to pass PDO options:
$db->users->insert([
'email' => 'john.doe@example.com',
'password_hash' => sha1('myverystrongpassword')
], $options);
$newUserId = $db->id();
$db->users->id[$userId] = [
'password_hash' => sha1('mynewverystrongpassword')
]
$db->users->id[[$userId1, $userId2]] = [
'is_blocked' => true
]
unset($db->users->id[$userId]);
unset($db->users->id[[$userId1, $userId2]]);
$usersCount = count($db->users);
$db->trans(function(queasy\db\Db $db) use(...) {
// Run queries inside a transaction
});
queasy\db\Db
instance will be passed as first argument.
foreach ($db->users as $user) {
// Do something
}
$result = $db->run('
SELECT *
FROM "users"
WHERE "name" LIKE concat(\'%\', :searchName, \'%\')',
[
':searchName' => $searchName
]
);
- Possible 3rd argument is
$driverOptions
which will be passed toPDO::prepare()
This feature can help keep code cleaner and place SQL code outside PHP, somewhere in config files.
$db = new queasy\db\Db(
[
'connection' => [
'driver' => pgysql',
'host' => 'localhost',
'name' => 'test',
'user' => 'test_user',
'password' => 'test_password'
],
'queries' => [
'getActiveUserByName' => [
'sql' => '
SELECT *
FROM "user_roles"
WHERE "name" = :name
AND "is_active" = 1',
'returns' => Db::RETURN_ONE
]
]
]
);
$user = $db->getActiveUserByName([
'name' => 'John Doe'
]);
- Possible values for
returns
option areDb::RETURN_STATEMENT
(default, returnsPDOStatement
instance),Db::RETURN_ONE
,Db::RETURN_ALL
,Db::RETURN_VALUE
Also it is possible to group predefined queries by tables:
$db = new queasy\db\Db(
[
'connection' => [
'driver' => 'pgsql',
'host' => 'localhost',
'name' => 'test',
'user' => 'test_user',
'password' => 'test_password'
],
'tables' => [
'users' => [
'getActiveByName' => [
'sql' => '
SELECT *
FROM "user_roles"
WHERE "name" = :name
AND "is_active" = 1',
'returns' => Db::RETURN_ONE
]
]
]
]
);
$user = $db->users->getActiveByName([
'name' => 'John Doe'
]);
config.php:
return [
'db' => [
'connection' => [
'driver' => 'pgsql',
'host' => 'localhost',
'name' => 'test',
'user' => 'test_user',
'password' => 'test_password'
],
'tables' => [
'users' => [
'getActiveByName' => [
'sql' => '
SELECT *
FROM "users"
WHERE "name" = :name
AND "is_active" = 1',
'returns' => Db::RETURN_ONE
]
]
]
],
'logger' => [
[
'class' => queasy\log\ConsoleLogger::class,
'minLevel' => Psr\Log\LogLevel::DEBUG
]
]
];
$config = new queasy\config\Config('config.php'); // Can be also INI, JSON or XML
$logger = new queasy\log\Logger($config->logger);
$db = new queasy\db\Db($config->db);
$db->setLogger($logger);
$user = $db->users->getActiveByName([
'name' => 'John Doe'
]);
- All queries will be logged with
Psr\Log\LogLevel::DEBUG
level. Also it's possible to use any other logger class compatible with PSR-3.