跳转至

2. 从数据库中读取

我们将在上一章项目 "heroes"的基础上，继续将我们的英雄存储在数据库中。这将使我们能够编辑我们的英雄，并在我们重新启动应用程序时保留更改。

对象-关系映射

关系型数据库管理系统（如PostgreSQL或MySQL）以表的形式存储数据。一个表代表某种实体，比如一个人或一个银行账户。每个表都有描述该实体属性的列，如名称或余额。表中的每一行都是该实体的一个实例，比如一个叫Bob的人或一个银行账户。

在像Aqueduct这样的面向对象框架中，我们有表、列和行的表示方法。一个类代表一个表，它的实例是行，实例属性是列值。ORM将数据库中的行转化为应用程序中的对象，并从对象转化为行。

Aqueduct 数据库 示例 #1 示例 #2
Class Table Person Bank Account
Instance Row A person named Bob Sally's Bank Account
Property Column Person's Name Bank Account Balance

In Aqueduct, each database table-class pairing is called an entity. Collectively, an application's entities are called its data model.

构建数据模型

在我们的 "heroes"应用程序中，我们将有一种类型的实体——"hero"。为了创建一个新的实体，我们继承ManagedObject<T>。创建一个新的目录lib/model/，然后在这个目录下添加一个新的文件，命名为hero.dart。添加以下代码：

import 'package:heroes/heroes.dart';

class Hero extends ManagedObject<_Hero> implements _Hero {}

class _Hero {
  @primaryKey
  int id;

  @Column(unique: true)
  String name;
}

这就声明了一个Hero实体。实体总是由两个类组成。

_Hero类是一个数据库表的直接映射。这个表的名字将与类的名字相同。_Hero类是数据库表的直接映射。在这个类中声明的每个属性都将在这个表中有一个相应的列。因此，_Hero表将有两列id和name。id列是这个表的主键（每个英雄的唯一标识符）。每个英雄的名字必须是唯一的。

另一个类，Hero，是我们在代码中使用的--当我们从数据库中获取英雄时，它们将是Hero的实例。

Hero类被称为实体的实例类型，因为这就是我们的实例。_Hero是实体的*表定义。除了描述数据库表之外，你不会将表定义用于其他任何事情。

一个实例类型必须实现它的表定义；这使我们的Hero具有_Hero的所有属性。一个实例类型必须扩展ManagedObject<T>，其中T也是表定义。ManagedObject<T>具有自动将对象传输到数据库并返回的行为（除其他外）。

瞬态属性

在实例类型中声明的属性并不存储在数据库中。这与表定义中的属性不同。例如，一个数据库表可能有 firstName和 lastName，但在某些地方，有一个 fullName属性是有用的。在实例类型中声明 fullName属性意味着我们可以很容易地访问全名，但我们仍然单独存储名字和姓氏。

定义上下文

我们的应用程序需要知道两件事来执行数据库查询：

  1. 数据模型（我们的实体集合）是什么？
  2. 我们连接的是哪个数据库？

这两个东西都是在应用程序第一次启动时设置的。在channel.dart中，添加一个新的属性context，并更新prepare()：

class HeroesChannel extends ApplicationChannel {
  ManagedContext context;

  @override
  Future prepare() async {
    logger.onRecord.listen((rec) => print("$rec ${rec.error ?? ""} ${rec.stackTrace ?? ""}"));

    final dataModel = ManagedDataModel.fromCurrentMirrorSystem();
    final persistentStore = PostgreSQLPersistentStore.fromConnectionInfo(
      "heroes_user", "password", "localhost", 5432, "heroes");

    context = ManagedContext(dataModel, persistentStore);
  }

  @override
  Controller get entryPoint {
    ...

ManagedDataModel.fromCurrentMirrorSystem()将找到我们所有的ManagedObject<T>子类，并将它们 "编译 "成一个数据模型。PostgreSQLPersistentStore获取数据库连接信息，它将用于连接和发送查询到数据库。这些对象一起被打包在一个ManagedContext中。

Configuring a Database Connection

This tutorial hardcodes the information needed to connect to a database. In a future chapter, we will move these values to a configuration file so that we can change them during tests and various deployment environments.

The context will coordinate with these two objects to execute queries and translate objects to and from the database. Controllers that make database queries need a reference to the context. So, we'll want HeroesController to have access to the context.

In heroes_controller.dart, add a property and create a new constructor:

class HeroesController extends ResourceController {
  HeroesController(this.context);

  final ManagedContext context;  
  ...

Now that HeroesController requires a context in its constructor, we need to pass it the context we created in prepare(). Update entryPoint in channel.dart.

@override
Controller get entryPoint {
  final router = Router();

  router
    .route("/heroes/[:id]")
    .link(() => HeroesController(context));

  router
    .route("/example")
    .linkFunction((request) async {
      return new Response.ok({"key": "value"});
  });

  return router;
}

Now that we've 'injected' this context into our HeroesController constructor, each HeroesController can execute database queries.

Service Objects and Dependency Injection

Our context is an example of a service object. A service encapsulates logic and state into a single object that can be reused in multiple controllers. A typical service object accesses another server, like a database or another REST API. Some service objects may simply provide a simplified interface to a complex process, like applying transforms to an image. Services are passed in a controller's constructor; this is called dependency injection. Unlike many frameworks, Aqueduct does not require a complex dependency injection framework; this is because you write the code to create instances of your controllers and can pass whatever you like in their constructor.

执行查询

Our operation methods in HeroesController currently return heroes from an in-memory list. To fetch data from a database instead of this list, we create and execute instances of Query<T> in our ManagedContext.

Let's start by replacing getAllHeroes in heroes_controller.dart. Make sure to import your model/hero.dart file at the top:

import 'package:heroes/heroes.dart';
import 'package:heroes/model/hero.dart';

class HeroesController extends ResourceController {
  HeroesController(this.context);

  final ManagedContext context;

  @Operation.get()
  Future<Response> getAllHeroes() async {
    final heroQuery = Query<Hero>(context);
    final heroes = await heroQuery.fetch();

    return Response.ok(heroes);
  }

...

Here, we create an instance of Query<Hero> and then execute its fetch() method. The type argument to Query<T> is an instance type; it lets the query know which table to fetch rows from and the type of objects that are returned by the query. The context argument tells it which database to fetch it from. The fetch() execution method returns a List<Hero>. We write that list to the body of the response.

Now, let's update getHeroByID to fetch a single hero from the database.

@Operation.get('id')
Future<Response> getHeroByID(@Bind.path('id') int id) async {
  final heroQuery = Query<Hero>(context)
    ..where((h) => h.id).equalTo(id);    

  final hero = await heroQuery.fetchOne();

  if (hero == null) {
    return Response.notFound();
  }
  return Response.ok(hero);
}

This query does two interesting things. First, it uses the where method to filter heroes that have the same id as the path variable. For example, /heroes/1 will fetch a hero with an id of 1. This works because Query.where adds a SQL WHERE clause to the query. We'd get the following SQL:

SELECT id, name FROM _question WHERE id = 1;

The where method uses the property selector syntax. This syntax is a closure that takes an argument of the type being queried, and must return a property of that object. This creates an expression object that targets the selected property. By invoking methods like equalTo on this expression object, a boolean expression is added to the query.

Property Selectors

Many query configuration methods use the property selector syntax. Setting up a keyboard shortcut (called a Live Template in IntelliJ) to enter the syntax is beneficial. A downloadable settings configuration for IntelliJ exists here that includes this shortcut.

The fetchOne() execution method will fetch a single object that fulfills all of the expressions applied to the query. If no database row meets the criteria, null is returned. Our controller returns a 404 Not Found response in that scenario.

We have now written code that fetches heroes from a database instead of from in memory, but we don't have a database - yet.

fetchObjectWithID, fetchOne() and Unique Properties

You can also fetch an object by its primary key with the method ManagedContext.fetchObjectWithID. When fetching with fetchOne, make sure the search criteria is guaranteed to be unique.

建立数据库

为了开发，你需要在本地机器上安装一个PostgreSQL服务器。如果你是在macOS上，请使用Postgres.app。这个本地的macOS应用程序可以管理机器上PostgreSQL服务器的启动和停止。对于其他平台，请参阅本页。

9.6 or Greater

The minimum version of PostgreSQL needed to work with Aqueduct is 9.6.

If you installed Postgres.app, open the application and select the + button on the bottom left corner of the screen to create a new database server. Choose a version (at least 9.6, but the most recent version is best), name the server whatever you like, and leave the rest of the options unchanged before clicking Create Server. Once the server has been created, click Start.

A list of databases available on this server will be shown as named, database icons. Double-click on any of them to open the psql command-line tool.

psql

For other platforms, psql should be available in your $PATH. You can also add Postgres.app's psql to your path with the directions here.

In psql, create a new database and a user to manage it.

CREATE DATABASE heroes;
CREATE USER heroes_user WITH createdb;
ALTER USER heroes_user WITH password 'password';
GRANT all ON database heroes TO heroes_user;

Next, we need to create the table where heroes are stored in this database. From your project directory, run the following command:

aqueduct db generate

This command will create a new migration file. A migration file is a Dart script that runs a series of SQL commands to alter a database's schema. It is created in a new directory in your project named migrations/. Open migrations/00000001_initial.migration.dart, it should look like this:

import 'package:aqueduct/aqueduct.dart';
import 'dart:async';

class Migration1 extends Migration {
  @override
  Future upgrade() async {
    database.createTable(SchemaTable(
      "_Hero", [
        SchemaColumn("id", ManagedPropertyType.bigInteger,
            isPrimaryKey: true, autoincrement: true, isIndexed: false, isNullable: false, isUnique: false),
        SchemaColumn("name", ManagedPropertyType.string,
            isPrimaryKey: false, autoincrement: false, isIndexed: false, isNullable: false, isUnique: true),
      ],
    ));
  }

  @override
  Future downgrade() async {}

  @override
  Future seed() async {}
}

In a moment, we'll execute this migration file. That will create a new table named _Hero with columns for id and name. Before we run it, we should seed the database with some initial heroes. In the seed() method, add the following:

@override
Future seed() async {
  final heroNames = ["Mr. Nice", "Narco", "Bombasto", "Celeritas", "Magneta"];

  for (final heroName in heroNames) {    
    await database.store.execute("INSERT INTO _Hero (name) VALUES (@name)", substitutionValues: {
      "name": heroName
    });
  }
}

Apply this migration file to our locally running heroes database with the following command in the project directory:

aqueduct db upgrade --connect postgres://heroes_user:password@localhost:5432/heroes

Re-run your application with aqueduct serve. Then, reload http://aqueduct-tutorial.stablekernel.io. Your dashboard of heroes and detail page for each will still show up - but this time, they are sourced from a database.

ManagedObjects and Migration Scripts

In our migration's seed() method, we executed SQL queries instead of using the Aqueduct ORM. It is very important that you do not use Query<T>, ManagedObject<T> or other elements of the Aqueduct ORM in migration files. Migration files represent an ordered series of historical steps that describe your database schema. If you replay those steps (which is what executing a migration file does), you will end up with the same database schema every time. However, a ManagedObject<T> subclass changes over time - the definition of a managed object is not historical, it only represents the current point in time. Since a ManagedObject<T> subclass can change, using one in our migration file would mean that our migration file could change.

查询参数和HTTP标头

在浏览器应用程序中，仪表板有一个用于搜索英雄的文本字段。当你在其中输入文本时，它将通过在GET /heroes后面附加一个查询参数来将搜索词发送到服务器。例如，如果你输入文本abc，它就会发出这样的请求：

GET /heroes?name=abc

Aqueduct Tutorial Run 4

Our Aqueduct application can use this value to return a list of heroes that contains the search string. In heroes_controller.dart, modify getAllHeroes() to bind the 'name' query parameter:

@Operation.get()
Future<Response> getAllHeroes({@Bind.query('name') String name}) async {
  final heroQuery = Query<Hero>(context);
  if (name != null) {
    heroQuery.where((h) => h.name).contains(name, caseSensitive: false);
  }
  final heroes = await heroQuery.fetch();

  return Response.ok(heroes);
}

You can re-run your Aqueduct application and use the search bar in the client application.

The @Bind.query('name') annotation will bind the value of the 'name' query parameter if it is included in the request URL. Otherwise, name will be null.

Notice that name is an optional parameter (it is surrounded by curly brackets). An optional parameter in an operation method is also optional in the HTTP request. If we removed the curly brackets from this binding, the 'name' query parameter would become required and the request GET /heroes without ?name=x would fail with a 400 Bad Request.

ResourceController Binding

There is even more to bindings than we've shown (like automatically parsing bound values into types like int and DateTime). For more information, see ResourceControllers.

Binding query and header parameters in a operation method is a good way to make your code more intentional and avoid boilerplate parsing code. Aqueduct is able to generate better documentation when using bindings.

下一章：存储数据