Database Mapper - opinions needed

[NetNerd85]

Hey all,

I have developed a database mapper class and I am not sure if the design of it is very smart. The database mapper class covers the basic CRUD functions that most modules need. The thing about my database mapper class is that you pass the database table name of a "module" to the database mapper class. This will enable you to use the basic CRUD functions without having another class. Say you have a pages table, you instantiate the database mapper class like this:

$pages = new Data_Mapper('pages');
$pages->input_data($_POST);
$pages->create();
$all_pages = $pages->get_all(); 

As you can see there is no need for a pages module. The input_data function takes all information from $_POST and filters it, matches it against the database column information and builds the necessary queries. You can have custom queries, exceptions to ignore input etc.

My question is have I made this way too complex? Even if I have, is it "smart"? I just don't feel that it is a good design

[Orjan]

So you're saying, that you have a class, that you can instance, as a model of a table in a database, and let it do all CRUD^Wiki methods as an interface between the database and the php?

You say it matches towards the $_POST,but then the input fields need to have specific names to make this work I assume.

I'm interested in the concept, but when getting a little bit more advanced, I feel that you would need to extend this class for each database table to specialize upon that very specific data, especially if it's complex. Can you show more of the methods you have implemented for the class, so it's better understandable? I guess you have many more methods than these showed in your example.

Another question is how about the create and get_all, what do they do?

[NetNerd85]

Yes to the first question.

You say it matches towards the $_POST,but then the input fields need to have specific names to make this work I assume.

It needs to have the specific column names in the $_POST array but this is not difficult to manage. You need to call them something in the HTML. You can always make a class to manage HTML generation too.

The base functions for CRUD are:

create() - insert record into the database
retrieve_record() - gets one record by id from db
retrieve_all() - gets all records from db
delete() - removes a record from the db

Other functions for setting up the data_mapper for usage are:

set_query() - use a custom query for CRUD functions
set_input_data() - set the data array to use with CRUD functions
set_input_exceptions() - set the exceptions for CRUD queries (such as when you insert a row in the database you will need to set an exception for the primary key)
set_primary_key() - this can be an array of keys, used with update and select statements

There are of course other functions but these are private for the data_mapper to build the queries only.

You will definitely need to extend the class at this point for advanced features. Say you have a categories module that you want to use for news, pages and files (aka downloads). You will need the category_id, the record_id and the module_id. You would extend the data_mapper and create the necessary function to insert a new category record for each module. This would be as simple as setting up a custom query.

class Pages extend Data_Mapper
{
  public function set_category_association($page_id, $category_id)
  {
    $this->set_query("INSERT INTO category_items (item_id, category_id, module_id) VALUES($page_id, $category_id, $this->module_id)");
    return $this->create();
  }
} 

You don't even need to extend the class you can do all that in the PHP file itself.

It has taken me a while to build this class, it does a lot but does it do too much? Should I stick to the Active Record pattern or something? My concerns are mainly about scalability and performance.

Here is a snippet of the processing. It basically is working with a module called file types - nothing special. The database columns are file_type, file_type_name and file_type_description. This page is what happens after you fill in the fields and click submit.

require_once(PATH_CLASSES . 'data_mapper.class.php');

$module_name = 'file_types';

$module = new Data_Mapper($module_name);

//if ID is set load data and assign smarty variables, send error if no ID match
if(isset($_GET['id']) && is_numeric($_GET['id']))
{
    $module->set_primary_key($_GET['id']);
}

if(isset($_REQUEST['option']))
{
    $option = $_REQUEST['option'];
    
    switch($option)
    {
        case 'add':
            $module->set_input_data($_POST);
            
            $exceptions = array('file_type_id');
            $module->set_input_exceptions($exceptions);
            
            $result = $module->create();
            
            if($result === TRUE)
            {
                $status = 'The File type has been added.';
            }
            else
            {
                $status = 'Failed to create.';
            }
        break;
        
        case 'edit':
            $module->set_input_data($_POST);
            $module->set_primary_key($_POST['file_type_id']);
            
            $result = $module->update();
            
            if($result === TRUE)
            {
                $status = 'Successfully edited.';
            }
            else
            {
                echo 'Failed to update.';
            }
        break;
        
        case 'delete':
            $result = $module->delete();
            if($result === TRUE)
            {
                $status = 'Deleted.';
            }
            else
            {
                $status = 'Failed to delete.';                
            }
        break;
    }
}