<?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);
}
}
?>