DataMapper 完成的许多无用查询

Question

我有一个 DataMapper 问题（我将它与 Sinatra 一起使用）

我有一个非常基本的应用程序，有 3 个模型。 这是代码。

class Level
  include DataMapper::Resource
  property :id, Serial
  property :name, String, :required => true, :unique => true, :lazy => true
  property :description, Text, :lazy => true
  timestamps :at
end

class Player
  include DataMapper::Resource
  property :id, Serial
  property :name, String, :required => true, :lazy => true
  timestamps :at
  belongs_to :game
end

class Game
  include DataMapper::Resource
  property :id, Serial
  has n, :players
  belongs_to :level
  belongs_to :current_player, 'Player', :required => false
end

这是一个基本路线：

get '/' do
  DataMapper::logger.debug 'Creating level'
  level = Level.create(:name => "One")

  DataMapper::logger.debug 'Creating game'
  game = Game.create(:level => level)

  DataMapper::logger.debug 'Adding players'
  alice = Player.create(:name => 'Alice', :game => game)
  bob = Player.create(:name => 'Bob', :game => game)

  DataMapper::logger.debug 'Setting game current player'
  game.current_player = alice
  game.save
  'ok'
end

我的问题是，当我查看 DataMapper 日志文件时，我发现它做了很多无用的查询，我不明白为什么！

这是日志输出：

 ~ Creating level
 ~ (0.000062) SELECT "id" FROM "levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1
 ~ (0.002241) INSERT INTO "levels" ("name", "created_at", "updated_at") VALUES ('One', '2012-01-15T18:15:28+01:00', '2012-01-15T18:15:28+01:00')
 ~ Creating game
 ~ (0.000048) SELECT "id" FROM "levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1
 ~ (0.001747) INSERT INTO "games" ("level_id") VALUES (1)
 ~ Adding players
 ~ (0.000050) SELECT "id" FROM "levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1
 ~ (0.003762) INSERT INTO "players" ("name", "created_at", "updated_at", "game_id") VALUES ('Alice', '2012-01-15T18:15:28+01:00', '2012-01-15T18:15:28+01:00', 1)
 ~ (0.000085) SELECT "id" FROM "levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1
 ~ (0.001820) INSERT INTO "players" ("name", "created_at", "updated_at", "game_id") VALUES ('Bob', '2012-01-15T18:15:28+01:00', '2012-01-15T18:15:28+01:00', 1)
 ~ Setting game current player
 ~ (0.000078) SELECT "id" FROM "levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1
 ~ (0.001826) UPDATE "games" SET "current_player_id" = 1 WHERE "id" = 1

正如您所看到的，有很多针对级别模型的查询。我实在不明白DataMapper为什么要做这些。

预先非常感谢您的帮助。

PS：你可能认为这没什么大不了的，但我实际上在发布之前简化了模型结构。实际的模型更复杂，并且充满了那些无用的查询。

这是我的真实数据映射器日志文件的一小部分： 当我保存游戏模型的实例时会发生这种情况。

 ~ (0.001640) UPDATE "asd_games" SET "updated_at" = '2012-01-15T17:51:27+01:00', "current_player_id" = 3, "current_action_id" = 3 WHERE "id" = 1
 ~ (0.000079) SELECT "id", "body" FROM "asd_actions" WHERE "id" = 3 ORDER BY "id"
 ~ (0.000083) SELECT "id", "name", "description" FROM "asd_levels" WHERE "id" = 1 ORDER BY "id"
 ~ (0.000057) SELECT "id" FROM "asd_levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1
 ~ (0.000075) SELECT "id" FROM "asd_levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1
 ~ (0.000083) SELECT "id" FROM "asd_levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1
 ~ (0.000082) SELECT "id" FROM "asd_levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1
 ~ (0.000084) SELECT "id" FROM "asd_levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1

Answer 1

额外的 SELECTS 正在检查 :unique =>; Level 类上的 true 约束。似乎在每次数据库调用时都会进行此检查。

避免这种情况的一种方法是在创建模型对象时不使用 create ，这会立即将模型保存在数据库中，使用 new 然后保存整个对象图当合适的对象准备就绪时，对它们进行一次save调用（请参阅有关创建和保存模型的文档）：

DataMapper::logger.debug 'Creating level'
level = Level.new(:name => "One")

DataMapper::logger.debug 'Creating game'
game = Game.new(:level => level)

DataMapper::logger.debug 'Adding players'
alice = Player.new(:name => 'Alice', :game => game)
bob = Player.new(:name => 'Bob', :game => game)

DataMapper::logger.debug 'Setting game current player'
game.current_player = alice
game.save

产生输出：

 ~ Creating level
 ~ Creating game
 ~ Adding players
 ~ Setting game current player
 ~ (0.000074) SELECT "id" FROM "levels" WHERE "name" = 'One' ORDER BY "id" LIMIT 1
 ~ (0.001062) INSERT INTO "levels" ("name", "created_at", "updated_at") VALUES ('One', '2012-01-15T20:07:16+00:00', '2012-01-15T20:07:16+00:00')
 ~ (0.001460) INSERT INTO "games" ("level_id") VALUES (1)
 ~ (0.001279) INSERT INTO "players" ("name", "created_at", "updated_at", "game_id") VALUES ('Alice', '2012-01-15T20:07:16+00:00', '2012-01-15T20:07:16+00:00', 1)
 ~ (0.001592) UPDATE "games" SET "current_player_id" = 1 WHERE "id" = 1

因此模型不会立即持久化，而是一起完成，并且唯一性检查只进行一次。

另一种可能性是设置 :auto_validation => :name 属性上的 false。

此更改会产生以下输出（使用create）：

 ~ Creating level
 ~ (0.001162) INSERT INTO "levels" ("name", "created_at", "updated_at") VALUES ('One', '2012-01-15T20:13:51+00:00', '2012-01-15T20:13:51+00:00')
 ~ Creating game
 ~ (0.001958) INSERT INTO "games" ("level_id") VALUES (1)
 ~ Adding players
 ~ (0.001194) INSERT INTO "players" ("name", "created_at", "updated_at", "game_id") VALUES ('Alice', '2012-01-15T20:13:51+00:00', '2012-01-15T20:13:51+00:00', 1)
 ~ (0.001304) INSERT INTO "players" ("name", "created_at", "updated_at", "game_id") VALUES ('Bob', '2012-01-15T20:13:51+00:00', '2012-01-15T20:13:51+00:00', 1)
 ~ Setting game current player
 ~ (0.001369) UPDATE "games" SET "current_player_id" = 1 WHERE "id" = 1

因此仍然存在对数据库的多个调用，但不会对每个调用进行检查（事实上，它看起来不像是在所有，所以这首先就违背了使用 :unique => true 的目的）。

Answer 2

我遇到了同样的问题，我有一个用户和一个工作。作业属于用户。片段：

class User
  include DataMapper::Resource
  property :id,                 Serial,   writer: :protected, key: true
  property :email,              String,   required: true, length: (5..40),
                                          unique: true, format: :email_address
end

class Job
  include DataMapper::Resource
  property :id, Serial, key: true
  property :progress, Integer
  property :updated_at, DateTime
  belongs_to :user
end

每次保存作业时，都会运行两个查询：

~ (0.000421) SELECT `id` FROM `users` WHERE `email` = '[email protected]' ORDER BY `id` LIMIT 1
~ (0.001589) UPDATE `jobs` SET `progress` = 19, `updated_at` = '2013-12-19 06:32:43' WHERE `id` = 91

因为我确信我的更新不包含不良数据（用户输入不会污染任何内容），所以我能够使用 bang (!) 停止 SELECT 运行save http://datamapper.org/docs/create_and_destroy.html 的版本

-    save
+    save!

描述了实际内容当您使用 bang 方法而不是非 bang 方法时会发生这种情况，因此您需要检查它并查看它对于您的用例是否安全。