October 12th, 2012, 08:53 AM
ORM vs SQL
what's the difference between using a DB module (ORM) to iterate over a record set and output to a template.
Or loading a recordset into an array of hashes and outputting to a template?
I'm assuming that the DB isn't accessed in an ORM until the template iterates and it uses a cusor and 'fetchrow'.
And my way loads the entire recordset into memory.
But if the record set is a few hundred records, with a small amount of data and is outputted so the array of hashes is destroyed pretty quick , is the overhead that much of an issue?
Your advice is appreciated.
October 12th, 2012, 09:34 AM
I think the question is too general. When it comes to using an ORM, the questions should be: "What is the query that was generated by the ORM for me? How long did it take to execute? Can I do better by writing a query by hand?"
Since my only ORM experience is with Rails, I'll just mention that you can have an open console while browing your site. As you move to different pages, the console will display messages showing the generated query and the execution time.
Hopefully you have the same sort of output in Catalyst. Most of the time the queries are straightforward, but on some pages you may find the ORM selecting the same records over and over again in an unintelligent manner.
October 12th, 2012, 10:08 AM
Yes the development server provided by Catalyst spits out all requests and SQL gumph.
My concern with using an ORM is the conveluted and complicated requirment to create a DB schema and models based on these schemas in the web app, when this is already handled by SQL (well sort off, and eventually will at least have a schema with all relationships defined, if I get the chance!)
I can see with a perfectly normalised DB the attempt to simplify from a coders point of view the way you grab records and spit them out to a view.
But if you don't have a normalised DB or even FK/PK relationships in the tables (not defined in the Schema, they exits just not defined - I know, don't need a kicking off anyone to tell me how badly designed our DB is!).
So trying to butcher a schema to match the SQL and the model for the auto generated ORM/CRUD functions, I don't think is going to work , or will be a nightmare to configure.
I'm considering using the CPAN module SQL-DB http://search.cpan.org/~mlawren/SQL-...lib/SQL/DB.pod, if I don't use my own SQL layer, and wondered is this really that much of a bigggie?
I have seen this CPAN module being classed as an ORM, though it's more of a DB interface to make it a little easier to work with DBI at the end of the day from what I can see!
I don't understand enough of the Catalyst framework to know how I go about doing this, if I need to handroll evey model or how you would use the helper script to create scaffolding for a schema that doesn't exist.
I don't even know how to set up user login / authentication yet, but I want to be sure I can use my own SQL abstraction layer before I spend too much time learning the framework.
If I don't use ORM/CRUD (well SQL-DB has CRUD functionality), am I a bad man and should not be allowed near an MVC framework?
Is it still a good idea to move to an MVC environment even if I'm going to circumvent the ORM/CRUD abstraction built into it?
I assume all I do is create a folder in the 'model' part of the folder hierachy for my model name and put the perl module in it and simply create my own 'model' logic which includes DB access for each method.
I still want to try to use the MVC paradigm and try to get to grips with the framework as a whole, I'm just concerned if I don't utilise the ORM/CRUD functionality, I'm kinda missing the point and wasting my time.
I've read Catalyst allows you to use what ever ORM you wan't does that mean it's OK to use a handrolled module that isn't really ORM at all?
as all logic (DB access) code should be in the model, with the controller managing the requests / routing and the view should simply provide the GUI.
As long as I stick to that ethos, it's still MVC isn't it and still a good idea?
As always, many thanks for your valuable input.
Last edited by 1DMF; October 12th, 2012 at 10:11 AM.