Looking for ORM for 'legacy' database

ruby-talk
21

Aside: Rails (or ActiveRecord) expects that the primary key is called
"id".

Yea, but I'd already rejected that convention.

create table Horses (
  horse_id primary-key-thing,
  ranch_id foreign-key-thing
  ...
);

create table Ranches (
  ranch_id primary-key-thing,
  ...
);

create table HiredHands (
  hiredhand_id primary-key-thing,
  ranch_id primary-key-thing,
  ...
);
  
select * from Horses join Ranches using (ranch_id) join HiredHands using (ranch_id);

  as opposed to

select * from Horses join Ranches on (Ranch.id = ranch_id) join HiredHands on (Ranch.id = ranch_id);

An ORM that makes the Ruby code cleaner by making the SQL code worse is not my friend. I do a LOT of my work directly in SQL.

Now, I've written applications which connect ActiveRecord to an existing
legacy database which doesn't respect Rails conventions, and they work
fine.

Oh, I never doubted it that it could be done. My problem was that the overwhelming percentage of available documentation assumes that I *won't* be doing it that way, so that I couldn't find the information I needed to make it work.

So I'd say:
* design and build your database how you want
* connect ActiveRecord to it as above
* watch it work
* if you really get stuck, then look at other ORMs (but I can't vouch
how well they will work with the rest of Rails, at least before Rails 3)

Um, but I hadn't planned on using Rails at all.

After setting up my models in Sequel, I started looking at frameworks. Padrino was the frontrunner, but it kept doing what Rails had done to me, just not as hard. The online tutorial included a feature that wasn't yet part of the version installed by rubygems (the -a switch), the 'bundle install' command that I was told to invoke without much explanation put some presumed-necessary gems in ~/.bundle/blahblahblah without telling me (and when I deploy it to the production server, there won't BE any home directory, so throwing crap in my home folder isn't satisfactory, especially if you don't tell me you're doing it), the 'admin' sub-app (I still have no idea what it does, I only know that the tutorial thought I'd be happier if I installed it) apparently took schema-write-access for granted and tried to create an 'accounts' table, and when "padrino rake seed" (and again, I still don't know what that command was supposed to actually do for me) failed with a cryptic database-related error, nobody in the #padrino IRC channel could help me resolve it.

That's more or less the kind of experience I had when I installed Rails and started trying to evaluate it, except that I threw a personal "Unable to find solution in current documentation space" error after two hours on Padrino, whereas it was about eight hours before I gave up on Rails.

So then I moved to Ramaze. Two hours of *that,* and I had a browser window full of data from my database.

Winner: Ramaze.

That just left selecting a templating engine. I figured I should be able to find at least a couple that would vastly exceed the templating functions I was used to from Tango, the DB-to-Web tool that I bought and loved back in 1997.

I guess 'lightweight' is just too popular an adjective, though, because so far, I haven't even found anybody *claiming* to do what it could do, never mind living up to such claims. I did find a "paginator" hiding in Ramaze as a 'helper' (not in the templater, where I'd expected to find it, but that's probably my own error), but it's still going to need a lot of customizing to do what I'm looking for.

Most of the templating tools out there are variations on a theme by ERB, which is pretty much the same as what I had with .idc/.htx files in IIS 2.0(?) running on Window NT 4.0 back in 1996. Except for haml.

I haven't gotten to actually using Haml yet, but it took me about 30 seconds to fall in love with it. Barring catastrophic incompatibilities with Sequel &/or Ramaze (which seems highly improbable), I'm done evaluating, and have finally moved on to developing.

···

On Jun 8, 2010, at 7:11 , Brian Candler wrote:

22

Dave Howell wrote:

Aside: Rails (or ActiveRecord) expects that the primary key is called
"id".

Yea, but I'd already rejected that convention.

create table Horses (
  horse_id primary-key-thing,
  ranch_id foreign-key-thing
  ...
);

OK. You said you'd gotten tired of including the underscores, so I
wasn't clear want convention you were using instead.

Oh, I never doubted it that it could be done. My problem was that the
overwhelming percentage of available documentation assumes that I
*won't* be doing it that way, so that I couldn't find the information I
needed to make it work.

The AR documentation is pretty comprehensive. All I needed was to find
  self.table_name = ...
  self.primary_key = ...
and to look at the options provided by belongs_to/has_many.

Note that the AR documentation is available separately from the Rails
documentation here:

http://ar.rubyonrails.org/

But anyway, if you've found something else you prefer, that's great. If
nothing else, it will likely to be much more lightweight than AR in
terms of startup time and memory usage.

Um, but I hadn't planned on using Rails at all.

Sorry, I misinterpreted "in anticipation of using Rails with it one day"
as meaning that Rails was on your roadmap.

I haven't gotten to actually using Haml yet, but it took me about 30
seconds to fall in love with it. Barring catastrophic incompatibilities
with Sequel &/or Ramaze (which seems highly improbable), I'm done
evaluating, and have finally moved on to developing.

If you love it at first glance, you'll love it completely. I've done
projects with Sinatra+HAML as well as Rails+HAML. Hopefully I'll never
see another ERB template :slight_smile:

Regards,

Brian.

···

On Jun 8, 2010, at 7:11 , Brian Candler wrote:

--
Posted via http://www.ruby-forum.com/\.

23

The piece I couldn't find was "How do I tell ActiveRecord to convert the database's "UUID" type to a string, and vice versa?"

···

On Jun 10, 2010, at 1:56 , Brian Candler wrote:

The AR documentation is pretty comprehensive. All I needed was to find
self.table_name = ...
self.primary_key = ...
and to look at the options provided by belongs_to/has_many.

24

Dave Howell wrote:

The piece I couldn't find was "How do I tell ActiveRecord to convert the
database's "UUID" type to a string, and vice versa?"

Ah, I've never come across a database with a "UUID" type. I'd imagine AR
would treat unknown types as strings, but you'd need to try it.

···

--
Posted via http://www.ruby-forum.com/\.

25

I had to hook a special library into Postgres in order to get the UUID type. Everybody (including me) expects AR to treat it as a string, but in fact, it threw an error because the column was an unknown type. That's why I was trying to figure out how to *tell* it to treat it as a string.

···

On Jun 11, 2010, at 0:48 , Brian Candler wrote:

Dave Howell wrote:

The piece I couldn't find was "How do I tell ActiveRecord to convert the
database's "UUID" type to a string, and vice versa?"

Ah, I've never come across a database with a "UUID" type. I'd imagine AR
would treat unknown types as strings, but you'd need to try it.

26

Worth noting: the error you supplied clearly came from running a
migration ("Could not create table blahblahblah. Unrecognized data type
UUID.") NOT accessing a table.

···

On 6/11/2010 2:36 AM, Dave Howell wrote:

On Jun 11, 2010, at 0:48 , Brian Candler wrote:

Dave Howell wrote:
    

The piece I couldn't find was "How do I tell ActiveRecord to convert the
database's "UUID" type to a string, and vice versa?"
      

Ah, I've never come across a database with a "UUID" type. I'd imagine AR
would treat unknown types as strings, but you'd need to try it.
    

I had to hook a special library into Postgres in order to get the UUID type. Everybody (including me) expects AR to treat it as a string, but in fact, it threw an error because the column was an unknown type. That's why I was trying to figure out how to *tell* it to treat it as a string.

27

Strange, my version of Postgre (8.4) comes with a UUID type.
Anyway since this seems to be something we're going back and forth on
have a look at:

The output from this script will be (using Ruby 1.9.1 and PostgreSQL 8.4):
bar
foo

Sure enough, a UUID gets treated just like a string.

···

On 6/11/2010 2:36 AM, Dave Howell wrote:

On Jun 11, 2010, at 0:48 , Brian Candler wrote:

Dave Howell wrote:
    

The piece I couldn't find was "How do I tell ActiveRecord to convert the
database's "UUID" type to a string, and vice versa?"
      

Ah, I've never come across a database with a "UUID" type. I'd imagine AR
would treat unknown types as strings, but you'd need to try it.
    

I had to hook a special library into Postgres in order to get the UUID type. Everybody (including me) expects AR to treat it as a string, but in fact, it threw an error because the column was an unknown type. That's why I was trying to figure out how to *tell* it to treat it as a string.

28

Dave Howell wrote:

The piece I couldn't find was "How do I tell ActiveRecord to convert the
database's "UUID" type to a string, and vice versa?"

Ah, I've never come across a database with a "UUID" type. I'd imagine AR
would treat unknown types as strings, but you'd need to try it.

I had to hook a special library into Postgres in order to get the UUID type. Everybody (including me) expects AR to treat it as a string, but in fact, it threw an error because the column was an unknown type. That's why I was trying to figure out how to *tell* it to treat it as a string.

Strange, my version of Postgre (8.4) comes with a UUID type.

8.3 did not; I had to get a special external library module and hook it in. 8.4 requires that it be compiled in with a special switch (&/or might also accept the external module, I'm not sure.) --with-ossd-uuid

Anyway since this seems to be something we're going back and forth on

{sigh} We're not really going "back and forth" on this. I knew when I first posted my query about alternative ORMs that I would inevitably get a lot of people trying to solve my original problem, even though I tried to make it clear that whether or not ActiveRecord *could* work was NOT the issue for me.

The problem is the documentation. Not ActiveRecord. The documentation.

When I first decided it was time to learn a new language a few years ago, I didn't decide to learn Ruby because it could do the most things. In fact, it was quite clear that Ruby would be quite a bit LESS capable than PHP, Perl, or Python. It was really new; there were a lot of libraries and whatnot that had not yet been ported. But it was also quite clear that it would do _most_ of what I wanted to do, and would probably soon expand to cover nearly everything I would want. More importantly, what it did was make doing what I wanted to do EASY. I was going to have to learn how to think about programming in a whole new way (well, not a *whole* new way, since AppleScript is also object-oriented in its own peculiar way) but once I had, things that were merely "possible" in PHP or Java or whatever would be *easy* in Ruby.

In the past couple of weeks, I have gotten 'stuck' on a problem about six times. One with Sequel, one with Rails/ActiveRecord, one with Ramaze, two with Haml, and I don't remember the last one. Of those six, the 'community' around them has managed to help me with three of them. The other three, nobody had a working solution for me. Now, three of six is pretty good, and people generally were willing to try to help me even if they couldn't solve my problem in the end. But advice from a mail list or chat room is a poor substitute for good documentation.

I also understand that the best way to get good documentation is to pay for it. Tango (now WiTango) comes with outstanding documentation, and costs $5000 per server to run. Documentation isn't all that much fun to create, so I don't expect the documentation for free &/or open source tools to be as good as for-profit commercial tools. Also, many of the Ruby tools I'm evaluating are very new, and haven't had time to accrete the documentation that they'll eventually have.

"Documentation" includes, of course, the myriad tutorials, blog entries, and mail list questions & answers archived on line. Rails, not surprisingly, has a LOT of 'documentation.' However, 99%+ of it takes migration for granted. I *know* that you don't HAVE to have migrations to use ActiveRecord; but I NEVER found anything that would actually tell me HOW to make a model from scratch by hand. I was *expected* to make the Ruby object, and migrate it TO the database.

A LOT of the online documentation was written by people who'd been using Rails for a while, and often was written FOR people who'd been using Rails for a while, so it takes a lot of Rails-behavior for granted. Perfectly reasonable, but it meant that a lot of the comments on mailing lists and stuff were fairly incomprehensible to me. It took me quite a while to figure out that I could ask Rails to build me my objects FROM my database. Since I hadn't been able to figure out what they should look like from scratch, this seemed like an excellent way to get started. I figured I'd have to modify/edit/improve them once that was done, but at least I'd have some place to start.

Unfortunately, that didn't work, because of the UUID problem. And for THAT, I could find *nothing* that seemed to offer a solution, even one I couldn't quite figure out. I rummaged through the source code, I Googled every possible variation I could think of, but nobody seems to have previously discussed how to add new types to ActiveRecord's understanding.

"This is a basic, fundamental ability." I said to myself. "If I cannot find the answer to THIS, what happens when I get to something that really is tricky?" Answer: I'm going to end up spending many hours trying to solve it myself, with just the source code (I hope) to go on.

I don't care if it's *possible* to make Rails or ActiveRecord work without migrations, or correctly and comprehensively support Postgres's advanced data types (Arrays, IP Addresses, and Records come to mind off the top of my head, none of which can be represented by plain strings). What matters is if it's *easy.* And it's not. It's insanely hard. The only documentation that will tell me how to do that assumes that I'm an experienced Rails user; that I have a very solid grounding in Rails fundamentals.

When I downloaded Tango and its tutorial back in 1996, I spent about 30 minutes with the tutorial and said "Wow, that was easy, and it's doing just the things I think I want to be able to do. Neat!" I spent about eight hours with Rails, and said "Well, I still haven't been able to get my data into a browser window yet, and that's just the first step. Maybe there's something else that will be easier."

It took me less than two hours to start playing with Ruby objects that were populating themselves from my Postgres database using Sequel. Even though I didn't need to tell Sequel how to handle the UUID fields (it defaulted to turning them into strings), I *did* find the information I would need if I *had.* Is Sequel 'better' or more capable than ActiveRecord? I really don't know. Did its documentation make it easier for *me* to do what *I* need to do? Unquestionably.

I spent two hours with Padrino before I ran into a problem that I was unable to solve from the documentation. Nor were the helpful folks in the #Padrino IRC room able to fix my issue. I'm sure it's *possible* to make it work, but again, I'd spent a fair amount of those two hours just trying to hunt down answers to fairly trivial issues. So I decided to look at the other strong candidate on my 'frameworks' list, Ramaze.

Two hours of work with Ramaze had me with a browser full of data from my database.

There's no doubt in my mind that Rails has more out-of-the-box ready-to-go functionality than Ramaze. But the overwhelming majority of Rails-world is intensely infused with the philosophy that, while you *can* make your web site any number of ways, what you *want* to do is start with your Ruby objects, and be able to switch from one data store to another at a moment's notice.

Rails and its cloud of associated components are clearly groovy nifty tools. But just as clearly, they are not the right tools for *me.*

···

On Jun 11, 2010, at 10:31 , Walton Hoops wrote:

On 6/11/2010 2:36 AM, Dave Howell wrote:

On Jun 11, 2010, at 0:48 , Brian Candler wrote: