TpMeCMS以及达梦数据库的适配

网上看了一个比较好的开源CMS框架，并对他做达梦数据库的适配，遇到部分问题。TpMeCMS是一款基于FastAdmin框架（version:V1.3.3.20220121）开发的，FastAdmin基于ThinkPHP5+Bootstrap开发的框架。

---

一、所用软件或者源码的下载地址

1、达梦数据库

2、phpstudy下载

3、TpMeCMS

二、安装达梦数据库

1、安装数据库

2、通过DM数据配置助手配置数据库

三、phpstudy下载小皮面板下载

1、配置php环境

2、配置mysql

四、TpMeCMS源代码下载

1、git clone TpMeCMS

2、通过phpstudy配置环境

注意在phpstudy添加伪静态

location / {
    if (!-e $request_filename) {
        rewrite ^(.*)$ /index.php?s=$1 last;
        break;
    }
}

3、给网站配置端口域名安装网站数据 库类型是mysql,在通过DM数据配置数据库数据迁移工具迁移mysql数据库

4、选定php环境我选的php7.3.9nts，进入达梦数据库的安装目录

D:\Program Files\DmData\dmdbms\drivers\php_pdo 把pdo73nts_dm.dll、php73nts_dm.dll拷贝到

phpstudy的D:\Program Files\phpstudy_pro\Extensions\php\php7.3.9nts\ext 重启环境查看安装完成DM 和 PDO_DM

C:\Users\FZDD>php -m
[PHP Modules]
bcmath
calendar
Core
ctype
curl
date
DM
dom
fileinfo
filter
gd
hash
iconv
json
libxml
mbstring
mysqli
mysqlnd
odbc
openssl
pcre
PDO
PDO_DM
pdo_mysql
PDO_ODBC
pdo_sqlite
Phar
readline
Reflection
session
SimpleXML
SPL
standard
tokenizer
wddx
xml
xmlreader
xmlwriter
zip
zlib

[Zend Modules]

5、配置达梦数据库链接并在D:\Project\TpMeCMS\thinkphp\library\think\db添加dm驱动类

修改程序目录下的.env.sample 改成.env文件的配置如下图

[app]
debug = true
trace = true

[database]
type = Dm
hostname = 127.0.0.1
database = XXXX           #数据库名称
username = SYSDBA		  #用户名	
password = XX_456         #用户密码
hostport = 5236           #端口号
prefix = zx_			  #前缀

添加驱动类

TpMeCMS\thinkphp\library\think\db\builder\Dm.php

<?php
// +----------------------------------------------------------------------
// | ThinkPHP [ WE CAN DO IT JUST THINK ]
// +----------------------------------------------------------------------
// | Copyright (c) 2006~2018 http://thinkphp.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: liu21st <liu21st@gmail.com>
// +----------------------------------------------------------------------

namespace think\db\builder;

use think\db\Builder;
use think\db\Expression;
use think\Exception;

/**
 * dm数据库驱动
 */
class Dm extends Builder
{

    protected $insertAllSql = '%INSERT% INTO %TABLE% (%FIELD%) VALUES %DATA% %COMMENT%';
    protected $updateSql    = 'UPDATE %TABLE% %JOIN% SET %SET% %WHERE% %ORDER%%LIMIT% %LOCK%%COMMENT%';

    /**
     * 生成insertall SQL
     * @access public
     * @param array     $dataSet 数据集
     * @param array     $options 表达式
     * @param bool      $replace 是否replace
     * @return string
     * @throws Exception
     */
    public function insertAll($dataSet, $options = [], $replace = false)
    {
        // 获取合法的字段
        if ('*' == $options['field']) {
            $fields = array_keys($this->query->getFieldsType($options['table']));
        } else {
            $fields = $options['field'];
        }

        foreach ($dataSet as $data) {
            foreach ($data as $key => $val) {
                if (!in_array($key, $fields, true)) {
                    if ($options['strict']) {
                        throw new Exception('fields not exists:[' . $key . ']');
                    }
                    unset($data[$key]);
                } elseif (is_null($val)) {
                    $data[$key] = 'NULL';
                } elseif (is_scalar($val)) {
                    $data[$key] = $this->parseValue($val, $key);
                } elseif (is_object($val) && method_exists($val, '__toString')) {
                    // 对象数据写入
                    $data[$key] = $val->__toString();
                } else {
                    // 过滤掉非标量数据
                    unset($data[$key]);
                }
            }
            $value    = array_values($data);
            $values[] = '( ' . implode(',', $value) . ' )';

            if (!isset($insertFields)) {
                $insertFields = array_map([$this, 'parseKey'], array_keys($data));
            }
        }

        return str_replace(
            ['%INSERT%', '%TABLE%', '%FIELD%', '%DATA%', '%COMMENT%'],
            [
                $replace ? 'REPLACE' : 'INSERT',
                $this->parseTable($options['table'], $options),
                implode(' , ', $insertFields),
                implode(' , ', $values),
                $this->parseComment($options['comment']),
            ], $this->insertAllSql);
    }

    /**
     * 字段和表名处理
     * @access protected
     * @param mixed  $key
     * @param array  $options
     * @return string
     */
    protected function parseKey($key, $options = [], $strict = false)
    {
        if (is_numeric($key)) {
            return $key;
        } elseif ($key instanceof Expression) {
            return $key->getValue();
        }

        $key = trim($key);
        if (strpos($key, '$.') && false === strpos($key, '(')) {
            // JSON字段支持
            list($field, $name) = explode('$.', $key);
            return 'json_extract(' . $field . ', \'$.' . $name . '\')';
        } elseif (strpos($key, '.') && !preg_match('/[,\'\"\(\)`\s]/', $key)) {
            list($table, $key) = explode('.', $key, 2);
            if ('__TABLE__' == $table) {
                $table = $this->query->getTable();
            }
            if (isset($options['alias'][$table])) {
                $table = $options['alias'][$table];
            }
        }


        if ($strict && !preg_match('/^[\w\.\*]+$/', $key)) {
            throw new Exception('not support data:' . $key);
        }
        if ('*' != $key && ($strict || !preg_match('/[,\'\"\*\(\)`.\s]/', $key))) {
            $key = '"' . $key . '"';
        }
        if (isset($table)) {
            if (strpos($table, '.')) {
                $table = str_replace('.', '`.`', $table);
            }
            $key = '"'.$table.'"' . '.' . $key;
        }

        if(strpos($key," AS ")!==false)
        {
            $key=str_replace(' AS ','" AS "',$key);
        }

        return $key;
    }

    /**
     * 随机排序
     * @access protected
     * @return string
     */
    protected function parseRand()
    {
        return 'rand()';
    }

    protected function parseOrder($order, $options = [])
    {
        if (empty($order)) {
            return '';
        }

        $array = [];
        foreach ($order as $key => $val) {
            if ($val instanceof Expression) {

                $array[] = $val->getValue();
            } elseif ('[rand]' == $val) {
                $array[] = $this->parseRand();
            } else {
                if (is_numeric($key)) {
                    list($key, $sort) = explode(' ', strpos($val, ' ') ? $val : $val . ' ');
                } else {

                    $sort = $val;
                }
                $sort    = strtoupper($sort);
                $sort    = in_array($sort, ['ASC', 'DESC'], true) ? ' ' . $sort : '';

                $array[] = $this->parseKey($key, $options, true) . $sort;
            }
        }
        $order = implode(',', $array);

        return !empty($order) ? ' ORDER BY ' . $order : '';
    }

    /**
     * 适配join查询
     * @param array $join
     * @param array $options
     * @return string
     */
    protected function parseJoin($join, &$options = [])
    {

        if (!empty($join)) {
            foreach ($join as &$item) {
                list($table, $type, $on) = $item;

                $tempOn = explode('=', $on);
                $tempOn = array_map('trim', $tempOn);
                $tempOn = array_map(function ($item){
                    if (strstr($item, '.')) {
                        $temp = explode('.', $item, 2);
                        return  '"' . $temp[0] . '"' . '.' . '"' . $temp[1] . '"';
                    }
                    return $item;
                }, $tempOn);
                $on = implode(' = ', $tempOn);
                $item = [$table, $type, $on];
            }
        }

        if($options["group"]&&strpos($options["group"],"\"")!=0)
        {
            $options["group"]='"'.$options["group"].'"';
        }

        return parent::parseJoin($join, $options); // TODO: Change the autogenerated stub
    }


    /**
     * field分析
     * @access protected
     * @param mixed     $fields
     * @param array     $options
     * @return string
     */
    protected function parseField($fields, $options = [])
    {
        
        if ('*' == $fields || empty($fields)) {
            $fieldsStr = '*';
        } elseif (is_array($fields)) {
            // 支持 'field1'=>'field2' 这样的字段别名定义
            $array = [];
            foreach ($fields as $key => $field) {
                if ($field instanceof Expression) {
                    $array[] = $this->handleAggregateSql($field->getValue());
                } elseif (!is_numeric($key)) {
                    $array[] = $this->parseKey($key, $options) . ' AS ' . $this->parseKey($field, $options, true);
                } else {
                    if (strstr($field, '.')) {
                        $temp = explode('.', $field, 2);
                        $field =  '"' . $temp[0] . '"' . '.' . '"' . $temp[1] . '"';
                    }

                    $array[] = $this->parseKey($field, $options);
                }
            }
            $fieldsStr = implode(',', $array);
        }
        return $fieldsStr;
    }

    /**
     * 适配聚合查询
     */
    public function handleAggregateSql($sql)
    {
        $sql = preg_replace_callback('/SUM\(([a-zA-z]+?)\)/', function ($item) {
            return str_replace($item[1], '"' . $item[1] . '"', $item[0]);
        }, $sql);
        return $sql;
    }

    protected function parseWhere($where, $options)
    {
        $whereStr = parent::parseWhere($where, $options); // TODO: Change the autogenerated stub

        $whereStr = preg_replace_callback('/([a-zA-z]*)\.([a-zA-z]*)/', function ($items) {
            $sql = array_shift($items);
            foreach ($items as $value) {
                $sql = str_replace($value, '"' . $value . '"', $sql);
            }
            return $sql;
        }, $whereStr);
        
        return $whereStr;
    }
}

TpMeCMS\thinkphp\library\think\db\connector\Dm.php

<?php
// +----------------------------------------------------------------------
// | ThinkPHP [ WE CAN DO IT JUST THINK ]
// +----------------------------------------------------------------------
// | Copyright (c) 2006~2018 http://thinkphp.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: liu21st <liu21st@gmail.com>
// +----------------------------------------------------------------------

namespace think\db\connector;

use PDO;
use think\db\Connection;
use think\Log;

/**
 * DM数据库驱动
 */
class Dm extends Connection
{

    // 字段属性大小写
    protected $attrCase = PDO::CASE_LOWER;

    protected $builder = '\\think\\db\\builder\\Dm';

    /**
     * 解析pdo连接的dsn信息
     * @access protected
     * @param array $config 连接信息
     * @return string
     */
    protected function parseDsn($config)
    {
        if (!empty($config['socket'])) {
            $dsn = 'dm:unix_socket=' . $config['socket'];
        } elseif (!empty($config['hostport'])) {
            $dsn = 'dm:host=' . $config['hostname'] . ';port=' . $config['hostport'];
        } else {
            $dsn = 'dm:host=' . $config['hostname'];
        }
        $dsn .= ';dbname=' . $config['database'];

        if (!empty($config['charset'])) {
            $dsn .= ';charset=' . $config['charset'];
        }
        return $dsn;
    }

    /**
     * 取得数据表的字段信息
     * @access public
     * @param string $tableName
     * @return array
     */
    public function getFields($tableName)
    {
        list($tableName) = explode(' ', $tableName);
        if (false === strpos($tableName, '`')) {
            if (strpos($tableName, '.')) {
                $tableName = str_replace('.', '`.`', $tableName);
            }
            $tableName = "'" . $tableName . "'";
        }


        $sql    = "select * from user_tab_columns where TABLE_NAME = {$tableName}";
        $pdo    = $this->query($sql, [], false, true);
        $result = $pdo->fetchAll(PDO::FETCH_ASSOC);
        $info   = [];
        if ($result) {
            foreach ($result as $key => $val) {
                $val                 = array_change_key_case($val);
                $info[$val['column_name']] = [
                    'name'    => $val['column_name'],
                    'type'    => $val['data_type'],
                    'notnull' => (bool) ('Y' === $val['nullable']), // not null is empty, null is yes
                    'default' => $val['data_default'],
                    'primary' => $val['column_name'] == 'id',
                    'autoinc' => false,
                ];
            }
        }
        return $this->fieldCase($info);
    }

    /**
     * 取得数据库的表信息
     * @access public
     * @param string $dbName
     * @return array
     */
    public function getTables($dbName = '')
    {
        $sql    = !empty($dbName) ? 'SHOW TABLES FROM ' . $dbName : 'SHOW TABLES ';
        $pdo    = $this->query($sql, [], false, true);
        $result = $pdo->fetchAll(PDO::FETCH_ASSOC);
        $info   = [];
        foreach ($result as $key => $val) {
            $info[$key] = current($val);
        }
        return $info;
    }

    /**
     * SQL性能分析
     * @access protected
     * @param string $sql
     * @return array
     */
    protected function getExplain($sql)
    {
        $pdo    = $this->linkID->query("EXPLAIN " . $sql);
        $result = $pdo->fetch(PDO::FETCH_ASSOC);
        $result = array_change_key_case($result);
        if (isset($result['extra'])) {
            if (strpos($result['extra'], 'filesort') || strpos($result['extra'], 'temporary')) {
                Log::record('SQL:' . $this->queryStr . '[' . $result['extra'] . ']', 'warn');
            }
        }
        return $result;
    }

    protected function supportSavepoint()
    {
        return true;
    }

}

如果上述出现中文乱码请在C:\Windows\System32中dm_svc.conf文件中

添加CHAR_CODE=(PG_UTF8)

TIME_ZONE=(480)
LANGUAGE=(CN)
CHAR_CODE=(PG_UTF8)

---