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,
],
// 更多的数据库配置信息
],
];
原文地址：https://blog.csdn.net/admin_web/article/details/140518848
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！