v-dem/queasy-db

Database access classes, part of QuEasy PHP framework

dev-master 2024-02-25 21:35 UTC

Requires

• php: >=5.3.0|>=7.0.0|>=8.0.0
• ext-pdo: *
• psr/log: ~1.1
• v-dem/queasy-helper: dev-master

Requires (Dev)

• php: >=7.1.0
• ext-sqlite3: *
• ext-xdebug: *
• phpunit/phpunit: ~10

Suggests

• queasy/config: Configuration provider package, supports PHP (and multifile configs in this case), INI, XML and JSON (and YAML in future) formats
• queasy/log: PSR-3 compatible logger package, supports file system log writer, console and email (using mail() function)

LGPL-3.0-only

• Vitaly Demyanenko

• database
• mysql
• sqlite
• postgresql
• pdo
• pdo wrapper

README

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

It will also install v-dem/queasy-helper.

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

IMPORTANT!

• For MySQL Server need to set option PDO::MYSQL_ATTR_INIT_COMMAND to SET GLOBAL SQL_MODE=ANSI_QUOTES.
• For MS SQL Server similar: SET QUOTED_IDENTIFIER O.

Initialization

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

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' => '[e-mail]',
    '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[] = [
    '[e-mail]',
    sha1('myverystrongpassword')
];

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

$db->users[] = [
    [
        'email' => '[e-mail]',
        'password_hash' => sha1('myverystrongpassword')
    ], [
        'email' => '[e-mail]',
        '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[] = [
    [
        '[e-mail]',
        sha1('myverystrongpassword')
    ], [
        '[e-mail]',
        sha1('herverystrongpassword')
    ]
];

Inserting many records into users table with field names denoted separately

$db->users[] = [
    [
        'email',
        'password_hash'
    ], [
        [
            '[e-mail]',
            sha1('myverystrongpassword')
        ], [
            '[e-mail]',
            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' => '[e-mail]',
    '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' => 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 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' => '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'
]);

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

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.