Thinkphp

1.简介

ThinkPHP是一个快速、简单的基于MVC和面向对象的轻量级PHP开发框架，遵循Apache2开源协议发布，从诞生以来一直秉承简洁实用的设计原则，在保持出色的性能和至简的代码的同时，尤其注重开发体验和易用性，并且拥有众多的原创功能和特性，为WEB应用开发提供了强有力的支持。

本文介绍 thinkphp 该如何连接使用瀚高数据库。

2.加载驱动

具体操作步骤请参考 php_pdo_pgsql 接口说明文档。

3.数据库连接

3.1.数据库准备

create database thinkphp;

CREATE TABLE student(
    id serial,
    name character varying(20),
    height character varying(12),
    nickname character varying(12),
    method character varying(12)
    PRIMARY KEY (id)
);
insert into student(name,height,nickname,method) values ('zhangsan','170','zhangsan',null);
insert into student(name,height,nickname,method) values ('lisi','170','lisi',null);

3.2.数据库配置

环境变量文件 .env 配置示例

[DATABASE]
TYPE = pgsql
HOSTNAME = 192.168.100.101
DATABASE = thinkphp
USERNAME = sysdba
PASSWORD = Qwer@1234
HOSTPORT = 5866
CHARSET = utf8
DEBUG = true

应用配置文件 database.php 配置示例：

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

    // 数据库连接配置信息，使用env函数,表示先从.env文件里面获取, 如果获取成功则使用, 如果获取失败, 则使用env函数的第二个参数
    'connections'     => [
        'pgsql' => [
            // 数据库类型
            'type'            => env('database.type', 'pgsql'),
            // 服务器地址
            'hostname'        => env('database.hostname', '192.168.100.101'),
            // 数据库名
            'database'        => env('database.database', 'thinkphp'),
            // 用户名
            'username'        => env('database.username', 'sysdba'),
            // 密码
            'password'        => env('database.password', 'Qwer@1234'),
            // 端口
            'hostport'        => env('database.hostport', '5866'),
             ........
        ],
        // 更多的数据库配置信息
    ],
];

3.3.控制层代码示例

app\controller\Index.php

<?php
namespace app\controller;

use app\BaseController;
use think\facade\Db;        //此处添加Db引用

class Index extends BaseController
{
    public function selectById()
    {
        //如需访问指定的模式schema,Db::table('schema.tablename')
        $stu=Db::table('student')
                ->where('id', 1)
                ->find();
        return json($stu);
    }

    public function insert()
    {
        $data = ['name' => 'wangwu', 'height' => '180','nickname' => 'wangwu','method' => 'insert'];
        $res=Db::table('student')
                ->save($data);
        dump($res);
    }

    public function update()
    {
        $res= Db::name('student')
                ->where('id', 1)
                ->update(['method' => 'update']);
        dump($res);
    }
    public function deleteById()
    {
        $res= Db::table('student')
                ->delete(1);
        dump($res);
    }
    public function deleteByWhere()
    {
        $res= Db::table('student')
                ->where('name','wangwu')
                ->delete();
        dump($res);
    }
}

4.常见问题

1) could not find driver

问题原因：php未成功加载pdo_pgsql扩展

解决方案：php.ini 文件，检查pdo_pgsql是否加载成功

2) authentication method 13 not supported

问题原因：瀚高数据库支持的认证密码加密方式包括 md5, sm3 和 scram-sha-256 ，报错是因为使用了sm3,驱动不支持国密算法

解决方案一：数据库密码认证方式修改为 md5 或 scram-sha-256

解决方案二：下载替换对应版本的驱动，具体操作方法请参考 php_pdo_pgsql 接口说明

3) function table_msg(unknown) does not exist

问题原因：报错table_msg函数不存在，是因为table_msg函数需要用户自己定义

解决方案：数据库中需执行脚本

CREATE OR REPLACE FUNCTION public.pgsql_type(a_type varchar) RETURNS varchar AS
$BODY$
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;
$BODY$
LANGUAGE PLPGSQL;

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
$body$
DECLARE
     v_ret tablestruct;
     v_oid oid;
     v_sql varchar;
     v_rec RECORD;
     v_key varchar;
BEGIN
     SELECT
           pg_class.oid  INTO v_oid
     FROM
           pg_class
           INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid AND lower(pg_namespace.nspname) = a_schema_name)
     WHERE
           pg_class.relname=a_table_name;
     IF NOT FOUND THEN
         RETURN;
     END IF;

     v_sql='
     SELECT
           pg_attribute.attname AS fields_name,
           pg_attribute.attnum AS fields_index,
           pgsql_type(pg_type.typname::varchar) AS fields_type,
           pg_attribute.atttypmod-4 as fields_length,
           CASE WHEN pg_attribute.attnotnull  THEN ''not null''
           ELSE ''''
           END AS fields_not_null,
           pg_get_expr(pg_attrdef.adbin, pg_attrdef.adrelid) AS fields_default, 
           pg_description.description AS fields_comment
     FROM
           pg_attribute
           INNER JOIN pg_class  ON pg_attribute.attrelid = pg_class.oid
           INNER JOIN pg_type   ON pg_attribute.atttypid = pg_type.oid
           LEFT OUTER JOIN pg_attrdef ON pg_attrdef.adrelid = pg_class.oid AND pg_attrdef.adnum = pg_attribute.attnum
           LEFT OUTER JOIN pg_description ON pg_description.objoid = pg_class.oid AND pg_description.objsubid = pg_attribute.attnum
     WHERE
           pg_attribute.attnum > 0
           AND attisdropped <> ''t''
           AND pg_class.oid = ' || v_oid || '
     ORDER BY pg_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;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

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
$body$
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;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

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