Blog

Recently for a client we had a very peculiar problem: a nightly script which checked if a person has done a certain action that day was always flagging everyone. We tested the script multiple times on our servers and it always worked fine. The query had something similar to this:

SELECT * FROM action_table WHERE DATE(action_table.time)=DATE(NOW())

Well after a while of trouble shooting we found out that the system clock on the clients server was skewed and in the wrong time zone. We synced the machines clock and update its time zone. Before it had been in UTC time and we switched it over to EST. What is interesting is that MySQL did not respect the new time zone of the clock, it was still reporting as if the system was set to UTC. We then did a soft restart(reload) on the MySQL service, but it still maintained that it was in a UTC timezone and not EST. It took a hard restart of the MySQL service to have it respect the EST timezone.

All in all, as far as we can tell you need to restart the MySQL service to have it respect a new timezone.

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.

Recently we found ourselves having a really weird problem on a project: Every time a page was loaded it seemed a bunch of different Javascript functions were being called multiple times and making many widgets on the page break and we couldn’t figure it out. Some of the functions were our own code, some part of the package we were using. After a while we narrowed it down to that all the functions in the

$(document).ready(...);

we’re being called twice. We had never seen this. After about an hour of removing javascript files and just headbanging, and many thanks to Ashish, we found the root cause. We had written in a quick hack for a late proof of concept to string replace on the entire HTML of a page a specific string. We did it this way:

$('body').html($('body').html().replace(/{REPLACETEXT}/i, "More important text"));

Basically we used a regex to parse the entire HTML tree and then replace it with the updated text. Unknowingly this caused the document ready event to be triggered again(though now it makes sense), causing many widgets to get extra HTML.

Let this save you some headbanging.

So earlier today I was working on a Facebook App and wanted to use “partials” in a similar fashion as Symfony’s partials. At this point, I realized I had no idea how Symfony placed variables into the current execution scope when you do things like

include_partial("somePartial", array("foo" => $foo, "bar" => $bar));

A bit of digging led me to the extract() function in PHP.

From the documentation, “extract — Import variables into the current symbol table from an array”.

Pretty neat.

Every now and then, I’ll end up having to scrape HTML pages for some content. I know, I know, there’s like a bazillion different ways to do this, but I *really* like doing it in PHP so I can jack right into Symfony. Usually, I just get down and dirty with the PHP DOM and use XPath to select nodes within the document. The problem with this is that the XPath sucks and the PHP implementation sucks…alot.

But hold on, we know a selector engine that doesn’t suck! The jQuery selector engine, called Sizzle is probably one of the best CSS/DOM selector engines to use. Turns out there is a PHP port! Enter phpQuery

At its root, phpQuery is a port of the jQuery selector syntax to PHP. Additionally, phpQuery includes dozens of the jQuery traversal methods like next(), prev(), find(), and so on. It also implements the CSS3 filters like :first, :last, :eq, ect.

Anyway, if you’re tired of suffering through the PHP XPath implementation and dig jQuery then you should definitely give phpQuery a whirl.

Happy holidays everyone! Hope everyone had an awesome Christmas and is getting excited for a fun New Years Eve and then a great 2010.

Anyway, since the sun never sets on the Setfive empire I was actually doing some coding earlier when I ran across an interesting little problem. What I was looking to do was “match” a string input against a set of acceptable strings. The caveat was that the inputs might have spelling mistakes or typos. For example, an input might be “onnlinee ad” matching against ["online ad", "video", "news", "online"] with the goal of matching “online ad”.

Unfortunately, you can’t simply iterate over the two strings matching letters because a single wrong letter will cause you to miss all of the rest. Remembering back to some old engineering courses I found my way over to the Hamming distance article on Wikipedia. From there, I made my way over to the Levenshtein distance article which proved extremely useful.

So, at this point I figured I wanted to minimize the Levenshtein distance and that would be my matching string. Fortunately enough, PHP has a built in function to calculate Levenshtein distances! levenshtein() The Levenshtein distance works pretty well for what I was looking to do. In addition, PHP has another built in function – similar_text() for comparing two strings. similar_text will return the number of matching characters in the two input strings.

Anyway, the only thing to be aware of is that both these functions have really bad running times. similar_text clocks in at O(n^3) where n is the length of the longest string and levenshtein runs at O(m*n) where m and n are the lengths of the input strings.

Well that’s it for now. Happy string comparing.

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!

Wow its been awhile!

We’ve been insanely busy over the last month or so. We launched Setfive Ventures and are anxiously anticipating the launch of both WeGov and OmniStrat in the immediate future. There are also a handful of internal project that should be rolling out before Christmas. Get Excited.

Anyway, the jQuery UI Dialog class is pretty sweet. Basically, it provides a class to display a modal dialog box from a regular old DOM element (a div, span, or whatever.)

One of the thing that isn’t explained well (or at all?) in the documentation is that you can create a dialog with on the fly HTML! I found this out after posting on the Google Group asking why this feature didn’t exist (it does. Ashish fail.)

So if you want to create a dialog with on the fly HTML all you need to do is:

$("<p>Hello World!</p>").dialog();

Pretty sweet.

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.