QuEasy PHP Framework - Database

Package v-dem/queasy-db

Database access classes. Some the most usual queries can be built automatically, more complex queries can be added into database and/or tables config.

Features

Requirements

• PHP version 5.3 or higher

Installation

composer require v-dem/queasy-db:master-dev

Usage

Notes

• `queasy\db\Db` class inherits `PDO` class, so any `PDO` methods can be called with it
• You can use `setLogger()` method which accepts `Psr\Log\LoggerInterface` to log all queries

Initialization

Sample:

$db = new queasy\db\Db(
    [
        'connection' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'name' => 'test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC // Default fetch mode for all queries
    ]
);

Or

$db = new queasy\db\Db(
    [
        'connection' => [
            'dsn' => 'mysql:host=localhost;dbname=test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC // Default fetch mode for all queries
    ]
);

Or PDO-way:

$db = new queasy\db\Db('mysql:host=localhost;dbname=test', 'test_user', 'test_password');

• By default error mode is set to `PDO::ERRMODE_EXCEPTION`

Get all records from users table

$user = $db->users->all();

Get a single record from users table by id key

$user = $db->users->id[$userId];

It will generate the following query:

SELECT  *
FROM    `users`
WHERE   `id` = :id

It's possible to use select() method to pass PDO options:

$user = $db->users->id->select($userId, $options);

Get multiple records

$users = $db->users->id[[$userId1, $userId2]];

SQL:

SELECT  *
FROM    `users`
WHERE   `id` IN (:id_1, :id_2)

Insert a record into users table using associative array

$db->users[] = [
    'email' => '[email protected]',
    'password_hash' => sha1('myverystrongpassword')
];

It will generate the following query:

INSERT  INTO `users` (`email`, `password_hash`)
VALUES  (:email, :password_hash)

Insert a record into users table by fields order

$db->users[] = [
    '[email protected]',
    sha1('myverystrongpassword')
];

Insert many records into users table using associative array (it will generate single INSERT statement)

$db->users[] = [
    [
        'email' => '[email protected]',
        'password_hash' => sha1('myverystrongpassword')
    ], [
        'email' => '[email protected]',
        'password_hash' => sha1('herverystrongpassword')
    ]
];

SQL:

INSERT  INTO `users` (`email`, `password_hash`)
VALUES  (:email_1, :password_hash_1),
        (:email_2, :password_hash_2)

Insert many records into users table by order

$db->users[] = [
    [
        '[email protected]',
        sha1('myverystrongpassword')
    ], [
        '[email protected]',
        sha1('herverystrongpassword')
    ]
];

Inserting many records into users table with field names denoted separately

$db->users[] = [
    [
        'email',
        'password_hash'
    ], [
        [
            '[email protected]',
            sha1('myverystrongpassword')
        ], [
            '[email protected]',
            sha1('herverystrongpassword')
        ]
    ]
];

It's possible to use insert() method to pass PDO options:

$db->users->insert([
    'email' => '[email protected]',
    'password_hash' => sha1('myverystrongpassword')
], $options);

Get last insert id (alias of lastInsertId() method)

$newUserId = $db->id();

Update a record in users table by id key

$db->users->id[$userId] = [
    'password_hash' => sha1('mynewverystrongpassword')
]

Update multiple records

$db->users->id[[$userId1, $userId2]] = [
    'is_blocked' => true
]

Delete a record in users table by id key

unset($db->users->id[$userId]);

Delete multiple records

unset($db->users->id[[$userId1, $userId2]]);

Get count of all records in users table

$usersCount = count($db->users);

Using transactions

$db->trans(function(queasy\db\Db $db) use(...) {
    // Run queries inside a transaction
});

• `queasy\db\Db` instance will be passed as first argument.

Using foreach with a users table

foreach($db->users as $user) {
    // Do something
}

Run custom query (returns PDOStatement)

$result = $db->run('
    SELECT  *
    FROM    `users`
    WHERE   `name` LIKE concat(\'%\', :searchName, \'%\')',
    [
        ':searchName' => $searchName
    ]
);

• Possible 3rd argument is `$driverOptions` which will be passed to `PDO::prepare()`

Run query predefined in configuration

This feature can help keep code cleaner and place SQL code outside PHP, somewhere in config files.

$db = new queasy\db\Db(
    [
        'connection' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'name' => 'test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC,
        'queries' => [
            'selectUserRoleByName' => [
                'sql' => '
                    SELECT  *
                    FROM    `user_roles`
                    WHERE   `name` = :name',
                'returns' => Db::RETURN_ONE
            ]
        ]
    ]
);

$role = $db->selectUserRoleByName(['name' => 'Manager']);

• Possible values for `returns` option are `Db::RETURN_STATEMENT` (default), `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' => 'mysql',
            'host' => 'localhost',
            'name' => 'test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC,
        'tables' => [
            `user_roles` => [
                `queries` => [
                    'selectUserRoleByName' => [
                        'sql' => '
                            SELECT  *
                            FROM    `user_roles`
                            WHERE   `name` = :name',
                        'returns' => Db::RETURN_ONE
                    ]
                ]
            ]
        ]
    ]
);

$role = $db->user_roles->selectUserRoleByName(['name' => 'Manager']);

Using v-dem/queasy-db together with v-dem/queasy-config

$config = new queasy\config\Config('config.php'); // Can be also INI, JSON or XML
$db = new queasy\db\Db($config->db);

config.php:

return [
    'db' => [
        'connection' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'name' => 'test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC,
        'tables' => [
            'user_roles' => [
                'queries' => [
                    'selectUserRoleByName' => [
                        'sql' => '
                            SELECT  *
                            FROM    `user_roles`
                            WHERE   `name` = :name',
                        'returns' => Db::RETURN_ONE
                    ]
                ]
            ]
        ]
    ]
];

Using v-dem/queasy-db together with v-dem/queasy-log

$config = new queasy\config\Config('config.php');
$logger = new queasy\log\Logger($config->logger);
$db = new queasy\db\Db($config->db);
$db->setLogger($config->logger);

• 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.