SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Jan 2007
    Posts
    323
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    redundant classes

    It seems that for every database table (mysql) we have a class that has the standard insert, update, fine, delete, etc. methods. It has become very tedious creating the classes for these (table gateway classes I believe?). I have researched a bit and it seems a data mapper would help here? Any suggestions?

    I could not find a concrete example for php4 of a data mapper.

  2. #2
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure what setup you are using, but the ideal situation would be to have ONE class that can automatically perform insert, update, find, delete no matter what table you are dealing with. A common type of class to use for this situation is the ActiveRecord pattern.

    You may have mutiple classes for every table under the ActiveRecord pattern, but they really only extend and inherit all the base class methods while only having to specify the table name. If you're going to brew your own, a good place to start is with the "SHOW COLUMNS FROM table" query. Good luck

  3. #3
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seems that for every database table (mysql) we have a class that has the standard insert, update, fine, delete, etc. methods.

    It's matter of taste, you could live with one DAO class:

    PHP Code:
    class DAO {
        private 
    $table;
        private 
    $conn;

        public function 
    __construct($table) {
            
    $this->table $table;
            
    $this->conn DB::Connect();
        }

        public function 
    getAllRows($fields) {
            if (
    is_array($fields)) { $fields implode(',',$fields); }
            
            
    $sql "SELECT $fields FROM {$this->table}";
            return 
    $this->_executeSQL($sql);
        }

        public function 
    getOneRow($fields$key$value) {
            if (
    is_array($fields)) { $fields implode(',',$fields); }
            
            
    $sql "SELECT $fields FROM {$this->table} WHERE $key = \$1 LIMIT 1";
            return 
    $this->_executeSQLWithParams($sql,Array($value));
        }
    ... 
    // other methods for CRUD
        
    private function _executeSQL($sql) {
            
    $result pg_query($this->conn$sql);
                    ...
            }
        }

        private function 
    _executeSQLWithParams($sql$params) {
            
    $result pg_query_params($this->conn$sql$params);
                    ....
        } 
    Last edited by Mastodont; Jul 3, 2007 at 02:09.

  4. #4
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could make an abstract base class, and let your gateways inherit from it?

    Switching table gateway with data mapper or active record is not going to solve your problem.

  5. #5
    SitePoint Addict
    Join Date
    Jan 2007
    Posts
    323
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you recommend one out of the three? gateway, active record, or data mapper? I was under the impression the data mapper pattern was basically a base class with gateways inheriting.

  6. #6
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not suggesting you switch to a different pattern like ActiveRecord, I was merely offering some insight on what I use for my own purposes to solve this problem. Any solution will work as long as you know the columns of the table you are dealing with. You just have to make your functions general enough to work with any table, which is actually fairly easy to do since the SQL for the your target database (like MySQL) is always going to use the same syntax for general SELECT / UPDATE / INSERT / DELETE operations.

    One of the easiest ways to achieve this is to have multiple classes inheriting from one abstract base class that has all these functions, regardless of what method you choose to go with.

  7. #7
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by atDev View Post
    Do you recommend one out of the three? gateway, active record, or data mapper? I was under the impression the data mapper pattern was basically a base class with gateways inheriting.
    You can implement either type as a generic class or as one-per-datatype. The difference between a data mapper and a table gateway, is that with a data mapper, the domain class is completely ignorant of the data mapper. This decouples the domain model from the persistence layer. For most PHP applications, I'd say that a data mapper is overkill. I prefer table gateway.

  8. #8
    SitePoint Addict
    Join Date
    Jan 2007
    Posts
    323
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I guess Mastodont's post above is a good starting point.

    I guess the standard insert, update, and delete functions are straightforward but in the end there is always going to be minor changes to a query like limit clauses and unique queries. I guess what im asking is does anyone have an example of a well put together base abstract class for use with table gateway? In other words a class that has most of the "generic" methods that will be used.

  9. #9
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by atDev View Post
    So I guess Mastodont's post above is a good starting point.

    I guess the standard insert, update, and delete functions are straightforward but in the end there is always going to be minor changes to a query like limit clauses and unique queries. I guess what im asking is does anyone have an example of a well put together base abstract class for use with table gateway? In other words a class that has most of the "generic" methods that will be used.
    The basic INSERT/UPDATE/DELETE is quite simple to do. You just need to make the table name a variable. Here's the code for a basic table gateway, I have used in some applications:
    PHP Code:
    <?php
      
    /**
       * A generic table gateway.
       */
    class PdoExt_TableGateway
    {
      protected 
    $tableName;
      protected 
    $pkey;

      protected 
    $db;
      protected 
    $columns NULL;

      
    /**
       *
       * @param  $tableName  string  Name of the table
       * @param  $db         PdoExt  The database connection
       */
      
    function __construct($tableName$db) {
        
    $this->tableName $tableName;
        
    $this->db $db;
        
    $this->pkey $this->getPKey();
      }

      function 
    reflect() {
        if (!
    $this->columns) {
          
    $this->columns $this->db->getTableMeta($this->tableName);
        }
        return 
    $this->columns;
      }

      function 
    getPKey() {
        foreach (
    $this->reflect() as $column => $info) {
          if (
    $info['pk']) {
            return 
    $column;
          }
        }
      }

      function 
    getTable() {
        return 
    $this->tableName;
      }

      function 
    getColumns() {
        return 
    array_keys($this->reflect());
      }

      
    /**
       * Selects a single row from the table.
       * If multiple rows are matched, only the first result is returned.
       * @param  $condition  array  Associative array of column => value to serve as conditions for the query.
       * @return array
       */
      
    function fetch($condition) {
        
    $query "DELETE FROM ".$this->db->quoteName($this->tableName);
        
    $where = Array();
        
    $values = Array();
        foreach (
    $condition as $column => $value) {
          
    $where[] = $this->db->quoteName($column)." = :".$column;
          
    $values[$column] = $value;
        }
        if (
    count($where) == 0) {
          throw new 
    Exception("No conditions given for fetch");
        }
        
    $query .= "\nWHERE\n    ".implode("\n    AND "$where);
        
    $result $this->db->pexecute($query$values);
        return 
    $result->fetch(PDO::FETCH_ASSOC);
      }

      
    /**
       * Inserts a row to the table.
       * @param  $data       array  Associative array of column => value to insert.
       * @return boolean
       */
      
    function insert($data) {
        
    $query "INSERT INTO ".$this->db->quoteName($this->tableName);
        
    $columns = Array();
        
    $values = Array();
        if (
    is_object($data)) {
          
    $data $data->getArrayCopy();
        }
        foreach (
    $this->getColumns() as $column) {
          if (
    array_key_exists($column$data)) {
            
    $columns[] = $column;
            
    $values[$column] = $data[$column];
          }
        }
        
    $query .= " (".implode(","array_map(Array($this->db'quoteName'), $columns)).")";
        
    $query .= " VALUES (:".implode(", :"$columns).")";
        return 
    $this->db->pexecute($query$values);
      }

      
    /**
       * Updates one or more rows.
       * @param  $data       array  Associative array of column => value to update the found columns with.
       * @param  $condition  array  Associative array of column => value to serve as conditions for the query.
       * @return boolean
       */
      
    function update($data$condition) {
        
    $query "UPDATE ".$this->db->quoteName($this->tableName)." SET";
        
    $columns = Array();
        
    $values = Array();
        
    $pk $this->getPKey();
        if (
    is_object($data)) {
          
    $data $data->getArrayCopy();
        }
        foreach (
    $this->getColumns() as $column) {
          if (
    array_key_exists($column$data) && $column != $pk) {
            
    $columns[] = $this->db->quoteName($column)." = :".$column;
            
    $values[$column] = $data[$column];
          }
        }
        
    $query .= "\n    ".implode(",\n    "$columns);
        
    $where = Array();
        foreach (
    $condition as $column => $value) {
          
    $where[] = $this->db->quoteName($column)." = :where_".$column;
          
    $values["where_".$column] = $value;
        }
        if (
    count($where) == 0) {
          throw new 
    Exception("No conditions given for update");
        }
        
    $query .= "\nWHERE\n    ".implode("\n    AND "$where);
        return 
    $this->db->pexecute($query$values);
      }

      
    /**
       * Deletes one or more rows.
       * @param  $condition  array  Associative array of column => value to serve as conditions for the query.
       * @return boolean
       */
      
    function delete($condition) {
        
    $query "DELETE FROM ".$this->db->quoteName($this->tableName);
        
    $where = Array();
        
    $values = Array();
        foreach (
    $condition as $column => $value) {
          
    $where[] = $this->db->quoteName($column)." = :".$column;
          
    $values[$column] = $value;
        }
        if (
    count($where) == 0) {
          throw new 
    Exception("No conditions given for delete");
        }
        
    $query .= "\nWHERE\n    ".implode("\n    AND "$where);
        
    $result $this->db->pexecute($query$values);
        return 
    $result->rowCount() > 0;
      }
    }


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •