thinkphp6连接kingbase数据库

在/vendor/topthink/think-orm/src/db/connector中将Pgsql.php和pgsql.sql文件复制后改名为Kingbase.php和Kingbase.sql

Kingbase.php

<?php
// +----------------------------------------------------------------------
// | ThinkPHP [ WE CAN DO IT JUST THINK ]
// +----------------------------------------------------------------------
// | Copyright (c) 2006~2019 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\PDOConnection;

/**
 * Kingbase数据库驱动
 */
class Kingbase extends PDOConnection
{

    /**
     * 默认PDO连接参数
     * @var array
     */
    protected $params = [
        PDO::ATTR_CASE              => PDO::CASE_NATURAL,
        PDO::ATTR_ERRMODE           => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
        PDO::ATTR_STRINGIFY_FETCHES => false,
    ];

    /**
     * 解析pdo连接的dsn信息
     * @access protected
     * @param  array $config 连接信息
     * @return string
     */
    protected function parseDsn(array $config): string
    {
        $dsn = 'kdb:host=' . $config['hostname'] . ';dbname=' . $config['database'];

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

        return $dsn;
    }

    /**
     * 取得数据表的字段信息
     * @access public
     * @param  string $tableName
     * @return array
     */
    public function getFields(string $tableName): array
    {
        [$tableName] = explode(' ', $tableName);
        // $sql         = 'select fields_name as "field",fields_type as "type",fields_not_null as "null",fields_key_name as "key",fields_default as "default",fields_default as "extra" from table_msg(\'' . $tableName . '\');';
        $sql    = 'SELECT a.attname AS "field", format_type(a.atttypid, a.atttypmod) AS "type", NOT a.attnotnull AS "null", COALESCE(conname, \'\') AS "key", pg_get_expr(adbin, adrelid) AS "default", \'\' AS "extra" FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid LEFT JOIN (SELECT conname, conrelid, unnest(conkey) AS attnum FROM pg_constraint WHERE contype = \'p\') pk ON pk.conrelid = a.attrelid AND pk.attnum = a.attnum LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum WHERE c.relname = \'' . $tableName . '\'  AND a.attnum > 0 ORDER BY a.attnum;';

        $pdo    = $this->getPDOStatement($sql);
        $result = $pdo->fetchAll(PDO::FETCH_ASSOC);
        $info   = [];

        if (!empty($result)) {
            foreach ($result as $key => $val) {
                $val = array_change_key_case($val);

                $info[$val['field']] = [
                    'name'    => $val['field'],
                    'type'    => $val['type'],
                    'notnull' => (bool) ('' !== $val['null']),
                    'default' => $val['default'],
                    'primary' => !empty($val['key']),
                    'autoinc' => (0 === strpos($val['extra'], 'nextval(')),
                ];
            }
        }

        return $this->fieldCase($info);
    }

    /**
     * 取得数据库的表信息
     * @access public
     * @param  string $dbName
     * @return array
     */
    public function getTables(string $dbName = ''): array
    {
        $sql    = "select tablename as Tables_in_test from pg_tables where  schemaname ='public'";
        $pdo    = $this->getPDOStatement($sql);
        $result = $pdo->fetchAll(PDO::FETCH_ASSOC);
        $info   = [];

        foreach ($result as $key => $val) {
            $info[$key] = current($val);
        }

        return $info;
    }

    protected function supportSavepoint(): bool
    {
        return true;
    }
}

Kingbase.sql

CREATE OR REPLACE FUNCTION pgsql_type(a_type varchar) RETURNS varchar AS
DECLARE
     v_type varchar;
BEGIN
     IF a_type='int8' THEN
          v_type:='bigint';
     ELSIF a_type='int4' THEN
          v_type:='integer';
     ELSIF a_type='int2' THEN
          v_type:='smallint';
     ELSIF a_type='bpchar' THEN
          v_type:='char';
     ELSE
          v_type:=a_type;
     END IF;
     RETURN v_type;
END;

CREATE TYPE "public"."tablestruct" AS (
  "fields_key_name" varchar(100),
  "fields_name" VARCHAR(200),
  "fields_type" VARCHAR(20),
  "fields_length" BIGINT,
  "fields_not_null" VARCHAR(10),
  "fields_default" VARCHAR(500),
  "fields_comment" VARCHAR(1000)
);

CREATE OR REPLACE FUNCTION "public"."table_msg" (a_schema_name varchar, a_table_name varchar) RETURNS SETOF "public"."tablestruct" AS
DECLARE
     v_ret tablestruct;
     v_oid oid;
     v_sql varchar;
     v_rec RECORD;
     v_key varchar;
BEGIN
     SELECT
           sys_class.oid  INTO v_oid
     FROM
           sys_class
           INNER JOIN sys_namespace ON (sys_class.relnamespace = sys_namespace.oid AND lower(sys_namespace.nspname) = a_schema_name)
     WHERE
           sys_class.relname=a_table_name;
     IF NOT FOUND THEN
         RETURN;
     END IF;

     v_sql='
     SELECT
           sys_attribute.attname AS fields_name,
           sys_attribute.attnum AS fields_index,
           pgsql_type(sys_type.typname::varchar) AS fields_type,
           sys_attribute.atttypmod-4 as fields_length,
           CASE WHEN sys_attribute.attnotnull  THEN ''not null''
           ELSE ''''
           END AS fields_not_null,
           sys_attrdef.adsrc AS fields_default,
           sys_description.description AS fields_comment
     FROM
           sys_attribute
           INNER JOIN sys_class  ON sys_attribute.attrelid = sys_class.oid
           INNER JOIN sys_type   ON sys_attribute.atttypid = sys_type.oid
           LEFT OUTER JOIN sys_attrdef ON sys_attrdef.adrelid = sys_class.oid AND sys_attrdef.adnum = sys_attribute.attnum
           LEFT OUTER JOIN sys_description ON sys_description.objoid = sys_class.oid AND sys_description.objsubid = sys_attribute.attnum
     WHERE
           sys_attribute.attnum > 0
           AND attisdropped <> ''t''
           AND sys_class.oid = ' || v_oid || '
     ORDER BY sys_attribute.attnum' ;

     FOR v_rec IN EXECUTE v_sql LOOP
         v_ret.fields_name=v_rec.fields_name;
         v_ret.fields_type=v_rec.fields_type;
         IF v_rec.fields_length > 0 THEN
            v_ret.fields_length:=v_rec.fields_length;
         ELSE
            v_ret.fields_length:=NULL;
         END IF;
         v_ret.fields_not_null=v_rec.fields_not_null;
         v_ret.fields_default=v_rec.fields_default;
         v_ret.fields_comment=v_rec.fields_comment;
         SELECT constraint_name INTO v_key FROM information_schema.key_column_usage WHERE table_schema=a_schema_name AND table_name=a_table_name AND column_name=v_rec.fields_name;
         IF FOUND THEN
            v_ret.fields_key_name=v_key;
         ELSE
            v_ret.fields_key_name='';
         END IF;
         RETURN NEXT v_ret;
     END LOOP;
     RETURN ;
END;

COMMENT ON FUNCTION "public"."table_msg"(a_schema_name varchar, a_table_name varchar)
IS '获得表信息';

---重载一个函数
CREATE OR REPLACE FUNCTION "public"."table_msg" (a_table_name varchar) RETURNS SETOF "public"."tablestruct" AS
DECLARE
    v_ret tablestruct;
BEGIN
    FOR v_ret IN SELECT * FROM table_msg('public',a_table_name) LOOP
        RETURN NEXT v_ret;
    END LOOP;
    RETURN;
END;

COMMENT ON FUNCTION "public"."table_msg"(a_table_name varchar)
IS '获得表信息';

复制/vendor/topthink/think-orm/src/db/builder中的Pgsql.php改名为Kingbase.php

Kingbase.php

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

namespace think\db\builder;

use think\db\Builder;
use think\db\Query;
use think\db\Raw;

/**
 * Kingbase数据库驱动
 */
class Kingbase extends Builder
{
    /**
     * INSERT SQL表达式
     * @var string
     */
    protected $insertSql = 'INSERT INTO %TABLE% (%FIELD%) VALUES (%DATA%) %COMMENT%';

    /**
     * INSERT ALL SQL表达式
     * @var string
     */
    protected $insertAllSql = 'INSERT INTO %TABLE% (%FIELD%) %DATA% %COMMENT%';

    /**
     * limit分析
     * @access protected
     * @param  Query     $query        查询对象
     * @param  mixed     $limit
     * @return string
     */
    public function parseLimit(Query $query, string $limit): string
    {
        $limitStr = '';

        if (!empty($limit)) {
            $limit = explode(',', $limit);
            if (count($limit) > 1) {
                $limitStr .= ' LIMIT ' . $limit[1] . ' OFFSET ' . $limit[0] . ' ';
            } else {
                $limitStr .= ' LIMIT ' . $limit[0] . ' ';
            }
        }

        return $limitStr;
    }

    /**
     * 字段和表名处理
     * @access public
     * @param  Query     $query     查询对象
     * @param  mixed     $key       字段名
     * @param  bool      $strict   严格检测
     * @return string
     */
    public function parseKey(Query $query, $key, bool $strict = false): string
    {
        if (is_int($key)) {
            return (string) $key;
        } elseif ($key instanceof Raw) {
            return $this->parseRaw($query, $key);
        }

        $key = trim($key);

        if (strpos($key, '->') && false === strpos($key, '(')) {
            // JSON字段支持
            [$field, $name] = explode('->', $key);
            $key            = '"' . $field . '"' . '->>\'' . $name . '\'';
        } elseif (strpos($key, '.')) {
            [$table, $key] = explode('.', $key, 2);

            $alias = $query->getOptions('alias');

            if ('__TABLE__' == $table) {
                $table = $query->getOptions('table');
                $table = is_array($table) ? array_shift($table) : $table;
            }

            if (isset($alias[$table])) {
                $table = $alias[$table];
            }

            if ('*' != $key && !preg_match('/[,\"\*\(\).\s]/', $key)) {
                $key = '"' . $key . '"';
            }
        }

        if (isset($table)) {
            $key = $table . '.' . $key;
        }

        return $key;
    }

    /**
     * 随机排序
     * @access protected
     * @param  Query     $query        查询对象
     * @return string
     */
    protected function parseRand(Query $query): string
    {
        return 'RANDOM()';
    }

}

config/database.php连接配置

<?php

return [
    // 默认使用的数据库连接配置
    'default'         => env('database.driver', 'kingbase'),

    // 自定义时间查询规则
    'time_query_rule' => [],

    // 自动写入时间戳字段
    // true为自动识别类型 false关闭
    // 字符串则明确指定时间字段类型 支持 int timestamp datetime date
    'auto_timestamp'  => true,

    // 时间字段取出后的默认时间格式
    'datetime_format' => 'Y-m-d H:i:s',

    // 时间字段配置 配置格式：create_time,update_time
    'datetime_field'  => '',

    // 自定义配置 - kingbase数据库配置模式
    'db_modal'        => 'ya_jz.',

    // 数据库连接配置信息
    'connections'     => [
        'kingbase' => [
            // 数据库类型
            'type'            => env('database.type', 'kingbase'),
            // 服务器地址
            'hostname'        => env('database.hostname', '192.168.1.16'),
            // 数据库名
            'database'        => env('database.database', 'testkingbase'),
            // 用户名
            'username'        => env('database.username', 'system'),
            // 密码
            'password'        => env('database.password', '123456'),
            // 端口
            'hostport'        => env('database.hostport', '54321'),
            // 数据库连接参数
            'params'          => [],
            // 数据库编码默认采用utf8
            'charset'         => env('database.charset', 'utf8'),
            // 数据库表前缀
            'prefix'          => env('database.prefix', 'ya_jz.tm_'), // 设置连接模式为ya_jz，默认public模式,不需要设置

            // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
            'deploy'          => 0,
            // 数据库读写是否分离 主从式有效
            'rw_separate'     => false,
            // 读写分离后 主服务器数量
            'master_num'      => 1,
            // 指定从服务器序号
            'slave_no'        => '',
            // 是否严格检查字段是否存在
            'fields_strict'   => true,
            // 是否需要断线重连
            'break_reconnect' => false,
            // 监听SQL
            'trigger_sql'     => env('app_debug', false),
            // 开启字段缓存
            'fields_cache'    => false,
        ],
        'mysql_2' => [
            // 数据库类型
            'type'            => env('mysql_2.type', 'mysql'),
            // 服务器地址
            'hostname'        => env('mysql_2.hostname', '192.168.1.66'),
            // 数据库名
            'database'        => env('mysql_2.database', 'mzjz01_xx'),
            // 用户名
            'username'        => env('mysql_2.username', 'mzt'),
            // 密码
            'password'        => env('mysql_2.password', 'mzt01'),
            // 端口
            'hostport'        => env('mysql_2.hostport', '3306'),
            // 数据库连接参数
            'params'          => [],
            // 数据库编码默认采用utf8
            'charset'         => env('mysql_2.charset', 'utf8'),
            // 数据库表前缀
            'prefix'          => env('mysql_2.prefix', 'tm_'),

            // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
            'deploy'          => 0,
            // 数据库读写是否分离 主从式有效
            'rw_separate'     => false,
            // 读写分离后 主服务器数量
            'master_num'      => 1,
            // 指定从服务器序号
            'slave_no'        => '',
            // 是否严格检查字段是否存在
            'fields_strict'   => true,
            // 是否需要断线重连
            'break_reconnect' => false,
            // 监听SQL
            'trigger_sql'     => env('app_debug', false),
            // 开启字段缓存
            'fields_cache'    => false,
        ],

        // 更多的数据库配置信息
    ],
];