Setting Up Database

Having created a skeleton application and finished database design, in this section we will create the blog database and establish the connection to it in the skeleton application.

1. Creating Database ¶

We choose to create a SQLite database. Because the database support in Yii is built on top of PDO, we can easily switch to use a different type of DBMS (e.g. MySQL, PostgreSQL) without the need to change our application code.

We create the database file blog.db under the directory /wwwroot/blog/protected/data. Note that both the directory and the database file have to be writable by the Web server process, as required by SQLite. We may simply copy the database file from the blog demo in our Yii installation which is located at /wwwroot/yii/demos/blog/protected/data/blog.db. We may also generate the database by executing the SQL statements in the file /wwwroot/yii/demos/blog/protected/data/schema.sqlite.sql.

Tip: To execute SQL statements, we may use the sqlite3 command line tool that can be found in the SQLite official website.

2. Establishing Database Connection ¶

To use the blog database in the skeleton application we created, we need to modify its application configuration which is stored as a PHP script /wwwroot/blog/protected/config/main.php. The script returns an associative array consisting of name-value pairs, each of which is used to initialize a property of the application instance.

We configure the components property of the application by adding a new entry named db shown as follows,

return array(
    ......
    'components'=>array(
        ......
        'db'=>array(
            'class'=>'CDbConnection',
            'connectionString'=>'sqlite:/wwwroot/blog/protected/data/blog.db',
        ),
    ),
    ......
);

The above configuration says that we have a db application component whose class is CDbConnection and whose connectionString property should be initialized as sqlite:/wwwroot/blog/protected/data/blog.db.

With this configuration, we can access the DB connection object using Yii::app()->db at any place in our code. Note that Yii::app() returns the application instance that we create in the entry script. If you are interested in possible methods and properties that the DB connection has, you may refer to its class reference. However, in most cases we are not going to use this DB connection directly. Instead, we will use the so-called ActiveRecord to access the database.