Adding support for Firebird DBMS to Laravel framework
At the time of writing the example (there will be a link later) of a web application in PHP using the Firebird DBMS, the question arose of choosing a framework for development using the MVC architectural model. The choice of PHP frameworks is very large, but Laravel seemed to be the most convenient, simple and easily extensible. However, this framework did not support Firebird from the DBMS box. Laravel uses the PDO driver to work with the database. Since there is a PDO driver for Firebird, this gave me the idea that it is possible to get Laravel to work with Firebird with some effort.
Laravel is a free, open source web framework designed for development using the MVC architecture model (Model View Controller - Model View Controller). Laravel is a convenient and easily extensible framework for building your web applications. Out of the box, the Laravel framework supports 4 DBMSs: MySQL, Postgres, SQLite, and MS SQL Server. In this article I will tell how to add another Firebird DBMS.
Each time you connect to a database, a specific connection instance is created using the Illuminate \ Database \ Connectors \ ConnectionFactory factory, depending on the type of DBMS that implements the Illuminate \ Database \ ConnectionInterface. In addition, the factory creates a certain connector that, based on the configuration parameters, forms a connection string and passes it to the PDO connection designer. The connector is created in the createConnector method. We modify it a little so that it can create a connector for Firebird.
We define the Illuminate \ Database \ Connectors \ FirebirdConnector connector class a bit later. In the meantime, we modify the createConnection method intended to create a connection that implements the Illuminate \ Database \ ConnectionInterface interface.
')
Now let's move on to creating a connector for Firebird - class Illuminate \ Database \ Connectors \ FirebirdConnector. You can take any of the existing connectors as a basis, for example, a Postgres connector and remake it under Firebird.
To begin with, we will change the method for generating the connection string in accordance with the format of the string described in the documentation :
The method that creates a PDO connection will be simplified in this case.
Connections for various DBMS used in Laravel inherit the class Illuminate \ Database \ Connection. It is this class that encapsulates in itself all the possibilities for working with databases that are used in Laravel, in particular in ORM Eloquent. In the heirs classes (for each type of DBMS), methods are implemented that return an instance of the class with a description of the grammar, which is required when building DML queries, and a grammar instance for DDL queries (used in migration), as well as helpers for adding the schema name to the table. This class will look like this:
The method to return an instance of the Doctrine driver is formally required, so we enter it, but I didn’t have a goal to work with Doctrine (only with Eloquent), so I did not implement it. If you wish, you can do it yourself.
The postprocessor Illuminate \ Database \ Query \ Processors \ FirebirdProcessor is intended for additional processing of query results, in particular, it helps to extract the record identifier from an INSERT query. The definition of its implementation is completely copied from Illuminate \ Database \ Query \ Processors \ PostgresProcessor.
Now we come to the most interesting and important, namely to the description of the grammar for the construction of DML queries. It is this class that is responsible for converting the expression written for the Laravel query builder to the SQL dialect used in your DBMS. Knowing what a particular construct does in one DBMS, you can easily write this construct for Firebird. In fact, the DML part of the SQL language is fairly standard and not significantly different for different DBMS, at least in terms of the queries that can be built using Laravel.
Grammar DML queries are inherited from the class Illuminate \ Database \ Query \ Grammars \ Grammar. The protected $ selectComponents property lists the parts of the SELECT query from which the query is built by the \ Illuminate \ Database \ Query \ Builder builder. In the compileComponents method, these parts are traversed and for each of them a method is called with the name, which consists of the name of the query part and the prefix compile.
Knowing this fact, it becomes clear what needs to be modified and where. Now it is time to create a heir to the class Illuminate \ Database \ Query \ Grammars \ Grammar to define the grammar of Firebird - Illuminate \ Database \ Query \ Grammars \ FirebirdGrammar. We define the main distinctive features of Firebird.
For a simple INSERT query, the main difference is the ability to return the newly added string using the RETURNING clause. In Laravel, this is used to return the identifier of the newly added string. However, MySQL does not have this capability, and therefore the compileInsertGetId method looks different for different DBMS. Firebird supports the RETURNING clause, as well as the Postgres DBMS, and therefore this method can be taken from the grammar for Postgres. It will look like this:
Perhaps the most basic difference for a SELECT query is the limit on the number of records returned by the query, which is often used in paged navigation. For example, the following expression:
In different DBMS will look in SQL language very differently. In MySQL, it looks like this:
in postgres like this:
There are three options in Firebird. Starting with version 1.5:
Starting with version 2.0, another construction is added:
Starting with version 3.0, a construction from the SQL-2011 standard has been added:
The most convenient and correct option, of course, is the option from the standard, but I wanted Laravel to support Firebird 2.5 and 3.0 at the same time, so we’ll choose the second option. In this case, the compileLimit and compileOffset methods will look like this:
The next thing that distinguishes requests is the extraction of parts of the date, this is done using the dateBasedWhere method. Firebird uses the standard EXTRACT function for this. Taking this into account, our method will look as follows:
That's all, all the main distinctive features were taken into account. You can find the fully implemented class Illuminate \ Database \ Query \ Grammars \ FirebirdGrammar in the source codes attached to the article.
We now turn to the more complex grammar used in the construction of DB schemas. This grammar is used for so-called migrations (in Laravel terms). Here, the differences between different DBMSs are much larger, starting from data types, ending with auto-increment fields. In addition, you will need to rewrite queries here to a number of system tables to determine if there is a table or column.
Grammar DDL queries are inherited from the class Illuminate \ Database \ Schema \ Grammars \ Grammar. Create your grammar Illuminate \ Database \ Schema \ Grammars \ FirebirdGrammar. The protected $ modifiers property lists the table field modifiers, for each of the modifiers listed in the array there should be a method that starts with modify, followed by the name of the modifier. We build these methods by analogy with the grammar for MySQL, but taking into account the specifics of Firebird.
The $ serials array lists the types (available in Laravel) for which the Increment modifier (auto-increment column) is available. On the types available in Laravel worth staying separately. The types available in Laravel are listed in the migration documentation in the “Available column types” section. To convert the type available in Laravel to the data type of a specific DBMS within a grammar, methods are used that begin with the word type, followed by the type name.
Now we add two methods that the query returns to check for the existence of a table and a column inside the table.
Add the compileCreate method for creating the CREATE TABLE statement. The same method is used to create temporary GTT tables. Strangely enough, even for Postgres, only one type of GTT is created - ON COMMIT DELETE ROWS, we also implement support for both types of GTT at once.
The class Illuminate \ Database \ Schema \ Blueprint does not contain the $ preserve property, so we will add it, as well as a method for installing it. The Blueprint class is designed to generate a query or set of queries to support the creation, modification, and deletion of table metadata.
Let us return to the grammar class Illuminate \ Database \ Schema \ Grammars \ FirebirdGrammar. Add a method to it to create a table deletion operator.
There is another method in Laravel that only tries to delete a table if it exists. This is done using the SQL statement DROP TABLE IF EXISTS. Firebird does not have an operator with similar functionality, however we can emulate it using an anonymous block (EXECUTE BLOCK + EXECUTE STATEMENT).
Now add methods to add and remove columns, constraints, and indexes.
You can test the performance of our classes by creating and running a migration with the following contents:
As a result, run with the command:
DDL:
:
Blueprint, , , SequenceBlueprint . , , Blueprint.
Firebird : , , NEXT VALUE FOR. 3.0. , :
, . restart, RESTART ALTER SEQUENCE.
Blueprint create drop, alter sequence.
Illuminate\Database\Schema\SequenceBlueprint .
Illuminate\Database\Schema\Grammars\ FirebirdGrammar {CREATE | ALTER | DROP} SEQUENCE.
Well, now the addition of support for sequences in the Laravel migration is complete. Let's see how it works, for this we slightly modify the migration given earlier.
You can go further and make support for creating a BEFORE INSERT trigger and a sequence (generator) to support auto-increment fields in Firebird 2.5 and below. But in most cases it is enough to get the next value of the sequence and pass it to the INSERT request.
These changes are sufficient for developing web applications using the Firebird DBMS using the Laravel framework. Of course, it would be nice to arrange it as a package and connect it to extend the functionality, as it is done with the rest of the modules Laravel.
In the next article I will discuss how to create a small application using Laravel and Firebird DBMS. Firebird Laravel , . Laravel Firebird .
Laravel is a free, open source web framework designed for development using the MVC architecture model (Model View Controller - Model View Controller). Laravel is a convenient and easily extensible framework for building your web applications. Out of the box, the Laravel framework supports 4 DBMSs: MySQL, Postgres, SQLite, and MS SQL Server. In this article I will tell how to add another Firebird DBMS.
FirebirdConnection Connection Class
Each time you connect to a database, a specific connection instance is created using the Illuminate \ Database \ Connectors \ ConnectionFactory factory, depending on the type of DBMS that implements the Illuminate \ Database \ ConnectionInterface. In addition, the factory creates a certain connector that, based on the configuration parameters, forms a connection string and passes it to the PDO connection designer. The connector is created in the createConnector method. We modify it a little so that it can create a connector for Firebird.
public function createConnector(array $config) { if (! isset($config['driver'])) { throw new InvalidArgumentException('A driver must be specified.'); } if ($this->container->bound($key = "db.connector.{$config['driver']}")) { return $this->container->make($key); } switch ($config['driver']) { case 'mysql': return new MySqlConnector; case 'pgsql': return new PostgresConnector; case 'sqlite': return new SQLiteConnector; case 'sqlsrv': return new SqlServerConnector; case 'firebird': // Add support Firebird return new FirebirdConnector; } throw new InvalidArgumentException("Unsupported driver [{$config['driver']}]"); } We define the Illuminate \ Database \ Connectors \ FirebirdConnector connector class a bit later. In the meantime, we modify the createConnection method intended to create a connection that implements the Illuminate \ Database \ ConnectionInterface interface.
')
protected function createConnection($driver, $connection, $database, $prefix = '', array $config = []) { if ($this->container->bound($key = "db.connection.{$driver}")) { return $this->container->make($key, [$connection, $database, $prefix, $config]); } switch ($driver) { case 'mysql': return new MySqlConnection($connection, $database, $prefix, $config); case 'pgsql': return new PostgresConnection($connection, $database, $prefix, $config); case 'sqlite': return new SQLiteConnection($connection, $database, $prefix, $config); case 'sqlsrv': return new SqlServerConnection($connection, $database, $prefix, $config); case 'firebird': // Add support Firebird return new FirebirdConnection($connection, $database, $prefix, $config); } throw new InvalidArgumentException("Unsupported driver [$driver]"); } Now let's move on to creating a connector for Firebird - class Illuminate \ Database \ Connectors \ FirebirdConnector. You can take any of the existing connectors as a basis, for example, a Postgres connector and remake it under Firebird.
To begin with, we will change the method for generating the connection string in accordance with the format of the string described in the documentation :
protected function getDsn(array $config) { $dsn = "firebird:dbname="; if (isset($config['host'])) { $dsn .= $config['host']; } if (isset($config['port'])) { $dsn .= "/" . $config['port']; } $dsn .= ":" . $config['database']; if (isset($config['charset'])) { $dsn .= ";charset=" . $config['charset']; } if (isset($config['role'])) { $dsn .= ";role=" . $config['role']; } return $dsn; } The method that creates a PDO connection will be simplified in this case.
public function connect(array $config) { $dsn = $this->getDsn($config); $options = $this->getOptions($config); // We need to grab the PDO options that should be used while making the brand // new connection instance. The PDO options control various aspects of the // connection's behavior, and some might be specified by the developers. $connection = $this->createConnection($dsn, $config, $options); return $connection; } Connections for various DBMS used in Laravel inherit the class Illuminate \ Database \ Connection. It is this class that encapsulates in itself all the possibilities for working with databases that are used in Laravel, in particular in ORM Eloquent. In the heirs classes (for each type of DBMS), methods are implemented that return an instance of the class with a description of the grammar, which is required when building DML queries, and a grammar instance for DDL queries (used in migration), as well as helpers for adding the schema name to the table. This class will look like this:
namespace Illuminate\Database; use Illuminate\Database\Query\Processors\FirebirdProcessor; use Doctrine\DBAL\Driver\PDOFirebird\Driver as DoctrineDriver; use Illuminate\Database\Query\Grammars\FirebirdGrammar as QueryGrammar; use Illuminate\Database\Schema\Grammars\FirebirdGrammar as SchemaGrammar; class FirebirdConnection extends Connection { /** * Get the default query grammar instance. * * @return \Illuminate\Database\Query\Grammars\FirebirdGrammar */ protected function getDefaultQueryGrammar() { return $this->withTablePrefix(new QueryGrammar); } /** * Get the default schema grammar instance. * * @return \Illuminate\Database\Schema\Grammars\FirebirdGrammar */ protected function getDefaultSchemaGrammar() { return $this->withTablePrefix(new SchemaGrammar); } /** * Get the default post processor instance. * * @return \Illuminate\Database\Query\Processors\FirebirdProcessor */ protected function getDefaultPostProcessor() { return new FirebirdProcessor; } /** * Get the Doctrine DBAL driver. * * @return \Doctrine\DBAL\Driver\PDOFirebird\Driver */ protected function getDoctrineDriver() { return new DoctrineDriver; } } The method to return an instance of the Doctrine driver is formally required, so we enter it, but I didn’t have a goal to work with Doctrine (only with Eloquent), so I did not implement it. If you wish, you can do it yourself.
The postprocessor Illuminate \ Database \ Query \ Processors \ FirebirdProcessor is intended for additional processing of query results, in particular, it helps to extract the record identifier from an INSERT query. The definition of its implementation is completely copied from Illuminate \ Database \ Query \ Processors \ PostgresProcessor.
Grammar class for building DML queries
Now we come to the most interesting and important, namely to the description of the grammar for the construction of DML queries. It is this class that is responsible for converting the expression written for the Laravel query builder to the SQL dialect used in your DBMS. Knowing what a particular construct does in one DBMS, you can easily write this construct for Firebird. In fact, the DML part of the SQL language is fairly standard and not significantly different for different DBMS, at least in terms of the queries that can be built using Laravel.
Grammar DML queries are inherited from the class Illuminate \ Database \ Query \ Grammars \ Grammar. The protected $ selectComponents property lists the parts of the SELECT query from which the query is built by the \ Illuminate \ Database \ Query \ Builder builder. In the compileComponents method, these parts are traversed and for each of them a method is called with the name, which consists of the name of the query part and the prefix compile.
/** * Compile the components necessary for a select clause. * * @param \Illuminate\Database\Query\Builder $query * @return array */ protected function compileComponents(Builder $query) { $sql = []; foreach ($this->selectComponents as $component) { // To compile the query, we'll spin through each component of the query and // see if that component exists. If it does we'll just call the compiler // function for the component which is responsible for making the SQL. if (! is_null($query->$component)) { $method = 'compile'.ucfirst($component); $sql[$component] = $this->$method($query, $query->$component); } } return $sql; } Knowing this fact, it becomes clear what needs to be modified and where. Now it is time to create a heir to the class Illuminate \ Database \ Query \ Grammars \ Grammar to define the grammar of Firebird - Illuminate \ Database \ Query \ Grammars \ FirebirdGrammar. We define the main distinctive features of Firebird.
For a simple INSERT query, the main difference is the ability to return the newly added string using the RETURNING clause. In Laravel, this is used to return the identifier of the newly added string. However, MySQL does not have this capability, and therefore the compileInsertGetId method looks different for different DBMS. Firebird supports the RETURNING clause, as well as the Postgres DBMS, and therefore this method can be taken from the grammar for Postgres. It will look like this:
/** * 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); } Perhaps the most basic difference for a SELECT query is the limit on the number of records returned by the query, which is often used in paged navigation. For example, the following expression:
DB::table('goods')->orderBy('name')->skip(10)->take(20)->get(); In different DBMS will look in SQL language very differently. In MySQL, it looks like this:
SELECT * FROM goods ORDER BY name LIMIT 10, 20 in postgres like this:
SELECT * FROM goods ORDER BY name LIMIT 20 OFFSET 10 There are three options in Firebird. Starting with version 1.5:
SELECT FIRST(10) SKIP(20) * FROM goods ORDER BY name Starting with version 2.0, another construction is added:
SELECT * FROM goods ORDER BY name ROWS 21, 30 Starting with version 3.0, a construction from the SQL-2011 standard has been added:
SELECT * FROM color ORDER BY name OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY The most convenient and correct option, of course, is the option from the standard, but I wanted Laravel to support Firebird 2.5 and 3.0 at the same time, so we’ll choose the second option. In this case, the compileLimit and compileOffset methods will look like this:
/** * Compile the "limit" portions of the query. * * @param \Illuminate\Database\Query\Builder $query * @param int $limit * @return string */ protected function compileLimit(Builder $query, $limit) { if ($query->offset) { $first = (int) $query->offset + 1; return 'rows ' . (int) $first; } else { return 'rows ' . (int) $limit; } } /** * Compile the "offset" portions of the query. * * @param \Illuminate\Database\Query\Builder $query * @param int $offset * @return string */ protected function compileOffset(Builder $query, $offset) { if ($query->limit) { if ($offset) { $end = (int) $query->limit + (int) $offset; return 'to ' . $end; } else { return ''; } } else { $begin = (int) $offset + 1; return 'rows ' . $begin . ' to 2147483647'; } } The next thing that distinguishes requests is the extraction of parts of the date, this is done using the dateBasedWhere method. Firebird uses the standard EXTRACT function for this. Taking this into account, our method will look as follows:
/** * Compile a date based where clause. * * @param string $type * @param \Illuminate\Database\Query\Builder $query * @param array $where * @return string */ protected function dateBasedWhere($type, Builder $query, $where) { $value = $this->parameter($where['value']); return 'extract(' . $type . ' from ' . $this->wrap($where['column']) . ') ' . $where['operator'] . ' ' . $value; } That's all, all the main distinctive features were taken into account. You can find the fully implemented class Illuminate \ Database \ Query \ Grammars \ FirebirdGrammar in the source codes attached to the article.
Grammar class for constructing DDL queries
We now turn to the more complex grammar used in the construction of DB schemas. This grammar is used for so-called migrations (in Laravel terms). Here, the differences between different DBMSs are much larger, starting from data types, ending with auto-increment fields. In addition, you will need to rewrite queries here to a number of system tables to determine if there is a table or column.
Grammar DDL queries are inherited from the class Illuminate \ Database \ Schema \ Grammars \ Grammar. Create your grammar Illuminate \ Database \ Schema \ Grammars \ FirebirdGrammar. The protected $ modifiers property lists the table field modifiers, for each of the modifiers listed in the array there should be a method that starts with modify, followed by the name of the modifier. We build these methods by analogy with the grammar for MySQL, but taking into account the specifics of Firebird.
Column modifier support
class FirebirdGrammar extends Grammar { /** * The possible column modifiers. * * @var array */ protected $modifiers = ['Charset', 'Collate', 'Increment', 'Nullable', 'Default']; /** * The columns available as serials. * * @var array */ protected $serials = ['bigInteger', 'integer', 'mediumInteger', 'smallInteger', 'tinyInteger']; // …………………… ……………… /** * Get the SQL for a character set column modifier. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $column * @return string|null */ protected function modifyCharset(Blueprint $blueprint, Fluent $column) { if (! is_null($column->charset)) { return ' character set '.$column->charset; } } /** * Get the SQL for a collation column modifier. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $column * @return string|null */ protected function modifyCollate(Blueprint $blueprint, Fluent $column) { if (! is_null($column->collation)) { return ' collate '.$column->collation; } } /** * Get the SQL for a nullable column modifier. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $column * @return string|null */ protected function modifyNullable(Blueprint $blueprint, Fluent $column) { return $column->nullable ? '' : ' not null'; } /** * Get the SQL for a default column modifier. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $column * @return string|null */ protected function modifyDefault(Blueprint $blueprint, Fluent $column) { if (!is_null($column->default)) { return ' default ' . $this->getDefaultValue($column->default); } } /** * Get the SQL for an auto-increment column modifier. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $column * @return string|null */ protected function modifyIncrement(Blueprint $blueprint, Fluent $column) { if (in_array($column->type, $this->serials) && $column->autoIncrement) { return ' primary key'; } } // …………………… ……………… } The $ serials array lists the types (available in Laravel) for which the Increment modifier (auto-increment column) is available. On the types available in Laravel worth staying separately. The types available in Laravel are listed in the migration documentation in the “Available column types” section. To convert the type available in Laravel to the data type of a specific DBMS within a grammar, methods are used that begin with the word type, followed by the type name.
Data Type Support
/** * Create the column definition for a char type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeChar(Fluent $column) { return "char({$column->length})"; } /** * Create the column definition for a string type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeString(Fluent $column) { return "varchar({$column->length})"; } /** * Create the column definition for a text type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeText(Fluent $column) { return 'BLOB SUB_TYPE TEXT'; } /** * Create the column definition for a medium text type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeMediumText(Fluent $column) { return 'BLOB SUB_TYPE TEXT'; } /** * Create the column definition for a long text type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeLongText(Fluent $column) { return 'BLOB SUB_TYPE TEXT'; } /** * Create the column definition for a integer type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeInteger(Fluent $column) { return $column->autoIncrement ? 'INTEGER GENERATED BY DEFAULT AS IDENTITY' : 'INTEGER'; } /** * Create the column definition for a big integer type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeBigInteger(Fluent $column) { return $column->autoIncrement ? 'BIGINT GENERATED BY DEFAULT AS IDENTITY' : 'BIGINT'; } /** * Create the column definition for a medium integer type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeMediumInteger(Fluent $column) { return $column->autoIncrement ? 'INTEGER GENERATED BY DEFAULT AS IDENTITY' : 'INTEGER'; } /** * Create the column definition for a tiny integer type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeTinyInteger(Fluent $column) { return $column->autoIncrement ? 'SMALLINT GENERATED BY DEFAULT AS IDENTITY' : 'SMALLINT'; } /** * Create the column definition for a small integer type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeSmallInteger(Fluent $column) { return $column->autoIncrement ? 'SMALLINT GENERATED BY DEFAULT AS IDENTITY' : 'SMALLINT'; } /** * Create the column definition for a float type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeFloat(Fluent $column) { return $this->typeDouble($column); } /** * Create the column definition for a double type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeDouble(Fluent $column) { return 'double precision'; } /** * Create the column definition for a decimal type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeDecimal(Fluent $column) { return "decimal({$column->total}, {$column->places})"; } /** * Create the column definition for a boolean type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeBoolean(Fluent $column) { return 'boolean'; } /** * Create the column definition for an enum type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeEnum(Fluent $column) { $allowed = array_map(function ($a) { return "'" . $a . "'"; }, $column->allowed); return "varchar(255) check (\"{$column->name}\" in (" . implode(', ', $allowed) . '))'; } /** * Create the column definition for a json type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeJson(Fluent $column) { return 'varchar(8191)'; } /** * Create the column definition for a jsonb type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeJsonb(Fluent $column) { return 'varchar(8191)'; } /** * Create the column definition for a date type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeDate(Fluent $column) { return 'date'; } /** * Create the column definition for a date-time type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeDateTime(Fluent $column) { return 'timestamp'; } /** * Create the column definition for a date-time type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeDateTimeTz(Fluent $column) { return 'timestamp'; } /** * Create the column definition for a time type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeTime(Fluent $column) { return 'time'; } /** * Create the column definition for a time type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeTimeTz(Fluent $column) { return 'time'; } /** * Create the column definition for a timestamp type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeTimestamp(Fluent $column) { if ($column->useCurrent) { return 'timestamp default CURRENT_TIMESTAMP'; } return 'timestamp'; } /** * Create the column definition for a timestamp type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeTimestampTz(Fluent $column) { if ($column->useCurrent) { return 'timestamp default CURRENT_TIMESTAMP'; } return 'timestamp'; } /** * Create the column definition for a binary type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeBinary(Fluent $column) { return 'varchar(8191) CHARACTER SET OCTETS'; } /** * Create the column definition for a uuid type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeUuid(Fluent $column) { return 'char(36)'; } /** * Create the column definition for an IP address type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeIpAddress(Fluent $column) { return 'varchar(45)'; } /** * Create the column definition for a MAC address type. * * @param \Illuminate\Support\Fluent $column * @return string */ protected function typeMacAddress(Fluent $column) { return 'varchar(17)'; } Note on auto-increment columnsIdentity columns are available starting with Firebird 3.0. Prior to version 3.0, a generator and a BEFORE INSERT trigger were used for similar functionality. Example: Similar functionality can be obtained as follows: Laravel Migrations do not support schema objects other than tables. Those. creation and modification of sequences, and even more so triggers are not supported. However, sequences are part of the functionality of a fairly large number of DBMS, including Postgres and MS SQL (starting from 2012). How to add support for sequences in the Laravel migration will be described later in this article. |
Now we add two methods that the query returns to check for the existence of a table and a column inside the table.
/** * Compile the query to determine if a table exists. * * @return string */ public function compileTableExists() { return 'select * from RDB$RELATIONS where RDB$RELATION_NAME = ?'; } /** * Compile the query to determine the list of columns. * * @param string $table * @return string */ public function compileColumnExists($table) { return "select TRIM(RDB\$FIELD_NAME) AS \"column_name\" from RDB\$RELATION_FIELDS where RDB\$RELATION_NAME = '$table'"; } Add the compileCreate method for creating the CREATE TABLE statement. The same method is used to create temporary GTT tables. Strangely enough, even for Postgres, only one type of GTT is created - ON COMMIT DELETE ROWS, we also implement support for both types of GTT at once.
/** * Compile a create table command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileCreate(Blueprint $blueprint, Fluent $command) { $columns = implode(', ', $this->getColumns($blueprint)); $sql = $blueprint->temporary ? 'create temporary' : 'create'; $sql .= ' table ' . $this->wrapTable($blueprint) . " ($columns)"; if ($blueprint->temporary) { if ($blueprint->preserve) { $sql .= ' ON COMMIT DELETE ROWS'; } else { $sql .= ' ON COMMIT PRESERVE ROWS'; } } return $sql; } The class Illuminate \ Database \ Schema \ Blueprint does not contain the $ preserve property, so we will add it, as well as a method for installing it. The Blueprint class is designed to generate a query or set of queries to support the creation, modification, and deletion of table metadata.
class Blueprint { // …………… /** * Whether a temporary table such as ON COMMIT PRESERVE ROWS * * @var bool */ public $preserve = false; // …………… /** * Indicate that the temporary table as ON COMMIT PRESERVE ROWS. * * @return void */ public function preserveRows() { $this->preserve = true; } // …………… } Let us return to the grammar class Illuminate \ Database \ Schema \ Grammars \ FirebirdGrammar. Add a method to it to create a table deletion operator.
/** * Compile a drop table command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileDrop(Blueprint $blueprint, Fluent $command) { return 'drop table ' . $this->wrapTable($blueprint); } There is another method in Laravel that only tries to delete a table if it exists. This is done using the SQL statement DROP TABLE IF EXISTS. Firebird does not have an operator with similar functionality, however we can emulate it using an anonymous block (EXECUTE BLOCK + EXECUTE STATEMENT).
/** * Compile a drop table (if exists) command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileDropIfExists(Blueprint $blueprint, Fluent $command) { $sql = 'EXECUTE BLOCK' . "\n"; $sql .= 'AS' . "\n"; $sql .= 'BEGIN' . "\n"; $sql .= " IF (EXISTS(select * from RDB\$RELATIONS where RDB\$RELATION_NAME = '" . $blueprint->getTable() . "')) THEN" . "\n"; $sql .= " EXECUTE STATEMENT 'DROP TABLE " . $this->wrapTable($blueprint) . "';" . "\n"; $sql .= 'END'; return $sql; } Now add methods to add and remove columns, constraints, and indexes.
Methods for adding and removing columns, constraints, and indexes
/** * Compile a column addition command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileAdd(Blueprint $blueprint, Fluent $command) { $table = $this->wrapTable($blueprint); $columns = $this->prefixArray('add column', $this->getColumns($blueprint)); return 'alter table ' . $table . ' ' . implode(', ', $columns); } /** * Compile a primary key command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compilePrimary(Blueprint $blueprint, Fluent $command) { $columns = $this->columnize($command->columns); return 'alter table ' . $this->wrapTable($blueprint) . " add primary key ({$columns})"; } /** * Compile a unique key command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileUnique(Blueprint $blueprint, Fluent $command) { $table = $this->wrapTable($blueprint); $index = $this->wrap($command->index); $columns = $this->columnize($command->columns); return "alter table $table add constraint {$index} unique ($columns)"; } /** * Compile a plain index key command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileIndex(Blueprint $blueprint, Fluent $command) { $columns = $this->columnize($command->columns); $index = $this->wrap($command->index); return "create index {$index} on " . $this->wrapTable($blueprint) . " ({$columns})"; } /** * Compile a drop column command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileDropColumn(Blueprint $blueprint, Fluent $command) { $columns = $this->prefixArray('drop column', $this->wrapArray($command->columns)); $table = $this->wrapTable($blueprint); return 'alter table ' . $table . ' ' . implode(', ', $columns); } /** * Compile a drop primary key command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileDropPrimary(Blueprint $blueprint, Fluent $command) { $table = $blueprint->getTable(); $index = $this->wrap("{$table}_pkey"); return 'alter table ' . $this->wrapTable($blueprint) . " drop constraint {$index}"; } /** * Compile a drop unique key command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileDropUnique(Blueprint $blueprint, Fluent $command) { $table = $this->wrapTable($blueprint); $index = $this->wrap($command->index); return "alter table {$table} drop constraint {$index}"; } /** * Compile a drop index command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileDropIndex(Blueprint $blueprint, Fluent $command) { $index = $this->wrap($command->index); return "drop index {$index}"; } /** * Compile a drop foreign key command. * * @param \Illuminate\Database\Schema\Blueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileDropForeign(Blueprint $blueprint, Fluent $command) { $table = $this->wrapTable($blueprint); $index = $this->wrap($command->index); return "alter table {$table} drop constraint {$index}"; } You can test the performance of our classes by creating and running a migration with the following contents:
use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; class CreateUsersTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('users', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('email')->unique(); $table->string('password'); $table->rememberToken(); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::drop('users'); } } As a result, run with the command:
php artisan migrate DDL:
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"); :
php artisan migrate:reset Blueprint, , , SequenceBlueprint . , , Blueprint.
Firebird : , , NEXT VALUE FOR. 3.0. , :
/** * The sequence the blueprint describes. * * @var string */ protected $sequence; /** * Initial sequence value * * @var int */ protected $start_with = 0; /** * Increment for sequence * * @var int */ protected $increment = 1; /** * Restart flag that indicates that the sequence should be reset * * @var bool */ protected $restart = false; , . restart, RESTART ALTER SEQUENCE.
/** * Restart sequence and set initial value * * @param int $startWith */ public function restart($startWith = null) { $this->restart = true; $this->start_with = $startWith; } Blueprint create drop, alter sequence.
/** * Determine if the blueprint has a create command. * * @return bool */ protected function creating() { foreach ($this->commands as $command) { if ($command->name == 'createSequence') { return true; } } return false; } /** * Determine if the blueprint has a drop command. * * @return bool */ protected function dropping() { foreach ($this->commands as $command) { if ($command->name == 'dropSequence') { return true; } if ($command->name == 'dropSequenceIfExists') { return true; } } return false; } /** * Add the commands that are implied by the blueprint. * * @return void */ protected function addImpliedCommands() { if (($this->restart || ($this->increment !== 1)) && ! $this->creating() && ! $this->dropping()) { array_unshift($this->commands, $this->createCommand('alterSequence')); } } /** * Get the raw SQL statements for the blueprint. * * @param \Illuminate\Database\Connection $connection * @param \Illuminate\Database\Schema\Grammars\Grammar $grammar * @return array */ public function toSql(Connection $connection, Grammar $grammar) { $this->addImpliedCommands(); $statements = []; // Each type of command has a corresponding compiler function on the schema // grammar which is used to build the necessary SQL statements to build // the sequence blueprint element, so we'll just call that compilers function. foreach ($this->commands as $command) { $method = 'compile'.ucfirst($command->name); if (method_exists($grammar, $method)) { if (! is_null($sql = $grammar->$method($this, $command, $connection))) { $statements = array_merge($statements, (array) $sql); } } } return $statements; } Illuminate\Database\Schema\SequenceBlueprint .
Illuminate\Database\Schema\Grammars\ FirebirdGrammar {CREATE | ALTER | DROP} SEQUENCE.
{CREATE | ALTER | DROP} SEQUENCE
/** * Compile a create sequence command. * * @param \Illuminate\Database\Schema\SequenceBlueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileCreateSequence(SequenceBlueprint $blueprint, Fluent $command) { $sql = 'create sequence '; $sql .= $this->wrapSequence($blueprint); if ($blueprint->getInitialValue() !== 0) { $sql .= ' start with ' . $blueprint->getInitialValue(); } if ($blueprint->getIncrement() !== 1) { $sql .= ' increment by ' . $blueprint->getIncrement(); } return $sql; } /** * Compile a alter sequence command. * * @param \Illuminate\Database\Schema\SequenceBlueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileAlterSequence(SequenceBlueprint $blueprint, Fluent $command) { $sql = 'alter sequence '; $sql .= $this->wrapSequence($blueprint); if ($blueprint->isRestart()) { $sql .= ' restart'; if ($blueprint->getInitialValue() !== null) { $sql .= ' with ' . $blueprint->getInitialValue(); } } if ($blueprint->getIncrement() !== 1) { $sql .= ' increment by ' . $blueprint->getIncrement(); } return $sql; } /** * Compile a drop sequence command. * * @param \Illuminate\Database\Schema\SequenceBlueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileDropSequence(SequenceBlueprint $blueprint, Fluent $command) { return 'drop sequence ' . $this->wrapSequence($blueprint); } /** * Compile a drop sequence command. * * @param \Illuminate\Database\Schema\SequenceBlueprint $blueprint * @param \Illuminate\Support\Fluent $command * @return string */ public function compileDropSequenceIfExists(SequenceBlueprint $blueprint, Fluent $command) { $sql = 'EXECUTE BLOCK' . "\n"; $sql .= 'AS' . "\n"; $sql .= 'BEGIN' . "\n"; $sql .= " IF (EXISTS(select * from RDB\$GENERATORS where RDB\$GENERATOR_NAME = '" . $blueprint->getSequence() . "')) THEN" . "\n"; $sql .= " EXECUTE STATEMENT 'DROP SEQUENCE " . $this->wrapSequence($blueprint) . "';" . "\n"; $sql .= 'END'; return $sql; } /** * Wrap a sequence in keyword identifiers. * * @param mixed $sequence * @return string */ public function wrapSequence($sequence) { if ($sequence instanceof SequenceBlueprint) { $sequence = $sequence->getSequence(); } if ($this->isExpression($sequence)) { return $this->getValue($sequence); } return $this->wrap($this->tablePrefix . $sequence, true); } Well, now the addition of support for sequences in the Laravel migration is complete. Let's see how it works, for this we slightly modify the migration given earlier.
Migration to test DDL over sequences
class CreateUsersTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { // // CREATE SEQUENCE "seq_users_id" Schema::createSequence('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"); Schema::create('users', function (Blueprint $table) { //$table->increments('id'); $table->integer('id')->primary(); $table->string('name'); $table->string('email')->unique(); $table->string('password'); $table->rememberToken(); $table->timestamps(); }); // // ALTER SEQUENCE "seq_users_id" RESTART WITH 10 INCREMENT BY 5 Schema::sequence('seq_users_id', function (SequenceBlueprint $sequence) { $sequence->increment(5); $sequence->restart(10); }); } /** * Reverse the migrations. * * @return void */ public function down() { // // DROP SEQUENCE "seq_users_id" Schema::dropSequence('seq_users_id'); // // DROP TABLE "users" Schema::drop('users'); } } You can go further and make support for creating a BEFORE INSERT trigger and a sequence (generator) to support auto-increment fields in Firebird 2.5 and below. But in most cases it is enough to get the next value of the sequence and pass it to the INSERT request.
Conclusion
These changes are sufficient for developing web applications using the Firebird DBMS using the Laravel framework. Of course, it would be nice to arrange it as a package and connect it to extend the functionality, as it is done with the rest of the modules Laravel.
In the next article I will discuss how to create a small application using Laravel and Firebird DBMS. Firebird Laravel , . Laravel Firebird .
Commentgithub.com/jacquestvanzuydam/laravel-firebird , Laravel. ellrion . |
Source: https://habr.com/ru/post/311446/
All Articles