README

想为游戏网络工作吗？我们正在招聘！

Yolk Database

一个简单的数据库抽象层，通过PDO提供轻量级包装，易于使用。目前支持MySQL、Postgres和SQLite。

还包括简单的查询生成器和处理关系数据库中树结构的类，通过修改前序树遍历。

需求

此库需要PHP 5.4或更高版本以及Yolk Contracts包（gamernetwork/yolk-contracts）。

安装

可以通过Composer安装和自动加载，作为gamernetwork/yolk-database。

或者，下载一个发布版本或克隆此存储库，并将\yolk\database命名空间添加到自动加载器中。

许可证

Yolk Database是开源软件，许可协议为MIT许可

快速入门

use yolk\database\DSN;
use yolk\database\adapters\MySQLConnection;

// create a DSN
$dsn = DSN::fromString('mysql:///mydb');

// create a connection instance
$db = new MySQLConnection($dsn);

// get some data
$user = $db->getAssoc("SELECT * FROM users WHERE user_id = ?", 123);

// update some data
$updated = $db->execute(
    "UPDATE users SET last_seen = :now WHERE id = :id",
    [
        'id'  => 123,
        'now' => date('Y-m-d H:i:s'),
    ]
);

DSNs

DSN是指定数据库连接属性的对象。

常见属性包括

type - 要连接的数据库类型（mysql、postgres或sqlite）
host - 要连接的主机
port - 要连接的端口号
user - 认证的用户
pass - 用户的密码
db - 要连接的数据库模式名称
options - 驱动程序特定选项的数组

可以通过传递属性数组给构造函数创建DSN

$dsn = new DSN([
	'type' => 'mysql',
	'host' => 'localhost',
	'db'   => 'myapp',
]);

或通过使用URI调用静态的fromString()方法

$dsn = DSN::fromString('mysql://root:abc123@myapp.db/myapp?charset=utf-8');

ConnectionManager

ConnectionManager是一个处理多个数据库连接的服务。客户端可以注册一个连接或DSN在特定名称下，并在以后检索连接。

当注册DSN时，会自动创建一个合适的连接对象。

use yolk\database\ConnectionManager;
use yolk\database\adapters\SQLiteConnection;

// create a ConnectionManager instance
$m = new ConnectionManager();

// register a DSN
$m->add('mydb1', 'mysql:///mydb');

// register an existing connection
$db = new SQLiteConnection('sqlite://var/www/myapp/myapp.db');
$m->add('mydb2', $db);

// determine if a connection with the specified name exists
$exists = $m->has('mydb1');

// retrieve a previously added connection
$db = $m->get('mydb1');

// remove a connection from the manager and return it
// NOTE: this does not disconnect the connection
$db = $m->remove('mydb1');

查询方法参考

 // Execute a query and return the resulting PDO_Statement
$stmt = $db->query($statement, $params = []);

// Execute a query and return the number of affected rows
$rows = $db->execute($statement, $params = []);

// Execute a query and return all matching data as an array of associative arrays of matching rows
// Each row array has column names as keys
$db->getAll($statement, $params = []);

// Execute a query and return all matching data as an associative array,
// the first selected column is used as the array key
$db->getAssoc($statement, $params = []);

// Execute a query and return all matching data as a two-dimensioanl
// associative array, the first two selected columns are used as the array keys
$db->getAssocMulti($statement, $params = []);

// Execute a query and return the first matching row as an associative array
$db->getRow($statement, $params = []);

// Execute a query and return all values of the first selected column as an array
$db->getCol($statement, $params = []);

// Execute a query and return the value of the first column in the first array
$db->getOne($statement, $params = []);

上述方法接受以下参数

$statement: PDO_Statement实例或SQL字符串
$params: 绑定到语句的参数数组

查询参数可以是按名称绑定

$user_id = $db->getOne(
    "SELECT id FROM user WHERE type = :type AND name LIKE :name", 
    [
        'type' => 'NORMAL',
        'name' => 'Jim%',
    ]
);

或按位置绑定

$user_id = $db->getOne(
    "SELECT id FROM user WHERE type = ? AND name LIKE ?",
    ['NORMAL', 'Jim%']
);

如果查询只有一个参数，则可以直接指定，并将其自动转换为位置参数

$user_id = $db->getOne("SELECT id FROM user WHERE login = ?", 'jimbob');

其他方法

// Returns the ID of the last inserted row or sequence value.
$id = $db->insertId($name = '');

// Escape/quote a value for use in a query string
$db->quote($value, $type = \PDO::PARAM_STR);

// Escape/quote an identifier name (table, column, etc)
// Allows reserved words to be used as identifiers.
$db->quoteIdentifier('key');

// Execute a raw SQL string and return the number of affected rows.
// Primarily used for DDL queries
$db->rawExec($sql);

事务

// Begin a transaction
$db->begin();

// Commit the current transaction
$db->commit();

// Rollback the current transaction
$db->rollback();

// Determines if a transaction is currently active
$db->inTransaction();

查询生成器

对于SELECT、INSERT、UPDATE和DELETE，都有OO查询生成器。可以通过调用DatabaseConnection上的相应方法创建每个实例。

选择

$db->select()

   // accepts true (default) or false as argument
   ->distinct()

   // comma-separated list or array of column names
   ->cols('*')

   // table to select from
   ->from('table')

   // append a where clause - column, operator, value
   // multiple calls add additional clauses
   ->where('created', '>=', '2016-01-01')		

   // with two arguments, operator is assumed to be '='
   ->where('id', 123)

   // array of columns to group by
   ->groupBy(['type', 'status'])

   // second parameter specifies ascending (true) or descending (false)
   // multiple calls add additional clauses
   ->orderBy('column', true)

   // return result as associative array
   // can also use the other fetch* methods defined by DatabaseConnection
   ->fetchAssoc();

插入

$db->insert()

   // accepts true (default) or false as argument
   ->ignore()

   // table to insert to
   ->into('table')

   // item to insert as an associative array of column names/values
   ->item([
       'col1' => 'value1',
       'col2' => 'value1',
   ])

   // run the query
   ->execute();

更新

$db->insert()

   // accepts true (default) or false as argument
   ->ignore()

   // table to insert to
   ->into('table')

   // columns to update as an associative array of column names/values
   ->set([
       'col1' => 'value1',
       'col2' => 'value1',
   ])

   // same usage as for SELECT
   ->where('id', 123)

   // run the query
   ->execute();

删除

$db->delete()

   // table to insert to
   ->from('table')

   // same usage as for SELECT
   ->where('id', 123)

   // run the query
   ->execute();

gamernetwork / yolk-database

维护者

详细信息