Day 02: setting up a data model

Previously on symfony

During day one of this long but interesting tutorial, we saw how to install the symfony framework, setup a new application and development environment, and bring safety to the code with source version control. By the way, the code of the application generated during the first day is available in the askeet SVN repository at:

http://svn.askeet.com/

The objectives for the second day are to define what the final result should be in terms of functionalities, sketch the data model, and begin coding. This will include generating an object-relational mapping and using it interactively to create, retrieve and update records in a database with an application scaffolding.

That's quite a lot. Let's get started.

The project unveiled

What do you want to know? That's an interesting question. There are many interesting questions, like:

• What shall I do tonight with my girlfriend?
• How can I generate traffic to my blog?
• What's the best web application framework?
• What's the best affordable restaurant in Paris?
• What's the answer to life, the universe, and everything?

All these questions don't have only one answer, and the best answer is a matter of opinion. As a matter of fact, the questions that only have one answer are often the least interesting (like, how much is 1 + 1?) but the only ones to be solved on the web. That's not fair.

Meet askeet. A website dedicated to help people find answers to their questions. Who will answer those ticklish questions? Everybody. And everybody will be able to rate other people's answers, so that the most popular answers get more visibility. As the number of questions increases, it becomes impossible to organize them in categories and sub-categories, so the creator of a question will be able to tag it with any word he/she wants, "à la" del.icio.us. Of course, the popularity of tags will have to be represented through a tag bubble. If one wants to follow the answers to a particular question, he/she can subscribe to this question's RSS feed. All these functionalities have to be elegant and lightweight, so all the interactions that don't actually need a new page have to be of AJAX type. Eventually, a back-end is necessary to moderate questions and answers reported as spam, or to push artificially a question that the administrator finds encouraging.

Then you should ask: Haven't I already seen such a website on the web? Well, if you actually did, we're busted, but if you refer to faqts, eHow, Ask Jeeves or something similar, with no collaborative answers, no AJAX, no RSS and no tags, this is not the same website. We are talking about a web 2.0 application here.

The big deal about askeet is that it is not only a website, it is an application that anyone can download, install at home or in a company Intranet, tweak and add features to. The source code will be released with an open-source license. Your HR head is looking for a knowledge management system? You want to keep track of all the tricks you learned about fixing your car? You don't want to develop a Frequently Asked Questions section for your website? Search no more, for askeet exists. Well, it will exist, that's our Christmas present.

Where to start?

So how are you supposed to start a symfony application? It all depends on you. You could write stories, do a planning game and find a partner to do pair programming if you were an XP adept, or write a detailed specification of the website, together with a sketch of all the objects, states, interactions and so on if you were a UML fan.

But this tutorial isn't about application development in general, so we'll start with a basic relational data model, and add working features one by one. What we need is an application that can be used at the end of every day, not a gigantic ongoing bunch of code that never outputs anything. In an ideal world, we should write unit tests for any feature we add, but we honestly won't have time for that. One day will be dedicated to unit tests though, so keep on reading.

For this project, we will use a MySQL database with the InnoDB table type to take advantage of the integrity constraints and transaction support. We could have used a SQLite database for the first steps, to avoid setting up a real database. This would have required only a few changes in the databases.yml file, that we'll leave for you to investigate as an exercise.

Data Model

Relational model

Obviously, there will be a 'question' and an 'answer' tables. We'll need a 'user' table, and we'll store the interest of users for a question in a 'interest' table, and the relevancy given by a person to an answer in a 'relevancy' table.

Users will have to be identified to add a question, to rate the relevancy an answer, or to declare interest to a question. Users won't need to be identified to add an answer, but an answer will always be linked to a user so that users with popular answers can be distinguished. The answers entered without any identification will be shown as contributions of a generic user, called 'Anonymous Coward'. It's easier to understand with an entity relationship diagram:

Notice that we've declared a created_at field for each table. Symfony recognizes such fields and sets the value to the current system time when a record is created. That's the same for updated_at fields: Their value is set to the system time whenever the record is updated.

schema.xml

The relational model has to be translated to a configuration file for symfony to understand it. That's the purpose of the schema.xml or schema.yml file, located in the askeet/config/ directory. Symfony supports schemas in the XML or YAML format.

There are two ways to write this file: by hand, and that's the way we like it, or from an existing database. Let's see the first solution.

First, we need to remove the sample YAML file installed by default:

$ svn delete config/schema.yml

The syntax of the schema.xml, explained in detail on the Propel website, is relatively simple: It's an XML file, in which <table> tags contain <column>, <foreign-key> and <index> tags. Once you write one, you can write all of them. Here is the schema.xml corresponding to the relational model described previously:

<?xml version="1.0" encoding="UTF-8"?>
 <database name="propel" defaultIdMethod="native" noxsd="true">
   <table name="ask_question" phpName="Question">
     <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
     <column name="user_id" type="integer" />
     <foreign-key foreignTable="ask_user">
       <reference local="user_id" foreign="id"/>
     </foreign-key>
     <column name="title" type="longvarchar" />
     <column name="body" type="longvarchar" />
     <column name="created_at" type="timestamp" />
     <column name="updated_at" type="timestamp" />
   </table>
 
   <table name="ask_answer" phpName="Answer">
     <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
     <column name="question_id" type="integer" />
     <foreign-key foreignTable="ask_question">
       <reference local="question_id" foreign="id"/>
     </foreign-key>
     <column name="user_id" type="integer" />
     <foreign-key foreignTable="ask_user">
       <reference local="user_id" foreign="id"/>
     </foreign-key>
     <column name="body" type="longvarchar" />
     <column name="created_at" type="timestamp" />
   </table>
 
   <table name="ask_user" phpName="User">
     <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
     <column name="nickname" type="varchar" size="50" />
     <column name="first_name" type="varchar" size="100" />
     <column name="last_name" type="varchar" size="100" />
     <column name="created_at" type="timestamp" />
   </table>
 
   <table name="ask_interest" phpName="Interest">
     <column name="question_id" type="integer" primaryKey="true" />
     <foreign-key foreignTable="ask_question">
       <reference local="question_id" foreign="id"/>
     </foreign-key>
     <column name="user_id" type="integer" primaryKey="true" />
     <foreign-key foreignTable="ask_user">
       <reference local="user_id" foreign="id"/>
     </foreign-key>
     <column name="created_at" type="timestamp" />
   </table>
 
   <table name="ask_relevancy" phpName="Relevancy">
     <column name="answer_id" type="integer" primaryKey="true" />
     <foreign-key foreignTable="ask_answer">
       <reference local="answer_id" foreign="id"/>
     </foreign-key>
     <column name="user_id" type="integer" primaryKey="true" />
     <foreign-key foreignTable="ask_user">
       <reference local="user_id" foreign="id"/>
     </foreign-key>
     <column name="score" type="integer" />
     <column name="created_at" type="timestamp" />
   </table>
 
 </database>

Notice that the database name is set to propel in this file, whatever the actual database name. This is a parameter used to connect the Propel layer to the symfony framework. The actual name of the database will be defined in the databases.yml configuration file (see below).

There is another way to create a schema.xml if you have an existing database. That is, if you are familiar with a graphical database design tool, you will prefer to build the schema from the generated MySQL database. Before you do that, you just need to edit the propel.ini file located in the askeet/config/ directory and enter the connection settings to your database:

propel.database.url = mysql://username:password@localhost/databasename

...where username, password, localhost and databasename are the actual connection settings of your database. You can now call the propel-build-schema command (from the askeet/ directory) to generate the schema.xml from the database:

$ symfony propel-build-schema

Instead of creating a schema.xml file, you can also create a schema.yml file using the YAML schema format:

propel:
  _attributes:   { noXsd: false, defaultIdMethod: none, package: lib.model }
 
  ask_question:
    _attributes: { phpName: Question, idMethod: native }
    id:          { type: integer, required: true, primaryKey: true, autoIncrement: true }
    user_id:     { type: integer, foreignTable: ask_user, foreignReference: id }
    title:       { type: longvarchar }
    body:        { type: longvarchar }
    created_at:  ~
    updated_at:  ~
 
  ask_answer:
    _attributes: { phpName: Answer, idMethod: native }
    id:          { type: integer, required: true, primaryKey: true, autoIncrement: true }
    question_id: { type: integer, foreignTable: ask_question, foreignReference: id }
    user_id:     { type: integer, foreignTable: ask_user, foreignReference: id }
    body:        { type: longvarchar }
    created_at:  ~
 
  ask_user:
    _attributes: { phpName: User, idMethod: native }
    id:          { type: integer, required: true, primaryKey: true, autoIncrement: true }
    nickname:    { type: varchar(50), required: true, index: true }
    first_name:  varchar(100)
    last_name:   varchar(100)
    created_at:  ~
 
  ask_interest:
    _attributes: { phpName: Interest, idMethod: native }
    question_id: { type: integer, foreignTable: ask_question, foreignReference: id, primaryKey: true }
    user_id:     { type: integer, foreignTable: ask_user, foreignReference: id, primaryKey: true }
    created_at:  ~
 
  ask_relevancy:
    _attributes: { phpName: Relevancy, idMethod: native }
    answer_id:   { type: integer, foreignTable: ask_answer, foreignReference: id, primaryKey: true }
    user_id:     { type: integer, foreignTable: ask_user, foreignReference: id, primaryKey: true }
    score:       { type: integer }
    created_at:  ~

Object model build

To use the InnoDB engine, one line has to be added to the propel.ini file of the askeet/config/ directory:

propel.mysql.tableType = InnoDB

Once the schema.xml is built, you can generate an object model based on the relational model. In symfony, the object relational mapping is handled by Propel, but encapsulated into the symfony command:

$ symfony propel-build-model

This command (you need to call it from the root directory of the askeet project) will generate the classes corresponding to the tables defined in the schema, together with standard accessors (->get() and ->set() methods). You can look at the generated code in the askeet/lib/model/om/ directory. If you wonder why there are two classes per table, go and check the model chapter of the symfony book. These classes will be overridden each time that you do a build-model, and this will happen a lot in this project. So if you need to add methods to the model objects, you have to modify the ones located in the askeet/lib/model/ directory - these classes inherit from the /om ones.

The database

Connection

Now that symfony has an object model of the database, it is time to connect your project to the MySQL database. First, you have to create a database in MySQL:

$ mysqladmin -u youruser -p create askeet

Now open the askeet/config/databases.yml configuration file. If this is your first time with symfony, you will discover that the symfony configuration files are written in YAML. The syntax is very simple, but there is one major obligation in YAML files: never use tabulations, always use spaces. Once you know that, you are ready to edit the file and enter the actual connection settings to your database under the all: category:

all:
  propel:
    class:      sfPropelDatabase
    param:
      phptype:  mysql
      host:     localhost
      database: askeet
      username: youruser
      password: yourpasswd

If you want to know more about symfony configuration and YAML files, read the configuration in practice chapter of the symfony book.

Build

If you didn't write the schema.xml file by hand, you probably already have the corresponding tables in your database. You can then skip this part.

For you keyboard fans, here is a surprise: You don't need to create the tables and the columns in the MySQL database. You did it once in the schema.xml, so symfony will build the SQL statement creating all that for you:

$ symfony propel-build-sql

This command creates a lib.model.schema.sql in the askeet/data/sql/ directory. Use it as a SQL command in MySQL:

$ mysql -u youruser -p askeet < data/sql/lib.model.schema.sql

Alternatively, you can also use the propel-insert-sql task:

$ symfony propel-insert-sql

Test data access via a CRUD

It is always good to see that the work done is useful. Until now, your browser wasn't of any use, and yet we are supposed to build a web application... So let's create a basic set of symfony templates and actions to manipulate the data of the 'question' table. This will allow you to create a few questions and display them.

In the askeet/ directory, type:

$ symfony propel-generate-crud frontend question Question

This generates a scaffolding for a question module in the frontend application, based on the Question Propel object model, with basic Create Retrieve Update Delete actions (which explains the CRUD acronym). Don't get confused: A scaffolding is not a finished application, but the basic structure on top of which you can develop new features, add business rules and customize the look and feel.

The list of all the actions created by a CRUD generator is:

You can find more about generated actions in the scaffolding chapter of the symfony book.

In the askeet/apps/frontend/modules/ directory, notice the new question module and browse its source.

Whenever you add a new class that need to be autoloaded, don't forget to clear the config cache (to reload the autoloading cache):

$ symfony cc frontend config

You can now test it online by requesting:

http://askeet/question

Go ahead, play with it. Add a few questions, edit them, list them, delete them. If it works, this means that the object model is correct, that the connection to the database is correct, and that the mapping between the relational model of the database and the object model of symfony is correct. That's a good functional test.

See you Tomorrow

You didn't write one line of PHP, and yet you have a basic application to use. That's not bad for the second day. Tomorrow, we'll start writing some code in order to have a welcoming home page that displays the list of questions. We will also add test data to our database using a batch process, and learn how to extend the model.

Now that you know what the application will do, you may be able to imagine an additional feature to it. Feel free to suggest anything using the askeet mailing-list, the most popular idea will become the 21st day addition of this symfony advent calendar.

Feel free to browse the source of today's tutorial (tag release_day_2) at:

http://svn.askeet.com/tags/release_day_2