Thinkphp

1.簡(jiǎn)介

ThinkPHP是一個(gè)快速、簡(jiǎn)單的基于MVC和面向?qū)ο蟮妮p量級(jí)PHP開發(fā)框架，遵循Apache2開源協(xié)議發(fā)布，從誕生以來(lái)一直秉承簡(jiǎn)潔實(shí)用的設(shè)計(jì)原則，在保持出色的性能和至簡(jiǎn)的代碼的同時(shí)，尤其注重開發(fā)體驗(yàn)和易用性，并且擁有眾多的原創(chuàng)功能和特性，為WEB應(yīng)用開發(fā)提供了強(qiáng)有力的支持。

本文介紹 thinkphp 該如何連接使用瀚高數(shù)據(jù)庫(kù)。

2.加載驅(qū)動(dòng)

具體操作步驟請(qǐng)參考 php_pdo_pgsql 接口說(shuō)明文檔。

3.數(shù)據(jù)庫(kù)連接

3.1.數(shù)據(jù)庫(kù)準(zhǔn)備

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.數(shù)據(jù)庫(kù)配置

環(huán)境變量文件 .env 配置示例

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

應(yīng)用配置文件 database.php 配置示例：

return [
    // 默認(rèn)使用的數(shù)據(jù)庫(kù)連接配置
    'default'         => env('database.driver', 'pgsql'),
    ......

    // 數(shù)據(jù)庫(kù)連接配置信息，使用env函數(shù),表示先從.env文件里面獲取, 如果獲取成功則使用, 如果獲取失敗, 則使用env函數(shù)的第二個(gè)參數(shù)
    'connections'     => [
        'pgsql' => [
            // 數(shù)據(jù)庫(kù)類型
            'type'            => env('database.type', 'pgsql'),
            // 服務(wù)器地址
            'hostname'        => env('database.hostname', '192.168.100.101'),
            // 數(shù)據(jù)庫(kù)名
            'database'        => env('database.database', 'thinkphp'),
            // 用戶名
            'username'        => env('database.username', 'sysdba'),
            // 密碼
            'password'        => env('database.password', 'Qwer@1234'),
            // 端口
            'hostport'        => env('database.hostport', '5866'),
             ........
        ],
        // 更多的數(shù)據(jù)庫(kù)配置信息
    ],
];

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()
    {
        //如需訪問(wèn)指定的模式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.常見問(wèn)題

1) could not find driver

問(wèn)題原因：php未成功加載pdo_pgsql擴(kuò)展

解決方案：php.ini 文件，檢查pdo_pgsql是否加載成功

2) authentication method 13 not supported

問(wèn)題原因：瀚高數(shù)據(jù)庫(kù)支持的認(rèn)證密碼加密方式包括 md5, sm3 和 scram-sha-256 ，報(bào)錯(cuò)是因?yàn)槭褂昧藄m3,驅(qū)動(dòng)不支持國(guó)密算法

解決方案一：數(shù)據(jù)庫(kù)密碼認(rèn)證方式修改為 md5 或 scram-sha-256

解決方案二：下載替換對(duì)應(yīng)版本的驅(qū)動(dòng)，具體操作方法請(qǐng)參考 php_pdo_pgsql 接口說(shuō)明

3) function table_msg(unknown) does not exist

問(wèn)題原因：報(bào)錯(cuò)table_msg函數(shù)不存在，是因?yàn)閠able_msg函數(shù)需要用戶自己定義

解決方案：數(shù)據(jù)庫(kù)中需執(zhí)行腳本

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 '獲得表信息';

---重載一個(gè)函數(shù)
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 '獲得表信息';