farm/mazephp/.svn/pristine/a7/a72bf83d032391cfef62afe0881540d36e7e8902.svn-base

<?php namespace Maze\Data;

class Sql
{
	/**
     * instance
     *
     * @var string
     */
    static protected $instance;

    /**
     * getInstance
     * 
     * @return Maze\Data\Sql;
     */
    static public function getInstance()
    {
        if(empty(self::$instance))
        {
            self::$instance = new self();
        }

        return self::$instance->init();
    }

    /**
     * create
     * 
     * @return string
     */
    public function create($table, $struct)
    {
        $create = $primary = array();

        foreach($struct as $k => $v)
        {
			if(isset($v['table']) && $v['table'] != $table)
			{
				continue;
			}
            $primary[$k] = '';
            if(is_array($v))
            {
				if(!isset($v['type']))
                {
                    continue;
                }
                $com = $v;
                $v = $com['type'];
                if(strpos($com['type'], 'text') !== false)
                {
                    $primary[$k] .= '';
                }
                elseif(strpos($com['type'], 'int') === false)
                {
                    $primary[$k] .= 'not null default \'\'';
                }
                elseif(!empty($com['default']))
                {
                    $primary[$k] .= 'not null default \'' . $com['default'] . '\'';
                }
                elseif($k != 'id' && strpos($com['type'], 'int') !== false)
                {
                    $primary[$k] .= 'not null default 0';
                }
                else
                {
                    $primary[$k] .= 'not null';
                }

                if(!empty($com['name']))
                {
                    $primary[$k] .= ' comment \'' . $com['name'] . '\'';
                }
            }
            elseif(is_string($v) && strpos($v,' ') !== false)
            {
                $com = explode(' ',$v);
                $v = $com[0];
                if(!empty($com[1]))
                {
                    $primary[$k] .= 'not null default \'' . $com[1] . '\'';
                }
                else
                {
                    $primary[$k] .= 'not null';
                }

                if(!empty($com[2]))
                {
                    $primary[$k] .= ' comment \'' . $com[2] . '\'';
                }
            }
            if($k == 'id')
            {
                $primary[$k] = 'unsigned auto_increment primary key ' . $primary[$k];
            }

            $create[] = '`' . $k . '` ' . strtoupper(str_replace('-','(',$v) . ') ' . $primary[$k] . '');// not null 
        }
        $sql    = 'DROP TABLE IF EXISTS `' . $table . '`;CREATE TABLE `' . $table . '`(' . implode(',', $create) . ')';

        //echo $sql;die;

        return $sql;
    }

    /**
     * alter
     * 
     * @return string
     */
    public function alter($table, $config)
    {
        $create = $primary = array();

        $alter = 'ALTER TABLE `' . $table;

        $sql = array();
        foreach($config as $k => $v)
        {
			if(isset($v['type']))
			{
				$v = array
				(
					'add', $k, $k, $v['type'] . ' ' . $v['default'] . ' ' . $v['name'],
				);
			}
            $sql[$k] = '';
            if(isset($v[3]) && strpos($v[3],' ') !== false)
            {
                $com = explode(' ',$v[3]);
                $sql[$k] = str_replace('-','(',$com[0]) . ') ';
                if(isset($com[1]) && $com[1] != '')
                {
                    $sql[$k] .= 'not null default \'' . $com[1] . '\'';
                }
                else
                {
                    $sql[$k] .= 'not null';
                }

                if(!empty($com[2]))
                {
                    $sql[$k] .= ' comment \'' . $com[2] . '\'';
                }
                $sql[$k] = strtoupper($sql[$k]);
            }

            if($v[0] == 'add')
            {
                # 新增字段
                $sql[$k] = $alter . '` ADD `' . $v[1] . '` ' . $sql[$k];
            }
            elseif($v[0] == 'delete')
            {
                # 删除字段
                $sql[$k] = $alter . '` DROP `' . $v[1] . '`';
            }
            else
            {
                # 修改字段
                $sql[$k] = $alter . '` CHANGE `' . $v[1] . '` `' . $v[2] . '` ' . $sql[$k];
            }
        }

        $sql = implode(';', $sql);
        
        return $sql;
    }

    /**
     * col
     * 
     * @return string
     */
    private function col($col)
    {
        $result = '';

        if(is_array($col))
        {
        	$array = array();
            foreach($col as $k => $v)
            {
                if(!is_numeric($k))
                {
                    $array[] = $k . ' AS ' . $v;
                }
                else
                {
                    $array[] = $v;
                }
            }
            $result = implode(' ', $array);
        }
        else
        {
            $result = $col ? $col : '*';
        }

        if(isset($this->col) && $this->col)
        {
            $result .= ',' . $this->col;
        }
        return $result;
    }

    /**
     * select
     * 
     * @return string
     */
    public function select($table, $col = '')
    {
        $where = '';
        if($this->where)
        {
			if(isset($this->between))
			{
				$where = 'WHERE ' . $this->between . ' AND ' . implode(' ', $this->where);
				$this->limit = '';
			}
			else
			{
				$where = 'WHERE ' . implode(' ', $this->where);
			}
        }

        $sql = 'SELECT ' . $this->col($col) . ' FROM `' . $table . '` ' . $where . ' ' . $this->group . ' ' . $this->order . ' ' . $this->limit;

        $this->init();

        return $sql;
    }

    /**
     * count
     * 
     * @return string
     */
    public function count($table, $col = '')
    {
        $where = '';
        if($this->where)
        {
            $where = 'WHERE ' . implode(' ', $this->where);
        }

        $state = 1;
        if($col == 'clear')
        {
            $col = '';
            $state = 2;
        }
        if(!$col)
        {
            $col = 'count(*) as total';
        }

        $sql = 'SELECT ' . $col . ' FROM `' . $table . '` ' . $where . ' ' . $this->group . ' ';

        if($state == 1)
        {
            $this->init();
        }

        return $sql;
    }
    
    /**
     * showIndex
     * 
     * @return string
     */
    public function showIndex($table)
    {
        $sql = 'SHOW INDEX FROM `' . $table . '` ';
        
        return $sql;
    }
    
    /**
     * dropIndex
     * 
     * @return string
     */
    public function dropIndex($table, $name)
    {
        $sql = 'ALTER TABLE `' . $table . '` DROP INDEX ' . $name;
        
        return $sql;
    }

    /**
     * index
     * 
     * @return string
     */
    public function index($table, $value)
    {
        $sql = 'ALTER TABLE `' . $table . '` ADD INDEX ';

        $max = count($value)-1;

        $i = 0;
        
        foreach($value as $k => $v)
        {
            $sql .= ' ' . $k . ' (' . $v . ')';

            if($i >= $max)
            {
                $sql .= '';
            }
            else
            {
                $sql .= ',';
            }

            $i++;
        }

        return $sql;
    }

    /**
     * insert
     * 
     * @return string
     */
    public function insert($table)
    {
        $sql = 'INSERT INTO `' . $table . '` (' . implode(',', $this->col) . ') VALUES (' . implode(',', $this->value) . ')';

        $this->init();

        return $sql;
    }
    
    /**
     * inserts
     * 
     * @return string
     */
    public function inserts($table, $col, $value)
    {
        $sql = 'INSERT INTO `' . $table . '` (' . $col . ') VALUES ';

        $max = count($value)-1;

        foreach($value as $k => $v)
        {
            $sql .= '(' . $v . ')';

            if($k >= $max)
            {
                $sql .= '';
            }
            else
            {
                $sql .= ',';
            }
        }

        return $sql;
    }

    /**
     * update
     * 
     * @return string
     */
    public function update($table)
    {
    	$where = '';
        if(!$this->where)
        {
            return false;
        }
        else
        {
        	$where = 'WHERE ' . implode(' ', $this->where);
        }

        $sql = 'UPDATE `' . $table . '` SET ' . implode(',', $this->value) . ' ' . $where;

        $this->init();

        return $sql;
    }

    /**
     * delete
     * 
     * @return string
     */
    public function delete($table)
    {
    	$where = '';
        if(!$this->where)
        {
            return false;
        }
        else
        {
        	$where = 'WHERE ' . implode(' ', $this->where);
        }

        $sql = 'DELETE FROM `' . $table . '` ' . $where;

        $this->init();

        return $sql;
    }
    
    /**
     * truncate
     * 
     * @return string
     */
    public function truncate($table)
    {
		$sql = 'TRUNCATE TABLE `' . $table . '`';
		
		return $sql;
	}
	
	/**
     * sql
     * 
     * @return string
     */
    public function sql($sql)
    {
		return $sql;
	}

    /**
     * init
     * 
     * @return object
     */
    public function init()
    {
        $this->where = $this->value = $this->col = array();
        $this->order = '';
        $this->group = '';
        $this->limit = '';

        return $this;
    }

    /**
     * where
     * 
     * @return string
     */
    public function where($param)
    {
        if(empty($param[2])) $param[2] = '=';

        if(empty($param[3])) $param[3] = 'and';

        $where = '`' . $param[0] . '` ' . $param[2] . ' ' . $param[1];

        if(!$this->where)
        {
            $this->where[] = $where;
        }
        else
        {
            $this->where[] = $param[3] . ' ' . $where;
        }
    }

    /**
     * order
     * 
     * @return string
     */
    public function order($param)
    {
		if(is_array($param[0]))
		{
			$this->order = 'order by ';
			foreach($param[0] as $k => $v)
			{
				$order[] = '`' . $k . '` ' . $v;
			}
			
			$this->order .= implode(',', $order);
			
			//echo $this->order;
		}
		else
		{
			if(empty($param[1])) $param[1] = 'desc';

			$this->order = 'order by `' . $param[0] . '` ' . $param[1];
		}
    }

    /**
     * group
     * 
     * @return string
     */
    public function group($param)
    {
        if(is_array($param))
        {
            $param = $param[0];
        }

		# 去掉id的分组，没用
		if(isset($param) && $param != 'id')
		{
            if($param == 'day')
            {
                $this->col = 'FROM_UNIXTIME(cdate, "%Y-%m-%d") as day';
            }
            elseif($param == 'month')
            {
                $this->col = 'FROM_UNIXTIME(cdate, "%Y-%m") as month';
            }
            elseif($param == 'year')
            {
                $this->col = 'FROM_UNIXTIME(cdate, "%Y") as year';
            }
            else
            {
                $this->col = $param;
            }
			$this->group = 'group by `' . $param . '`';
		}
    }

    /**
     * limit
     * 
     * @return string
     */
    public function limit($param)
    {
        if(empty($param[1])) $param[1] = 0;

        $this->limit = 'limit ' . $param[1] . ',' . $param[0];
        
        //$this->between = ' `id` BETWEEN ' . $param[1] . ' AND ' . ($param[1] + $param[0]);
    }

    /**
     * reset limit
     * 
     * @return string
     */
    public function reset($param)
    {
        $this->{$param[0]} = '';
    }

    /**
     * add
     * 
     * @return string
     */
    public function add($param)
    {
        $this->col[] = '`' . $param[0] . '`';
        $this->value[] = $param[1];
    }

    /**
     * set
     * 
     * @return string
     */
    public function set($param)
    {
        if(empty($param[2])) $param[2] = '=';

        $param[0] = '`' . $param[0] . '`';

        if(strpos($param[2], '+=') !== false)
        {
            $param[2] = '=' . $param[0] . '+';
        }

        if(strpos($param[2], '-=') !== false)
        {
            $param[2] = '=' . $param[0] . '-';
        }

        $this->value[] = $param[0] . $param[2] . $param[1];
    }
}