DooPhp Model DB Class For Sharded DB PHP MySql Parse Ini PDO

I really like the DooSqlMagic for DooPhp apps with small amount of database tables (less than 100).

It can get you up and running pretty fast and has pretty good performance for ORM. DooPhp has great php performance and a simple basic mvc structure.

However there are three situations I can think of where this kind of generated models in protected/models/ can be problematic.

1. Optimizing complex queries.
2. Extended Vertical Partitioning
3. Extended Horizontal Partitioning (Database sharding)

I had an install of multi user wordpress which has 1000′s of tables over 1000′s of databases. To be able to access this app with the DooPhp framework I also had to add in a class that allows the framework to access multiple databases on the same request.

So what did I do?

1. I replaced Doo::db class with one that was basically the same as the SQL Magic class except all of the ORM is and sql generators is removed. Function query() is rewritten to use PDO bindValues.

2. I have one class in Models which parses php ini files (one file per view). In the php ini files is the sql and bind paramaters. Depending on the blog that is being accessed the db is changed and the table prefix is also changed. This does not actually generate models but instead is a wrapper that quickly routes $variables like $_GET, $_POST into specific queries then executes them using PDO. It would not require much to extend to use PDO Fetch Class for CUD views.

This is a sample of how the db controller looks although db prefix settings are generated on the fly:

    public function index(){

	    Doo::loadModel("BaseDb", false);

            // Initiate class and load /protected/sql/wordpress.ini file

	    $db = new BaseDb('wordpress');

	    // set the db prefix, usually for wordpress this will be wp_

	    $db->prefix = 'wp_2_';
	    $db->values = $this->params; // :post_type :post_status :limit

	    $db->sql_query = 'get_posts';
	    $results = $db->query();

	    // set query to get_tags
	    $db->sql_query = 'get_tags';

	    foreach($results as $result) {

		//add each post to post array
		$id = $result['ID'];
		$posts[$id ]['posts'] = $result;

		//get tags for this post ID
		$db->values['id'] = $id ;
		$posts[$id ]['tags'] = $db->query();

	    }

	    echo '
';
	    print_r($posts);
	    echo '
';
	    //$this->renderc('index', $posts);

    }

If you are interested in more you can visit here:

Parse Ini PDO Sql

I like Parse Ini File. It has a simple syntax and parse_ini_file is reasonably fast because its written in C.

WordPress Controller

Ini File Example