DOWNLOAD
 Oracle Database 11g
 Symfony
   TAGS
php, database, All
DBA: PHP

Oracle Symfony in PHP Minor

by Mladen Gogala

Oracle Database 11g and the Symfony Web PHP Framework can make beautiful music together, as illustrated by this simple sample app.

Published July 2008

Symfony is a very popular model-view-controller (MVC) framework for creating Web applications quickly and efficiently. Like many such tools, it uses an object-relational mapper (ORM) called Propel. In my work as a DBA, I was recently asked to modify an application that was written using the Symfony framework and whose author has since left the company. At the time, I was the only resource in the company who knew both PHP and Oracle, so I was the natural choice—despite being a DBA.

To make a long story short, learning Symfony wasn't a problem, and the framework delighted me with its simplicity, versatility, and ease of use. Where should someone who wants to learn Symfony begin? Project Symfony has a very informative and well-kept Web site, with extensive documentation. As it turns out, there is a book about Symfony and there are also numerous tutorials—unfortunately all of them created using a MySQL database. That is, in essence, why I decided to write this article about Symfony and Oracle. It's not a full-blown tutorial, but I hope it will be helpful to those who want to use Symfony with Oracle.

Next, I will create a small Web application using Oracle Database 11g and Symfony. The application will access the ubiquitous SCOTT schema and equally famous EMP and DEPT tables. The application will be fully functional, if somewhat inane.

Basics

Symfony is an MVC tool that uses an ORM called Propel. That means that Symfony will do the following for you, during the course of developing the application:

There are three common deployment topologies:

  • Map Oracle relational database management system tables to PHP5 classes. It will also create basic create, retrieve, update, and delete (CRUD) methods.
  • Create modules that will enable us to view and control the data, using the CRUD methods created by Propel.

The need for programming will be minimal, although one should know both PHP5 and Oracle before venturing into PHP5 and Oracle application development.

As with any Web application, you also need a Web server. The Web server used for this article is Apache: httpd-2.2.3-11.el5_1.centos.3. The application software and the test Oracle Database 11g were both installed on Centos 5.0 for its similarity to Red Hat Linux. PHP5 supports all major versions currently in use. One can easily develop on Oracle Database 11g and deploy it on Oracle Database 10g. I must say, however, that Oracle Database 11g is far better suited for Web applications than Oracle Database 10g because of the Result Cache feature that allows the results of an already-executed query to be reused without the need for going through the ropes of reparsing and executing the query.

In order to work with PHP5, the corresponding Apache module must be linked and installed. PHP4 is not supported by Symfony. An easy way to install PHP5 with all relevant modules is to download and install Zend Core for Oracle.

Symfony, of course, needs to be installed. This article will not deal with the Symfony installation in detail, as it is perfectly explained on the product Web site. If suffices to say that Symfony is installed using the standard PEAR utility. The latest stable version of Symfony is 1.1.0.

So, how does Symfony work? Symfony is an application generator, so there is a descriptive element in it. You first have to describe Oracle objects, then generate an application on top of those objects and, eventually, modify the application to suit our needs. For each step, Symfony provides an easy way.

Describing the Objects

In order to start with describing an Oracle schema to Symfony, you first need to describe the Oracle instance used to access the schema. The component that does the trick is Propel, an ORM that maps relational object descriptions into object classes. For each table, a class is generated and all the CRUD are generated for that class. The language used to describe the components to Symfony (and Propel, of course) is called YAML,which is a simplified version of XML and has recently gained popularity with the users of scripting languages like PHP and Perl. YAML syntax is extremely simple, forgiving, intuitive, and easy to use.

Without further ado, let me start with our project. The first step is to create the project directory and initialize it by creating subdirectory structure, using the command symfony init-proj test_proj:

mgogala@TheBox> mkdir test_proj 
mgogala@TheBox> cd test_proj 
mgogala@TheBox> symfony init-proj test_proj 
>> dir+   /home/mgogala/test_proj/doc 
>> dir+   /home/mgogala/test_proj/log 
>> dir+   /home/mgogala/test_proj/test 
>> dir+   /home/mgogala/test_proj/test/functional 
>> dir+   /home/mgogala/test_proj/test/bootstrap 
>> file+   /home/mgogala/test_proj/test/bootstrap/functional.php 
>> file+   /home/mgogala/test_proj/test/bootstrap/unit.php 
>> dir+   /home/mgogala/test_proj/test/unit 
>> dir+   /home/mgogala/test_proj/config 
>> file+   /home/mgogala/test_proj/config/config.php 
>> file+   /home/mgogala/test_proj/config/databases.yml 
>> file+   /home/mgogala/test_proj/config/properties.ini 
>> file+   /home/mgogala/test_proj/config/rsync_exclude.txt 
>> file+   /home/mgogala/test_proj/config/schema.yml 
>> file+   /home/mgogala/test_proj/config/propel.ini 
>> dir+   /home/mgogala/test_proj/cache 
>> dir+   /home/mgogala/test_proj/plugins 
>> dir+   /home/mgogala/test_proj/data 
>> dir+   /home/mgogala/test_proj/data/model 
>> dir+   /home/mgogala/test_proj/data/sql 
>> file+   /home/mgogala/test_proj/symfony 
>> dir+   /home/mgogala/test_proj/batch 
>> dir+   /home/mgogala/test_proj/web 
>> dir+   /home/mgogala/test_proj/web/images 
>> file+   /home/mgogala/test_proj/web/.htaccess 
>> dir+   /home/mgogala/test_proj/web/js 
>> file+   /home/mgogala/test_proj/web/robots.txt 
>> dir+   /home/mgogala/test_proj/web/uploads 
>> dir+   /home/mgogala/test_proj/web/uploads/assets 
>> dir+   /home/mgogala/test_proj/web/css 
>> file+   /home/mgogala/test_proj/web/css/main.css 
>> dir+   /home/mgogala/test_proj/lib 
>> dir+   /home/mgogala/test_proj/lib/model 
>> dir+   /home/mgogala/test_proj/apps 
>> tokens  /home/mgogala/test_proj/config/properties.ini 
>> tokens  /home/mgogala/test_proj/config/propel.ini 
>> tokens  /home/mgogala/test_proj/config/propel.ini 
>> tokens  /home/mgogala/test_proj/config/config.php 
>> chmod 777 /home/mgogala/test_proj/cache 
>> chmod 777 /home/mgogala/test_proj/log 
>> chmod 777 /home/mgogala/test_proj/web/uploads 
>> chmod 777 /home/mgogala/test_proj/symfony 
>> chmod 777 web/uploads/assets 
mgogala@TheBox> 
The first subdirectory that needs our attention is, of course, config. Important files on the config directory are databases.yml, propel.ini, are schema.yml.

The first file, databases.yml, tells Symfony the location of the Oracle instance it needs to connect to. The file looks like this:

all: 
 propel: 
  class:   sfPropelDatabase 
  param: 
   phptype: oracle 
   host:   localhost 
   database: test11
   username: scott 
   password: tiger 
This file is self-explanatory and can be used as a template. We also need a description file for Propel, our designated ORM. The configuration file is called propel.ini and looks like this:
propel.targetPackage    = lib.model 
propel.packageObjectModel = true 
propel.project       = test_proj 
propel.database      = oracle 
propel.database.createUrl = oracle://scott:tiger@localhost/test11
propel.database.url    = oracle://scott:tiger@localhost/test11
propel.addGenericAccessors = true 
propel.addGenericMutators = true 
propel.addTimeStamp    = false 

propel.schema.validate   = false 

; directories 
propel.home          = . 
propel.output.dir       = /home/mgogala/test_proj 
propel.schema.dir       = ${propel.output.dir}/config 
propel.conf.dir        = ${propel.output.dir}/config 
propel.phpconf.dir       = ${propel.output.dir}/config 
propel.sql.dir         = ${propel.output.dir}/data/sql 
propel.runtime.conf.file    = runtime-conf.xml 
propel.php.dir         = ${propel.output.dir} 
propel.default.schema.basename = schema 
.....
This file is very similar to the first one and it configures the Propel connection to the database. The last important file here is schema.yml, which describes the Oracle schema. In our case, this file looks like this:
propel: 
 _attributes: 
  package: lib.model 
 emp: 
  empno: 
   type: integer 
   size: 22 
   primaryKey: true 
   autoIncrement: true 
  ename: 
   type: varchar(10) 
   size: 10 
  job: 
   type: varchar(9) 
   size: 9 
  mgr: 
   type: integer 
   size: 22 
   foreignTable: emp 
   foreignReference: empno 
  hiredate: 
   type: date 
   size: 7 
  sal: 
   type: numeric 
   size: 22 
  comm: 
   type: numeric 
   size: 22 
  deptno: 
   type: integer 
   size: 22 
   foreignTable: dept 
   foreignReference: deptno 
   onDelete: CASCADE 
  _indexes: 
   emp_deptno_i: 
    deptno 
   emp_mgr_i: 
    mgr 
 dept: 
  deptno: 
  deptno: 
   type: integer 
   size: 22 
   primaryKey: true 
   autoIncrement: true 
  dname: 
   type: varchar(14) 
   size: 14 
  loc: 
   type: varchar(13) 
   size: 13 
This file, quite obviously, contains a description of tables and columns that we need to build our application. The autoIncrement attribute for the primary key columns will result in creation of sequences to implement the autoincrement feature, natively present in some other databases. Obviously, this attribute makes sense only in the case of a numeric primary key. Symfony can use information about the foreign key to automatically build a list of values. With this comes one rather significant limitation: Symfony doesn't yet support multicolumn foreign keys. For each primary key, Symfony defines a method of fetching the data by the virtue of the primary key. It is therefore important to include primary keys in the schema description.

A little Perl script that can help with generation of schema.yml can be downloaded here. The script itself is extremely simple and is used like this:

symfony_yaml -u scott/tiger@test11 -t "emp,dept" -f /tmp/schema.yml
The result is the file presented above. Web applications rarely use great multitudes of tables; they usually access three or four. It is easy to list them for the -t flag. Help is also provided:
mg> symfony_yaml -h 
symfony_yaml -> Produce YAML description of given tables for Symfony 
        app. generator. The output can be used as "schema.yml" 
        configuration file. 
   USAGE:symfony_yaml -u=<user> -p=<passwd> -t <table1,table2,..> 
        -f <output file> 
   OPTIONS: -u Oracle username (standard u/p@d syntax is supported) 
        -p Password for the above. 
        -d Database to connect to. 
        -t Tables to describe. 
        -ns Do not generate "autoIncrement: true" for the primary key. 
        -f Output file. 
        -h This screen. 
      
   ------- 
   Username and password are mandatory arguments. Default 
   for the output file is standard output. For help, 
   try -help or -h. 
Now that our description files are ready, Propel object-mapping the relational database management system tables into object classes can be generated by issuing the command symfony propel-build-model:
mgogala@TheBox> cd ~/test_proj/ 
mgogala@TheBox> symfony propel-build-model 
>> schema  converting "/home/mgogala/test_proj/config/schema.yml" to XML 
>> schema  putting /home/mgogala/test_proj/config/generated-schema.xml 
Buildfile: /usr/local/lib/php/symfony/vendor/propel-generator/build.xml 
[resolvepath] Resolved /home/mgogala/test_proj/config to /home/mgogala/test_proj/config 

propel-project-builder > check-project-or-dir-set: 

propel-project-builder > check-project-set: 

propel-project-builder > set-project-dir: 

propel-project-builder > check-buildprops-exists: 

propel-project-builder > check-buildprops-for-propel-gen: 

propel-project-builder > check-buildprops: 

propel-project-builder > configure: 
   [echo] Loading project-specific props from /home/mgogala/test_proj/config/propel.ini 
 [property] Loading /home/mgogala/test_proj/config/propel.ini 

propel-project-builder > om: 
  [phing] Calling Buildfile '/usr/local/lib/php/symfony/vendor/propel-generator/build-propel.xml' with target 'om' 
 [property] Loading /usr/local/lib/php/symfony/vendor/propel-generator/./default.properties 

propel > check-run-only-on-schema-change: 

propel > om-check: 

propel > om: 
   [echo] +------------------------------------------+ 
   [echo] |                     | 
   [echo] | Generating Peer-based Object Model for  | 
   [echo] | YOUR Propel project! (NEW OM BUILDERS)! | 
   [echo] |                     | 
   [echo] +------------------------------------------+ 
[phingcall] Calling Buildfile '/usr/local/lib/php/symfony/vendor/propel-generator/build-propel.xml' with target 'om-template' 
 [property] Loading /usr/local/lib/php/symfony/vendor/propel-generator/./default.properties 

propel > om-template: 
[propel-om] Target database type: oracle 
[propel-om] Target package: lib.model 
[propel-om] Using template path: /usr/local/lib/php/symfony/vendor/propel-generator/templates 
[propel-om] Output directory: /home/mgogala/test_proj 
[propel-om] Processing: generated-schema.xml 
[propel-om] Processing Datamodel : JoinedDataModel 
[propel-om]  - processing database : propel 
[propel-om]   + emp 
[propel-om]       -> BaseEmpPeer [builder: SfPeerBuilder] 
[propel-om]       -> BaseEmp [builder: SfObjectBuilder] 
[propel-om]       -> EmpMapBuilder [builder: SfMapBuilderBuilder] 
[propel-om]       -> EmpPeer [builder: SfExtensionPeerBuilder] 
[propel-om]       -> Emp [builder: SfExtensionObjectBuilder] 
[propel-om]   + dept 
[propel-om]       -> BaseDeptPeer [builder: SfPeerBuilder] 
[propel-om]       -> BaseDept [builder: SfObjectBuilder] 
[propel-om]       -> DeptMapBuilder [builder: SfMapBuilderBuilder] 
[propel-om]       -> DeptPeer [builder: SfExtensionPeerBuilder] 
[propel-om]       -> Dept [builder: SfExtensionObjectBuilder] 

BUILD FINISHED 

Total time: 0.6661 seconds 
>> file-   /home/mgogala/test_proj/config/generated-schema.xml 
mgogala@TheBox> 
The model is generated. If you look carefully at the output, you will see that the first step is to convert the YAML file schema.yml into an XML file, and the last step is to map relational objects into a set of object classes. You can also generate the data definition language needed to create the Oracle objects by issuing the command symfony propel-build-sql.
mgogala@TheBox> symfony propel-build-sql 
>> schema  converting "/home/mgogala/test_proj/config/schema.yml" to XML 
>> schema  putting /home/mgogala/test_proj/config/generated-schema.xml 
Buildfile: /usr/local/lib/php/symfony/vendor/propel-generator/build.xml 
[resolvepath] Resolved /home/mgogala/test_proj/config to /home/mgogala/test_proj/config 

propel-project-builder > check-project-or-dir-set: 

propel-project-builder > check-project-set: 

propel-project-builder > set-project-dir: 

propel-project-builder > check-buildprops-exists: 

propel-project-builder > check-buildprops-for-propel-gen: 

propel-project-builder > check-buildprops: 

propel-project-builder > configure: 
   [echo] Loading project-specific props from /home/mgogala/test_proj/config/propel.ini 
 [property] Loading /home/mgogala/test_proj/config/propel.ini 

propel-project-builder > sql: 
  [phing] Calling Buildfile '/usr/local/lib/php/symfony/vendor/propel-generator/build-propel.xml' with target 'sql' 
 [property] Loading /usr/local/lib/php/symfony/vendor/propel-generator/./default.properties 

propel > check-run-only-on-schema-change: 

propel > sql-check: 

propel > pgsql-quoting-check: 

propel > sql: 
   [echo] +------------------------------------------+ 
   [echo] |                     | 
   [echo] | Generating SQL for YOUR Propel project! | 
   [echo] |                     | 
   [echo] +------------------------------------------+ 
[phingcall] Calling Buildfile '/usr/local/lib/php/symfony/vendor/propel-generator/build-propel.xml' with target 'sql-template' 
 [property] Loading /usr/local/lib/php/symfony/vendor/propel-generator/./default.properties 

propel > sql-template: 
[propel-sql] Processing: generated-schema.xml 
[propel-sql] Writing to SQL file: /home/mgogala/test_proj/data/sql/lib.model.schema.sql 
[propel-sql]  + emp [builder: OracleDDLBuilder] 
[propel-sql]  + dept [builder: OracleDDLBuilder] 

BUILD FINISHED 

Total time: 0.3073 seconds 
>> file-   /home/mgogala/test_proj/config/generated-schema.xml 
mgogala@TheBox> 
The generated file is called /home/mgogala/test_proj/data/sql/lib.model.schema.sql. It looks like this:
/* ----------------------------------------------------------------------- 
  emp 
  ----------------------------------------------------------------------- */ 

DROP TABLE "emp" CASCADE CONSTRAINTS; 

DROP SEQUENCE "emp_SEQ"; 


CREATE TABLE "emp" 
( 
    "empno" NUMBER(22) NOT NULL, 
    "ename" VARCHAR2(10), 
    "job" VARCHAR2(9), 
    "mgr" NUMBER(22), 
    "hiredate" DATE(7), 
    "sal" NUMBER(22), 
    "comm" NUMBER(22), 
    "deptno" NUMBER(22) 
); 
    ALTER TABLE "emp" 
        ADD CONSTRAINT "emp_PK" 
    PRIMARY KEY ("empno"); 
CREATE INDEX "emp_deptno_i" ON "emp" (); 
CREATE INDEX "emp_mgr_i" ON "emp" (); 
CREATE SEQUENCE "emp_SEQ" INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER; 
CREATE INDEX "emp_deptno_i" ON "emp" (); 
CREATE INDEX "emp_mgr_i" ON "emp" (); 

ALTER TABLE "emp" ADD CONSTRAINT "emp_FK_1" FOREIGN KEY ("mgr") REFERENCES "emp" ("empno"); 

ALTER TABLE "emp" ADD CONSTRAINT "emp_FK_2" FOREIGN KEY ("deptno") REFERENCES "dept" ("deptno") ON DELETE CASCADE; 
This script was generated for illustrative purposes only, and one would be ill-advised to run it. Due to an extensive use of double quotes, all table names and column names would be generated in lowercase. In other words, Symfony is not a CASE tool; it isn't meant for developing an Oracle schema from a YAML description file. Also, please note that the generated SQL contains DROP statements, which means that you shouldn't run it on tables that already exist. PHP files that implement mapping are somewhat more complex and will be illustrated a little bit later.

The only practical use of this script is to check whether the Symfony's view of the data model coincides with the real situation. In our case, it does. Applications are suitable for being generated by an application generator only if the underlying application generator can correctly describe the underlying data model. That general principle applies not only to Symfony but also to Django, Ruby on Rails, Jifty, or any other such tool. (If the data model is very complex, professional CASE tools like Oracle Business Process Analysis Suite are required.) Symfony and its open source relatives are good for generating relatively simple applications very quickly, but they cannot replace serious CASE tools needed for things like enterprise resource planning or serious financial modeling. Our schema, however, falls in the "simple" category.

Now that your schema is described, you can generate our application.

Generating Application

First, you should generate the application framework by issuing symfony init-app <app name>. Call the application "frontend."

mgogala@TheBox> symfony init-app frontend 
>> dir+   /home/mgogala/test_proj/apps/frontend/templates 
>> file+   /home/mgogala/test_proj/apps/frontend/templates/layout.php 
>> dir+   /home/mgogala/test_proj/apps/frontend/modules 
>> dir+   /home/mgogala/test_proj/apps/frontend/config 
>> file+   /home/mgogala/test_proj/apps/frontend/config/filters.yml 
>> file+   /home/mgogala/test_proj/apps/frontend/config/view.yml 
>> file+   /home/mgogala/test_proj/apps/frontend/config/config.php 
>> file+   /home/mgogala/test_proj/apps/frontend/config/cache.yml 
>> file+   /home/mgogala/test_proj/apps/frontend/config/settings.yml 
>> file+   /home/mgogala/test_proj/apps/frontend/config/factories.yml 
>> file+   /home/mgogala/test_proj/apps/frontend/config/security.yml 
>> file+   /home/mgogala/test_proj/apps/frontend/config/routing.yml 
>> file+   /home/mgogala/test_proj/apps/frontend/config/logging.yml 
>> file+   /home/mgogala/test_proj/apps/frontend/config/app.yml 
>> file+   /home/mgogala/test_proj/apps/frontend/config/i18n.yml 
>> dir+   /home/mgogala/test_proj/apps/frontend/i18n 
>> dir+   /home/mgogala/test_proj/apps/frontend/lib 
>> file+   /home/mgogala/test_proj/apps/frontend/lib/myUser.class.php 
>> tokens  /home/mgogala/test_proj/apps/frontend/config/settings.yml 
>> file+   /home/mgogala/test_proj/web/frontend.php 
>> tokens  /home/mgogala/test_proj/web/frontend_dev.php 
>> tokens  /home/mgogala/test_proj/web/frontend.php 
>> chmod 777 /home/mgogala/test_proj/cache 
>> chmod 777 /home/mgogala/test_proj/log 
>> chmod 777 /home/mgogala/test_proj/web/uploads 
>> chmod 777 /home/mgogala/test_proj/symfony 
>> chmod 777 web/uploads/assets 
mgogala@TheBox> 
The command was issued from top of the project directory structure, as is the case with all Symfony commands. Now, you have to generate modules. That is done by issuing the command symfony propel-init-admin <app name> <table name> <module name>, for each table (EMP and DEPT, in this case):
mgogala@TheBox> symfony propel-init-admin frontend emp Emp 
>> dir+   /home/mgogala/test_proj/apps/frontend/modules/emp/config 
>> file+   /home/mgogala/test_proj/apps/fr...odules/emp/config/generator.yml 
>> dir+   /home/mgogala/test_proj/apps/frontend/modules/emp/actions 
>> file+   /home/mgogala/test_proj/apps/fr...s/emp/actions/actions.class.php 
>> tokens  /home/mgogala/test_proj/apps/fr...odules/emp/config/generator.yml 
>> tokens  /home/mgogala/test_proj/apps/fr...s/emp/actions/actions.class.php 
mgogala@TheBox> symfony propel-init-admin frontend dept Dept 
>> dir+   /home/mgogala/test_proj/apps/frontend/modules/dept/config 
>> file+   /home/mgogala/test_proj/apps/fr...dules/dept/config/generator.yml 
>> dir+   /home/mgogala/test_proj/apps/frontend/modules/dept/actions 
>> file+   /home/mgogala/test_proj/apps/fr.../dept/actions/actions.class.php 
>> tokens  /home/mgogala/test_proj/apps/fr...dules/dept/config/generator.yml 
>> tokens  /home/mgogala/test_proj/apps/fr.../dept/actions/actions.class.php 
That's it: You have generated a simple application! Now, you can take a look at it by visiting http://localhost/emp/list in your browser. That, of course, is just a beginning. You can set pagination, column names, column formats and even add the search widgets by modifying the module generator configuration file generator.yml for each module. This file resides in the directory, under your project home directory: test_proj/apps/frontend/modules/emp/config. You generated one module per table, thus you have a module emp.

Here is a very simple version: the one used to generate the basic application below:

generator: 
 class:       sfPropelAdminGenerator 
 param: 
  model_class:   Emp 
  theme:      default 
  list: 
    title: Company Employees 
    max_per_page: 7 
    filters: [ ename ] 
    fields: 
      hiredate: { params: date_format='MM/dd/yy' } 
      ename: { name: Employee Name } 
Be careful when modifying generator.yml file; it's hierarchical and indented with spaces. There is a good tutorial for using the admin generator on the project Web site.

Here is what the result looks like:

This is just a basic little application, but now you have something to fiddle with—and it only took five minutes to create it.

Observe that the page title is Company Employees, that the ENAME column is displayed as Employee Name, and that the HIREDATE column has the format MM/DD/YY, just as was specified in the generator.yml file shown above. There are only seven employees on the page and there are links for the next, previous, first, and last pages. Also, the primary key field is clearly marked as links. Clicking on those links will bring up the modification form, which can be used to modify the record in question. There is also a little widget that allows us to search employees by name. The CREATE button provides the INSERT functionality and helps you insert new records.

Modifying the Underlying SQL

Object-relational mappers map SQL statements into object classes. That means that, in order to modify SQL statements issued by our application, we have to modify the code generated by the application generator. Symfony generates the code into the cache subdirectory of the main project directory in the same tidy manner. The actionsof our mapped object classes are defined in the file named actions.class.php, which resides in test_proj/cache/frontend/prod/modules/autoEmp/actions directory—the cachefor the emp module. This file is a PHP script, with class definitions. The methods in these classes can be overridden by using the file provided by Symfony.

The file name is, of course, actions.class.php, but it resides in the application definition tree, in the apps/frontend/modules/emp/actions directory rather then in the cache tree. The supplied file looks like this:

<?php

/**
 * emp actions.
 *
 * @package  test_proj
 * @subpackage emp
 * @author   Your name here
 * @version  SVN: $Id: actions.class.php 2288 2006-10-02 15:22:13Z fabien $
 */
class empActions extends autoempActions
{
}
In other words, it is an empty stub. In order to modify the SQL and alter the behavior, you need to modify this file. Here is how the modified file looks, for our sample application:
<?php
// auto-generated by sfPropelAdmin
// date: 2008/04/11 12:44:09
?>
<?php

/**
 * autoEmp actions.
 *
 * @package  ##PROJECT_NAME##
 * @subpackage autoEmp
 * @author   Fabien Potencier <fabien.potencier@symfony-project.com>
 * @version  SVN: $Id: actions.class.php 7997 2008-03-20 12:29:34Z noel $
 */
class empActions extends autoempActions
{
 public function executeList()
 {
  $this->processSort();

  $this->processFilters();

  $this->filters = $this->getUser()->getAttributeHolder()->getAll('sf_admin/emp/filters');

  // pager
  $this->pager = new sfPropelPager('Emp', 20);
  $c = new Criteria();
   
                              
$c ->addAscendingOrderByColumn(EmpPeer::DEPTNO);
  $this->addSortCriteria($c);
  $this->addFiltersCriteria($c);
  $this->pager->setCriteria($c);
  $this->pager->setPage($this->getRequestParameter('page', 1));
  $this->pager->init();
 }

 public function addFiltersCriteria($c)
 {
  if (isset($this->filters['ename_is_empty']))
  {
   $criterion = $c->getNewCriterion(EmpPeer::ENAME, '');
   $criterion->addOr($c->getNewCriterion(EmpPeer::ENAME, null, Criteria::ISNULL));
   $c->add($criterion);
  }
  else if (isset($this->filters['ename']) && $this->filters['ename'] !== '')
  {
    
                              
$c->add(EmpPeer::ENAME, "ENAME LIKE UPPER('".$this->filters['ename']."%')", Criteria::CUSTOM);
  }
 }
}
                            
The code is copied from the version in the cache tree and modified to provide different behavior. The code shown in bold font sorts the input by deptno and makes sure that the search by ENAME column is case-insensitive. Modifying the Criteriaclass is not as complex as it looks. There are many well-documented functions to enter our own SQL, as I did with the filter class, to modify the sort columns and do many different things. Now, there is only one thing left to do:
cd ~/test_proj; symfony cc
That will clear cache (remove everything from the cache tree) and regenerate it from the apps tree on the next invocation. Voila, our application is ready for now. Referencing the URL http://localhost/emp/list will automatically regenerate the application and override the default behavior by the one above.

Conclusion

Symfony is a large and complex application generator with many options, and describing it in a relatively small article isn't an easy task. Most of the work is done with creating YAML description files, but there certainly are "some assembly required" parts. This tool works perfectly with version control, there are numerous plug-in options, and it is relatively easy to learn and start using quickly. The current version, 1.0.15, has two major limitations: the complexity of the Propel ORM and lack of support for multicolumn foreign keys.

In the near future the Propel ORM in Symfony will be replaced by one called Doctrine. Doctrine was inspired by the Java Hibernate framework and its Hibernate Query Language (HQL).

Symfony version 1.1 is currently in RC1 (first-release candidate) phase. Oracle Database 11g and Symfony are an excellent combination, because together they make it easy to create a high-performance application that will look nice, scale well, and satisfy customer needs with very little effort. I hope you will enjoy using both.


Mladen Gogala is a long-time Oracle DBA and the author of Easy Oracle PHP (2006). He has extensive experience in UNIX scripting, Oracle tuning and UNIX system administration, using every popular dialect of UNIX and Linux. Mladen Gogala is also active on the Usenet Oracle forums where he assists in solving Oracle questions.