Relating to the post http://www.symfony-project.org/forum/index.php/m/32510/?srch=insert+values+propel

I've created tools to generate SQL.

PS. Done with haste and for postgresSQL.

Can be edited to take in array of objects and form a whole SQL but I don't want to keep a large array of objects.

CREATED_AT, UPDATED_AT forced as NOW()

  /**
  * To generate sql values for insert statement
  * 
  * @param  $object           object      the object with relavant data
  * 
  * @return $valueSql         string     the object values string
  */
  public static function getObjectInsertValue($object)
  {
    if(!$object->isNew())
    {
        throw new Exception('ScToolkit::getObjectInsertValue($object) Only usable with new object.');
      return '';
    }
 
    $className = get_class($object);
    $classPeer = get_class($object->getPeer());
    $tableName = "";
    eval('$tableName = '.$classPeer.'::TABLE_NAME;');
    //For postgres insert (postgres need define Primary Key)
    $nextId = 'nextval(\''.$tableName.'_seq\')';
    $phpNameMap = array();
    eval('$phpNameMap = array_flip('.$classPeer.'::getPhpNameMap());');
 
    //For checking primary key
    $object->setPrimaryKey(1);
    $object->resetModified();
    $object->setPrimaryKey(2);
 
    $valueArray = array();
    foreach($phpNameMap as $dataKey=>$objectKey)
    {
      $isPrimaryKey = false;
      $columnName = "";
      eval('$columnName = '.$classPeer.'::'.$dataKey.';');
      if($object->isColumnModified($columnName))
      {
        $isPrimaryKey = true;
      }
      if(!$isPrimaryKey)
      {
        if($dataKey=='CREATED_AT' || $dataKey=='UPDATED_AT')
        {
          $valueArray[] = 'NOW()';
        }
        else
        {
          if(is_null($object->getByName($objectKey)))
          {
            $valueArray[] = 'NULL';
          }
          else
          {
            $data = $object->getByName($objectKey);
            $wrapper = "";
            if(is_string($data))
            {
                $wrapper = "'";
              $data = pg_escape_string($data);
            }
            $valueArray[] = $wrapper.$data.$wrapper;
          }
        }
      }
      else
      {
        //For postgres insert (postgres need define Primary Key)
        eval('$valueArray[] = "'.$nextId.'";');
      }
    }
 
    $valueStr = '('.implode(", ", $valueArray).')';
    return $valueStr;
  }
 
  /**
  * To generate sql insert into for insert statement
  * 
  * @param  $classPeer        string    the object class peer string
  * 
  * @return $intoSql          string    the object insert into string
  */
  public static function getObjectInsertInto($classPeer)
  {
    $classObject = substr($classPeer, 0, -strlen('Peer'));
    $object = null;
    eval('$object = new '.$classObject.'();');
    //For checking primary key
    $object->resetModified();
    $object->setPrimaryKey(1);
 
    $phpNameMap = array();
    eval('$phpNameMap = array_flip('.$classPeer.'::getPhpNameMap());');
 
    $intoArray = array();
    foreach($phpNameMap as $dataKey=>$objectKey)
    {
      $isPrimaryKey = false;
      $columnName = "";
      eval('$columnName = '.$classPeer.'::'.$dataKey.';');
      if($object->isColumnModified($columnName))
      {
        $isPrimaryKey = true;
      }
 
      //For postgres insert (postgres need define Primary Key)        
      //if(!$isPrimaryKey)
      {
        $intoArray[] = $dataKey;
      }
    }
 
    $intoStr = '('.implode(", ", $intoArray).')';
    return $intoStr;
  }
 

How to use.

$inserts = array();
while($creatingObjects)
{
  $object = new ObjectClass();
  $object->setTitle("title");
  $object->setSummary("summary");
  $inserts[] = Toolkit::getObjectInsertValue($object);
}
 
$sql = sprintf('INSERT INTO %s %s', 
    ObjectPeer::TABLE_NAME, Toolkit::getObjectInsertInto('ObjectPeer')) . 
    ' VALUES ' . implode(', ', $inserts);
 
$con = Propel::getConnection();
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
 

The following patch will help you exclude the schema generation of certain tables when running propel-build-schema.

This might come in handy when you use propel-build-schema along with plugin schemas.

edit $sf_symfony_lib_dir/vendor/propel-generator/classes/propel/phing/PropelCreoleTransformTask.php

    protected function createDatabaseNode($dbInfo) {
 
        $this->log("Processing database");
 
        $node = $this->doc->createElement("database");
        $node->setAttribute("name", $dbInfo->getName());
 
        if ($vendorNode = $this->createVendorInfoNode($dbInfo->getVendorSpecificInfo())) {
            $node->appendChild($vendorNode);
        }
 
        global $schema_exclude_pattern;
        $pattern = $schema_exclude_pattern;
 
        $this->log("Exclude pattern : ".$pattern);
        // create and add table nodes
        foreach($dbInfo->getTables() as $table) {
            if (preg_match($pattern,$table->getName()))
            {
                $this->log("Skipping : ".$table->getName()." ( matches exclude pattern )");
                continue;
            }
            $tableNode = $this->createTableNode($table);
            $node->appendChild($tableNode);
        }
 
        return $node;
    }
 

pattern provided via the $schema_exclude_pattern variable which can be set in config.php (kinda ugly but it works)

config.php

<?php
//
// symfony directories
$sf_symfony_lib_dir  = '/bridge/lib/symfony/1.0/lib';
$sf_symfony_data_dir = '/bridge/lib/symfony/1.0/data';
 
// skips schema creation for tables which name matches the following pattern 
// when executing propel-build-schema
$schema_exclude_pattern = "/^sf_guard.*/i";
 

By default, I18n content in database does not support fallback in default culture. This snippet allow you to enable I18n content fallback in order to always have a default value for your texts.

This snippet is sponsored by Dorigo consultants.

Fallback I18n content

To enable the fallback, edit your table object class in lib/model/TableClassName.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Fetch the i18n object for this object culture.
   * 
   * @return     mixed A i18n object
   * @throws     PropelException Any exceptions caught during processing will be
   *     rethrown wrapped into a PropelException.
   */
  public function getCurrentTableClassNameI18n()
  {
    if (!isset($this->current_i18n[$this->culture]))
    {
      $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $this->culture);
      if ($obj) // Test if there is a translation for current culture
      {
        $this->setTableClassNameI18nForCulture($obj, $this->culture);
      }
      else // Create a translation for this culture
      {
        $new_i18n = new TableClassNameI18n();
 
        $default_culture = sfConfig::get('sf_i18n_default_culture');
 
        // We try to fetch the default culture translation to initialise the new culture.
        if (!isset($this->current_i18n[$default_culture]))
        {
          $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $default_culture);
          if ($obj) // Test if there is a translation for current culture
          {
            $this->setTableClassNameI18nForCulture($obj, $default_culture);
          }
        }
        else
        {
          $obj = $this->current_i18n[$default_culture];
        }
 
        if ($obj)
        {
          $obj->copyInto($new_i18n);
        }
 
        $new_i18n->setId($this->getId());
        $new_i18n->setCulture($this->culture);
 
        $this->setTableClassNameI18nForCulture($new_i18n, $this->culture);
      }
    }
 
    return $this->current_i18n[$this->culture];
  }
 

Saving fallback values at creation

You now need to add default translation when you create a new object. We do that with this doSave function.

To use this function, edit your table object class in lib/model/TableClassName.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Stores the object in the database while setting default culture if necessary.
   *
   * If the object is new, it inserts it; otherwise an update is performed.
   * All related objects are also updated in this method.
   *
   * @param      Connection $con The database connection
   * @return     int The number of rows affected by this insert/update and any referring fk objects' save() operations.
   * @throws     PropelException Any exceptions caught during processing will be
   *     rethrown wrapped into a PropelException.
   * @see        save()
   */
  protected function doSave($con)
  {
 
    $default_culture = sfConfig::get('sf_i18n_default_culture');
 
    // We try to fetch the default culture translation to initialise the new culture.
    if (!isset($this->current_i18n[$default_culture]))
    {
      $obj = TableClassNameI18nPeer::retrieveByPK($this->getId(), $default_culture, $con);
      if ($obj) // Test if there is a translation for current culture
      {
        $this->setTableClassNameI18nForCulture($obj, $default_culture);
      }
    }
    else
    {
      $obj = $this->current_i18n[$default_culture];
    }
 
    if(!$obj && isset($this->current_i18n[$this->culture]))
    {
      $new_i18n = new TableClassNameI18n();
      $this->current_i18n[$this->culture]->copyInto($new_i18n);
 
      $new_i18n->setId($this->getId());
      $new_i18n->setCulture($default_culture);
 
      $this->setTableClassNameI18nForCulture($new_i18n, $default_culture);
    }
 
    return parent::doSave($con);
  }
 

Fetching a list with default translations

To complete this snippet, here is a fallback version of doSelectWithI18n.

To enable the fallback, edit your table object peer class in lib/model/TableClassNamePeer.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Selects a collection of TableClassName objects pre-filled with their i18n objects.
   *
   * @param      Criteria $criteria
   * @param      string $culture The selected culture.
   * @param      Connection $con An optional database connection
   * @return     array Array of TableClassName objects.
   * @throws     PropelException Any exceptions caught during processing will be
   *     rethrown wrapped into a PropelException.
   */
  public static function doSelectWithI18n(Criteria $c, $culture = null, $con = null)
  {
    if ($culture === null)
    {
      $culture = sfContext::getInstance()->getUser()->getCulture();
    }
 
    $default_culture = sfConfig::get('sf_i18n_default_culture');
 
    // Set the correct dbName if it has not been overridden
    if ($c->getDbName() == Propel::getDefaultDB())
    {
      $c->setDbName(self::DATABASE_NAME);
    }
 
    TableClassNamePeer::addSelectColumns($c);
    $startcol = (TableClassNamePeer::NUM_COLUMNS - TableClassNamePeer::NUM_LAZY_LOAD_COLUMNS) + 1;
 
    TableClassNameI18nPeer::addSelectColumns($c);
 
    $c->addJoin(TableClassNamePeer::ID, TableClassNameI18nPeer::ID);
    $criterion = $c->getNewCriterion(TableClassNameI18nPeer::CULTURE, $culture);
    $criterion->addOr($c->getNewCriterion(TableClassNameI18nPeer::CULTURE, $default_culture));
    $c->add($criterion);
 
    $rs = BasePeer::doSelect($c, $con);
    $results = array();
    $uncultured_results = array();
 
    while($rs->next()) {
 
      $omClass = TableClassNamePeer::getOMClass();
 
      $cls = Propel::import($omClass);
      $obj1 = new $cls();
      $obj1->hydrate($rs);
      $obj1->setCulture($culture);
 
      if(isset($results[$obj1->getId()]))
      {
        $obj1 = $results[$obj1->getId()];
      }
 
      $omClass = TableClassNameI18nPeer::getOMClass($rs, $startcol);
 
      $cls = Propel::import($omClass);
      $obj2 = new $cls();
      $obj2->hydrate($rs, $startcol);
 
      $obj1->setTableClassNameI18nForCulture($obj2, $obj2->getCulture());
      $obj2->setTableClassName($obj1);
 
      if(!isset($uncultured_results[$obj1->getId()]))
      {
        $uncultured_results[$obj1->getId()] = $obj1;
      }
 
      if($obj2->getCulture() == $culture)
      {
        $uncultured_results[$obj1->getId()] = false;
      }
 
      if(!isset($results[$obj1->getId()]))
      {
        $results[$obj1->getId()] = $obj1;
      }
      elseif($obj2->getCulture() == $culture)
      {
        // Move result to the end of results array to fit eventual sort
        // criteria (ugly fix).
        unset($results[$obj1->getId()]);
        $results[$obj1->getId()] = $obj1;
      }
 
    }
 
    foreach($uncultured_results as $obj1)
    {
      if($obj1)
      {
        $obj1->setCulture($default_culture);
        $default_culture_object = $obj1->getCurrentTableClassNameI18n();
        if($default_culture_object)
        {
          $obj2 = new TableClassNameI18n();
          $default_culture_object->copyInto($obj2);
          $obj2->setCulture($culture);
          $obj2->setTableClassName($obj1);
          $obj1->setTableClassNameI18nForCulture($obj2, $obj2->getCulture());
        }
        $obj1->setCulture($culture);
      }
    }
 
    return array_values($results);
  }
 

Counting results filtered on translations

If you want to use a pager with a filter on translations, you will need this count method. Once this method present, the magic is done by :

    $criteria->setDistinct();
    $pager->setCriteria($criteria);
    $pager->setPeerMethod('doSelectWithI18n');
    $pager->setPeerCountMethod('doCountWithI18n');
 

Note : The setDistinct is very important for this snippet to work. It should not falsify your results, and without it, the doCountWithI18n method could return bad results.

To enable the fallback, edit your table object peer class in lib/model/TableClassNamePeer.php and add the following code. Then search and replace TableClassName by your table object class name.

  /**
   * Returns the number of rows matching criteria with I18N criteria.
   *
   * @param      Criteria $criteria
   * @param      boolean $distinct Whether to select only distinct columns (You can also set DISTINCT modifier in Criteria).
   * @param      Connection $con An optional database connection
   * @param      string $culture The selected culture.
   * @return     int Number of matching rows.
   */
  public static function doCountWithI18n(Criteria $criteria, $distinct = false, $con = null, $culture = null)
  {
    // we're going to modify criteria, so copy it first
    $criteria = clone $criteria;
 
    $default_culture = sfConfig::get('sf_i18n_default_culture');
 
    if ($culture === null)
    {
      // We use current user culture.
      $culture = sfContext::getInstance()->getUser()->getCulture();
    }
 
    // clear out anything that might confuse the ORDER BY clause
    $criteria->clearSelectColumns()->clearOrderByColumns();
    $criteria->addSelectColumn(TableClassNamePeer::COUNT_DISTINCT);
 
    // just in case we're grouping: add those columns to the select statement
    foreach($criteria->getGroupByColumns() as $column)
    {
      $criteria->addSelectColumn($column);
    }
 
    $criteria->addJoin(TableClassNamePeer::ID, TableClassNameI18nPeer::ID);
    $criterion = $criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $culture);
    $criterion->addOr($criteria->getNewCriterion(TableClassNameI18nPeer::CULTURE, $default_culture));
    $criteria->add($criterion);
 
    $rs = TableClassNamePeer::doSelectRS($criteria, $con);
    if ($rs->next()) {
      return $rs->getInt(1);
    } else {
      // no rows returned; we infer that means 0 matches.
      return 0;
    }
  }
 

Even though the book is quite good, the part in which unit testing is explained could use some better information. Especially the testing with a database connection (Propel in this case) needs some fixing.

Below you find an example unit test in which the database is accessed.

It tests a fictional class 'testClass' with the method 'load()'. That method accesses the database ... for which Propel needs to be running. The Symfony application that is loaded is called 'myapp';

if (!@constant('SF_APP')) { // Only load constants in not done before (group tests)
    define('SF_APP', 'myapp');
    define('SF_ENVIRONMENT', 'dev');
    define('SF_DEBUG', TRUE);
}
 
if (!@constant('SF_ROOT_DIR')) { // Only load constants in not done before (group tests)
    include(dirname(__FILE__).'/../bootstrap/unit.php');
}
 
sfCore::initSimpleAutoload(array(SF_ROOT_DIR.'/lib/model' // DB model classes
                                ,$sf_symfony_lib_dir // Symfony itself
                                ,dirname(__FILE__).'/../../lib' // Location class to be tested
                                ,dirname(__FILE__).'/../../apps/stageselect/lib' // Location myapp application
                                ,SF_ROOT_DIR.'/plugins')); // Location plugins
 
set_include_path($sf_symfony_lib_dir . '/vendor' . PATH_SEPARATOR . SF_ROOT_DIR . PATH_SEPARATOR . get_include_path());
 
/*
 * Start database connection and Symfony core
 */
sfCore::bootstrap($sf_symfony_lib_dir, $sf_symfony_data_dir);
sfContext::getInstance();
Propel::setConfiguration(sfPropelDatabase::getConfiguration());
Propel::initialize();
 
/*
 * Test
 */
// Init
$oTest = new lime_test(1, new lime_output_color());
 
// Print head
$oTest->diag('testClass');
$oTest->diag('----');
 
// Does the method load() exist in class 'testClass'
$oTest->can_ok('testClass', 'load', 'testClass has method load()');

Multiple databases is not really the strongest area of Propel when using symfony, for this it is better recomanded the use of sfDoctrine plugin.

Still, some propel-symfony based projects may require that one or many modules will have to use different database handles, and to do that, first you need to define each database into your database.yml file.

You can have as many dbs you like and on any combination you want, but for this example we will have one mysql db and one sqlite db.

Your database.yml file should look like this:

all:

  database1:
    class:                sfPropelDatabase
    param:
      phptype:            mysql
      hostspec:           localhost
      database:           mysql_databse
      username:           my_username

  database2:
    class:                sfPropelDatabase
    param:
      phptype:            sqlite
      database:           ../data/sqlite_database.db

Once you have the databases defined you need to write the schema for each database you have defined and with this step you can decide which table (model) will use which handle.

In our case we'll have two schema.yml files because we have two dbs and they will look like this:

database1.schema.yml

---
database1:

  table_1:
    _attributes: 
      idMethod: native
    id: 
      type: INTEGER
      required: true
      autoIncrement: true
      primaryKey: true
    value: 
      type: VARCHAR
      size: 32
      required: true

  table_2: 
    _attributes: 
      idMethod: native
    id: 
      type: INTEGER
      required: true
      autoIncrement: true
      primaryKey: true
    value: 
      type: VARCHAR
      size: 32
      required: true

database2.schema.yml

---
database2:

  table_3:
    _attributes: 
      idMethod: native
    id: 
      type: INTEGER
      required: true
      autoIncrement: true
      primaryKey: true
    value: 
      type: VARCHAR
      size: 32
      required: true

Now is time to build the models

$ symfony propel-build-model

If you want to use other symfony commands that uses the database directly like:

$ symfony propel-build-sql
$ symfony propel-insert-sql

You'll have to configure the dbs connection into propel.ini.

Here we have a problem because you can't use both databases with propel.ini, so you'll have to do this one at a time.

database1 on propel.ini

propel.database            = mysql
propel.database.createUrl  = mysql://localhost/
propel.database.url        = mysql://localhost/mysql_database

database2 on propel.ini

propel.database            = sqlite
propel.database.createUrl  = sqlite://./../data/sqlite_database.db
propel.database.url        = sqlite://./../data/sqlite_database.db

Once you have the models generated and the data loaded into your databases, you can play with your databases.yml file.

If you generate the databases for every handle you'll like, you can switch a model from a handle to another.

if you won´t to add a foreignkey to sf_guard_user put this on top off you schema.yml file ;)

propel:
  _attributes :          { package: "plugins.sfGuardPlugin.lib.model" }
  sf_guard_user:
    _attributes:         { phpName: sfGuardUser }
    id:

now you can simple add a foreignkey

propel:
  tbl_user_profile:
    user_id:             { type: integer, primaryKey: true, foreignTable: sf_guard_user, foreignReference: id, onDelete: cascade }

or

propel:
  tbl_user_profile:
    sf_guard_user_id:

if you using multiple database from Propel, It can be solve by writing two or more schema files.

PROJECT_DIR/config/databases.yml

all:
  database1:
    class:   sfPropelDatabase
    param:
      dsn:   pgsql://foo:bar@hostname/database1

  database2:
    class:   sfPropelDatabase
    param:
      dsn:   mysql://foo:bar@hostname/database2

PROJECT_DIR/config/database1.schema.xml

<?xml version="1.0" encoding="UTF-8"?>
<database name="database1" defaultIdMethod="native" noxsd="true">
　　<table name="foo" phpName="Foo">
    ....
　　</table>
</database>

PROJECT_DIR/config/database2.schema.xml

<?xml version="1.0" encoding="UTF-8"?>
<database name="database2" defaultIdMethod="native" noxsd="true">
　　<table name="bar" phpName="Bar">
    ....
　　</table>
</database>

Last, build model command.

$ symfony propel-build-model

Build complete.

Example, getting multiple databases connection handler.

$database1_connection_handler = Propel::getConnection(FooPeer::DATABASE_NAME);
$database2_connection_handler = Propel::getConnection(BarPeer::DATABASE_NAME);

In app/lib/ create a file named myDBConnectionFilter.class.php and add:

class myDBConnectionFilter 
{
  public function initialize($filterChain) 
  {
    $db = sfContext::getInstance()->getDatabaseManager()->getDatabase('myschema');
    $db->setConnectionParameter('username', 'myusername');
    $db->setConnectionParameter('password', 'mypassword');
    $db->setConnectionParameter('hostspec', 'localhost');
    $db->setConnectionParameter('database', 'mydatabase');
 
    // The below line is optional - Symfony will connect anyway if no connection is present
    $db->connect();
 
  }
}

Then declare this filter to run on every page load by adding this to app/config/filters.yml:

myDBConnectionFilter:
  class: myDBConnectionFilter

Finally, Symfony will fail at the call above

sfContext::getInstance()->getDatabaseManager()->getDatabase('myschema');

because it does not know what type of database driver 'myschema' requires (MySQL/SQLite/etc.). You must add the following to app/config/databases.yml:

all:
  propel:
    class:          sfPropelDatabase
    param:
      phptype:            mysql

You can now dynamically select databases from the above class.

To modify the settings of a connection named 'propel':

$con = sfContext::getInstance()->getDatabaseConnection('propel');
$con->setConnectionParameter('username', 'foo');
$con->setConnectionParameter('password', 'bar');

This works for all the settings that can be defined in the databases.yml (more info in the sfPropelDatabase class source).

You must execute this code before the first query to the database. If you do that after a first query, it fails. This means that the best way to implement it is in a filter.

More a PHP trick than symfony's one, but as you can't with propel randomize order of results, just do:

$c = new Criteria()
... fill your criteria there ...
$result = MyTablePeer::doSelect($c);
shuffle($result);

obvious? sorry, seen someone asking on IRC once :D

$connection = sfContext::getInstance()->getDatabaseConnection('propel');