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:host=localhost;dbname=mydatabase
sqlite:/path/to/database/file
pgsql:host=localhost;port=5432;dbname=mydatabase
cubrid:dbname=demodb;host=localhost;port=33000
sqlsrv
driver): sqlsrv:Server=localhost;Database=mydatabase
dblib
driver): dblib:host=localhost;dbname=mydatabase
mssql
driver): mssql:host=localhost;dbname=mydatabase
oci:dbname=//localhost:1521/mydatabase
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).
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'; } }
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:
room
table (if it does not exist).dbSqlite
(alias truncate).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:
We can now download the db.sqlite
file from http://hostname/basic/web/db.sqlite
to preserve a backup copy of the room table!
18.119.118.99