YII通过OCI8连接远程Oracle19C - Linux

By刘立博
2021-09-01 17:07:28
730人已阅读

如果PHP运行环境与Oracle服务端在同一台机器，见 https://blog.liboliu.com/a/132

安装oracle client

根据服务端版本，前往官网下载对应的client

https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/

mkdir /oracle_rpms
cd /oracle_rpms

wget https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/getPackage/oracle-instantclient19.3-basic-19.3.0.0.0-1.x86_64.rpm
wget https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/getPackage/oracle-instantclient19.3-devel-19.3.0.0.0-1.x86_64.rpm
wget https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/getPackage/oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm

yum localinstall oracle* --nogpgcheck

vi /etc/profile

export ORACLE_HOME=/usr/lib/oracle/19.3/client64
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

搭建PHP运行环境

yum install -y epel-release
yum install -y nginx redis mariadb mariadb-server git
rpm -Uvh https://mirrors.tuna.tsinghua.edu.cn/remi/enterprise/remi-release-7.rpm
yum --enablerepo=remi-php72 install -y php
yum --enablerepo=remi-php72 install -y php-bcmath php-gd php-fpm php-devel php-mbstring php-mysqlnd php-pear php-pecl-mcrypt php-pecl-event php-pecl-redis php-pecl-oci8

停用remi安装的OCI8扩展

remi安装的OCI8会报错，需要手工编译安装，所以先注释这两个扩展

vi /etc/php.d/20-oci8.ini
#extension=oci8

vi /etc/php.d/30-pdo_oci.ini
#extension=pdo_oci

安装OCI8

yum install systemtap-sdt-devel
export PHP_DTRACE=yes
C_INCLUDE_PATH=/usr/include/oracle/19.3/client64/ pecl install oci8-2.2.0

安装PDO_OCI

前往PHP官网，下载对应版本的PHP源码：https://www.php.net/releases/

wget https://www.php.net/distributions/php-7.2.34.tar.gz
tar -xvzf php-7.2.34.tar.gz 
cd php-7.2.34/ext/pdo_oci/
/usr/bin/phpize
./configure --with-pdo-oci=instantclient,/usr/lib/oracle/19.3/client64/lib --with-php-config=/usr/bin/php-config

make
make instal

启用OCI8扩展

vi /etc/php.d/20-oci8.ini
extension=oci8

vi /etc/php.d/30-pdo_oci.ini
extension=pdo_oci

配置环境变量

配置LD_LIBRARY_PATH

sh -c "echo /usr/lib/oracle/19.3/client64/lib  > /etc/ld.so.conf.d/oracle.conf"
ldconfig

编辑php-fpm

vi /etc/php-fpm.d/www.conf 

env[LD_LIBRARY_PATH] = /usr/lib/oracle/19.3/client64/lib
env[ORACLE_HOME] = /usr/lib/oracle/19.3/client64

配置PHP运行环境

安装composer

curl -sS https://getcomposer.org/installer | php
mv composer.phar /usr/local/bin/composer

安装YII2

mkdir /code
cd /code
composer create-project --prefer-dist yiisoft/yii2-app-basic basic

配置nginx

vi /etc/nginx/conf.d/virtual.conf

server {

       listen       80;
       server_name  0.0.0.0;
       charset utf-8;


       location / {
           root   /code/basic/web/;
           index  index.php index.html index.htm;
            try_files $uri $uri/ /index.php$is_args$args;
       }
       location ~ .php$ {
           root           /code/basic/web/;
           fastcgi_pass   127.0.0.1:9000;
           fastcgi_index  index.php;
           fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
           include        fastcgi_params;

       }

   }

配置自启动

systemctl enable nginx
systemctl enable php-fpm

启动服务

systemctl restart nginx
systemctl restart php-fpm

测试是否配置成功

使用浏览器访问服务器IP，可以显示YII欢迎页

使用YII连接Oracle

创建测试表

创建一张cms_user表用于测试，使用navicat在C##TEST 执行SQL

CREATE TABLE "C##TEST"."cms_user" (
  "USER_ID" NUMBER VISIBLE NOT NULL,
  "ACCOUNT" VARCHAR2(255 BYTE) VISIBLE,
  "PASSWORD" VARCHAR2(255 BYTE) VISIBLE,
  "SALT" VARCHAR2(255 BYTE) VISIBLE,
  "AVATAR" VARCHAR2(255 BYTE) VISIBLE,
  "USERNAME" VARCHAR2(255 BYTE) VISIBLE,
  "STATUS" NUMBER VISIBLE,
  "CREATE_DATE" DATE VISIBLE
);

ALTER TABLE "C##TEST"."cms_user" ADD CONSTRAINT "TF_B_AIR_CONFIG_PK" PRIMARY KEY ("USER_ID");
ALTER TABLE "C##TEST"."cms_user" ADD CONSTRAINT "SYS_C007550" CHECK ("USER_ID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

INSERT INTO "C##TEST"."cms_user" VALUES ('16293403122330', 'liulibo', '3fc0dcf79b0e94b8bf60dff185e0b834', '8460', 'https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif?imageView2/1/w/80/h/80', 'liulibo', '1', TO_DATE('2021-08-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));

修改数据库配置文件

vi /code/basic/config/db.php

<?php

return [
    'class' => 'yii\db\Connection',
    'dsn' => 'oci:dbname=//localhost:1521/ORCLCDB;charset=AL32UTF8',
    'username' => 'c##test',
    'password' => '123456',
    'charset' => 'utf8',

    // Schema cache options (for production environment)
    //'enableSchemaCache' => true,
    //'schemaCacheDuration' => 60,
    //'schemaCache' => 'cache',
];

编写model

vi /code/basic/models/CmsUser.php

<?php

namespace app\models;

use app\ObjectDef\UserBO;
use yii\db\ActiveRecord;

class CmsUser extends ActiveRecord
{
    public $primaryKey = 'USER_ID';

    public static function tableName()
    {
        return 'cms_user';
    }

}

编写测试控制器

vi /code/basic/controllers/TestController.php

<?php

namespace app\controllers;

use app\models\CmsUser;
use yii\web\Controller;

class TestController extends Controller
{
    public function actionIndex()
    {
        var_dump(CmsUser::find()->all());
        die();
    }

}

测试

访问 /?r=test，成功查询出该表数据