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)
    {
        $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
     *
     * @param integer number of affected rows
     */
    protected function exec($sql)
    {
        $res = $this->db->exec($sql);
        if ($res===false) {
            $err = $this->db->errorInfo();
            throw new Exception($err[2]);
        }
        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;
    }

    /**
     * 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;
    }
    /**
     * Create the table
     */
    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='')
    {
        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
     *
     * @return hashtable
     */
    public function getAllArray($fieldkey, $fieldvalue)
    {
        $crit = array('FIELDS'  => array($fieldkey, $fieldvalue),
                      'ORDER'   => $fieldkey);
        $tab = array();
        foreach ($this->request($crit) as $data) {
            $tab[$data[$fieldkey]] = $data[$fieldvalue];
        }
        return $tab;
    }
}

class TablePearRepo extends CommonTable
{

    /**
     * Instanciate a TablePearRepo to manage pearrepo table
     *
     * @param object $db PDO instance of the DB connection
     */
    function __construct($db)
    {
        parent::__construct($db, 'pearrepo');
    }

    /**
     * Create the table and populate it with known repo
     *
     * @return void
     */
    protected function createTable()
    {

        // Table schema
        $sql = "CREATE TABLE `pearrepo` (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `alias` varchar(30) NOT NULL,
                  `url` varchar(255) NOT NULL,
                  PRIMARY KEY (`id`),
                  UNIQUE KEY `alias` (`alias`)
                ) DEFAULT CHARSET=utf8";

        $this->exec($sql);

        // Some known repo, other could be add manually
        // no reply from "phpdb"    => "pear.phpdb.org"
        $channels = array(
            "pear"      => "pear.php.net"
            ,"doctrine" => "pear.phpdoctrine.org"
            ,"ezc"      => "components.ez.no"
            ,"pdepend"  => "pear.pdepend.org"
            ,"phing"    => "pear.phing.info"
            ,"phpmd"    => "pear.phpmd.org"
            ,"phpunit"  => "pear.phpunit.de"
            ,"swift"    => "pear.swiftmailer.org"
            ,"symphony" => "pear.symfony-project.com"
        );

        foreach ($channels as $alias => $url) {
            $this->add(array('alias'=>$alias, 'url'=>$url));
        }
    }

    /**
     * Retrieve all the known repository
     *
     * @return hastable of alias => url
     */
    function getAllRepo()
    {
        return $this->getAllArray('alias', 'url');
    }
}

/**
 * Helper for simple query => use directly or through  CommonTable::request()
 *
 * Freely inspired from DBmysqlIterator class from GLPI
 * (already written by Remi, and ported to PDO)
 * See http://www.glpi-project.org/
 */
class TableIterator  implements Iterator
{
    private $con;
    private $sql;
    private $res = false;
    private $row;
    private $pos;

    /**
     * Constructor
     *
     * @param CommonDBTM   $dbconnexion Database Connnexion
     *                                  (must be a CommonDBTM object)
     * @param string       $table       table name
     * @param string|array $crit        string or array of filed/values,
     *                                  ex array("id"=>1), if empty => all rows
     */
    function __construct (PDO $dbconnexion, $table, $crit='')
    {

        $this->conn = $dbconnexion;
        if (is_string($table) && strpos($table, " ")) {
            $this->sql = $table;
        } else {
            // check field, orderby, limit, start in criterias
            $field="";
            $orderby="";
            $limit=0;
            $start=0;

            if (is_array($crit) && count($crit)) {
                foreach ($crit as $key => $val) {
                    if ($key==="FIELDS") {
                        $field = $val;
                        unset($crit[$key]);
                    } else if ($key==="ORDER") {
                        $orderby = $val;
                        unset($crit[$key]);
                    } else if ($key==="LIMIT") {
                        $limit = $val;
                        unset($crit[$key]);
                    } else if ($key==="START") {
                            $start = $val;
                            unset($crit[$key]);
                    }
                }
            }

            // SELECT field list
            if (is_array($field)) {
                $this->sql = "";
                foreach ($field as $t => $f) {
                    if (is_numeric($t)) {
                        $this->sql .= (empty($this->sql)
                            ? "SELECT " : ",") . $f;
                    } else if (is_array($f)) {
                        $this->sql .= (empty($this->sql)
                            ? "SELECT $t." : ",$t.") . implode(",$t.", $f);
                    } else {
                        $this->sql .= (empty($this->sql)
                            ? "SELECT " : ",") . "$t.$f";
                    }
                }
            } else if (empty($field)) {
                $this->sql = "SELECT *";
            } else {
                $this->sql = "SELECT `$field`";
            }
            // FROM table list
            if (is_array($table)) {
                $this->sql .= " FROM `".implode("`, `", $table)."`";
            } else {
                $this->sql .= " FROM `$table`";
            }
            // WHERE criteria list
            if (!empty($crit)) {
                print_r($crit);
                $this->sql .= " WHERE ".$this->_analyseCrit($crit);
            }
            // ORDER BY
            if (is_array($orderby)) {
                $this->sql .= " ORDER BY `".implode("`, `", $orderby)."`";
            } else if (!empty($orderby)) {
                $this->sql .= " ORDER BY `$orderby`";
            }
            if (is_numeric($limit) && $limit>0) {
                $this->sql .= " LIMIT $limit";
                if (is_numeric($start) && $start>0) {
                    $this->sql .= " OFFSET $start";
                }
            }
        }
        //echo "SQL: ".$this->sql."\n";
        $this->res = $this->conn->prepare($this->sql);
        if ($this->res===false) {
            $err = $this->db->errorInfo();
            throw new Exception($err[2]);
        }

        $this->pos = -1;
    }

    /**
     * Class destructor
     */
    function __destruct ()
    {
        if ($this->res) {
            $this->res->closeCursor();
        }
    }

    /**
     * Build WHERE clause
     *
     * @param TODO $crit To document
     * @param TODO $bool To document
     *
     * @return To document
     */
    private function _analyseCrit ($crit, $bool="AND")
    {

        if (!is_array($crit)) {
            return $crit;
        }
        $ret = "";
        foreach ($crit as $name => $value) {
            if (!empty($ret)) {
                $ret .= " $bool ";
            }
            if (is_numeric($name)) {
                // No Key case => recurse.
                $ret .= "(" . $this->_analyseCrit($value, $bool) . ")";
            } else if ($name==="OR" || $name==="AND") {
                // Binary logical operator
                $ret .= "(" . $this->_analyseCrit($value, $name) . ")";
            } else if ($name==="NOT") {
                // Uninary logicial operator
                $ret .= " NOT (" . $this->_analyseCrit($value, "AND") . ")";
            } else if ($name==="FKEY") {
                // Foreign Key condition
                if (is_array($value) && count($value)==2) {
                    reset($value);
                    list($t1,$f1)=each($value);
                    list($t2,$f2)=each($value);
                    $ret .= (is_numeric($t1) ? "$f1" : "$t1.$f1") . "=" .
                            (is_numeric($t2) ? "$f2" : "$t2.$f2");
                } else {
                    trigger_error("BAD FOREIGN KEY", E_USER_ERROR);
                }
            } else if (is_array($value)) {
                // Array of Value
                $ret .= "$name IN ('". implode("','", $value)."')";
            } else if (is_null($value)) {
                // NULL condition
                $ret .= "$name IS NULL";
            } else if (is_numeric($value)) {
                // Integer
                $ret .= "$name=$value";
            } else {
                // String
                $ret .= "$name='$value'";
            }
        }
        return $ret;
    }

    /**
    * To document
     *
     * @return To document
    */
    public function rewind ()
    {

        if ($this->res && $this->pos>=0) {
            $this->res->closeCursor();
            $this->pos = -1;
        }

        if ($this->res && $this->pos<0) {
            if (!$this->res->execute()) {
                $err = $this->res->errorInfo();
                throw new Exception($err[2]);
            }
        }
        return $this->next();
    }

    /**
     * To document
     *
     * @return To document
     */
    public function current()
    {
        return $this->row;
    }

    /**
     * To document
     *
     * @return To document
     */
    public function key()
    {
        return (isset($this->row["id"]) ? $this->row["id"] : $this->pos);
    }

    /**
     * To document
     *
     * @return To document
     */
    public function next()
    {
        if (!$this->res) {
            return false;
        }
        $this->row = $this->res->fetch(PDO::FETCH_ASSOC);
        $this->pos++;
        return $this->row;
    }

    /**
     * To document
     *
     * @return To document
     */
    public function valid()
    {
        return $this->res && $this->row;
    }

    /**
     * To document
     *
     * @return To document
     */
    public function numrows()
    {
        return ($this->res ? $this->res->rowCount() : 0);
    }
}
?>