Object Oriented SQL

[eak]

Perl Monks,
I am running into the problem of copying and pasting sql statements throughout different scripts I am writing. I could just put each of these statements into a module and call them as methods, which would solve the problem of repeating code all over the place, but I don't like that approach much. What I am interested in is, how do most of you deal with a RDBMS. Do you try to create an object oriented wrapper to the DB? Do you just squirrel away SQL throughout your code? Ideally I would like to be able to create a create class from which I could request a number of columns and just forget about any of the joins that may have to occur. I have heard of Tangram and some of the other object persistence work done on CPAN, but I haven't heard much about their implementation. So, any thoughts, suggestions, or comments are welcome.

thanks,
eric

[merlyn]

There are a half dozen object-DB-ish things in Perl. Trying to recall off the top of my head, I'd say Tangram, Class::DBI, and something called Alzabo. Look at the Poop Group Archives for more ideas, or even subscribe to the list.

There's a nice paper on Class::DBI as well.

-- Randal L. Schwartz, Perl hacker

[Anonymous Monk]

Tangram is a pretty cool way to worth with SQL databases in an object-oriented fashion. If you're interested in learning more about Tangram, you can check out an article I wrote at:

http://www.webtechniques.com/archives/2000/03/jepson/

--
Brian Jepson - http://www.jepstone.net/

[Anonymous Monk]

Eak wrote: "Do you just squirrel away SQL throughout your code?"

Depends on the scale of the project.

On large projects, there are some good arguments for hiding (encapsulating) all of the nitty gritty SQL behind stored procedures inside the database. That way, should the backend database change, all well-behaved front-end apps that read and write data through the stored procedures wont break.

On medium-size projects, I hide (encapsulate) the SQL inside purposeful functions (eg insert_new_customer( ... ), get_all_customers( ... )) and share these functions across the project by putting them in a module.

[nop]

oops... forgot to login... the previous post was mine, nop.

[BastardOperator]

NOTE: please don't flame me for the following ;)

Too late, I'm getting down voted, aaahhh ;) oh well...

I recently wrote a web app in *duck* Python *peek**duck again*, in which I did exactly that, and it worked out quite nicely. I had a module for the sql, one for the html, a config module, a content module which was basically a Super class of a web page which was then subclassed for each page on the site. The url to get the code would be http://members.home.com/m.v.wilson.

The sql module probably could have been split up some, but due to Python's lack of anything close to mod_perl, I couldn't get persistent db connections, so the sql module's constructor created the database connection, the destructor broke it down. That way I only had one database connection per page. Each thing I wanted to do was represented by a method of that class (from that module). So, even down the road as I add pages, I can simply create a new method, and create the subclass with that method in it. Plus, I tried to keep a lot of the methods fairly generic so that I didn't _always_ have to create new ones.

I had actually written that app in Perl previously (should have stuck with it ;)), but everything was hardcoded, the html, sql, and everything else. It was quite ugly and every time I wanted to say...change the color of some part of the page, I had to edit 8 different cgi's. (that was my first perl project, so it wasn't too clean ;)).

Modularizing everything is great for say, producing apps which can have a console/gui/html interface, the logic is seperated out from everything else, so you just pull in what you need.

Another upside to the way that I wrote the python app is that I only had one cgi that was ever called. Even if someone could see the cgi source, they would never be able to get the database username and password, etc. 'cuz it was in a module outside of the chroot jail. That cgi was more or less just a traffic cop with a couple big if statements that created an instance of a class depending on what the specified "action" was.