README

最简单的迁移和SqlModel工具

swew/db库是一个轻量级且快速的PHP库，提供了创建和执行数据库迁移以及通过查询与数据库交互的简单接口。它通过参数绑定和其他安全措施设计来防止SQL注入攻击。

PHP - 应该轻量且快速！！！

SWEW开发的包

swew/cli - 一个具有格式化和文本输入功能的命令行界面程序。

swew/test - 一个旨在修复其他测试框架致命缺陷的测试框架。

swew/db - 一个轻量级、快速且安全的PHP库，用于与数据库交互、创建迁移和运行查询。

swew/dd - 简单调试变量的方式。就像在Laravel中一样。

安装

composer require swew/db

迁移

<?php

// Migration file
use Swew\Db\{Migrate,Migrator};

Migrate::up(function (Migrator $table) {
    $table->tableCreate('users');
    $table->id();
    $table->string('name')->unique();
    $table->string('login', 64)->unique()->index();
    $table->string('password', 64)->default('123456');
    $table->text('description')->fulltext();
    $table->integer('rating')->nullable();
    
    // $table->softDeletable(); // If need
    $table->timestamps();
});

Migrate::down(function (Migrator $table) {
    $table->tableDrop('users');
});

运行迁移

<?php

use Swew\Db\{Migrate,ModelConfig};

// path to autoload file
require __DIR__ . '/../vendor/autoload.php';

// PDO connection
$pdo = new PDO('sqlite:' . __DIR__ . '/database.sqlite');
ModelConfig::setPDO($pdo);

// "**" - is alias for sub folders
$filePattern = __DIR__ . '/migrations/**.php';
// Run "UP" migrations
$isUpMigration = true;

Migrate::run($filePattern, $isUpMigration);

数据库查询

模型

<?php

use Swew\Db\Model;

class UserModel extends Model {
    // acceptable fields, should be used with default values, so there are no errors in php 8.2
    public ?int   $id = null;
    public string $login = '';
    public string $name = '';
    public string $password = '';
    public int    $rating = 0;

    // Table name [required]
    protected function table(): string {
        return 'users';
    }

    protected function getCache(): bool {
        return true;
    }


    // By this key use counts [optional] [default: 'id']
    protected function id(): string {
        return 'id';
    }

    // Update updated_at, and add created_at date fields [optional] [default: false]
    protected function hasTimestamp(): bool {
        return true;
    }

    protected function getCast(): array
    {
         return [
            // Default casting, created_at and updated_at - INT
            'created_at' => fn (mixed $timeStamp) => $timeStamp ? strtotime($timeStamp) : '',
            'updated_at' => fn (mixed $timeStamp) => $timeStamp ? strtotime($timeStamp) : '',
        ];
    }

    protected function setCast(): array
    {
        return [
            'password' => fn ($str) => password_hash($str, PASSWORD_BCRYPT),
        ];
    }

    protected function mapTable(): array
    {
        return [
            // 'TABLE' => $this, // default value, fixed
            'T1' => $this,
            'T2' => CommentModel::class,
            'T3' => 'table_name',
        ];
    }

    // SQL Query
    const MOST_POPULAR_USER = 'SELECT id, login, name FROM [TABLE] WHERE rating >= 9';
    const FIND_BY_NAME = 'SELECT id, login, name FROM [TABLE] WHERE name = ?';
    const UPDATE_NAME_BY_ID = 'UPDATE [TABLE] SET name = ? WHERE id = ?';
    const UPDATE_NAME = 'UPDATE [TABLE] SET name = ?';
    const INSERT_LOGIN_NAME = 'INSERT INTO [TABLE] (login, name) VALUES (:login, :name)';
    const JOIN_COMMENT = 'SELECT [T1].name, [T2].comment FROM [T1] JOIN [T2] ON [T1].id=[T2].user_id';
}

GET

UserModel::vm()->query(UserModel::FIND_BY_NAME, 'Jack')->get(); // array
UserModel::vm()->query(UserModel::FIND_BY_NAME, 'Jack')->getFirst(); // item
UserModel::vm()->query(UserModel::MOST_POPULAR_USER)
    ->offset(2)
    ->limit(1)
    ->getFirst();

UserModel::vm()->query(UserModel::FIND_BY_NAME, 'Jack')->getFirstItem(); // UserModel
UserModel::vm()->query(UserModel::FIND_BY_NAME, 'Jack')->getItems(); // UserModel[]

UserModel::vm()->query(UserModel::MOST_POPULAR_USER)->getValue(); // First value from first item

// Mapped values
UserModel::vm()->query(UserModel::MOST_POPULAR_USER)->getMap(
    fn ($v) => $v['login']
);

插入 AND 插入多个

save 的别名

$user = new UserModel();

$user->login = 'Mr 007';
$user->name = 'James';

$lastId = UserModel::vm()->query(UserModel::INSERT_LOGIN_NAME)
    ->setData($user)
    ->exec()
    ->id();

UserModel::vm()
    ->query(UserModel::INSERT_LOGIN_NAME, ['login' => 'MyLogin', 'name' => 'My Name'])
    ->exec();

UserModel::vm()
    ->insert(['login' => 'MyLogin', 'name' => 'My Name']);

UserModel::vm()
    ->insertMany([
      ['login' => 'MyLogin_1', 'name' => 'My Name 1'],
      ['login' => 'MyLogin_2', 'name' => 'My Name 2'],
    ]);

UPDATE

UserModel::vm()
    ->query(UserModel::UPDATE_NAME, 'Garry')
    ->where('id', 1)
    ->exec();

UserModel::vm()
    ->query(UserModel::UPDATE_NAME)
    ->where('id', 1)
    ->exec('Garry');

UserModel::vm()
    ->query(UserModel::UPDATE_NAME)
    ->where('id', 1)
    ->execMany(['Garry']);

COUNT

$count = UserModel::vm()
    ->count()
    ->where('id', '>', 2)
    ->getValue();

JOIN

UserModel::vm()->query(UserModel::JOIN_COMMENT)->get();

PAGINATE

// Paginate
UserModel::vm()->query(UserModel::JOIN_COMMENT)->getPages($pageNumber = 1, $perPage = 10);
UserModel::vm()->query(UserModel::JOIN_COMMENT)->getPagesWithCount();

// Result
[
    'data' => $items, // array
    'page' => 1,
    'next' => 2,
    'prev' => 0,
    // 'count' => 10, // if use ->getPageWithCount()
];

// cursor pagination
UserModel::vm()->query(UserModel::JOIN_COMMENT)->getCursorPages($id = 11, $pageNumber = 2, $perPage = 10);

// Result
[
    'data' => $items, // array
    'next_id' => 21,
    'prev_id' => 1,
    'page' => 1,
    'next' => 2,
    'prev' => 0,
];

事务

$isOk = UserModel::transaction(function () {
    UserModel::vm()->query(UserModel::UPDATE_NAME, 'Leo')->where('id', 1)->exec();
    UserModel::vm()->query(UserModel::UPDATE_NAME, 'Don')->where('id', 2)->exec();
    UserModel::vm()->query(UserModel::UPDATE_NAME, 'Mike')->where('id', 3)->exec();
});

无SQL查询

select

UserModel::vm()
    ->select('name', 'rating')
    ->where('rating', '>', 4)
    ->getFirst();
    // [
    //     'name' => 'Leo',
    //     'rating' => 5,
    // ],

max

UserModel::vm()
    ->max('rating')
    ->getValue('rating'); // 5

min

UserModel::vm()
    ->min('rating')
    ->getValue(); // 1

save

$user = new UserModel();
$user->name = 'Leo';
$user->login = 'Ninja';
$user->password = 'secret';

$user->save();

UserModel::vm()->save([
    'name' => 'Don',
    'login' => 'Ninja',
    'password' => 'secret',
]);

update

$user = new UserModel();
$user->name = 'Master Splinter';
$user->email = 's2@mail.xx';
// OR
// $user = [
//     'name' => 'Master Splinter',
//     'email' => 's2@mail.xx',
// ];
UserModel::vm()->update($user)->where('id', 1)->exec();

delete

UserModel::vm()->delete()->where('id', 1)->exec();

软删除

要使软删除生效，您的表必须有一个类型为DATETIME的deleted_at字段（对于SQLite则为TEXT）以及默认值为NULL。在您的模型中，必须有一个返回true的softDelete()方法。

UserModel::vm()->softDelete()->where('id', 1)->exec();

where

UserModel::vm()->select()->where('id', 1)->exec();
UserModel::vm()->select()->where('id', '=', 1)->exec();
UserModel::vm()->select()->where('id', '!=', 1)->exec();
UserModel::vm()->select()->where('id', '>', 1)->exec();
UserModel::vm()->select()->where('id', '<', 1)->exec();

or where

UserModel::vm()->select()->orWhere('id', 1)->exec();
UserModel::vm()->select()->orWhere('id', '=', 1)->exec();
UserModel::vm()->select()->orWhere('id', '!=', 1)->exec();
UserModel::vm()->select()->orWhere('id', '>', 1)->exec();
UserModel::vm()->select()->orWhere('id', '<', 1)->exec();

where in

UserModel::vm()->select()->whereIn('id', [1, 2, 3])->exec();

where not in

UserModel::vm()->select()->whereNotIn('id', [1, 2, 3])->exec();

缓存

UserModel::vm()
    ->select('name')
    ->where('id', 3)
    ->cache(3600) // seconds
    ->getFirst();

swew / db

维护者

详情