Re: Abstracting SQL Statements In An Object

richard wrote: 
On 2005-07-15, Scott Auge <scott_auge@xxxxxxxxx> wrote:

I am looking for comments on something that lets me abstract database updates in an object. 


Something like:

$DB->query("BEGIN TRANSACTION");

// Person fools around with $DB
$Person->SetFirstName("Something");
$Person->SetLastName ("Something");
$Person->SendSQL();

// Another object fooling around with $DB
$AnotherOBj->SomeMethod ("Foo");
$AnotherOBj->SendSQL();

$DB->query ("COMMIT TRANSACTION"); 



I work for a company called zedcore.com, and we code php for money. We
have solved this issue. I'll explain what we have done.

For sql we use arrays rather than objects which are then acted upon by
a database object. 

I'ts probably easiest if I give you some examples.

$oRst=new Recordset('RstUsers',DATASOURCE);

$oRst->SetDefinitionFromDatasource(array('Users'=>''));

//All fields from Users table added to a recordset.

Example 1 - Basic select
------------------------

$aSQL=array('Command'=>'Select');
$oRst->ExecuteSQLArr($aSQL);

Example 2 - Add a where
-----------------------

$aSQL=array();
$aSQL['Command']='Select';
$aSQL['Where']="username='bob'";

$oRst->ExecuteSQLArr($aSQL);


Example3 - More than one where
--------------------------------

$aSQL=array();
$aSQL['Where']=array();
$aSQL['Where'][]="username='bob'";
$aSQL['Where'][]="accountactive='t'";


Example4 - ordering
--------------------

$aSQL=array();
$aSQL['Command']='Select';
$aSQL['OrderBy']='UserName';

Example5 - Offset and Limit
---------------------------
$aSQL=array();
$aSQL['Command']='Select';
$aSQL['Limit']=2;
$aSQL['Offset']=10;

Now when we have a recordset we can put tags into our html to refer to
the recordset. Guess what this does:-

<$TEST.RST.RstTest.ANYRECORDS>
<table>
<tr><td><$RST.RstTest.CAPTION.username></td></tr>
<$ITERATE.RST.RstTest>
<tr><td><$RST.RstTest.FIELD.username></td></tr>
<$/ITERATE>
</table>
<$TEST.ELSE>
<p> No records</p>
<$/TEST>


But we do more as well. Guess what this does.

<RST.RstTest.PAGER>

<$TEST.RST.RstTest.ANYRECORDS>
<table>
<$RST.RstTest.ALLCAPTIONSORTER>
<$ITERATE.RST.RstTest>
<tr><$RST.RstTest.ALLFIELDS></tr>
<$/ITERATE>
</table>
<$TEST.ELSE>
<p> No records</p>
<$/TEST>

It does the same as the first template example unless you populate the recordset with

$oRst->SetSQLArr(array('Command'=>'Select','Limit'=>2));
$oRst->SetSorterPagerFromURL();
$oRst->Execute();

To be clear, it allows us to add table headers which will sort the
data and then move to the correct 'page'.

Neat huh?

We are going to gpl our code in the future, but we are busy with paying
work, so it may take some time. I have had a look at all the other application frameworks for PHP, and excepting ezPublish which I was half asleep when I was checking I can say our code is superior. Although
in places, less polished. Which is another reason why it has not been
opensourced yet.

zedcore.com will have the news when it comes...

Hope some of this helps! Integration of template engines to a source of
data directly is so convienient..


Gee, that looks a lot like ASP <g>

Seriously - it looks flexible, but overly complicated. I've found in general the simpler the interface the better. Also, you're interface does not abstract the sql at all. What happens if the column "username" changes, for instance? A proper abstraction layer removes all dependencies from the database.

For instance - I've got a problem on one of my customer's sites. Their MySQL isn't working properly. Their "IT person" is working to identify the problem, but in the meantime I need to get some stuff up.

So I wrote a couple of classes (i.e. Object and ObjectList) which used a flat file. Took me an hour or so and I have something which works. When they get the database problem resolved, it will be a simple matter to drop MySQL based classes in place of the flat file. Nothing else changes. And since the classes are in an include file, the source need not change.

Such is the value of real abstraction - which is what I practice.

Oh, and BTW - my company makes money writing PHP, also. I suspect most of us here work are in the same boat.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@xxxxxxxxxxxxx
==================
.

Relevant Pages

  • Re: Just say no to threads [Was: Software architecture]
    ... they knew there was going to be a database in the app. ... Now my colleague just spent a couple/three weeks designing an SQL ... Turns out they have a flat file of sample information, ...
    (comp.object)
  • Re: Can databases form automatic calculations?
    ... Basically I would like to know if there is some way to get a database ... All my product sales are ... Anyone know the MySQL function syntax for this if it is possible? ... The user interface is the SQL language which can be invoked ...
    (comp.databases)
  • DBConvert for MS SQL & MySQL 2.1.0
    ... DBConvert for MS SQL & MySQL is a database migration tool for data ... MySQL server directly. ...
    (comp.software.shareware.announce)
  • Re: Can databases form automatic calculations?
    ... Basically I would like to know if there is some way to get a database ... All my product sales are ... Anyone know the MySQL function syntax for this if it is possible? ... The user interface is the SQL language which can be invoked ...
    (comp.databases)
  • Re: Dabbling in plain-text databases
    ... plain-text databases (and if they're worth using in lieu of sql). ... will be sort of a video game fansite, that will have a news page (news ... parsing a file like that any faster or slower than using MySQL? ... An sql based database is better for situations requiring  better ...
    (comp.lang.php)