Rails - Feb 2005 - Generating SQL from model and not vice versa

Hi,

I have been studying Ruby and Rails only for a couple of days so far, so 
bear with me if my question sounds stupid ;-)

After going though the tutorials I am very impressed with the 
productivity of Rails. But one thing seems strange to me: It looks like 
the preferred way of modeling is to create the database definition first 
and then generate a Ruby/Rails model from it.

I am used to working the other way around - getting the model right 
first, and then concentrate on how to present it (Web, GUI, etc). The 
database is reduced to be merely a persistence store for the objects, 
but since all access is done through the model it doesn''t hurt. With 
this approach, some precompiler typically generates SQL scripts that 
generate the database definition the model expects.

Is this something Rails supports, and if yes, how could I get started 
with this approach?

TIA

Steffen

PS: I am aware of Prevailer, but having all objects in memory all the 
time isn''t possible in all cases.

Steffen Uhlig:
> I have been studying Ruby and Rails only for a couple of days so far, so 
> bear with me if my question sounds stupid ;-)
> 
> After going though the tutorials I am very impressed with the 
> productivity of Rails. But one thing seems strange to me: It looks like 
> the preferred way of modeling is to create the database definition first 
> and then generate a Ruby/Rails model from it.
> 
> I am used to working the other way around - getting the model right 
> first, and then concentrate on how to present it (Web, GUI, etc). The 
> database is reduced to be merely a persistence store for the objects, 
> but since all access is done through the model it doesn''t hurt.
With
> this approach, some precompiler typically generates SQL scripts that 
> generate the database definition the model expects.
> 
> Is this something Rails supports, and if yes, how could I get started 
> with this approach?
I heard that this is something Nitro / Og [1] supports, but haven''t 
actually tested this myself. Rails doesn''t, but Rails is not the only 
contender for web app frameworksm though. Only the one with the biggest 
publicity ;) I don''t know why, but it seems like David hasn''t
planned
for this. From my perspective it would be useful to have this kind of 
functionality [2]. Such a feature might make the models more complex, 
because you would have to define the fields there. I would indeed like 
to hear David''s opinion on this. Nitro does this by doing this (from
the
docs)

class User
   prop_accessor :name, String
   prop_accessor :level, Fixnum
   ...
end

Now the schema for user gets automatically generated. Isn''t that a 
useful feature. What are its drawbacks from the Rails perspective?


Sascha

[1] gem install nitro
[2] I really hate fiddling with the database schema.

> 
> class User
>    prop_accessor :name, String
>    prop_accessor :level, Fixnum
>    ...
> end
> 
> Now the schema for user gets automatically generated. Isn''t that a
> useful feature. What are its drawbacks from the Rails perspective?
* DRY violation
* Extra typing
* Uses the worst tool for the job ( text ) as opposed to the best (
cocoamysql, phpmyadmin etc )

SQL basically *is* code. Its a language made for describing database
schemata. The best rails could ever hope to offer would be equally
good support for creating databases as SQL does but without the tools
to make it pragmatic.

Breaking from this model you described, which is what most ORM
packages follow, is one of the big innovations AR brought in my book.

You can see what I mean when you compare the Trails and Rails quicktime movies. 

-- 
Tobi
http://www.snowdevil.ca - Snowboards that don''t suck
http://www.hieraki.org   - Open source book authoring
http://blog.leetsoft.com - Technical weblog

Tobias Luetke schrieb:
>>class User
>>   prop_accessor :name, String
>>   prop_accessor :level, Fixnum
>>   ...
>>end
>>
>>Now the schema for user gets automatically generated. Isn''t
that a
>>useful feature. What are its drawbacks from the Rails perspective?
I don''t fully understand so let me ask a couple of questions on the 
point you mention.
> * DRY violation
Where is the duplication? I have a hard time coming up with the 
circumstances where you have to duplicate.
> * Extra typing
Do you mean typing on the keyboard or working around the datatypes in Ruby?

In the first case. The typing is either in the database administration 
program or in the Ruby script.

Datatypes are already addressed by Rails. It also does type conversions. 
Couldn''t the existing logic be used to extend the framework in this 
direction?
> * Uses the worst tool for the job ( text ) as opposed to the best (
> cocoamysql, phpmyadmin etc )
> SQL basically *is* code. Its a language made for describing database
> schemata. The best rails could ever hope to offer would be equally
> good support for creating databases as SQL does but without the tools
> to make it pragmatic.
This is exactly what Rails does right now, isn''t it? And amazingly 
efficient I might add. Also, there are a gazillion things you cannot do 
with AR because there is so much different functionality in the 
different RDBMS out there. With some RDSMSs you can probably write your 
whole application in SQL. Automating the most common queries and 
offering an easy way to circumvent the rest by issuing a real sql query 
and / or mixing sql into the code. In my opinion this is exactly how it 
should be done. The question is, can this be done for schema creation 
too? Nitro / Og does it. I don''t know how well, but maybe it is
possible
in Rails too.
> Breaking from this model you described, which is what most ORM
> packages follow, is one of the big innovations AR brought in my book.
I wouldn''t call it an innovation because nothing new has been 
discovered. Only something known has been neglected. This is fine and I 
get the whole ''say no by default'' philosophy. But in the long
run and in
a lot of cases this can not really be upheld, can it? I remember the 
early discussions (when there were maximum 5-10 ppl in #rubyonrails) 
that so much things simple wouldn''t make sense. Components, Modules, 
other template engines, whatever, now they are there. Not everybody uses 
them. That''s fine. But you can if you want to. So maybe ''say
no by
default'' really means ''say no by default 3 times, then say
yes''. It
certainly seems that way and I am glad about it ;)

And, you may have seen other ORM packages in other languages, but what 
about such a thing in Ruby? Are there any besides Nitro / Og. Have you 
tried Og and come to that conclsion (I haven''t, just curious)? Would
you
have thought that such a beautiful web framework could exist in another 
language? Trails proves that it can never be as elegant as Rails IMO 
because Java is not Ruby. Thanks to David who trusted Ruby enough to 
really make this great framework. I believe if he wouldn''t have found 
confidence in Ruby there wouldn''t be Rails today. Basecamp would 
probably run on PHP.
> You can see what I mean when you compare the Trails and Rails quicktime
movies.
I don''t think the two can be compared in this regard. Java just
doesn''t
have these strong introspection / reflection abilities that Ruby has. 
Again, you have to type the sql schema _anyway_. Let it be into a 
database program or into the Rails script. There is only a difference of 
location. If it were possible I''d like to do it in Ruby, like
everything
else. If there were really a hard reason for not doing it I''d have no 
problem accepting that. Mostly I am just curious wether this can help. I 
personaly think it is just amazing if you type in your fields into your 
class and all over sudden they get automatically created and updated in 
the database.

Certainly this should be an addon like the component support. You know, 
components are there. You don''t have to use them if they don''t
fit your
needs or style. So, by default, the schema doesn''t get generated. When 
you use your first prop_accessor, it does.

Sascha

On Sunday 27 February 2005 05:54 pm, Tobias Luetke
wrote:
> > class User
> >    prop_accessor :name, String
> >    prop_accessor :level, Fixnum
> >    ...
> > end
> >
> > Now the schema for user gets automatically generated. Isn''t
that a
> > useful feature. What are its drawbacks from the Rails perspective?
>
> * DRY violation
Actually, no.  DRY states that there is only one /authoritative/ source of the 
information.  You declare the Ruby code to be authoritative and generate 
everything from that.

Dave Thomas actually did the above and talked about it in his RubyConf 2001 
presentation (http://www.zenspider.com/dl/rubyconf2002/SummerVacation.tgz, 
see slide 27). It seemed to be quite successful for him.

I''m not saying its the best approach, but there are certainly some
upsides to
it as well.

-- 
-- Jim Weirich    jim-Fxty1mrVU9GlFc2d6oM/ew@public.gmane.org    
http://onestepback.org
-----------------------------------------------------------------
"Beware of bugs in the above code; I have only proved it correct, 
not tried it." -- Donald Knuth (in a memo to Peter van Emde Boas)

Tobias Luetke wrote:
>>class User
>>   prop_accessor :name, String
>>   prop_accessor :level, Fixnum
>>   ...
>>end
>>
>>Now the schema for user gets automatically generated. Isn''t
that a
>>useful feature. What are its drawbacks from the Rails perspective?
> 
> 
> * DRY violation
> * Extra typing
> * Uses the worst tool for the job ( text ) as opposed to the best (
> cocoamysql, phpmyadmin etc )
Where is the DRY violation, if the code is the only place you define the 
tables?  In Og, the table defs SQL is dynamically generated; in Rails it 
is the reverse.  Granted, someone might change the tables outside of the 
code, and things will break, but that''s true of Rails, too.  If you 
change the Rails tables but not the code that expects certain table 
structures, then things fall on the floor.

Extra typing? Small price to pay for being able to think in terms of 
business objects and code rather than have to work out the tables first.
> 
> SQL basically *is* code. Its a language made for describing database
> schemata. 
Right, which is the drawback.  If your app is largely data-centric, then 
working out the tables first via SQL  may be best.  If the app is more 
concerned with the interactions of business objects, with a persistence 
layer on the back end, then code-driven table creation may be the most 
natural.

With the Og approach, you can sketch out an application using Ruby, and 
the persistence layer is just magically handled for you. As you adjust 
your objects to suit your application, the tables change with you.

It may offer greater design freedom with less coupling to early table 
design decisions.

James

* James Britt <james.britt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> [0255
02:55]:
> Tobias Luetke wrote:
> >>class User
> >>  prop_accessor :name, String
> >>  prop_accessor :level, Fixnum
> >>  ...
> >>end
> >>
> >>Now the schema for user gets automatically generated.
Isn''t that a
> >>useful feature. What are its drawbacks from the Rails perspective?
> >* DRY violation
> >* Extra typing
> >* Uses the worst tool for the job ( text ) as opposed to the best (
> >cocoamysql, phpmyadmin etc )
 
> Where is the DRY violation, if the code is the only place you define the 
> tables?  In Og, the table defs SQL is dynamically generated; in Rails it 
> is the reverse.  Granted, someone might change the tables outside of the 
> code, and things will break, but that''s true of Rails, too.  
When I first looked at Rails the ''db is definitive'' concept
stuck in my craw a
bit too (largely because I suck at SQL). 
I also did''nt like the idea of having my app defined in two languages.

But when you think of a Ruby <-> SQL mapping, where one side is responding
to changes
in the other, which do you think is more dynamic ? :)

> If you 
> change the Rails tables but not the code that expects certain table 
> structures, then things fall on the floor.
But with AR ''expecting table structures'' =  just calling
methods or using accessors.

I''ve found that adding/dropping table columns has required no changes
to my ruby code,
whereas I wouldn''t expect an SQL backend to be dynamic enough to
''ALTER TABLE'' on the
fly (and certainly not in a portable way).

What I''ve seen of Og (from the Wee tutorial videos) shows that it does
what the OP
wants, so I''d recommend he try that. But there does seem to be a couple
of ''if you
do X you need to drop/recreate your table'' sort of caveats involved..
> It may offer greater design freedom with less coupling to early table 
> design decisions.
I''m not expert in ORM, but I''ve rewritten my db design three
or four times while
writing my first Rails app and I''ve only had to tweak the odd unit
test. It really
does seem to be more ''agile'' to be having Ruby respond to SQL
in my experience.
 
-- 
''Interesting. No, wait, the other thing - Tedious.''
		-- Bender
Rasputin :: Jack of All Trades - Master of Nuns

An advantage I can clearly see from doing the schema in ruby code, is 
that it can more easily be incorporated into version control. One thing 
that I''ve seen in every rails app I''ve dowloaded to try, is
some scripts
in the db folder, but they were most often for mysql or pgsql, which 
means they were somewhat platform specific.
If they would have been done in code, apart from avoiding the need to 
run certain scripts before trying the app, it would be possible to run 
them in every rdbm platform compatible with rails

that''s my 0,02€

Victor

Tobias Luetke wrote:
>>class User
>>   prop_accessor :name, String
>>   prop_accessor :level, Fixnum
>>   ...
>>end
>>
>>Now the schema for user gets automatically generated. Isn''t
that a
>>useful feature. What are its drawbacks from the Rails perspective?
> 
> 
> * DRY violation
> * Extra typing
> * Uses the worst tool for the job ( text ) as opposed to the best (
> cocoamysql, phpmyadmin etc )
> 
> SQL basically *is* code. Its a language made for describing database
> schemata. The best rails could ever hope to offer would be equally
> good support for creating databases as SQL does but without the tools
> to make it pragmatic.
> 
> Breaking from this model you described, which is what most ORM
> packages follow, is one of the big innovations AR brought in my book.
> 
> You can see what I mean when you compare the Trails and Rails quicktime
movies.
>

> From: Victor Jalencas
<victor-ronr-ZNEtucZYht7k1uMJSBkQmQ@public.gmane.org>
>
> An advantage I can clearly see from doing the schema in ruby code, is
> that it can more easily be incorporated into version control. One thing
> that I''ve seen in every rails app I''ve dowloaded to try,
is some scripts
> in the db folder, but they were most often for mysql or pgsql, which
> means they were somewhat platform specific.
> If they would have been done in code, apart from avoiding the need to
> run certain scripts before trying the app, it would be possible to run
> them in every rdbm platform compatible with rails
>
A DBMS-independent schema definition language (DDL) could solve this problem.
How does DBI do at this?
How about Rails'' code? I''m not sure how much of the logic that
would be needed
for that already exists in ActiveRecord adapters (data types, etc.); maybe not 
too more is needed.

Cheers,
Dave

> I don''t know why, but it seems like David hasn''t planned
for this.
This is not about lack of planning, but a conscious choice. Consider 
this scenario:

You''re in your third iteration of the application. You''re
already
eating your own dog food, so you have useful data in the database that 
you don''t want to throw away. You now need to add a new attribute to a 
model.

With the current approach, you would bring up your graphical database 
editor (like CocoaMySQL) and add the field, then reload the application 
to see the change.

With a schema-in-model, you would bring up a text editor and add 
something like "attribute :nickname, :type => :string" to the
model.
Then you would either write a migration script that could preserve your 
existing data while another field is added to the model (and run your 
migration build). Or you could go and also complete the step from the 
current approach.

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.

That is not to say that auto-generation of a schema from an abstract 
source is worthless. There are interesting benefits from being able to 
generate a schema for any database from a shared source.

But Rails is an extraction. I extracted the approach that proved to 
provide me with the greatest velocity developing real-life 
applications. I cared (and care) more about going fast than a possible 
future where I would need the same schema in multiple database flavors.

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

It''s not something I''d use myself for Basecamp, Ta-da, or
Honey. But I
could imagine using it for instiki2.
--
David Heinemeier Hansson,
http://www.basecamphq.com/   -- Web-based Project Management
http://www.rubyonrails.org/  -- Web-application framework for Ruby
http://www.loudthinking.com/ -- Broadcasting Brain

Victor,

This is wrong. The scripts are RDBM specific not platform specific. It 
probably meant that the authors either only had access to those 
specific servers, chose to support only those servers or didn''t have 
time to develop schema''s for other servers.

All that is needed to support another RDBM is for someone to create a 
compatible schema based on the ones included. This is trivial in most 
cases and once done they could be sent to the original author for 
inclusion in any distribution.

Do you really want to place more code in your application than is 
necessary? Coming from java I am more and more amazed at how much code 
is actually irrelevant. Come on getters/setters for every field in a 
database? In AR full access to one of my old model object''s fields is 2
lines of code and in java the same class has 200+ lines of code.

AR has may have some rough spots but some great developers are ironing 
those out at light speed. But its elegance is its simplicity and lack 
of bloat.

While attending the Building Basecamp workshop I discussed David, Jason 
and Ryan''s comments during a break with other attendees. Another 
developer asked what was different about Rails. My response was that it 
reverses the traditional development process. Instead of building up to 
the user interface, you worked your way down the model.

In the past my traditional first step was similar to yours. Model 
First, Present Later. Why is that? Because modeling and generating the 
foundation for your application required comparatively massive code 
generation.

Rails encourages a different process. Present First, Model Later. The 
interface is the application and everything else supports the 
interface.

This is probably why you are seeing challenges with AR. You are looking 
to get under the hood before the car has been designed.

It may also be a key reason why casual observers have a hard time 
seeing the value in Rails. They are so accustomed to the Model First, 
Present Later pattern other frameworks encourage.

--
Thanks.
Lon Baker

Speedymac LLC
http://www.speedymac.com
lon-JCsVN+o+Xp98UrSeD/g0lQ@public.gmane.org
AIM: spdemac

On Mon, 28 Feb 2005 13:46:43 +0100, David Heinemeier Hansson
<david-OiTZALl8rpK0mm7Ywyx6yg@public.gmane.org>
wrote:
> It''s not something I''d use myself for Basecamp, Ta-da, or
Honey. But I
> could imagine using it for instiki2.
I''m a bit intrigued ... What''s Honey? And is Instiki2 related
to
Madeleine record wish?

Zsombor

> I''m a bit intrigued ... What''s Honey? And is Instiki2
related to
> Madeleine record wish?
Honey is the code name for the next application that 37signals is 
working on (no more info at this time). The plan with instiki2 is to 
make it into a "real" rails application. That is move away from 
madeleine and use a SQL database.
--
David Heinemeier Hansson,
http://www.basecamphq.com/   -- Web-based Project Management
http://www.rubyonrails.org/  -- Web-application framework for Ruby
http://www.loudthinking.com/ -- Broadcasting Brain

I''m a little out of my element in this discussion, but isn''t
an obvious
problem that Rails assumes SQL-based storage, despite the fact that a
lot of data is not well-modelled in RDBMSs?

What about object or xml databases (BDB XML is excellent, for example,
and has a Ruby binding), or even just simple BDB?

Related aside I''ll interject here:

I''m interested in bibliographic applications, which are one of those
domains where the metadata is not a good fit for relational databases.
In that community, they have an interesting indexing and query
abstraction (CQL) and communication protocol (SRU/W).

http://www.loc.gov/z3950/agency/zing/srw/
http://www.loc.gov/z3950/agency/zing/cql/

So you set up the indices apart from any storage details, and then map
from there to the storage implementation details.

Bruce

Lon Baker wrote:
...
> Rails encourages a different process. Present First, Model Later. 
Interesting.  What are you presenting?

James

On Feb 28, 2005, at 12:15 PM,
rails-request-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org wrote:
> Interesting.  What are you presenting?
>
> James
Sorry. I will clarify what I mean.

PRESENT - Design, code and test the visual aspects, such as interface 
design, user interaction and browser compatibility.

MODEL - Implement business logic, customize models,helpers, etc. 
Everything needed to support the designed interface and usage.

After the initial iteration through these steps, development kicks into 
dueling banjos mode between the two steps.

In thinking about it here is a third step.

PERFORM - Deploy. Analyze performance. Improve when needed.

There are probably better terms to use, as design and coding are used 
throughout all steps.

--
Thanks.
Lon Baker

Speedymac LLC
http://www.speedymac.com
lon-JCsVN+o+Xp98UrSeD/g0lQ@public.gmane.org
AIM: spdemac

> I''m a little out of my element in this discussion, but
isn''t an obvious
> problem that Rails assumes SQL-based storage, despite the fact that a
> lot of data is not well-modelled in RDBMSs?
That may well be, but I''m not sure Rails is meant to be so generic.
Most
web applications'' data *is* well-modelled in RDBMs, and most web 
application developers, hosts, and development techniques expect to use 
RDMS, and certainly SQL. IMHO, there''s so much to be gained in Rails by
assuming that kind of data storage and--importantly--that kind of 
developer knowledge, it seems like it would be a detriment to try to 
accomodate these unusual situations out of the box.

On Mon, 28 Feb 2005 13:03:29 -0500, Lon Baker
<lon-JCsVN+o+Xp98UrSeD/g0lQ@public.gmane.org>
wrote:
> On Feb 28, 2005, at 12:15 PM,
rails-request-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org wrote:
> 
> > Interesting.  What are you presenting?
> >
> > James
> 
> Sorry. I will clarify what I mean.
> 
> PRESENT - Design, code and test the visual aspects, such as interface
> design, user interaction and browser compatibility.
> 
> MODEL - Implement business logic, customize models,helpers, etc.
> Everything needed to support the designed interface and usage.
> 
> After the initial iteration through these steps, development kicks into
> dueling banjos mode between the two steps.
> 
> In thinking about it here is a third step.
> 
> PERFORM - Deploy. Analyze performance. Improve when needed.
> 
> There are probably better terms to use, as design and coding are used
> throughout all steps.
> 
Hm.  I modeled first, then presented with my Rails app.  I realize
that I could easily do it the other way, but I already knew exactly
what the interface would be like.

On Mon, 2005-02-28 at 10:08 -0500, Bruce D''Arcus
wrote:
> I''m a little out of my element in this discussion, but
isn''t an obvious
> problem that Rails assumes SQL-based storage, despite the fact that a
> lot of data is not well-modelled in RDBMSs?
> 
> What about object or xml databases (BDB XML is excellent, for example,
> and has a Ruby binding), or even just simple BDB?
> 
> Related aside I''ll interject here:
> 
> I''m interested in bibliographic applications, which are one of
those
> domains where the metadata is not a good fit for relational databases.
> In that community, they have an interesting indexing and query
> abstraction (CQL) and communication protocol (SRU/W).
> 
> http://www.loc.gov/z3950/agency/zing/srw/
> http://www.loc.gov/z3950/agency/zing/cql/
> 
> So you set up the indices apart from any storage details, and then map
> from there to the storage implementation details.
I may be stepping into something over my head here, but I seem to
remember that ActiveRecord is just the preferred method of storage.
There is nothing stopping you from jumping in and creating something
that is either totally new and standalone or even inheriting from some
other library to get access to the data. 
-- 
Steven Critchfield <critch-wQLwMjUOumVBDgjK7y7TUQ@public.gmane.org>

Lon Baker wrote:
> On Feb 28, 2005, at 12:15 PM, 
> rails-request-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org wrote:
> 
>> Interesting.  What are you presenting?
>>
>> James
> 
> 
> Sorry. I will clarify what I mean.
> 
> PRESENT - Design, code and test the visual aspects, such as interface 
> design, user interaction and browser compatibility.
> 
> MODEL - Implement business logic, customize models,helpers, etc. 
> Everything needed to support the designed interface and usage.
OK, I see, and that''s basically what I do now.  In fact, I have a case 
where a client has a set of pages and a description of the behavior, but 
no details on implementation (naturally).

So, from looking at the the pages, having discussions, reading and 
making notes, I''ve teased out a first set of likely objects for the
model.

To play this out in Rails, though, and evolve the application,  I need 
to first create the database and tables before I can get to the models.

As I go along, I''ll perhaps see that a given attribute serves no 
purpose, or (more likely) that a model is missing some essential property.

Ordinarily (e.g., in a non-database-centric app), one would go to the 
source files where the classes are defined and just add or edit the 
accessors, and keep on coding and testing.  It''s all just a Ruby coding
session.  But with Rails, one has to go open up some database admin tool 
and/or muck with SQL to alter a model.

I''m suggesting that this breaks the flow, and that it might be nicer if
one could work out the models as Ruby classes, without being concerned 
with just how things are being persisted.

As DHH suggested, in another post in this thread, that this sort of 
magical SQL invocation may lead to lost data.  Perhaps, and that would 
put a crimp in things.

I''ve just started taking a closer look at Og, and I believe it will
keep
existing data if you alter the structure of your models, but don''t know
this for a fact.  I don''t see that this sort of data loss is 
unavoidable, and should be fairly easy (no?) if all one is doing is 
adding a field to a table.

James

On Mon, 28 Feb 2005 12:28:20 -0600, Steven Critchfield
<critch-wQLwMjUOumVBDgjK7y7TUQ@public.gmane.org>
wrote:
> On Mon, 2005-02-28 at 10:08 -0500, Bruce D''Arcus wrote:
> > I''m a little out of my element in this discussion, but
isn''t an obvious
> > problem that Rails assumes SQL-based storage, despite the fact that a
> > lot of data is not well-modelled in RDBMSs?
> >
> > What about object or xml databases (BDB XML is excellent, for example,
> > and has a Ruby binding), or even just simple BDB?
> >
> > Related aside I''ll interject here:
> >
> > I''m interested in bibliographic applications, which are one
of those
> > domains where the metadata is not a good fit for relational databases.
> > In that community, they have an interesting indexing and query
> > abstraction (CQL) and communication protocol (SRU/W).
> >
> > http://www.loc.gov/z3950/agency/zing/srw/
> > http://www.loc.gov/z3950/agency/zing/cql/
> >
> > So you set up the indices apart from any storage details, and then map
> > from there to the storage implementation details.
> 
> I may be stepping into something over my head here, but I seem to
> remember that ActiveRecord is just the preferred method of storage.
> There is nothing stopping you from jumping in and creating something
> that is either totally new and standalone or even inheriting from some
> other library to get access to the data.
You''re absolutely correct Steven.  I''ve been using a custom
model
implementation with one app for a while.   Not a database in sight, 
just files on disk.

I just created a .rb file that contained my classes, dropped it into
lib,  and went about building my controllers and views.

> --
> Steven Critchfield <critch-wQLwMjUOumVBDgjK7y7TUQ@public.gmane.org>
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz

On Feb 28, 2005, at 1:54 PM, Joe wrote:
> Hm.  I modeled first, then presented with my Rails app.  I realize
> that I could easily do it the other way, but I already knew exactly
> what the interface would be like.
That make sense to me. I am trying to form a new habit when starting 
any project I go through the steps. Even if I am 99% certain of the 
needs at the other end. After all once the interface is designed half 
the work is done.

That being said for every rule there are exceptions of course.

--
Thanks.
Lon Baker

Speedymac LLC
http://www.speedymac.com
lon-JCsVN+o+Xp98UrSeD/g0lQ@public.gmane.org
AIM: spdemac

On Feb 28, 2005, at 1:54 PM, James wrote:
> OK, I see, and that''s basically what I do now.  In fact, I have a
case
> where a client has a set of pages and a description of the behavior, 
> but
> no details on implementation (naturally).
So the client handled the present step for you then.

When you say no details on implementation, what is missing? Is it 
something that involves the interface or the model?
> So, from looking at the the pages, having discussions, reading and
> making notes, I''ve teased out a first set of likely objects for
the
> model.
>
> To play this out in Rails, though, and evolve the application,  I need
> to first create the database and tables before I can get to the models.
>
> As I go along, I''ll perhaps see that a given attribute serves no
> purpose, or (more likely) that a model is missing some essential 
> property.
That is part of the modeling step in my opinion.
> Ordinarily (e.g., in a non-database-centric app), one would go to the
> source files where the classes are defined and just add or edit the
> accessors, and keep on coding and testing.  It''s all just a Ruby
coding
> session.  But with Rails, one has to go open up some database admin 
> tool
> and/or muck with SQL to alter a model.
One of many huge time savings in Rails happens right there. Instead of 
editing code, adding setters/getters and confirming compatibility with 
your persistence layer, you simply modify the persistence layer.
> I''m suggesting that this breaks the flow, and that it might be
nicer if
> one could work out the models as Ruby classes, without being concerned
> with just how things are being persisted.
With the persistence layer maintaining the database, tables, rows, 
fields and naming conventions why duplicate the effort?

Is it only to avoid switching to a tool specially designed for such 
tasks and doing it with a few clicks/

--
Thanks.
Lon Baker

Speedymac LLC
http://www.speedymac.com
lon-JCsVN+o+Xp98UrSeD/g0lQ@public.gmane.org
AIM: spdemac

Lon Baker wrote:
> With the persistence layer maintaining the database, tables, rows, 
> fields and naming conventions why duplicate the effort?
It is not a duplication. Rather a shift the way I see it.
> Is it only to avoid switching to a tool specially designed for such 
> tasks and doing it with a few clicks/
Every click counts. Especially when the RSI come haunting you, I can 
tell. Programming is automization, and automization is avoiding clicks ;)

Sascha

Lon Baker wrote:
> 
> All that is needed to support another RDBM is for someone to create a 
> compatible schema based on the ones included. This is trivial in most 
> cases and once done they could be sent to the original author for 
> inclusion in any distribution.
Exactly! Simple and direct -- why bloat the code base with another 
abstraction layer for a task that is simple and, in any case, is 
performed infrequently on non-demo applications.

-- 
Stuart Rackham

>> I''m a little out of my element in this discussion, but
isn''t an
>> obvious
>> problem that Rails assumes SQL-based storage, despite the fact that a
>> lot of data is not well-modelled in RDBMSs?
>
> That may well be, but I''m not sure Rails is meant to be so
generic.
> Most web applications'' data *is* well-modelled in RDBMs, and most
web
> application developers, hosts, and development techniques expect to 
> use RDMS, and certainly SQL. IMHO, there''s so much to be gained in
> Rails by assuming that kind of data storage and--importantly--that 
> kind of developer knowledge, it seems like it would be a detriment to 
> try to accomodate these unusual situations out of the box.
Yeah. Active Record is for SQL-backed databases. Madeleine and other 
libraries are great for other kinds of storages and you can easily use 
them with Rails.
--
David Heinemeier Hansson,
http://www.basecamphq.com/   -- Web-based Project Management
http://www.rubyonrails.org/  -- Web-application framework for Ruby
http://www.loudthinking.com/ -- Broadcasting Brain

On Feb 28, 2005, at 5:00 PM, Sacha wrote:
>> With the persistence layer maintaining the database, tables, rows,
>> fields and naming conventions why duplicate the effort?
>
> It is not a duplication. Rather a shift the way I see it.
I see your point. However nearly every RDBM has numerous full function 
tools for handling these functions. Why add code to both Rails and your 
application?
>
>> Is it only to avoid switching to a tool specially designed for such
>> tasks and doing it with a few clicks/
>
> Every click counts. Especially when the RSI come haunting you, I can
> tell. Programming is automization, and automization is avoiding clicks 
> ;)
I see the temptation. I have felt the pain and leapt for tools to 
"automate" tasks, code generation and builds in my former life as 
WebObjects (Java) developer. Those experiences fuel my hesitation at 
too much automation.

--
Thanks.
Lon Baker

Speedymac LLC
http://www.speedymac.com
lon-JCsVN+o+Xp98UrSeD/g0lQ@public.gmane.org
AIM: spdemac

On Mon, 2005-02-28 at 23:17 +1100, Dave Burt wrote:
> A DBMS-independent schema definition language (DDL) could solve this
problem.
> How does DBI do at this?
> How about Rails'' code? I''m not sure how much of the logic
that would be needed
> for that already exists in ActiveRecord adapters (data types, etc.); maybe
not
> too more is needed.
A workaround that I use to create DBMS-independent schema definitions is
to:

1. draw UML diagrams in dia (http://www.gnome.org/projects/dia/) that
models the database tables

2. use tedia2sql to generate database-specific SQL DDL scripts
(http://tedia2sql.tigris.org/)

Of course this is only useful at the beginning of my development when
the data model is continuously changing. I wrote a simple shell script
that drops and recreates the entire database from the dia file. Plus it
helps to have an up-to-date UML diagram of your application.

cheers,
mengkuan

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.

Lon Baker wrote:
> On Feb 28, 2005, at 1:54 PM, James wrote:
> 
>> OK, I see, and that''s basically what I do now.  In fact, I
have a case
>> where a client has a set of pages and a description of the behavior,
but
>> no details on implementation (naturally).
> 
> 
> So the client handled the present step for you then.
> 
> When you say no details on implementation, what is missing? Is it 
> something that involves the interface or the model?
Sort of both, but really on the model end.  The client has an idea in 
mind, has worked out the pages and application flow (barring some 
assorted minor details).  But no work had been done to explicitly model 
what''s going on in any formal way.  One has to bounce around the pages 
to see where different properties of assorted objects appear to piece 
things together.  So it''s possible that as one is working backwards
from
the view that you discover, oh, yeah, this thing here needs a date 
attribute in order to make this page work.
> 
>> So, from looking at the the pages, having discussions, reading and
>> making notes, I''ve teased out a first set of likely objects
for the
>> model.
>>
>> To play this out in Rails, though, and evolve the application,  I need
>> to first create the database and tables before I can get to the models.
>>
>> As I go along, I''ll perhaps see that a given attribute serves
no
>> purpose, or (more likely) that a model is missing some essential 
>> property.
> 
> 
> That is part of the modeling step in my opinion.
Of course.  But you need to bounce out to a SQL tool to do it.
> 
>> Ordinarily (e.g., in a non-database-centric app), one would go to the
>> source files where the classes are defined and just add or edit the
>> accessors, and keep on coding and testing.  It''s all just a
Ruby coding
>> session.  But with Rails, one has to go open up some database admin
tool
>> and/or muck with SQL to alter a model.
> 
> 
> One of many huge time savings in Rails happens right there. Instead of 
> editing code, adding setters/getters and confirming compatibility with 
> your persistence layer, you simply modify the persistence layer.
Same thing.  The assumption would be that the class attributes would 
automagically drive the persistence layer; you don''t need to confirm 
compatibility.  The code is the authoritive description of the tables, 
as opposed to the tables defs being the authoritive description of the 
objects.  I don''t see tying out field anes as any easier than tying out
object attributes.
> 
>> I''m suggesting that this breaks the flow, and that it might be
nicer if
>> one could work out the models as Ruby classes, without being concerned
>> with just how things are being persisted.
> 
> 
> With the persistence layer maintaining the database, tables, rows, 
> fields and naming conventions why duplicate the effort?
It''s not duplication if you let the code drive the table defs.  And the
main motivtion is mental, not manual, labor.
> 
> Is it only to avoid switching to a tool specially designed for such 
> tasks and doing it with a few clicks/
Few clicks?  Hardly.  Either way I have to type the table/class name, 
field/attribute names, data types, and associations.  No savings on 
typing either way, but one less conceptual shift if everything is driven 
by code.

Just a thought.


James

>> Is it only to avoid switching to a tool specially designed for such 
>> tasks and doing it with a few clicks/
>
> Few clicks?  Hardly.  Either way I have to type the table/class name, 
> field/attribute names, data types, and associations.  No savings on 
> typing either way, but one less conceptual shift if everything is 
> driven by code.
Have you seen the Rails videos? There was a new one just released that 
shows the flow once again. When you make the changes straight on the 
database, you don''t have a build phase (generate SQL from model, drop 
database, create new database), which means its
change''n''reload to see
result.

And, as I described in the earlier example, you don''t throw out the 
data you''ve been working on to make a change. Which is the most 
important benefit to me.

But the discussion is no longer an either or. Since I''m going to do the
migration approach anyway, having the possibility of an in-model schema 
is a fairly trivial addition. And one that''ll benefit those who
don''t
like to leave their editor while working a system or those who needs 
the benefits of 1 abstract schema => 6 concrete schemas.
--
David Heinemeier Hansson,
http://www.basecamphq.com/   -- Web-based Project Management
http://www.rubyonrails.org/  -- Web-application framework for Ruby
http://www.loudthinking.com/ -- Broadcasting Brain

I''m totally fine with the approach of modifying the schema in a SQL
tool
like CocoaMySQL. My issue is how to keep the development db and the
production db in sync. What I''ve been doing lately is to modify my
development db with CocoaMySQL, then cutting and pasting the appropriate
ALTER TABLE and CREATE TABLE statements from the CocoaMySQL log into a SQL
script I later use on the existing production db when I release the code
into production. Does anyone have a more automated way of doing this for
MySQL? Perhaps by "diffing" the development and production schemas,
and
producing a delta SQL script to update the production schema? I''ve
searched
the web and found SQL schema diffing tools for MS SQL Server for example,
but not much for MySQL.

Sorry if this is a bit off-topic, but it''s got me thinking since
everybody
is adding up all the clicks in their guis and editor keystrokes. This is
definitely an area that requires a bit of bookkeeping.

Dave


On 3/1/05 3:24 AM, "David Heinemeier Hansson"
<david-OiTZALl8rpK0mm7Ywyx6yg@public.gmane.org>
wrote:
>>> Is it only to avoid switching to a tool specially designed for such
>>> tasks and doing it with a few clicks/
>> 
>> Few clicks?  Hardly.  Either way I have to type the table/class name,
>> field/attribute names, data types, and associations.  No savings on
>> typing either way, but one less conceptual shift if everything is
>> driven by code.
> 
> Have you seen the Rails videos? There was a new one just released that
> shows the flow once again. When you make the changes straight on the
> database, you don''t have a build phase (generate SQL from model,
drop
> database, create new database), which means its
change''n''reload to see
> result.
> 
> And, as I described in the earlier example, you don''t throw out
the
> data you''ve been working on to make a change. Which is the most
> important benefit to me.
> 
> But the discussion is no longer an either or. Since I''m going to
do the
> migration approach anyway, having the possibility of an in-model schema
> is a fairly trivial addition. And one that''ll benefit those who
don''t
> like to leave their editor while working a system or those who needs
> the benefits of 1 abstract schema => 6 concrete schemas.
> --
> David Heinemeier Hansson,
> http://www.basecamphq.com/   -- Web-based Project Management
> http://www.rubyonrails.org/  -- Web-application framework for Ruby
> http://www.loudthinking.com/ -- Broadcasting Brain
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

For Windows, an excellent ER diagramming tool (non-$0.00), is "DeZign
for Databases", by datanamic, especially for generating database
creation scripts for multiple target databases from one design.

The one thing it doesn''t do too well is recognize that a given field
is an "autoinc" field populated by a trigger that grabs numbers from a
sequence/generator (Oracle, Interbase), and when converting the
diagram to target a database that does support autoinc fields (SQL
Server, MySQL), just translate that construct to an autoinc field type
modifier. If you convert from Oracle to MySQL, you''ll lose the trigger
& generator calls. If you then translate back to Oracle, you''ll
have
to put them back in...  Not sure if Dia or ReKall will catch this.

Also, the free version of ReKall (thekompany.com) might work, too.

There are others, including Visio, ER-Win, etc.

-Corey Lawson

On Tue, 01 Mar 2005 09:03:34 +0800, Meng Kuan
<mengkuan-i6YX4oj4YlBLCxcys0AdiZqQE7yCjDx5@public.gmane.org>
wrote:
> On Mon, 2005-02-28 at 23:17 +1100, Dave Burt wrote:
> > A DBMS-independent schema definition language (DDL) could solve this
problem.
> > How does DBI do at this?
> > How about Rails'' code? I''m not sure how much of the
logic that would be needed
> > for that already exists in ActiveRecord adapters (data types, etc.);
maybe not
> > too more is needed.
> 
> A workaround that I use to create DBMS-independent schema definitions is
> to:
> 
> 1. draw UML diagrams in dia (http://www.gnome.org/projects/dia/) that
> models the database tables
> 
> 2. use tedia2sql to generate database-specific SQL DDL scripts
> (http://tedia2sql.tigris.org/)
> 
> Of course this is only useful at the beginning of my development when
> the data model is continuously changing. I wrote a simple shell script
> that drops and recreates the entire database from the dia file. Plus it
> helps to have an up-to-date UML diagram of your application.
> 
> cheers,
> mengkuan
> 
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>