jhq0113/roach-orm

v1.0.9 2020-08-07 12:58 UTC

This package is auto-updated.

Last update: 2020-10-07 13:18:47 UTC


README

roach-orm是一个简单高性能的PHP语言ORM框架,支持数据库的负载均衡预防SQL注入故障自动摘除自动恢复以及读写分离等强大功能,整个代码文件不到20K

我的官方网站

https://404.360tryst.com/

安装方式

composer require jhq0113/roach-orm

1.使用Model

1.1 配置db

通过composer下载完本依赖后,在您的项目中加载composer依赖(一般在您的项目入口文件中requirevendor/autoload.php即可),如果已经加载忽略此步骤。

在使用Model前需要将db组件注册到roach\Container中,配置是数组格式,可以放到配置文件中,注册方式如下

<?php
\roach\Container::set('db', [
    'class' => 'roach\orm\Connection',
    //
    'masters' => [
        [
            'dsn'      => 'mysql:host=192.168.1.14;port=3306;dbname=roach;charset=utf8',
            'username' => 'roach', 
            'password' => 'roach',   
        ],
        [
            'dsn'      => 'mysql:host=192.168.1.13;port=3306;dbname=roach;charset=utf8',
            'username' => 'roach', 
            'password' => 'roach',
            //可以通过options指定配置属性
            'options'  => [
                \PDO::ATTR_TIMEOUT => 3,   
            ]    
        ],
    ],
    //如果没有slave节点,可以不配置,会自动复用master节点
    'slaves' => [
        [
           'dsn'      => 'mysql:host=192.168.1.15;port=3306;dbname=roach;charset=utf8',
           'username' => 'roach', 
           'password' => 'roach',  
           'options'  => [
                \PDO::ATTR_TIMEOUT => 2,   
           ] 
        ],
        [
           'dsn'      => 'mysql:host=192.168.1.16;port=3306;dbname=roach;charset=utf8',
           'username' => 'roach', 
           'password' => 'roach', 
           'options'  => [
               \PDO::ATTR_TIMEOUT => 2,   
           ]   
        ], 
    ]
]);

1.2 使用Model进行CRUD

假入您的数据库中有如下表

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `user_name` varchar(32) CHARACTER SET utf8 DEFAULT 'NULL' COMMENT '登录名',
  `true_name` varchar(32) CHARACTER SET utf8 DEFAULT '' COMMENT '真实姓名',
  `password` char(32) CHARACTER SET utf8 DEFAULT '' COMMENT '密码',
  `is_on` tinyint(3) unsigned DEFAULT 0 COMMENT '是否启用(0禁用1启用)',
  `last_login_ip` bigint(20) unsigned DEFAULT 0 COMMENT '上次登录ip',
  `add_time` timestamp NULL DEFAULT current_timestamp() COMMENT '添加时间',
  `update_time` int(10) unsigned DEFAULT 0 COMMENT '修改时间',
  `version` int(10) unsigned DEFAULT 0 COMMENT '乐观锁版本',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='用户表'

创建UserModel类,使之继承roach\orm\Model,如下

<?php
/**
 * Created by PhpStorm.
 * User: Jiang Haiqiang
 * Date: 2020/7/5
 * Time: 1:33 PM
 */

/**
 * Class UserModel
 * @datetime 2020/7/5 1:33 PM
 * @author roach
 * @email jhq0113@163.com
 */
class UserModel extends \roach\orm\Model
{
    /**表名称
     * @var string
     * @datetime 2020/7/5 1:33 PM
     * @author roach
     * @email jhq0113@163.com 
     */
    public static $tableName = 'user';
}
  • A.insert数据

插入单条数据

<?php
/**
 * 此处返回受影响行数 
 */
$rows = UserModel::insert([
    'user_name'   => 'zhou boss',
    'true_name'   => '周**',
    'password'    => hash_hmac('md5', 'Mr.zhou', 'sdfs#$#@3fd'),
    'update_time' => time()
]);

if($rows < 1) {
    exit('插入失败'.PHP_EOL);
}

//如果想获取刚刚插入数据的`id`,通过如下方式
$newUserId = UserModel::getDb()->lastInsertId();
exit('插入成功,用户id为'.$newUserId.PHP_EOL);

插入多条数据

<?php
/**
 * 此处返回受影响行数 
 */
$rows = UserModel::multiInsert([
    [
        'user_name'   => 'zhao boss',
        'true_name'   => '赵**',
        'password'    => hash_hmac('md5', 'Mr.zhao', 'sdfs#$#@3fd'),
        'update_time' => time()
    ],
    [
        'user_name'   => 'li boss',
        'true_name'   => '李**',
        'password'    => hash_hmac('md5', 'Mr.li', 'sdfs#$#@3fd'),
        'update_time' => time()
    ],
]);

var_dump($rows);
  • 查询

where条件可以是数组,也可以是字符串,当where条件为数组时,多个条件之间是AND关系,alloneupdateAlldeleteAll等方法中的where条件的表达式解析是一致的

相等查询

<?php
//SELECT * FROM `user` WHERE `id`=1 LIMIT 1
$user = UserModel::find()
    ->where([
        'id' => 1,
    ])
    ->one();

IN查询

<?php
//SELECT * FROM `user` WHERE `id` IN(1,2,3) LIMIT 1000
$userList = UserModel::find()
            ->where([
               'id' => [1, 2, 3] 
            ])
            ->all();

BETWEEN查询

<?php
//SELECT * FROM `user` WHERE `id` BETWEEN 1 AND 3 LIMIT 1000
$userList = UserModel::find()
            ->where([
               'id BETWEEN' => [1, 3] 
            ])
            ->all();

范围查询(>, >=, <, <=, ><, !=)

<?php
//SELECT * FROM `user` WHERE `id`<3 LIMIT 1000
$userList = UserModel::find()
            ->where([
               'id <' => 3
            ])
            ->all();

LIKE查询

<?php
//SELECT * FROM `user` WHERE `true_name` LIKE '周%' LIMIT 1000
$userList = UserModel::find()
            ->where([
               'true_name LIKE' => '周%'
            ])
            ->all();

多条件查询

<?php
//SELECT * FROM `user` WHERE `id`=1 AND `is_on`=1
$user = UserModel::find()
    ->where([
        'id'    => 1,
        'is_on' => 1
    ])
    ->one();

GROUP BY查询

<?php
//SELECT COUNT(`is_on`) AS `count`,`is_on` FROM `user` GROUP BY `is_on` LIMIT 1000
$list = UserModel::find()
    ->select('COUNT(`is_on`) AS `count`,`is_on`')
    ->group([
        'is_on', //可以接多个
    ])
    ->all();

ORDER BY查询

<?php
//SELECT 'id', 'true_name' FROM `user` ORDER BY `id` DESC LIMIT 1000
$userList = UserModel::find()
    ->select([
       'id', 'true_name' 
    ])
    ->order([
        'id'    => SORT_DESC, 
        'is_on' => SORT_ASC,
    ])
    ->all();

分页查询

<?php
//SELECT * FROM `user` LIMIT 0,10
$userList = UserModel::find()
    ->offset(0)
    ->limit(10)
    ->all();
  • 更新操作
<?php
//这里返回的是受影响行数
//UPDATE `user` SET `true_name`='sun boss' WHERE `id`=1;
$rows = UserModel::updateAll(['true_name' => 'sun boss'], ['id' => 1]);
  • 删除操作
<?php
//这里返回的是受影响行数
//DELETE FROM `user` WHERE `id`=4;
$rows = UserModel::deleteAll(['id' => 4]);
  • 使用事务

不支持跨库事务

普通方式

<?php
$db =  UserModel::getDb();

try {
   $db->begin();
   $user = UserModel::find()
       ->where([
           'id'    => 1,
           'is_on' => 1
       ])
       //事务要都用主库查询
       ->one(true);
   if(!isset($user['id'])) {
       //返回false会自动回滚事务
       return false;
   }
   
   //.....其他操作
   
   $rows = $db->execute('UPDATE `user` SET `true_name`=? WHERE id=1 AND version=?', [
       'zheng boss', $user['version']
   ]);
   
   //如果受影响函数是1,返回true,返回true会自动提交事务
   if($rows === 1) {
        $db->commit();
   }
   $db->rollback();
}catch (\Exception $exception) {
    $db->rollback();
}

闭包方式

<?php
$success = UserModel::getDb()->transaction(function (\roach\orm\Connection $connection){
    $user = UserModel::find()
                ->where([
                    'id'    => 1,
                    'is_on' => 1
                ])
                //事务要都用主库查询
                ->one(true);
    if(!isset($user['id'])) {
        //返回false会自动回滚事务
        return false;
    }
    
    //.....其他操作
    
    $rows = $connection->execute('UPDATE `user` SET `true_name`=? WHERE id=1 AND version=?', [
        'zheng boss', $user['version']
    ]);
    
    //如果受影响函数是1,返回true,返回true会自动提交事务
    return $rows === 1;
});

if(!$success) {
    exit('事务提交失败'.PHP_EOL);
}
exit('事务提交成功'.PHP_EOL);

2.读写分离

  • 默认情况下,查询使用从库进行查询,如果想使用主库查询,需要将allone方法的参数变为true即可
<?php
//SELECT * FROM `user` WHERE `id`=1 AND `is_on`=1
$user = UserModel::find()
    ->where([
        'id'    => 1,
        'is_on' => 1
    ])
    ->one(true);
  • 所有写操作都是走的主库

  • 执行原生sql

读操作

<?php
$users = UserModel::getDb()->queryAll('SELECT * FROM `user` WHERE id=? UNION SELECT * FROM `user` WHERE id=?', [
    1, 2
]);

写操作

<?php
//这里返回受影响行数
$rows = UserModel::getDb()->execute('UPDATE `user` SET `true_name`=? WHERE id=1 AND version=1', [
    'wu boss'
]);

3.切库

如果我们的项目使用的不是一个数据库集群,这样我们的项目就需要跨集群访问数据库,可以通过如下方式实现

<?php
//将一组新的数据库集群注册到`Container`中,key自己定义即可
\roach\Container::set('tradeDb', [
    'class' => 'roach\orm\Connection',
    //
    'masters' => [
        [
            'dsn'      => 'mysql:host=192.168.1.14;port=3306;dbname=roach;charset=utf8',
            'username' => 'roach', 
            'password' => 'roach',   
        ],
        [
            'dsn'      => 'mysql:host=192.168.1.13;port=3306;dbname=roach;charset=utf8',
            'username' => 'roach', 
            'password' => 'roach',
            //可以通过options指定配置属性
            'options'  => [
                \PDO::ATTR_TIMEOUT => 3,   
            ]    
        ],
    ],
    //如果没有slave节点,可以不配置,会自动复用master节点
    'slaves' => [
        [
           'dsn'      => 'mysql:host=192.168.1.15;port=3306;dbname=roach;charset=utf8',
           'username' => 'roach', 
           'password' => 'roach',  
           'options'  => [
                \PDO::ATTR_TIMEOUT => 2,   
           ] 
        ],
        [
           'dsn'      => 'mysql:host=192.168.1.16;port=3306;dbname=roach;charset=utf8',
           'username' => 'roach', 
           'password' => 'roach', 
           'options'  => [
               \PDO::ATTR_TIMEOUT => 2,   
           ]   
        ], 
    ]
]);

Model类中

<?php
/**
 * Created by PhpStorm.
 * User: Jiang Haiqiang
 * Date: 2020/7/5
 * Time: 1:33 PM
 */

/**
 * Class TradeModel
 * @datetime 2020/7/5 1:33 PM
 * @author roach
 * @email jhq0113@163.com
 */
class TradeModel extends \roach\orm\Model
{
    /**表名称
     * @var string
     * @datetime 2020/7/5 1:33 PM
     * @author roach
     * @email jhq0113@163.com 
     */
    public static $tableName = 'trade';
    
    /**
    * @return mixed|\roach\orm\Connection
    * @throws ReflectionException
    * @datetime 2020/7/5 2:22 PM
    * @author roach
    * @email jhq0113@163.com
     */
    public static function getDb()
    {
        return \roach\Container::get('tradeDb');
    }
}

这样我们就完成切库,当我们使用TradeModel访问数据库是自动调用的是tradeDb集群的数据库

4. 事件处理

roach-orm支持四种事件

事件名称 常量 触发机制
db:connect:exception roach\orm\Connection::EVENT_EXCEPTION_CONNECT 在连接数据时,某个节点连接异常,此事件不会抛出异常,只有当所有连接都连接不上才会抛出异常
db:query:before roach\orm\Connection::EVENT_BEFORE_QUERY 执行sql之前触发
db:query:after roach\orm\Connection::EVENT_AFTER_QUERY 执行sql之后触发
db:connect:lost roach\orm\Connection::EVENT_EXCEPTION_CONNECT_LOST 在执行sql时,连接断了,此事件不会抛出异常,只有当所有连接都连接不上才会抛出异常

我们可以在向Container中注册数据库组件时监听这些事件,等事件触发时做相应的处理即可,绑定事件的方式有两种,第一种是注入方法方式绑定。

<?php
\roach\Container::set('db', [
    'class' => 'roach\orm\Connection',
    //
    'masters' => [
        [
            'dsn'      => 'mysql:host=192.168.1.14;port=3306;dbname=roach;charset=utf8',
            'username' => 'roach', 
            'password' => 'roach',   
        ],
        [
            'dsn'      => 'mysql:host=192.168.1.13;port=3306;dbname=roach;charset=utf8',
            'username' => 'roach', 
            'password' => 'roach',
            //可以通过options指定配置属性
            'options'  => [
                \PDO::ATTR_TIMEOUT => 3,   
            ]    
        ],
    ],
    //如果没有slave节点,可以不配置,会自动复用master节点
    'slaves' => [
        [
           'dsn'      => 'mysql:host=192.168.1.15;port=3306;dbname=roach;charset=utf8',
           'username' => 'roach', 
           'password' => 'roach',  
           'options'  => [
                \PDO::ATTR_TIMEOUT => 2,   
           ] 
        ],
        [
           'dsn'      => 'mysql:host=192.168.1.16;port=3306;dbname=roach;charset=utf8',
           'username' => 'roach', 
           'password' => 'roach', 
           'options'  => [
               \PDO::ATTR_TIMEOUT => 2,   
           ]   
        ], 
    ],
    'calls' => [
        [
            'method' => 'on',
            'params' => [
                \roach\orm\Connection::EVENT_EXCEPTION_CONNECT,
                function(\roach\events\EventObject $event){
                    //。。。打日志报警等各种处理,该事件触发了,并不一定所有的节点都不能用了
                    //exception中是异常信息,config是节点配置
                    var_dump($event->data['exception'], $event->data['config']);
                }
            ]
        ],
        [
            'method' => 'on',
            'params' => [
                \roach\orm\Connection::EVENT_EXCEPTION_CONNECT_LOST, function (\roach\events\EventObject $event){
                   //...各种操作
                   //sql是指当执行某条sql时,mysql连接断了,但是会自动重连一次,如果重连失败,不会再触发该事件,会抛出异常
                   var_dump($event->data['sql'], $event->data['exception']);
               }
            ]
        ],
        [
            'method' => 'on',
            'params' => [
                \roach\orm\Connection::EVENT_BEFORE_QUERY, function (\roach\events\EventObject $event){
                   //params为参数绑定查询的参数
                   var_dump($event->data['stmt'], $event->data['sql'], $event->data['params']);
                }
            ]
        ],
        [
            'method' => 'on',
            'params' => [
                \roach\orm\Connection::EVENT_AFTER_QUERY, function (\roach\events\EventObject $event){
                   //params为参数绑定查询的参数
                   var_dump($event->data['stmt'], $event->data['sql'], $event->data['params']);
               }
            ]
        ],
    ]
]);

第二种是手动绑定事件

<?php
/**此处不会去连接数据库,只是创建\roach\orm\Connection类而已,当真正执行sql的时候才会真正的去连接数据库
 * @var \roach\orm\Connection $db
 */
$db = \roach\Container::get('db');
$db->on(\roach\orm\Connection::EVENT_EXCEPTION_CONNECT, function(\roach\events\EventObject $event){
    //。。。打日志报警等各种处理,该事件触发了,并不一定所有的节点都不能用了
    //exception中是异常信息,config是节点配置
    var_dump($event->data['exception'], $event->data['config']);
});

$db->on(\roach\orm\Connection::EVENT_EXCEPTION_CONNECT_LOST, function (\roach\events\EventObject $event){
    //...各种操作
    //sql是指当执行某条sql时,mysql连接断了,但是会自动重连一次,如果重连失败,不会再触发该事件,会抛出异常
    var_dump($event->data['sql'], $event->data['exception']);
});

$db->on(\roach\orm\Connection::EVENT_BEFORE_QUERY, function (\roach\events\EventObject $event){
    //params为参数绑定查询的参数
    var_dump($event->data['stmt'], $event->data['sql'], $event->data['params']);
});

$db->on(\roach\orm\Connection::EVENT_AFTER_QUERY, function (\roach\events\EventObject $event){
    //params为参数绑定查询的参数
    var_dump($event->data['stmt'], $event->data['sql'], $event->data['params']);
});

我的主页

image

image

公众号

image

image