Database Driven Routes without hooking or datamapper [UPDATED]

[eluser]ranjudsokomora[/eluser]
Look at replies for updated code

Good Day CI Coders,
I was looking for dynamic route building the other day on here and found http://ellislab.com/forums/viewthread/121486

Here is my version of the above without building a hook, or using DataMapper:
I removed EllisLabs comments because I couldn't post on the forum with them include.
You can always look at them by opening /system/libraries/router.php

Code:

<?PHP IF ( ! defined('BASEPATH')) exit('No direct script access allowed');
# REMOVED EllisLab Comments to save space, to view comments
# open /system/libraries/router.php
class MY_Router extends CI_Router
{
    var $database_connects = array();
    var $route_conn_now        =    array();
    
    function MY_Router()
    {
        parent::CI_Router();
    }
    
    function _set_routing()
    {
        if ($this->config->item('enable_query_strings') === TRUE AND isset($_GET[$this->config->item('controller_trigger')]))
        {
            $this->set_class(trim($this->uri->_filter_uri($_GET[$this->config->item('controller_trigger')])));
            if (isset($_GET[$this->config->item('function_trigger')]))
            {
                $this->set_method(trim($this->uri->_filter_uri($_GET[$this->config->item('function_trigger')])));
            }
            return;
        }
        
        @include(APPPATH.'config/routes'.EXT);
        # If you want all your routes to come from a database remove the above line
        # Keep in mind your database must contain the default route(s) or CI will not function.
        
        ## Code Authored By: Ranjudso Komora
        // Load the database.php file.
        @include(APPPATH.'config/database'.EXT);
        $this->database_connects    =    (!isset($db) OR !is_array($db)) ? array() : $db;
        
        IF (!EMPTY($this->database_connects))
        {
            $this->route_conn_now    =     (ISSET($this->database_connects['router'])) ? $this->database_connects['router'] : $this->database_connects['default'];
            SWITCH (trim($this->route_conn_now['dbdriver']))
            {
                case "mysql";
                    $link = mysql_connect($this->route_conn_now['hostname'], $this->route_conn_now['username'], $this->route_conn_now['password']);
                    IF (!$link)
                    {
                        $error_message    =    "An error occured connecting to your database host<br />Check your connection setttings.";
                        show_error($error_message);
                    }
                    $db        =    mysql_select_db($this->route_conn_now['database']);
                    IF (!$db)
                    {
                        $error_message    =    "An error occured connecting to your database.<br />Check your connection settings.";
                        show_error($error_message);
                    }
                    $mysql        =    "SELECT * FROM tblRoutes";
                    $my_query    =    mysql_query($mysql);
                    IF (!$my_query)
                    {
                        $error_message    =    "An error occured while trying to query your database.";
                        show_error($error_message);
                    }
                    while ($data = mysql_fetch_assoc($my_query))
                    {
                        $route[trim($data['vchRoute'])]    =    trim($data['vchController']);
                    }
                break;
            }
        }
        
        #End Of Custom Code
        
        
        $this->routes = ( ! isset($route) OR ! is_array($route)) ? array() : $route;
        unset($route);
        $this->default_controller = ( ! isset($this->routes['default_controller']) OR $this->routes['default_controller'] == '') ? FALSE : strtolower($this->routes['default_controller']);    
        $this->uri->_fetch_uri_string();
        if ($this->uri->uri_string == '')
        {
            if ($this->default_controller === FALSE)
            {
                show_error("Unable to determine what should be displayed. A default route has not been specified in the routing file.");
            }
            
            if (strpos($this->default_controller, '/') !== FALSE)
            {
                $x = explode('/', $this->default_controller);
                $this->set_class(end($x));
                $this->set_method('index');
                $this->_set_request($x);
            }
            else
            {
                $this->set_class($this->default_controller);
                $this->set_method('index');
                $this->_set_request(array($this->default_controller, 'index'));
            }
            $this->uri->_reindex_segments();
            log_message('debug', "No URI present. Default controller set.");
            return;
        }
        unset($this->routes['default_controller']);
        $this->uri->_remove_url_suffix();
        $this->uri->_explode_segments();
        $this->_parse_routes();        
        $this->uri->_reindex_segments();
    }
}

Save the above as MY_Router.php in your /application/libraries folder.

If you routes are stored in a separate database from your default database you will need to create a new database array with the name of "router" and set the appropriate information.

I built the above with a SWITCH statement, but only put the logic for MYSQL in. It should be easy for anyone to use other database engines, as you would just need to add a case statement and logic for your dbdriver type.

My scheme for MYSQL

Code:

CREATE TABLE `tblRoutes` (
  `vchRoute` VARCHAR(250) NOT NULL,
  `vchController` VARCHAR(250) NOT NULL
)

[eluser]ranjudsokomora[/eluser]
Anyone have suggestions or comments about this?
Anyone, Anyone?

[eluser]Addow[/eluser]
hi ranjudsokomora,

Let's drop some feedback on this piece of code. First, I may say I liked the approach to exclude hooks and the use of Data Mapper by extending CI's Router model. I am struggling with a huge amount of routes because I want a dynamic language based route system, so I liked the database driven approach. However, I do not want to loose too much time on the route loading and after some benchmarking, so I prefer the cached db approach (topic), which is 25 to 40% faster in execution time.

I think you can modify this version to a cached version too (maybe a good extra feature), which will have approximately the same benchmark results as the cached db driven hook of neillyons.info.

[eluser]ranjudsokomora[/eluser]
Hi Addow,
Thanks for the suggestion. I will work on that today.

[eluser]ranjudsokomora[/eluser]
Hi Addow,
I liked your suggestion of cached results. So here is that version! With the database class right in the Router class In any case here are the instructions:

1) Download the zip file attached to this post
2) Extract both files to the ./system/application/libraries folder
3) Modify your ./system/application/config/database.php to reflect your database connection settings


SIDE NOTE: If your routes table is in a database other than "default" you will need to add this to your ./system/application/config/database.php file.

Code:

$db['router']['hostname'] = "localhost";
$db['router']['username'] = "";
$db['router']['password'] = "";
$db['router']['database'] = "";
$db['router']['dbdriver'] = "";
$db['router']['dbprefix'] = "";
$db['router']['pconnect'] = TRUE;
$db['router']['db_debug'] = TRUE;
$db['router']['cache_on'] = FALSE;
$db['router']['cachedir'] = "";
$db['router']['char_set'] = "utf8";
$db['router']['dbcollat'] = "utf8_general_ci";

Of course filling in your information.

Here is my SQL table

Code:

CREATE TABLE `tblRoutes` (
  `routeID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `vchRoute` VARCHAR(150) NOT NULL,
  `vchController` VARCHAR(150) NOT NULL,
  `dteEntryDate` DATETIME NOT NULL,
  `dteModifyDate` DATETIME NOT NULL,
  PRIMARY KEY (`routeID`)
)

This extended router class is very customizable and I tried to make easy to edit things.
Let me know how I did.

Thanks

[eluser]Addow[/eluser]
Hi ranjudsokomora,
Wow, that was a quick cache integration. I did not expect to results this fast. I think you did a great job. Hope other people will use this too. The benchmarks are quite similar to the other approach, so time is no longer an issue.

I really like the way you customized it. I noticed a little typo (Dynomite route) by the way Good job!

In the meanwhile I build my own route cache system, because I use language based URI's, the DB cached approach did not solve all my issues Currently I use your non-hook approach, but I replaced the DB input by a language based input. For every language i wrote a nav_lang.php file which contains language entries for every method in a controller or a controller itself. I can use these language entries when I display a layout for a certain language.

For example.

Code:

$lang['nav_home']             = "home";
$lang['nav_home_online']      = "home/online";
$lang['nav_news']             = "news";
$lang['nav_news_article']     = "news/article";

My cache generator scrolls through all nav_lang.php files, one for every language, then generates a route for every entry by reversing the order and replacing the underscores by forward slashes and some other parse stuff.

This is the result after generating the routes cache file (english/dutch/french/german):

Code:

// Lang routes for controller/method: [home]
$route['[a-z]{2}/home'] = "home";
$route['[a-z]{2}/accueil'] = "home";
$route['[a-z]{2}/start'] = "home";

// Lang routes for controller/method: [home/online]
$route['[a-z]{2}/home/online/(.+)'] = "home/online/$1";
$route['[a-z]{2}/accueil/online/(.+)'] = "home/online/$1";
$route['[a-z]{2}/start/online/(.+)'] = "home/online/$1";

// Lang routes for controller/method: [news]
$route['[a-z]{2}/nieuws'] = "news";
$route['[a-z]{2}/news'] = "news";
$route['[a-z]{2}/actualite'] = "news";
$route['[a-z]{2}/nachrichten'] = "news";

// Lang routes for controller/method: [news/article]
$route['[a-z]{2}/nieuws/artikel/(.+)'] = "news/article/$1";
$route['[a-z]{2}/news/article/(.+)'] = "news/article/$1";
$route['[a-z]{2}/actualite/article/(.+)'] = "news/article/$1";
$route['[a-z]{2}/nachrichten/artikel/(.+)'] = "news/article/$1";

Anyway, this was just an illustration of my situation. Great work and I support your approach next to neillyons.info's. Well done.

[eluser]ranjudsokomora[/eluser]
Addow,
What envirorment are you using this on? I built it on Windows (XP) Apache, and PHP5.

In my mind the perfect DB driven routes would be this would go something like this:

1) User comes to site www.example.com/index.php/welcome
2) The router function checks the ./system/application/config/routes.php file for a possible match
3) If a match is found done, else
4) check the ./system/cache/routes.cache for a possible match if found done, else
5) The router function does some queries to the DB for that single route if found updates the count of that route, if the count is greater than x (any number specfied) then it adds it to the ./system/cache/routes.cache
6) if not found in any of the above we can will know it is a 404

That approach would greatly cut down on proccessing time. There would most certianly be some challenging logic to go with it though. In the ./system/application/config/routes.php file you can put something like this:

Code:

$route['test/:(any)'] = "welcome/$1";

I don't think you can query by using expressions.

PS. What were the benchmarks compared to neillyons.info's

[eluser]Addow[/eluser]
Hi ranj,
I am running on two different environments: Centos/Apache/PHP5 and UbuntuServer/Lighttpd/PHP5.

My benchmark timings of neillyons.info’s:

Code:

Total Execution Time      0.0480
Total Execution Time      0.0479
Total Execution Time      0.0467
Total Execution Time      0.0468
Total Execution Time      0.0453
Total Execution Time      0.0507
Total Execution Time      0.0484
------------------------------
Average Execution Time    0.0476

My benchmark timings of ranjudsokomora's:

Code:

Total Execution Time      0.0488
Total Execution Time      0.0556
Total Execution Time      0.0492
Total Execution Time      0.0599
Total Execution Time      0.0572
Total Execution Time      0.0468
Total Execution Time      0.0515
------------------------------
Average Execution Time    0.0527

The execution timings are quite similar, but neillyons.info’s is a fraction faster.
So, we may conclude that both approaches are great and have the same advantages. Your version does a little more checks and verifying, and therefor a fraction slower. But we are talking about milliseconds, so no one will notice

[eluser]ranjudsokomora[/eluser]
Addow,
How many routes did you have in the DB when running benchmarks?
I got around the same benchmarks, and ran the test against 15000 DB records.

[eluser]Addow[/eluser]
Ik had around 22000 records in DB.