# 数据库
<p class="uk-article-lead">本章讨论关于数据库连接配置、创建数据表、在扩展中运行数据库脚本和手动构建数据库查询的基础知识。</p>
**Note** 要以舒适的方式将应用程序数据映射到数据库表中，推荐的方式是使用[Pagekit ORM](orm.md)。
## 配置
数据库凭证存储在 `config.php` 中。Pagekit 支持 `mysql`和 `sqlite`。
```
'database' => [
'connections' => [
'mysql' => [
'host' => 'localhost',
'user' => 'root',
'password' => 'PASSWORD',
'dbname' => 'DATABASE',
'prefix' => 'PREFIX_',
],
],
],
...
```
## 使用数据库前缀
Pagekit 的数据表都包含前缀。要在后台动态处理数据表，使用了 `@` 符号作为数据表前缀的占位符。作为惯例，应当以你的前缀作为数据表名的开头，例如 `foobar` 扩展的 _options_ 数据表：`@foobar_option`
## 数据库实用程序
使用数据库服务实用程序来管理数据库架构（如下例）
```
$util = $this['db']->getUtility();
```
## 检查表是否存在
```
if ($util->tablesExist(['@table1', '@table2'])) {
// tables exists
}
```
## 创建表
使用 `Utility::createTable($table, \Closure $callback)` 创建数据表，传递给回调函数第一个参数是一个 `Doctrine\DBAL\Schema\Table` 实例。
```
$util->createTable('@foobar_option', function($table) {
$table->addColumn('id', 'integer', ['unsigned' => true, 'length' => 10, 'autoincrement' => true]);
$table->addColumn('name', 'string', ['length' => 64, 'default' => '']);
$table->addColumn('value', 'text');
$table->addColumn('autoload', 'boolean', ['default' => false]);
$table->setPrimaryKey(['id']);
$table->addUniqueIndex(['name'], 'OPTION_NAME');
});
```
`$table` 对象是 `\Doctrine\DBAL\Schema\Table` 的实例。 在官方 Doctrine 文档中，你可以找到它的 [class 参考](http://www.doctrine-project.org/api/dbal/2.5/class-Doctrine.DBAL.Schema.Table.html) 。
创建列时，使用 `addColumn`，你可能想要查看可用的[数据类型](http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html) 和可用的[列选项](http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/schema-representation.html#portable-options) 。
创建表通常是在扩展中 `scripts.php` 的 `install` 钩子中完成的。在下一节，阅读更多关于数据库迁移的细节。
## 数据库迁移
数据库迁移（Database migrations）是在扩展程序的 `scripts.php` 中 `'updates'` 这部分定义的。完整的[scripts.php](https://github.com/pagekit/extension-hello/blob/master/scripts.php)例子可以在 Hello 扩展中找到。记得在 `composer.json` 中链接该文件，这样它才能切实地被执行：
```
"extra": {
"scripts": "scripts.php"
},
```
在 `scripts.php` 中，你可以钩住（hook into） 扩展程序生命周期的不同事件。
| 事件钩子 | 描述 |
| ----------- | -------------- |
| `install` | 扩展被安装时调用。通常在此创建数据表|
| `enable` | 在管理面板中启用扩展时调用|
| `uninstall` | 扩展被移除时调用。无论你创建了什么，都要在此处删除，比如，移除扩展的所有数据表|
| `updates` | 在扩展更新后运行任意代码。预期有一段代码会运行，它生成的一个数组每个键都是版本号。|
Example:
```
/*
* 运行所有可用的更新/Runs all updates that are newer than the current version.
*
*/
'updates' => [
'0.5.0' => function ($app) {
// 从 0.5.0以前的版本升级时执行
},
'0.9.0' => function ($app) {
// 从 0.9.0以前的版本升级时执行
},
],
```
### 修改现有的数据表
修改现有的数据表，使用基于 Doctrine DBAL 的现成工具。要为现有的数据表添加列，可以在你的扩展的 `scripts.php` 的一个 `update` 版本钩子中引入以下片段：
```
use Doctrine\DBAL\Schema\Comparator;
// ...
$util = App::db()->getUtility();
$manager = $util->getSchemaManager();
if ($util->tableExists('@my_table')) {
$tableOld = $util->getTable('@my_table');
$table = clone $tableOld;
$table->addColumn('title', 'string', ['length' => 255]);
$comparator = new Comparator;
$manager->alterTable($comparator->diffTable($tableOld, $table));
}
```
`$table` 对象是 `\Doctrine\DBAL\Schema\Table` 的一个实例。在官方文档可以找到它的[class 参考](http://www.doctrine-project.org/api/dbal/2.5/class-Doctrine.DBAL.Schema.Table.html)。
## 查询
有几种访问数据库的方式。Pagekit 提供在 MySQL 或 SQLite 基础上提供了一个抽线，因此不必再使用 PRO 或类似机制了。
### 1. 查询生成器/Query builder
[查询生成器](https://github.com/pagekit/pagekit/blob/develop/app/modules/database/src/Query/QueryBuilder.php) 允许以更舒适的方式创建查询。
Example:
```
$result = Application::db()->createQueryBuilder()->select('*')->from('@blog_post')->where('id = :id', ['id' => 1])->execute()->fetchAll();
```
#### 获取查询构建器对象
```
use Pagekit\Application;
// ...
$query = Application::db()->createQueryBuilder();
```
#### select和condition 基础知识
|方法 | 描述|
|-------- | -----------|
|`select($columns = ['*'])` | 为查询创建并添加 "select"|
|`from($table)` | 为查询创建并设置 "from"|
|`where($condition, array $params = [])` | 为查询创建并添加 "where"|
|`orWhere($condition, array $params = [])` | 为查询创建并添加 "or where"|
Example:
```
// 创建查询
$query = Application::db()->createQueryBuilder();
// 获取所有无评论的博客文章的标题和内容
$comments = $query
->select(['title', 'content'])
->from('@blog_post')
->where('comment_count = ?', [0])
->get();
```
#### 查询的执行
|方法 | 描述|
|-------- | -----------|
|`get($columns = ['*'])` | 执行查询并获取所有结果|
|`first($columns = ['*'])` | 执行查询并获取第一个结果|
|`count($column = '*')` | 执行查询并获取序号为"count" 的结果|
|`execute($columns = ['*'])` | 执行 "select" 查询|
|`update(array $values)` | 使用给定的值执行 "update"查询|
|`delete()` | 执行 "delete"查询|
#### 聚合函数
|方法 | 描述|
|-------------- | -----------|
|`min($column)` | 执行查询并获取最小结果|
|`max($column)` | 执行查询并获取最大结果|
|`sum($column)` | 执行查询并获取数值列的总数（总额）结果|
|`avg($column)` | 执行查询并获取平均值结果|
Example:
```
// create query
$query = $query = Application::db()->createQueryBuilder();
// determine total number of blog comments
$count = $query
->select(['comment_count'])
->from('@blog_post')
->sum('comment_count');
```
#### 高级查询方法
|方法 | 描述|
|-------- | -----------|
|`whereIn($column, $values, $not = false, $type = null)` | 创建并添加 "where in" 到查询中|
|`orWhereIn($column, $values, $not = false)` | 创建并添加 "or where in" 到查询中|
|`whereExists($callback, $not = false, $type = null)` | 创建并添加 "where exists" 到查询中|
|`orWhereExists(Closure $callback, $not = false)` | 创建并添加 "or where exists" 到查询中|
|`whereInSet($column, $values, $not = false, $type = null)` | 创建并添加 "where FIND_IN_SET" 等式到查询中|
|`groupBy($groupBy)` | 创建并添加 "group by" 到查询中|
|`having($having, $type = null)` | 创建并添加 "having" 到查询中|
|`orHaving($having)` | 创建并添加 "or having" 到查询中|
|`orderBy($sort, $order = null)` | 创建并添加 "order by" 到查询中|
|`offset($offset)` | 设置查询的偏移量，意思是查询结果并不以结果的第一个开始，而是按设定的索引值`$offset`的结果开始。这在分页时很好用。|
|`limit($limit)` | 设置查询的限制。`$limit` 定义要返回的结果的最大数目。|
|`getSQL()` | 获取查询的SQL|
#### Joins
|方法 | 描述|
|-------- | -----------|
|`join($table, $condition = null, $type = 'inner')` | 创建并添加 "join" 到查询中|
|`innerJoin($table, $condition = null)` | 创建并添加 "inner join" 到查询中.|
|`leftJoin($table, $condition = null)` | 创建并添加"left join" 到查询中|
|`rightJoin($table, $condition = null)` | 创建并添加"right join" 到查询中|
### 2. ORM 查询
如果在扩展中启用了 [ORM](orm.md)，你就可以使用模型类（model class）创建非常具有可读性的查询。
Example:
```
$result = Role::where(['id <> ?'], [Role::ROLE_ANONYMOUS])->orderBy('priority')->get();
```
以下方法是可用的（定义在[ModelTrait](https://github.com/pagekit/pagekit/blob/develop/app/modules/database/src/ORM/ModelTrait.php)中）。
|方法 | 描述|
|-------- | -----------|
|`create($data = [])` | 创建一个从 data 数组传递的模型的实例|
|`where($condition, array $params = [])` | 指定一个 where 条件。`$condition` 中标注的条件会被传入的参数 `$params`替代。返回一个 `QueryBuilder` 对象，这样就能将方法调用链接到更多具体的查询。Example: `User::where(['name = ?'], ['peter'])`|
|`find($id)` | 通过标识符检索模型实体|
|`findAll()` | 检索模型的所有实体|
|`save(array $data = [])` | 保存模型实体|
|`delete()` | 删除模型实体|
|`toArray(array $data = [], array $ignore = [])` | 以数组形式返回模型数据。作为 `$data` 参数传递所需的属性键的列表。作为 `$ignore`参数传递需要排除的属性键的列表。|
|`query()` | 返回一个 `ORM\QueryBuilder` 实例来使用这个类的任意方法。这个实例提供了常规查询构建器的所有方法，以及一些其他的方法，特别是 ORM 的方法。|
#### ORM查询构建器:额外的方法
|方法 | 描述|
|-------- | -----------|
|`get()` | 执行查询并获取所有的结果。|
|`first()` | 执行查询和获取第一个结果。|
|`related($related)` | 设置渴望被加载的关系|
|`getRelations()` | 获取查询的所有关系|
|`getNestedRelations($relation)` | 获取查询的所有嵌套关系|
Example:
```
$comments = Comment::query()->related(['post' => function ($query) {
return $query->related('comments');
}])->get();
```
### 3. 原生查询
查询数据库的最简单的方式是将原生查询发送到数据库。这基本上是PDO上的一个 wrapper。
```
$result = Application::db()->executeQuery('select * from @blog_post')->fetchAll();
$result = Application::db()->executeQuery('select * from @blog_post WHERE id = :id', ['id' => 1])->fetchAll();
```
## Insert
将数据插入到数据库，可以使用数据库连接实例，通过 `Application::db()` 来完成 (记得将 `use Pagekit\Application;` 添加到文件顶部 ).
使用方法 `insert($tableExpression, array $data, array $types = array())`
Example:
```
Application::db()->insert('@system_page', [
'title' => 'Home',
'content' => "<p>Hello World</p>",
'data' => '{"title":true}'
]);
```
在使用 [ORM](#ORM) 时，你只需要新建一个模型实例并调用 `save()` 方法。
## ORM
对于 Pagekit 中的 ORM，你可以绑定一个模型（Model）类到数据表。虽然它比起 QueryBuilder 需要多设置几行，但它让你从一堆工作中解放了双手，岂不美哉。使用 ORM 是推荐的管理数据库存储和检索应用程序数据的方式。更多信息阅读[ORM](224141)。
