Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Re^5: Input on Lightweight SQL Query Templating?

by perrin (Chancellor)
on Apr 27, 2009 at 15:23 UTC ( #760369=note: print w/replies, xml ) Need Help??

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

If you really need to do a straight SELECT and grab a bunch of data without making objects, then you can just get the dbh and do exactly what you did here. Nothing prevents it. What you were looking at is for people who want to get back a set of objects that can be written back to the db or have further queries performed on them.

Common applications of DBI involve a lot of boring CRUD operations on single objects. Using Rose::DB::Object for these turns lots of boiler-plate code about tracking data changes, generating slightly different SQL for INSERT vs UPDATE and for just the columns which need to be written, preparing queries, and executing them with bind values, into one-liners.

If you don't want to use an ORM, I'm not going to waste time trying to convince you, but I think you're way off the mark if you imagine the point of these tools is to avoid writing SQL. Like most reusable code, the point is to avoid repetitive and mindless work.

  • Comment on Re^5: Input on Lightweight SQL Query Templating?

Replies are listed 'Best First'.
Re^6: Input on Lightweight SQL Query Templating?
by Xenofur (Monk) on Apr 27, 2009 at 16:07 UTC
    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. :)

      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.

        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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://760369]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (13)
As of 2019-12-06 15:56 GMT
Find Nodes?
    Voting Booth?
    Strict and warnings: which comes first?

    Results (156 votes). Check out past polls.