Setting Up Database

Having created a skeleton application and finished the 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 in the 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 writable property of the application instance.

We configure the db component as follows,

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

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

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.

We would like to explain a bit more about the tablePrefix property that we set in the configuration. This tells the db connection that it should respect the fact we are using tbl_ as the prefix to our database table names. In particular, if in a SQL statement there is a token enclosed within double curly brackets (e.g. {{post}}), then the db connection should translate it into a name with the table prefix (e.g. tbl_post) before sending it to DBMS for execution. This feature is especially useful if in future we need to modify the table name prefix without touching our source code. For example, if we are developing a generic content management system (CMS), we may exploit this feature so that when it is being installed in a new environment, we can allow users to choose a table prefix they like.

Tip: If you want to use MySQL instead of SQLite to store data, you may create a MySQL database named blog using the SQL statements in /wwwroot/yii/demos/blog/protected/data/schema.mysql.sql. Then, modify the application configuration as follows,

return array(
    ......
    'components'=>array(
        ......
        'db'=>array(
            'connectionString' => 'mysql:host=localhost;dbname=blog',
            'emulatePrepare' => true,
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8',
            'tablePrefix' => 'tbl_',
        ),
    ),
  ......
);