Because of the relational database's pivotal role in driving Web applications, a great deal of time and effort has been put into creating tools that not only simplify the task of mapping database tables to a programming language's object-oriented class structure, but also facilitate the management of your data and schemas over the project lifecycle. The Java and .NET communities have benefited from two such solutions in particular for years, namely Hibernate and NHibernate.
The PHP community also has a powerful database integration tool at their disposal: a project known as Doctrine. This open source project was inspired by Hibernate and Rails' Active Record implementation, and both the symfony and Zend frameworks have adopted it as a primary database solution. This article introduces Doctrine and shows you how to begin mapping your database tables to PHP objects.
Doctrine requires PHP 5.2.3 or newer configured with PHP Data Objects (PDO) support. You'll need to get those two requirements in order if your system doesn't already meet this criterion. Next, head over to the Doctrine website and either download the latest 1.2 package or check out the latest stable version from the listed Git or Subversion repositories.
Doctrine can be somewhat of a bear to configure -- a subject worthy of an entire article by itself -- so I've opted to just introduce you to Doctrine using a handy sandbox environment, which is packaged with the download. The sandbox environment allows you to get acquainted with Doctrine features with minimal configuration. In fact, you won't even need to install a database, because the sandbox uses a self-contained version of SQLite. Therefore, when the package has downloaded, unzip it, open a terminal window, and navigate to the project's
Generating a Schema
Much of your interaction with Doctrine will take place via a convenient command-line interface, which is used for tasks such as creating the database schema, loading sample data (known as fixtures), and generating the models that will serve as an object-oriented bridge between the database and application. You can view a list of available commands by executing the following command from within the
%>./doctrine ./doctrine generate-sql ./doctrine build-all-load ./doctrine build-all ./doctrine migrate ... ./doctrine drop-db
In the interest of space I've removed some of the commands from this listing. In total you'll find 21 different commands used to carry out a wide variety of tasks. One of the first commands you'll use is
build-all-reload, which you'll use to create the project database and load the schemas and starter data.
Because a database is useless without tables, let's start by creating the schema that defines the table structure. Create a new file named
schema.yml and save it to the
tools/sandbox/schema directory, adding the following contents:
User: columns: id: primary: true autoincrement: true type: integer(4) email: string(255) first_name: string(255)
This YAML-formatted document defines the schema that will be used to both create the database tables and the models that will serve as the application's database interface. With the schema defined, you can generate the schema and models using the following command:
%>./doctrine build-all-load build-all-load - Generated models successfully from YAML schema build-all-load - Successfully created database for connection named 'doctrine' build-all-load - Created tables successfully build-all-load - Data was successfully loaded
sandbox directory anew, you'll see that a file named
sandbox.db has been created. This is a SQLite database. You can peer into the database by loading the database into the SQLite console (presuming SQLite3 is installed on your system):
%>sqlite3 sandbox.db SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>.tables user sqlite> .schema user CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT, email VARCHAR(255), first_name VARCHAR(255));
Loading Data Fixtures
Inserting sample data into the database using a custom HTML form or an administration application such as PHPMyAdmin can be tedious and error-prone. To facilitate the rapid loading of sample data, you can instead use Doctrine's data fixtures feature, using the same YAML format to create sample records. Create a file named
data.yml and save it to the
User: wjgilmore: email: email@example.com first_name: Jason jimspammer: email: firstname.lastname@example.org first_name: Jim
When this file is in place, load it into the database using the following command:
Entering SQLite anew you'll see that the data has indeed been inserted:
sqlite> select * from user; email@example.com|Jason firstname.lastname@example.org|Jim