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);
 

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);

By default, symfony stores user sessions in files.

You can store them in your database by changing your apps/APPNAME/config/factories.yml configuration file:

all:
  storage:
    class: sfMySQLSessionStorage
    param:
      database: propel
      db_table: SESSION_TABLE_NAME

There are several available session storage classes:

• sfCreoleSessionStorage
• sfMySQLSessionStorage
• sfPostgreSQLSessionStorage
• sfPDOSessionStorage

The API documentation lists all available configuration parameters.

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:

[DEPRECATED SEE COMMENTS]

{-- DEPRECATED SEE COMMENTS --}

---DEPRECATED SEE COMMENTS---

This basic filter can transform pages into pdf using the sfTCPDFPlugin. You can also save the generated PDF to the database using the sfPropelFileStoragePlugin.

this is the first version so please post comments if you like it or use it !

Create a file in apps/myapp/lib/pdfFilter.class.php

<?php
/**
 * Filter for redirecting to PDF for the pages that need it
 *
 * @author Laurent Marchal
 * @version 1
 */
 
class pdfFilter extends sfFilter
{
    /**
     * Execute filter
     *
     * @param FilterChain $filterChain The symfony filter chain
     */
 
    public function execute ($filterChain)
    {
 
        if ($this->getContext()->getActionName() == 'pdf')
        {
        $pdf = $this->initPDF();
 
        // Next filter
        $filterChain->execute();
 
        $rawpdf = $this->writePDF($pdf, $this->getContext()->getResponse()->getContent());
 
        $pdf->Output(); //shows the pdf to the user
        //$this->savePdfToDatabase($rawpdf);
        return;
        }
        $filterChain->execute();
    }
 
    /**
     * initialyse the TCPDF object, must be instanciated before
     * filterChain->execute() in order to analyse the request.
     *
     */
    protected function initPDF()
    {
        //create new PDF document
        $pdf = new sfTCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true);
        $pdf->SetFont("FreeSerif", "", 12);
        return $pdf;
    }
 
    /**
     * write the contents to the PDF generally
     * getContext()->getResponse()->getContent()
     *
     *@param sfTCPDF $pdf the TCPDF object created with function initPDF()
     *@param stream $html_content the contents to transform to PDF
     */
    protected function writePDF($pdf, $html_content)
    {
        $pdf->AddPage();
        $pdf->writeHTML($html_content, true, 0);
        //$this->logMessage('ficheduActions::createPDF '.$html_content , 'info');
        //$document = $pdf->Output('test.pdf', 's');
        return $pdf->Output('test.pdf', 's');
    }
 
    /**
     * Save the PDF in the database using sfPropelFileStoragePlugin
     *
     *@param stream $pdf_content output of function writePDF()
     */
    protected function  savePdfToDatabase($pdf_content)
    {
        //File Info
      $file_info = new sfPropelFileStorageInfo();
      $file_info->setName('DU_Entreprise_v'.$fichedu->getFdVersion().'.pdf');
      $file_info->setSize(null);
      $file_info->setMimeType('application/pdf');
        //File Data
      $file_data = new sfPropelFileStorageData();
      $file_data->setBinaryData($pdf_content);
      $file_info->addsfPropelFileStorageData($file_data);
      $file_info->save();
    }
 
}

and in apps/myapp/config/filters.yml

rendering: ~
web_debug: ~
security:  ~
 
pdfFilter:
    class:  pdfFilter
 
cache:     ~
common:    ~
flash:     ~
execution: ~

then in your module,you will have to create this function in the action.class.php :

  public function executePdf()
  {
    $this->renderPDF = true;
    //use a special layout to clean the pdf
    //remove if you want your entire layout to be generated into pdf
    $this->setLayout ('pdf');
    //the show action generally fit
    $this->setTemplate ('show');
  }

then go to http://mywebsite/yourmodule/pdf to generate the pdf

H@ve Fun !

Here is the schema you need to set up database session storage.

CREATE TABLE `session` (
  `sess_id` varchar(32) NOT NULL,
  `sess_data` text NOT NULL,
  `sess_time` int(11) NOT NULL
);

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.

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()');

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";
 

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

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;
    }
  }
 

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

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.

This snippet allows you to retrieve the country of the visitor using the IP number of the visitor. It will also become available as a symfony Plugin.

Very handy when you are developing a internationalized application. You can use the ISO code in lowercase to set the interface language. Like this:

$lang = strtolower(Country::getIso2WithIp(Country::getIpAddress()))
 

By passing the IP number to a function, you'll get the following information: - ISO 3166 Code, 2 characters, might be used to set the culture - Country, 20 characters

The IP data is imported from the webnet77 (http://software77.net/cgi-bin/ip-country/geo-ip.pl) database, probably the most frequently updated database on the web.

Data is stored on your server, that means that retrieving data is a lot faster compared to the already existing plugin sfIp2Country, which uses a web request.

config/schema.xml:

    <table name="YOURDB_country" phpName="Country">
      <column name="ipfrom" type="bigint" size="20" required="true" primaryKey="true" />
      <column name="ipto" type="bigint" size="20" />
      <column name="registry" type="varchar" size="7" />
      <column name="assigned" type="varchar" size="8" />
      <column name="iso2" type="varchar" size="2" />
      <column name="iso3" type="varchar" size="3" />
      <column name="country" type="varchar" size="20" />
    </table>
 

Do a build-model and whatever is needed.

Additions to lib/model/country.php

    static function getIpAddress()
    {
        // Get the ip v4 address
        if (getenv("HTTP_CLIENT_IP")) $ipaddr = getenv("HTTP_CLIENT_IP"); 
        else if(getenv("HTTP_X_FORWARDED_FOR")) $ipaddr = getenv("HTTP_X_FORWARDED_FOR"); 
        else if(getenv("REMOTE_ADDR")) $ipaddr = getenv("REMOTE_ADDR"); 
        else $ipaddr = "127.0.0.1"; // not found, make it localhost
 
        return $ipaddr;
    }
 
    static function retrievePkByIp($ipaddr)
    {
        // Make IP numerical
        $ipnum = sprintf("%u", ip2long($ipaddr));
 
      // lookup IP address 
        $c = new Criteria();
      $c->add(CountryPeer::IPFROM, "'$ipnum' >= ".CountryPeer::IPFROM." and "."'$ipnum' <= ".CountryPeer::IPTO, Criteria::CUSTOM);
        $rs = CountryPeer::doSelectRS($c);   
        $pk = 0;
    while ($rs->next()){
      $pk = $rs->getString(1);
        }
        return $pk;
    }
 
    static function getIso2WithIp($ipaddr)
    {
        // Make IP numerical
        $ipnum = sprintf("%u", ip2long($ipaddr));
 
      // lookup IP address 
        $c = new Criteria();
      $c->add(CountryPeer::IPFROM, "'$ipnum' >= ".CountryPeer::IPFROM." and "."'$ipnum' <= ".CountryPeer::IPTO, Criteria::CUSTOM);
        $rs = CountryPeer::doSelectRS($c);   
        $iso2 = "EN"; // initialize with your default country
    while ($rs->next()){
      $iso2 = $rs->getString(5);
        }
        return $iso2;
    }
 

Helper to demonstrate the works: \apps\frontend\lib\helper\CountryHelper.php

<?php 
 
function display_ipinfo()
{
        // Get IP address and make numeric
        $ipaddr = Country::getIpAddress();
        $ipnum = sprintf("%u", ip2long($ipaddr));
 
        // Retrieve from database
        $country = CountryPeer::retrieveByPk(Country::retrievePkByIp($ipaddr));
 
        // Output
        $o =  '<p>';
        $o .= 'Your IP number: '  . '&nbsp;&nbsp;&nbsp;' . $ipaddr . '<br />';
        $o .= 'Your IP numerical: ' . $ipnum . '<br />';
        $o .= 'Data retrieved from IP database:<br />';
 
        if ($country) // found
        {
            $o .= 'ISO code: ' . $country->getIso2() . '<br />';
            $o .= 'Country: ' . '&nbsp;&nbsp;' . $country->getCountry();
        }
        else                    // not found
        {
            $o .= 'Your IP number was not found in the IP-database';
        }
 
        $o .=  '</p>';
        echo $o;
}
 

Code to call the demo function in the helper:

<?php use_helper('Country'); ?>
<?php display_ipinfo() ?>
 

I load my data with a Cron job, the SQL statement is as follows:

LOAD DATA INFILE 'YOURPATH_IpToCountry.csv' REPLACE INTO TABLE YOURDB_country FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
 

Demo at http://www.noorden.com/en/my+symfony+php+projects/project-ip-number-to-country.html

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.

This is a simple example for object cache.

$key = md5('myPropelObjectKey');
$sfProcessCache = new sfProcessCache();
if ($sfProcessCache->has($key)) {
  $obj = unserialize($sfProcessCache->get($key));
}else{
  $obj = Table::doSelect();
  $sfProcessCache->set($key, serialize($obj))
}