Automatic Logging of Datamapper Queries

Automatic logging of DataMapper queries

It seems that I missed a perfectly reasonable step. You need to place the DataMapper::Logger.new(STDOUT, :debug) before you make the connection. HT to @snusnu on #datamapper IRC.

Many useless queries done by DataMapper

The extra SELECTS are being made to check the :unique => true constraint on the Level class. This check seems to be being made on every database call.

One way to avoid this would be instead of using create when creating your model objects, which immediately saves the model in the database, use new and then save the whole object graph with a single call to save on a suitable object when they're all ready (see the docs on creating and saving models):

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

produces the output:

 ~ 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

So the models are not immediately persisted, but are all done together, and the uniqueness check is only done once.

Another possibility would be to set :auto_validation => false on the :name property.

This change produces this output (using 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

So there are still multiple calls to the database, but the check isn't made on each call (in fact it doesn't look like it's being made at all, so this rather defeats the object of using :unique => true in the first place).

Datamapper has n through and related queries

I've tried setting up working models that should be in-line with what you're trying to achieve and here's the result:

class A
  include DataMapper::Resource

  property :id, Serial

  has n, :bs
  has n, :cs, :through => :bs, :via => :c

  def active_cs
    cs.active
  end
end

class B
  include DataMapper::Resource

  property :id, Serial

  belongs_to :a
  belongs_to :c
end

class C
  include DataMapper::Resource

  property :id, Serial
  property :end_on, Date
  property :applied_to, Date

  has n, :bs

  def active
    all(:end_on => nil) + all(:conditions => [ "cs.end_on > applied_to" ])
  end
end

a = A.create
b = B.create(:a => a)
c = C.create(:b => b)

puts a.active_cs.inspect

Here's the SQL query:

SELECT "id", "end_on", "applied_to" FROM "cs" WHERE ("id" IN (SELECT "cs"."id" FROM "cs" INNER JOIN "bs" ON "cs"."id" = "bs"."c_id" INNER JOIN "as" ON "bs"."a_id" = "as"."id" WHERE ("bs"."a_id" = 1 AND "cs"."end_on" IS NULL)) OR "id" IN (SELECT "cs"."id" FROM "cs" INNER JOIN "bs" ON "cs"."id" = "bs"."c_id" INNER JOIN "as" ON "bs"."a_id" = "as"."id" WHERE ("bs"."a_id" = 1 AND (cs.end_on > applied_to)))) GROUP BY "id", "end_on", "applied_to" ORDER BY "id"

I'm not sure if that's exactly what you need - but I hope it helps.

Here's a link to gist with a working script: https://gist.github.com/916164

Benchmarking datamapper queries

Item.all doesn't actually do the query, it's delayed until needed. Use Item.all.to_a to force the query.

Datamapper resource becomes collection in session value

I would try casting the result of get_event to an Array. There might be an issue with storing DataMapper::Collection in the session.

session["lastLDEvent"] = ldg.get_event.to_a

If that doesn't work, you may need to store the IDs and then re-query the events.

session["lastLDEvent"] = ldg.get_event.map(&:id)

DataMapper case-insensitive unique validation

You can provide your own custom validation:

class User
  include DataMapper::Resource

  property :id, Serial
  property :username, String

  validates_with_method :username,
                        :method => :case_insensitive_unique_username

  def case_insensitive_unique_username
    User.first(conditions: ["username ILIKE ?", self.username]).nil?
  end
end

Note that ILIKE will only work with PostgreSQL, you will have to find out how to find records case insensitively with your specific adapter for yourself.

Strange DataMapper (0.10.2) error. Please help!

Looks like you have an old version of DataObjects (probably pre 0.10.0) installed. Please update to the latest version and I think this error will disappear. Depending on the database you use it's most likely either do_postgres or do_mysql you need to upgrade.

---

Related Topics