Mark Armendariz wrote:
I'm a huge fan of SQL, and I've been using it for over 10 years. its
a solid and reliable friend. But it seems to be far too wordy and
gets hairy to maintain, which is why we tend to look for ways to
modularize it within our programming languages.
Personally, my feeling has generally been that since the "wordy" SQL is
generally performing the tasks of a lot of different functions, it is no
wordier than writing the code in PHP. Nor is making objects necessarily
less "work" than making functions.
I started with SQL on an SQL/DS system and then moved over to DB2 over
15 years ago.
At the time, SQL was powerful, it was it's own virtual mini language if
you took the time to craft your queries carefully, and in a client
server app on the slow network of the day(an 16MB Token Ring network) it
just made sense to do as much processing and filtering as possible on
the server, rather than transmit lots of data across the network and tie
up the client's memory and bandwidth in processing it.
Plus you got a lot of extra power in that processing of the data was the
most CPU and memory intensive operation, it's a lot easier to add extra
memory and upgrade the server than 50 clients. And tuning queries and
indexes is can make things run faster without having a heavy coding impact.
But back in those days we had a DB Admin, a Network admin, and a
Programmer working together to determine the problems(and I was lucky
enough to train in all 3 areas, plus the people I worked lacked many of
the ego's I would later encounter. If a problem will take the DB Admin
a couple hours to tweak the indexes and tune them for the queries, a
couple days for the network admin to update the parameters in the
network window sizes to fetch the result sets more efficiently, or a
week for the programmer to recode everything to meet the current
limitations - we took the business decision of correcting it in the
Database. I was later to run into people who would insist that the
"correct" solution was for someone else to fix it, no matter what the
time involved.).
When I went to MySQL it was a massive shock to lose so much
functionality that the DB2 had. This was after years of looking at DB2
compared to Oracle and always saying "if only we could store blobs in
the database like Oracle..." etc. But there was a good reason for this,
MySQL was a lightweight database that eschewed many of these features in
order to have the best performance it could. Plus it seemed rather
obvious that the developers of it had gotten burned by a few of those
features in the past.
Over time, MySQL is still advancing, and has added many cool features of
it's own. It also traditionally runs in an environment where you don't
have a DBAdmin committed to just your applications. You don't have a
network admin, as the application passes through many networks and you
can't force them to tune for your app. So a lot of logic ends up
getting pushed into the application.
But I'd not give up on SQL programming.
Heck, here is a simple benefit of SQL.
I had to export data for half a dozen different tables I inherited on
different reports. All I was doing was creating CSV files with a header.
The procedural way of creating the report would be to pull the data,
than create the file and header, than export all the rows parsing them
out , knowing which are strings and which are numbers so you can
determine which should be given quotes for importing into excel and
which should be placed without quotes.
The object way would be to create a master object, subclass it for each
report, have a little createReport function unique to each one, etc.
The SQL way was to use the handy little "as" function to name each of
the obtusely named columns, so pno could become "Part Number". Also a
little conversion to convert different application fine grained status
codes into human readable summary codes(have 3 pending status codes for
application logic is fine, but to the user they all mean the same thing
- pending). Use a little data type checking in the PHP to determine
which column was text and which was numbers, and create the file.
If done purely with objects, you would need to create a new class for
each report. Procedures means making some small changes and a new copy
of the program for each report. With the bulk of the logic in the SQL,
to make a "new" report all that is required is changing 1 line of code -
the SQL query.
Each tool has it's place for it's need. For me, the two main factors
tend to be:
Time: How much time will each one take. It's all well and good to say
"this is the best solution, and doing it any other way is lazy". But
when the question boils down to "you have 3 hours to do it" - you choose
the solution that fits the time constraints.
Other coders: If most of the coders working on a project use 1 style of
coding, I like to follow the same style. And if there is a wealth of
code out there that can adapted but it uses one style or another, I go
with that style. Of course, this means I'm now working on rewriting a
number of apps, on my own time, to use object oriented design as the
bulk of other code and modules I use are now using that design. It
makes for a jarring transition to go from one to the other, so I might
as well learn now.
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
