Classes para facilitar manipulação com banco de dados (versão 2.0).

database.php

<?php

/** 
 * Config do banco
 */
class DATABASE_CONFIG {
	public $config = array();
	public $dev = array(
		'host' => 'localhost', 
		'login' => 'root', 
		'password' => '123', 
		'database' => 'banco', 
		'encoding' => 'utf8', 
	);
	public $web = array(
		'host' => '', 
		'login' => '', 
		'password' => '', 
		'database' => '', 
		'encoding' => '', 
	);
	
	public static function getConfig () {
		$self = new self;
		if (strstr($_SERVER['HTTP_HOST'], 'local') !== false) {
			$self->config = $self->dev;
		} else {
			$self->config = $self->web;
		}
		return $self->config;
	}
}

/** 
 * Database
 */ 
class Database 
{ 
	private static $driver = null;
	private $query;
	public static $all_tables;
	private $sql_log = array();
	public static $prefix = null;
	
	private function __construct () { 
		$config = DATABASE_CONFIG::getConfig();
		$config['driver'] = (isset($config['driver']) ? $config['driver'] : null);
		
		switch ($config['driver'])
		{
			case 'mysql' : default : 
				$dsn = sprintf('mysql:host=%s;port=%s;dbname=%s', $config['host'], '3306', $config['database']);
				break;
		}
		
		$options = array(
			PDO::ATTR_PERSISTENT => true,
			PDO::ATTR_ERRMODE => true, 
			PDO::ERRMODE_EXCEPTION => true
		);
		
		try {
			$this->driver = new PDO($dsn, $config['login'], $config['password'], $options);
			
			if (isset($config['encoding'])) {
				$this->driver->query(sprintf('SET NAMES `%s`', $config['encoding']));
			}
			
			if (isset($config['prefix'])) {
				self::$prefix = $config['prefix'];
			}
		}
		catch (PDOException $e) {
			throw new PDOException($e->getMessage());
		}
		
		// Resgata nomes das tabelas do banco
		$this->query('SHOW TABLES');
		$data = $this->fetchAll();
		
		foreach($data as $key => $value) {
			if (is_array($value)) {
				foreach($value as $table) {
					self::$all_tables[] = $table;
				}
			}
			else {
				self::$all_tables[] = $value;
			}
		}
	} 
	
	public static function open () {
		if (!self::$driver) {
			self::$driver = new DataBase;
		}
		return self::$driver;
	}
	
	public function query ($sql) {
		try {
			$this->sql_log[] = $sql;
			$this->query = $this->driver->query($sql);
		}
		catch (PDOException $e) {
			throw new PDOException($e->getMessage());
		}
	}
	
	public function lastId () {
		return $this->driver->lastInsertId();
	}
	
	
	public function fetchFirst () {
		return $this->query->fetch(PDO::FETCH_ASSOC);
	}
	
	public function fetchAll () {
		$data = $this->query->fetchAll(PDO::FETCH_ASSOC);
		return $data;
	}
	
	
}

/** 
 * Model
 */ 
class Model 
{ 
	/** 
	 * Conexao com banco de dados
	 */
	private $database;
	
	/** 
	 * Nome da tabela 
	 */
	protected $table;
	
	/** 
	 * Alias da tabelas 
	 */
	protected $alias;
	
	/** 
	 * Campos da tabela para utilizar no select 
	 */
	protected $fields = array();
	
	/** 
	 * Condicoes para select 
	 */
	protected $cond;
	
	/** 
	 * Group
	 */
	public $group;
	
	/** 
	 * Order 
	 */
	public $order;
	
	/** 
	 * Limit 
	 */
	public $limit;
	
	/** 
	 * Dados da tabela 
	 */
	protected $schema = array();
	
	/** 
	 * Campo para ser salvo no insert
	 */
	protected $created = 'created';
	
	/** 
	 * Campo para ser atualizado ao utilizar o update
	 */
	protected $modified = 'modified';
	
	/** 
	 * ID que acabou de sair do forno! 
	 */
	public $id = null;
	
	/** 
	 * Chave primaria da tabela 
	 */
	private $_primaryKey = 'id';
	
	/** 
	 * Total de registros, sem paginacao 
	 */
	protected $count;
	
	/** 
	 * Join 
	 */
	protected $join = array();
	
	/** 
	 * Bloqueia paginacao automatica via limit 
	 */
	public $block_paginate = false;
	
	/** 
	 * Log da sql 
	 */
	protected $sql_log = array();
	
	/** 
	 * Construtor 
	 */
	public function __construct () { 
		$this->database = Database::open();
		$this->setTable();
		
		$this->setSchema();
	}
	
	/** 
	 * Seta a tabela 
	 */
	private function setTable () {
		if (empty($this->table)) {
			$this->table = Database::$prefix.strtolower(get_class($this));
		}
		else {
			$prefix = Database::$prefix;
			if (!empty($prefix) && strpos($this->table, $prefix) === false) {
				$this->table = Database::$prefix.$this->table;
			}
		}
		
		if (empty($this->alias)) {
			$this->alias = get_class($this);
		}
		
	}
	
	/** 
	 * Seta nomes dos campos da tabela 
	 */
	public function setSchema () {
		$this->database->query('SHOW COLUMNS FROM '.$this->table);
		$fields = $this->database->fetchAll();
		
		if (!empty($fields)) {
			foreach($fields as $data) {
				$this->schema['fields'][$data['Field']] = array(
					'null' => ($data['Null'] == 'YES' ? true : false)
				);
				
				if ($data['Key'] == 'PRI') {
					$this->_primaryKey = $data['Field'];
				}
				
				$this->fields[] = $this->alias.'.'.$data['Field'];
			}
		}
	}
	
	/** 
	 * Seta join entre tabelas 
	 */
	public function addJoin	($array) {
		$this->join[] = $array;
		
		if (isset($array['fields'])) {
			foreach($array['fields'] as $field) {
				$alias = isset($array['alias']) ? $array['alias'] : $array['table'];
				$this->fields[] = $alias.'.'.$field;
			}
		}
		
	}
	
	/** 
	 * Resgata campos para select 
	 */
	protected function getFields () {
		$out = null;
		if (!empty($this->fields)) {
			$out = implode(', ', $this->fields);
		}
		else {
			$out = '*';
		}
		
		return $out;
	}
	
	
	/** 
	 * Gera sql 
	 */
	public function getSelect ($conditions = null, $fields = null) {
		// Select dos campos
		$sql  = sprintf('SELECT %s ', (!empty($fields) ? $fields : $this->getFields()));
		
		// Tabela
		$sql .= sprintf('FROM `%s` as %s ', $this->table, $this->alias);
		
		// Joins
		if (!empty($this->join)) {
			foreach($this->join as $join) {
				$alias = (isset($join['alias']) ? $join['alias'] : $join['table']);
				$sql .= sprintf('%s JOIN %s AS %s ', $join['join'], Database::$prefix.$join['table'], $alias);
				$sql .= sprintf('ON (%s.%s = %s.%s) ', $this->alias, $join['foreignKey'], $alias, 'id');
			}
		}
		
		// Condicoes
		$sql .= sprintf('%s ', $conditions);
		
		if (!empty($this->group)) {
			$sql .= sprintf('GROUP BY %s ', $this->group);
		}
		if (!empty($this->order)) {
			$sql .= sprintf('ORDER BY %s ', $this->order);
		}
		if (!empty($this->limit)) {
			$sql .= sprintf('LIMIT %s ', $this->limit);
		}
		
		$this->sql_log[] = $sql;
		return $sql;
	}
	
	/** 
	 * Resgata log com sql 
	 */
	public function getSqlLog() {
		return $this->sql_log;
	}
	
	/** 
	 * Busca tudo 
	 */
	public function all ($conditions = null, $fields = null) {
		$sql = $this->getSelect($conditions, $fields);
		$this->database->query($sql);
		$data = $this->database->fetchAll();
		$this->setCount($conditions);

		if (isset($data[0]['find_count'])) {
			$data = $data[0]['find_count'];
		}
		else if ($this->limit == '0,1' && isset($data[0])) {
			$data = $data[0];
		}

		$this->reset();
		
		return $data;
	}
	
	/** 
	 * Find 
	 */
	public function find ($type = 'all', $params = array()) {
		$conditions = null;
		$fields = null;
		
		switch ($type) { 
			case 'first' : 
				$this->order = $this->alias.'.'.$this->_primaryKey.' ASC';
				$this->limit = '0,1';
				break; 
			case 'last' : 
				$this->order = $this->alias.'.'.$this->_primaryKey.' DESC';
				$this->limit = '0,1';
				break; 
			case 'count' : 
				$params['fields'] = array('COUNT(*) as find_count');
				$this->limit = null;
				break; 
		} 
		
		if (!empty($params)) {
			
			// Join
			if (isset($params['joins'])) {
				foreach($params['joins'] as $join) {
					$this->addJoin($join);	
				}
			}
			
			// Fields
			if (isset($params['fields'])) {
				$fields = implode(', ', $params['fields']);
			}
			
			// Conditions
			if (isset($params['conditions'])) {
				if (is_array($params['conditions'])) {
					$temp_conditions = array();
					foreach($params['conditions'] as $key => $value) {
						if (is_string($key)) {
							$temp_conditions[] = $key . ' = "'.$value.'"';
						}
						else {
							$temp_conditions[] = $value;
						}
					}
					$conditions = implode(' AND ', $temp_conditions);
				}
				else if (is_string($params['conditions'])) {
					$conditions = $params['conditions'];
				}
				
				if (!empty($conditions)) {
					$conditions = 'WHERE '.$conditions;
				}
			}
			
			// Order
			if (isset($params['order'])) {
				if (is_array($params['order'])) {
					$temp_order = array();
					foreach($params['order'] as $value) {
						$temp_order[] = $value;
					}
					$this->order = implode(' AND ', $temp_order);
				}
				else if (is_string($params['order'])) {
					$this->order = $params['order'];
				}
			}
			
			// Limit
			if (isset($params['limit'])) {
				if (!empty($params['limit'])) {
					$paginate = $this->getPaginate($params['limit']);
					
					if (!empty($paginate)) {
						$this->limit = $paginate;
					}
					else {
						$this->limit = $params['limit'];
					}
				}
			}
			
		}
		
		return $this->all($conditions, $fields);
	}
	
	/** 
	 * Resgata dados para paginacao automatica 
	 */
	public function getPaginate ($limit) {
		$limit_paginate = null;
		if (isset($_GET['page']) && !$this->block_paginate) {
			$page = (int) $_GET['page'];
			$start = ($page - 1);
			$start = ($start < 0) ? 0 : $start; 
			$limit_paginate = $start * $limit . ', ' . $limit;
		}
		
		return $limit_paginate;
	}
	
	/** 
	 * Seta total de registros de uma condicao	 
	 */
	public function setCount ($conditions = null) {
		$fields = 'COUNT('.$this->alias.'.'.$this->_primaryKey.') as total';
		$sql = $this->getSelect($conditions, $fields);
		$this->database->query($sql);
		$data = $this->database->fetchAll();
		
		if (!empty($data[0]['total'])) {
			$this->count = $data[0]['total'];
		}
	}
	
	/** 
	 * Resgata total de registros de uma condicao	 
	 */
	public function getCount () {
		return $this->count;
	}
	
	/** 
	 * Busca primeiro registro 
	 */
	public function first ($conditions = null) {
		$this->limit = '0, 1';
		$sql = $this->getSelect($conditions);
		$this->database->query($sql);
		$data = $this->database->fetchFirst();
		$this->reset();
		return $data;
	}
	
	/** 
	 * Busca ultimo registro 
	 */
	public function last ($conditions = null) {
		$this->limit = '0, 1';
		$this->order = $this->alias.'.'.$this->_primaryKey.' DESC';
		$sql = $this->getSelect($conditions);
		$this->database->query($sql);
		$data = $this->database->fetchFirst();
		$this->reset();
		return $data;
	}
	
	/** 
	 * Metodo magico para facilitar condições comuns 
	 */
	public function __call ($function, $args) {
		// Busca pelo campo sem limite
		if (preg_match('/^(findAllBy)(.*)/', $function, $matches)) {
			$field = $matches[2];
			$field = preg_replace('/([A-Z0-9])/', '_\\0', $field);
			$field = strtolower(substr($field, 1));
			
			$cond = sprintf('WHERE `%s` = "%s"', $field, $args[0]);
			return $this->all($cond);
		}
		// Busca pelo campo com limit de 1 registro
		else if (preg_match('/^(findBy)(.*)/', $function, $matches)) {
			$this->limit = '0, 1';
			$field = $matches[2];
			$field = preg_replace('/([A-Z0-9])/', '_\\0', $field);
			$field = strtolower(substr($field, 1));
			
			$cond = sprintf('WHERE `%s` = "%s"', $field, $args[0]);
			return $this->first($cond);
		}
	}
	
	/** 
	 * Save 
	 */
	public function save (array $data = array()) {
	
		$default = array();
		
		if (isset($this->schema['fields'][$this->created])) {
			$default[$this->created] = date('Y-m-d H:i:s');
		}
		if (isset($this->schema['fields'][$this->modified])) {
			$default[$this->modified] = date('Y-m-d H:i:s');
		}
		
		// Mescla dados padrões com dados passados
		$data = array_merge($default, $data);
		$data = $this->prepareData($data);
		$fields = array_keys($data);
		$validate = $this->validateData($data);
		
		if (empty($validate)) {
			$sql  = 'INSERT INTO '.$this->table.' ';
			$sql .= '('.implode(', ', $fields).') ';
			$sql .= 'VALUES ';
			$sql .= '("'.implode('", "', array_values($data)).'") ';
			
			$this->database->query($sql);
			$this->id = $this->database->lastId();
		}
		else {
			return $validate;
		}
		
		return true;
	}
	
	/** 
	 * Update 
	 */
	public function update (array $data = array(), $conditions = null) {
		
		if ($conditions) {
			$conditions = 'WHERE '.$conditions;
		}
		else if ($this->id) {
			$conditions = 'WHERE '.$this->_primaryKey.' = "'.$this->id.'"';
		}
		else if (!$this->id) {
			return 'Não existe condição ou ID para atualizar tabela!';
		}
		
		$default = array();
		
		if (isset($this->schema['fields'][$this->modified])) {
			$default[$this->modified] = date('Y-m-d H:i:s');
		}
		
		// Mescla dados padrões com dados passados
		$data = array_merge($default, $data);
		$data = $this->prepareData($data);
		$fields = array_keys($data);
		$validate = $this->validateData($data);
		
		if (empty($validate)) {
			$sql  = 'UPDATE '.$this->table.' ';
			$sql .= 'SET ';
			
			$lines = array();
			foreach($data as $key => $value) {
				$lines[] .= $key . ' = "'.$value.'"';
			}
			
			$sql .= implode(', ', $lines);
			$sql .= ' '.$conditions;
			
			$this->database->query($sql);
		}
		else {
			return $validate;
		}
		
		return true;
	}
	
	/** 
	 * Recupera dados somente de campos que existe na tabela
	 */
	protected function prepareData ($data) {
		$new_data = array();
		foreach ($data as $key => $value) {
			if (isset($this->schema['fields'][$key])) {
				$new_data[$key] = $value;
			}
		}
		return $new_data;
	}
	
	/** 
	 * Valida dados 
	 */
	public function validateData ($data = array()) {
		$errors = array();
		foreach($data as $key => $value) {
			if (isset($this->schema['fields'][$key]) && !$this->schema['fields'][$key]['null'] && empty($value)) {
				$errors[$key] = 'O valor não pode ser nulo!';
			}
		}
		
		return $errors;
	}
	
	/** 
	 * Delete 
	 */
	public function delete ($conditions = null) {
		$sql = 'DELETE FROM '.$this->table.' ';
		
		if (is_numeric($conditions)) {
			$sql .= 'WHERE '.$this->_primaryKey.' = "'.$conditions.'"';
			$this->database->query($sql);
		}
		else if (!empty($conditions)) {
			$sql .= 'WHERE '.$conditions;
			$this->database->query($sql);
		}
		else if ($this->id) {
			$sql .= 'WHERE '.$this->_primaryKey.' = "'.$this->id.'"';
			$this->database->query($sql);
		}
		else {
			return 'Erro ao excluir registro';
		}
		
		return true;
	}
	
	
	/** 
	 * Limpa dados para nova consulta 
	 */
	public function reset () {
		$this->id 		= null;
		$this->join 	= array();
		$this->group 	= null;
		$this->order 	= null;
		$this->limit 	= null;
	}
	
}

?>