Day 02: setting up a data model
===============================

Previously on symfony
---------------------

During [day one](1.txt) 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](http://www.askeet.com/). 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](http://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](http://www.faqts.com/), [eHow](http://www.ehow.com/), [Ask Jeeves](http://www.ask.com/) 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][1] 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][2] 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:

![ERD](/images/askeet/mcd1.gif)

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][3], 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]
    <?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
    
>**Note**: some tools allow you to build a database graphically (for instance [Fabforce's Dbdesigner](http://www.fabforce.net/dbdesigner4/)) and generate directly a `schema.xml` (with [DB Designer 4 TO Propel Schema Converter](http://blog.tooleshed.com/docs/dbd2propel/transform.php)).

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

    [yml]
    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](http://www.symfony-project.com/book/1_0/08-Inside-the-Model-Layer) 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][4]. 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](http://www.symfony-project.com/book/1_0/19-Mastering-Symfony-s-Configuration-Files) 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:

| Action name  | Description
|--------------|------------
| list         | shows all the records of a table
| index        | forwards to list
| show         | shows all the fields of a given record
| edit         | displays a form to create a new record or edit an existing one
| update       | modifies a record according to the parameters given in the request, then forwards to show
| delete       | deletes a given record from the table

You can find more about generated actions in the [scaffolding chapter](http://www.symfony-project.com/book/1_0/14-Generators) 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

![Create a new record](/images/askeet/CRUD1.gif)
![List all records](/images/askeet/CRUD2.gif)

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](mailto:askeet-subscribe@symfony-project.com), 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

[1]:  http://www.xprogramming.com/xpmag/whatisxp.htm    "(XP) Extreme Programming Explained"
[2]:  http://www.uml.org/                               "Unified Modeling Language"
[3]:  http://propel.phpdb.org/trac/                     "Propel"
[4]:  http://www.yaml.org/                              "YAML"