Establishing DB Connection

To use the database that we just created, we first need to establish a connection to it.

We are going to use Data Access Objects (DAO) to abstract our data access mechanisms. If in future we decide to use a different DBMS (e.g. PostgreSQL, Oracle) to store our blog data, we only need to change the database source name (DSN) and we can keep our PHP code intact.

Note: To use DAO, you have to install and enable the PHP PDO extension and a database-specific PDO driver (in our case, it is the SQLite PDO driver). This can be achieved easily on Windows by modifying the php.ini file with the following lines:
extension=php_pdo.dll
extension=php_pdo_sqlite.dll

To further abstract the actual database tables, we will also use the Active Record (AR) feature. Each data record will be represented as an Active Record object which is capable of performing query, saving and deletion without writing SQL statements.

We modify our application configuration file protected/application.xml by inserting the following lines, which tells Active Record how to connect to our newly created database:

<modules>
  <module id="db" class="System.Data.TDataSourceConfig">
    <database ConnectionString="sqlite:protected/data/blog.db" />
  </module>
  <module class="System.Data.ActiveRecord.TActiveRecordConfig" ConnectionID="db" />
</modules>

The configuration above shows that we are adding two modules to our application. The TDataSourceConfig module is configured with the connection string sqlite:protected/data/blog.db which points to our SQLite database. This connection is used by the TActiveRecordConfig module which is required by Active Record.

Info: One may set up two or more DB connections in the application configuration. For more details, see the Active Record documentation. And of course, one may also explicitly create a DB connection in PHP code using the TDbConnection component in PDO.