Package for working with Firebird in Laravel

Hi Habr! In the last article, I talked about how you can add support for Firebird in Laravel. At that moment I did not know about the existence of the jacquestvanzuydam / laravel-firebird package and added Firebird support from scratch. This was done through the modification of the Laravel core files, for which I was rightly criticized. Looking at the jacquestvanzuydam / laravel-firebird package, I realized that its capabilities do not suit me, and decided to expand it.

In this article I want to describe the main functional differences between my sim1984 / laravel-firebird package and the jacquestvanzuydam / laravel-firebird package.

Auto-increment column support

The most important disadvantage of the original package is the lack of support for auto-increment columns in the migration. In my package, support for auto-increment columns is implemented in two ways. The first method is classic for Firebird. In this method, when creating an auto-increment column, a sequence (generator) and a BEFORE INSERT trigger are automatically created. The following PHP script

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

will generate and execute the following SQL statements
')

  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 BEFORE INSERT AS BEGIN IF (NEW."id" IS NULL) THEN NEW."id" = NEXT VALUE FOR "seq_users"; END 

The second method works starting with Firebird 3.0. In this case, the IDENTITY field is used instead of a sequence and a trigger. The following PHP script

  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(); }); 

Generates and executes the following SQL statements.

  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 support ... RETURNING

The Firebird \ Schema \ Grammars \ FirebirdGrammar grammar is expanded by the compileInsertGetId method, which is intended for building an INSERT query with the return of the identifier of the newly added string.

  /** * Compile an insert 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); } 

It was found out here that INSERT ... RETURNING does not work through PDO driver with Fireird 3.0. Previously, it was manifested as https://bugs.php.net/bug.php?id=72931 . On the last snapshot, the behavior has changed and now the driver produces an SQLSTATE [HY000] error: General error: -502 Cursor is not open. Obviously, PDO can only return data from cursors (indirectly hinted at by the PDOStatement :: fetch () method). Interestingly, this worked in firebird 2.5. So in the API, which must be compatible, somewhere there have been changes that have affected the performance.

I decided to trick the PDO by reworking the query so that the cursor would return. To do this, we wrap our INSERT ... RETURNING operator into an anonymous PSQL block (EXECUTE BLOCK). There is one unpleasant feature. The fact is that in order to support the named parameters, the PDO does the replacement of all variables of the form: VARNAME with “?”. This spoils the contents of the body of the anonymous block. Such a replacement would work correctly if it were made only between the keywords EXECUTE BLOCK and AS. Another way is to replace the marker variables, as it is done in some other components of access. Unfortunately the PDO does not have the ability to change the variable token. Therefore, I had to find a way to avoid the “:” symbol inside the block body. Since this should be done only for Firebird 3.0, I allocated a separate FirebirdGrammar30 grammar for it, and added a special method to determine the version of Firebird. In addition, a separate grammar will allow us to better use the new features of Firebird 3.0. I will give a code that fixes a bug with 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 an insert 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; } 

Comment I absolutely do not like this method. I hope that in the future the bug will be fixed and this temporary solution can be removed.

Work with sequences

Sometimes there is a need to work with a sequence generated not using Laravel migrations, for example, you can work with a ready-made database. In some cases, the same sequence can be used by several tables. You can use an arbitrary sequence name in your models using the extended Firebird \ Eloquent \ Model. In this model, there is an additional property $ sequence that will contain the name of the desired sequence.

Example:

 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'; } 

In the base model, the insertAndSetId method is redefined, which does not use INSERT ... RETURNING, but receives the next sequence number and uses it in a normal INSERT query. The use of this model also makes it possible not to use the not-so-beautiful INSERT solution ... RETURNING in Firebird 3.0.

As I said, sequences are completely independent metadata objects in Firebird, so it would be nice to be able to manage them through Laravel migrations. For this, the Firebird \ Schema \ SequenceBlueprint class was written. Let's see how this works on the example of such a migration.

 <?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'); } } 

Rolling out such a migration will result in the execution of the following SQL statements:

 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; 

Migration rollback will execute the following statements:

 DROP SEQUENCE "seq_users_id"; DROP TABLE "users"; 

Advanced configuration options

Our package has added two additional configuration parameters to configure the connection:

• role - the name of the role that will connect to the database;
• engine_version - version of Firebird. This optional parameter allows you to not make an additional request to the server to determine the version of Firebird.

Example:

  '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', ], ], 

Features of installation through composer

Since my package is a fork of the jacquestvanzuydam / laravel-firebird package , its installation is somewhat different. As with the installation of the original package, do not forget to set the minimum-stability parameter in composer.json equal to dev. Next you need to add a link to the repository:

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

Then add the following line to the require parameter:

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

Conclusion

I hope that my package has sufficient functionality for developing applications using Firebird DBMS. If you have questions and suggestions for improving the package sim1984 / laravel-firebird write in a personal, be sure to answer. In the next article I will discuss how to create a small application using Laravel and Firebird DBMS.