[ciapug] On or Off ...
Tony Bibbs
tony at tonybibbs.com
Tue Aug 23 12:00:04 CDT 2005
Yeah, I've run into that as well. Fact is Propel can save you from
writing about 80% of the SQL you'd normally have to do. The other 20%
is not easily supported in an ORM paradigm. What we do to get around
this is used a concept called Named Queries. Hibernate supports a
similar concept in the Java world using Hibernate Query Language (HQL)
which I, personally, think is stupid since all developers know SQL already.
We put all the non-propel based SQL in a single XML file and give them
names (i.e. GetCustomersByMiddleName). We then have methods that allow
the name of a query to be sent along with any parameters which is then
generated into a prepared statement. For the wary it seems like a lot
of work but there are a few benefits most notably our ability to run a
PHPUnit script after major database changes to regression test *all*
custom queries in the click of a button.
But you are right, Propel and even our Named Queries is simply too much
abstraction and some developers either don't want to or can't black-box
what Propel does for you. However, we've found here at work that we've
stripped and average of 25% off of our development time using this
framework compared to the old do-it-all-yourself days of a couple of
years ago.
--Tony
David Champion wrote:
> This is kind of a personal preference thing... but I don't mind writing
> SQL code - I've been doing SQL for about 12 years. IMHO if you spend
> more time writing / maintaining your class library than you would have
> just doing the SQL code, then you've wasted your time.
>
> I've used tools & libraries before like what you're describing, and
> similar to the Propel stuff that Tony likes, but I've been burned before
> by something that doesn't fit what I needed to do - i.e. it wouldn't
> support some complex multi-table joins or something, so you end up
> having to work around them more than working with them.
>
> Of course, this is totally outside the prepared statement issues for
> security and whatnot...
>
> -dc
>
> Carl Olsen wrote:
>
>> I got really deep into object oriented PHP this weekend and decided to
>> start
>> with something a little more simple, so I'm creating a class for each
>> table
>> with select, insert, update, and delete functions that all use prepared
>> statements. I agree that the security blanket is great. I'm also using
>> exceptions to send error messages back to the page with messages I can
>> display to the user (right now, I have it sending the page name and line
>> number, but I'll remove that when the site goes into production).
>>
>> My slimmed down version is not as cool as the examples in the book, but I
>> need to take a small step forward before I try everything at once. I'm
>> going to look for places where I can use some of the advanced classes
>> from
>> the book. They have some classes for handling collections of objects
>> that
>> look very useful. PHP5 can now do a foreach loop on an array of objects,
>> but you have to implement the built-in iterator and interatoraggregate
>> classes in collection and collectioniterator classes to get there. I got
>> all the code to run (the examples are all in PostgreSQL, so I had to
>> convert
>> them all to mysqli), but I can't say I'm comfortable with it. The
>> book says
>> you have to use them a few times before you will get comfortable with
>> them,
>> so I'm not experiencing anything unusual. I need to get my project
>> going,
>> so I'm going to take a few of the simpler classes and start with them (my
>> project is not extremely data intensive, so I can get by with less).
>> I've
>> been doing object oriented programming with C# for several years, so at
>> least that makes it easier to learn than if I didn't know anything about
>> OOP.
>>
>> As I look at the prepared statements, I can vaguely visualize feeding
>> in the
>> parameters as arrays so that a generic set of select, insert, update, and
>> delete functions could be written to handle any table in the database,
>> but
>> I'm going to take the longer route this time.
>>
>> Thanks for the timer!
>>
>> Carl
>> -----Original Message-----
>> From: ciapug-bounces at cialug.org [mailto:ciapug-bounces at cialug.org] On
>> Behalf
>> Of Tony Bibbs
>> Sent: Monday, August 22, 2005 9:15 AM
>> To: carl-olsen at mchsi.com
>> Cc: ciapug at cialug.org
>> Subject: Re: [ciapug] On or Off ...
>>
>> Sorry for the delay, I was on vacation.
>>
>> Well, given you have already written both a raw SQL and prepared
>> statement version, I would simply use a timer to see how they do.
>> I've attached a simple timer class for you to use (not that you'd
>> really need it).
>>
>> My guess is the prepared statements, if not supported by your DBMS,
>> will take a little longer but the security blanket it gives will
>> probably offset any performance concerns.
>>
>> --Tony
>>
>> Carl Olsen wrote:
>>
>>> I'm just wondering about performance. I just wrote a class for a
>>> table in
>>> my MySQL database and I'm using mysqli for the connections and queries.
>>> Will it increase my performance to add prepared statements? Here's my
>>
>>
>> code:
>>
>>> <?php
>>>
>>> class faqs
>>> {
>>> private $id;
>>> private $question;
>>> private $answer;
>>> private $order;
>>> private $conn;
>>> private $needsupdating = false;
>>>
>>> public function __construct($faqid)
>>> {
>>> $this->conn = mysqli_connect("host", "username", "password",
>>> "database");
>>> if(!$this->conn)
>>> {
>>> throw new Exception("Unable to connect to the
>>> server: " . mysqli_connect_error());
>>> }
>>> $sql = "SELECT faq_question, faq_answer, faq_order FROM
>>> faq_faq WHERE faq_id = " . $faqid;
>>> $rs = mysqli_query($this->conn, $sql);
>>> if(!$rs)
>>> {
>>> throw new Exception("An error occurred selecting
>>> from the database: " . mysqli_error($this->conn));
>>> }
>>> if(!mysqli_num_rows($rs))
>>> {
>>> throw new Exception("The specified faq does not
>>> exist!");
>>> }
>>> $data = mysqli_fetch_array($rs);
>>> $this->id = $faqid;
>>> $this->question = $data['faq_question'];
>>> $this->answer = $data['faq_answer'];
>>> $this->order = $data['faq_order'];
>>> mysqli_free_result($rs);
>>> }
>>>
>>> public function getquestion()
>>> {
>>> return $this->question;
>>> }
>>> public function getanswer()
>>> {
>>> return $this->answer;
>>> }
>>> public function getorder()
>>> {
>>> return $this->order;
>>> }
>>> public function setquestion($question)
>>> {
>>> if(!is_string($question) || strlen($question) == 0)
>>> {
>>> throw new Exception("Invalid question value");
>>> }
>>> $this->question = $question;
>>> $this->needsupdating = true;
>>> }
>>> public function setanswer($answer)
>>> {
>>> if(!is_string($answer) || strlen($answer) == 0)
>>> {
>>> throw new Exception("Invalid answer value");
>>> }
>>> $this->answer = $answer;
>>> $this->needsupdating = true;
>>> }
>>> public function setorder($order)
>>> {
>>> if(!is_integer($order) || strlen($order) == 0)
>>> {
>>> throw new Exception("Invalid order value");
>>> }
>>> $this->order = $order;
>>> $this->needsupdating = true;
>>> }
>>>
>>> public function __destruct()
>>> {
>>> if(!$this->needsupdating)
>>> {
>>> return;
>>> }
>>> $sql = "UPDATE faq_faq SET ";
>>> $sql .= "faq_question = '" .
>>> mysqli_real_escape_string($this->conn, $this->question) . "', ";
>>> $sql .= "faq_answer = '" .
>>> mysqli_real_escape_string($this->conn, $this->answer) . "', ";
>>> $sql .= "faq_order = '" .
>>> mysqli_real_escape_string($this->conn, $this->order) . "' ";
>>> $sql .= "WHERE faq_id = " . $this->id;
>>> $rs = mysqli_query($this->conn, $sql);
>>> if(!$rs)
>>> {
>>> throw new Exception("An error occurred updating the
>>> database: " . mysqli_error($this->conn));
>>> }
>>> mysqli_free_result($rs);
>>> mysqli_close($this->conn);
>>> }
>>> }
>>>
>>> ?>
>>>
>>> -----Original Message-----
>>> From: Tony Bibbs [mailto:tony at tonybibbs.com] Sent: Friday, August 12,
>>> 2005 2:05 PM
>>> To: carl-olsen at mchsi.com; ciapug at cialug.org
>>> Subject: Re: [ciapug] On or Off ...
>>>
>>> Are you talking about cleaning it as far as escaping arguments to
>>> your prepared statements? If so, 4.1.x should be doing it on the
>>> server for you. Older ones would have to be done by your database
>>> abstraction layer.
>>>
>>> I think it's safe to black-box that much of it. As long as you are
>>> using prepared statements you are safe(r).
>>>
>>> --Tony
>>>
>>>
>>>
>>>
>>
>>
>>
>> _______________________________________________
>> ciapug mailing list
>> ciapug at cialug.org
>> http://cialug.org/mailman/listinfo/ciapug
>>
>
>
> _______________________________________________
> ciapug mailing list
> ciapug at cialug.org
> http://cialug.org/mailman/listinfo/ciapug
More information about the ciapug
mailing list