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
DECLAREv_type varchar;
BEGINIF a_type='int8' THENv_type:='bigint';ELSIF a_type='int4' THENv_type:='integer';ELSIF a_type='int2' THENv_type:='smallint';ELSIF a_type='bpchar' THENv_type:='char';ELSEv_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
DECLAREv_ret tablestruct;v_oid oid;v_sql varchar;v_rec RECORD;v_key varchar;
BEGINSELECTsys_class.oid  INTO v_oidFROMsys_classINNER JOIN sys_namespace ON (sys_class.relnamespace = sys_namespace.oid AND lower(sys_namespace.nspname) = a_schema_name)WHEREsys_class.relname=a_table_name;IF NOT FOUND THENRETURN;END IF;v_sql='SELECTsys_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_commentFROMsys_attributeINNER JOIN sys_class  ON sys_attribute.attrelid = sys_class.oidINNER JOIN sys_type   ON sys_attribute.atttypid = sys_type.oidLEFT OUTER JOIN sys_attrdef ON sys_attrdef.adrelid = sys_class.oid AND sys_attrdef.adnum = sys_attribute.attnumLEFT OUTER JOIN sys_description ON sys_description.objoid = sys_class.oid AND sys_description.objsubid = sys_attribute.attnumWHEREsys_attribute.attnum > 0AND attisdropped <> ''t''AND sys_class.oid = ' || v_oid || 'ORDER BY sys_attribute.attnum' ;FOR v_rec IN EXECUTE v_sql LOOPv_ret.fields_name=v_rec.fields_name;v_ret.fields_type=v_rec.fields_type;IF v_rec.fields_length > 0 THENv_ret.fields_length:=v_rec.fields_length;ELSEv_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 THENv_ret.fields_key_name=v_key;ELSEv_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
DECLAREv_ret tablestruct;
BEGINFOR v_ret IN SELECT * FROM table_msg('public',a_table_name) LOOPRETURN 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连接配置

<?phpreturn [// 默认使用的数据库连接配置'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,],// 更多的数据库配置信息],
];