Introduction to Propel

What is Propel?
When I first started producing websites with PHP, I used text files for storing user submitted content. It wasn't very sophisticated but did work. Of course, I abandoned that after discovering databases, and used them ever since. Unfortunately using plain mysql_query() function brought new threats like SQL Injection. Forgetting to check one variable passed to SQL query could result in someone clearing out the entire database.

Database Abstraction Libraries like ADOdb or PDO fixed that problem - but than others appeared.

PHP was primarily an easy scripting language for websites, but pretty soon it evolved into a fairly powerful object oriented language. And as more and more scripts were written with use of objects and classes it became irritating to manually write set and get methods for all properties of all classes and making sure that model from PHP is well connected with database structure.

This is where Propel comes in. It's main goal is to allow coder to forget about connecting PHP with the database model. Furthermore, when you get to know Propel well, you will probably never have to use SQL ever again (except maybe for some extremely complicated queries). Once you have database structure prepared in an Propel-friendly XML format, with execution of two commands you can generate all model classes and put structure into database.

After setting it up, you can use it as following (lets assume that there is a book table in the database with fields: id, author and title):

$book = new Book( );
$book->setAuthor( 'Arthur C. Clarke' );
$book->setTitle( 'The Stars' );
$book->save();

The code above will create a new row in book table and set author and title. There will be more examples to follow showing how to select single and multiple rows from table, update rows, etc. From above example you can get idea what Propel is about.

Setting up the model
Before we can start using model we need to generate it from schema.xml file. Let's try with a simple example from database table we used above.

This will generate table book with two fields id, title and author. Now lets try to use a separate table for authors, and we will connect it with book:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<database name="myModel" defaultIdMethod="native">
<table name="book">
<column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/>
<column name="title" type="varchar" size="255" required="true" />
<column name="author_id" type="integer" required="true" />
<foreign-key foreignTable="author">
<reference local="author_id" foreign="id"/>
</foreign-key>
</table>
<table name="author">
<column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/>
<column name="name" type="varchar" size="255" required="true" />
<column name="surname" type="varchar" size="255" required="true" />
</table>
</database>

Once we have our schema.xml file ready, we need to prepare configuration in the build.configuration file. You can basically connect to any database supported by PDO, as Propel uses PDO as database abstraction library. Let's use sqlite, our build.configuration will look as following:

propel.project = myProject
propel.database = sqlite
propel.database.url = sqlite:/path/to/myDatabase.db

You will learn how to connect Propel with different database types in next releases of this tutorial or you can navigate now to Propel project website. http://propel.phpdb.org/trac/

The last thing we need to do before building our model is to prepare runtime configuration. This configuration contains information about database, user, password, etc. In our example it will contain path to sqlite database file:

So our runtime-conf.xml will contain:

sqlite

sqlite2:/path/to/myDatabase.db

Building model
To build a model all you need to do is to execute propel-gen. Optionally as a parameter you can provide path where you want Propel to be build in, f.e.
propel-gen /path/to/myModel

Once you build your model you will notice three new directories inside of build directory: conf, sql and classes. The first one contains runtime configuration, and it is basically what you inserted in runtime-conf.xml but converted into PHP arrays to speed up process of reading configuration. Sql directory contains schema.sql file that is used to create the database structure. In the classes folder you will find all PHP classes that we will use to access database.

You can execute propel-gen command as many times as you want, you should execute it always after you modified the structure in schema.xml file.

To insert structure into your database, you can use propel-gen /path/to/myModel insert-sql command, but be aware that every time you execute this command, your database is being overwritten, so any existing rows in database will be removed, if you want to keep them back them up first.

In the next article you will find information on how to use your newly generated classes to perform basic CRUD (Create/Retrieve/Update/Delete) operations on the database without a single line of SQL.