Using multiple database connections

Applications can require multiple database connections so that they can send and get data from different sources.

Using other database sources is incredibly simple. The only thing to do is to add a new database entry in the main configuration and use ActiveRecord support. All the operations on records will be transparent for the developer.

Here are some examples of connection strings (dsn) to configure access to other databases:

  • MySQL and MariaDB: mysql:host=localhost;dbname=mydatabase
  • SQLite: sqlite:/path/to/database/file
  • PostgreSQL: pgsql:host=localhost;port=5432;dbname=mydatabase
  • CUBRID: cubrid:dbname=demodb;host=localhost;port=33000
  • MS SQL Server (via the sqlsrv driver): sqlsrv:Server=localhost;Database=mydatabase
  • MS SQL Server (via the dblib driver): dblib:host=localhost;dbname=mydatabase
  • MS SQL Server (via the mssql driver): mssql:host=localhost;dbname=mydatabase
  • Oracle: oci:dbname=//localhost:1521/mydatabase

Example – configuring a second DB connection to export data to a local SQLite DB

We now want to add a new database connection to a SQLite DB. When we use a database, we have to make sure that the PDO driver is installed in the system, otherwise PHP cannot handle it.

Open basic/config/web.php and the inner components attribute, and append a new attribute named dbSqlite with the following attributes:

        'dbSqlite' => [
            'class' => 'yiidbConnection',
            'dsn' => 'sqlite:'.dirname(__DIR__).'/../db.sqlite',
        ],

This entry will use a DB SQLite named db.sqlite, which we can find in the dirname(__DIR__).'/../web/db.sqlite' path, under the /basic/web folder. If this file does not exist, it will be created (if a write permission is present in the /basic/web folder).

Note

Be sure that the /basic/web folder is writable, otherwise it will be impossible for the framework to create a db.sqlite file.

Create a new controller to handle actions in this new database. This will be put in /basic/controllers/TestSqliteController.php.

Insert the first action named actionCreateRoomTable in this new controller, which will create the same structure of the Room table from MySQL in dbSqlite:

<?php

namespace appcontrollers;

use Yii;
use yiiwebController;


class TestSqliteController extends Controller
{
    public function actionCreateRoomTable()
    {
        // Create room table
        $sql = 'CREATE TABLE IF NOT EXISTS room (id int not null, floor int not null, room_number int not null, has_conditioner int not null, has_tv int not null, has_phone int not null, available_from date not null, price_per_day float, description text)';
        Yii::$app->dbSqlite->createCommand($sql)->execute();
        echo 'Room table created in dbSqlite';
    }
}

Note

Pay attention so that in actionCreateRoomTable, the database instance is taken from: Yii::$app->dbSqlite.

Point your browser to http://hostname/basic/web/test-sqlite/create-room-table and create a db.sqlite file in basic/web and a room table in it.

As we have mentioned before, if the PDO driver is correctly installed, a blank page with the Room table created in dbSqlite text will be displayed.

Now we want to clone the room table from MySQL to SQLite to make a backup of this table. We need to save the records from MySQL to SQLite and verify the data stored to display it in a table.

Create a new action named actionBackupRoomTable() that executes these steps:

  1. Create a room table (if it does not exist).
  2. Delete all the records from the room in dbSqlite (alias truncate).
  3. Load all the records from the room table in MySQL (using ActiveRecord).
  4. Insert every single record from MySQL into SQLite.
  5. Render the view to display data from SQLite with the table (to verify that the copy succeeded).

The content of the actionBackupRoomTable() action is:

    use appmodelsRoom;

    public function actionBackupRoomTable()
    {
        // Create room table
        $sql = 'CREATE TABLE IF NOT EXISTS room (id int not null, floor int not null, room_number int not null, has_conditioner int not null, has_tv int not null, has_phone int not null, available_from date not null, price_per_day float, description text)';
        Yii::$app->dbSqlite->createCommand($sql)->execute();
        
        // Truncate room table in dbSqlite
        $sql = 'DELETE FROM room';
        Yii::$app->dbSqlite->createCommand($sql)->execute();
        
        // Load all records from MySQL and insert every single record in dbqlite
        $models = Room::find()->all();
        
        foreach($models as $m)
        {
            Yii::$app->dbSqlite->createCommand()->insert('room', $m->attributes)->execute();            
        }
        
        // Load all records from dbSqlite
        $sql = 'SELECT * FROM room';
        $sqliteModels = Yii::$app->dbSqlite->createCommand($sql)->queryAll();
 
        return $this->render('backupRoomTable', ['sqliteModels' => $sqliteModels]);
   }

Finally, create a view backupRoomTable in basic/views/test-sqlite/backupRoomTable.php with the following content to display data from dbSqlite:

<h2>Rooms from dbSqlite</h2>

<table class="table">
    <tr>
        <th>Floor</th>
        <th>Room number</th>
        <th>Has conditioner</th>
        <th>Has tv</th>
        <th>Has phone</th>
        <th>Available from</th>
        <th>Available from (db format)</th>
        <th>Price per day</th>
        <th>Description</th>
    </tr>
    <?php foreach($sqliteModels as $item) { ?>
    <tr>
        <td><?php echo $item['floor'] ?></td>
        <td><?php echo $item['room_number'] ?></td>
        <td><?php echo Yii::$app->formatter->asBoolean($item['has_conditioner']) ?></td>
        <td><?php echo Yii::$app->formatter->asBoolean($item['has_tv']) ?></td>
        <td><?php echo ($item['has_phone'] == 1)?'Yes':'No' ?></td>
        <td><?php echo Yii::$app->formatter->asDate($item['available_from']) ?></td>
        <td><?php echo Yii::$app->formatter->asDate($item['available_from'], 'php:Y-m-d') ?></td>
        <td><?php echo Yii::$app->formatter->asCurrency($item['price_per_day'], 'EUR') ?></td>
        <td><?php echo $item['description'] ?></td>
    </tr>
    <?php } ?>
</table>

Navigate your browser to http://hostname/basic/web/test-sqlite/backup-room-table, which should display a similar output to this:

Example – configuring a second DB connection to export data to a local SQLite DB

The list of rooms from the SQLite database

We can now download the db.sqlite file from http://hostname/basic/web/db.sqlite to preserve a backup copy of the room table!

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.119.118.99