in reply to Re^5: Input on Lightweight SQL Query Templating?
in thread Input on Lightweight SQL Query Storage?

Sorry, but isn't the summary of what you just wrote: "Rose is good for single-row access, but when you want to operate on lots of data you're on your own."?

My problem here isn't that I don't want to use ORM. Something that genuinely makes db access easier would be awesome. But whenever I look at ORM solutions they only seem to complicate things and so far nobody has been able to explain and/or demonstrate in plain english what the concrete advantage in a real world situation is.

Also, as a sidenote: You may want to look into REPLACE, as it completely eleminates the need for slightly different insert and update instructions. :)
  • Comment on Re^6: Input on Lightweight SQL Query Templating?

Replies are listed 'Best First'.
Re^7: Input on Lightweight SQL Query Templating?
by perrin (Chancellor) on Apr 27, 2009 at 16:38 UTC

    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.

[reply]
      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.
[reply]
        Agreed that ORMs should auto-discover as much as possible. Most can do this, actually.
[reply]
        "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).

[reply]

In Section Meditations