Introduce CommonTable, TableIterator, TablePearRepo classes (and use it in refresh)

1 files changed, 368 insertions, 0 deletions

diff --git a/class/CommonTable.php b/class/CommonTable.php
new file mode 100644
index 0000000..721a224
--- /dev/null
+++ b/class/CommonTable.php
@@ -0,0 +1,368 @@
+<?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;
+
+    function __construct(PDO $db, $table) {
+
+        $this->db = $db;
+        $this->table = $table;
+
+        if (!$this->existsTable($table)) {
+            $this->createTable($table);
+        }
+    }
+
+    public function existsTable($table) {
+        $req = new TableIterator($this->db, "SHOW TABLES LIKE '$table'");
+        foreach ($req as $data) {
+            return true;
+        }
+        return false;
+    }
+
+    protected function exec($sql) {
+        $res = $this->db->exec($sql);
+        if ($res===false) {
+            $err = $this->db->errorInfo();
+            throw new Exception($err[2]);
+        }
+    }
+
+    protected 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[] = "'$value'";
+            }
+        }
+        $sql = "INSERT INTO `".$this->table."` (".implode(',',$col).")
+                VALUE (".implode(',',$val).")";
+        $this->exec($sql);
+    }
+    abstract protected function createTable();
+
+    /**
+     * Instanciate a Simple DBIterator
+     *
+     * Examples =
+     *  foreach ($DB->request("select * from glpi_states") as $data) { ... }
+     *  foreach ($DB->request("glpi_states") as $ID => $data) { ... }
+     *  foreach ($DB->request("glpi_states", "ID=1") as $ID => $data) { ... }
+     *  foreach ($DB->request("glpi_states", "", "name") as $ID => $data) { ... }
+     *  foreach ($DB->request("glpi_computers",array("name"=>"SBEI003W","entities_id"=>1),array("serial","otherserial")) { ... }
+     *
+     * @param $tableorsql table name, array of names or SQL query
+     * @param $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);
+    }
+
+    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 {
+
+    function __construct($db) {
+        parent::__construct($db, 'pearrepo');
+    }
+
+    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));
+        }
+    }
+
+    function getAllRepo() {
+        return $this->getAllArray('alias', 'url');
+    }
+}
+
+/*
+ * Helper for simple query => see $DBmysql->requete
+ */
+class TableIterator  implements Iterator {
+
+    private $con;
+    private $sql;
+    private $res = false;
+    private $row;
+    private $pos;
+
+    /**
+     * Constructor
+     *
+     * @param $dbconnexion Database Connnexion (must be a CommonDBTM object)
+     * @param $table table name
+     * @param $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;
+    }
+
+    function __destruct () {
+
+        if ($this->res) {
+            $this->res->closeCursor();
+        }
+    }
+
+    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;
+    }
+
+    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();
+    }
+
+    public function current() {
+
+        return $this->row;
+    }
+
+    public function key() {
+
+        return (isset($this->row["id"]) ? $this->row["id"] : $this->pos);
+    }
+
+    public function next() {
+
+        if (!$this->res) {
+            return false;
+        }
+        $this->row = $this->res->fetch(PDO::FETCH_ASSOC);
+        $this->pos++;
+        return $this->row;
+    }
+
+    public function valid() {
+
+        return $this->res && $this->row;
+    }
+
+    public function numrows() {
+
+        return ($this->res ? $this->res->rowCount() : 0);
+    }
+}
+?>