| [ Index ] |
|
Code source de Symfony 1.0.0 |
1 Chapter 8 - Inside The Model Layer 2 ================================== 3 4 Much of the discussion so far has been devoted to building pages, and processing requests and responses. But the business logic of a web application relies mostly on its data model. Symfony's default model component is based on an object/relational mapping layer known as the Propel project ([http://propel.phpdb.org/](http://propel.phpdb.org/)). In a symfony application, you access data stored in a database and modify it through objects; you never address the database explicitly. This maintains a high level of abstraction and portability. 5 6 This chapter explains how to create an object data model, and the way to access and modify the data in Propel. It also demonstrates the integration of Propel in Symfony. 7 8 Why Use an ORM and an Abstraction Layer? 9 ---------------------------------------- 10 11 Databases are relational. PHP 5 and symfony are object-oriented. In order to most effectively access the database in an object-oriented context, an interface translating the object logic to the relational logic is required. As explained in Chapter 1, this interface is called an object-relational mapping (ORM), and it is made up of objects that give access to data and keep business rules within themselves. 12 13 The main benefit of an ORM is reusability, allowing the methods of a data object to be called from various parts of the application, even from different applications. The ORM layer also encapsulates the data logic--for instance, the calculation of a forum user rating based on how many contributions were made and how popular these contributions are. When a page needs to display such a user rating, it simply calls a method of the data model, without worrying about the details of the calculation. If the calculation changes afterwards, you will just need to modify the rating method in the model, leaving the rest of the application unchanged. 14 15 Using objects instead of records, and classes instead of tables, has another benefit: They allow you to add new accessors to your objects that don't necessarily match a column in a table. For instance, if you have a table called `client` with two fields named `first_name` and `last_name`, you might like to be able to require just a `Name`. In an object-oriented world, it is as easy as adding a new accessor method to the `Client` class, as in Listing 8-1. From the application point of view, there is no difference between the `FirstName`, `LastName`, and `Name` attributes of the `Client` class. Only the class itself can determine which attributes correspond to a database column. 16 17 Listing 8-1 - Accessors Mask the Actual Table Structure in a Model Class 18 19 [php] 20 public function getName() 21 { 22 return $this->getFirstName.' '.$this->getLastName(); 23 } 24 25 All the repeated data-access functions and the business logic of the data itself can be kept in such objects. Suppose you have a `ShoppingCart` class in which you keep `Items` (which are objects). To get the full amount of the shopping cart for the checkout, write a custom method to encapsulate the actual calculation, as shown in Listing 8-2. 26 27 Listing 8-2 - Accessors Mask the Data Logic 28 29 [php] 30 public function getTotal() 31 { 32 $total = 0; 33 foreach ($this->getItems() as $item) 34 { 35 $total += $item->getPrice() * $item->getQuantity(); 36 } 37 38 return $total; 39 } 40 41 There is another important point to consider when building data-access procedures: Database vendors use different SQL syntax variants. Switching to another database management system (DBMS) forces you to rewrite part of the SQL queries that were designed for the previous one. If you build your queries using a database-independent syntax, and leave the actual SQL translation to a third-party component, you can switch database systems without pain. This is the goal of the database abstraction layer. It forces you to use a specific syntax for queries, and does the dirty job of conforming to the DBMS particulars and optimizing the SQL code. 42 43 The main benefit of an abstraction layer is portability, because it makes switching to another database possible, even in the middle of a project. Suppose that you need to write a quick prototype for an application, but the client hasn't decided yet which database system would best suit his needs. You can start building your application with SQLite, for instance, and switch to MySQL, PostgreSQL, or Oracle when the client is ready to decide. Just change one line in a configuration file, and it works. 44 45 Symfony uses Propel as the ORM, and Propel uses Creole for database abstraction. These two third-party components, both developed by the Propel team, are seamlessly integrated into symfony, and you can consider them as part of the framework. Their syntax and conventions, described in this chapter, were adapted so that they differ from the symfony ones as little as possible. 46 47 >**NOTE** 48 >In a symfony project, all the applications share the same model. That's the whole point of the project level: regrouping applications that rely on common business rules. This is the reason that the model is independent from the applications and the model files are stored in a `lib/model/` directory at the root of the project. 49 50 Symfony's Database Schema 51 ------------------------- 52 53 In order to create the data object model that symfony will use, you need to translate whatever relational model your database has to an object data model. The ORM needs a description of the relational model to do the mapping, and this is called a schema. In a schema, you define the tables, their relations, and the characteristics of their columns. 54 55 Symfony's syntax for schemas uses the YAML format. The `schema.yml` files must be located in the `myproject/config/` directory. 56 57 >**NOTE** 58 >Symfony also understands the Propel native XML schema format, as described in the "Beyond the schema.yml: The schema.xml" section later in this chapter. 59 60 ### Schema Example 61 62 How do you translate a database structure into a schema? An example is the best way to understand it. Imagine that you have a blog database with two tables: `blog_article` and `blog_comment`, with the structure shown in Figure 8-1. 63 64 Figure 8-1 - A blog database table structure 65 66  67 68 The related `schema.yml` file should look like Listing 8-3. 69 70 Listing 8-3 - Sample `schema.yml` 71 72 propel: 73 blog_article: 74 _attributes: { phpName: Article } 75 id: 76 title: varchar(255) 77 content: longvarchar 78 created_at: 79 blog_comment: 80 _attributes: { phpName: Comment } 81 id: 82 article_id: 83 author: varchar(255) 84 content: longvarchar 85 created_at: 86 87 Notice that the name of the database itself (`blog`) doesn't appear in the `schema.yml` file. Instead, the database is described under a connection name (`propel` in this example). This is because the actual connection settings can depend on the environment in which your application runs. For instance, when you run your application in the development environment, you will access a development database (maybe `blog_dev`), but with the same schema as the production database. The connection settings will be specified in the `databases.yml` file, described in the "Database Connections" section later in this chapter. The schema doesn't contain any detailed connection to settings, only a connection name, to maintain database abstraction. 88 89 ### Basic Schema Syntax 90 91 In a `schema.yml` file, the first key represents a connection name. It can contain several tables, each having a set of columns. According to the YAML syntax, the keys end with a colon, and the structure is shown through indentation (one or more spaces, but no tabulations). 92 93 A table can have special attributes, including the `phpName` (the name of the class that will be generated). If you don't mention a phpName for a table, symfony creates it based on the camelCase version of the table name. 94 95 >**TIP** 96 >The camelCase convention removes underscores from words, and capitalizes the first letter of inner words. The default camelCase versions of `blog_article` and `blog_comment` are `BlogArticle` and `BlogComment`. The name of this convention comes from the appearance of capitals inside a long word, suggestive of the humps of a camel. 97 98 A table contains columns. The column value can be defined in three different ways: 99 100 * If you define nothing, symfony will guess the best attributes according to the column name and a few conventions that will be described in the "Empty Columns" section later in this chapter. For instance, the `id` column in Listing 8-3 doesn't need to be defined. Symfony will make it an auto-incremented integer, primary key of the table. The article_id in the `blog_comment` table will be understood as a foreign key to the `blog_article` table (columns ending with `_id` are considered to be foreign keys, and the related table is automatically determined according to the first part of the column name). Columns called `created_at` are automatically set to the `timestamp` type. For all these columns, you don't need to specify any type. This is one of the reasons why `schema.yml` is so easy to write. 101 * If you define only one attribute, it is the column type. Symfony understands the usual column types: `boolean`, `integer`, `float`, `date`, `varchar(size)`, `longvarchar` (converted, for instance, to `text` in MySQL), and so on. For text content over 256 characters, you need to use the `longvarchar` type, which has no size (but cannot exceed 65KB in MySQL). Note that the `date` and `timestamp` types have the usual limitations of Unix dates and cannot be set to a date prior to 1970-01-01. As you may need to set older dates (for instance, for dates of birth), a format of dates "before Unix" can be used with bu_date and b`u_timestamp`. 102 * If you need to define other column attributes (like default value, required, and so on), you should write the column attributes as a set of `key: value`. This extended schema syntax is described later in the chapter. 103 104 Columns can also have a `phpName` attribute, which is the capitalized version of the name (`Id`, `Title`, `Content`, and so on) and doesn't need overriding in most cases. 105 106 Tables can also contain explicit foreign keys and indexes, as well as a few database-specific structure definitions. Refer to the "Extended Schema Syntax" section later in this chapter to learn more. 107 108 Model Classes 109 ------------- 110 111 The schema is used to build the model classes of the ORM layer. To save execution time, these classes are generated with a command-line task called `propel-build-model`. 112 113 > symfony propel-build-model 114 115 Typing this command will launch the analysis of the schema and the generation of base data model classes in the `lib/model/om/` directory of your project: 116 117 * `BaseArticle.php` 118 * `BaseArticlePeer.php` 119 * `BaseComment.php` 120 * `BaseCommentPeer.php` 121 122 In addition, the actual data model classes will be created in `lib/model/`: 123 124 * `Article.php` 125 * `ArticlePeer.php` 126 * `Comment.php` 127 * `CommentPeer.php` 128 129 You defined only two tables, and you end up with eight files. There is nothing wrong, but it deserves some explanation. 130 131 ### Base and Custom Classes 132 133 Why keep two versions of the data object model in two different directories? 134 135 You will probably need to add custom methods and properties to the model objects (think about the `getName()` method in Listing 8-1). But as your project develops, you will also add tables or columns. Whenever you change the `schema.yml` file, you need to regenerate the object model classes by making a new call to propel-build-model. If your custom methods were written in the classes actually generated, they would be erased after each generation. 136 137 The `Base` classes kept in the `lib/model/om/` directory are the ones directly generated from the schema. You should never modify them, since every new build of the model will completely erase these files. 138 139 On the other hand, the custom object classes, kept in the `lib/model/` directory, actually inherit from the `Base` ones. When the `propel-build-model` task is called on an existing model, these classes are not modified. So this is where you can add custom methods. 140 141 Listing 8-4 presents an example of a custom model class as created by the first call to the `propel-build-model` task. 142 143 Listing 8-4 - Sample Model Class File, in `lib/model/Article.php` 144 145 [php] 146 <?php 147 148 class Article extends BaseArticle 149 { 150 } 151 152 It inherits all the methods of the `BaseArticle` class, but a modification in the schema will not affect it. 153 154 The mechanism of custom classes extending base classes allows you to start coding, even without knowing the final relational model of your database. The related file structure makes the model both customizable and evolutionary. 155 156 ### Object and Peer Classes 157 158 `Article` and `Comment` are object classes that represent a record in the database. They give access to the columns of a record and to related records. This means that you will be able to know the title of an article by calling a method of an Article object, as in the example shown in Listing 8-5. 159 160 Listing 8-5 - Getters for Record Columns Are Available in the Object Class 161 162 [php] 163 $article = new Article(); 164 ... 165 $title = $article->getTitle(); 166 167 `ArticlePeer` and `CommentPeer` are peer classes; that is, classes that contain static methods to operate on the tables. They provide a way to retrieve records from the tables. Their methods usually return an object or a collection of objects of the related object class, as shown in Listing 8-6. 168 169 Listing 8-6 - Static Methods to Retrieve Records Are Available in the Peer Class 170 171 [php] 172 $articles = ArticlePeer::retrieveByPks(array(123, 124, 125)); 173 // $articles is an array of objects of class Article 174 175 >**NOTE** 176 >From a data model point of view, there cannot be any peer object. That's why the methods of the peer classes are called with a `::` (for static method call), instead of the usual `->` (for instance method call). 177 178 So combining object and peer classes in a base and a custom version results in four classes generated per table described in the schema. In fact, there is a fifth class created in the `lib/model/map/` directory, which contains metadata information about the table that is needed for the runtime environment. But as you will probably never change this class, you can forget about it. 179 180 Accessing Data 181 -------------- 182 183 In symfony, your data is accessed through objects. If you are used to the relational model and using SQL to retrieve and alter your data, the object model methods will likely look complicated. But once you've tasted the power of object orientation for data access, you will probably like it a lot. 184 185 But first, let's make sure we share the same vocabulary. Relational and object data model use similar concepts, but they each have their own nomenclature: 186 187 Relational | Object-Oriented 188 ------------- | --------------- 189 Table | Class 190 Row, record | Object 191 Field, column | Property 192 193 ### Retrieving the Column Value 194 195 When symfony builds the model, it creates one base object class for each of the tables defined in the `schema.yml`. Each of these classes comes with default constructors, accessors, and mutators based on the column definitions: The `new`, `getXXX()`, and `setXXX()` methods help to create objects and give access to the object properties, as shown in Listing 8-7. 196 197 Listing 8-7 - Generated Object Class Methods 198 199 [php] 200 $article = new Article(); 201 $article->setTitle('My first article'); 202 $article->setContent('This is my very first article.\n Hope you enjoy it!'); 203 204 $title = $article->getTitle(); 205 $content = $article->getContent(); 206 207 >**NOTE** 208 >The generated object class is called `Article`, which is the `phpName` given to the `blog_article` table. If the `phpName` were not defined in the schema, the class would have been called `BlogArticle`. The accessors and mutators use a camelCase variant of the column names, so the `getTitle()` method retrieves the value of the `title` column. 209 210 To set several fields at one time, you can use the `fromArray()` method, also generated for each object class, as shown in Listing 8-8. 211 212 Listing 8-8 - The `fromArray()` Method Is a Multiple Setter 213 214 [php] 215 $article->fromArray(array( 216 'title' => 'My first article', 217 'content' => 'This is my very first article.\n Hope you enjoy it!', 218 )); 219 220 ### Retrieving Related Records 221 222 The `article_id` column in the `blog_comment` table implicitly defines a foreign key to the `blog_article` table. Each comment is related to one article, and one article can have many comments. The generated classes contain five methods translating this relationship in an object-oriented way, as follows: 223 224 * `$comment->getArticle()`: To get the related `Article` object 225 * `$comment->getArticleId()`: To get the ID of the related `Article` object 226 * `$comment->setArticle($article)`: To define the related `Article` object 227 * `$comment->setArticleId($id)`: To define the related `Article` object from an ID 228 * `$article->getComments()`: To get the related `Comment` objects 229 230 The `getArticleId()` and `setArticleId()` methods show that you can consider the article_id column as a regular column and set the relationships by hand, but they are not very interesting. The benefit of the object-oriented approach is much more apparent in the three other methods. Listing 8-9 shows how to use the generated setters. 231 232 Listing 8-9 - Foreign Keys Are Translated into a Special Setter 233 234 [php] 235 $comment = new Comment(); 236 $comment->setAuthor('Steve'); 237 $comment->setContent('Gee, dude, you rock: best article ever!); 238 239 // Attach this comment to the previous $article object 240 $comment->setArticle($article); 241 242 // Alternative syntax 243 // Only makes sense if the object is already saved in the database 244 $comment->setArticleId($article->getId()); 245 246 Listing 8-10 shows how to use the generated getters. It also demonstrates how to chain method calls on model objects. 247 248 Listing 8-10 - Foreign Keys Are Translated into Special Getters 249 250 [php] 251 // Many to one relationship 252 echo $comment->getArticle()->getTitle(); 253 => My first article 254 echo $comment->getArticle()->getContent(); 255 => This is my very first article. 256 Hope you enjoy it! 257 258 // One to many relationship 259 $comments = $article->getComments(); 260 261 The `getArticle()` method returns an object of class `Article`, which benefits from the `getTitle()` accessor. This is much better than doing the join yourself, which may take a few lines of code (starting from the `$comment->getArticleId()` call). 262 263 The `$comments` variable in Listing 8-10 contains an array of objects of class `Comment`. You can display the first one with `$comments[0]` or iterate through the collection with `foreach ($comments as $comment)`. 264 265 >**NOTE** 266 >Objects from the model are defined with a singular name by convention, and you can now understand why. The foreign key defined in the `blog_comment` table causes the creation of a `getComments()` method, named by adding an `s` to the `Comment` object name. If you gave the model object a plural name, the generation would lead to a method named `getCommentss()`, which doesn't make sense. 267 268 ### Saving and Deleting Data 269 270 By calling the `new` constructor, you created a new object, but not an actual record in the `blog_article` table. Modifying the object has no effect on the database either. In order to save the data into the database, you need to call the `save()` method of the object. 271 272 [php] 273 $article->save(); 274 275 The ORM is smart enough to detect relationships between objects, so saving the `$article` object also saves the related `$comment` object. It also knows if the saved object has an existing counterpart in the database, so the call to `save()` is sometimes translated in SQL by an `INSERT`, and sometimes by an `UPDATE`. The primary key is automatically set by the `save()` method, so after saving, you can retrieve the new primary key with `$article->getId()`. 276 277 >**TIP** 278 >You can check if an object is new by calling isNew(). And if you wonder if an object has been modified and deserves saving, call its `isModified()` method. 279 280 If you read comments to your articles, you might change your mind about the interest of publishing on the Internet. And if you don't appreciate the irony of article reviewers, you can easily delete the comments with the `delete()` method, as shown in Listing 8-11. 281 282 Listing 8-11 - Delete Records from the Database with the `delete()`Method on the Related Object 283 284 [php] 285 foreach ($article->getComments() as $comment) 286 { 287 $comment->delete(); 288 } 289 290 >**TIP** 291 >Even after calling the `delete()` method, an object remains available until the end of the request. To determine if an object is deleted in the database, call the `isDeleted()` method. 292 293 ### Retrieving Records by Primary Key 294 295 If you know the primary key of a particular record, use the `retrieveByPk()` class method of the peer class to get the related object. 296 297 [php] 298 $article = ArticlePeer::retrieveByPk(7); 299 300 The `schema.yml` file defines the `id` field as the primary key of the `blog_article` table, so this statement will actually return the article that has `id` 7. As you used the primary key, you know that only one record will be returned; the `$article` variable contains an object of class `Article`. 301 302 In some cases, a primary key may consist of more than one column. In those cases, the `retrieveByPK()` method accepts multiple parameters, one for each primary key column. 303 304 You can also select multiple objects based on their primary keys, by calling the generated `retrieveByPKs()` method, which expects an array of primary keys as a parameter. 305 306 ### Retrieving Records with Criteria 307 308 When you want to retrieve more than one record, you need to call the `doSelect()` method of the peer class corresponding to the objects you want to retrieve. For instance, to retrieve objects of class `Article`, call `ArticlePeer::doSelect()`. 309 310 The first parameter of the `doSelect()` method is an object of class `Criteria`, which is a simple query definition class defined without SQL for the sake of database abstraction. 311 312 An empty `Criteria` returns all the objects of the class. For instance, the code shown in Listing 8-12 retrieves all the articles. 313 314 Listing 8-12 - Retrieving Records by Criteria with `doSelect()`--Empty Criteria 315 316 [php] 317 $c = new Criteria(); 318 $articles = ArticlePeer::doSelect($c); 319 320 // Will result in the following SQL query 321 SELECT blog_article.ID, blog_article.TITLE, blog_article.CONTENT, 322 blog_article.CREATED_AT 323 FROM blog_article; 324 325 >**SIDEBAR** 326 >Hydrating 327 > 328 >The call to `::doSelect()` is actually much more powerful than a simple SQL query. First, the SQL is optimized for the DBMS you choose. Second, any value passed to the `Criteria` is escaped before being integrated into the SQL code, which prevents SQL injection risks. Third, the method returns an array of objects, rather than a result set. The ORM automatically creates and populates objects based on the database result set. This process is called hydrating. 329 330 For a more complex object selection, you need an equivalent of the WHERE, ORDER BY, GROUP BY, and other SQL statements. The `Criteria` object has methods and parameters for all these conditions. For example, to get all comments written by Steve, ordered by date, build a `Criteria` as shown in Listing 8-13. 331 332 Listing 8-13 - Retrieving Records by Criteria with `doSelect()`--Criteria with Conditions 333 334 [php] 335 $c = new Criteria(); 336 $c->add(CommentPeer::AUTHOR, 'Steve'); 337 $c->addAscendingOrderByColumn(CommentPeer::CREATED_AT); 338 $comments = CommentPeer::doSelect($c); 339 340 // Will result in the following SQL query 341 SELECT blog_comment.ARTICLE_ID, blog_comment.AUTHOR, blog_comment.CONTENT, 342 blog_comment.CREATED_AT 343 FROM blog_comment 344 WHERE blog_comment.author = 'Steve' 345 ORDER BY blog_comment.CREATED_AT ASC; 346 347 The class constants passed as parameters to the add() methods refer to the property names. They are named after the capitalized version of the column names. For instance, to address the `content` column of the `blog_article` table, use the `ArticlePeer::CONTENT` class constant. 348 349 >**NOTE** 350 >Why use `CommentPeer::AUTHOR` instead of `blog_comment.AUTHOR`, which is the way it will be output in the SQL query anyway? Suppose that you need to change the name of the author field to `contributor` in the database. If you used `blog_comment.AUTHOR`, you would have to change it in every call to the model. On the other hand, by using `CommentPeer::AUTHOR`, you simply need to change the column name in the `schema.yml` file, keep `phpName` as `AUTHOR`, and rebuild the model. 351 352 Table 8-1 compares the SQL syntax with the `Criteria` object syntax. 353 354 Table 8-1 - SQL and Criteria Object Syntax 355 356 SQL | Criteria 357 ------------------------------------------------------------ | ----------------------------------------------- 358 `WHERE column = value` | `->add(column, value);` 359 `WHERE column <> value` | `->add(column, value, Criteria::NOT_EQUAL);` 360 **Other Comparison Operators** | 361 `> , <` | `Criteria::GREATER_THAN, Criteria::LESS_THAN` 362 `>=, <=` | `Criteria::GREATER_EQUAL, Criteria::LESS_EQUAL` 363 `IS NULL, IS NOT NULL` | `Criteria::ISNULL, Criteria::ISNOTNULL` 364 `LIKE, ILIKE` | `Criteria::LIKE, Criteria::ILIKE` 365 `IN, NOT IN` | `Criteria::IN, Criteria::NOT_IN` 366 **Other SQL Keywords** | 367 `ORDER BY column ASC` | `->addAscendingOrderByColumn(column);` 368 `ORDER BY column DESC` | `->addDescendingOrderByColumn(column);` 369 `LIMIT limit` | `->setLimit(limit)` 370 `OFFSET offset` | `->setOffset(offset) ` 371 `FROM table1, table2 WHERE table1.col1 = table2.col2` | `->addJoin(col1, col2)` 372 `FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col2` | `->addJoin(col1, col2, Criteria::LEFT_JOIN)` 373 `FROM table1 RIGHT JOIN table2 ON table1.col1 = table2.col2` | `->addJoin(col1, col2, Criteria::RIGHT_JOIN)` 374 375 >**TIP** 376 >The best way to discover and understand which methods are available in generated classes is to look at the `Base` files in the `lib/model/om/` folder after generation. The method names are pretty explicit, but if you need more comments on them, set the `propel.builder.addComments` parameter to `true` in the `config/propel.ini` file and rebuild the model. 377 378 Listing 8-14 shows another example of `Criteria` with multiple conditions. It retrieves all the comments by Steve on articles containing the word "enjoy," ordered by date. 379 380 Listing 8-14 - Another Example of Retrieving Records by Criteria with `doSelect()`--Criteria with Conditions 381 382 [php] 383 $c = new Criteria(); 384 $c->add(CommentPeer::AUTHOR, 'Steve'); 385 $c->addJoin(CommentPeer::ARTICLE_ID, ArticlePeer::ID); 386 $c->add(ArticlePeer::CONTENT, '%enjoy%', Criteria::LIKE); 387 $c->addAscendingOrderByColumn(CommentPeer::CREATED_AT); 388 $comments = CommentPeer::doSelect($c); 389 390 // Will result in the following SQL query 391 SELECT blog_comment.ID, blog_comment.ARTICLE_ID, blog_comment.AUTHOR, 392 blog_comment.CONTENT, blog_comment.CREATED_AT 393 FROM blog_comment, blog_article 394 WHERE blog_comment.AUTHOR = 'Steve' 395 AND blog_article.CONTENT LIKE '%enjoy%' 396 AND blog_comment.ARTICLE_ID = blog_article.ID 397 ORDER BY blog_comment.CREATED_AT ASC 398 399 Just as SQL is a simple language that allows you to build very complex queries, the Criteria object can handle conditions with any level of complexity. But since many developers think first in SQL before translating a condition into object-oriented logic, the `Criteria` object may be difficult to comprehend at first. The best way to understand it is to learn from examples and sample applications. The symfony project website, for instance, is full of `Criteria` building examples that will enlighten you in many ways. 400 401 In addition to the `doSelect()` method, every peer class has a `doCount()` method, which simply counts the number of records satisfying the criteria passed as a parameter and returns the count as an integer. As there is no object to return, the hydrating process doesn't occur in this case, and the `doCount()` method is faster than `doSelect()`. 402 403 The peer classes also provide `doDelete()`, `doInsert()`, and `doUpdate()` methods, which all expect a `Criteria` as a parameter. These methods allow you to issue `DELETE`, `INSERT`, and `UPDATE` queries to your database. Check the generated peer classes in your model for more details on these Propel methods. 404 405 Finally, if you just want the first object returned, replace `doSelect()` with a `doSelectOne()` call. This may be the case when you know that a `Criteria` will return only one result, and the advantage is that this method returns an object rather than an array of objects. 406 407 >**TIP** 408 >When a `doSelect()` query returns a large number of results, you might want to display only a subset of it in your response. Symfony provides a pager class called sfPropelPager, which automates the pagination of results. Check the API documentation at [http://www.symfony-project.com/api/symfony.html](http://www.symfony-project.com/api/symfony.html) for more information and usage examples. 409 410 ### Using Raw SQL Queries 411 412 Sometimes, you don't want to retrieve objects, but want to get only synthetic results calculated by the database. For instance, to get the latest creation date of all articles, it doesn't make sense to retrieve all the articles and to loop on the array. You will prefer to ask the database to return only the result, because it will skip the object hydrating process. 413 414 On the other hand, you don't want to call the PHP commands for database management directly, because then you would lose the benefit of database abstraction. This means that you need to bypass the ORM (Propel) but not the database abstraction (Creole). 415 416 Querying the database with Creole requires that you do the following: 417 418 1. Get a database connection. 419 2. Build a query string. 420 3. Create a statement out of it. 421 4. Iterate on the result set that results from the statement execution. 422 423 If this looks like gibberish to you, the code in Listing 8-15 will probably be more explicit. 424 425 Listing 8-15 - Custom SQL Query with Creole 426 427 [php] 428 $connection = Propel::getConnection(); 429 $query = 'SELECT MAX(%s) AS max FROM %s'; 430 $query = sprintf($query, ArticlePeer::CREATED_AT, ArticlePeer::TABLE_NAME); 431 $statement = $connnection->prepareStatement($query); 432 $resultset = $statement->executeQuery(); 433 $resultset->next(); 434 $max = $resultset->getInt('max'); 435 436 Just like Propel selections, Creole queries are tricky when you first start using them. Once again, examples from existing applications and tutorials will show you the right way. 437 438 >**CAUTION** 439 >If you are tempted to bypass this process and access the database directly, you risk losing the security and abstraction provided by Creole. Doing it the Creole way is longer, but it forces you to use good practices that guarantee the performance, portability, and security of your application. This is especially true for queries that contain parameters coming from a untrusted source (such as an Internet user). Creole does all the necessary escaping and secures your database. Accessing the database directly puts you at risk of SQL-injection attacks. 440 441 ### Using Special Date Columns 442 443 Usually, when a table has a column called `created_at`, it is used to store a timestamp of the date when the record was created. The same applies to updated_at columns, which are to be updated each time the record itself is updated, to the value of the current time. 444 445 The good news is that symfony will recognize the names of these columns and handle their updates for you. You don't need to manually set the `created_at` and `updated_at` columns; they will automatically be updated, as shown in Listing 8-16. The same applies for columns named `created_on` and `updated_on`. 446 447 Listing 8-16 - `created_at` and `updated_at` Columns Are Dealt with Automatically 448 449 [php] 450 $comment = new Comment(); 451 $comment->setAuthor('Steve'); 452 $comment->save(); 453 454 // Show the creation date 455 echo $comment->getCreatedAt(); 456 => [date of the database INSERT operation] 457 458 Additionally, the getters for date columns accept a date format as an argument: 459 460 [php] 461 echo $comment->getCreatedAt('Y-m-d'); 462 463 >**SIDEBAR** 464 >Refactoring to the Data layer 465 > 466 >When developing a symfony project, you often start by writing the domain logic code in the actions. But the database queries and model manipulation should not be stored in the controller layer. So all the logic related to the data should be moved to the model layer. Whenever you need to do the same request in more than one place in your actions, think about transferring the related code to the model. It helps to keep the actions short and readable. 467 > 468 >For example, imagine the code needed in a blog to retrieve the ten most popular articles for a given tag (passed as request parameter). This code should not be in an action, but in the model. In fact, if you need to display this list in a template, the action should simply look like this: 469 > 470 > [php] 471 > public function executeShowPopularArticlesForTag() 472 > { 473 > $tag = TagPeer::retrieveByName($this->getRequestParameter('tag')); 474 > $this->foward404Unless($tag); 475 > $this->articles = $tag->getPopularArticles(10); 476 > } 477 > 478 >The action creates an object of class `Tag` from the request parameter. Then all the code needed to query the database is located in a `getPopularArticles()` method of this class. It makes the action more readable, and the model code can easily be reused in another action. 479 > 480 >Moving code to a more appropriate location is one of the techniques of refactoring. If you do it often, your code will be easy to maintain and to understand by other developers. A good rule of thumb about when to do refactoring to the data layer is that the code of an action should rarely contain more than ten lines of PHP code. 481 482 Database Connections 483 -------------------- 484 485 The data model is independent from the database used, but you will definitely use a database. The minimum information required by symfony to send requests to the project database is the name, the access codes, and the type of database. These connection settings should be entered in the `databases.yml` file located in the `config/` directory. Listing 8-17 shows an example of such a file. 486 487 Listing 8-17 - Sample Database Connection Settings, in `myproject/config/databases.yml` 488 489 prod: 490 propel: 491 param: 492 host: mydataserver 493 username: myusername 494 password: xxxxxxxxxx 495 496 all: 497 propel: 498 class: sfPropelDatabase 499 param: 500 phptype: mysql # Database vendor 501 hostspec: localhost 502 database: blog 503 username: login 504 password: passwd 505 port: 80 506 encoding: utf-8 # Default charset for table creation 507 persistent: true # Use persistent connections 508 509 The connection settings are environment-dependent. You can define distinct settings for the `prod`, `dev`, and `test` environments, or any other environment in your application. This configuration can also be overridden per application, by setting different values in an application-specific file, such as in `apps/myapp/config/databases.yml`. For instance, you can use this approach to have different security policies for a front-end and a back-end application, and define several database users with different privileges in your database to handle this. 510 511 For each environment, you can define many connections. Each connection refers to a schema being labeled with the same name. In the example in Listing 8-17, the propel connection refers to the `propel` schema in Listing 8-3. 512 513 The permitted values of the `phptype` parameter are the ones of the database systems supported by Creole: 514 515 * `mysql` 516 * `sqlserver` 517 * `pgsql` 518 * `sqlite` 519 * `oracle` 520 521 `hostspec`, `database`, `username`, and `password` are the usual database connection settings. They can also be written in a shorter way as a data source name (DSN). Listing 8-18 is equivalent to the `all:` section of Listing 8-17. 522 523 Listing 8-18 - Shorthand Database Connection Settings 524 525 all: 526 propel: 527 class: sfPropelDatabase 528 param: 529 dsn: mysql://login:passwd@localhost/blog 530 531 If you use a SQLite database, the `hostspec` parameter must be set to the path of the database file. For instance, if you keep your blog database in `data/blog.db`, the `databases.yml` file will look like Listing 8-19. 532 533 Listing 8-19 - Database Connection Settings for SQLite Use a File Path As Host 534 535 all: 536 propel: 537 class: sfPropelDatabase 538 param: 539 phptype: sqlite 540 database: %SF_DATA_DIR%/blog.db 541 542 Extending the Model 543 ------------------- 544 545 The generated model methods are great but often not sufficient. As soon as you implement your own business logic, you need to extend it, either by adding new methods or by overriding existing ones. 546 547 ### Adding New Methods 548 549 You can add new methods to the empty model classes generated in the `lib/model/` directory. Use `$this` to call methods of the current object, and use `self::` to call static methods of the current class. Remember that the custom classes inherit methods from the `Base` classes located in the `lib/model/om/` directory. 550 551 For instance, for the `Article` object generated based on Listing 8-3, you can add a magic `__toString()` method so that echoing an object of class `Article` displays its title, as shown in Listing 8-20. 552 553 Listing 8-20 - Customizing the Model, in `lib/model/Article.php` 554 555 [php] 556 <?php 557 558 class Article extends BaseArticle 559 { 560 public function __toString() 561 { 562 return $this->getTitle(); // getTitle() is inherited from BaseArticle 563 } 564 } 565 566 You can also extend the peer classes--for instance, to add a method to retrieve all articles ordered by creation date, as shown in Listing 8-21. 567 568 Listing 8-21 - Customizing the Model, in `lib/model/ArticlePeer.php` 569 570 [php] 571 <?php 572 573 class ArticlePeer extends BaseArticlePeer 574 { 575 public static function getAllOrderedByDate() 576 { 577 $c = new Criteria(); 578 $c->addAscendingOrderByColumn(self:CREATED_AT); 579 return self::doSelect($c); 580 581 } 582 } 583 584 The new methods are available in the same way as the generated ones, as shown in Listing 8-22. 585 586 Listing 8-22 - Using Custom Model Methods Is Like Using the Generated Methods 587 588 [php] 589 foreach (ArticlePeer::getAllOrderedByDate() as $article) 590 { 591 echo $article; // Will call the magic __toString() method 592 } 593 594 ### Overriding Existing Methods 595 596 If some of the generated methods in the `Base` classes don't fit your requirements, you can still override them in the custom classes. Just make sure that you use the same method signature (that is, the same number of arguments). 597 598 For instance, the `$article->getComments()` method returns an array of `Comment` objects, in no particular order. If you want to have the results ordered by creation date, with the latest comment coming first, then override the `getComments()` method, as shown in Listing 8-23. Be aware that the original `getComments()` method (found in `lib/model/om/BaseArticle.php`) expects a criteria value and a connection value as parameters, so your function must do the same. 599 600 Listing 8-23 - Overriding Existing Model Methods, in `lib/model/Article.php` 601 602 [php] 603 public function getComments($criteria = null, $con = null ) 604 { 605 // Objects are passed by reference in PHP5, so to avoid modifying the original, 606 // you must clone it 607 $criteria = clone $criteria; 608 $criteria->addDescendingOrderByColumn(ArticlePeer::CREATED_AT); 609 610 return parent::getComments($criteria, $con); 611 } 612 613 The custom method eventually calls the one of the parent Base class, and that's good practice. However, you can completely bypass it and return the result you want. 614 615 ### Using Model Behaviors 616 617 Some model modifications are generic and can be reused. For instance, methods to make a model object sortable and an optimistic lock to prevent conflicts between concurrent object saving are generic extensions that can be added to many classes. 618 619 Symfony packages these extensions into behaviors. Behaviors are external classes that provide additional methods to model classes. The model classes already contain hooks, and symfony knows how to extend them by way of `sfMixer` (see Chapter 17 for details). 620 621 To enable behaviors in your model classes, you must modify one setting in the `config/propel.ini` file: 622 623 propel.builder.AddBehaviors = true // Default value is false 624 625 There is no behavior bundled by default in symfony, but they can be installed via plug-ins. Once a behavior plug-in is installed, you can assign the behavior to a class with a single line. For instance, if you install the sfPropelParanoidBehaviorPlugin in your application, you can extend an Article class with this behavior by adding the following at the end of the Article.clas`s.php`: 626 627 [php] 628 sfPropelBehavior::add('Article', array( 629 'paranoid' => array('column' => 'deleted_at') 630 )); 631 632 After rebuilding the model, deleted `Article` objects will remain in the database, invisible to the queries using the ORM, unless you temporarily disable the behavior with `sfPropelParanoidBehavior::disable()`. 633 634 Check the list of symfony plug-ins in the wiki to find behaviors ([http://www.symfony-project.com/trac/wiki/SymfonyPlugins#Propelbehaviorplugins](http://www.symfony-project.com/trac/wiki/SymfonyPlugins#Propelbehaviorplugins)). Each has its own documentation and installation guide. 635 636 Extended Schema Syntax 637 ---------------------- 638 639 A `schema.yml` file can be simple, as shown in Listing 8-3. But relational models are often complex. That's why the schema has an extensive syntax able to handle almost every case. 640 641 ### Attributes 642 643 Connections and tables can have specific attributes, as shown in Listing 8-24. They are set under an `_attributes` key. 644 645 Listing 8-24 - Attributes for Connections and Tables 646 647 propel: 648 _attributes: { noXsd: false, defaultIdMethod: none, package: lib.model } 649 blog_article: 650 _attributes: { phpName: Article } 651 652 You may want your schema to be validated before code generation takes place. To do that, deactivate the `noXSD` attribute for the connection. The connection also supports the `defaultIdMethod` attribute. If none is provided, then the database's native method of generating IDs will be used--for example, `autoincrement` for MySQL, or `sequences` for PostgreSQL. The other possible value is `none`. 653 654 The `package` attribute is like a namespace; it determines the path where the generated classes are stored. It defaults to `lib/model/`, but you can change it to organize your model in subpackages. For instance, if you don't want to mix the core business classes and the classes defining a database-stored statistics engine in the same directory, then define two schemas with `lib.model.business` and `lib.model.stats` packages. 655 656 You already saw the `phpName` table attribute, used to set the name of the generated class mapping the table. 657 658 Tables that contain localized content (that is, several versions of the content, in a related table, for internationalization) also take two additional attributes (see Chapter 13 for details), as shown in Listing 8-25. 659 660 Listing 8-25 - Attributes for i18n Tables 661 662 propel: 663 blog_article: 664 _attributes: { isI18N: true, i18nTable: db_group_i18n } 665 666 >**SIDEBAR** 667 >Dealing with multiple schemas 668 > 669 >You can have more than one schema per application. Symfony will take into account every file ending with `schema.yml` or `schema.xml` in the `config/` folder. If your application has many tables, or if some tables don't share the same connection, you will find this approach very useful. 670 > 671 >Consider these two schemas: 672 > 673 > 674 > // In config/business-schema.yml 675 > propel: 676 > blog_article: 677 > _attributes: { phpName: Article } 678 > id: 679 > title: varchar(50) 680 > 681 > // In config/stats-schema.yml 682 > propel: 683 > stats_hit: 684 > _attributes: { phpName: Hit } 685 > id: 686 > resource: varchar(100) 687 > created_at: 688 > 689 > 690 >Both schemas share the same connection (`propel`), and the `Article` and `Hit` classes will be generated under the same `lib/model/` directory. Everything happens as if you had written only one schema. 691 > 692 >You can also have different schemas use different connections (for instance, `propel` and `propel_bis`, to be defined in `databases.yml`) and organize the generated classes in subdirectories: 693 > 694 > 695 > // In config/business-schema.yml 696 > propel: 697 > blog_article: 698 > _attributes: { phpName: Article, package: lib.model.business } 699 > id: 700 > title: varchar(50) 701 > 702 > // In config/stats-schema.yml 703 > propel_bis: 704 > stats_hit: 705 > _attributes: { phpName: Hit, package.lib.model.stat } 706 > id: 707 > resource: varchar(100) 708 > created_at: 709 > 710 > 711 >Many applications use more than one schema. In particular, some plug-ins have their own schema and package to avoid messing with your own classes (see Chapter 17 for details). 712 713 ### Column Details 714 715 The basic syntax gives you two choices: let symfony deduce the column characteristics from its name (by giving an empty value) or define the type with one of the type keywords. Listing 8-26 demonstrates these choices. 716 717 Listing 8-26 - Basic Column Attributes 718 719 propel: 720 blog_article: 721 id: # Let symfony do the work 722 title: varchar(50) # Specify the type yourself 723 724 But you can define much more for a column. If you do, you will need to define column settings as an associative array, as shown in Listing 8-27. 725 726 Listing 8-27 - Complex Column Attributes 727 728 propel: 729 blog_article: 730 id: { type: integer, required: true,primaryKey: true, autoIncrement: true } 731 name: { type: varchar(50), default: foobar, index: true } 732 group_id: { type: integer, foreignTable: db_group,foreignReference: id, onDelete: cascade } 733 734 The column parameters are as follows: 735 736 * `type`: Column type. The choices are `boolean`, `tinyint`, `smallint`, `integer`, `bigint`, `double`, `float`, `real`, `decimal`, `char`, `varchar(size)`, `longvarchar`, `date`, `time`, `timestamp`, `bu_date`, `bu_timestamp`, `blob`, and `clob`. 737 * `required`: Boolean. Set it to `true` if you want the column to be required. 738 * `default`: Default value. 739 * `primaryKey`: Boolean. Set it to `true` for primary keys. 740 * `autoIncrement`: Boolean. Set it to `true` for columns of type `integer` that need to take an auto-incremented value. 741 * `sequence`: Sequence name for databases using sequences for `autoIncrement` columns (for example, PostgreSQL and Oracle). 742 * `index`: Boolean. Set it to `true` if you want a simple index or to `unique` if you want a unique index to be created on the column. 743 * `foreignTable`: A table name, used to create a foreign key to another table. 744 * `foreignReference`: The name of the related column if a foreign key is defined via `foreignTable`. 745 * `onDe`lete: Determines the action to trigger when a record in a related table is deleted. When set to `setnull`, the foreign key column is set to `null`. When set to `cascade`, the record is deleted. If the database engine doesn't support the set behavior, the ORM emulates it. This is relevant only for columns bearing a `foreignTable` and a `foreignReference`. 746 * `isCulture`: Boolean. Set it to `true` for culture columns in localized content tables (see Chapter 13). 747 748 ### Foreign Keys 749 750 As an alternative to the `foreignTable` and `foreignReference` column attributes, you can add foreign keys under the `_foreignKeys:` key in a table. The schema in Listing 8-28 will create a foreign key on the `user_id` column, matching the `id` column in the `blog_user` table. 751 752 Listing 8-28 - Foreign Key Alternative Syntax 753 754 propel: 755 blog_article: 756 id: 757 title: varchar(50) 758 user_id: { type: integer } 759 _foreignKeys: 760 - 761 foreignTable: blog_user 762 onDelete: cascade 763 references: 764 - { local: user_id, foreign: id } 765 766 The alternative syntax is useful for multiple-reference foreign keys and to give foreign keys a name, as shown in Listing 8-29. 767 768 Listing 8-29 - Foreign Key Alternative Syntax Applied to Multiple Reference Foreign Key 769 770 _foreignKeys: 771 my_foreign_key: 772 foreignTable: db_user 773 onDelete: cascade 774 references: 775 - { local: user_id, foreign: id } 776 - { local: post_id, foreign: id } 777 778 ### Indexes 779 780 As an alternative to the `index` column attribute, you can add indexes under the `_indexes:` key in a table. If you want to define unique indexes, you must use the `_uniques:` header instead. Listing 8-30 shows the alternative syntax for indexes. 781 782 Listing 8-30 - Indexes and Unique Indexes Alternative Syntax 783 784 propel: 785 blog_article: 786 id: 787 title: varchar(50) 788 created_at: 789 _indexes: 790 my_index: [title, user_id] 791 _uniques: 792 my_other_index: [created_at] 793 794 The alternative syntax is useful only for indexes built on more than one column. 795 796 ### Empty Columns 797 798 When meeting a column with no value, symfony will do some magic and add a value of its own. See Listing 8-31 for the details added to empty columns. 799 800 Listing 8-31 - Column Details Deduced from the Column Name 801 802 // Empty columns named id are considered primary keys 803 id: { type: integer, required: true, primaryKey: true, autoIncrement: true } 804 805 // Empty columns named XXX_id are considered foreign keys 806 foobar_id: { type: integer, foreignTable: db_foobar, foreignReference: id } 807 808 // Empty columns named created_at, updated at, created_on and updated_on 809 // are considered dates and automatically take the timestamp type 810 created_at: { type: timestamp } 811 updated_at: { type: timestamp } 812 813 For foreign keys, symfony will look for a table having the same `phpName` as the beginning of the column name, and if one is found, it will take this table name as the `foreignTable`. 814 815 ### I18n Tables 816 817 Symfony supports content internationalization in related tables. This means that when you have content subject to internationalization, it is stored in two separate tables: one with the invariable columns and another with the internationalized columns. 818 819 In a `schema.yml` file, all that is implied when you name a table `foobar_i18n`. For instance, the schema shown in Listing 8-32 will be automatically completed with columns and table attributes to make the internationalized content mechanism work. Internally, symfony will understand it as if it were written like Listing 8-33. Chapter 13 will tell you more about i18n. 820 821 Listing 8-32 - Implied i18n Mechanism 822 823 propel: 824 db_group: 825 id: 826 created_at: 827 828 db_group_i18n: 829 name: varchar(50) 830 831 Listing 8-33 - Explicit i18n Mechanism 832 833 propel: 834 db_group: 835 _attributes: { isI18N: true, i18nTable: db_group_i18n } 836 id: 837 created_at: 838 839 db_group_i18n: 840 id: { type: integer, required: true, primaryKey: true,foreignTable: db_group, foreignReference: id, onDelete: cascade } 841 culture: { isCulture: true, type: varchar(7), required: true,primaryKey: true } 842 name: varchar(50) 843 844 ### Beyond the schema.yml: The schema.xml 845 846 As a matter of fact, the schema.yml format is internal to symfony. When you call a propel- command, symfony actually translates this file into a `generated-schema.xml` file, which is the type of file expected by Propel to actually perform tasks on the model. 847 848 The `schema.xml` file contains the same information as its YAML equivalent. For example, Listing 8-3 is converted to the XML file shown in Listing 8-34. 849 850 Listing 8-34 - Sample `schema.xml`, Corresponding to Listing 8-3 851 852 [xml] 853 <?xml version="1.0" encoding="UTF-8"?> 854 <database name="propel" defaultIdMethod="native" noXsd="true" package="lib.model"> 855 <table name="blog_article" phpName="Article"> 856 <column name="id" type="integer" required="true" primaryKey="true"autoIncrement="true" /> 857 <column name="title" type="varchar" size="255" /> 858 <column name="content" type="longvarchar" /> 859 <column name="created_at" type="timestamp" /> 860 </table> 861 <table name="blog_comment" phpName="Comment"> 862 <column name="id" type="integer" required="true" primaryKey="true"autoIncrement="true" /> 863 <column name="article_id" type="integer" /> 864 <foreign-key foreignTable="blog_article"> 865 <reference local="article_id" foreign="id"/> 866 </foreign-key> 867 <column name="author" type="varchar" size="255" /> 868 <column name="content" type="longvarchar" /> 869 <column name="created_at" type="timestamp" /> 870 </table> 871 </database> 872 873 The description of the `schema.xml` format can be found in the documentation and the "Getting Started" sections of the Propel project website ([http://propel.phpdb.org/docs/user_guide/chapters/appendices/AppendixB-SchemaReference.html](http://propel.phpdb.org/docs/user_guide/chapters/appendices/AppendixB-SchemaReference.html)). 874 875 The YAML format was designed to keep the schemas simple to read and write, but the trade-off is that the most complex schemas can't be described with a `schema.yml` file. On the other hand, the XML format allows for full schema description, whatever its complexity, and includes database vendor-specific settings, table inheritance, and so on. 876 877 Symfony actually understands schemas written in XML format. So if your schema is too complex for the YAML syntax, if you have an existing XML schema, or if you are already familiar with the Propel XML syntax, you don't have to switch to the symfony YAML syntax. Place your `schema.xml` in the project `config/` directory, build the model, and there you go. 878 879 >**SIDEBAR** 880 >Propel in symfony 881 > 882 >All the details given in this chapter are not specific to symfony, but rather to Propel. Propel is the preferred object/relational abstraction layer for symfony, but you can choose an alternative one. However, symfony works more seamlessly with Propel, for the following reasons: 883 > 884 >All the object data model classes and the `Criteria` class are autoloading classes. As soon as you use them, symfony will include the right files, and you don't need to manually add the file inclusion statements. In symfony, Propel doesn't need to be launched nor initialized. When an object uses Propel, the library initiates by itself. Some symfony helpers use Propel objects as parameters to achieve high-level tasks (such as pagination or filtering). Propel objects allow rapid prototyping and generation of a backend for your application (Chapter 14 provides more details). The schema is faster to write through the `schema.yml` file. 885 > 886 >And, as Propel is independent of the database used, so is symfony. 887 888 Don't Create the Model Twice 889 ---------------------------- 890 891 The trade-off of using an ORM is that you must define the data structure twice: once for the database, and once for the object model. Fortunately, symfony offers command-line tools to generate one based on the other, so you can avoid duplicate work. 892 893 ### Building a SQL Database Structure Based on an Existing Schema 894 895 If you start your application by writing the `schema.yml` file, symfony can generate a SQL query that creates the tables directly from the YAML data model. To use the query, go to your root project directory and type this: 896 897 > symfony propel-build-sql 898 899 A `lib.model.schema.sql` file will be created in `myproject/data/sql/`. Note that the generated SQL code will be optimized for the database system defined in the `phptype` parameter of the `propel.ini` file. 900 901 You can use the schema.sql file directly to build the tables. For instance, in MySQL, type this: 902 903 > mysqladmin -u root -p create blog 904 > mysql -u root -p blog < data/sql/lib.model.schema.sql 905 906 The generated SQL is also helpful to rebuild the database in another environment, or to change to another DBMS. If the connection settings are properly defined in your `propel.ini`, you can even use the `symfony propel-insert-sql` command to do this automatically. 907 908 >**TIP** 909 >The command line also offers a task to populate your database with data based on a text file. See Chapter 16 for more information about the `propel-load-data` task and the YAML fixture files. 910 911 ### Generating a YAML Data Model from an Existing Database 912 913 Symfony can use the Creole database access layer to generate a `schema.yml` file from an existing database, thanks to introspection (the capability of databases to determine the structure of the tables on which they are operating). This can be particularly useful when you do reverse-engineering, or if you prefer working on the database before working on the object model. 914 915 In order to do this, you need to make sure that the project `propel.ini` file points to the correct database and contains all connection settings, and then call the `propel-build-schema` command: 916 917 > symfony propel-build-schema 918 919 A brand-new `schema.yml` file built from your database structure is generated in the `config/` directory. You can build your model based on this schema. 920 921 The schema-generation command is quite powerful and can add a lot of database-dependent information to your schema. As the YAML format doesn't handle this kind of vendor information, you need to generate an XML schema to take advantage of it. You can do this simply by adding an `xml` argument to the `build-schema` task: 922 923 > symfony propel-build-schema xml 924 925 Instead of generating a `schema.yml` file, this will create a `schema.xml` file fully compatible with Propel, containing all the vendor information. But be aware that generated XML schemas tend to be quite verbose and difficult to read. 926 927 >**SIDEBAR** 928 >The propel.ini configuration 929 > 930 >The `propel-build-sql` and `propel-build-schema` tasks don't use the connection settings defined in the `databases.yml` file. Rather, these tasks use the connection settings in another file, called `propel.ini` and stored in the project `config/` directory: 931 > 932 > 933 > propel.database.createUrl = mysql://login:passwd@localhost 934 > propel.database.url = mysql://login:passwd@localhost/blog 935 > 936 > 937 >This file contains other settings used to configure the Propel generator to make generated model classes compatible with symfony. Most settings are internal and of no interest to the user, apart from a few: 938 > 939 > 940 > // Base classes are autoloaded in symfony 941 > // Set this to true to use include_once statements instead 942 > // (Small negative impact on performance) 943 > propel.builder.addIncludes = false 944 > 945 > // Generated classes are not commented by default 946 > // Set this to true to add comments to Base classes 947 > // (Small negative impact on performance) 948 > propel.builder.addComments = false 949 > 950 > // Behaviors are not handled by default 951 > // Set this to true to be able to handle them 952 > propel.builder.AddBehaviors = false 953 > 954 > 955 >After you make a modification to the `propel.ini` settings, don't forget to rebuild the model so the changes will take effect. 956 957 Summary 958 ------- 959 960 Symfony uses Propel as the ORM and Creole as the database abstraction layer. It means that you must first describe the relational schema of your database in YAML before generating the object model classes. Then, at runtime, use the methods of the object and peer classes to retrieve information about a record or a set of records. You can override them and extend the model easily by adding methods to the custom classes. The connection settings are defined in a `databases.yml` file, which can support more than one connection. And the command line contains special tasks to avoid duplicate structure definition. 961 962 The model layer is the most complex of the symfony framework. One reason for this complexity is that data manipulation is an intricate matter. The related security issues are crucial for a website and should not be ignored. Another reason is that symfony is more suited for middle- to large-scale applications in an enterprise context. In such applications, the automations provided by the symfony model really represent a gain of time, worth the investment in learning its internals. 963 964 So don't hesitate to spend some time testing the model objects and methods to fully understand them. The solidity and scalability of your applications will be a great reward.
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Fri Mar 16 22:42:14 2007 | par Balluche grâce à PHPXref 0.7 |