mysql.php | searchcode

/sparkplug/plugs/sparkdb/adapters/mysql.php

https://code.google.com/p/sparkplug-framework/
PHP | 692 lines | 559 code | 103 blank | 30 comment | 31 complexity | f0f91cb023f8e52d03f3703fc993e59a MD5 | raw file
Possible License(s): GPL-3.0, LGPL-2.1

<?php

/*
Copyright 2009-2012 Sam Weiss
All Rights Reserved.

This file is part of Spark/Plug.

Spark/Plug is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program 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 General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

if (!defined('spark/plug'))
{
	header('HTTP/1.1 403 Forbidden');
	exit('<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN"><html><head><title>403 Forbidden</title></head><body><h1>Forbidden</h1><p>You don\'t have permission to access the requested resource on this server.</p></body></html>');
}

// -----------------------------------------------------------------------------

class MySQLFunction_metadata extends SparkDBFunction_metadata implements iSparkDBQueryFunctionMetadata
{
	protected function tables_sql()
	{
		return 'SHOW TABLES';
	}
	
	protected function columns_sql($table)
	{
		return "SELECT column_name FROM information_schema.columns WHERE table_name = '{$table}'";
	}
}

// -----------------------------------------------------------------------------

class MySQLFunction_create_table extends SparkDBQueryFunction implements iSparkDBQueryFunctionCreateTable
{
	protected static $_triggers = array
	(
		self::kForeignKeyTriggerDelete => 'DELETE',
		self::kForeignKeyTriggerUpdate => 'UPDATE',
	);

	protected static $_actions = array
	(
		self::kForeignKeyActionNone => 'NO ACTION',
		self::kForeignKeyActionCascade => 'CASCADE',
		self::kForeignKeyActionRestrict => 'RESTRICT',
		self::kForeignKeyActionSetNULL => 'SET NULL',
	);

	protected $_db;
	protected $_table;
	protected $_fields;
	protected $_primaryKey;
	protected $_foreignKeys;
	protected $_options;
	
	public function __construct($db)
	{
		$this->_db = $db;
		$this->clear();
	}

	public function compile()
	{
		$fields = implode(', ', $this->_fields);
		if (!empty($this->_primaryKey))
		{
			$fields .= ', ' . $this->_primaryKey;
		}
		if (!empty($this->_foreignKeys))
		{
			$fields .= ', ' . implode(', ', $this->_foreignKeys);
		}
		$engine = !empty($this->_options['engine']) ? $this->_options['engine'] : 'InnoDB';
		$sql = "CREATE TABLE {{$this->_table}} ({$fields}) ENGINE = {$engine}";
		if ($charset = $this->_db->charset())
		{
			$sql .= ", DEFAULT CHARACTER SET {$charset}";
		}
		return $sql;
	}

	public function clear()
	{
		$this->_table = '';
		$this->_fields = array();
		$this->_primaryKey = NULL;
		$this->_foreignKeys = array();
		$this->_options = NULL;
		return $this;
	}

	public function table($name, $options = NULL)
	{
		$this->clear();
		$this->_table = $name;
		$this->_options = $options;
		return $this;
	}

	public function field($name, $type, $length = NULL, $default = NULL, $canBeNULL = false, $flags = NULL)
	{
		switch ($type)
		{
			case self::kFieldTypeInteger:
				$typeName = 'INT';
				$default = is_integer($default) ? "{$default}" : NULL;
				break;
		
			case self::kFieldTypeFloat:
				$typeName = 'FLOAT';
				$default = is_float($default) ? "{$default}" : NULL;
				break;
		
			case self::kFieldTypeString:
				$typeName = ($flags & self::kFlagFixedLength) ? 'CHAR' : 'VARCHAR';
				if ($length === NULL)
				{
					$length = 255;
				}
				$default = is_string($default) ? "'".$default."'" : ($canBeNULL ? NULL : "''");
				break;
		
			case self::kFieldTypeText:
				$typeName = 'MEDIUMTEXT';
				$default = is_string($default) ? "'".$default."'" : ($canBeNULL ? NULL : "''");
				break;
		
			case self::kFieldTypeDate:
				$typeName = 'DATETIME';
				$default = is_string($default) ? "'".$default."'" : ($canBeNULL ? NULL : "'0000-00-00 00:00:00'");
				break;

			case self::kFieldTypeBoolean:
				$typeName = 'BOOL';
				$default = is_bool($default) ? ($default ? '1' : '0') : NULL;
				break;

			case self::kFieldTypeBinary:
				$typeName = 'BLOB';
				$default = NULL;
				break;

			case self::kFieldTypeByte:
				$typeName = 'TINYINT';
				$default = is_integer($default) ? "{$default}" : NULL;
				break;

			case self::kFieldTypeShort:
				$typeName = 'SMALLINT';
				$default = is_integer($default) ? "{$default}" : NULL;
				break;

			case self::kFieldTypeLong:
				$typeName = 'INT';
				$default = is_integer($default) ? "{$default}" : NULL;
				break;
		}

		$field = $this->_db->quoteIdentifier($name) . ' ' . $typeName;
		if ($length !== NULL)
		{
			$field .= "({$length})";
		}
		if ($flags & self::kFlagPrimaryKey)
		{
			$field .= ' PRIMARY KEY';
		}
		if ($flags & self::kFlagAutoIncrement)
		{
			$field .= ' AUTO_INCREMENT';
		}
		if ($default !== NULL)
		{
			$field .= " DEFAULT {$default}";
		}
		if (!$canBeNULL && !($flags & self::kFlagPrimaryKey))
		{
			$field .= ' NOT NULL';
		}
		
		$this->_fields[] = $field;

		return $this;
	}

	public function primaryKey($columns)
	{
		if (!is_array($columns))
		{
			$columns = explode(',', $columns);
		}
		$columns = implode(',', array_map(array($this->_db, 'quoteIdentifier'), array_map('trim', $columns)));

		$this->_primaryKey = "PRIMARY KEY($columns)";
	}

	public function foreignKey($columns, $foreignTable, $foreignColumns, $actions = NULL)
	{
		if (!is_array($columns))
		{
			$columns = explode(',', $columns);
		}
		$columns = implode(',', array_map(array($this->_db, 'quoteIdentifier'), array_map('trim', $columns)));

		if (!is_array($foreignColumns))
		{
			$foreignColumns = explode(',', $foreignColumns);
		}
		$foreignColumns = implode(',', array_map(array($this->_db, 'quoteIdentifier'), array_map('trim', $foreignColumns)));
		
		$key = "FOREIGN KEY($columns) REFERENCES {{$foreignTable}}($foreignColumns)";
		
		if (!empty($actions))
		{
			foreach ($actions as $trigger => $action)
			{
				$key .= (' ON ' . self::$_triggers[$trigger] . ' ' . self::$_actions[$action]);
			}
		}
		
		$this->_foreignKeys[] = $key;
	}
}

// -----------------------------------------------------------------------------

class MySQLFunction_alter_table extends MySQLFunction_create_table
{
	public function __construct($db)
	{
		parent::__construct($db);
	}

	public function compile()
	{
		foreach ($this->_fields as $field)
		{
			$fields[] = "ADD COLUMN {$field}";
		}
		if (!empty($this->_primaryKey))
		{
			$fields[] = "ADD {$this->_primaryKey}";
		}
		foreach ($this->_foreignKeys as $field)
		{
			$fields[] = "ADD {$field}";
		}
		$fields = implode(', ', $fields);
		return "ALTER TABLE {{$this->_table}} {$fields}";
	}
}

// -----------------------------------------------------------------------------

class MySQLFunction_create_index extends SparkDBQueryFunction implements iSparkDBQueryFunctionCreateIndex
{
	protected $_db;
	protected $_table;
	protected $_index;
	
	public function __construct($db)
	{
		$this->_db = $db;
		$this->clear();
	}

	public function compile()
	{
		extract($this->_index);
		
		$sql = 'CREATE ';
		
		if ($type == self::kIndexTypeUnique)
		{
			$sql .= 'UNIQUE ';
		}
		
		if (!is_array($fields))
		{
			$fields = explode(',', $fields);
		}
		$fields = implode(',', array_map(array($this->_db, 'quoteIdentifier'), array_map('trim', $fields)));
		
		$multiField = strpos($fields, ',');
		if (empty($name))
		{
			$name = $fields;
			if ($multiField !== false)
			{
				$name = substr($name, 0, $multiField);
			}
		}
		
		$sql .= "INDEX {{$name}} ON {{$this->_table}} ({$fields})";

		return $sql;
	}

	public function clear()
	{
		$this->_table = '';
		$this->_index = NULL;
		return $this;
	}

	public function table($name)
	{
		$this->clear();
		$this->_table = $name;
		return $this;
	}

	public function index($type, $fields, $name = NULL)
	{
		$this->_index = array('type'=>$type, 'fields'=>$fields, 'name'=>$name);
		return $this;
	}
}

// -----------------------------------------------------------------------------

class MySQLFunction_drop_index extends SparkDBQueryFunction implements iSparkDBQueryFunctionDropIndex
{
	protected $_db;
	protected $_table;
	protected $_index;
	
	public function __construct($db)
	{
		$this->_db = $db;
		$this->clear();
	}

	public function compile()
	{
		return "DROP INDEX {{$this->_index}} ON {{$this->_table}}";
	}

	public function clear()
	{
		$this->_table = '';
		$this->_index = '';
		return $this;
	}

	public function table($name)
	{
		$this->clear();
		$this->_table = $name;
		return $this;
	}

	public function drop($name)
	{
		$this->_index = $name;
		return $this;
	}
}

// -----------------------------------------------------------------------------

class MySQLFunction_reset_auto_increment extends SparkDBQueryFunction implements iSparkDBQueryFunctionResetAutoIncrement
{
	protected $_db;
	protected $_table;
	protected $_value;
	
	public function __construct($db)
	{
		$this->_db = $db;
		$this->clear();
	}

	public function compile()
	{
		return "ALTER TABLE {{$this->_table}} AUTO_INCREMENT={$this->_value}";
	}

	public function clear()
	{
		$this->_table = '';
		$this->_value = 1;
		return $this;
	}

	public function table($name)
	{
		$this->clear();
		$this->_table = $name;
		return $this;
	}

	public function reset($value = 1)
	{
		$this->_value = $value;
		return $this;
	}
}

// -----------------------------------------------------------------------------

class MySQLFunction_date extends SparkDBQueryFunction implements iSparkDBQueryFunctionDate
{
	const kNow = 1;
	const kAdd = 2;
	const kSub = 3;
	const kInterval = 4;

	private $_opStack;
	
	public function __construct($db)
	{
		$this->_opStack = array();
	}

	public function compile()
	{
		$ops = array_reverse($this->_opStack);
		$work = array();

		while (!empty($ops))
		{
			$next = array_pop($ops);
			
			if (is_string($next))
			{
				$work[] = $next;
				continue;
			}
			switch ($next)
			{
				case self::kAdd:
					$work[] = $this->buildAdd(array_pop($work), array_pop($work));
					break;
				case self::kSub:
					$work[] = $this->buildSub(array_pop($work), array_pop($work));
					break;
				case self::kInterval:
					$work[] = $this->buildInterval(array_pop($work), array_pop($work));
					break;
			}
		}
		
		return implode(', ', $work);
	}
	
	public function clear()
	{
		$this->_opStack = array();
		return $this;
	}

	public function date($dateStr)
	{
		$this->_opStack[] = '\''.strval($dateStr).'\'';
		return $this;
	}

	public function now()
	{
		$this->_opStack[] = 'NOW()';
		return $this;
	}

	public function today()
	{
		$this->_opStack[] = 'CURDATE()';
		return $this;
	}

	public function add()
	{
		$this->_opStack[] = self::kAdd;
		return $this;
	}

	public function sub()
	{
		$this->_opStack[] = self::kSub;
		return $this;
	}

	public function interval($num, $type)
	{
		$this->_opStack[] = "{$num}";
		$this->_opStack[] = "{$type}";
		$this->_opStack[] = self::kInterval;
		return $this;
	}

	private function buildAdd($b, $a)
	{
		return "DATE_ADD({$a}, {$b})";
	}
	
	private function buildSub($b, $a)
	{
		return "DATE_SUB({$a}, {$b})";
	}

	private function buildInterval($type, $num)
	{
		switch ($type)
		{
			case self::kSeconds:
				return "INTERVAL {$num} SECOND";
			case self::kMinutes:
				return "INTERVAL {$num} MINUTE";
			case self::kHours:
				return "INTERVAL {$num} HOUR";
			case self::kDays:
				return "INTERVAL {$num} DAY";
		}
	}
}

// -----------------------------------------------------------------------------

class MySQLFunction_replace extends SparkDBQueryFunction implements iSparkDBQueryFunctionReplace
{
	private $_search;
	private $_replace;
	private $_column;
	
	public function __construct($db)
	{
	}

	public function compile()
	{
		return "REPLACE({$this->_column}, '{$this->_search}', '{$this->_replace}')";
	}
	
	public function clear()
	{
		$this->_search = '';
		$this->_replace = '';
		$this->_column = '';
		return $this;
	}

	public function replace($search, $replace, $column)
	{
		$this->_search = $search;
		$this->_replace = $replace;
		$this->_column = $column;
		return $this;
	}
}

// -----------------------------------------------------------------------------

class MySQLFunction_condition extends SparkDBQueryFunction implements iSparkDBQueryFunctionCondition
{
	private $_cond;
	private $_if;
	private $_else;
	
	public function __construct($db)
	{
	}

	public function compile()
	{
		return "IF({$this->_cond}, {$this->_if}, {$this->_else})";
	}
	
	public function clear()
	{
		$this->_cond = '';
		$this->_if = '';
		$this->_else = '';
		return $this;
	}

	public function condition($cond, $if, $else)
	{
		$this->_cond = $cond;
		$this->_if = $if;
		$this->_else = $else;
		return $this;
	}
}

// -----------------------------------------------------------------------------

class _SparkDB_mysql extends SparkDatabase
{
	public function __construct($params)
	{
		$charset = isset($params['charset']) ? $params['charset'] : 'utf8';
		$timezone = isset($params['timezone']) ? $params['timezone'] : '+00:00';

		if ($charset)
		{
			if (!defined('PDO_MYSQL_ATTR_INIT_COMMAND'))	// work around bug in PHP 5.3.0-5.3.2 (fails to define class constant)
			{
				define('PDO_MYSQL_ATTR_INIT_COMMAND', defined('PDO::MYSQL_ATTR_INIT_COMMAND') ? PDO::MYSQL_ATTR_INIT_COMMAND : 1002);
			}
			$params['options'][PDO_MYSQL_ATTR_INIT_COMMAND] = "SET NAMES \"{$charset}\"";
		}
		parent::__construct($params);
		
		if ($timezone)
		{
			$this->query("SET time_zone = \"{$timezone}\"");
		}
	}

	public function quoteIdentifier($ident)
	{
		return '`' . $ident . '`';
	}

	public function buildSelectOrderBy($orderBy = NULL)
	{
		return preg_replace('/^\s*order\s+by\s+rand\s*$/i', 'ORDER BY RAND()', parent::buildSelectOrderBy($orderBy));
	}

	public function buildUpsert($table, $row, $primaryKey, &$bind)
	{
		$columns = array_keys($row);
		$bind = array_values($row);
		$bind = array_merge($bind, $bind);
		$sql = $this->buildInsert($table, array_combine($columns, array_fill(0, count($columns), '?')));
		foreach($row as $col => $val)
		{
			$set[] = $this->quoteIdentifier($col) . '=?';
		}
		$sql .= ' ON DUPLICATE KEY UPDATE ' . implode(', ', $set);
		return $sql;
	}

	public function translateErrorCode($errorCode)
	{
		switch ($errorCode)
		{
			case 1007:
				return SparkDBException::kDatabaseExists;
			case 1054:
				return SparkDBException::kUnknownColumn;
			case 1062:
				return SparkDBException::kDuplicateRecord;
			case 1213:
				return SparkDBException::kDeadlock;
			default:
				return SparkDBException::kUnknown;
		}
	}

	public function getFunction($name)
	{
		switch ($name)
		{
			case 'metadata':
				return new MySQLFunction_metadata($this);
			case 'create_table':
				return new MySQLFunction_create_table($this);
			case 'create_index':
				return new MySQLFunction_create_index($this);
			case 'drop_index':
				return new MySQLFunction_drop_index($this);
			case 'alter_table':
				return new MySQLFunction_alter_table($this);
			case 'resetAutoIncrement':
				return new MySQLFunction_reset_auto_increment($this);
			case 'date':
				return new MySQLFunction_date($this);
			case 'replace':
				return new MySQLFunction_replace($this);
			case 'cond':
				return new MySQLFunction_condition($this);
			default:
				return parent::getFunction($name);
		}
	}
}

// -----------------------------------------------------------------------------