Re: Looking for ORM for 'legacy' database.

On 6/4/2010 4:29 AM, Dave Howell wrote:
> I feel I should start with some pre-emptive apologies. I used to subscribe to this list, but haven't been reading it for a year or so, so I may have missed the answer to my question. (I did try finding it in the list archives, of course.) Also, I'm a little frustrated, and that might adversely influence the tone of this message. I'll try to avoid that, but I beg your forgiveness if I sound a little snide.
>
> I started using Ruby years ago, but, although I built my first database-driven website in 1998, before Microsoft had invented ASP pages, I'd never had the time to work with Rails. I've been looking forward to it, and last week finally had the chance to take a closer look. To my dismay, I discovered that one of the core opinions of this 'opinionated software' is exactly opposed to one of mine. For my work, the data is the supreme treasure, and the database engine is the god-emperor.
>
> "Migrations" really had me mystified when I started trying to figure out Rails. The idea that I would ever consider letting the middleware have any kind of write access to the database schema seemed so improbable that I spent rather a lot of time looking for the 'other' tutorial, the one where you start with the data and work your way forward to the web site, rather than starting in the middle and working out in both directions.
>    
Migrations can be very useful even if you are doing PostgreSQL specific 
DDL (which I am). You can just call execute in the migration with an SQL 
string. It's nice to keep changes to app code and data schema version 
controlled together.
> I eventually figured out that Rails just doesn't work that way. Clearly, the paradigm that it uses is very effective for a lot of people. That's a fine thing; I'm not here to try to convince people that it's doing stuff the 'wrong way.' I just need to find something that supports a more data-centric (and database-centric) approach.
>
> I have an old site I'd like to modernize, and a brand-new site to develop. My pre-existing site is currently using WiTango as the middleware, and I'd love to keep using it, but can't afford the $5,000 non-restricted server license. I've been looking at various non-Rails options, and it looks like, to a certain degree, it turns into a snap-your-own-together situation, where I get to pick a framework (like Merb, Padrino, Ramaze, Nitro, etc.), an adapter (Mongrel, Rack, WEBrick, Thin, etc.), a templating engine (Erubis, Liquid, Haml, Markaby, Radius, etc, etc, etc, etc), and an ORM. Since I'm working from the database out, I'm starting with the ORM, and will then find parts that work well with it.
>
> Not too surprisingly, most explanations and introductions tend to compare the new ORM to what somebody would have been doing in Rails; most people reading those pages will have already worked in Rails. It took me a little while to figure out that most of the information *I* wanted was appearing under the phrase 'legacy database.' I'm not dealing with a 'legacy' database for the new project; I'm still finishing up the work on the PostgreSQL schema. It's brand new, and I'm leveraging Postgres's feature set hard in order to protect the integrity of the data. Composite unique keys, foreign keys, and two different custom datatypes are part of the schema, for example.
>
> ActiveRecord did turn out to have a command available to build its migration file (I think that's what it was) FROM the schema, rather than vice versa. Unfortunately, it only managed to extract about 20% of the tables, because I'm using UUIDs for the primary keys, not sequences or integers, and it didn't know what to do with a UUID. I have no doubt that there is some place, somewhere, where I could define a new database type, but a couple of hours digging and fiddling with various files didn't lead me to the solution.
>
>    
I think you are referring to schema.rb. You can change environment.rb to 
dump to SQL for its schema definitions.

   # Use SQL instead of Active Record's schema dumper when creating the 
test database.
   # This is necessary if your schema can't be completely dumped by the 
schema dumper,
   # like if you have constraints or database-specific column types
   config.active_record.schema_format = :sql

I don't think ActiveRecord itself should have a problem with PostgreSQL 
custom data types. It should just treat them as strings.
> I did get the distinct impression that, while it was almost certainly possible, it probably wouldn't ever be smooth. I decided it made more sense to try to find an ORM that was more comfortable with the idea it could only reflect the database's schema, rather than trying to make the DB schema conform to Ruby objects.
>
> So far, I've found DataMapper, Friendly, M4DBI, Ohm, and Sequel as possible alternative ORMs, but all the documentation for them is heavy with terminology and jargon from Rails, and usually brags about how I can make tables appear in my database magically from my Ruby objects. Which ORMs are better for connecting to my Postgres databases in a way that will best take advantage of, or at least not get in fights with, the constraints enforced by Postgres itself?
>
>    
It sounds like you really want a minimal ORM layer that mostly just 
stays out of the way. Probably Sequel would be a good place to start 
looking.
> Thanks for reading to the end of a pretty long message, and thanks even more to those of you who help me figure out where to look for the answers. :)
>
>
>
>
>
>
>    
Jack