path: root/class/CommonTable.php

<?php
/**
 * Class for Database Table management
 *
 * PHP version 5
 *
 * Copyright © 2010 Remi Collet
 *
 * This file is part of rpmphp.
 *
 * rpmphp is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * rpmphp is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with rpmphp.  If not, see <http://www.gnu.org/licenses/>.
 *
 * @category  Main
 * @package   RPMPHP
 *
 * @author    Remi Collet <unknown@unknwown.com>
 * @author    Johan Cwiklinski <johan@x-tnd.be>
 * @copyright 2010 Remi Collet
 * @license   http://www.gnu.org/licenses/agpl-3.0-standalone.html AGPL License 3.0 or (at your option) any later version
 * @link      http://github.com/remicollet/rpmphp/
 * @since     The begining of times.
 */

abstract class CommonTable
{
    protected $db;
    protected $table;

    /**
     * Instanciate a CommonTable
     *
     * @param object $db    PDO instance of the DB connection
     * @param string $table with table name
     */
    function __construct(PDO $db, $table='')
    {
        if (empty($table)) {
            $table = substr(strtolower(get_class($this)),5);
        }
        $this->db = $db;
        $this->table = $table;

        if (!$this->existsTable($table)) {
            $this->createTable($table);
        }
    }

    /**
     * Check if the table already exists
     *
     * @param string $table with table name
     *
     * @return boolean
     */
    public function existsTable($table)
    {
        $req = new TableIterator($this->db, "SHOW TABLES LIKE '$table'");
        foreach ($req as $data) {
            return true;
        }
        return false;
    }

    /**
     * Execute an SQL statement (INSERT, DELETE, ...)
     *
     * @param string $sql The SQL clause
    *
     * @return integer number of affected rows
     */
    protected function exec($sql)
    {
        $res = $this->db->exec($sql);
        if ($res===false) {
            $err = $this->db->errorInfo();
            throw new Exception(
                "\nSQL: $sql\nERROR: " . $err[2] . "\nCODE:" . $err[0]
            );
        }
        return $res;
    }

    /**
     * Add a new row in the table
     *
     * @param hashtable $fields hashtable of fieldname => value
     *
     * @return integer primary key of inserted row
     */
    public function add(array $fields)
    {
        $col = array();
        $val = array();
        foreach ($fields as $name => $value) {
            $col[] = "`$name`";
            if (is_null($value)) {
                $val[] = 'NULL';
            } else if (is_numeric($value)) {
                $val[] = $value;
            } else {
                $val[] = "'".addslashes($value)."'";
            }
        }
        $sql = "INSERT INTO `".$this->table."` (".implode(',', $col).")
                VALUE (".implode(',', $val).")";
        $this->exec($sql);

        $id = $this->db->lastInsertId();

        return $id;
    }

    /**
     * Find a row from the table (the first matching criteria)
     *
     * @param hastable $crit array of field name => value
     *
     * @return hashtable (the first row)
     */
    function find(array $crit)
    {
        if (count($crit)) {
            foreach ($this->request($crit) as $row) {
                return $row;
            }
        }
        return false;
    }

    /**
     * Read a row from the table
     *
     * @param integer $id of the row
     *
     * @return hashtable
     */
    function get($id)
    {
        if (intval($id)>0) {
            return $this->find(array('id'=>$id));
        }
        return false;
    }

    /**
     * Delete a row in the table
     *
     * @param hashtable $crit of key => value
     *
     * @return integer : number of row deleted
     */
    public function delete(array $crit)
    {
        $sql = "DELETE FROM `".$this->table."` ";

        $link="WHERE";
        foreach ($crit as $key => $value) {
            $sql .= " $link `$key`";

            if (is_null($value)) {
                $sql .= 'IS NULL';
            } else if (is_numeric($value)) {
                $sql .= '='.$value;
            } else {
                $sql .= "='".addslashes($value)."'";
            }

            $link = "AND";
        }
        $nb = $this->exec($sql);

        return $nb;
    }

    /**
     * Update a row in the table
     *
     * @param integer   $id     of the record
     * @param hashtable $fields of key => value
     *
     * @return integer : number of row deleted
     */
    public function update($id, array $fields)
    {
        $sql = "UPDATE `".$this->table."` ";

        $link = 'SET';
        foreach ($fields as $key => $value) {
            if ($key=='id') {
                // Don't update id
                continue;
            }

            $sql .= "$link `$key`=";
            if (is_null($value)) {
                $sql .= 'NULL';
            } else if (is_numeric($value)) {
                $sql .= $value;
            } else {
                $sql .= "'".addslashes($value)."'";
            }
            $link = ',';
        }
        $sql .= " WHERE `id`=".intval($id);

        $nb = $this->exec($sql);

        return $nb;
    }

    /**
     * Create the table
     *
     * @return void
     */
    abstract protected function createTable();

    /**
     * Instanciate a Simple TableIterator on the current table
     *
     * Examples =
     *  foreach ($DB->request() as $ID => $data) { ... }
     *  foreach ($DB->request("ID=1") as $ID => $data) { ... }
     *  foreach ($DB->request("", "name") as $ID => $data) { ... }
     *  foreach ($DB->request(array(
     *              "name"=>"SBEI003W",
     *              "entities_id"=>1),
     *              array("serial","otherserial")) { ... }
     *
     * @param string|array $crit string or array of field/values,
     *                           ex array("id"=>1), if empty => all rows
     *
     * Examples =
     *  array("id"=>NULL)
     *  array("OR"=>array("id"=>1, "NOT"=>array("state"=>3)));
     *  array(
     *      "AND"=>array(
     *          "id"=>1,array(
     *              "NOT"=>array(
     *                  "state"=>array(3,4,5),
     *                  "toto"=>2
     *              )
     *          )
     *      )
     *  )
     *
     * param 'FIELDS' name or array of field names
     * param 'ORDER' filed name or array of field names
     * param 'LIMIT' max of row to retrieve
     * param 'START' first row to retrieve
     *
     * @return DBIterator
     **/
    public function request ($crit='')
    {
        if (is_string($crit) && strpos($crit,' ')) {
            // $crit is a full SQL command
            return new TableIterator ($this->db, $crit);
        }
        return new TableIterator ($this->db, $this->table, $crit);
    }

    /**
     * Retrieve 2 columns of all the table's row in a hashtable
     *
     * @param string $fieldkey   name of the field to use as index
     * @param string $fieldvalue name of the field to use as value
     * @param array  $crit       for request
     *
     * @return hashtable
     */
    public function getHashtable($fieldkey, $fieldvalue, array $crit=array())
    {
        $crit['FIELDS'] = array($fieldkey, $fieldvalue);
        $crit['ORDER']  = $fieldkey;

        $tab = array();
        foreach ($this->request($crit) as $data) {
            $tab[$data[$fieldkey]] = $data[$fieldvalue];
        }
        return $tab;
    }

    /**
     * Retrieve a big array with all date from the table
     *
     * @param array|string $crit for the request
     * @param string       $key  name of the key for the return array
     *
     * @return array, index is rowid, value is a hastable
     */
    public function getArray($crit='', $key='id')
    {
        $tab = array();
        foreach ($this->request($crit) as $id => $data) {
            $tab[$data[$key]] = $data;
        }
        return $tab;
    }

    /**
     * Truncate the table
     */
    public function truncate()
    {
        return $this->exec('TRUNCATE `'.$this->table.'`');
    }

    /**
     * Optimize the table
     */
    public function optimize()
    {
        $res = array();
        foreach ($this->request('OPTIMIZE TABLE `'.$this->table.'`') as $row) {
            $res[] = $row;
        }
        return $res;
    }


    /**
     * Get the number of rows in the table
     */
    public function getCount()
    {
        $sql = 'SELECT COUNT(*) AS cpt FROM `'.$this->table.'`';
        foreach ($this->request($sql) as $row) {
            return ($row['cpt']);
        }
        return 0;
    }
}

?>