NYCPHP Meetup

[Gary Mort]

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.