Fixtures and Foreign keys.

Is there a recommended way to manage fixtures when your tables are
normalized?

I'm trying to manage a database of stock, with {description}s of
{device}s, some of which have pictures, and I need to get the
description_id fields correct in the devices, and get the
fixtures to reference them correctly. Creating the YAML manually is
getting silly, the complexity level is higher than I'd expect to
write ruby code to do this [YAML is easy, it's the relationships
between the fixtures that isn't], and I can find nothing by means of
searching. I therefore conclude I'm using the wrong tools for this job,
or holding the right tools the wrong way round.

        Thank you,
        Hugh

Hi --

Hi --

>
> Is there a recommended way to manage fixtures when your tables are
> normalized?

        [...]

I don't know if this is helpful or, as I suspect, something you
already know and decided wasn't helpful, but fixture files are
preprocessed with ERb. So if you can bunch things into loops, rather
than type the IDs individually, that can save a lot of the work.

The only way I can think to use this is to load the other fixture
from which I want to extract the id fields, and then connect them up
using the un-normalized data. I'm not sure how to do that inside
another fixture. Is there some rails idiom along these lines? Is
this practice called something, so I can search the web for it?

David

        Thank you,
        Hugh

I do not use fixtures for the same reasons you indicate. I just have
empty fixture files and create test data programmatically in ruby.
Since you can not guarantee the load order of fixtures, and you can
not create bidirectional references, it seems a lame approach to the
creation of test data.

Michael

I do not use fixtures for the same reasons you indicate. I just have
empty fixture files and create test data programmatically in ruby.
Since you can not guarantee the load order of fixtures, and you can
not create bidirectional references, it seems a lame approach to the
creation of test data.

Web searches turned up lots of examples of this opinion. OTOH
'AWDR' states "It would be easy to assume that Rails treats testing
as an afterthought. Nothing could be further from the truth"
pp177-8, second edition.

So, I'm assuming that given Testing is standard practice, in
Databases normalization is standard practice ( == DRY), then there
must be some intention about how one is meant to do this, with the
supplied facilities. This list would seem to be the place to find
that out.

Failing that, are there any good techniques and/or tools that help
one do this programmatically, as you suggest?

Michael

        Thank you,
        Hugh

If you have problems with the order of things, you can tell the fixture (actually, YAML) that the data isn't an unordered Hash, but an omap (ordered map) so that the records are put into the database in the same order as they appear in the fixture.

--- !omap
- first:
     id: "1"
     other_fields: "stuff"
- second:
     id: "2"
     ...

You still have the problem of loading fixtures in the "right" order, of course.

-Rob

Rob Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com

If you have problems with the order of things, you can tell the
fixture (actually, YAML) that the data isn't an unordered Hash, but
an omap (ordered map) so that the records are put into the database
in the same order as they appear in the fixture.

Thank you. There are whole areas of YAML I'm unfamiliar with.

But my problem is more like this: In any abstraction of the data,
the individual id values don't matter, so long as they are cross-
referenced between the tables effectively. [They are only there
for the benefit of the database, in one way of thinking]. It's the
has_a, has_many, belongs_to_many concepts that i'm trying to 'wire up'
in the fixtures, between separate YAML structures in separate files
that is the problem.

[Going a little off-topic, and well out of my depth: would the criticisms
of fixtures in normalised tables be less if they were all in one YAML
doc, with YAML aliases and anchors doing the linkage?]

--- !omap
- first:
     id: "1"
     other_fields: "stuff"
- second:
     id: "2"
     ...

You still have the problem of loading fixtures in the "right" order,
of course.

Yes, I've seen articles discussing that, and at the moment I'm not
sure how to diagnose that problem.

-Rob

        Thank you,
        Hugh

I also tend to do generate my test data in ruby rather than fixtures.
I've also heard of people building a database (say, starting from
xyz_development copying to to xyz_fixture), and then copying that to
xyz_test instead of loading fixtures from yml

linoj

Normalization is fine, but let's say that there is a certain disregards
in certain circles of the Rails community for enforcing constraints in
the database.

That said, what I'm doing is this. I'm using various of the plugins from

  http://www.redhillonrails.org/

to be able to specify FK constraints in migrations *and* have them show
in schema.rb. Particularly important is the ability to mark FK
constraints as "deferrable". Also, I've patched
Fixtures#create_fixtures to issue

  SET CONSTRAINTS ALL DEFERRED

inside the transaction that loads the fixtures (see below). "Deferrable"
constraints can be "deferred", which means that they are only checked
when the enclosing transaction is about to be committed. This way, it
is possible to load fixture files in any order, even if there are
circular dependencies among tables. I have only tried this in
PostgreSQL, where it works like a charm.

Michael

Put somewhere in RAILS_ROOT/lib and load at startup

require 'active_record/fixtures'

Fixtures.class_eval do
  def self.create_fixtures(fixtures_directory, table_names, class_names
= {})
    table_names = [table_names].flatten.map { |n| n.to_s }
    connection = block_given? ? yield : ActiveRecord::Base.connection
    ActiveRecord::Base.silence do
      fixtures_map = {}
      fixtures = table_names.map do |table_name|
        fixtures_map[table_name] = Fixtures.new(connection,
File.split(table_name.to_s).last, class_names[table_name.to_sym],
File.join(fixtures_directory, table_name.to_s))
      end
      all_loaded_fixtures.merge! fixtures_map

      connection.transaction(Thread.current['open_transactions'].to_i ==
0) do
        connection.execute("SET CONSTRAINTS ALL DEFERRED")

        fixtures.reverse.each { |fixture|
fixture.delete_existing_fixtures }
        fixtures.each { |fixture| fixture.insert_fixtures }

        # Cap primary key sequences to max(pk).
        if connection.respond_to?(:reset_pk_sequence!)
          table_names.each do |table_name|
            connection.reset_pk_sequence!(table_name)
          end
        end
      end

      return fixtures.size > 1 ? fixtures : fixtures.first
    end
  end
end

> So, I'm assuming that given Testing is standard practice, in
> Databases normalization is standard practice ( == DRY), then there
> must be some intention about how one is meant to do this, with the
> supplied facilities. This list would seem to be the place to find
> that out.

Normalization is fine, but let's say that there is a certain disregards
in certain circles of the Rails community for enforcing constraints in
the database.

I've encountered this, too, in my searches :slight_smile:

That said, what I'm doing is this. I'm using various of the plugins from

  http://www.redhillonrails.org/

to be able to specify FK constraints in migrations *and* have them show
in schema.rb. Particularly important is the ability to mark FK
constraints as "deferrable". Also, I've patched
Fixtures#create_fixtures to issue

  SET CONSTRAINTS ALL DEFERRED

inside the transaction that loads the fixtures (see below). "Deferrable"
constraints can be "deferred", which means that they are only checked
when the enclosing transaction is about to be committed. This way, it
is possible to load fixture files in any order, even if there are
circular dependencies among tables. I have only tried this in
PostgreSQL, where it works like a charm.

I can see that will be useful. So what do you do about creating the
fixtures so that the FKs are all 'wired up' correctly? Are there
tools to help one do that? I'm thinking I don't want to re-invent
wooden rollers if there are wheels with pneumatic tires available.

Michael

Put somewhere in RAILS_ROOT/lib and load at startup

        [Code trimmed]

Thank you for that.

        Hugh

I don't know of any tools, I've done it manually. It worked ok when
creating the fixtures and writing the tests. However, I noticed that my
tests have become brittle. When adding a new test + fixtures, as a
result, completely unrelated tests might break.

Say, one test expects that a method returns 6 objects of class Foo. Now,
for another test I need to add a new Foo instance to the fixture.
Unfortunately, if that instance meets the relevant criteria, it may be
returned as the 7nth object by the method I tested somewhere else.

Using mock objects *may* be the solution, but I'm still a little
hesitant as I've found that it can result in a large amount of setup
stuff that is implicit in *real* AR objects.

Michael

Hugh,

The
    fixture_references plugin
    http://agilewebdevelopment.com/plugins/fixture_references

let's you write cleaner fixtures when it comes to foreign keys.

Alain Ravet