Re^7: Input on Lightweight SQL Query Templating?

[perrin]

If you're doing mass updates or deletes, then an ORM is not typically going to help much. If you're grabbing a bunch of data, performing operations on each row, and writing it back to the db, an ORM can be a big time-saver.

I don't know how to explain it any simpler than I already have. You'd have to try it. If you feel like the setup for Rose is too much, you can try Class::DBI. It's not as capable as Rose::DB::Object, but I still use it, and it makes direct SQL very simple.

BTW, I usually avoid REPLACE. It's a slower way to do that operation than the alternatives and it makes DELETE triggers fire. If you're only supporting MySQL and you want to handle both cases with a single statement, INSERT...ON DUPLICATE KEY UPDATE is better.

[Xenofur]

If you're grabbing a bunch of data, performing operations on each row, and writing it back to the db, an ORM can be a big time-saver.

This is actually a pretty good explanation. I just realized that I'd simply never encountered such a situation at all. Most of my projects use SQL only in specialized tasks, i.e. storage of mass data, handling of caches.

My main gripe (at least as far as the setup goes) with all ORMs i've seen so far is the whole "tell me your db layout" thing, which is pretty annoying when you're still developing the app and your layout is still in a flux. Especially since this could be better done by using the DESCRIBE command.

Thanks for the note about REPLACE. I typically use SQLite, where it's an alias for INSERT OR REPLACE and didn't know it'd be slow in MySQL.

[perrin]

Agreed that ORMs should auto-discover as much as possible. Most can do this, actually.

[Your Mother]

"tell me your db layout", which is pretty annoying when you're still developing the app and your layout is this in a flux

I agree. DBIx::Class gets around this (Rose::DB might too, I'm not sure) with auto loading schema. It also has facilities to do bulk operations on resultsets, not just rows. The only real downside to ORMs which are at the level and quality of Rose and DBIC are that they are slower than straight DBI. Yes, they are only harder to follow/code at first but once you are in them they make reading the intention of DB code much easier than wading through SQL and will shave off nearly all the boring repetitive parts and can even make the really difficult parts pretty easy (like versioning, object inflation, and normalization of behavior across DB engines).

[siracusa]

"tell me your db layout", which is pretty annoying when you're still developing the app and your layout is this in a flux

I agree. DBIx::Class gets around this (Rose::DB might too, I'm not sure) with auto loading schema.

FWIW, Rose::DB::Object can indeed inspect your database and create appropriate Perl classes, either in memory or on disk as *.pm files, including column data types, foreign keys, and other relationships between tables. There's an example in the tutorial.