<?php
/**
 * TSqliteMetaData class file.
 *
 * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
 * @link http://www.pradosoft.com/
 * @copyright Copyright &copy; 2005-2014 PradoSoft
 * @license http://www.pradosoft.com/license/
 * @version $Id: TSqliteMetaData.php 1861 2007-04-12 08:05:03Z wei $
 * @package System.Data.Common.Sqlite
 */

/**
 * Load the base TDbMetaData class.
 */
Prado::using('System.Data.Common.TDbMetaData');
Prado::using('System.Data.Common.Sqlite.TSqliteTableInfo');

/**
 * TSqliteMetaData loads SQLite database table and column information.
 *
 * @author Wei Zhuo <weizho[at]gmail[dot]com>
 * @version $Id: TSqliteMetaData.php 1861 2007-04-12 08:05:03Z wei $
 * @package System.Data.Commom.Sqlite
 * @since 3.1
 */
class TSqliteMetaData extends TDbMetaData
{
	/**
	 * @return string TDbTableInfo class name.
	 */
	protected function getTableInfoClass()
	{
		return 'TSqliteTableInfo';
	}

	/**
	 * Quotes a table name for use in a query.
	 * @param string $name table name
	 * @return string the properly quoted table name
	 */
	public function quoteTableName($name)
	{
		return parent::quoteTableName($name, "'", "'");
	}

	/**
	 * Quotes a column name for use in a query.
	 * @param string $name column name
	 * @return string the properly quoted column name
	 */
	public function quoteColumnName($name)
	{
		return parent::quoteColumnName($name, '"', '"');
	}

	/**
	 * Quotes a column alias for use in a query.
	 * @param string $name column alias
	 * @return string the properly quoted column alias
	 */
	public function quoteColumnAlias($name)
	{
		return parent::quoteColumnAlias($name, '"', '"');
	}

	/**
	 * Get the column definitions for given table.
	 * @param string table name.
	 * @return TPgsqlTableInfo table information.
	 */
	protected function createTableInfo($tableName)
	{
		$tableName = str_replace("'",'',$tableName);
		$this->getDbConnection()->setActive(true);
		$table = $this->getDbConnection()->quoteString($tableName);
		$sql = "PRAGMA table_info({$table})";
		$command = $this->getDbConnection()->createCommand($sql);
		$foreign = $this->getForeignKeys($table);
		$index=0;
		$columns=array();
		$primary=array();
		foreach($command->query() as $col)
		{
			$col['index'] = $index++;
			$column = $this->processColumn($col, $foreign);
			$columns[$col['name']] = $column;
			if($column->getIsPrimaryKey())
				$primary[] = $col['name'];
		}
		$info['TableName'] = $tableName;
		if($this->getIsView($tableName))
			$info['IsView'] = true;
		if(count($columns)===0)
			throw new TDbException('dbmetadata_invalid_table_view', $tableName);
		$class = $this->getTableInfoClass();
		$tableInfo = new $class($info,$primary,$foreign);
		$tableInfo->getColumns()->copyFrom($columns);
		return $tableInfo;
	}

	/**
	 * @param string table name.
	 * @return boolean true if the table is a view.
	 */
	protected function getIsView($tableName)
	{
		$sql = 'SELECT count(*) FROM sqlite_master WHERE type="view" AND name= :table';
		$this->getDbConnection()->setActive(true);
		$command = $this->getDbConnection()->createCommand($sql);
		$command->bindValue(':table', $tableName);
		return intval($command->queryScalar()) === 1;
	}

	/**
	 * @param array column information.
	 * @param array foreign key details.
	 * @return TSqliteTableColumn column details.
	 */
	protected function processColumn($col, $foreign)
	{
		$columnId = $col['name']; //use column name as column Id

		$info['ColumnName'] = '"'.$columnId.'"'; //quote the column names!
		$info['ColumnId'] = $columnId;
		$info['ColumnIndex'] = $col['index'];

		if($col['notnull']!=='99')
			$info['AllowNull'] = true;

		if($col['pk']==='1')
			$info['IsPrimaryKey'] = true;
		if($this->isForeignKeyColumn($columnId, $foreign))
			$info['IsForeignKey'] = true;

		if($col['dflt_value']!==null)
			$info['DefaultValue'] = $col['dflt_value'];

		$type = strtolower($col['type']);
		$info['AutoIncrement'] = $type==='integer' && $col['pk']==='1';

		$info['DbType'] = $type;
		$match=array();
		if(is_int($pos=strpos($type, '(')) && preg_match('/\((.*)\)/', $type, $match))
		{
			$ps = explode(',', $match[1]);
			if(count($ps)===2)
			{
				$info['NumericPrecision'] = intval($ps[0]);
				$info['NumericScale'] = intval($ps[1]);
			}
			else
				$info['ColumnSize']=intval($match[1]);
			$info['DbType'] = substr($type,0,$pos);
		}

		return new TSqliteTableColumn($info);
	}

	/**
	 *
	 *
	 * @param string quoted table name.
	 * @return array foreign key details.
	 */
	protected function getForeignKeys($table)
	{
		$sql = "PRAGMA foreign_key_list({$table})";
		$command = $this->getDbConnection()->createCommand($sql);
		$fkeys = array();
		foreach($command->query() as $col)
		{
			$fkeys[$col['table']]['keys'][$col['from']] = $col['to'];
			$fkeys[$col['table']]['table'] = $col['table'];
		}
		return count($fkeys) > 0 ? array_values($fkeys) : $fkeys;
	}

	/**
	 * @param string column name.
	 * @param array foreign key column names.
	 * @return boolean true if column is a foreign key.
	 */
	protected function isForeignKeyColumn($columnId, $foreign)
	{
		foreach($foreign as $fk)
		{
			if(in_array($columnId, array_keys($fk['keys'])))
				return true;
		}
		return false;
	}

        /**
	 * Returns all table names in the database.
	 * @param string $schema the schema of the tables. This is not used for sqlite database.
	 * @return array all table names in the database.
	 */
	public function findTableNames($schema='')
	{
		$sql="SELECT DISTINCT tbl_name FROM sqlite_master WHERE tbl_name<>'sqlite_sequence'";
		return $this->getDbConnection()->createCommand($sql)->queryColumn();
	}
}