# 数据库 <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)。