Skip to content

Scheme Model

Jump to bottom Edit New page
World Wide Web Server edited this page Jul 4, 2012 · 9 revisions

Sometimes we need to create different objects but with the same behavior. For example, list of text blocks with possibility to reorder this list. And sometimes we need to create relations between entities. What should we do if our RDBMS does not support for example cascade deletion?

For simplification development I've created model which has basic methods for creating, deleting, moving up/down and updating.

Here is example showing how we can create entity like text blocks: [code] <?php include_once("schema.php");

class TextBlockModel extends Schema {

function addTextBlock($title, $body) {
    return $this->_addObject("text_block", array("title" => $title, "body" => $body));
}

function updateTextBlock($id, $title, $body) {
    return $this->_updateObject("text_block", $id, array("title" => $title, "body" => $body));
}

function deleteTextBlock($id) {
    return $this->_deleteObject("text_block", $id);
}

function moveTextBlockUp($id) {
    $this->_moveObjectUp("text_block", $id);
}

function moveTextBlockDown($id) {
    $this->_moveObjectDown("text_block", $id);
}

function getTextBlockList() {
    $this->db->orderby("position");
    return $this->db->get("text_block");
}

function getTextBlock($id) {
    return $this->_getObject("text_block", $id);
}

function TextBlockModel() {
    parent::Model();
    
    $this->_setSchema(array(
        "text_block" => array(
            "fields" => array(
                "id" => array("system" => true),
                "title" => array(),
                "body" => array(),
                "position" => array("system" => true)
            ),
            "position" => true
        )
    ));
}

} ?> [/code]

And here is more complex schema with relations: [code] $this->_setSchema(array( "club" => array( "fields" => array( "id" => array("system" => true), "title" => array(), "description" => array(), "logo" => array(), "hasflyer" => array("type" => "int"), "bar_discount" => array("type" => "int") ), "relations" => array( "club_genre" => "club_id", "announcement" => "club_id", ) ), "genre" => array( "fields" => array( "id" => array("system" => true), "title" => array() ), "relations" => array( "club_genre" => "genre_id" ) ), "club_genre" => array( "fields" => array( "id" => array("system" => true), "position" => array("system" => true), "club_id" => array(), "genre_id" => array() ), "position" => array("club_id") ), "announcement" => array( "fields" => array( "id" => array("system" => true), "club_id" => array("noupdate" => true), "start" => array(), "title" => array(), "price" => array(), "flyer_price" => array(), "guests" => array(), "lineup" => array(), "hasflyer" => array("type" => "int"), "isconcert" => array("type" => "int"), "isrecomended" => array("type" => "int") ), "relations" => array( "announcement_sms_action" => "announcement_id" ) ), "sms_action" => array( "fields" => array( "id" => array("system" => true), "title" => array(), "number" => array(), "code" => array(), "price" => array() ), "relations" => array( "announcement_sms_action" => "sms_action_id" ) ), "announcement_sms_action" => array( "fields" => array( "id" => array("system" => true), "sms_action_id" => array(), "announcement_id" => array() ), "position" => array("announcement_id") ) )); [/code]

And here is source of the Schema model:

[code] <?php if (!defined('BASEPATH')) exit('No direct script access allowed'); /**

  • Scheme Model for CodeIgniter

  • @package CodeIgniter

  • @author Dmitry Matyukhin */ class Schema extends Model { var $schema;

    function _addObject($table, $info) { $table_info = @$this->schema[$table];

     $fields = array();
 if (@$table_info) {
     foreach ($table_info["fields"] as $name => $props) {
         if (!@$props["system"]) {
             $fields[$name] = @$info[$name];
             if (@$props["type"] == "int") {
                 settype($fields[$name], "int");
             }
         }
     }
     
     if (@$table_info["position"]) {
         if (is_array($table_info["position"]))
         foreach($table_info["position"] as $fld) {
             $this->db->where($fld, $fields[$name]);
         }
         
         $this->db->select("max(position) as position");
         $res = $this->db->get($table);
         $position = 0;
         if (@$res && $res->num_rows()) {
             $res = $res->row();
             $position = $res->position;
         }
         
         $fields["position"] = $position + 1;
         
     }
 } else {
     $fields = $info;
 }
 
 log_message("debug", "fields: " . print_r($fields, true));
 log_message("debug", "info" . print_r($info, true));
 
 $this->db->insert($table, $fields);
 
 return $this->db->insert_id();

    }

    function _updateObject($table, $id, $info) { $table_info = @$this->schema[$table];

     $fields = array();
 if (@$table_info) {
     foreach ($table_info["fields"] as $name => $props) {
         if (!@$props["system"] && !@$props["noupdate"]) {
             $fields[$name] = @$info[$name];
             if (@$props["type"] == "int") {
                 settype($fields[$name], "int");
             }
         }
     }
 } else {
     $fields = $info;
 }
 $this->db->where("id", $id);
 $this->db->update($table, $fields);

    }

    function _deleteObject($table, $id) { $table_info = @$this->schema[$table];

     $info = $this->_getObject($table, $id);
 if (!$info) return;
 
 if ($table_info) {
     // Position update
     if (is_array(@$table_info["position"])) {
         $where = array("position > " . $info->position);
         foreach($table_info["position"] as $field) {
             $where[] = $field . " = '". $this->db->escape_str($info->$field) . "'";
         }
         $this->db->query(sprintf("update %s set position = position - 1 where %s", $table, join(' AND ', $where)));
     }
     
     // Cascade delete
     log_message("debug", "cascade");
     if (is_array(@$table_info["relations"])) {
         log_message("debug", "delete");
         foreach($table_info["relations"] as $relation_table => $relation_field) {
             log_message("debug", "talbe " . $relation_table . " > " . $relation_field);
             $this->db->where($relation_field, $info->id);
             $res = $this->db->get($relation_table);
             
             if ($res->num_rows()) {
                 log_message("debug", "found " . $this->db->last_query());
                 foreach($res->result() as $row) {
                     log_message("debug", "found: " . $row->id);
                     $this->_deleteObject($relation_table, $row->id);
                 }
             }
         }
     }
 } else {
     log_message("debug", "no table info");
 }
 $this->db->where("id", $id);
 $this->db->delete($table);

    }

    function _moveObjectUp($table, $id) { $obj = $this->_getObject($table, $id); if (!$obj) return;

     $this->db->where("position", $obj->position - 1);
 $res = $this->db->get($table);
 $row = $res->row();
 
 if (!$row) return;
 $this->db->where("id", $obj->id);
 $this->db->update($table, array("position" => $obj->position - 1));
 
 $this->db->where("id", $row->id);
 $this->db->update($table, array("position" => $row->position + 1));

    }

    function _moveObjectDown($table, $id) { $obj = $this->_getObject($table, $id); if (!$obj) return;

     $this->db->where("position", $obj->position + 1);
 $res = $this->db->get($table);
 $row = $res->row();
 
 if (!$row) return;
 $this->db->where("id", $obj->id);
 $this->db->update($table, array("position" => $obj->position + 1));
 
 $this->db->where("id", $row->id);
 $this->db->update($table, array("position" => $row->position - 1));

    }

    function _getObject($table, $id) { $this->db->where("id", $id); $res = $this->db->get($table); return $res->row(); }

    function _setSchema($schema) { $this->schema = $schema; }

}

?> [/code]

Add a custom footer
Add a custom sidebar
Clone this wiki locally