db/schema.rb

[Stephen Touset]

Reading the Rails commit logs, I see that db/schema.rb's purpose has
been clarified. It's intended to be the authoritative location for
your database schema, and rake tasks like db:reset use it to rebuild
the schema rather than running migrations.

This raises several questions for me.

1 ) What, then, is the preferred mechanism for "seed" data in the
database? Currently, many people use migrations to insert bootstrap
data. If schema.rb will be used for tasks like db:reset, none of this
data will be loaded.

2 ) What about those of us who specify additional options like charset
or table types? This information, last time I checked, doesn't seem to
be saved in the schema.rb.

3 ) Should there be yet another database-related Rake task for
development? Many of us create migrations, then modify them until
we're ready to commit. A simple "rake db:reset" used to re-run the
migrations from scratch, but the new semantics mean that migration
changes are never reflected.

Thanks in advance.

[Michael Koziarski]

> 1 ) What, then, is the preferred mechanism for "seed" data in the
> database? Currently, many people use migrations to insert bootstrap
> data. If schema.rb will be used for tasks like db:reset, none of this
> data will be loaded.

For a production database, migrations are the obvious option. I'll
probably get lynched but I just load fixtures to get my laptop's data
good to go...

> 2 ) What about those of us who specify additional options like charset
> or table types? This information, last time I checked, doesn't seem to
> be saved in the schema.rb.

Use the :sql schema format, perhaps you could supply patches to have
the rake tasks figure out which importer to run based on that setting?

> 3 ) Should there be yet another database-related Rake task for
> development? Many of us create migrations, then modify them until
> we're ready to commit. A simple "rake db:reset" used to re-run the
> migrations from scratch, but the new semantics mean that migration
> changes are never reflected.

rake db:drop db:create db:migrate will do what you need. if you think
there should be a task for this, come up with a name and a patch and
we can take a look.

--
Cheers

Koz

[Jim Meyer]

On Nov 13, 2007 8:17 PM, Stephen Touset <ste...@touset.org> wrote:
> Reading the Rails commit logs, I see that db/schema.rb's purpose has
> been clarified. It's intended to be the authoritative location for
> your database schema, and rake tasks like db:reset use it to rebuild
> the schema rather than running migrations.
>
> This raises several questions for me.
>
> 1 ) What, then, is the preferred mechanism for "seed" data in the
> database? Currently, many people use migrations to insert bootstrap
> data. If schema.rb will be used for tasks like db:reset, none of this
> data will be loaded.

Good question. I'm looking forward to the answer(s).

> 2 ) What about those of us who specify additional options like charset
> or table types? This information, last time I checked, doesn't seem to
> be saved in the schema.rb.

The way to get this is `rake db:structure:dump`, which dumps the
database structure to db/<rails_env>_structure.sql. More info in this
thread from a few days ago:

http:/xrl.us/baojk [groups.google.com/group/rubyonrails-core/...]

> 3 ) Should there be yet another database-related Rake task for
> development? Many of us create migrations, then modify them until
> we're ready to commit. A simple "rake db:reset" used to re-run the
> migrations from scratch, but the new semantics mean that migration
> changes are never reflected.

I added "db:recycle" to the GenerallyUseful plugin for just this
reason.

http://agilewebdevelopment.com/plugins/generally_useful

It only supports MySQL at present, but I'll get to a few others when
I've got some time. Meanwhile, I'll happily take patches for anyone's
favorite non-MySQL db.

Cheers!

--j

[Chris Cruft]

Stephen,
Your questions are a mirror of mine. I am surprised that the emphasis
placed on migrations for building the development and production
databases does not extend to the test database.

There is a rake task proposed on ticket 8389 (http://
dev.rubyonrails.org/ticket/8389) that addresses this problem by
offering a third option to the config.active_record.schema_format
setting. With the setting set to :migration, the test database is
built with migrations. I have used this hack and it works tolerably.
But I would really like to see it in edge to ensure that tools
suppliers know it is an option. Also, right now it sets the test
database to the same schema version as development whereas I think it
should go to the latest version.

It's great feeling knowing that my migrations are being exercised
every time I run tests. I have been burned in the past with early
migrations no longer being valid (due to DB upgrade) and not catching
that problem until it was time to go into production. With this hack,
I no longer worry about that potential problem.

I also have moved to loading "seed" data with migrations. I really
just load three records (a system admin user, a public group and some
global permissions for them). But I expect to put some more seed data
in this way -including Globalize languages and such.

Lastly, I too use non-default DB options and that is, of course, no
longer a problem.

I would recommend that you look at ticket 8389 and I would ask Michael
to look at it as well -it needs some "official" support (or a
statement to the contrary).

Regards,
Chris

[Chris Cruft]

Michael,
Please see me earlier comment and see ticket 8389 -that's the patch
for a task to support migration-based building of the test database.

-Chris

On Nov 14, 12:29 am, "Michael Koziarski" <mich...@koziarski.com>
wrote:

[James H.]

> 1 ) What, then, is the preferred mechanism for "seed" data in the
> database? Currently, many people use migrations to insert bootstrap
> data. If schema.rb will be used for tasks like db:reset, none of this
> data will be loaded.

In my app I made a folder under vendor called "data". I write rake
tasks specifically for loading this data. This way I can load my data
willy-nilly, get new members on the team up to speed quickly, and
there's one easy-to-find place for all my data import scripts: lib/
tasks.

James

[ara.t.howard]

we are using timestamped migrations and the first one is stamped like
this:

00000000000042_schema.rb

thus, it's before any other timestamped migration, most of which look
like this

20071103035131_globalize_migration.rb

the '42' migration looks for a file, db/dump.gz, which iff found it
assumed to be a binary dump of the db. this will typically be dumped
from production, for instance. when found it's loaded. this has the
side effect of also loading the schema_info table so it short
circuits all the other migrations if, and only if, they were run on
the production db. if you have a newer migration locally then that
will be run.

we also have custom db:down and db:up tasks which drop the db, create
it, and then run db:migrate.

so far this is working quite well.

regards.

a @ http://codeforpeople.com/
--
we can deny everything, except that we have the possibility of being
better. simply reflect on that.
h.h. the 14th dalai lama

[Pedro Mariano Belo]

> > 1 ) What, then, is the preferred mechanism for "seed" data in the
> > database? Currently, many people use migrations to insert bootstrap
> > data. If schema.rb will be used for tasks like db:reset, none of this
> > data will be loaded.

There's a new plugin to fill that "gap", yaml_db:
http://blog.heroku.com/archives/2007/11/23/yamldb_for_databaseindependent_data_dumps/

[Jeremy McAnally]

I typically don't use migrations for "seed" data; instead, I use
something like "rake db:bootstrap" or something like that.

Is there a reason you prefer migrations?

--Jeremy

--
http://www.jeremymcanally.com/

My books:
Ruby in Practice
http://www.manning.com/mcanally/

My free Ruby e-book
http://www.humblelittlerubybook.com/

My blogs:
http://www.mrneighborly.com/
http://www.rubyinpractice.com/

[Josh Knowles]

On 11/13/07, Stephen Touset <ste...@touset.org> wrote:
> 1 ) What, then, is the preferred mechanism for "seed" data in the
> database? Currently, many people use migrations to insert bootstrap
> data. If schema.rb will be used for tasks like db:reset, none of this
> data will be loaded.

I've written a simple rake task that loads any .rb files found within
the RAILS_ROOT/db/fixtures/RAILS_ENV directory. I've bundled it up as
a plugin if anyone else wants to try it out:
http://code.google.com/p/db-populate/. README, examples, etc. to come
soon.

--
Josh Knowles
phone: 509-979-1593
email: joshk...@gmail.com
web: http://joshknowles.com

[Chris Cruft]

Josh's rake task looks like it provides a nice ability to load data
via Rake. But there is an inconsistent message being sent by Rails:

Use Migrations. Use Migrations. Use Migrations.

Except for testing, where we'll use a limited, one-off method (schema
dumper) ensuring that you'll never know if your migrations are broken
until it's too late.

Why? Migrations are perfectly capable of handling the test DB setup:
like schema dumper they are database-neutral, but they also support
seed data loading and where desired, DB-specific commands. Plus, they
are the blessed approach to building development and production
databases and could thus benefit from regular exercise during testing
as well.

Is there a good reason for the avoidance of migrations for building
the test DB, or is the use of schema dumper just a vestige of days
gone by? I continue to be surprised by the lack of comments from the
core team on this thread (Koz's early comment being the only one) and
the lack of feedback on ticket 8389.

For those of you that have bought into migrations like I have, add
your thoughts (or improvements) to the patch on ticket 8389.

-Chris


On Nov 28, 5:22 pm, "Josh Knowles" <joshknow...@gmail.com> wrote:

> On 11/13/07, Stephen Touset <step...@touset.org> wrote:
>
> > 1 ) What, then, is the preferred mechanism for "seed" data in the
> > database? Currently, many people use migrations to insert bootstrap
> > data. If schema.rb will be used for tasks like db:reset, none of this
> > data will be loaded.
>
> I've written a simple rake task that loads any .rb files found within
> the RAILS_ROOT/db/fixtures/RAILS_ENV directory. I've bundled it up as
> a plugin if anyone else wants to try it out:http://code.google.com/p/db-populate/. README, examples, etc. to come
> soon.
>
> --
> Josh Knowles
> phone: 509-979-1593

> email: joshknow...@gmail.com
> web: http://joshknowles.com

[Michael Koziarski]

> Josh's rake task looks like it provides a nice ability to load data
> via Rake. But there is an inconsistent message being sent by Rails:
>
> Use Migrations. Use Migrations. Use Migrations.
>
> Except for testing, where we'll use a limited, one-off method (schema
> dumper) ensuring that you'll never know if your migrations are broken
> until it's too late.

You run your migrations against your development database, it's
hardly as bad as you're implying :).

> Is there a good reason for the avoidance of migrations for building
> the test DB, or is the use of schema dumper just a vestige of days
> gone by? I continue to be surprised by the lack of comments from the
> core team on this thread (Koz's early comment being the only one) and
> the lack of feedback on ticket 8389.

Migrations are designed for cases where the data in your db matters,
with cases like a test database you might as well just drop and
recreate every table.

But more than that, *most* of the migrations in my current
application (200ish out of 300ish) are for maintaining *data*, they
don't make schema changes at all. Things like fixing up data after a
bug messed it up, changing the prices of various plans, altering
lookup data Etc. My test database doesn't need to know about them.
Schema.rb works fine, I'm not sure what additional benefits you're
looking for.

> For those of you that have bought into migrations like I have, add
> your thoughts (or improvements) to the patch on ticket 8389.
>
> -Chris
>
>
> On Nov 28, 5:22 pm, "Josh Knowles" <joshknow...@gmail.com> wrote:
> > On 11/13/07, Stephen Touset <step...@touset.org> wrote:
> >
> > > 1 ) What, then, is the preferred mechanism for "seed" data in the
> > > database? Currently, many people use migrations to insert bootstrap
> > > data. If schema.rb will be used for tasks like db:reset, none of this
> > > data will be loaded.
> >
> > I've written a simple rake task that loads any .rb files found within
> > the RAILS_ROOT/db/fixtures/RAILS_ENV directory. I've bundled it up as
> > a plugin if anyone else wants to try it out:http://code.google.com/p/db-populate/. README, examples, etc. to come
> > soon.
> >
> > --
> > Josh Knowles
> > phone: 509-979-1593
> > email: joshknow...@gmail.com
>
> > web: http://joshknowles.com
> >
>

--
Cheers

Koz

[sishen]

On Nov 30, 2007 4:53 AM, Michael Koziarski <mic...@koziarski.com> wrote:

> Josh's rake task looks like it provides a nice ability to load data
> via Rake.  But there is an inconsistent message being sent by Rails:
>
> Use Migrations.  Use Migrations.  Use Migrations.
>
> Except for testing, where we'll use a limited, one-off method (schema
> dumper) ensuring that you'll never know if your migrations are broken
> until it's too late.

You run your migrations against your development database,  it's
hardly as bad as you're implying :).

> Is there a good reason for the avoidance of migrations for building
> the test DB, or is the use of schema dumper just a vestige of days
> gone by?  I continue to be surprised by the lack of comments from the
> core team on this thread (Koz's early comment being the only one) and
> the lack of feedback on ticket 8389.

Migrations are designed for cases where the data in your db matters,
with cases like a test database you might as well just drop and
recreate every table.

 

Many times drop and recreate the database is not works, especially when you have some functions which
is beyon the ability of active_records, such as view schema, contrib function in postgresql, etc.

I have wrote a post about "testing with postgresql". Hope it's meaningful.

http://sishen.lifegoo.com/?p=50

[Michael Koziarski]

> Many times drop and recreate the database is not works, especially when you
> have some functions which
> is beyon the ability of active_records, such as view schema, contrib
> function in postgresql, etc.
>
> I have wrote a post about "testing with postgresql". Hope it's meaningful.
>
> http://sishen.lifegoo.com/?p=50

You can set the schema_format to :sql and it should work well.
--
Cheers

Koz

[sishen]

You will know it only works partially if you have read my post, :)
And also, set the schema_format to :sql is a must.

--

Cheers

Koz

[Chris Cruft]

>>"You run your migrations against your development database, it's hardly as bad as you're implying :)."

True, but... In development, you run the new migrations -you don't
typically tear down the DB and run all of them from the start.
Consequently, older migrations become stale and broken (due to DB
upgrades, model changes, etc.) -and you don't learn about that until
it's too late. I maintain that the current functionality leaves no
reasonable means of keeping migrations up-to-date and tested in their
entirety. It doesn't sound like you use your migrations in their
entirety to build a DB so perhaps you have not been bitten by this
problem.

I see this issue is distilling into three separate arguments for
migration-based test DB setup:

1. migrations (in their entirety) need testing too
2. migrations are the most capable means of building a database
(including test).
3. Rails is sending an inconsistent message: migrations for
development and production, but schema dumper for test.

What's the argument for the schema dumper?

This thread is probably reaching the end of it's useful like, but I
would like to close with a request: please consider endorsing (test,
+1) ticket 8389 or something similar.

Chris

On Nov 29, 3:53 pm, "Michael Koziarski" <mich...@koziarski.com> wrote:
> > Josh's rake task looks like it provides a nice ability to load data
> > via Rake. But there is an inconsistent message being sent by Rails:
>
> > Use Migrations. Use Migrations. Use Migrations.
>
> > Except for testing, where we'll use a limited, one-off method (schema
> > dumper) ensuring that you'll never know if your migrations are broken
> > until it's too late.
>

>

[Michael Koziarski]

> I see this issue is distilling into three separate arguments for
> migration-based test DB setup:
>
> 1. migrations (in their entirety) need testing too

I think the fact that I immediately disagree with this is a sign that
we're facing two distinct problems which require two distinct
solutions.

If you're building a single application with a single database
installation, you will *never* need to run your migrations from start
to finish. Basecamp is up and running, no-one's out there
downloading it and setting up a new installation. The old migrations
are simply interesting relics of previous schema versions. Of no
interest to anyone.

By contrast if you're constantly installing new versions of your
application all over the place (say, mephisto) then your needs are
completely different.

> 2. migrations are the most capable means of building a database
> (including test).
> 3. Rails is sending an inconsistent message: migrations for
> development and production, but schema dumper for test.
>
> What's the argument for the schema dumper?
>
> This thread is probably reaching the end of it's useful like, but I
> would like to close with a request: please consider endorsing (test,
> +1) ticket 8389 or something similar.

I think that we're well past the point of getting some ROI on this
thread, and it's definitely not something that's going to change in
the short term. Let's get a plugin out of 8389 and see how people like
using it. We can re-evaluate the pros and cons when it comes time to
do 2.1.

--
Cheers

Koz