IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New How do you manage database changes?
[moved from the Flame forum, natch, and for both Ben and Todd, and whomever else]

We have a in-house C++ program that manages table/object/data migrations (somewhat like ERwin used to do) in conjunction with the build and deploy scripts (Perl), but there's still a hand on the wheel when moving to production, mainly because of production data.

Of course, we also have about 1M lines of PL/SQL code, so we're a pretty big shop as those things go.
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
Collapse Edited by admin Nov. 28, 2005, 09:26:01 PM EST
How do you manage database changes?
[moved from the Flame forum, natch]

We have a in-house C++ program that manages table/object/data migrations (somewhat like ERwin used to do) in conjunction with the build and deploy scripts (Perl), but there's still a hand on the wheel when moving to production, mainly because of production data.

Of course, we also have about 1M lines of PL/SQL code, so we're a pretty big shop as those things go.
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
New Re: How do you manage database changes?
The rails guys have an interesting approach. They define migration objects that support an "up" and "down" method. The "up" method alters the schema to move from the previous version to the one the migration is defined for (generally an alter table command, but could be any code you desire). The "down" method undoes that change.

The version is stored in the DB itself and the migration objects are associated with version via a file naming convention.

Then to change the DB to a particular version, you type:

rake migrate VERSION=3

and the software will automatically apply the up/down methonds in the proper order to migrate the DB to the proper version. (rake is the build tool rails uses).
--
-- Jim Weirich jim@weirichhouse.org [link|http://onestepback.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)
New Mind responding to some rails criticism?
[link|http://use.perl.org/~Ovid/journal/27047|http://use.perl.org/~Ovid/journal/27047] has fairly unkind things to say about the way that rails interacts with the database. Most of the people in that thread are fairly well known in the Perl community.

I'm curious about how you'd respond to that.

Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
New I would be interested to see...
... a nested set tree implementation in the ORM from Rails.

I am always wary of things that claim to do nearly everything for me. I'm wary in principle of things that act by way of implicit instruction, as well, as much of Rails seems to (based on an admittedly cursory examination of the tutorial and documentation).

Toy examples used to show how easy something is are suspicious as well. The examples and tutorials in things like Rails and Twisted and CherryPy and the like are pretty much, as a rule, of the toy persuasion. One thing I like about Spring is that you get several complete non-trivial working applications for the examples. The Rails tutorial in particular is ridiculously lightweight.
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
New A Nested Comments Example
... a nested set tree implementation in the ORM from Rails.

Would a nested comments implementation be adequate?

Here is the DB Schema:
  create table comments (\n    id int not null auto_increment,\n    content text not null,\n    parent_id int,\n    updated_on date,\n    primary key(id)\n  )
Here is the ActiveRecord model object
class Comment < ActiveRecord::Base\n  acts_as_tree :order=>"updated_on"\n\n  def nested_display(level=0)\n    print "  " * level\n    puts "#{content} (#{updated_on})"\n    children.each do |child| child.nested_display(level + 1) end\n  end\nend
Here is a script to populate a few comments:
require 'active_record'\nrequire 'comments'\n\nActiveRecord::Base.establish_connection(\n  :adapter  => "mysql",\n  :database => "comments_development",\n  :socket   => "/var/run/mysqld/mysqld.sock",\n  :username => "jim"\n)\n\nroot_comment = Comment.create(:content => "This is great!")\ndisagree     = Comment.create(:content => "No it isn't.",      :parent => root_comment)\nargue        = Comment.create(:content => "You're nuts.",      :parent => disagree)\nagree        = Comment.create(:content => "Right on Brother.", :parent => root_comment)
And here is a script to dump the comments (I'll omit the connection code this time, which is the same as the populate script above).
# [... connection code elided ...]\nComment.roots.each do |comment| comment.nested_display end
And finally, the output of the dump script against a real database.
$ ruby dump.rb\nThis is great! (2005-11-29)\n  No it isn't. (2005-11-29)\n    You're nuts. (2005-11-29)\n  Right on Brother. (2005-11-29)
--
-- Jim Weirich jim@weirichhouse.org [link|http://onestepback.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)
New Either one of us is missing something, or Rails is magic
Unless I'm missing some major Rails magic, that's not an implementation of [link|http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427|nested set trees]. But then I don't see how you're even getting the children for the "each" in nested_display.
===

Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
Expand Edited by drewk Nov. 29, 2005, 12:23:04 PM EST
New I Missed Something
that's not an implementation of nested set trees

Sorry, I missed the 'nested set' portion of the request. What I supplied was an adjacency list implementation (according to the link you sent ... thanks BTW).

But then I don't see how you're even getting the children for the "each" in nested_display.

The acts_as_tree method defines a bunch of methods in your model object, including children, siblings, self_and_siblings, and ancestors. It also defines a roots method in the class object.

Theoretically, you would just create a acts_as_nested_set method that could create all the same methods, but using the nested set implementation. I'll have to review your linked article in more detail before I could say how easy that might be.
--
-- Jim Weirich jim@weirichhouse.org [link|http://onestepback.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)
New Adjacency trees are simple.
Almost all ORM implementations can handle simple parent/child relationships. Nested set trees, though, as you can see from Drew's link, are a different animal. Of interest will be the code to insert/remove children, since that operaton would be prohibitively expensive if it required a SQL call for each child being modified.
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
New Re: Adjacency trees are simple.
Of interest will be the code to insert/remove children, since that operaton would be prohibitively expensive if it required a SQL call for each child being modified.

So I see. It looks as if this is more of a "can the database support this" question than an ActiveRecord question. You can specify arbitrary SQL to be run on any given insert/update/delete action on ActiveRecord, so its just a matter of getting the SQL right.

But not all database handle those fancy SQL routines, right? (As I disclaimed in my other message, I am not a DB expert). Sothen it becomes a DB portability issue.

Is that the kind of answer you are looking for? (I'm willing to play with this idea, but since this will be more than a two minute demo, I want to make sure I'm addressing your concerns).
--
-- Jim Weirich jim@weirichhouse.org [link|http://onestepback.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)
New The arbitrary SQL somewhat answers my question...
But since the arbitrary SQL that gets run will update every other record in a thread (in this example), I'm interested to see how ActiveRecord handles the results, particularly with respect to caching and the like.

At the very least you don't have to run code that would cause every object in the thread to save itself individually, which is good.

Note: just about any database can support a nested set tree. If it can't, then it's not much of a database. The only difference would be whether the logic can be placed in a stored procedure or not, but in the end that's not a big deal for the purposes of this example.

Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
New Not what he meant
From your example, it looks like the concept of walking the tree is handled automagically. In the set tree model you have updates something like:
update comments set lft = lft + 1 where lft > input1 and rgt < input2

If Rails typically hides SQL details from you, requiring you to walk the tree, this becomes a problem.
===

Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
New Re: Not what he meant
If Rails typically hides SQL details from you, requiring you to walk the tree, this becomes a problem.

ActiveRecord (the Rails ORM) is just a very thin layer of code on top of SQL. This makes it dirt easy to customize the SQL for fetching or updating. I don't think this will be a problem.

However, I'm not a heavy-duty user of AR, so it will be fun to play with this and see exactly how this all falls out.
--
-- Jim Weirich jim@weirichhouse.org [link|http://onestepback.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)
New Re: Mind responding to some rails criticism?
I'm curious about how you'd respond to that.

Keep in mind I am not a DB expert nor a heavy-duty rails user, so take what I say with a grain of salt.

In a lot of ways I see the whole debate as a matter of control, and which side of the debate you fall on depends on where you exert the most control.

Database owners (DBAs and schema designers) want the rules applied at the lowest possible level. Allowing the rules to be enforced at the application level scares them because that means /every/ application needs to follow the rules. Since the apps can be written in any language using any framework, the only way to provide consistent rules is at the DB level.

Application developers have different perspective, particularly open source authors or anyone writing against multiple databases. Since /any/ database could be used by the application, moving the rules out of the application means that that they can't depend on the rules being enforced at all. Different databases have different capabilities for rule enforment. So if you want your application to run on anything from Sqlite to the top end Oracle server, the rules must be in the application.

So where do you put the rules? David HH (the author of Rails) suggests that for single point of entry database with a single app (or at least a single framework), having the rules in the app code is not a bad idea. However, he doesn't disagree with the rules in the DB approach in enterprise DBs.

Robby Russel (a big Rails user and owner of Planet Argon, a Rails friendly ISP) is a big fan of putting as many rules as possible in the DB, and he does so in all of his DB applications. Of course, he sticks primarily to Postgresql, so he doesn't have to deal with the cross database platform problems.

So there you have it, Rails is able to support either approach.

Where do I stand? Putting all the rules in the DB only seems to leave the app with only an anemic ability to validate. Putting the rules in the app only leaves the DB without the information it needs to help. I would put it in /both/ places, but express the rules at a single point, probably using some kind of domain specific language that could easily be translated to whatever database or application language needed it. (You can look here [link|http://onestepback.org/articles/lingo|http://onestepback.org/articles/lingo] to guess what I would use to create the DSL).
--
-- Jim Weirich jim@weirichhouse.org [link|http://onestepback.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)
New Weekly change scripts
We have free reign to do as we please in the DV database. All database changes made during that week go into a numbered mod_schema_##.sql script. The client's DBAs then run this script (which we provide to them via email weekly) upon request.
-YendorMike

"They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
- Benjamin Franklin, 1759 Historical Review of Pennsylvania
New We do something like that.
But on an even smaller scale.

Given I am the DBA, and the developer for an internal app, most of the SQL changes I keep in version-specific files. The other 3 developers do changes in the local DB and then manually replicate them to production using MyAdmin (no, I don't like this, either). They are also conceptually resistant to version numbers in their product so I haven't even tried to get them to put SQL changes in versioned files...

Wade.
"Insert crowbar. Apply force."
New Oh, *that* process will scale well :-/
===

Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
New Tell me about it.
If I lose development of my app, I will probably become the gatekeeper to the website releases. At which point, I will be insisting on version numbers and improving what currently passes for a release process.

Taking their access away from the database will be harder...

Wade.
"Insert crowbar. Apply force."
New We make them independent of code, and do them first
We are not, however, using significant PL/SQL.

Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
New Automated or no?
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
New No
Changes are fairly minor and not that frequent. We'll probably tighten this up after it becomes an issue, but it hasn't ever been one. If we did more in the database, or more databases to synchronize, then we'd automate in a heartbeat.

The farthest that we've gone towards automation is to have a file listing things that need to be done before a push. The push halts, displays the file, and asks if this has been done yet.

Remember the scale here. We have less than 10 developers, and logic tends to be implemented at the Perl level. (Major exception, reports tend to be built in pure SQL.)

Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
New Two answers
First, we don't have any big databases so mods aren't that tricky.

Huh?

We have the mother of all SOAs, each service keeps the data it wants any way it wants to. Some is in databases, some is in flat files, pickles, bdbs, whatever. Its not particularly normalized or anything - its totally optimized for speed - cache and hash is the order of the day. So schemas tend to be small and fairly simple - one to two dozen tables tops. Nine out of ten times you're hitting cache for stuff. The term "eventually consistent" gets used a lot.

We have elaborate automated build and deploy system that runs scripts at various phases - db alter scripts can be added as appropriate.

I don't know a lot more about it than that as I've pretty much been client focused.

When I do my own web based db apps, I have the Smalltalk objects automatically reshape to the database schema everytime I connect and I use the glorp ORM lib to map entities to objects and tables.



"Whenever you find you are on the side of the majority, it is time to pause and reflect"   --Mark Twain

"The significant problems we face cannot be solved at the same level of thinking we were at when we created them."   --Albert Einstein

"This is still a dangerous world. It's a world of madmen and uncertainty and potential mental losses."   --George W. Bush
Expand Edited by tuberculosis Aug. 21, 2007, 12:47:35 PM EDT
New Re: How do you manage database changes?
The product I work on includes schema changes (AKA "migrations") nearly ever time we release. Each migration is effectively atomic, which takes a bit of work, since not all of the data subject to migration is in the database (PostgreSQL). We only have two stored procedures, though. In our situation, it's practical to ask customers to do a full database backup prior to an upgrade, though I don't know of cases of customers having to roll back.

A fresh install of our product installs a seed schema and data, and then applies whatever sequence of schema changes is needed to migrate that schema to current. Our seed state reflects the oldest supported version of the product in the field, and moves forward whenever the slowest customers do.

An update of the product just applies whatever schema changes haven't already been applied.

The thorny part of the process is that we can't safely rely on most of our production code to assist in the migrations. Relying on whatever version is installed is problematic, since it doesn't know about the upgrades. Updating the app code first is problematic, since it doesn't know about older versions of the schema. As a result, we write (and test) a lot of one-time migration code.

I don't know how we'd handle having a lot of logic in stored procedures.
     How do you manage database changes? - (admin) - (21)
         Re: How do you manage database changes? - (JimWeirich) - (11)
             Mind responding to some rails criticism? - (ben_tilly) - (10)
                 I would be interested to see... - (admin) - (8)
                     A Nested Comments Example - (JimWeirich) - (7)
                         Either one of us is missing something, or Rails is magic - (drewk) - (6)
                             I Missed Something - (JimWeirich) - (5)
                                 Adjacency trees are simple. - (admin) - (4)
                                     Re: Adjacency trees are simple. - (JimWeirich) - (3)
                                         The arbitrary SQL somewhat answers my question... - (admin)
                                         Not what he meant - (drewk) - (1)
                                             Re: Not what he meant - (JimWeirich)
                 Re: Mind responding to some rails criticism? - (JimWeirich)
         Weekly change scripts - (Yendor) - (3)
             We do something like that. - (static) - (2)
                 Oh, *that* process will scale well :-/ -NT - (drewk) - (1)
                     Tell me about it. - (static)
         We make them independent of code, and do them first - (ben_tilly) - (2)
             Automated or no? -NT - (admin) - (1)
                 No - (ben_tilly)
         Two answers - (tuberculosis)
         Re: How do you manage database changes? - (dws)

42, of course.
100 ms