Access to the Firebird database from YII2
It so happened that I needed to transfer the user interface from the Delphi + Firebird bundle to the WEB. In order not to stop the work of the program complex, it was decided to transfer the forms gradually, and for this it is necessary to save the work of the Firebird DBMS and connect to it from the web application. When developing a web application, I decided to use the framework YII2. At the development stage, I do not need a full-fledged HTTP server, Denwer or XAMMP, or something else will do. I chose XAMMP because in Denver, the PHP version has not been updated for a long time, and we don’t want to update it with our hands; Initially, neither in XAMMP PHP nor in YII2 is it possible to work with Firebird DBMS, but with certain tambourine dances, this is quite realistic. So, I will assume that you have already installed: Windows 7 x64 , XAMMP and YII2, Firebird 2.5.
Ways to install packages for me (for yourself, make the appropriate adjustments):
Now you can start.
To install Firebird support, you must stop the Apache server in XAMMP. To do this, open the XAMMP console and check that there would be a “Start” button in front of the Apache service, i.e. the service is not running, otherwise click "Stop".
')
Open the PHP D: \ xampp \ php \ php.ini configuration file with any editor and find the line in it:
Remove the ";" at the beginning of i. uncomment and save. The PDO driver used by YII2 to access the DBMS is enabled. However, it will not work so easily, now you need to copy the standard library of access to the DBMS fbclient.dll . Here it is necessary to be attentive because we have Windows x64 and Firebird x64 server installed, and XAMMP is a 32-bit application. Accordingly, the fbclient.dll library must be compiled for the version of the client (in this case, XAMMP, more precisely, PHP as part of XAMMP), which is used to access the database. The 32-bit version of the library is located in the WOW64 directory of the Firebird installation directory. take the file C: \ Program Files \ Firebird \ Firebird_2_5 \ WOW64 \ fbclient.dll and copy to the following paths:
Some sources advise you to copy it to D: \ xampp \ apache \ bin \ , but in my case it didn’t affect anything. And, in theory, could not affect it. Apache server starts PHP and it doesn’t need any additional access libraries.
I also recommend checking the availability of the PDO library for access to Firebird, file D: \ xampp \ php \ ext \ php_pdo_firebird.dll .
Start the Apache server (see figure above), click “Start” in the XAMMP console. At the moment it’s nice to make sure that the PDO driver for Firebird appeared in the system, I spent the whole day until I realized that the driver is not connected. The YII2 error handling system in this case turned out to be a weak assistant. reported another error elsewhere. The list of tables in the Model Generator gii was not loaded, and before that there was an error installing the extension for accessing Firebird, so it was not obvious to associate the error with the non-connected PDO driver.
Create a file test-fb.php in the working directory of the framework YII2 D: \ xampp \ htdocs \ yii \ , in which we look for the code:
This code lists the PHP PDO drivers that are connected to PHP. We start the browser and type the URL localhost / yii / test-fb.php in the address bar, in response we get a page with a list of connected drivers.
Be sure to make sure that the firebird driver is in the list. MySQL and SQLite drivers are always connected by default.
For to connect to Firebird in YII2 there are two extensions.
I will describe the installation of both extensions in sequence. I'll start with the second extension of yii2-firebird . it has the status of stable .
Open the file D: \ xampp \ htdocs \ yii \ composer.json and find in it a piece of code:
Add to the end a comma-separated line "edgardmessias / yii2-firebird": "*" , we get:
Please note that there should not be a comma after the added string. it is added to the end of the array, and the presence of a comma when executing Composer will give an error. Although PHP itself allows a comma at the end of the list of array elements before the closing bracket, Composer does not allow this.
So, we told Composer to download the yii2-firebird extension . We start the command line, make the current directory D: \ xampp \ htdocs \ yii \ and give two consecutive commands:
Carefully study the output of Composer for errors, check the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ edgardmessias \ yii2-firebird \ . The extension is installed.
Configure access to the database. The yii2-firebird documentation says that you need to change the configuration. If we carefully examine the D: \ xampp \ htdocs \ yii \ config \ web.php configuration file , we find the line (approximately line 40):
from which it becomes clear that editing the database connection settings is necessary in the file D: \ xampp \ htdocs \ yii \ config \ db.php . Open it and bring it to the form:
I want to draw attention to some points:
As I indicated above, yii2-firebirddb has the status dev , therefore there are some minor installation features for this extension.
Now in the file D: \ xampp \ htdocs \ yii \ composer.json in the section "require" we add the boot path of the extension yii2-firebirddb
We told Composer to load the yii2-firebirddb extension . We start the command line, make the current directory D: \ xampp \ htdocs \ yii \ and give two consecutive commands:
We check for the absence of errors and the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ srusakov \ firebirddb \ . The extension is installed.
The documentation for yii2-firebirddb says that you still need to register the path to the repository for downloading the extension. In my case, this, in addition to an error, did nothing, but just in case I will describe how to do it.
First install GIT for Windows . You can download the link https://git-scm.com/download/win . Restart the command line console so that the PATH environment variable is updated. Check on the command line what the command would pass
In response, a prompt for using the utility on the command line should appear.
In the file D: \ xampp \ htdocs \ yii \ composer.json after the closing bracket of the "require" section we write the path to the repository, it turns out:
Pay attention to the last 4 lines. Next, we run the command line as standard, make the current directory D: \ xampp \ htdocs \ yii \ and give two commands in sequence:
We check for the absence of errors and the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ srusakov \ firebirddb \ . The extension is installed.
Open the database connection configuration file D: \ xampp \ htdocs \ yii \ config \ db.php , make changes, we get:
The easiest way to verify the installation of extensions, drivers and parameters for connecting to the database is to connect. Run the gii utility from YII2 and open the Model Generator . Open link gii http: // localhost / yii / web /? R = gii , select the "Start" button in the Model Generator section, or direct link http: //localhost/yii/web/index.php? R = gii / default / view & id = model
If the Model Generator opens without errors, then everything is installed and works correctly, since Model Generator loads the list of database tables. connects to firebird. If you get a page loading error, most likely you do not have a single table in the database and the list of tables is returned empty. Create at least one table and try again. Also check the correctness of the paths to the database in the configuration (file D: \ xampp \ htdocs \ yii \ config \ db.php ).
Ways to install packages for me (for yourself, make the appropriate adjustments):
- Firebird 2.5 x64 version (server type does not matter) in C: \ Program Files \ Firebird \ Firebird_2_5 \
- XAMMP is installed in D: \ xampp \
- YII2 is installed in D: \ xampp \ htdocs \ yii \
- The database is located on the D: \ WORK \ database \ TBOT.GDB path.
Now you can start.
Installing Firebird Support for PHP
To install Firebird support, you must stop the Apache server in XAMMP. To do this, open the XAMMP console and check that there would be a “Start” button in front of the Apache service, i.e. the service is not running, otherwise click "Stop".
')
Open the PHP D: \ xampp \ php \ php.ini configuration file with any editor and find the line in it:
;extension=php_pdo_firebird.dll Remove the ";" at the beginning of i. uncomment and save. The PDO driver used by YII2 to access the DBMS is enabled. However, it will not work so easily, now you need to copy the standard library of access to the DBMS fbclient.dll . Here it is necessary to be attentive because we have Windows x64 and Firebird x64 server installed, and XAMMP is a 32-bit application. Accordingly, the fbclient.dll library must be compiled for the version of the client (in this case, XAMMP, more precisely, PHP as part of XAMMP), which is used to access the database. The 32-bit version of the library is located in the WOW64 directory of the Firebird installation directory. take the file C: \ Program Files \ Firebird \ Firebird_2_5 \ WOW64 \ fbclient.dll and copy to the following paths:
- to the working directory PHP D: \ xampp \ php \
- in SYSTEM32 C: \ Windows \ System32 \ . In theory, copying here is not necessary, but I use the library in other projects and in order not to produce copies throughout the system, we copy it here.
Some sources advise you to copy it to D: \ xampp \ apache \ bin \ , but in my case it didn’t affect anything. And, in theory, could not affect it. Apache server starts PHP and it doesn’t need any additional access libraries.
I also recommend checking the availability of the PDO library for access to Firebird, file D: \ xampp \ php \ ext \ php_pdo_firebird.dll .
Start the Apache server (see figure above), click “Start” in the XAMMP console. At the moment it’s nice to make sure that the PDO driver for Firebird appeared in the system, I spent the whole day until I realized that the driver is not connected. The YII2 error handling system in this case turned out to be a weak assistant. reported another error elsewhere. The list of tables in the Model Generator gii was not loaded, and before that there was an error installing the extension for accessing Firebird, so it was not obvious to associate the error with the non-connected PDO driver.
Create a file test-fb.php in the working directory of the framework YII2 D: \ xampp \ htdocs \ yii \ , in which we look for the code:
<?php foreach (PDO::getAvailableDrivers() as $drv) { echo $drv."<br/>"; } ?> This code lists the PHP PDO drivers that are connected to PHP. We start the browser and type the URL localhost / yii / test-fb.php in the address bar, in response we get a page with a list of connected drivers.
firebird
mysql
sqlite
Be sure to make sure that the firebird driver is in the list. MySQL and SQLite drivers are always connected by default.
Installing the YII2 extensions to access Firebird
For to connect to Firebird in YII2 there are two extensions.
- yii2-firebirddb from Sergey Rusakov . Judging by the description is an adaptation of the extension from YII1. The disadvantage is that it is a dev build, not a stable one , so in the future you will have to change the "minimum-stability" in composer.json , which will lead to the downloading of unstable versions of other YII2 extensions when updating via Composer .
- yii2-firebird by Edgard Lorraine Messias . Judging by the source code of this extension and the same bugs, this is yii2-firebirddb from Sergey Rusakov , transferred from the dev branch to the stable branch.
I will describe the installation of both extensions in sequence. I'll start with the second extension of yii2-firebird . it has the status of stable .
Install yii2-firebird
Open the file D: \ xampp \ htdocs \ yii \ composer.json and find in it a piece of code:
... "require": { "php": ">=5.4.0", "yiisoft/yii2": ">=2.0.5", "yiisoft/yii2-bootstrap": "*", "yiisoft/yii2-swiftmailer": "*" ... Add to the end a comma-separated line "edgardmessias / yii2-firebird": "*" , we get:
... "require": { "php": ">=5.4.0", "yiisoft/yii2": ">=2.0.5", "yiisoft/yii2-bootstrap": "*", "yiisoft/yii2-swiftmailer": "*", "edgardmessias/yii2-firebird": "*" ... Please note that there should not be a comma after the added string. it is added to the end of the array, and the presence of a comma when executing Composer will give an error. Although PHP itself allows a comma at the end of the list of array elements before the closing bracket, Composer does not allow this.
So, we told Composer to download the yii2-firebird extension . We start the command line, make the current directory D: \ xampp \ htdocs \ yii \ and give two consecutive commands:
composer update
composer install
Carefully study the output of Composer for errors, check the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ edgardmessias \ yii2-firebird \ . The extension is installed.
Configure access to the database. The yii2-firebird documentation says that you need to change the configuration. If we carefully examine the D: \ xampp \ htdocs \ yii \ config \ web.php configuration file , we find the line (approximately line 40):
'db' => require(__DIR__ . '/db.php'), from which it becomes clear that editing the database connection settings is necessary in the file D: \ xampp \ htdocs \ yii \ config \ db.php . Open it and bring it to the form:
<?php return [ 'class' => 'edgardmessias\db\firebird\Connection', 'dsn' => 'firebird:dbname=localhost:D:/WORK/database/TBOT.GDB;charset=WIN1251', 'username' => 'SYSDBA', 'password' => 'masterkey' ]; I want to draw attention to some points:
- The first configuration line contains the Connection class with the namespace.
- the firebird access driver is specified in the 'dsn' line, the database encoding must be specified in the 'dsn' line
- the path to the database is specified through forward slash, whereas in Windows it is customary to specify paths through backslashes. In fact, there is no difference in how to specify. in D: \ xampp \ htdocs \ yii \ vendor \ edgardmessias \ yii2-firebird \ src \ PdoAdapter.php in the class constructor (line 37) there is a code:
... public function __construct($dsn, $username, $password, $driver_options = []) { // Windows OS paths with backslashes should be changed $dsn = str_replace("\\", "/", $dsn); ...
those. in the dsn line, always replacing back slashes with straight lines. - When using backslashes, remember to escape characters in PHP, so backslashes should be repeated 2 times. those. dsn line should look like this:
'dsn' => 'firebird:dbname=localhost:D:\\WORK\\database\\TBOT.GDB;charset=WIN1251', - when connecting to a local database, it is not necessary to specify localhost in dsn , I just showed how to make a connection to an external server using TCP / IP protocol
Installing yii2-firebirddb
As I indicated above, yii2-firebirddb has the status dev , therefore there are some minor installation features for this extension.
Now in the file D: \ xampp \ htdocs \ yii \ composer.json in the section "require" we add the boot path of the extension yii2-firebirddb
"srusakov/firebirddb": "*" . And since the dev status library, we find and change "minimum-stability": "stable", on "minimum-stability": "dev", over the "require" section. We get this view (note the first and last lines): ... "minimum-stability": "dev", "require": { "php": ">=5.4.0", "yiisoft/yii2": ">=2.0.5", "yiisoft/yii2-bootstrap": "*", "yiisoft/yii2-swiftmailer": "*", "edgardmessias/yii2-firebird": "*" ... We told Composer to load the yii2-firebirddb extension . We start the command line, make the current directory D: \ xampp \ htdocs \ yii \ and give two consecutive commands:
composer update
composer install
We check for the absence of errors and the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ srusakov \ firebirddb \ . The extension is installed.
Comment
The documentation for yii2-firebirddb says that you still need to register the path to the repository for downloading the extension. In my case, this, in addition to an error, did nothing, but just in case I will describe how to do it.
First install GIT for Windows . You can download the link https://git-scm.com/download/win . Restart the command line console so that the PATH environment variable is updated. Check on the command line what the command would pass
git
In response, a prompt for using the utility on the command line should appear.
In the file D: \ xampp \ htdocs \ yii \ composer.json after the closing bracket of the "require" section we write the path to the repository, it turns out:
... "require": { "php": ">=5.4.0", "yiisoft/yii2": ">=2.0.5", "yiisoft/yii2-bootstrap": "*", "yiisoft/yii2-swiftmailer": "*", "edgardmessias/yii2-firebird": "*", "srusakov/firebirddb": "*" }, "repositories":[{ "type":"git", "url":"http://github.com/srusakov/yii2-firebirddb" }], ... Pay attention to the last 4 lines. Next, we run the command line as standard, make the current directory D: \ xampp \ htdocs \ yii \ and give two commands in sequence:
composer update
composer install
We check for the absence of errors and the presence of the directory D: \ xampp \ htdocs \ yii \ vendor \ srusakov \ firebirddb \ . The extension is installed.
Open the database connection configuration file D: \ xampp \ htdocs \ yii \ config \ db.php , make changes, we get:
<?php return [ 'class' => 'yii\db\Connection', 'dsn' => 'firebird:dbname=localhost:D:/WORK/database/TBOT.GDB;charset=WIN1251', 'username' => 'sysdba', 'password' => 'masterkey', 'charset' => 'utf8', // , 'pdoClass' => 'srusakov\firebirddb\PDO', 'schemaMap' => [ 'firebird' => 'srusakov\firebirddb\Schema' // FireBird ] ]; Health check
The easiest way to verify the installation of extensions, drivers and parameters for connecting to the database is to connect. Run the gii utility from YII2 and open the Model Generator . Open link gii http: // localhost / yii / web /? R = gii , select the "Start" button in the Model Generator section, or direct link http: //localhost/yii/web/index.php? R = gii / default / view & id = model
If the Model Generator opens without errors, then everything is installed and works correctly, since Model Generator loads the list of database tables. connects to firebird. If you get a page loading error, most likely you do not have a single table in the database and the list of tables is returned empty. Create at least one table and try again. Also check the correctness of the paths to the database in the configuration (file D: \ xampp \ htdocs \ yii \ config \ db.php ).
Conclusion
- I want to note the difference in approach to the work of extensions. The yii2-firebirddb extension works with the Connection class from the standard YII2 namespace, but it uses its PDO driver to access Firebird. The yii2-firebird extension extends the standard Connection with its own.
- Both extensions do not work with BLOB fields. Description of this bug here https://bugs.php.net/bug.php?id=61183 . Since for me this is not very relevant, I did not deal with this issue, I suggest creating views in the database in which to convert the field blob to varchar . Especially varchar allows text size up to 32Kb. For the media (pictures, video, etc.) this is not enough, but I work mostly with text data and this is quite enough. The second option is to read the description of the bug, so they suggest imposing a patch, which should be fixed. If someone does this, post in the comments, I will make changes to the article.
- For those programmers who have been using PHP and YII2 for a long time, this material may seem overly detailed. For example, it is often enough to say “change the configuration” and that's it. However, for those who start migrating from Delphi to PHP and YII2, such a phrase has little to say because it is also necessary to find the files in which the configuration lies. The same goes for the commands for updating extensions.
Source: https://habr.com/ru/post/283222/
All Articles