[Rails] Generating SQL from model and not vice versa

[Adelle Hartley]

Hi all,

I have been following this discussion with some interest and thought I'd add
my (possibly irrelevant) take.

I suspect that my interests are slightly different from most of the Rails
developers I've seen on this list in that, ActiveRecord is the only part of
Rails that I am interested in at this stage, and I have not yet even touched
the presentation-layer capabilities of rails.

For most applications, I prefer the "model first" approach, and I think
ActiveRecord lends itself to that very well.

I can design a database using my favourite tool of the day - at the moment
it is FabForce DbDesigner http://www.fabforce.net/ - throw the model onto my
favourite server (which changes more frequently) and start coding against
that model.

(I am envious of developers that can start with the presentation layer -
they have something nice to show clients much sooner than I do).

Keeping the model in SQL (or any format external to the application) is OK
if you only have to deploy to 1 server.  It gets harder when you are
deploying an application to multiple clients and multiple servers.

So I find myself copying the SQL into my application (or into an installer
for my application) so that the client can set up the server for themselves.

As the application (and the underlying model) evolves, I add "ALTER TABLE"
logic to the installer so that clients can upgrade the server for themselves
too.

David Heinemeier Hansson wrote:
> In other words, you can't use auto-generating of the schema 
> for anything but creating the initial layout (unless you have 
> something terribly clever to generate alter table logic to 
> figure out a content-preserving diff). It's not going to help 
> your agile process where you only add another attribute as its needed.

Fortunately, the graphical tools do a decent job of generating the ALTER
TABLE code, but I don't think it is such a big ask for AR to be doing it for
me.

> THAT said, I am recognizing that other applications have 
> reversed priorities. Where you know from day 1 that you're 
> going to need to run on 5 databases. I'm already working on a 
> database migration script approach that'll provide an easy 
> way to migrate the database when you have the same 
> application installed across multiple locations. Here's an 
> example from the on-the-drawing-board API:
> 
> class AddReminders < Migration
>    def up
>      create_table("reminders") do |table|
>        table.field "account_id", :integer
>        table.field "content",    :text
>        table.field "remind_at",  :datetime
>        table.field "created_at", :datetime
>        table.field "sent",       :boolean, :default => false
>      end
> 
>      add_field "people", "reminders_count", :integer
>    end
> 
>    def down
>      drop_table("reminders")
>      remove_field("people", "reminders_count")
>    end
> end
> 
> It's pretty easy to see that this will require the 
> infrastructure needed to do model-to-database schema 
> generation as well. And I'm not rejecting a multi-paradigm 
> approach that would wrap this infrastructure in models to do 
> something like:
> 
> class User
>    attribute :content, :type => :text
>    attribute :remind_at, :created_at, :type => :datetime
>    attribute :sent, :type => boolean, :default => false
> 
>    belongs_to :account # would add attribute :account_id, 
> :type => :integer end

I would like to see some kind of meta-data model that accounts for the
changing nature of the database schemas.

I would also like to see a higher level description of schema-changes.

For example, a common type of schema change involves changing the
relationship between one type of object and one of it's attributes (fields)
from one-to-one to one-to-many.

In practical terms, the steps are:

1. Create a new table.
2. Move the data from a field (or fields) in the existing table into the new
table.
3. Drop the field(s) from the original table.
4. (optionally) Create an "IsDefault" field in the new table and set it to
True for all existing records (in order to preserve the one-to-one logic for
situations where it might be appropriate).

This set of changes could be (and should be IMO) represented as a single
operation.

eg:  ChangeRelationshipFrom121To12m(ExistingClass, NewClass,
AttributesToBeMoved, NameOfIsDefaultAttribute)

Another example of this type of operation would be creating a superclass
from a collection of previously unrelated classes:

1. Define a table to represent the parent class (this could be a new table
or an existing table).
2. Define a field that will indicate the class of each record stored in the
parent table (this field might be in the parent table, or it might be a
field in an existing table that the model uses for all classes).
3. Copy data from the child classes into the parent table, updating the
"Class" field for each record.
4. Drop fields from the child tables.

Again, I think this could be represented as a single operation:

CreateSuperclass(ChildClasses, [some more parameters go here])

A "would-be-nice" would be to optionally store a SchemaID with each record
so that the application could tell which version of the schema was current
at the time that the record was entered (a requirement of some data-entry
applications that I have worked on in the past has been that updates made to
existing records need to be validated according to the validation rules that
were current at the time that the record was originally created).

Adelle.