php-general Digest 8 Mar 2011 17:38:26 - Issue 7217
Topics (messages 311741 through 311742):
Re: Somewhat OT - Stored Procedures
311741 by: Nathan Nobbe
Re: Help translating PHP5 code to PHP4.
311742 by: Jim Lucas
Administrivia:
To subscribe to the digest, e-mail:
php-general-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-general-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-gene...@lists.php.net
--
---BeginMessage---
On Sat, Mar 5, 2011 at 5:31 AM, Florin Jurcovici florin.jurcov...@gmail.com
wrote:
Hi.
I would always recommend stored procedures, as long as there are a
very few rules obeyed:
- keep them simple - they should mostly implement just CRUD operations
plus application-specific searches, and should not encapsulate any
other logic
i dont see the value in this approach, read more below.
- use just portable SQL (well, as long as this is possible)
My reasoning for using stored procedures and sticking to these rules
is the following:
- no matter what you do, especially with PHP, you can't achieve the
same performance if you generate your SQL on the fly as when you just
call a precompiled stored procedure
is this performance advantage enough to merit pushing all the queries to
stored procedures? id love to see some benchmarks.
- by keeping stored procedures very simple, and sticking to the
convention of packing just CRUD + specialized searches into them, plus
using just portable SQL, inasmuch as possible, you can easily switch
databases - in most cases, just copying over the stored procedures
does the trick
i doubt it takes the database long to compile trivial crud queries. i doubt
these would see much performance gain implemented as stored procedures
rather than generated by a php script.
- for the same reasons listed for the previous point, the readability
of your application is much improved - reading
users_getByLogin(:login) is IMO easier to comprehend than SELECT *
FROM USERS WHERE loginName = :login, without sacrificing any
performance or portability, compared to using inline SQL statements as
strings
this doesn't make much sense, since in php i can easily write a function
users_getByLogin($login) which provides the same readability advantage.
after all this would be invoked via the function name throughout the
source.
The consequences of not sticking to the above listed two criteria can
be very bad:
- packing more than reasonable logic into the database makes your
application incomprehensible - for instance
company_doMonthEndCalculations() is likely to include such a huge
quantity of logic, that the PHP code calling it is mostly irrelevant,
and you must actually comprehend both the details of the SQL in the
database (in the stored procedures) and the way PHP is connecting them
to understand an application - obviously harder if you have all your
logic in just one place
this makes a lot of sense and gets into the issue of how much application
logic if any would be encapsulated in a stored procedure layer.
doMonthEndCalculations does sound really scary lol!
- whereas if packing only very specific and simple operations into
stored procedures allows you to keep the design of the PHP application
very object-oriented, packing very much logic into stored procedures
may cause your PHP code to be just an adapter to an application
written in SQL, instead of being the application itself; SQL being
procedural, your application will have all the flexibility,
extensibility and maintainability problems that a non-OO design causes
this is a definite issue. when deciding to implement large chunks of domain
logic in stored procedures, it must be considered that the expressiveness of
these db scripting languages are not as extensive as the languages used to
implement the application layer.
from a performance perspective i think pre-compiling large complex queries
may be advantageous, but i suspect for trivial queries the difference may be
marginal. some benchmarks would be helpful in this area if anyone knows
where to find them.
-nathan
---End Message---
---BeginMessage---
On 3/7/2011 8:16 AM, Marc Guay wrote:
Hi folks,
I've stumbled into a project involving a server running PHP4 without
cURL. The script fetches data from an XML webservice and deals with
it. Is http://ca2.php.net/xml_parser_create the place to start? Any
tips (besides updating PHP)?
Here's an example of the PHP5 code:
$url = http://www.domain.com/webservice.php?var=foo;;
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$val = curl_exec($ch);
curl_close($ch);
libxml_use_internal_errors(true);
$xml = simplexml_load_string($val);
$errors = libxml_get_errors();
if (!($errors)){
$myvariable = $xml-attributes()-value;
}
else{
//
