Blog

In April I wrote about using Doctrine with multiple connections for specific table models.  This worked really well, except Symfony’s web debug toolbar would not show any SQL queries that were not defined in the databases.yml file.  This of course made it quite difficult to debug many queries, as the query logs show the queries before the parameters are inserted, for example:

SELECT a.id, a.title FROM posts a WHERE id = ?

The web debug toolbar however shows them with the parameters in place. This makes it a ton quicker to debug as you can see the parameters in place, as well as copy and paste the query straight into the SQL client to see the raw results. After a few months and a project becoming much more complex it was necessary to see the queries. I looked up how sfWebDebugPanelDoctrine gets the queries and found:

  protected function getDoctrineEvents()
  {
    $databaseManager = sfContext::getInstance()->getDatabaseManager();

    $events = array();
    if ($databaseManager)
    {
      foreach ($databaseManager->getNames() as $name)
      {
        $database = $databaseManager->getDatabase($name);
        if ($database instanceof sfDoctrineDatabase && $profiler = $database->getProfiler())
        {
          foreach ($profiler->getQueryExecutionEvents() as $event)
          {
            $events[$event->getSequence()] = $event;
          }
        }
      }
    }

    // sequence events
    ksort($events);

    return $events;
  }

So the sfDatabaseManager would manage all the connections and return which ones to pull queries off of. I looked at it a bit and saw that it has setDatabase which sets the databases it has registered. Since it requires you give it a sfDatabase as a parameter I had to update the way we connect the databases a bit so we could pass them to it. The new version is below:

          $databaseManager = sfContext::getInstance()->getDatabaseManager();
          $newConn=new sfDoctrineDatabase(array('name'=>'NewConnectionName','dsn'=>$databaseString));
          $newConn->connect();
          $databaseManager->setDatabase('NewConnectionName',$newConn);

Now you will now see the queries in the web debug toolbar.

We’re proud to announce the relaunch of of a partners website: http://www.forextv.com.  The website delivers Forex news and video along with other Forex resources.  The website has been rebuilt on the Symfony framework.

In the coming weeks we will be rolling out many new features, including several social components.  Keep checking back and let us know how we are doing!

Recently we’ve been working on a new project that requires caching of both views and database queries. One of the problems I came across I wanted to Result Cache an query I was using for a pager. This caused a couple of problems, one being I needed to be able to clear the cache by its prefix so we would never have a stale cache. Doctrine has a built in deleteByPrefix call for this, however on a pager how do I get it so that it will use a result cache, but still use different indexes for different pages? The following code would not work:

$this->createQuery('sc')
           // A couple of complex joins here, etc
            ->where('sc.video_id = ?',$id)
            ->orderBy('sc.created_at DESC')
            ->useResultCache(true,sfConfig::get('app_comment_cache'),'comment_index');

Well here the problem is everything is being cached as as the ‘comment_index’ cache, so if you passed that query to a pager, and told it to be on the second page, it’d see the ‘comment_index’ cache exists, and use that. A simple way around this is:

  // Query build...
 ->useResultCache(true,sfConfig::get('app_comment_cache'),'comment_index_'.$page);

In this example page is the parameter you are passing the query and the Doctrine pager to tell it what page cache to look at.

Then a very weird problem was occurring, I was getting more queries if I USED the cache than if I didn’t. Very weird. It seemed that one of the joins object did not seem to be getting stored in the cache. The join looked something like this:

  $this->createQuery('sc')
         ->leftJoin('sc.User u')
         ->leftJoin('u.Profile p')
         //.... more joins etc

The problem was the profile object was not getting stored in the result cache and thus causing a query each time it was called from the user object. After much hunting around, a long time in #doctrine, and a few leads from a couple of people, it turns out, by default, Doctrine will only serialize the immediate relation to the main object (in this case ‘sc’). However, you can make it so that it will serialize objects further down the line by overriding the function serializeReferences to return true in the class you want to serialize references from. In my example this is the User class. Since our application will never only need the ‘User’ class to be serialized on a result cache I completely overrode the function and made it always return true

class User extends BaseUser{
  public function serializeReferences($bool=null)
  {
    return true;
  }
}

Of course you can set this on a per object instance via $user->serializeReferences(true). Overriding the method the way I did you need to be careful as you could potentially waste a ton of storage space in your result cache.

Hope this saves someone some head banging and confusion on how using a cache could actually cause more queries if not stored properly.

Recently for a project we had the following situation: Users have their own specific databases, however each database has the same schema. Since there are an unlimited number of databases this prevented us from using databases.yml to define which tables need to go to which databases. The user’s database connection would be defined at run time and we needed only certain tables to be bound to that connection. A bunch of googling turned not much up, mostly talking about defining it in the databases.yml file. After a little bit of searching through some of the Doctrine documentation I came across bindComponent. This allows you to tell a model to use a specific connection. For example:

$newConn = Doctrine_Manager::connection('mysql://myuser:mypassword@somehost/somedatabase','UsersConnection');
$manager=Doctrine_Manager::getInstance();
$manager->bindComponent('SomeModel','UsersConnection');

The above would bind the model ‘SomeModel’ to the connection ‘UsersConnection’. To make this so that it is automatically done each time it is executed a good place to add it is a custom filter.

Also if you plan on storing the user’s database passwords it is a good practice to encrypt them. Look into possibly using the class sfCrypt for this.

Recently I received an email from Davert.  He noted that the sfSCMIgnoresTaksPlugin would not work on Windows as Windows has a different directory separator.  He sent over a patch which uses PHP’s DIRECTORY_SEPARATOR instead of the coded “/”.  This makes the plugin compatible on Windows.  Thanks Davert.  You can read more about the plugin and download the most recent release at http://www.symfony-project.org/plugins/sfSCMIgnoresTaskPlugin.

For a client we’ve been working with recently it came to our attention that they needed more frontend servers to keep up with the traffic for their surveys. They use LimeSurvey which is powerful open source survey platform. We set the client up in the cloud to scale as necessary with a load balancer in front. This is when we noticed the problem that LimeSurvey doesn’t work well when a user is bouncing between different frontend servers. LimeSurvey keeps all the user’s session attributes on the local server and not in the database. After googling around for a while, we found other people also had this problem before, and no one had really solved it. We figured we would.

We didn’t feel like doing a ton of extra work to reinvent the wheel in terms of storing the session in the database. We snagged most of the code straight from the Symfony “storage” module which handles it’s session management if you want to store the user sessions in a database. After a quick few modifications, we got it up:

/**
 * Taken from parts the Symfony package "storage" module
 * @author Ashish Datta ashish@setfive.com
 * @author Matt Daum matt@setfive.com
 * Setfive Consulting, LLC
 */                                                     

class setfiveSession {

  private $options = array();
  private $sessionIdRegenerated;

  public function initialize()
  {
    $this->options =  array('db_table' => 'session',
                            'db_id_col'   => 'sess_id',
                            'db_data_col' => 'sess_data',
                            'db_time_col' => 'sess_time');

    session_set_save_handler(array($this, 'sessionOpen'),
                             array($this, 'sessionClose'),
                             array($this, 'sessionRead'),
                             array($this, 'sessionWrite'),
                             array($this, 'sessionDestroy'),
                             array($this, 'sessionGC'));    

    // start our session
    // session_start();
  }                                                         

  /**
   * Closes a session.
   *
   * @return boolean true, if the session was closed, otherwise false
   */
  public function sessionClose()
  {
    // do nothing
    return true;
  }                                                                  

  /**
   * Opens a session.
   *
   * @param  string $path  (ignored)
   * @param  string $name  (ignored)
   *
   * @return boolean true, if the session was opened, otherwise an exception is thrown
   *
   * @throws Exception If a connection with the database does not exist or cannot be created
   */
  public function sessionOpen($path = null, $name = null)
  {
    // we're assuming LimeSurvey all ready has db settings and opened a db connection
    return true;
  }                                                                                                

  /**
   * Destroys a session.
   *
   * @param  string $id  A session ID
   *
   * @return bool true, if the session was destroyed, otherwise an exception is thrown
   *
   * @throws Exception If the session cannot be destroyed.
   */
  public function sessionDestroy($id)
  {
    // get table/column
    $db_table  = $this->options['db_table'];
    $db_id_col = $this->options['db_id_col'];                                                      

    // cleanup the session id, just in case
    $id = $this->db_escape($id);           

    // delete the record associated with this id
    $sql = "DELETE FROM $db_table WHERE $db_id_col = '$id'";

    if ($this->db_query($sql))
    {
      return true;
    }                         

    // failed to destroy session
    throw new Exception(sprintf('%s cannot destroy session id "%s" (%s).', get_class($this), $id, mysql_error()));
  }                                                                                                               

  /**
   * Cleans up old sessions.
   *
   * @param  int $lifetime  The lifetime of a session
   *
   * @return bool true, if old sessions have been cleaned, otherwise an exception is thrown
   *
   * @throws Exception If any old sessions cannot be cleaned
   */
  public function sessionGC($lifetime)
  {
    // get table/column
    $db_table    = $this->options['db_table'];
    $db_time_col = $this->options['db_time_col'];                                                                 

    // delete the record older than the authorised session life time
    $lifetime = $this->db_escape($lifetime); // We never know...
    $sql = "DELETE FROM $db_table WHERE $db_time_col + $lifetime < UNIX_TIMESTAMP()";

    if (!$this->db_query($sql))
    {
      throw new Exception(sprintf('%s cannot delete old sessions (%s).', get_class($this), mysql_error()));
    }                                                                                                      

    return true;
  }             

  /**
   * Reads a session.
   *
   * @param  string $id  A session ID
   *
   * @return string      The session data if the session was read or created, otherwise an exception is thrown
   *
   * @throws Exception If the session cannot be read
   */
  public function sessionRead($id)
  {                                                                                                           

    // get table/column
    $db_table    = $this->options['db_table'];
    $db_data_col = $this->options['db_data_col'];
    $db_id_col   = $this->options['db_id_col'];
    $db_time_col = $this->options['db_time_col'];                                                             

    // cleanup the session id, just in case
    $id = $this->db_escape($id);           

    // delete the record associated with this id
    $sql = "SELECT $db_data_col FROM $db_table WHERE $db_id_col = '$id'";

    $result = $this->db_query($sql);

    if ($result != false && $this->db_num_rows($result) == 1)
    {
      // found the session
      $data = $this->db_fetch_row($result);                  

      return $data[0];
    }
    else
    {
      // session does not exist, create it
      $sql = "INSERT INTO $db_table ($db_id_col, $db_data_col, $db_time_col) VALUES ('$id', '', UNIX_TIMESTAMP())";
      if ($this->db_query($sql))
      {
        return '';
      }                                                                                                            

      // can't create record
      throw new Exception(sprintf('%s cannot create new record for id "%s" (%s).', get_class($this), $id, mysql_error()));
    }                                                                                                               

  }                                                                                                                 

  /**
   * Writes session data.
   *
   * @param  string $id    A session ID
   * @param  string $data  A serialized chunk of session data
   *
   * @return bool true, if the session was written, otherwise an exception is thrown
   *
   * @throws sfDatabaseException If the session data cannot be written
   */
  public function sessionWrite($id, $data)
  {
    // get table/column
    $db_table    = $this->options['db_table'];
    $db_data_col = $this->options['db_data_col'];
    $db_id_col   = $this->options['db_id_col'];
    $db_time_col = $this->options['db_time_col'];                                   

    // cleanup the session id and data, just in case
    $id   = $this->db_escape($id);
    $data = $this->db_escape($data);                

    // update the record associated with this id
    $sql = "UPDATE $db_table SET $db_data_col='$data', $db_time_col=UNIX_TIMESTAMP() WHERE $db_id_col='$id'";

    if ($this->db_query($sql))
    {
      return true;
    }                         

    // failed to write session data
    throw new Exception(sprintf('%s cannot write session data for id "%s" (%s).', get_class($this), $id, mysql_error()));
  }                                                                                                                 

/**
   * Regenerates id that represents this storage.
   *
   * @param  boolean $destroy Destroy session when regenerating?
   *
   * @return boolean True if session regenerated, false if error
   *
   */
  public function regenerate($destroy = false)
  {
    if ($this->sessionIdRegenerated)
    {
      return;
    }                                                           

    $this->sessionIdRegenerated = true;
    $currentId = session_id();
    $newId = session_id();
    $this->sessionRead($newId);        

    return $this->sessionWrite($newId, $this->sessionRead($currentId));
  }                                                                    

  /**
   * Counts the rows in a query result
   *
   * @param  resource $result  Result of a query
   * @return int Number of rows
   */
  protected function db_num_rows($result)
  {
    return mysql_num_rows($result);
  }                                                                    

  /**
   * Extracts a row from a query result set
   *
   * @param  resource $result  Result of a query
   * @return array Extracted row as an indexed array
   */
  protected function db_fetch_row($result)
  {
    return mysql_fetch_row($result);
  }

  /**
   * Executes an SQL Query
   *
   * @param  string $query  The query to execute
   * @return mixed The result of the query
   */
  protected function db_query($query)
  {
    return @mysql_query($query);
  }

  /**
   * Escapes a string before using it in a query statement
   *
   * @param  string $string  The string to escape
   * @return string The escaped string
   */
  protected function db_escape($string)
  {
    return mysql_real_escape_string($string);
  }

}

$sfSessionHandler = new setfiveSession();
$sfSessionHandler->initialize();

This requires you create a table in your MySQL database called session. Here is a dump of the create statement for the table:

CREATE TABLE IF NOT EXISTS `session` (
  `id` int(11) NOT NULL auto_increment,
  `sess_id` varchar(255) NOT NULL,
  `sess_data` text NOT NULL,
  `sess_time` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Basically this uses PHP’s session_set_handler function to manipulate how the PHP retrieves, updates, and stores the user’s session. The final touches were to include this class where the user sessions are started in LimeSurvey. We found them in the index.php and sessioncontrol.php files. Include our file from above just before the session_start(); in the code in those two files. In admin/scripts/fckeditor.265/editor/filemanager/connectors/php/upload.php include the file before the first include. Lastly we need to update a couple locations where it does session_regenerate_id and replace it with $sfSessionHandler->regenerate(). You can find these edits in the following three files: admin/usercontrol.php on line 128, admin/login_check.php line 65, and index.php at lines 207 and 215. You should be up and running now, let us know if you have any problems.

We were recently working on an application that required users to enter a significant amount of complex data that often meant that they had to look things up in between saves. Users kept running into the problem that their sfGuard sessions would timeout before they were able to click “Save” on the form which in turn caused them to loose all of their hard work. Obviously, this is lame so we decided to add a popup warning users that their session had expired and prompting them to login again before saving their data.

We decided to implement this by using setTimeout in Javascript to pop up a window once the user’s session had expired.

Setting the session length for a Symfony user is easy enough, open up app/config/factories.yml and add the following:

all:
  user:
    class: myUser
    param:
      timeout: 1800 # this is the default but you can change it at will (its in seconds)

As it turns out, the tricky part is how do you access this value inside the application? Un-characteristically, I couldn’t find anything in the Symfony documentation about how to access these variables. For whatever reason, sfConfig::get() doesn’t provide access to the variables in factories.yml.

In order to get that timeout value I used (inside a template):

  $userOptions = $sf_user->getOptions();
  $timeout = $userOptions["timeout"];

Anyway, once I figured that out the rest is pretty straightforward.

After $timeout a Javascript function opens a jQuery UI Dialog box informing the user that their session has expired and presents the standard sfGuard sign in form. I override the onSubmit of this form to perform the request via AJAX in the background (so the user doesn’t loose their data) and then if the credentials are valid the dialog closes and the user can go on their way. If the credentials are invalid, the form re-populates with any errors and the user can correct them to re-login to the app.

Hope everyone had a good Thanksgiving!

Every now and then, we use Sphinx to provide full text searching in MySQL InnoDB tables. Sphinx is pretty solid. It’s easy to set up, pretty fast, and easy to deploy.

My one big issue with Sphinx has always been making it play nice with Symfony, specifically Propel. The way Sphinx returns a result set is as an ordered list of [id, weight] for each document it matched. As outlined here the idea is to then hit your MySQL server to return the actual documents and use “ORDER BY FIELD(id, [id list])” to keep them in the right order that you received the list.

The problem is, Propel Criteria objects provide no mechanism to set an ORDER BY FIELD. This is an issue because if you drop Criterias you loose Propel Pagers which generally adds to a lot of duplicated code and is honestly just not very elegant.

Anyway, after some thought I came up with this solution.

If you read through the definition of “Criteria::addDescendingOrderByColumn()”:

	/**
	 * Add order by column name, explicitly specifying descending.
	 *
	 * @param      string $name The name of the column to order by.
	 * @return     Criteria Modified Criteria object (for fluent API)
	 */
	public function addDescendingOrderByColumn($name)
	{
		$this->orderByColumns[] = $name . ' ' . self::DESC;
		return $this;
	}

All it really does is add the second part of the ORDER BY clause to an array which then gets joined up to build the final SQL. Because of this, you can actually just add an element onto the orderByColumns array which will cause Propel to execute an ORDER BY FIELD SQL statement.

To make the magic happen, I sub-classed Criteria and then added a addOrderByField() function to let me add a field to order by as well as a list to order by.

class sfCriteria extends Criteria {

  private $myOrderByColumns = array();

  /**
   * Add an ORDER BY FIELD clause.
   *
   * @param String $name The field to order by.
   * @param Array $elements A list to order the elements by.
   * @return unknown
   */
  public function addOrderByField($name, $elements)
  {
    $this->myOrderByColumns[] = ' FIELD(' . $name . ', ' . join(", ", $elements) . ')';
    return $this;
  }

  public function getOrderByColumns(){
    return array_merge( $this->myOrderByColumns, parent::getOrderByColumns() );
  }
}

To use it, do something like this:

$ids = array(1, 3, 7);
$c = new sfCriteria();
$c->add( SomeModelPeer::ID, $ids, Criteria::IN);
$c->addOrderByField( SomeModelPeer::ID, $ids);
$results = SomeModelPeer::doSelect( $c );

And thats about it. Since sfCriteria is a sub-class of Criteria the code works seamlessly with existing PropelPagers and anything else that expects a Propel Criteria.

Today we are proud to unveil  internOwl.  internOwl is a site for students to research internships and find them.  As the site grows students will be able to gain invaluable insight into the quality of different internships around the country.   Currently the site is being launched with a focus on targeting Massachusetts’ students.  We are excited to see how it performs.

If you are a student in the Amherst or Northampton area you can get a FREE burrito via the following url: http://www.internowl.com/bueno

We hope you all enjoy and there will be more updates about the site to follow as well as the technology used behind the site!

I was slaving over a hot keyboard all Friday!

But at last it is done – FBConnect for sfGuard.

Get it here http://www.symfony-project.org/plugins/sfFbConnectGuardPlugin

A detailed explanation of how to install it and use it is on the Symfony site.

Anyway, the plugin basically just introduces a new table to keep track of Facebook IDs <---> sfGuardUserIds

Here’s a fun nugget. One of the problems with using FB Connect is that you can’t mug a user’s email address from Facebook. Obviously this is a smart move on Facebook’s part but it makes life hard for my Nigerian spammer friends. If you want to snag a user’s email address (or anything else for that matter) while still using Facebook Connect here’s a sketch of how to do it.

Everything is the same except you can’t use Facebook’s FBML to render the FB Connect button. What you want to do instead is trigger the “connect” event by hand. Here is basically how we do it:

1. The user requests to sign up.
2. We pop up a Lightbox using Thickbox
3. We ask the user for their email address and verify that is valid and unique via AJAX in the background.
4. The validation routing sets an attribute on the user using setAttribute() that contains the entered email address.
5. We close the Lightbox and initiate a Facebook Connect request with FB.Connect.requireSession
6. In our createFbUser() method we get the attribute back and save it with the new user

Bam. Got the user’s email address and logged them in via FB Connect.