flatphp/lightdb

simple light db component

v5.2.0 2021-03-30 16:12 UTC

Requires

• php: >=5.5
• ext-pdo: *

MIT

• Fred Yang

• database
• lightdb

README

Lightdb is a simple db component. 轻量DB库

Install 安装

composer require flatphp/lightdb

Usage 使用

use \Lightdb\DB;

// config 配置
$conf = array(
    'dsn' => 'mysql:host=localhost;dbname=testdb',
    'username' => 'root',
    'password' => '123456',
    'options' => [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8']
);

// init 初始化
DB::init($conf);

// raw sql 原生sql使用
$sql = 'select * from test where id>?';

// static use 静态门面模式使用
$data = DB::fetchAllIndexed($sql, 2);

$sql = 'insert into test(aa, bb) values(?, ?)';
$res = DB::execute($sql, [1, 2]);

// connection instance use
$conn = DB::conn();
$sql = 'select * from channel where id>?';
$data = $conn->fetchAll($sql, 2);

// fetch all to classes array
// 数据实例化对象
$data = $conn->fetchAllTo('MyModel', $sql, 2);

// fetch row to class
$data = $conn->fetchRowTo('MyModel', $sql, 2);

Support Fetch Method

Master-Slaves 主从使用

use \Lightdb\DB;

$conf = array(
    'dsn' => 'mysql:host=master;dbname=testdb',
    'username' => 'root',
    'password' => '123456',
    'options' => [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'],
    'slaves' => array(
        [
            'dsn' => 'mysql:host=slave1;dbname=testdb',
            'username' => 'root',
            'password' => '123456',
        ],
        [
            'dsn' => 'mysql:host=slave2;dbname=testdb',
            'username' => 'root',
            'password' => '123456',
        ]
    )
);

DB::init($conf);

// write to master db
$sql = 'insert into test(aa) values(1)';
$res = DB::execute($sql);

// select from slave db
$sql = 'select * from test';
$data = DB::fetchAll($sql);

// force read from master
// 强制从主库读取
$sql = 'select * from test';
$data = DB::master()->fetchAll($sql);

Multiple DB Instance 多DB实例

use \Lightdb\DB;

$conf = array(
    'db1' => array(...),
    'db2' => array(...),
);

$sql = 'select * from test';
DB::conn('db1')->fetchRow($sql);
DB::conn('db2')->fetchPairs($sql);

Transaction 事务

use \Lightdb\DB;

$transaction = DB::transaction();
$transaction->run(function(){
    // do something
}, function(){
    // do something after commit
}, function(){
    // do something after rollback
});

another way:

use \Lightdb\DB;
$transaction = DB::transaction();
$transaction->beginTransaction();
try {
    // do something
    $transaction->commit();
} catch (\Exception $e) {
    $transaction->rollback();
    throw $e;
}

Query Builder sql构建查询

Select

use Lightdb\DB;

$conn = DB::conn();

$conn->query()->table('users')->select('name, age')->where('age>?', 10)->fetchRow();

// read from master
$query = DB::query(['master' => true])->table('users as u')->select('u.id, u.name')
    ->leftJoin('score as s', 'u.user_id=s.user_id')
    ->where('class=?', 1)->orWhere('(age>12 AND sex=1)')
    ->orderBy('u.id DESC');
$result = $query->page(1, 10)->fetchAll();
$count = $query->count();

Insert

use Lightdb\DB;

DB::query()->table('users')->insert(array(
    'name' => 'peter',
    'age' => 12,
    'sex' => 1
));

Update

use Lightdb\DB;
use Lightdb\Raw;

DB::conn('other')->query()->table('users')->whereIn('name', ['peter', 'tom'])->update(array(
    'class' => 1,
    'point' => new Raw('point+1')
));

Delete

use Lightdb\DB;

// get delete sql
DB::query()->where('id=?', 10)->delete();

// execute
$query = DB::conn('another')->query();
$query->table('users')->whereNotIn('id', [1,2,3])->delete();
print_r($query->getLog());

Query Join Method

• leftJoin($table, $on, $bind = null)
• rightJoin($table, $on, $bind = null)
• innerJoin($table, $on, $bind = null)
• fullJoin($table, $on, $bind = null)

Query Where Method

• where($where, $bind = null)
• whereIn($field, array $values)
• whereNotIn($field, array $values)
• orWhere($where, $bind = null)
• orWhereIn($field, array $values)
• orWhereNotIn($field, array $values)

Query Limit and Offset

• limit($limit, $offset = 0)
• offset($offset)
• page($page, $page_size = 10)

Query Count

• count() (ignore limit and offset)

Execute

• insert(array $data)
• update(array $data)
• delete()