Пакет для роботи з СКБД Firebird в Laravel

firebird-logoПривіт Хабр! У минулій статті я розповідав про те, як можна додати підтримку Firebird в Laravel. На той момент я не знав про існування пакету jacquestvanzuydam/laravel-firebird і додавав підтримку Firebird з нуля. Зроблено це було через модифікацію файлів ядра Laravel, за що я був справедливо розкритикований. Подивившись пакет jacquestvanzuydam/laravel-firebird, я зрозумів, що його можливості мене не влаштовують, і вирішив розширити його.

У цій статті я хочу описати основні функціональні відмінності мого пакету sim1984/laravel-firebird від пакета jacquestvanzuydam/laravel-firebird.

Підтримка автоинкрементных стовпців
Найважливішим недоліком оригінального пакету є відсутність підтримки автоинкрементных стовпців міграції. В моєму пакеті підтримка автоинкрементных стовпців реалізована двома способами. Перший спосіб є класичним для Firebird. У цьому способі при створенні автоінкрементний стовпця автоматично створюється послідовність (генератор) та INSERT BEFORE тригер. Наступний скрипт на PHP

Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->string('email')->unique();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});

згенерує і виконає наступні оператори SQL

CREATE TABLE "users" (
"id" INTEGER NOT NULL PRIMARY KEY,
"name" VARCHAR(255) NOT NULL,
"email" VARCHAR(255) NOT NULL,
"password" VARCHAR(255) NOT NULL,
"remember_token" VARCHAR(100),
"created_at" TIMESTAMP,
"updated_at" TIMESTAMP
);

ALTER TABLE "users" ADD CONSTRAINT "users_email_unique" UNIQUE ("email");

CREATE SEQUENCE "seq_users";

CREATE OR ALTER TRIGGER "tr_users_bi" FOR "users"
ACTIVE INSERT BEFORE
AS
BEGIN
IF (NEW."id" IS NULL) THEN
NEW."id" = NEXT VALUE FOR "seq_users";
END

Другий спосіб працює, починаючи з Firebird 3.0. У цьому випадку замість послідовності і тригера використовується IDENTITY полі. Наступний скрипт на PHP

Schema::create('users', function (Blueprint $table) {
$table->useIdentity(); // only Firebird 3.0
$table->increments('id');
$table->string('name');
$table->string('email')->unique();
$table->string('password');
$table->rememberToken();
$table->timestamps();
}); 

згенерує і виконає наступні оператори SQL.

CREATE TABLE "users" (
"id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
"name" VARCHAR(255) NOT NULL,
"email" VARCHAR(255) NOT NULL,
"password" VARCHAR(255) NOT NULL,
"remember_token" VARCHAR(100),
"created_at" TIMESTAMP,
"updated_at" TIMESTAMP
);

ALTER TABLE "users" ADD CONSTRAINT "users_email_unique" UNIQUE ("email");

Підтримка INSERT… RETURNING
Граматика Firebird\Schema\Grammars\FirebirdGrammar розширена методом compileInsertGetId, який призначений для складання INSERT запиту з поверненням ідентифікатора щойно доданої рядка.

/**
* Compile insert an and get ID statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @param string $sequence
* @return string
*/
public function compileInsertGetId(Builder $query, $values, $sequence) {
if (is_null($sequence)) {
$sequence = 'id';
}

return $this->compileInsert($query, $values) . 'returning' . $this->wrap($sequence);
}

Тут виявилося, що INSERT… RETURNING не працює через PDO драйвер з Fireird 3.0. Раніше це проявлялося так https://bugs.php.net/bug.php?id=72931. На останніх снапшотах поведінка змінилася і тепер драйвер видає помилку SQLSTATE[HY000]: General error: -502 Cursor is not open. Очевидно, що PDO вміє повертати дані тільки з курсорів (побічно на це натякає метод PDOStatement::fetch()). Цікаво, що це працювало в firebird 2.5. Значить в API, яке повинно бути сумісним, де відбулися зміни, які вплинули на працездатність.

Я вирішив обдурити PDO, переробивши запит так, щоб повертався курсор. Для цього обернем наш оператор INSERT… RETURNING у анонімний PSQL блок (EXECUTE BLOCK). Тут є одна неприємна особливість. Справа в тому, що для підтримки іменованих параметрів PDO робить заміну змінних виду :VARNAME на «?». Це псує вміст тіло анонімного блоку. Така заміна працювала б правильно, якби робилася тільки між ключовими словами EXECUTE BLOCK і AS. Іншим способом є заміна маркера змінних, як це зроблено в деяких інших компонентах доступу. На жаль PDO не має можливості змінити маркер змінних. Тому довелося шукати спосіб уникати символу «:» усередині тіла блоку. Оскільки це треба робити тільки для Firebird 3.0, я виділив для нього окрему граматику FirebirdGrammar30, і додав спеціальний метод для визначення версії Firebird. Крім того, окрема граматика дозволить нам краще використовувати нові можливості Firebird 3.0. Наведу код, який фиксит баг з INSERT… RETURNING

/**
* Fix PDO driver bug for 'INSERT ... RETURNING'
* See https://bugs.php.net/bug.php?id=72931
* Reproduced in Firebird 3.0 only
* Remove when the bug is fixed!
* 
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @param string $sequence
* @param string $sql
*/
private function fixInsertReturningBug(Builder $query, $values, $sequence, $sql)
{
/*
* Since the PDO Firebird driver bug because of which is not executed 
* sql query 'INSERT ... RETURNING', then we wrap the statement in 
* the block and execute it. PDO may not recognize the colon (:) within 
* a block properly, so we will not use it. The only way I found 
* buyout perform a query via EXECUTE STATEMENT.
*/
if (!is_array(reset($values))) {
$values = [$values];
}
$table = $this->wrapTable($query->from);
$columns = array_map([$this, 'wrap'], array_keys(reset($values)));
$columnsWithTypeOf = [];
foreach ($columns as $column) {
$columnsWithTypeOf[] = " {$column} TYPE OF COLUMN {$table}.{$column} = ?";
}
$ret_column = $this->wrap($sequence);

$columns_str = $this->columnize(array_keys(reset($values)));

$new_sql = "EXECUTE BLOCK (\n";
$new_sql .= implode(",\n", $columnsWithTypeOf);
$new_sql .= ")\n";
$new_sql .= "RETURNS ({$ret_column} TYPE OF COLUMN {$table}.{$ret_column})\n";
$new_sql .= "AS\n";
$new_sql .= " DECLARE STMT VARCHAR(8191);\n";
$new_sql .= "BEGIN\n";
$new_sql .= " STMT = '{$sql}';\n";
$new_sql .= " EXECUTE STATEMENT (STMT) ({$columns_str})\n";

if (!$query->getConnection()->getPdo()->inTransaction()) {
// For some unknown reason, there is a ROLLBACK. Probably due to the COMMIT RETAINING.
$new_sql .= " WITH AUTONOMOUS TRANSACTION\n";
}
$new_sql .= " INTO {$ret_column};\n";
$new_sql .= " SUSPEND;\n";
$new_sql .= "END";

return $new_sql;
}

/**
* Compile insert an and get ID statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @param string $sequence
* @return string
*/
public function compileInsertGetId(Builder $query, $values, $sequence)
{
$sql = parent::compileInsertGetId($query, $values, $sequence);
// Fix PDO driver bug for 'INSERT ... RETURNING'
// See https://bugs.php.net/bug.php?id=72931
$sql = $this->fixInsertReturningBug($query, $values, $sequence, $sql);

return $sql;
}

Примітка

Мені зовсім не подобається цей спосіб. Сподіваюся, що в майбутньому буде виправлений баг і це тимчасове рішення можна буде видалити.
Робота з послідовностями
Іноді виникає потреба працювати з послідовністю згенерованої не з допомогою міграцій Laravel, наприклад, ви можете працювати з вже готовою базою даних. У ряді випадку одна і та ж послідовність може використовуватися декількома таблицями. Ви можете використовувати в своїх моделях довільне ім'я послідовності, скориставшись розширеною моделлю Firebird\Eloquent\Model. У цій моделі є додаткове властивість $sequence, яке містить ім'я необхідній послідовності.

Приклад:

use Firebird\Eloquent\Model;


class Customer extends Model
{
/**
* таблиця пов'язана з моделлю
* @var string
*/
protected $table = 'CUSTOMER';

/**
* Первинний ключ моделі
* @var string
*/
protected $primaryKey = 'CUSTOMER_ID'; 

/**
* Indicates if the model should be timestamped.
* @var bool
*/
public $timestamps = false; 

/**
* ім'я для генерації послідовності первинного ключа
* @var string 
*/
protected $sequence = 'GEN_CUSTOMER_ID';
}

В базовій моделі перевизначений метод insertAndSetId, який не використовує INSERT… RETURNING, а отримує наступний номер послідовності і використовує його в звичайному запиті INSERT. Використання цієї моделі дозволяє також не використовувати не дуже гарне рішення INSERT… RETURNING в Firebird 3.0.

Як я вже говорив, послідовності є повністю самостійними об'єктами метаданих у Firebird, тому непогано б мати можливість керувати ними через міграції Laravel. Для цього був написаний клас Firebird\Schema\ SequenceBlueprint. Давайте подивимося, як це працює на прикладі ось такої міграції.

<?php

use Firebird\Schema\Blueprint;
use Firebird\Schema\SequenceBlueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::createSequence('seq_users_id');

Schema::create('users', function (Blueprint $table) {
$table->integer('id')->primary();
$table->string('name');
$table->string('email')->unique();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});


Schema::sequence('seq_users_id', function (SequenceBlueprint $sequence) {
$sequence->increment(5);
$sequence->restart(10);
});
}

/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropSequence('seq_users_id');

Schema::drop('users');
}
}

Накат такої міграції призведе до наступних операторів SQL:

CREATE SEQUENCE "seq_users_id";
CREATE TABLE "users" (
"id" INTEGER NOT NULL,
"name" VARCHAR(255) NOT NULL,
"email" VARCHAR(255) NOT NULL,
"password" VARCHAR(255) NOT NULL,
"remember_token" VARCHAR(100),
"created_at" TIMESTAMP,
"updated_at" TIMESTAMP
);

ALTER TABLE "users" ADD PRIMARY KEY ("id");

ALTER TABLE "users" ADD CONSTRAINT "users_email_unique" UNIQUE ("email");

ALTER SEQUENCE "seq_users_id" RESTART WITH 10 INCREMENT BY 5;

Відкат міграції виконає наступні оператори:

DROP SEQUENCE "seq_users_id";

DROP TABLE "users";

Додаткові параметри конфігурації
У нашому пакеті додано два додаткові параметри конфігурації для налаштування підключення:

  • role – ім'я ролі якої відбудеться підключення до бази даних;
  • engine_version – версія Firebird. Цей необов'язковий параметр не дозволяє робити додатковий запит до сервера для визначення версії Firebird.
Приклад:

'connections' => [

'firebird' => [
'driver' => 'firebird',
'host' => env('DB_HOST', 'localhost'), 
'port' => env('DB_PORT', '3050'),
'database' => env('DB_DATABASE', 'examples'),
'username' => env('DB_USERNAME', 'BOSS'),
'password' => env('DB_PASSWORD', 'qw897tr'),
'role' => 'RDB$ADMIN',
'charset' => env('DB_CHARSET', 'UTF8'),
'engine_version' => '3.0.0', 
],
],

Особливості установки через composer
Оскільки мій пакет є форком пакету jacquestvanzuydam/laravel-firebird, то його встановлення дещо відрізняється. Як і при встановленні оригінального пакету, не забувайте ставити в composer.json параметр minimum-stability рівний dev. Далі необхідно додати посилання на репозиторій:

"repositories": [
{
"type": "package",
"package": {
"version": "dev-master",
"name": "sim1984/laravel-firebird",
"джерело": {
"url": "https://github.com/sim1984/laravel-firebird",
"type": "git",
"reference": "master"
},
"autoload": {
"classmap": [""]
}
}
}
], 

Після чого додайте параметр require наступний рядок:

"sim1984/laravel-firebird": "dev-master"

Висновок
Я сподіваюся, що мій пакет має достатній функціонал для розробки додатків з використанням СУБД Firebird. Якщо у вас є питання та пропозиції по поліпшенню пакету sim1984/laravel-firebird пишіть в лічку, обов'язково відповім. У наступній статті я розповім про те, як створити невеликий додаток з використанням Laravel і СУБД Firebird.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.