Programmers Notes

Handling Application Parameters in Yii – Using the Database

The problem

Nearly every large application has some configuration parameters – site admin email, cache time for different blocks, number of latest news, number of items per search or catalogue page etc.

Standard Way

Yii comes with the built-in mechanism for this. You can create a file with an associative array of all your variables, like this:

  1. <?php
  2. return array(
  3.                 ‘brandNew’ => 7// number of days to consider new
  4.                 ‘onSale’ => 7// number of days to consider on sale
  5.                 ‘favourites’ => array(
  6.                                 ‘max’ => 9//max number of last favourites to show
  7.                                 ),
  8.                 ‘search’ => array(
  9.                                 ‘itemsPerPage’ => 20,
  10.                                 ‘tagsInFilter’ => 30,
  11.                                 ),
  12.                 ‘product’ => array(
  13.                                 ‘maxAlts’ => 5//max alternatives
  14.                                 ),
  15.                 ‘cacheTime’ => array(
  16.                                 ‘assets’ => 1800,
  17.                                 ),
  18.         );
  19. ?>

Then put it somewhere, for example in /protected/config/params.php and configure your application to use it:

  1. return array(
  2.         // application components
  3.         ‘components’=>array(
  4. //components go here
  5.         ),
  6.         // application-level parameters that can be accessed
  7.         // using Yii::app()->params[‘paramName’]
  8.         ‘params’=>include(dirname(__FILE__).‘/params.php’),//<– here is our file
  9. );

Usage is simple, just refer to it as to array:

  1. echo Yii::app()->params[‘siteAdmin’];

This is quite convenient when you just want to save some constants. End-user will not be able to change this from admin in any way. Sure, you can create the interface for editing this config and update the file, but much more flexible way is to store the configuration in DB.

The Database Way

To make the life easier, I’ve created an extension, that can be used as an application component. Extension can be downloaded from the Yii extension page. Here I’ll give an overview of it’s work.

First thing to note, it implements IApplicationComponent, so it should implement init() method where initialization is performed. In this method we check if there are any parameters to load. If so – we try to load them. If there is an exception, it means, that no table is present, so we create it and initialize the parameters that should be preloaded with null values

  1. /**
  2.  * Initializes component, creates table if it doesn’t exist and populates preloaded attributes
  3.  * @throws CException Throws exception if table does not exist and auto creation is set to false
  4.  */
  5. public function init()
  6. {
  7.         $this->_init = true;
  8.         $db = $this->getDbConnection();
  9.         $this->preload = $this->preprocessParams($this->preload);
  10.         if (!empty($this->preload) || $this->autoLoad)
  11.         {
  12.                 $sql = ‘SELECT name, value FROM ‘.$this->paramsTableName;
  13.                 if (sizeof($this->preload))
  14.                         $sql .= ‘ WHERE name IN (\’.implode(\’,\’$this->preload).\’)’;
  15.                 $cmd = $db->createCommand($sql);
  16.                 try
  17.                 {
  18.                         $reader = $cmd->query();
  19.                         foreach ($reader as $row)
  20.                         {
  21.                                 $this->add($row[‘name’]$row[‘value’]);
  22.                         }
  23.                 }
  24.                 catch (CException $e)
  25.                 {
  26.                         //table is not present
  27.                         $createTable = true;
  28.                         //if there is no table, then attributes are empty.
  29.                         for ($i = 0$s = sizeof($this->preload)$i < $s$i++)
  30.                         {
  31.                                 $this->add($this->preload[$i]null);
  32.                         }
  33.                 }
  34.         }
  35.         else
  36.         {
  37.                 //check if table exist
  38.                 if ($db->createCommand(‘SHOW TABLES LIKE \’.$this->paramsTableName.\’)->query()->rowCount <= 0)
  39.                         $createTable = true;
  40.         }
  41.         if ($createTable === true)
  42.         {
  43.                 if($this->autoCreateParamsTable)
  44.                         $this->createParamsTable($db,$this->paramsTableName);
  45.                 else
  46.                         throw new CException(Yii::t(‘xparam’,‘Params table “{tableName}” does not exist.’,
  47.                                 array(‘{tableName}’=>$this->paramsTableName)));
  48.         }
  49. }

If parameter is requested, in the __get() method we reuse the parent’s (CAttributesCollection) contains() method, which checks if we have this parameter in the collection already. If so – we simply return it. If not – we call the loadParam() method, it loads if from DB and adds to the component collection. If there is no attribute, it throws an exception. We catch it in the __get() and call the parent::__get(). Here is the code:

  1. /**
  2.  * Returns a property value or an event handler list by property or event name.
  3.  * This method overrides the parent implementation by returning
  4.  * a parameter value if the it exist in the collection or loading it from DB
  5.  * if not.
  6.  *
  7.  * @param string the property name or the event name
  8.  * @return mixed the property value or the event handler list
  9.  * @throws CException if the property/event is not defined.
  10.  */
  11. public function __get($name)
  12. {
  13.         if($this->contains($name))
  14.                 return $this->itemAt($name);
  15.         else
  16.         {
  17.                 try
  18.                 {
  19.                         return $this->loadParam($name);
  20.                 }
  21.                 catch (CException $e)
  22.                 {
  23.                         return parent::__get($name);
  24.                 }
  25.         }
  26. }
  27. /**
  28.  * Returns parameter from DB or cache if it was requested earlier
  29.  *
  30.  * @param string $name
  31.  */
  32. protected function loadParam($name)
  33. {
  34.         if ($this->caseSensitive)
  35.         $name = strtolower($name);
  36.         $db = $this->getDbConnection();
  37.         $res = $db->createCommand(‘SELECT value FROM ‘.$this->paramsTableName.‘ WHERE name=\’.$name.\’)->query();
  38.         if ($res->rowCount == 1)
  39.         {
  40.                 $row = $res->read();
  41.                 $this->add($name,$row[‘value’]);
  42.                 return $row[‘value’];
  43.         }
  44.         else
  45.         {
  46.                 throw new CException(Yii::t(‘xparam’,‘XDbParam->{name} does not exist!’,
  47.                         array(‘{name}’=>$name)));
  48.         }
  49. }

Setter method simply updates the collection. We check if the parameter is already present in DB. If so – we update it. If not – we create it. And, if everything was OK, we add the parameter to the internal collection ($this->add($name,$value)):

  1. /**
  2.  * Sets value of a component property.
  3.  * This method overrides the parent implementation by adding a new param
  4.  * value to the collection and updating the DB.
  5.  * @param string the property name or event name
  6.  * @param mixed the property value or event handler
  7.  * @throws CException If the property is not defined or read-only.
  8.  */
  9. public function __set($name,$value)
  10. {
  11.         if ($this->caseSensitive)
  12.                 $name = strtolower($name);
  13.         $db = $this->getDbConnection();
  14.         if ($db->createCommand(‘SELECT name FROM ‘.$this->paramsTableName.‘ WHERE name=\’.$name.\’)->query()->rowCount >= 1)
  15.         {
  16.                 $sql = ‘UPDATE `’.$this->paramsTableName.‘` SET `value`=:value WHERE `name`=:name’;
  17.                 $cmd = $db->createCommand($sql);
  18.                 $cmd->bindValue(‘:value’$value, PDO::PARAM_LOB);
  19.                 $cmd->bindValue(‘:name’$name, PDO::PARAM_STR);
  20.         }
  21.         else
  22.         {
  23.                 $sql = ‘INSERT INTO `’.$this->paramsTableName.‘`(name, value) VALUES(:name, :value)’;
  24.                 $cmd = $db->createCommand($sql);
  25.                 $cmd->bindValue(‘:name’$name, PDO::PARAM_STR);
  26.                 $cmd->bindValue(‘:value’$value, PDO::PARAM_LOB);
  27.         }
  28.         $cmd->execute();
  29.         $this->add($name,$value);
  30. }

When configuring the extension or using load() or purge() method, we can specify parameters either as array or as comma-separated string. This is achieved by the preprocessing method, which also converts parameter names to lowercase and they are case insensitive (if this is required by the caseSensitive property, it must be set to true). Here is it:

  1. /**
  2.  * Sets value of a component property.
  3.  * This method overrides the parent implementation by adding a new param
  4.  * value to the collection and updating the DB.
  5.  * @param string the property name or event name
  6.  * @param mixed the property value or event handler
  7.  * @throws CException If the property is not defined or read-only.
  8.  */
  9. public function __set($name,$value)
  10. {
  11.         if ($this->caseSensitive)
  12.                 $name = strtolower($name);
  13.         $db = $this->getDbConnection();
  14.         if ($db->createCommand(‘SELECT name FROM ‘.$this->paramsTableName.‘ WHERE name=\’.$name.\’)->query()->rowCount >= 1)
  15.         {
  16.                 $sql = ‘UPDATE `’.$this->paramsTableName.‘` SET `value`=:value WHERE `name`=:name’;
  17.                 $cmd = $db->createCommand($sql);
  18.                 $cmd->bindValue(‘:value’$value, PDO::PARAM_LOB);
  19.                 $cmd->bindValue(‘:name’$name, PDO::PARAM_STR);
  20.         }
  21.         else
  22.         {
  23.                 $sql = ‘INSERT INTO `’.$this->paramsTableName.‘`(name, value) VALUES(:name, :value)’;
  24.                 $cmd = $db->createCommand($sql);
  25.                 $cmd->bindValue(‘:name’$name, PDO::PARAM_STR);
  26.                 $cmd->bindValue(‘:value’$value, PDO::PARAM_LOB);
  27.         }
  28.         $cmd->execute();
  29.         $this->add($name,$value);
  30. }

And, finally, load and purge methods:

  1. /**
  2.  * Loads several params from DB at once. If nothing specified, all parameters
  3.  * are loaded. This saves queries if you plan to use all that params in the
  4.  * next lines.
  5.  *
  6.  * @param array|string $params
  7.  * @throws CException
  8.  */
  9. public function load($params = array())
  10. {
  11.         $params = $this->preprocessParams($params);
  12.         $db = $this->getDbConnection();
  13.         $sql = ‘SELECT name, value FROM ‘.$this->paramsTableName;
  14.         if (sizeof($params) > 0)
  15.         {
  16.                 $sql .= ‘WHERE name IN (\’.implode(\’,\’$params).\’)’;
  17.         }
  18.         $cmd = $db->createCommand($sql);
  19.         $reader = $cmd->query();
  20.         foreach ($reader as $row)
  21.         {
  22.                 $this->add($row[‘name’]$row[‘value’]);
  23.                 $loaded++;
  24.         }
  25.         if ($loaded < sizeof($params))//this will not be thrown if loading all attributes
  26.         {
  27.                 throw new CException(Yii::t(‘xparam’,‘Some of the requested params do not exist!’));
  28.         }
  29. }
  30. /**
  31.  * Deletes specified params (or all params if none specified) from the parameters table
  32.  *
  33.  * @param array|string $params Comma-separated list of params or array of param names
  34.  */
  35. public function purge($params = array())
  36. {
  37.         $sql = ‘DELETE FROM ‘.$this->paramsTableName;
  38.         if (sizeof($params) > 0)
  39.         {
  40.                 $params = $this->preprocessParams($params);
  41.                 $sql .= ‘ WHERE name IN (\’.implode(\’,\’$params).\’)’;
  42.         }
  43.         $db = $this->getDbConnection();
  44.         $db->createCommand($sql)->execute();
  45.         if (sizeof($params) > 0)
  46.         {
  47.                 for ($i = 0$s = sizeof($params)$i < $s$i++)
  48.                 {
  49.                         $this->remove($params[$i]);
  50.                 }
  51.         }
  52.         else
  53.                 $this->clear();
  54. }

Usage Instructions

Using the extension is simple. Like any extension, you should unzip it to the /protected/extensions/ folder and then set it up as an application component. To do this, you should add it with any name to the components array in the application configuration:

  1. ‘par’=>array(
  2.     ‘class’ => ‘application.extensions.dbparam.XDbParam’,
  3.     ‘connectionID’ => ‘db’,//id of the connection component, just the same as with CDbCache
  4. //  ’preload’ => ‘test,test2′, //comma-separated string or array of params to be loaded anyway. Other params are loaded only when requested.
  5. //  ’autoLoad’ => true, //setting to true enables loading of all attributes present in DB in the beginning
  6. //  ’caseSensitive’ => true, //setting to true makes all parameters case sensitive
  7.     ),

‘par’ is the ID of the component, you will refer to it in any place of the application by it’s ID:

  1. Yii::app()->par->test = ’1234′;//set parameter. If it is not present, it will be created
  2. echo Yii::app()->par->test;//output parameter. If it is not present, exception is thrown
  3. //load several parameters in one query. Useful if you;re going to use them in the next lines
  4. Yii::app()->par->load(array(‘test’‘test2′));
  5. //OR
  6. Yii::app()->par->load(‘test,test2′);
  7. //OR
  8. Yii::app()->par->load();//load all parameters
  9. //delete the specified parameters or all of them if none specified
  10. Yii::app()->par->purge(‘test,test2′);//delete test and test 2
  11. //OR
  12. Yii::app()->par->purge(array(‘test’‘test3′));//delete test and test 3
  13. //OR
  14. Yii::app()->par->purge();//delete ALL parameters!