<?php
// Fichier SPDO.php

class SPDO
{
  /* Instance de la classe SPDO
  @var SPDO
  @access private */  
  private $PDOInstance = null;
  
  /* Instance de la classe SPDO
  @var SPDO
  @access private
  @static */
  private static $instance = null;
  
  /* Constante: nom d'utilisateur de la bdd
  @var string */  
  const DEFAULT_SQL_USER = '*******'; // => nom d'utilisateur exemple 'root'
  
  /* Constante: hôte de la bdd
  @var string */  
  const DEFAULT_SQL_HOST = '*******';  // => Host de la Bd exemple = 'localhost'
  
  /* Constante: hôte de la bdd
  @var string */  
  const DEFAULT_SQL_PASS = '*******'; // => mot de passe
  /* Constante: nom de la bdd
  @var string */  
  const DEFAULT_SQL_DTB = '*******'; // => nom de la table
  
  /* Constructeur de la classe SPDO
  @param void
  @return void
  @acess private
  @see PDO::__construct() */  
  private function __construct()
  {      
    try
    {
      // Nouvelle instance PDO : connexion à la table
      $this->PDOInstance = new PDO('mysql:dbname='.self::DEFAULT_SQL_DTB.';host='.self::DEFAULT_SQL_HOST,self::DEFAULT_SQL_USER ,self::DEFAULT_SQL_PASS);
      //
      $this->PDOInstance->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
    }
    catch (PDOException $e)
    {
      echo "<b>Erreur PDO:</b> ".$e->getMessage()."<br />\n";
    }  
  }
  
  /* Empeche la copie externe de l'instance
  @access private */
  private function __clone()
  {
    throw new Exception('Le clonage de SPDO n\'est pas autoris&eacute;');
  }
  
  /* Crée et retourne l'objet SPDO */
  public static function getInstance()
  {
    if(is_null(self::$instance))
    {      
      self::$instance = new SPDO();
    }
    return self::$instance;
  }
  
  /* Initiates a transaction
  * @return <bool>  */
  public function beginTransaction()
  {
    return $this->PDOInstance->beginTransaction();
  }
  
  /* Commits a transaction
  @return <bool> */
  public function commit()
  {
    return $this->PDOInstance->commit();
  }
  
  /* Fetch the SQLSTATE associated with the
  last operation on the database handle
  * @return <string> */
  public function errorCode()
  {
    return $this->PDOInstance->errorCode();
  }
  
  /* Fetch extended error information associated with
  the last operation on the database handle
  @return <array> */
  public function erroInfo()
  {
    return $this->PDOInstance->errorInfo();
  }
  
  /* Executes an SQL statement, return the number of affected row
  @param <String> $statement
  @return <int> */
  public function exec($statement)
  {
    return $this->PDOInstance->exec($statement);
  }
    
  /* Retrieve a database connection attribute
  @param <int> $attribute
  @return <mixed> */
  public function getAttribute($attribute)
  {
    return $this->PDOInstance->getAttribute($attribute);
  }
  
  /* Return an array of available PDO drivers
  @return <array> */
  public function getAvailabelDrivers()
  {
    return $this->PDOInstance->getAvailableDrivers();
  }
  
  /* Returns the ID of the last inserted row or sequence value
  @param <type> $name
  @return <type> */
  public function lastInsertId($name)
  {
    return $this->PDOInstance->lastInsertId($name);
  }
  
  /* Prepare an SQL statement, returning a result set as PDOStatement object
  @param <String> $statement
  @return <type> */
  public function prepare($statement,$driver_options=false)
  {
    if (!$driver_options)
    {
      $driver_options=array();
    }
    return $this->PDOInstance->prepare($statement,$driver_options);;
  }
  
  /* Executes an SQL statement, returning a result set as PDOStatement object
  @param <string> $statement
  @return PDOStatement */
  public function query($statement)
  {
    try
    {
      return $this->PDOInstance->query($statement);
    }
    catch (PDOException $e)
    {
      echo $e->getMessage();
    }
  }
  
  /* Quotes a string for use in a query
  @param <type> $input
  @param <type> $parametre_type
  @return <type> */
  public function quote($input, $parametre_type=0)
  {
    return $this->PDOInstance->quote($input,$parametre_type);
  }
  
  /* Rolls back a transaction
  @return <bool> */
  public function rollBack()
  {
    return $this->PDOInstance->rollBack();
  }
  
  /*Set an attribute
  @param <int> $attribute
  @param <mixed> $value
  @return <bool> */
  public function setAttribute($attribute, $value)
  {
    return $this->PDOInstance->setAttribute($attribute,$value);
  }
  
  /*Execute query and return one row in assoc array
  @param <string> $statement
  @return <type> */
  public function queryFetchAllAssoc($statement)
  {
    return $this->PDOInstance->query($statement)->fetchAll(PDO::FETCH_ASSOC);
  }
  
  /* @param <type> $statement
  @return <type> */
  public function queryFetchAllObj($statement)
  {
    return $this->PDOInstance->query($statement)->fetchAll(PDO::FETCH_OBJ);
  }
  
  public function queryFetchAllBoth($statement)
  {
    return $this->query($statement)->fetchAll(PDO::FETCH_BOTH);
  }
  
  /*Close a connection database
  @access public
  @return <void> */
  public function close()
  {
    $this->PDOInstance = null;
  }
  
}
?>

<?php
// Table.php

abstract class Table
{
  /* @var string */
  /* Nom de la table */
  protected $_name;
  /* Nom de la séquence (laisser vide pour auto-gen) */
  protected $_sequence;
  /* Nom de la colonne de la clé primaire */
  protected $_pk;
  /* Pour tout sélectionner  */
  protected $_selection = '*';
  
  /* @var PDO->SPDO */
  /* Instance de connexion SPDO */
  protected $connection;
  
    
  /* Constructeur de Table */
  function __construct()
  {
    $this->_ensureConnection();
  }
  
  /* fonction qui retourne une instance de connexion SPDO */
  private function _ensureConnection()
  {
    $this->connection = SPDO::getInstance();
  }
  
  /**
   * Setup what to select from DB table
   *
   * @param string | array $selection
   */
  function setSelection($selection)
  {
    $oldValue = $this->_selection;
    $this->_selection = implode(', ', (array) $selection);
    return $oldValue;
  }
  
  /**
   * Selectionne une ligne par ID
   *
   * @param int $id
   * @return mixed array row or bool FALSE if none found
   */
  function select($id)
  {
    return $this->_statement('SELECT ' . $this->_selection .
        ' FROM ' . $this->_name ,
        array (' WHERE ' . $this->_name . '.' . $this->_pk . ' = ?', (int) $id))->fetch();
  }
  
  /**
   * Selectionne toutes les lignes par rapport aux conditions dans $where
   *
   * @param array $where
   * @param array | string $order
   * @param int $pageSize
   * @param int $pageNumber
   * @return PDOStatement
   */
  function selectAll($where = null, $order = null, $pageSize = null, $pageNumber = 1)
  {
    $sql = 'SELECT ' . $this->_selection . ' FROM ' . $this->_name;
    $binds = $this->_appendWhere($sql, $where);
    
    $this->_appendOrder($sql, $order);
    $this->_appendLimit($sql, $pageSize, $pageNumber);
    return $this->_statement($sql, $binds)->fetchAll(PDO::FETCH_OBJ);
  }
  
  /**
   * @param array $idList
   * @param array | string $where
   * @param array | string $order
   * @return PDOStatement | ArrayObject
   */
  function selectInIdList(array $idList, $where = null, $order = null)
  {
    if (! ($count = count($idList)))
      return new ArrayObject(array());
    
    $sql = $this->_name . '.' . $this->_pk . ' IN (?' . str_repeat(', ?', $count - 1) . ')';
    $where = array_merge((array) $where, array($sql => $idList));
    return $this->selectAll($where, $order);
  }
  
  /**
   * Fetch id => colonnes paires en array
   *
   * @param string $column
   * @param array | string $where
   * @param array | string $order
   * @return array
   */
  function fetchPairs($column, $where = null, $order = null)
  {
    $selection = $this->_selection;
    $this->setSelection($this->_name . '.' . $this->_pk . ', ' . $this->_name . '.' . $column);
    $output = array();
    
    try {
      foreach ($this->selectAll($where, $order) as $row)
        $output[$row[$this->_pk]] = $row[$column];
    } catch (Exception $e) {
      $this->setSelection($selection);
      throw $e;
    }
    
    $this->setSelection($selection);
    return $output;
  }
  
  /**
   * Compte toutes les lignes par rapport aux conditions dans $where
   *
   * @param array | string $where
   * @return int
   */
  function count($where = null)
  {
    $sql = 'SELECT COUNT(*) FROM ' . $this->_name;
    $binds = $this->_appendWhere($sql, $where);
    return $this->_statement($sql, $binds)->fetchColumn();
  }
  
  /**
   * Calculate sum of values in column $column satisfying conditions in $where
   *
   * @param string | array $expressions Either a string (SQL expression to be wrapped in SUM() ) or
   *               an array (SQL expression => alias,..)
   * @param array | string $where
   * @return int
   */
  function sum($expressions, $where = null)
  {
    if (is_string($expressions))
    {
      $sql = 'SELECT SUM(' . $expressions . ') FROM ' . $this->_name;
      $binds = $this->_appendWhere($sql, $where);
      return $this->_statement($sql, $binds)->fetchColumn();
    } else {
      $sum = array();
      foreach ($expressions as $expr => $alias)
      {
        $sum[] = "SUM($expr) AS $alias";
      }
      $sql = 'SELECT ' . implode(", ", $sum) . ' FROM ' . $this->_name;
      $binds = $this->_appendWhere($sql, $where);
      return $this->_statement($sql, $binds)->fetch();
    }
  }
  
  /**
   * Get SQL clauses: SELECT ... FROM ... WHERE ...
   *
   * @param array | string $where
   * @return array ( SQL, BOUNDS )
   */
  function getSelectSql($where)
  {
    $sql = "SELECT " . $this->_selection . " FROM " . $this->_name;
    $bounds = $this->_appendWhere($sql, $where);
    return array($sql, $bounds);
  }
  
  /**
   * Insert une nouvelle ligne
   *
   * @param array $row
   * @return int new row ID
   */
  function insert(array $row)
  {
    $this->_removeId($row);
    $sql = 'INSERT INTO ' . $this->_name . ' (' . implode(', ', array_keys($row)) .
        ') VALUES (?' . str_repeat(', ?', count($row) - 1) . ')';
    $this->_statement($sql, array_values($row));
    return (int) $this->connection->lastInsertId($this->_sequence);
  }
  
  /**
   * Update une ligne spécifié par son ID $id
   *
   * @param array $row
   * @return int new row ID
   */
  function update($row, $id)
  {
    $this->_removeId($row);
    $updates = array();
    foreach (array_keys($row) as $field)
      $updates[] = $field . ' = ?';
    
    $sql = 'UPDATE ' . $this->_name . ' SET ' . implode(', ', $updates) .
        ' WHERE ' . $this->_pk . ' = ?';
    $binds = array_values($row);
    $binds[] = $id;
    $this->_statement($sql, $binds);
    return (int) $id;
  }
  
  /**
   * Update une ligne spécifié par rapport aux conditions dans $where
   *
   * @param array $values
   * @return int new row ID
   */
  function updateAll($values, $where)
  {
    $this->_removeId($values);
    $updates = array();
    foreach (array_keys($values) as $field)
      $updates[] = $field . ' = ?';
    
    $sql = 'UPDATE ' . $this->_name . ' SET ' . implode(', ', $updates);
    $binds = $this->_appendWhere($sql, $where);
    return (int) $this->_statement($sql, array_merge(array_values($values), $binds))->rowCount();
  }
  
  /**
   * Supprime une ligne spécifier par son ID $id
   *
   * @param int | int[] $id
   * @return int Number of rows deleted
   */
  function delete($id)
  {
    $sql = 'DELETE FROM ' . $this->_name . ' WHERE ' . $this->_pk . ' = ?';
    return $this->_statement($sql, array($id))->rowCount();
  }
  
  /**
   * Supprime une ligne spécifié par rapport aux conditions dans $where
   *
   * @param int | int[] $id
   * @return int Number of rows deleted
   */
  function deleteAll($where = null)
  {
    $sql = 'DELETE FROM ' . $this->_name;
    $binds = $this->_appendWhere($sql, $where);
    return $this->_statement($sql, $binds)->rowCount();
  }
  
  /**
   * @param string $sql
   * @param array $binds
   * @return PDOStatement
   */
  function _statement($sql, array $binds)
  {
    $stmt = $this->connection->prepare($sql);
    $this->_prepareBooleans($binds);
    $stmt->execute($binds);
    $stmt->setFetchMode(PDO::FETCH_OBJ);
    return $stmt;
  }
  
  function _appendWhere(&$sql, $where)
  {
    if (empty($where))
    {
      return array();
    }
    elseif (is_string($where))
    {
      $sql .= ' WHERE (' . $where . ')';
      return array();
    }
    else
    {
      $binds = $and = array();
      foreach ($where as $condition => $bindsPart)
      {
        if (null === $bindsPart)
          $bindsPart = array(null);
        
        $binds = array_merge($binds, (array) $bindsPart);
        $and[] = $condition;
      }
      
      $sql .= ' WHERE (' . implode(') AND (', $and) . ')';
      return $binds;
    }
  }
  
  function _appendOrder(&$sql, $order)
  {
    if (empty($order))
      return ;
    
    $normalized = array();
    foreach ((array) $order as $key => $val)
    {
      if (is_numeric($key))
      {
        $normalized[] = $val;
      } else {
        $normalized[] = $key . ' ' . $val;
      }
    }
    $sql .= ' ORDER BY ' . implode(', ', $normalized);
  }
  
  function _appendLimit(&$sql, $pageSize, $pageNumber)
  {
    if (! $pageSize)
      return ;
    
    $sql .= ' LIMIT ' . $pageSize . ' OFFSET ' . (($pageNumber - 1) * $pageSize);
  }
  
  protected function _removeId(&$row)
  {
    if (array_key_exists($this->_pk, $row))
      unset($row[$this->_pk]);
  }
  
  protected function _prepareBooleans(&$row)
  {
    foreach ($row as $key => $value)
      if (is_bool($value))
        $row[$key] = $value ? 'true' : 'false';
  }
}
?>

<?php
// Fichier Page.php
require_once('Table.php');

Class Page extends Table
{
  protected $_name = 'table_page';
  
  protected $_id = 'page_id';
}
?>