fastadmin对接达梦数据库

一、cms插件适配

1./addons/cms/library/Service.php

public static function getTableFields($table)
    {
        $tagName = "cms-table-fields-{$table}";
        $fieldlist = Cache::get($tagName);
        if (!Config::get('app_debug') && $fieldlist) {
            return $fieldlist;
        }
        $dbname = Config::get('database.database');
        //从数据库中获取表字段信息
        $sql = "SELECT * FROM `information_schema`.`columns` WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION";

        //加载主表的列
        $columnList = Db::query($sql, [$dbname, $table]);
        $fieldlist = [];
        foreach ($columnList as $index => $item) {
            $fieldlist[] = ['name' => $index, 'title' => $item['COLUMN_COMMENT'], 'type' => $item['DATA_TYPE']];
        }
        Cache::set($tagName, $fieldlist);
        return $fieldlist;
    }

改为

public static function getTableFields($table)
    {
        $tagName = "cms-table-fields-{$table}";
        $fieldlist = Cache::get($tagName);
        if (!Config::get('app_debug') && $fieldlist) {
            return $fieldlist;
        }
        $dbname = Config::get('database.database');
        $sql             = "SELECT 
  a.column_name,
  a.data_type,
  DECODE(a.nullable, 'Y', 0, 1) notnull,
  a.data_default,
  DECODE(a.column_name, b.column_name, 1, 0) pk,
  c.comments AS column_comments
FROM 
  all_tab_columns a, 
  (
  SELECT column_name 
    FROM all_constraints c, all_cons_columns col 
    WHERE c.constraint_name = col.constraint_name
    AND c.constraint_type = 'P'
    AND c.table_name = '" . strtoupper($table) . "'
  ) b,
  all_col_comments c
WHERE 
  a.table_name =  '" . strtoupper($table) . "'
  AND a.column_name = b.column_name(+)
  AND a.table_name = c.table_name
  AND a.column_name = c.column_name";
        $pdo    = Db::query($sql, [], false, true);
        $columnList = $pdo->fetchAll(PDO::FETCH_ASSOC);
       $fieldlist = [];
        foreach ($columnList as $index => $item) {
            $fieldlist[] = ['name' => $item['column_name'], 'title' => $item['data_type'], 'type' => $item['column_comments']];
        }
        Cache::set($tagName, $fieldlist);
        return $fieldlist;
    }

2.\addons\cms\controller\Channel::index中

 $pageList = Archives::with(['channel', 'user'])->alias('a')
            ->where('a.status', 'normal')
            ->whereNull('a.deletetime')
            ->where($filterWhere)
            ->bind($filterBind)
            ->where($filterPagelist)
            ->where($filterChannel)
            ->where('model_id', $channel->model_id)
            ->join($model['table'] . ' n', 'a.id=n.id', 'LEFT')
            //->join($join_model, 'a.id=n.id', 'LEFT')
            ->field('a.*')
            ->field('id,content', true, config('database.prefix') . $model['table'], 'n')
            ->order($orderby, $orderway)
            ->paginate($pagesize, $simple);

这部分的join获取不到表别名。explode导致的连续空格问题

在这个文件中\think\db\Query::getJoinTable

list($table, $alias) = explode(' ', $join);

这一句追加为

$arr=explode(' ', $join);
                    $arr = array_filter($arr);
                    $new_arr=[];
                    foreach ($arr as $k => $v) {
                        $new_arr[]=$v;
                    }
                    // 使用别名
                    list($table, $alias) = $new_arr;

3.同2方法中，未指定channel_ids的表别名

$filterChannel = function ($query) use ($channel) {
            $query->where(function ($query) use ($channel) {
                if ($channel['listtype'] <= 2) {
                    $query->whereOr("channel_id", $channel['id']);
                }
                if ($channel['listtype'] == 1 || $channel['listtype'] == 3) {
                    $query->whereOr('channel_id', 'in', function ($query) use ($channel) {
                        $query->name("cms_channel")->where('parent_id', $channel['id'])->field("id");
                    });
                }
                if ($channel['listtype'] == 0 || $channel['listtype'] == 4) {
                    $childrenIds = \addons\cms\model\Channel::getChannelChildrenIds($channel['id'], false);
                    if ($childrenIds) {
                        $query->whereOr('channel_id', 'in', $childrenIds);
                    }
                }
            })
                ->whereOr("(`channel_ids`!='' AND FIND_IN_SET('{$channel['id']}', `channel_ids`))");
        };

改为

$filterChannel = function ($query) use ($channel) {
            $query->where(function ($query) use ($channel) {
                if ($channel['listtype'] <= 2) {
                    $query->whereOr("channel_id", $channel['id']);
                }
                if ($channel['listtype'] == 1 || $channel['listtype'] == 3) {
                    $query->whereOr('channel_id', 'in', function ($query) use ($channel) {
                        $query->name("cms_channel")->where('parent_id', $channel['id'])->field("id");
                    });
                }
                if ($channel['listtype'] == 0 || $channel['listtype'] == 4) {
                    $childrenIds = \addons\cms\model\Channel::getChannelChildrenIds($channel['id'], false);
                    if ($childrenIds) {
                        $query->whereOr('channel_id', 'in', $childrenIds);
                    }
                }
            })
                ->whereOr("(a.channel_ids!='' AND FIND_IN_SET('{$channel['id']}', a.channel_ids))");
        };

4.同上方法中

$template = ($this->request->isAjax() ? '/ajax/' : '/') . ($channel["{$channel['type']}tpl"] ?? '');

改为

$chatype=trim($channel['type']);
        $template = ($this->request->isAjax() ? '/ajax/' : '/') . ($channel["{$chatype}tpl"] ?? '');

5.没找到文章

\addons\cms\controller\Archives::index

if (!$archives || ($archives['status'] != 'normal' && (!$archives['user_id'] || $archives['user_id'] != $this->auth->id)) || $archives['deletetime']) {

改为

$arcs=trim($archives['status']);
        if (!$archives || ($arcs != 'normal' && (!$archives['user_id'] || $archives['user_id'] != $this->auth->id)) || $archives['deletetime']) {

6.统计控制台中

\app\admin\controller\cms\Statistics::getArchivesStatisticsData

 $format = '%Y-%m-%d';
        if ($totalseconds > 86400 * 30 * 2) {
            $format = '%Y-%m';
        } else {
            if ($totalseconds > 86400) {
                $format = '%Y-%m-%d';
            } else {
                $format = '%H:00';
            }
        }
        $model_id = $this->request->post("model_id", "");
        $archivesList = \app\admin\model\cms\Archives::with(["admin"])
            ->where('createtime', 'between time', [$starttime, $endtime])
            ->where(function ($query) use ($model_id) {
                if ($model_id) {
                    $query->where('model_id', $model_id);
                }
            })
            ->field('admin_id, createtime, status, COUNT(*) AS nums, MIN(createtime) AS min_createtime, MAX(createtime) AS max_createtime, 
            DATE_FORMAT(FROM_UNIXTIME(createtime), "' . $format . '") AS create_date')
            ->group('admin_id,create_date')
            ->select();

改为

 $format = '%Y-%m-%d';
        $format = 'YYYY-MM-DD';
        if ($totalseconds > 86400 * 30 * 2) {
            $format = '%Y-%m';
            $format = 'YYYY-MM';
        } else {
            if ($totalseconds > 86400) {
                $format = '%Y-%m-%d';
                $format = 'YYYY-MM-DD';
            } else {
                $format = '%H:00';
                $format = 'HH24:00';
            }
        }
        $model_id = $this->request->post("model_id", "");
        $archivesList = \app\admin\model\cms\Archives::with(["admin"])
            ->where('createtime', 'between time', [$starttime, $endtime])
            ->where(function ($query) use ($model_id) {
                if ($model_id) {
                    $query->where('model_id', $model_id);
                }
            })
            /*->field('admin_id, createtime, status, COUNT(*) AS nums, MIN(createtime) AS min_createtime, MAX(createtime) AS max_createtime,
            DATE_FORMAT(FROM_UNIXTIME(createtime), "' . $format . '") AS create_date')*/
            ->field("admin_id, createtime, status, COUNT(*) AS nums, MIN(createtime) AS min_createtime, MAX(createtime) AS max_createtime, 
            TO_CHAR(TO_TIMESTAMP(createtime), '" . $format . "') AS create_date")
            ->group('admin_id,create_date')
            ->select();

本类中其余时间有关问题类似

7.\app\admin\controller\cms\Statistics::index

$todayPaidList = \app\admin\model\cms\Order::with(['archives'])->whereTime('paytime', 'today')->group('archives_id')->field("COUNT(*) as nums,SUM(payamount) as amount,archives_id")->order("amount", "desc")->limit(10)->select();

改为

$todayPaidList = \app\admin\model\cms\Order::with(['archives'])->whereTime('paytime', 'today')->group('archives_id')->field("COUNT(*) as nums,SUM(payamount) as amount,archives_id")->order("order1.amount", "desc")->limit(10)->select();

之前改过关键词的，把图中部分可以都改了

8.\app\admin\controller\cms\Channel::edit

->whereOr('FIND_IN_SET(:id, `channel_ids`)', ['id' => $channel['id']])

改为

->whereOr('INSTR(',' + channel_ids + ',', ',' + :id + ',')', ['id' => $channel['id']])

二、后台

1.\app\admin\controller\Dashboard::index

$dbTableList = Db::query("SHOW TABLE STATUS");

改为

$dbTableList = Db::query("SELECT * FROM USER_TABLES");

 $this->view->assign([
            'totaluser'         => User::count(),
            'totaladdon'        => $totaladdon,
            'totaladmin'        => Admin::count(),
            'totalcategory'     => \app\common\model\Category::count(),
            'todayusersignup'   => User::whereTime('jointime', 'today')->count(),
            'todayuserlogin'    => User::whereTime('logintime', 'today')->count(),
            'sevendau'          => User::whereTime('jointime|logintime|prevtime', '-7 days')->count(),
            'thirtydau'         => User::whereTime('jointime|logintime|prevtime', '-30 days')->count(),
            'threednu'          => User::whereTime('jointime', '-3 days')->count(),
            'sevendnu'          => User::whereTime('jointime', '-7 days')->count(),
            'dbtablenums'       => count($dbTableList),
            'dbsize'            => array_sum(array_map(function ($item) {
                return $item['Data_length'] + $item['Index_length'];
            }, $dbTableList)),
            'totalworkingaddon' => $totalworkingaddon,
            'attachmentnums'    => Attachment::count(),
            'attachmentsize'    => Attachment::sum('filesize'),
            'picturenums'       => Attachment::where('mimetype', 'like', 'image/%')->count(),
            'picturesize'       => Attachment::where('mimetype', 'like', 'image/%')->sum('filesize'),
        ]);

改为

$dbTableListInfo = Db::query("SELECT T.OWNER,
         T.SEGMENT_NAME,
         T.SEGMENT_TYPE,
         T.TABLESPACE_NAME,
         T.BYTES,
         T.BYTES/1024 BYTE_KB,
         T.BYTES/1024/1024 BYTE_MB
    FROM DBA_SEGMENTS T
   WHERE T.OWNER = 'CHANJIREN'  AND  -- 用户/模式名
         T.SEGMENT_TYPE IN ('TABLE','INDEX')
ORDER BY T.BYTES DESC");
        $dbsize=array_sum(array_map(function ($item) {
            return $item['bytes'];
        }, $dbTableListInfo));
        $this->view->assign([
            'totaluser'         => User::count(),
            'totaladdon'        => $totaladdon,
            'totaladmin'        => Admin::count(),
            'totalcategory'     => \app\common\model\Category::count(),
            'todayusersignup'   => User::whereTime('jointime', 'today')->count(),
            'todayuserlogin'    => User::whereTime('logintime', 'today')->count(),
            'sevendau'          => User::whereTime('jointime|logintime|prevtime', '-7 days')->count(),
            'thirtydau'         => User::whereTime('jointime|logintime|prevtime', '-30 days')->count(),
            'threednu'          => User::whereTime('jointime', '-3 days')->count(),
            'sevendnu'          => User::whereTime('jointime', '-7 days')->count(),
            'dbtablenums'       => count($dbTableList),
            /*'dbsize'            => array_sum(array_map(function ($item) {
                return $item['Data_length'] + $item['Index_length'];
            }, $dbTableList)),*/
            'dbsize'            => $dbsize,
            'totalworkingaddon' => $totalworkingaddon,
            'attachmentnums'    => Attachment::count(),
            'attachmentsize'    => Attachment::sum('filesize'),
            'picturenums'       => Attachment::where('mimetype', 'like', 'image/%')->count(),
            'picturesize'       => Attachment::where('mimetype', 'like', 'image/%')->sum('filesize'),
        ]);

2.有些达梦关键字导致的[current]附近出现错误: 语法分析出错

\think\db\Connection::query

在这句

 $this->initConnect($master);

前加上

if($this->isKeyword($sql)){
            $sql=$this->replaceallKeyword($sql);
            //var_dump($sql);
        }

在这个类里补全方法，相关关键字可以在$keyword变量里加

protected $keyword = ['user','group','current','order','comment','table'];
    public function isKeyword($sql){
        $found = false;
        foreach ($this->keyword as $value) {
            if (strpos($sql, ' '.$value) !== false) {
                $found = true;
                break;
            }
            if (strpos($sql, '.'.$value) !== false) {
                $found = true;
                break;
            }
        }
        return $found;
    }
    public function replaceallKeyword($sql)
    {
        foreach ($this->keyword as $value) {
            if (strpos($sql, $value) !== false) {
                $newvalue=$value."1";
                $sql=str_replace(' '.$value.' ', ' '.$newvalue." ", $sql);
                $sql=str_replace($value.'.', $newvalue.".", $sql);
                $sql=str_replace('.'.$value.' ', '."'.strtoupper($value).'" ', $sql);
            }
        }
        return $sql;
    }

3.avatarUrl替换为avatarurl

遇到的其他问题：
1.SQLSTATE[HY000]: General error: -70005 字符串截断 (dpi_execute[4294897291] at /home/test/yx/trunk/dm_php//pdo7/dm_stmt.c:168)
达梦数据库clob类型提示字符串截断
https://blog.itpub.net/69949798/viewspace-2660602/
原thinkphp框架中使用REPLACE INTO 达梦不支持，我把这句改成MERGE...,。然后就报字符串截断。直接INSERT是没有问题的，当然有问题按上面的配置一下就可以

2.php上传大视频
https://blog.csdn.net/lhkuxia/article/details/129427021

参考链接：

1.https://blog.csdn.net/chenxuan12/article/details/130201958

2.https://eco.dameng.com/community/question/e662aeae41487980a3124087bcfff0cd