v-dem/queasy-db

Database access classes, part of QuEasy PHP framework

dev-master 2021-09-30 14:08 UTC

This package is auto-updated.

Last update: 2021-09-30 14:08:22 UTC


README

Codacy Badge Build Status codecov Total Downloads License

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

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

Resulting SQL:

SELECT  *
FROM    `users`

Get a single record from users table by id key

$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);

Get multiple records

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

Resulting SQL:

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

Insert a record into users table using associative array

$db->users[] = [
    'email' => 'john.doe@example.com',
    'password_hash' => sha1('myverystrongpassword')
];

Resulting SQL:

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

Insert a record into users table by fields order

$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)

Insert many records into users table by order

$db->users[] = [
    [
        'john.doe@example.com',
        sha1('myverystrongpassword')
    ], [
        'mary.joe@example.com',
        sha1('herverystrongpassword')
    ]
];

Inserting many records into users table with field names denoted separately

$db->users[] = [
    [
        'email',
        'password_hash'
    ], [
        [
            'john.doe@example.com',
            sha1('myverystrongpassword')
        ], [
            'mary.joe@example.com',
            sha1('herverystrongpassword')
        ]
    ]
];

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

$db->users->insert([
    'email' => 'john.doe@example.com',
    '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, returns PDOStatement 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' => 'mysql',
            'host' => 'localhost',
            'name' => 'test',
            'user' => 'test_user',
            'password' => 'test_password'
        ],
        'fetchMode' => PDO::FETCH_ASSOC,
        'tables' => [
            'user_roles' => [
                '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' => [
                '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.