Location: PHPKode > projects > Escher CMS > eschercms-0.9.2/escher/sparkplug/plugs/sparkdb/adapters/sqlite.php
<?php

/*
Copyright 2009-2011 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 SQLiteFunction_metadata extends SparkDBFunction_metadata implements iSparkDBQueryFunctionMetadata
{
	protected function tables_sql()
	{
		return 'SELECT name FROM SQLITE_MASTER WHERE type="table" ORDER BY name';
	}
	
	protected function columns_sql($table)
	{
		return "PRAGMA table_info ({$table})";
	}
}

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

class SQLiteFunction_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',
		self::kForeignKeyActionSetDefault => 'SET DEFAULT',
	);

	protected $_table;
	protected $_fields;
	protected $_primaryKey;
	protected $_foreignKeys;
	
	public function __construct($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);
		}
		return "CREATE TABLE {{$this->_table}} ({$fields})";
	}

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

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

	public function field($name, $type, $length = NULL, $default = NULL, $canBeNULL = false, $flags = NULL)
	{
		switch ($type)
		{
			case self::kFieldTypeInteger:
			case self::kFieldTypeByte:
			case self::kFieldTypeShort:
			case self::kFieldTypeLong:
				$typeName = 'INTEGER';
				$default = is_integer($default) ? "{$default}" : NULL;
				break;
		
			case self::kFieldTypeFloat:
				$typeName = 'REAL';
				$default = is_float($default) ? "{$default}" : NULL;
				break;
		
			case self::kFieldTypeString:
				$typeName = 'TEXT';
				if ($length === NULL)
				{
					$length = 255;
				}
				$default = is_string($default) ? "'".$default."'" : ($canBeNULL ? NULL : "''");
				break;
		
			case self::kFieldTypeText:
				$typeName = 'TEXT';
				$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 = 'INTEGER';
				$default = is_bool($default) ? ($default ? '1' : '0') : NULL;
				break;

			case self::kFieldTypeBinary:
				$typeName = 'BLOB';
				$default = is_bool($default) ? ($default ? '1' : '0') : NULL;
				break;
		}

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

		return $this;
	}

	public function primaryKey($columns)
	{
		if (is_array($columns))
		{
			$columns = implode(',', $columns);
		}

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

	public function foreignKey($columns, $foreignTable, $foreignColumns, $actions = NULL)
	{
		if (is_array($columns))
		{
			$columns = implode(',', $columns);
		}
		if (is_array($foreignColumns))
		{
			$foreignColumns = implode(',', $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 SQLiteFunction_alter_table extends SQLiteFunction_create_table
{
	public function __construct($db)
	{
		parent::__construct($db);
	}

	public function compile()
	{
		if (count($this->_fields) !== 1)
		{
			throw new SparkDBException('sqlite adapter does not support appending multiple fields', SparkDBException::kFunctionNotImplemented);
		}
		return "ALTER TABLE {{$this->_table}} ADD COLUMN {$this->_fields[0]}";
	}

	public function primaryKey($columns)
	{
		throw new SparkDBException('sqlite adapter does not support altering primary key constraints', SparkDBException::kFunctionNotImplemented);
	}

	public function foreignKey($columns, $foreignTable, $foreignColumns, $actions = NULL)
	{
		throw new SparkDBException('sqlite adapter does not support altering foreign key constraints', SparkDBException::kFunctionNotImplemented);
	}
}

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

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

	public function compile()
	{
		extract($this->_index);
		
		$multiField = strpos($fields, ',');
		if (empty($name))
		{
			$name = $fields;
			if ($multiField !== false)
			{
				$name = substr($name, 0, $multiField);
			}
		}
		
		$sql = 'CREATE ';
		
		if ($type == self::kIndexTypeUnique)
		{
			$sql .= 'UNIQUE ';
		}
		
		$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 SQLiteFunction_drop_index extends SparkDBQueryFunction implements iSparkDBQueryFunctionDropIndex
{
	protected $_table;
	protected $_index;
	
	public function __construct($db)
	{
		$this->clear();
	}

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

	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 SQLiteFunction_reset_auto_increment extends SparkDBQueryFunction implements iSparkDBQueryFunctionResetAutoIncrement
{
	protected $_table;
	
	public function __construct($db)
	{
		$this->clear();
	}

	public function compile()
	{
		return "DELETE FROM sqlite_sequence WHERE name='{{$this->_table}}'";
	}

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

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

	public function reset($value = 1)
	{
		if ($value != 1)
		{
			throw new SparkDBException('sqlite adapter does not support resetting auto_increment to a value other than 1', SparkDBException::kFunctionNotImplemented);
		}
		return $this;
	}
}

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

class SQLiteFunction_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 'datetime(' . implode(', ', $work) . ')';
	}
	
	public function clear()
	{
		$this->_opStack = array();
		return $this;
	}

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

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

	public function today()
	{
		$this->_opStack[] = "'now', 'start of day'";
		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 "datetime({$a}, {$b})";
	}
	
	private function buildSub($b, $a)
	{
		$b = '\'-' . ltrim($b, '\'');
		return "datetime({$a}, {$b})";
	}

	private function buildInterval($type, $num)
	{
		switch ($type)
		{
			case self::kSeconds:
				return "'{$num} seconds'";
			case self::kMinutes:
				return "'{$num} minutes'";
			case self::kHours:
				return "'{$num} hours'";
			case self::kDays:
				return "'{$num} days'";
		}
	}
}

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

class SQLiteFunction_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 SQLiteFunction_condition extends SparkDBQueryFunction implements iSparkDBQueryFunctionCondition
{
	private $_cond;
	private $_if;
	private $_else;
	
	public function __construct($db)
	{
	}

	public function compile()
	{
		return "CASE WHEN {$this->_cond} THEN {$this->_if} ELSE {$this->_else} END";
	}
	
	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_sqlite extends SparkDatabase
{
	private $_is_php_5_3;
	
	public function __construct($params)
	{
		parent::__construct($params);
		
		$this->_is_php_5_3 = (version_compare(PHP_VERSION, '5.3.0') >= 0);
		
		$this->query('PRAGMA foreign_keys = ON');
	}

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

	public function buildUpsert($table, $row, $primaryKey, &$bind)
	{
		$columns = array_keys($row);
		$bind = array_values($row);
		$sql = $this->buildInsert($table, array_combine($columns, array_fill(0, count($columns), '?')));
		$sql = preg_replace('/^INSERT/', 'INSERT OR REPLACE', $sql);
		return $sql;
	}

	protected function preBindArg(&$arg)
	{
		// Prior to PHP 5.3, BLOBs are not handled correctly by PDO, so we override
		// PDO's behavior  by manually creating a BLOB in the preBindArg() method.
		
		if (!$this->_is_php_5_3)
		{
			if (is_array($arg))
			{
				$arg = "X'" . bin2hex($arg[0]) . "'";
				return true;
			}
		}
		return false;
	}

	public function translateErrorCode($errorCode)
	{
		switch ($errorCode)
		{
			case 19:
				return SparkDBException::kDuplicateRecord;
			default:
				return SparkDBException::kUnknown;
		}
	}

	public function getFunction($name)
	{
		switch ($name)
		{
			case 'metadata':
				return new SQLiteFunction_metadata($this);
			case 'create_table':
				return new SQLiteFunction_create_table($this);
			case 'create_index':
				return new SQLiteFunction_create_index($this);
			case 'drop_index':
				return new SQLiteFunction_drop_index($this);
			case 'alter_table':
				return new SQLiteFunction_alter_table($this);
			case 'resetAutoIncrement':
				return new SQLiteFunction_reset_auto_increment($this);
			case 'date':
				return new SQLiteFunction_date($this);
			case 'replace':
				return new SQLiteFunction_replace($this);
			case 'cond':
				return new SQLiteFunction_condition($this);
			default:
				return parent::getFunction($name);
		}
	}
}

// -----------------------------------------------------------------------------
Return current item: Escher CMS