taoqu/Frame/Package/Database/Sharding.php

<?php

namespace Heanup\Frame\Package\Database;

/**
 * 数据库操作类
 * 分表模式
 */
class Sharding extends \Heanup\Frame\Package\Database
{
    protected $configClass = 'MySQL\\Core';
    protected $configSection = null;
    protected $tableName = '';
    protected $fields = array();
    protected $primaryKey = 'id';

    /**
     * 获取计数
     * @param Array $whereParam
     * @param string $whereString
     * @return int
     */
    public function count($whereParam, $whereString = '')
    {
        $query = $this->getQuery()->setWhere($whereParam, $whereString)->count();
        $result = $this->getConnection()->read($query->getSQL(), $query->getData());
        if (!$result) {
            return 0;
        }
        $result = current($result);
        return intval($result['count']);
    }

    /**
     * 获取列表
     * @param array $fields
     * @param array $whereParam
     * @param string $whereString
     * @param int $limit
     * @param int $offset
     * @param array $orderBy
     * @return array
     */
    public function select(
        $fields = array(),
        $whereParam = array(),
        $whereString = '',
        $limit = 10,
        $offset = 0,
        $orderBy = array())
    {
        $query = $this->getQuery()
            ->setField($fields)
            ->setWhere($whereParam, $whereString)
            ->setLimit($limit, $offset)
            ->setOrderBy($orderBy)
            ->select();
        $result = $this->getConnection()->read($query->getSQL(), $query->getData());
        if (!$result) {
            return $result;
        }
        $data = array();
        foreach ($result as $row) {
            $data[$row[$this->primaryKey]] = $row;
        }
        unset($result);
        return $data;
    }

    /**
     * 获取列表数据，简单版
     * @param array $whereParam
     * @param int $limit
     * @return array
     */
    public function getList($whereParam = array(), $limit = 10)
    {
        return $this->select(array(), $whereParam, '', $limit, 0, array());
    }

    /**
     * 获取列表ID信息
     * @param string $field
     * @param array $whereParam
     * @param $orderBy
     * @param $limit
     * @return array
     */
    public function getListId($field = '', $whereParam = array(), $orderBy = array(), $limit = 2000)
    {
        $query = self::getQuery()
            ->setField($field)
            ->setWhere($whereParam)
            ->setLimit($limit)
            ->setOrderBy($orderBy)
            ->select();
        $result = $this->getConnection()->read($query->getSQL(), $query->getData());
        if (!$result) {
            return $result;
        }
        $data = array();
        foreach ($result as $row) {
            $data[] = $row[$field];
        }
        unset($result);
        return $data;
    }

    /**
     * 获取单条记录
     * @param array $whereParam
     * @param string $whereString
     * @param array $orderBy
     * @return array;
     */
    public function getOne($whereParam = array(), $whereString = '', $orderBy = array())
    {
        $query = $this->getQuery()
            ->setWhere($whereParam, $whereString)
            ->setOrderBy($orderBy)
            ->setLimit(1)
            ->select();
        $result = $this->getConnection()->read($query->getSQL(), $query->getData(), false, false);
        return $result;
    }

    /**
     * 根据主键获取单条记录
     * @param int $id
     * @return array
     */
    public function getLine($id)
    {
        $where = array($this->primaryKey => $id);
        $query = $this->getQuery()->setWhere($where)->setLimit(1)->select();
        $result = $this->getConnection()->read($query->getSQL(), $query->getData(), false, false);
        return $result;
    }

    /**
     * 批量根据主键查询
     * @param $idList
     * @return array|mixed
     */
    public function getBatch($idList)
    {
        $where = array($this->primaryKey => $idList);
        $query = $this->getQuery()->setWhere($where)->select();
        $result = $this->getConnection()->read($query->getSQL(), $query->getData());
        if (!$result) {
            return $result;
        }
        $data = array();
        foreach ($result as $row) {
            $data[$row[$this->primaryKey]] = $row;
        }
        unset($result);
        return $data;
    }

    /**
     * 创建记录
     * @param $data
     * @param bool 是否返回最后最后插入ID
     * @return bool|int
     */
    public function insert($data, $returnLastInsertId = false)
    {
        $query = $this->getQuery()->setData($data)->insert();
        $result = $this->getConnection()->write($query->getSQL(), $query->getData());
        // 返回最后插入的id
        if ($result && $returnLastInsertId) {
            $result = $this->getConnection()->getLastInsertId();
        }
        return $result;
    }

    /**
     * 批量插入
     * $data = array(
     *      array(
     *          'id' => 10001,
     *          'target_id' => 100,
     *      ),
     *      array(
     *          'id' => 10002,
     *          'target_id' => 100,
     *      ),
     * );
     * @param $data
     * @return bool|int
     */
    public function insertBatch($data)
    {
        $prefix = 'prepared_';
        // 确定字段名
        $fields = implode('`, `', array_keys(current($data)));
        // 拼SQL和数据
        $dataList = array();
        $values = array();
        $i = 0;
        foreach ($data as $k => $v) {
            foreach ($v as $x => $y) {
                $key = $prefix . $i;
                $dataList[$key] = $y;
                $v[$x] = ':' . $key;
                $i++;
            }
            $values[] = "(" . implode(", ", $v) . ")";
        }
        $values = implode(', ', $values);
        $sql = "INSERT INTO `" . $this->tableName . "` (`$fields`) VALUES $values";
        $result = $this->write($sql, $dataList);
        return $result;
    }

    /**
     * 更新记录
     * @param $data
     * @param $whereParam
     * @param $whereString
     * @return bool
     */
    public function update($data, $whereParam, $whereString = '')
    {
        $query = $this->getQuery()->setData($data)->setWhere($whereParam, $whereString)->update();
        $result = $this->getConnection()->write($query->getSQL(), $query->getData());
        return $result;
    }

    /**
     * 替换输入
     * @param $data
     * @param $whereParam
     * @param $whereString
     * @return bool
     */
    public function replace($data, $whereParam, $whereString = '')
    {
        $query = $this->getQuery()->setData($data)->setWhere($whereParam, $whereString)->replace();
        $result = $this->getConnection()->write($query->getSQL(), $query->getData());
        return $result;
    }

    /**
     * 删除逻辑
     * @param $whereParam
     * @param $whereString
     * @return bool
     */
    public function delete($whereParam, $whereString = '')
    {
        $query = $this->getQuery()->setWhere($whereParam, $whereString)->delete();
        $result = $this->getConnection()->write($query->getSQL(), $query->getData());
        return $result;
    }


    /**
     * 获取一个新的SQL生成器
     * @return \Heanup\Frame\Library\MySQL\Query
     */
    protected function getQuery()
    {
        return \Heanup\Frame\Library\MySQL\Query::newInstance()
            ->setTableName($this->tableName)
            ->setField($this->fields)
            ->setPrimaryKey($this->primaryKey);
    }

    /**
     * 读取
     * @param $sql
     * @param $data
     * @param bool $isMaster
     * @return mixed|Array
     */
    protected function read($sql, $data, $isMaster = false)
    {
        $result = $this->getConnection()->read($sql, $data, $isMaster);
        return $result;
    }

    /**
     * 写入
     * @param $sql
     * @param $data
     * @return bool|int
     */
    protected function write($sql, $data)
    {
        $result = $this->getConnection()->write($sql, $data);
        return $result;
    }

    /**
     * 获取连接池
     * @return \Heanup\Frame\Library\MySQL
     */
    protected function getConnection()
    {
        return new \Heanup\Frame\Library\MySQL($this->configClass, $this->configSection);
    }
    /**
     * 根据数值来确定分表-分库方法
     * 1.
     * 可以自定义分表-分库的模板前缀$tbl变量
     * 2. 可以自定义截取长度
     * 3. 一般可以根据用户UID来获取分表或者分库
     *
     * @param int $num
     *            数值
     * @param string $tbl
     *            模板前缀
     * @param int $default
     *            默认截取长度
     */
    protected function num_identify($num, $tbl, $default = 1)
    {
        $num = (string) $num;
        $len = strlen($num);
        if ($len >= $default)
            $str = substr($num, $len - $default, $default);
        else
            $str = str_pad($num, $default, '0', STR_PAD_LEFT);
        return $tbl . '_' . $str;
    }
    /**
     * 求余数的方式获取分表-分库方法
     * 1. 求余方式余数比较少，适合小型的分表法
     * 2. 可以自定义求余除数
     * Dao中使用方法：$this->dao->db->fmod_identify($num, $tbl, $default = 7)
     * @param int $num
     * @param string $tbl
     * @param int $default
     * @return
     */
    public function fmod_identify($num, $tbl, $default = 7) {
        return $tbl . '_' . fmod($num/$default);
    }

}