Database driven routes in CodeIgniter

[eluser]neillyons.co[/eluser]
See updated version of this here

Using a pre_system hook the routes array can be populated from your database.

A route is a CodeIgniter controller action alias. Used to build search engine friendly sites. eg posts/database-driven-routes-in-codeigniter is mapped to posts/get/2

Step 1

Define the hook by inserting the code below into the hooks.php file ( ..system/application/config/hooks.php ).

Code:

require_once BASEPATH."application/config/database.php";

$hook['pre_system'] = array(
                                'class'    => 'Router_Hook',
                                'function' => 'get_routes',
                                'filename' => 'Router_Hook.php',
                                'filepath' => 'hooks',
                                'params'   => array(
                                    $db['default']['hostname'],
                                    $db['default']['username'],
                                    $db['default']['password'],
                                    $db['default']['database'],
                                    $db['default']['dbprefix'],
                                    )
                                );

This is a pre system hook, which means it is called before any routing has occurred. This hook will call the function get_routes with the database connection details passed as a paramater.

Step 2

Create a file called Router_Hook.php in the hooks directory ( ..system/application/hooks/Router_Hook.php ) and insert the code below.

Code:

if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Router_Hook
{
        /**
         * Loads routes from database.
         *
         * @access public
         * @params array : hostname, username, password, database, db_prefix
         * @return void
         */
    function get_routes($params)
    {
        global $DB_ROUTES;

        mysql_connect($params[0], $params[1], $params[2]);

        mysql_select_db($params[3]);

        $sql = "SELECT * FROM {$params[4]}routes";
        $query = mysql_query($sql);

        $routes = array();
        while ($route = mysql_fetch_array($query, MYSQL_ASSOC)) {
            $routes[$route['route']] = $route['controller'];
        }
        mysql_free_result($query);
        mysql_close();
        $DB_ROUTES = $routes;
    }
}

The database schema

Code:

id, route, controller, created, updated

This function populates a keyed array from the database and makes the array globally available.

Step 3

Insert the code below into the routes.php file ( ..system/application/configs/routes.php ) after the scaffolding trigger.

Code:

global $DB_ROUTES;
if(!empty($DB_ROUTES)) $route = array_merge($route,$DB_ROUTES);

This code merges the array populated from the database with the array defined in the routes.php file.

Step 4

In the config.php file ( ..system/application/configs/config.php ) set enable hooks to TRUE.

Code:

$config['enable_hooks'] = TRUE;

Now when CodeIgniter is routing a request it will consider the routes from the database.


This technique will work as it is, but I recommend using DataMapper to save routes to the database aswell. Below is my route datamapper model.

Code:

class Route extends DataMapper
{
    var $validation = array(
        array(
            'field' => 'route',
            'label' => 'URL alias',
            'rules' => array('required', 'trim', 'unique', 'min_length' => 3, 'max_length' => 255, 'reserved_word', 'dash_url', 'alpha_slash_dot')
        ),
        array(
            'field' => 'controller',
            'label' => 'Controller URL',
            'rules' => array('required')
        )
    );


    /**
     * Constructor
     */
    function __construct()
    {
        parent::__construct();
    }


    /**
     * Validation function. Checks field value isn't a controller name.
     *
     * @access private
     * @param  string : field name
     * @return bool
     */
    function _reserved_word($field)
    {
        $controller = array();

        $this->load->helper('directory');

        $map = directory_map(BASEPATH."application/controllers");

        foreach ($map as $value)
        {

                $filename_array = explode(".", $value);
                if ($filename_array[1] == EXT)
                {

                    //If file extension is php then store filename in array.
                    $controller[] = $filename_array[0];
                }
        }

        if (in_array($this->{$field}, $controller))
        {
            return FALSE;
        }
        else
        {
            return TRUE;
        }
    }


    /**
     * Converts the route into a search engine friendly URL
     *
     * @access private
     * @param string : field value
     * @return void
     */
    function _dash_url($field)
    {
        $this->{$field} = dash($this->{$field});
    }
}

This provides a great way to check the route to be saved isn’t an existing controller name. Preventing routes with the same name pointing to different controller actions.


Insert the code below at the bottom of the inflector_helper.php file ( ..system/application/helpers/inflector_helper.php )

Code:

/**
* Dash
*
* Takes multiple words separated by spaces and dashes them
*
* @access    public
* @param    string
* @return    str
*/
if ( ! function_exists('dash'))
{
    function dash($str)
    {
        return preg_replace('/[\s_]+/', '-', strtolower(trim($str)));
    }
}

This function is called when saving the route to the database. eg. paGe/HELLO WORLD to page/hello-world.

Let me know what you think of this feature.

[eluser]Alan Wizemann[/eluser]
I am strongly considering implementing this hook solution to my routing issue (I am currently using a flat file that gets read into our route file that works great but harder to manage).

The problem that I had with a similar approach, and the problem I see with this solution is that it still creates two database connections, so this could dramatically effect scale / memory use.

Have you seen any impact to using a solution like this?

[eluser]garymardell[/eluser]
Alan, the first connection is opened and then closed again in the routes hook, then a new connection will be opened for the controller, etc. This means there will only be one open at any single time. However it will effect the speed of the application slightly as it has to fetch the routes. Maybe it could cache the routes file somewhere that it gets from the database, then if you update a route, add or delete the cache file will be deleted and rebuilt on the next page load. That would decrease the impact a lot.

[eluser]neillyons.co[/eluser]
@Alan Wizemann

The reason two database connections are created is because I can't call CodeIgntier's get_instance() function to initialize the Database Class. I don't think this will have a big impact on performance though.

[eluser]Alan Wizemann[/eluser]
Thanks for your reply, I think the way I am going about it is better for speed (we really need to keep an eye on scale) but I really think your solution is perfect for many apps out there. I use an approach where my admin bakes a serialized array of my routes from its app logic into a static file, the route file loads this and creates its array for CI to use, its very very fast. I would like to see if I can compare the speed of two connections versus reading a file. Regardless, we can push the file array after it has been parsed into the APC of PHP and really see a kick in speed.

[eluser]neillyons.co[/eluser]
Database driven routes: Benchmark test.

Code:

Default CodeIgniter set up.
$config['enable_hooks'] = FALSE;
2 routes.
Average Page rendered in 0.072 seconds

Database driven routes.
$config['enable_hooks'] = TRUE;
10,000 routes.
Average Page rendered in 0.425 seconds

Routes written to file and then included.
$config['enable_hooks'] = TRUE;
10,000 routes.
Average Page rendered in 0.376 seconds

[eluser]Alan Wizemann[/eluser]
Wow, thanks for this benchmark - it really does show a pretty decent speed increase to the include, aside from time though, I bet memory and CPU utilization are also lower. Although this is an extreme case, some of our route files can be upwards of 1000 records, so I have to say this definitively proves we are going the right route for ultimate speed (no pun intended).

I think the include method with APC active, can really make things cook.

Thanks again!

[eluser]doubleplusgood[/eluser]
Bit late to this one and still trying to process the excellent example...

However, I was wondering if I was to create a 'pages' and 'blog' tables for example. Could the insert/update operations for those tables also post to the routes database or would I not need to go to that extent? Rather than keeping all of the 'slugs' in the pages and blog tables?

Thank you.

[eluser]Spir[/eluser]
edit: nevermind I just underdtood Datamapper was a lib http://stensi.com/datamapper/

[eluser]Clooner[/eluser]
I use also a form of database driven routes in my CI site and it works really straight forward with no speed loss what so ever. It uses only a couple of lines of code.

It doesn't use hooks or hacks into the system and it uses the standard AR from CI.

In my router file I check if a file exists. If it doesn't exist it uses the default routes which in all cases forwards you to a controller which generates an array from the database. This array contains the routing information. This array is then saved in a cache file. The controller redirects itself and next time the router is accessed the cache file is loaded. The cache file is updated once a day.

Simple, Effective and no speed loss.