數據庫 · Pagekit 中文手冊

# 數據庫 <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)。