赞
踩
先感谢思路
https://weiku.co/article/37/
需要修改thinkphp里面sqlserver数据库驱动,不要去改composer里面的东西。所以只能自己重写驱动,并且在database.php配置文件中,显示指定builder和type参数来完成非侵入式的修改。
'sqlserver' => [ // 这两个配置需要指定对应的文件 'builder' => '\app\common\driver\builder\FunSqlsrv', 'type' => '\app\common\driver\connector\FunSqlsrv', //下面配置都是正常写 // 'type' => env('yifei_database.type', 'sqlsrv'), // 服务器地址 'hostname' => env('yifei_database.hostname', ''), // 数据库名 //不同账套不一样,需要配置一下 'database' => env('yifei_database.database', ''), // 数据库用户名 'username' => env('yifei_database.username', ''), // 数据库密码 'password' => env('yifei_database.password', ''), // 数据库连接端口 'hostport' => env('yifei_database.hostport', '1433'), // 数据库连接参数 'params' => [], // 数据库编码默认采用utf8 'charset' => env('yifei_database.charset', 'utf8'), // 数据库表前缀 'prefix' => '', // 增加证书信任 'trust_server_certificate'=>true, ],
2个核心文件代码
FunSqlsrv.php
<?php namespace app\common\driver\builder; use think\db\Builder; use think\db\exception\DbException as Exception; use think\db\Query; use think\db\Raw; /** * Sqlsrv数据库驱动 */ class FunSqlsrv extends Builder { /** * SELECT SQL表达式 * @var string */ protected $selectSql = 'SELECT T1.* FROM (SELECT thinkphp.*, ROW_NUMBER() OVER (%ORDER%) AS ROW_NUMBER FROM (SELECT %DISTINCT% %FIELD% FROM %TABLE%%JOIN%%WHERE%%GROUP%%HAVING%) AS thinkphp) AS T1 %LIMIT%%COMMENT%'; /** * SELECT INSERT SQL表达式 * @var string */ protected $selectInsertSql = 'SELECT %DISTINCT% %FIELD% FROM %TABLE%%JOIN%%WHERE%%GROUP%%HAVING%'; /** * UPDATE SQL表达式 * @var string */ protected $updateSql = 'UPDATE %TABLE% SET %SET% FROM %TABLE% %JOIN% %WHERE% %LIMIT% %LOCK%%COMMENT%'; /** * DELETE SQL表达式 * @var string */ protected $deleteSql = 'DELETE FROM %TABLE% %USING% FROM %TABLE% %JOIN% %WHERE% %LIMIT% %LOCK%%COMMENT%'; /** * 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%'; /** * order分析 * @access protected * @param Query $query 查询对象 * @param mixed $order * @return string */ protected function parseOrder(Query $query, array $order): string { if (empty($order)) { return ' ORDER BY rand()'; } $array = []; foreach ($order as $key => $val) { if ($val instanceof Raw) { $array[] = $this->parseRaw($query, $val); } elseif ('[rand]' == $val) { $array[] = $this->parseRand($query); } else { if (is_numeric($key)) { [$key, $sort] = explode(' ', strpos($val, ' ') ? $val : $val . ' '); } else { $sort = $val; } $sort = in_array(strtolower($sort), ['asc', 'desc'], true) ? ' ' . $sort : ''; $array[] = $this->parseKey($query, $key, true) . $sort; } } return ' ORDER BY ' . implode(',', $array); } /** * 随机排序 * @access protected * @param Query $query 查询对象 * @return string */ protected function parseRand(Query $query): string { return 'rand()'; } /** * 字段和表名处理 * @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, '.') && !preg_match('/[,\'\"\(\)\[\s]/', $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 ($strict && !preg_match('/^[\w\.\*]+$/', $key)) { throw new Exception('not support data:' . $key); } if ('*' != $key && !preg_match('/[,\'\"\*\(\)\[.\s]/', $key)) { $key = '[' . $key . ']'; } if (isset($table)) { $key = '[' . $table . '].' . $key; } return $key; } /** * limit * @access protected * @param Query $query 查询对象 * @param mixed $limit * @return string */ protected function parseLimit(Query $query, string $limit): string { if (empty($limit)) { return ''; } $limit = explode(',', $limit); if (count($limit) > 1) { $limitStr = '(T1.ROW_NUMBER BETWEEN ' . $limit[0] . ' + 1 AND ' . $limit[0] . ' + ' . $limit[1] . ')'; } else { $limitStr = '(T1.ROW_NUMBER BETWEEN 1 AND ' . $limit[0] . ")"; } return 'WHERE ' . $limitStr; } public function selectInsert(Query $query, array $fields, string $table): string { $this->selectSql = $this->selectInsertSql; return parent::selectInsert($query, $fields, $table); } }
FunSqlsrv.php
<?php // +---------------------------------------------------------------------- // | ThinkPHP [ WE CAN DO IT JUST THINK IT ] // +---------------------------------------------------------------------- // | Copyright (c) 2006-2012 http://thinkphp.cn All rights reserved. // +---------------------------------------------------------------------- // | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 ) // +---------------------------------------------------------------------- // | Author: liu21st <liu21st@gmail.com> // +---------------------------------------------------------------------- namespace app\common\driver\connector; use PDO; use think\db\PDOConnection; /** * Sqlsrv数据库驱动 魔改版 */ class FunSqlsrv 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, ]; /** * 获取当前连接器类对应的Builder类 * @access public * @return string */ //public function getBuilderClass(): string //{ // return '\\app\\common\\driver\\builder\\FunSqlsrv'; //} /** * 解析pdo连接的dsn信息 * @access protected * @param array $config 连接信息 * @return string */ protected function parseDsn(array $config): string { $dsn = 'sqlsrv:Database=' . $config['database'] . ';Server=' . $config['hostname']; if (!empty($config['hostport'])) { $dsn .= ',' . $config['hostport']; } if (!empty($config['trust_server_certificate'])) { $dsn .= ';TrustServerCertificate=' . $config['trust_server_certificate']; } return $dsn; } /** * 取得数据表的字段信息 * @access public * @param string $tableName * @return array */ public function getFields(string $tableName): array { [$tableName] = explode(' ', $tableName); strpos($tableName, '.') && $tableName = substr($tableName, strpos($tableName, '.') + 1); $sql = "SELECT column_name, data_type, column_default, is_nullable FROM INFORMATION_SCHEMA.TABLES AS t JOIN INFORMATION_SCHEMA.COLUMNS AS c ON t.table_catalog = c.table_catalog AND t.table_schema = c.table_schema AND t.table_name = c.table_name WHERE t.table_name = '$tableName'"; $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['column_name']] = [ 'name' => $val['column_name'], 'type' => $val['data_type'], 'notnull' => (bool)('' === $val['is_nullable']), // not null is empty, null is yes 'default' => $val['column_default'], 'primary' => false, 'autoinc' => false, ]; } } $sql = "SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name='$tableName'"; $pdo = $this->linkID->query($sql); $result = $pdo->fetch(PDO::FETCH_ASSOC); if ($result) { $info[$result['column_name']]['primary'] = true; } return $this->fieldCase($info); } /** * 取得数据表的字段信息 * @access public * @param string $dbName * @return array */ public function getTables(string $dbName = ''): array { $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' "; $pdo = $this->getPDOStatement($sql); $result = $pdo->fetchAll(PDO::FETCH_ASSOC); $info = []; foreach ($result as $key => $val) { $info[$key] = current($val); } return $info; } }
参考
https://weiku.co/article/37/
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。