README

OscarDB（为 Laravel 8.x 更新）

OscarDB 是 Laravel 框架的 Oscar 数据库驱动程序包

注意：本包在 PHP 8 上未进行测试。

请报告您可能发现的任何错误。

在线安装

使用 Composer

composer require microestc/oscardb

在此命令期间，Laravel 的 "自动发现" 功能应自动注册 OscarDB 的服务提供程序。

离线安装

使用 Composer

1. 首先获取到该项目源码 （Laravel-OscarDB）

root@deb10:~/works/Laravel-OscarDB# tree
.
├── composer.json
├── phpunit.xml
├── readme.md
├── src
│   ├── config
│   │   └── oscardb.php
│   └── Microestc
│       └── OscarDB
│           ├── Connectors
│           │   └── OscarConnector.php
│           ├── OscarConnection.php
│           ├── OscarDBServiceProvider.php
│           ├── Query
│           │   ├── Grammars
│           │   │   └── OscarGrammar.php
│           │   ├── OscarBuilder.php
│           │   └── Processors
│           │       └── OscarProcessor.php
│           └── Schema
│               ├── Grammars
│               │   └── OscarGrammar.php
│               └── OscarSchemaBuilder.php
└── tests
    ├── mocks
    │   └── PDOMocks.php
    ├── OscarDBConnectorTest.php
    ├── OscarDBPDOProcessorTest.php
    ├── OscarDBQueryBuilderTest.php
    └── OscarDBSchemaGrammarTest.php

12 directories, 17 files
root@deb10:~/works/Laravel-OscarDB# 

2. 配置项目依赖组件

在项目配置文件 composer.json 中添加下列配置

    "require": {
        "microestc/oscardb": "*"
    },
    "repositories": [
        {
            "packagist.org": false,
            "type": "path",
            "url": "../Laravel-OscarDB",
            "options": {
                "symlink": false
            }
        }
    ],

"url": "../Laravel-OscarDB", 该路径为 Laravel-OscarDB 源码文件的路径

3. 执行离线安装

composer update

安装结果

root@deb10:~/works/webapp# composer update
PHP Warning:  Module 'PDO_ACI' already loaded in Unknown on line 0

Warning: Module 'PDO_ACI' already loaded in Unknown on line 0
PHP Warning:  Xdebug MUST be loaded as a Zend extension in Unknown on line 0

Warning: Xdebug MUST be loaded as a Zend extension in Unknown on line 0
PHP Warning:  Module 'xdebug' already loaded in Unknown on line 0

Warning: Module 'xdebug' already loaded in Unknown on line 0
Xdebug: [Step Debug] Could not connect to debugging client. Tried: localhost:9003 (through xdebug.client_host/xdebug.client_port) :-(
PHP Warning:  Module 'PDO_ACI' already loaded in Unknown on line 0

Warning: Module 'PDO_ACI' already loaded in Unknown on line 0
PHP Warning:  Xdebug MUST be loaded as a Zend extension in Unknown on line 0

Warning: Xdebug MUST be loaded as a Zend extension in Unknown on line 0
Xdebug: [Step Debug] Could not connect to debugging client. Tried: localhost:9003 (through xdebug.client_host/xdebug.client_port) :-(
Composer is operating slower than normal because you have Xdebug enabled. See https://getcomposer.org/xdebug
Do not run Composer as root/super user! See https://getcomposer.org/root for details
Continue as root/super user [yes]? y
Loading composer repositories with package information
Updating dependencies
Lock file operations: 1 install, 0 updates, 0 removals
  - Locking microestc/oscardb (dev-master)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 1 install, 0 updates, 0 removals
  - Installing microestc/oscardb (dev-master): Mirroring from ../Laravel-OscarDB
Package swiftmailer/swiftmailer is abandoned, you should avoid using it. Use symfony/mailer instead.
Generating optimized autoload files
> Illuminate\Foundation\ComposerScripts::postAutoloadDump
> @php artisan package:discover --ansi
PHP Warning:  Module 'PDO_ACI' already loaded in Unknown on line 0

Warning: Module 'PDO_ACI' already loaded in Unknown on line 0
PHP Warning:  Xdebug MUST be loaded as a Zend extension in Unknown on line 0

Warning: Xdebug MUST be loaded as a Zend extension in Unknown on line 0
PHP Warning:  Module 'xdebug' already loaded in Unknown on line 0

Warning: Module 'xdebug' already loaded in Unknown on line 0
Xdebug: [Step Debug] Could not connect to debugging client. Tried: localhost:9003 (through xdebug.client_host/xdebug.client_port) :-(
Discovered Package: facade/ignition
Discovered Package: fruitcake/laravel-cors
Discovered Package: laravel/sail
Discovered Package: laravel/sanctum
Discovered Package: laravel/tinker
Discovered Package: microestc/oscardb
Discovered Package: nesbot/carbon
Discovered Package: nunomaduro/collision
Package manifest generated successfully.
77 packages you are using are looking for funding.
Use the `composer fund` command to find out more!
> @php artisan vendor:publish --tag=laravel-assets --ansi --force
PHP Warning:  Module 'PDO_ACI' already loaded in Unknown on line 0

Warning: Module 'PDO_ACI' already loaded in Unknown on line 0
PHP Warning:  Xdebug MUST be loaded as a Zend extension in Unknown on line 0

Warning: Xdebug MUST be loaded as a Zend extension in Unknown on line 0
PHP Warning:  Module 'xdebug' already loaded in Unknown on line 0

Warning: Module 'xdebug' already loaded in Unknown on line 0
Xdebug: [Step Debug] Could not connect to debugging client. Tried: localhost:9003 (through xdebug.client_host/xdebug.client_port) :-(
No publishable resources for tag [laravel-assets].
Publishing complete.

配置

接下来，使用 vendor:publish Artisan 命令发布 OscarDB 的配置文件。这将把 OscarDB 的配置文件复制到您的项目中的 config/oscardb.php。

php artisan vendor:publish --tag=oscardb-config

为了完成安装，设置您的环境变量（通常在您的 .env 文件中）到 config/oscardb.php 中使用的相应环境变量：例如 DB_HOST、DB_USERNAME 等。

此外，在执行任何查询之前，您的应用程序可能需要配置数据库连接会话的 NLS_DATE_FORMAT。一种实现方式是在 AppServiceProvider 的 boot 方法中运行一个语句，例如

if (config('database.default') === 'oscardb') {
	DB::statement("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
}

基本用法

本包的配置文件位于 config/oscardb.php。在此文件中，您定义了所有的 Oscar 数据库连接。如果您需要建立多个连接，只需复制示例即可。如果您想将其中一个连接设为默认连接，请在 config/database.php 中的 "默认数据库连接名称" 部分输入您给该连接起的名字。

例如在 config/database.php 中

'default' => 'oscardb',

一旦配置了 OscarDB 数据库连接，您就可以像平常一样使用 DB 门面运行查询。

注意：ACI 是默认驱动程序。如果您想使用 PDO_ACI 驱动程序，请在 config/oscardb.php 文件中将任何您希望使用 PDO_ACI 的连接的 driver 值更改为 'pdo'。将驱动程序设置为 'pdo' 将使 OscarDB 使用 PDO_ACI 扩展。给定任何其他 driver 值，OscarDB 将使用 ACI 函数。

$results = DB::select('select * from users where id = ?', [1]);

以上语句假设您已将默认连接设置为在 config/database.php 文件中设置的 Oscar 连接，并且将始终返回一个 array 的结果。

$results = DB::connection('oscardb')->select('select * from users where id = ?', [1]);

就像内置的数据库驱动程序一样，您可以使用连接方法访问在 config/oscardb.php 文件中设置的 Oscar 数据库。

将记录插入具有自增 ID 的表中

	$id = DB::connection('oscardb')->table('users')->insertGetId(
		['email' => 'john@example.com', 'votes' => 0], 'userid'
	);

注意：当使用 insertGetId 方法时，您可以在 insertGetId 函数中将自增列名称作为第二个参数指定。如果没有指定，它将默认为 "id"。

有关更多信息，请参阅 Laravel 数据库基本文档。

未实现的功能

本包未实现一些 Laravel 数据库驱动程序提供的功能。欢迎使用 pull requests 来实现这些功能，或者如果您发现未列出的未实现功能，可以扩展此列表。

查询构建器

• 插入或忽略 DB::from('users')->insertOrIgnore(['email' => 'foo']);
• 使用空值进行 insertGetId DB::from('users')->insertGetId([]); (但支持使用非空值调用)
• upserts DB::from('users')->upsert([['email' => 'foo', 'name' => 'bar'], ['name' => 'bar2', 'email' => 'foo2']], 'email');
• 通过连接删除 DB::from('users')->join('contacts', 'users.id', '=', 'contacts.id')->where('users.email', '=', 'foo')->delete();
• 使用限制进行删除 DB::from('users')->where('email', '=', 'foo')->orderBy('id')->take(1)->delete();
• json操作 DB::from('users')->where('items->sku', '=', 'foo-bar')->get();

模式构建器

• 如果存在则删除表 Schema::dropIfExists('some_table');
• 删除所有表、视图或类型 Schema::dropAllTables()，Schema::dropAllViews()，和 Schema::dropAllTypes()
• 设置表上的校对规则 $blueprint->collation('BINARY_CI')
• 设置列上的校对规则 $blueprint->string('some_column')->collation('BINARY_CI')
• 设置表上的注释 $blueprint->comment("This table is great.")
• 设置列上的注释 $blueprint->string('foo')->comment("Some helpful info about the foo column")
• 设置自增列的起始值 $blueprint->increments('id')->startingValue(1000)
• 创建一个私有临时表 $blueprint->temporary()
• 重命名索引 $blueprint->renameIndex('foo', 'bar')
• 创建索引时指定算法（通过第三个参数） $blueprint->index(['foo', 'bar'], 'baz', 'hash')
• 创建空间索引 $blueprint->spatialIndex('coordinates')
• 流畅地创建空间索引 $blueprint->point('coordinates')->spatialIndex()
• 创建一个生成列，如mysql驱动程序有 virtualAs 和 storedAs 以及postgres有 generatedAs；例如，假设表上存在名为price的整数类型列，$blueprint->integer('discounted_virtual')->virtualAs('price - 5')
• 创建json列 $blueprint->json('foo') 或jsonb列 $blueprint->jsonb('foo')（Oscar建议将json存储在VARCHAR2、CLOB或BLOB列中）
• 创建无精度的时间戳列 $blueprint->dateTimeTz('created_at')，或带精度的 $blueprint->timestampTz('created_at', 1)
• 创建具有时区组件的Laravel风格时间戳列 $blueprint->timestampsTz()
• 创建uuid列 $blueprint->uuid('foo')（Oscar建议使用16字节原始数据类型的列来存储uuids）
• 创建外键uuid列 $blueprint->foreignUuid('foo')
• 创建存储IP地址的列 $blueprint->ipAddress('foo')（将实现为45个字符的varchar2）
• 创建存储MAC地址的列 $blueprint->macAddress('foo')（将实现为17个字符的varchar2）
• 创建几何列 $blueprint->geometry('coordinates')
• 创建几何点列 $blueprint->point('coordinates')
• 创建指定srid的几何点列 $blueprint->point('coordinates', 4326)
• 创建线字符串列 $blueprint->linestring('coordinates')
• 创建多边形列 $blueprint->polygon('coordinates')
• 创建几何集合列 $blueprint->geometrycollection('coordinates')
• 创建多点列 $blueprint->multipoint('coordinates')
• 创建多线字符串列 $blueprint->multilinestring('coordinates')
• 创建多边形列 $blueprint->multipolygon('coordinates')
• 创建不指定第二个或第三个参数的双精度列 $blueprint->double('foo')（但$blueprint->double('foo', 5, 2)是支持的）
• 创建具有useCurrent修饰符的时间戳列 $blueprint->timestamp('created_at')->useCurrent()

许可证

在[MIT许可证]下许可。

DEMO

创建web新应用webapp

laravel new webapp
cd webapp

安装microestc/oscardb

composer require microestc/oscardb

创建配置文件config/oscardb.php

<?php

return [
    'oscardb' => [
        'driver'    => 'oscar',
        'url'       => env('DB_URL'),
        'host'      => '10.1.1.66',
        'port'      => '2003',
        'database'  => 'osrdb',
        'username'  => 'sysdba',
        'password'  => 'szoscar55',
        'charset'   => 'utf8',
        'prefix'    => '',
        'quoting'   => false,
    ],
];

修改默认数据库config/database.php

    'default' => 'oscardb',

配置默认数据库提供服务config/app.php providers中加入

Microestc\OscarDB\OscarDBServiceProvider::class,

开始测试

追加路由routes/web.php

Route::get('/users/index', [UsersController::class, 'index']);

app/Http/Controllers/UsersController.php

<?php
 
namespace App\Http\Controllers;
 
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
 
class UsersController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        DB::statement('drop table if exists php_users');

        DB::statement('create table php_users (id int auto_increment primary key, name text)');
        
        DB::unprepared("insert into php_users (name) values ('张三'),('Lili')");

        $users = DB::select('select * from php_users');
        \print_r($users);

        $users = DB::select('select * from php_users where id > ?', [1]);
        \print_r($users);

        $users = DB::connection()->select('select * from php_users');
        \print_r($users);

        $users = DB::connection('oscardb')->select('select * from php_users');
        \print_r($users);

        $id = DB::connection('oscardb')->table('php_users')->insertGetId(
            ['name' => 'john@example.com'], 'id'
        );

        \print_r($id);

        DB::insert('insert into php_users (name) values (?)', ['Marc']);
        $users = DB::select('select * from php_users');
        \print_r($users);


        $affected = DB::update(
            'update php_users set name = ? where id = 1',
            ['Anita']
        );

        \print_r("\n affected:".$affected);

        $deleted = DB::delete('delete from php_users where id = 1');

        \print_r("\n deleted:".$deleted);

        DB::transaction(function () {
            DB::update('update php_users set name = ? where id = 2', ['李四']);
         
            DB::delete('delete from php_posts');
            DB::rollBack();
        }, 2);

        $users = DB::select('select * from php_users where id = 2');
        \print_r($users);
 
        return view('users_index', ['users' => $users]);
    }
}

resources/views/users_index.blade.php

<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <title>Laravel</title>

        <!-- Fonts -->
        <link href="https://fonts.googleapis.com/css2?family=Nunito:wght@400;600;700&display=swap" rel="stylesheet">

        <style>
            body {
                font-family: 'Nunito', sans-serif;
            }
        </style>
    </head>
    <body class="antialiased">
    </body>
</html>

启动应用

php artisan serve

在浏览器中键入地址查看结果