[其它 Other] ci数据库工具类扩展

ci的数据库工具类、没有获取数据表列表等等的方法。在这里做了个扩展。主要用于做数据中心管理的。

<?php

/**
*
* @describe 数据中心模型
*
* @see class Databases()
* @author CrazyCodes <625566775@qq.com>
* @copyright MyAdmin 2017/3/14
* @version MyAdmin v0.0.1 Beta
*
* @internal
*      CrazyCodes <625566775@qq.com>
*
*/
class Databases_model extends MY_Model
{
    protected $tablesDb = NULL;

    protected $tableName = 'Tables_in_';

    private $mysqlSize = "concat(round(sum((DATA_LENGTH+INDEX_LENGTH)/1024/1024),2),'M')";

    private $mysqlRow = "TABLE_ROWS";

    private $mysqlEngine = "ENGINE";

    private $mysqlTime = "CREATE_TIME";

    /**
     *
     * @describe 构造
     *
     * @see $tablesDb 实例化一个新的数据对象、用于查询数据库
     * @load dbutil 数据库工具类
     * @access public
     * @see __construct()
     */
    public function __construct()
    {
        parent::__construct();

        $this->load->dbutil();

        $this->tablesDb = $this->load->database('tables', true);

        $this->tableName = $this->tableName . $this->db->database;
    }

    /**
     *
     * @describe 获取数据库列表 - 保留方法
     *
     * @load null
     * @access public
     * @see _getDatabases()
     */
    public function _getDatabases()
    {

    }

    /**
     *
     * @describe 获取数据表列表
     *
     * @load null
     * @access public
     * @see _getTables()
     */
    public function _getTables()
    {
        $result = $this->db->query('SHOW TABLES')->result_array();

        return $result;
    }

    /**
     *
     * @describe 获取数据表详细信息
     *
     * @load null
     * @access public
     * @see _getTableData()
     * @$_databasesData 查询到的数据表
     *
     */
    public function _getTableData($_databasesData)
    {

        foreach ($_databasesData as $key => $val) {

            $_databasesData[$key]['size'] = $this->_getSizeData($val[$this->tableName]);
            $_databasesData[$key]['row'] = $this->_getRowData($val[$this->tableName]);
            $_databasesData[$key]['engine'] = $this->_getEngineData($val[$this->tableName]);
            $_databasesData[$key]['time'] = $this->_getTimeData($val[$this->tableName]);

        }


        return $_databasesData;
    }

    /**
     *
     * @describe 获取数据添加时间
     *
     * @load null
     * @access protected
     * @see _getTimeData()
     * @$_tableName 要查询的表名
     */
    protected function _getTimeData($_tableName)
    {
        $timeData = $this->tablesDb->query($this->_sqlData($this->mysqlTime, $_tableName))->row_array();

        return $timeData['data'];
    }

    /**
     *
     * @describe 获取数据引擎
     *
     * @load null
     * @access protected
     * @see _getEngineData()
     * @$_tableName 要查询的表名
     */
    protected function _getEngineData($_tableName)
    {
        $engineData = $this->tablesDb->query($this->_sqlData($this->mysqlEngine, $_tableName))->row_array();

        return $engineData['data'];
    }

    /**
     *
     * @describe 获取数据条数
     *
     * @load null
     * @access protected
     * @see _getRowData()
     * @$_tableName 要查询的表名
     */
    protected function _getRowData($_tableName)
    {
        $rowData = $this->tablesDb->query($this->_sqlData($this->mysqlRow, $_tableName))->row_array();

        return $rowData['data'];
    }

    /**
     *
     * @describe 获取数据表大小
     *
     * @load null
     * @access protected
     * @see _getSizeData()
     * @$_tableName 要查询的表名
     */
    protected function _getSizeData($_tableName)
    {
        $sizeData = $this->tablesDb->query($this->_sqlData($this->mysqlSize, $_tableName))->row_array();

        return $sizeData['data'];
    }

    /**
     *
     * @describe 获取数据表详细信息
     *
     * @load null
     * @access protected
     * @see _sqlData()
     * @$_param 查询的参数
     * @$_tableName 要查询的表名
     */
    protected function _sqlData($_param, $_tableName)
    {
        $sql = "select " . $_param . " as data from tables " .
            "where table_schema='{$this->db->database}' AND " .
            "table_name='{$_tableName}'; ";

        return $sql;
    }
}

database.php 内设置 \ 因为要在information_schema库内查询、所以需要重新指向一个库。

/**
*
* @describe 为实现数据字典新建的库
*
* @access public
* @see __construct()
*/
$db['tables'] = $db['default'];
$db['tables']['database'] = 'information_schema';
 2018-06-28 14:12:32 星期四