db4.sh

#!/bin/bash
 
SYMFONY=`dirname $0`"/symfony"
 
SEP="==========================================================================="
echo "Rebuilding full database from DB4 schema and fixtures"
 
echo $SEP
echo "Converting DB4 schema for propel..."
$SYMFONY propel:db4-to-propel frontend --env=cli --debug=1 --file_dir=/doc/database --file=snippets_db4.xml --output_dir=/config --output=snippets_schema --package=lib.model.snippets
 
echo $SEP
echo "Rebuilding all tables & ORM classes..."
$SYMFONY propel:build-all-load frontend
 
echo $SEP
echo "Clearing the cache..."
$SYMFONY cc
 
echo $SEP
cd ..
echo "JOB's DONE ! ;)"

Permet de pouvoir faire des scripts de migration fiables puisque les noms de clé et d'index seront les mêmes sur tout les serveurs.

<?php
 
/**
 * Migration qui ajoute les colonne permettant d'effectuer la localisation
 * d'un membre comme les bars.
 */
 
class Migration024 extends sfMigration 
{
 
  /**
   * Up
   */
  public function up() 
  {
    $this->diag('Debut UP 24');
 
    // Table user_profile
    $this->diag('MAJ champs user_profile');
    $this->executeSQL("
ALTER TABLE `user_profile` 
ADD `country_code` varchar(2) default NULL COMMENT 'Code du pays' AFTER `personal_url` ,
ADD `zone_id` int(11) default NULL COMMENT 'Departement du membre' AFTER `country_code` ,
ADD `lat` decimal(12,9) default NULL COMMENT 'Latitude' AFTER `zone_id` ,
ADD `lng` decimal(12,9) default NULL COMMENT 'Longitude' AFTER `lat` ,
ADD `latlng_precision` int( 11 ) default NULL COMMENT 'Precision de la localisation' AFTER `lng`;
    ");
 
    // ALTER TABLE `user_profile` ADD FOREIGN KEY ( `zone_id` ) REFERENCES `zone` (`id`) ON DELETE SET NULL;
    // Ajout des cle etrangeres
    $this->diag('MAJ contraintes user_profile');
    $this->executeSQL("    
ALTER TABLE `user_profile`
 ADD CONSTRAINT `zone_id_FK`
 FOREIGN KEY `zone_id_idx` ( `zone_id` )
 REFERENCES `zone` (`id`)
 ON DELETE SET NULL;
");
 
  }
 
  /**
   * Down
   */
  public function down() 
  {
    $this->diag('Debut DOWN 24');
 
    // Suppr
    $this->diag('SUPPRESSION contraintes champs a supprimer');
 
    $this->executeSQL("
      ALTER TABLE `user_profile`
      DROP FOREIGN KEY `zone_id_FK`
    ");
 
    $this->executeSQL("
      DROP INDEX `zone_id_idx` ON `user_profile` 
    ");
 
    $this->diag('SUPPRESSION champs user_profile');
    $this->executeSQL("
ALTER TABLE `user_profile`
  DROP `country_code`,
  DROP `zone_id`,
  DROP `lat`,
  DROP `lng`,
  DROP `town`,
  DROP `latlng_precision`;    
    ");
  }
}
 

Ou en décomposant la création des index/clés

<?php
 
/**
 * Migration qui ajoute les colonne permettant d'effectuer la localisation
 * d'un membre comme les bars.
 */
 
class Migration024 extends sfMigration 
{
 
  /**
   * Up
   */
  public function up() 
  {
    $this->diag('Debut UP 24');
 
    // Table am_user_profile
    $this->diag('MAJ champs user_profile');
    $this->executeSQL("
ALTER TABLE `user_profile` 
ADD `country_code` varchar(2) default NULL COMMENT 'Code du pays' AFTER `personal_url` ,
ADD `lat` decimal(12,9) default NULL COMMENT 'Latitude' AFTER `country_code` ,
ADD `lng` decimal(12,9) default NULL COMMENT 'Longitude' AFTER `lat` ,
ADD `latlng_precision` int( 11 ) default NULL COMMENT 'Precision de la localisation' AFTER `lng`;
    ");
 
    // ALTER TABLE `am_user_profile` ADD FOREIGN KEY ( `zone_id` ) REFERENCES `am_zone` (`id`) ON DELETE SET NULL;
    // Ajout des cle etrangeres
    /*
    $this->diag('MAJ conrtaintes am_user_profile');
    $this->executeSQL("    
ALTER TABLE `user_profile`
 ADD CONSTRAINT `zone_id_FK`
 FOREIGN KEY `zone_id_idx` ( `zone_id` )
 REFERENCES `am_zone` (`id`)
 ON DELETE SET NULL;
");
  */
    $this->diag('CREATION table am_user_zone');
    $this->executeSQL("        
CREATE TABLE `user_zone` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `zone_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;    
");
 
/*
  KEY `user_zone_FI_1` (`user_id`),
  KEY `user_zone_FI_2` (`zone_id`)
  ALTER TABLE `user_zone` ADD INDEX ( `user_id` )  
*/
 
    $this->diag('MAJ conrtaintes am_user_zone');
    $this->executeSQL("
ALTER TABLE `user_zone` 
ADD INDEX `user_zone_FI_1` ( `user_id` )         
    ");
    $this->executeSQL("     
ALTER TABLE `user_zone` 
ADD FOREIGN KEY `user_zone_FK_1` ( `user_id` ) 
REFERENCES `sf_guard_user` (`id` ) ON DELETE CASCADE;
");
 
    $this->executeSQL("
ALTER TABLE `user_zone` ADD INDEX `am_user_zone_FI_2` ( `zone_id` )         
    ");
    $this->executeSQL("     
ALTER TABLE `user_zone` 
ADD FOREIGN KEY `user_zone_FK_2` ( `zone_id` ) 
REFERENCES `zone` (`id` ) ON DELETE CASCADE;
");
 
  }
 
  /**
   * Down
   */
  public function down() 
  {
    $this->diag('Debut DOWN 24');
 
    // Suppr
    $this->diag('SUPPRESSION contraintes champs a supprimer');
 
    /*
    $this->executeSQL("
      ALTER TABLE `user_profile`
      DROP FOREIGN KEY `zone_id_FK`
    ");
 
    $this->executeSQL("
      DROP INDEX `zone_id_idx` ON `user_profile` 
    ");
    */
 
    $this->diag('SUPPRESSION champs user_profile');
    $this->executeSQL("
ALTER TABLE `user_profile`
  DROP `country_code`,
  DROP `lat`,
  DROP `lng`,
  DROP `latlng_precision`;    
    ");
 
    $this->diag('SUPPRESSION table user_zone');
    $this->executeSQL("DROP TABLE IF EXISTS `user_zone`");
  }
}
 

  // Delete external tables from plugin schema (already defined in another schema (main or other plugin))
  if (DCMC_EXTERNAL_TABLE) {
    foreach (explode(',', DCMC_EXTERNAL_TABLES) as $external_table) {
      $reg_exp = '/(<table name="'. $external_table. ')(((.)*(\s)*)*?)(<\/table>)/';
      $xmlstr = preg_replace($reg_exp, '', $xmlstr);
    }
  }
 

all:
  connexion1:
    class:          sfPropelDatabase
    param:
      dsn:          mysql://root:password@localhost/db1
  connexion2:
    class:          sfPropelDatabase
    param:
      dsn:          mysql://root:password@localhost/db2
 

Après tu peux instancier une connection depuis propel :

$con1 = Propel::getConnection('connexion1');
$con2 = Propel::getConnection('connexion2');
 

2ere maniere full mysql (donc plus safe)

$criterion->addAnd($c->getNewCriterion(ShopOrderPeer::CREATED_AT, 'DATE('. ShopOrderPeer::CREATED_AT. ')  <= \''. goDate::getMySqlDateFromTs($this->filters['created_at']['to']). '\'', Criteria::CUSTOM)); 
$criterion->addAnd($c->getNewCriterion(ShopOrderPeer::CREATED_AT, 'DATE('. ShopOrderPeer::CREATED_AT. ')  <= DATE(FROM_UNIXTIME('. $this->filters['created_at']['to']. '))', Criteria::CUSTOM));
 

Appliquer le patch suivant a propel:

--> http://propel.phpdb.org/trac/ticket/425

Objet1Peer::addSelectColumns($c);
$startcol = (self::$numColumns - self::$numLazyLoadColumns) + 1;
Objet2Peer::addSelectColumns($c);
$startcol3 = (self::$numColumns - self::$numLazyLoadColumns) + 1;
Objet3Peer::addSelectColumns($c);
 
$c->addJoin(Objet1Peer::CHAMP_1, Objet2Peer::CHAMP_2);
$c->addJoin(Objet2Peer::CHAMP_2, Objet3Peer::CHAMP_3);
 
$rs = BasePeer::doSelect($c, $con);
$results = array();
 
while($rs->next()) {
    $omClass = Objet1Peer::getOMClass();
    $cls = Propel::import($omClass);
    $obj1 = new $cls();
    $obj1->hydrate($rs);
 
    $omClass = Objet2Peer::getOMClass();
    $cls = Propel::import($omClass);
    $obj2 = new $cls();
    $obj2->hydrate($rs, $startcol);
 
    $omClass = Objet3Peer::getOMClass();
    $cls = Propel::import($omClass);
    $obj3 = new $cls();
    $obj3->hydrate($rs, $startcol3);
}
 

Exemple 1:

      $criterion = null;
      $form_product_state_id = array_flip($form_product_state_id);
 
      // Test avec neuf
      $with_new = isset($form_product_state_id[0]);
      if ($with_new) {
        unset($form_product_state_id[0]);
      }
 
      // Seulement neuf ou au moins un des autres etatss ?
      if ($form_product_state_id) {
        $criterion = $c->getNewCriterion(ShopProductPeer::PRODUCT_STATE_ID, array_keys($form_product_state_id), Criteria::IN);
      }
 
      // Produits neufs
      if ($with_new) {
        if (!$criterion) {
          $criterion = $c->getNewCriterion(ShopProductPeer::PRODUCT_STATE_ID, null, Criteria::ISNULL);
        } else {
          $criterion->addOr($c->getNewCriterion(ShopProductPeer::PRODUCT_STATE_ID, null, Criteria::ISNULL));
        }  
      }
      $c->add($criterion);
 

Exemple 2

  /**
   * Recuperation d'un produit par son code EAN
   *
   * @author lvernet
   * @since 18 sept. 07
   */
  public static function doSelectByEan($ean, Criteria $c = null)
  {
    if ($c == null) {
      $c = new Criteria();
    }
 
    $ean = trim($ean);
    $padded_ean = str_pad($ean, ProductEan::EAN_STD_LENGTH, ProductEan::EAN_PADDING_CHAR, STR_PAD_LEFT);
 
    ProductPeer::isVisibleCriteria($c);
    $c->addJoin(ProductPeer::ID, self::PRODUCT_ID);
 
    // Test par recherche stricte
    $criterion = $c->getNewCriterion(self::EAN13, $ean);
 
    // Test en completant a gauche a saisie utilisateur par des 0 a 13 caraceteres
    $criterion->addOr($c->getNewCriterion(self::EAN13, $padded_ean));
 
    // Test en completant a gauche le code barre en base par des 0 a 13 caraceteres
    $criterion->addOr($c->getNewCriterion(self::EAN13, 'LPAD('. self::EAN13 .' , 13,  \'0\') = \''. $ean. '\'', Criteria::CUSTOM));
 
    // Test en completant a gauche des codes barres et la saisie utilisateur
    $criterion->addOr($c->getNewCriterion(self::EAN13, 'LPAD('. self::EAN13 .' , 13,  \'0\') = \''. $padded_ean. '\'', Criteria::CUSTOM));
 
    // Exec
    $c->add($criterion);
    $product_ean = self::doSelectOne($c);
 
    return $product_ean ? $product_ean : null;
  }
 

/!\ Ce champs est trop long, utiliser un champs de type varchar et mettre entre quotes la valeur dans les fixtures. /!\

$c->add(self::EAN13, self::EAN13 .' LIKE \'%'. $ean. '%\'', Criteria::CUSTOM);
 

$c = $shop->buildPkeyCriteria();
 
 $c->addJoin(ClientQuestionPeer::SHOP_PRODUCT_ID, ShopProductPeer::ID, Criteria::LEFT_JOIN);
    $c->addJoin(ClientQuestionPeer::SHOP_ORDER_ID, ShopOrderPeer::ID, Criteria::LEFT_JOIN);
 
    $criterion = $c->getNewCriterion(ShopOrderPeer::SHOP_ID, $shop->getId())
      ->addOr($c->getNewCriterion(ShopProductPeer::SHOP_ID, $shop->getId()));
    $c->addAnd($criterion);
 
    $c->addDescendingOrderByColumn(self::CREATED_AT);
    return $c;
 

    $con = Propel::getConnection();
 
    $selectCriteria = new Criteria();
    $selectCriteria->add(StaticTemplatesPeer::CODE, $code);        
    $selectCriteria->add(StaticTemplatesPeer::IS_ACTIVE, true);
    $ids = goPropelTools::getIdTab(self::doSelect($selectCriteria));
 
    $updateCriteria = new Criteria();
    $updateCriteria->add(self::ID, $ids, Criteria::IN);
    $updateCriteria->add(StaticTemplatesPeer::IS_ACTIVE, false);
 
    return self::doUpdate($updateCriteria);
 

public static function doSelectJoinUserAndStateAndCountry(Criteria $c, $con = null) {
$c = clone $c;
 
if ($c->getDbName() == Propel::getDefaultDB()) {
$c->setDbName(self::DATABASE_NAME);
}
 
MemberPeer::addSelectColumns($c);
$startcol2 = (MemberPeer::NUM_COLUMNS - MemberPeer::NUM_LAZY_LOAD_COLUMNS) + 1;
 
UserPeer::addSelectColumns($c);
$startcol3 = $startcol2 + UserPeer::NUM_COLUMNS ;
 
CountryPeer::addSelectColumns($c);
$startcol4 = $startcol3 + CountryPeer::NUM_COLUMNS ;
 
StatePeer::addSelectColumns($c);
CountryPeer::addSelectColumns($c);
 
 
$c->addJoin(MemberPeer::USER_ID, UserPeer::ID);
$c->addJoin(MemberPeer::STATE_ID, StatePeer::ID);
$c->addJoin(MemberPeer::COUNTRY_ID, CountryPeer::ID);
 
 
$rs = BasePeer::doSelect($c, $con);
$results = array();
 
while($rs->next())
{
$omClass = MemberPeer::getOMClass();
 
$cls = Propel::import($omClass);
$obj1 = new $cls();
$obj1->hydrate($rs);
 
$omClass = UserPeer::getOMClass();
 
$cls = Propel::import($omClass);
$obj2 = new $cls();
$obj2->hydrate($rs, $startcol2);
 
$omClass = StatePeer::getOMClass();
 
$cls = Propel::import($omClass);
$obj3 = new $cls();
$obj3->hydrate($rs, $startcol3);
 
$omClass = CountryPeer::getOMClass();
 
$cls = Propel::import($omClass);
$obj4 = new $cls();
$obj4->hydrate($rs, $startcol4);
 
$obj1->setUser($obj2);
$obj1->setState($obj3);
$obj1->setState($obj4);
$results[] = $obj1;
}
 
return $results;
}
 

/!\ Les fonctions de criterion n'acceptent que des autres criterions en parametre /!\

/!\ Appel d'une methode doSelect sans passer de criteria /!\

/!\ Passage d'une valeur nulle ou d'un objet quelquonque a place d'un criteria /!\

$this->getEndAt(null);
 

// Debut de la transaction
      $con = Propel::getConnection();
      $con->begin();      
      try
      { 
        $this->shop_ribs->saveShopRibsFromRequest($shop_ribs_form);
        $this->shop->setShopRibs($this->shop_ribs);
        $this->shop->save();
        $con->commit();
      }
      catch (Exception $e)
      {
        $con->rollback();
        $this->getRequest()->setError('error', $this->getContext()->getI18N()->__('Une erreur est survenue pendant l\'enregistrement, veuillez essayer d\'ici quelques minutes.'));
      }
 

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

A utiliser dans le database.yml et le propel.ini:

mysql://user:password@host/database-name
 

Or you can use the explicit synthax:

dev:
  propel:
    class:      sfPropelDatabase
    param:
      phptype:  mysql
      host:     localhost
      hostspec: localhost
      database: db
      username: user
      password: ~
      compat_assoc_lower: true
      compat_rtrim_string: true
 

<?php
  public function getDistinctTypes()
  {
    $crit=new Criteria();
    $crit->addAscendingOrderByColumn(TablenamePeer::TYPE);
    $crit->setDistinct();
    return TablenamePeer::doSelect($crit);
  }
?>
 

// Recuperation de la liste des commandes
switch ($type_batch) {
    case TYPE_BATCH_7H:
        $start_time = mktime(10, 0, 0, date("m")  , date("d")-1, date("Y"));
        $end_time = mktime(7, 0, 0, date("m")  , date("d"), date("Y"));
    break;
 
    case TYPE_BATCH_10H:
        $start_time = mktime(7, 0, 0, date("m")  , date("d"), date("Y"));
        $end_time = mktime(10, 0, 0, date("m")  , date("d"), date("Y"));
    break;
}
 
 
$c = new Criteria();
$c->add(ExcommandesPeer::ISVALIDE, 1);
$c->add(ExcommandesPeer::DATECOMMANDE, ExcommandesPeer::DATECOMMANDE. '> \''. myDate::getMySqlDateTimeFromTs($start_time). '\'', Criteria::CUSTOM);
$c->addAnd(ExcommandesPeer::DATECOMMANDE, ExcommandesPeer::DATECOMMANDE. '< \''. myDate::getMySqlDateTimeFromTs($end_time). '\'', Criteria::CUSTOM);        
$details_commandes = ExdetailscommandesPeer::doSelectJoinExcommandes($c);
 

// Prochain numero de sequence
$filename .= '-'. myPropel::getNextSeq(ExbatchCommandesPeer::TABLE_NAME);
 

myPropel.class.php

/**
     * Fonction retournant la prochaine valeur autoincrement d'une table
     * 
     * @author Vernet Loic
     * @since  version - 28 févr. 2007
     */
    public static function getNextSeq($table_name)
    {
        $sql = "SHOW TABLE STATUS LIKE '$table_name'";
        $con = Propel::getConnection();
        $stmt = $con->createStatement();
        $rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_ASSOC);
        $rs->first();
        return $rs->get('Auto_increment');
    }
 

$c->addAscendingOrderByColumn(TablePeer::GOALS . "+" . TablePeer::ASSISTS);
 

if ($this->isNew())
{
  // ...
}
 

$c = new Criteria(); 
 
$c->clearSelectColumns()->addSelectColumn(ClientsPeer::IDCLIENT);
$c->addSelectColumn(ClientsPeer::NOM);
$rs = ClientsPeer::doSelectRS($c);        
$clientsWithId = myPropel::getTabFromRS($rs);
 

Fonction getTabFromRS:

    /**
     * Fonction retournant un tableau avec un ensemble de colonnes selectionnees
     * a l'aide d'un resultset
     * 
     * @author Vernet Loic
     * @since  2 janv. 2007
     */
    public static function getTabFromRS($rs, $one_row = false)
    {
        $tab = array();
        while ($rs->next()) {
            $tab[] = $rs->getRow();
        }
        return $one_row ? $tab[0] : $tab;
    }
 

$criterion = $c->getNewCriterion(MagasinsPeer::CODEPOSTAL, '');
$criterion->addOr($c->getNewCriterion(MagasinsPeer::CODEPOSTAL, MagasinsPeer::CODEPOSTAL. ' LIKE \''. $dept.'%\'' , Criteria::CUSTOM));
 

Voir classe myPropel.

This snippet shows you to test if there was at least one modification on a list of fields.

  /**
   * Test if one of the 3 columns was modified.
   * 
   * @author COil
   * @since  13 sept 08
   * 
   * @return boolean
   */
  public function isAdrressModified()
  {
    $columns = array_flip($this->modifiedColumns);
 
    return 
      isset($columns[UserProfilePeer::TOWN]) ||
      isset($columns[UserProfilePeer::ADDRESS]) ||
      isset($columns[UserProfilePeer::POSTAL_CODE]);
  }