Doctrine Multiple Connections and Specific Tables

By Matt Daum | 0 Comments

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:

<?php
$newConn = Doctrine_Manager::connection('mysql://myuser:mypassword@somehost/somedatabase','UsersConnection');
$manager=Doctrine_Manager::getInstance();
$manager->bindComponent('SomeModel','UsersConnection');
view raw gistfile1.php hosted with ❤ by GitHub

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.

MySQL and System Time

By Matt Daum | 0 Comments

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.

sfSCMIgnoresTaskPlugin for Symfony released, windows compatible!

By Matt Daum | 1 Comment

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.

LimeSurvey with load balancers, fixing the user sessions.

By Matt Daum | 2 Comments

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:

<?php
/**
* 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 <b>Exception</b> 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 <b>Exception</b> 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 <b>Exception</b> 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 <b>Exception</b> 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 <b>sfDatabaseException</b> 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();
view raw gistfile1.php hosted with ❤ by GitHub

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.

javascript – $(document).ready getting called twice? Here’s why.

By Matt Daum | 4 Comments

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.