Abstracting SQL Statements In An Object

[Scott Auge]

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

Lemme explain what I am thinking:

Lets say I have an object Person with...

SetFirstName()
SetLastName()

etc.

(Basically the goal is for the development team to use the objects and
not try to write to the database themselves.)

Well, what I am trying to avoid is when I call SetFirstName() and then
call SetLastName() the execution of two UPDATE statements. I would like
to combine it all into one single UPDATE or INSERT so I am not beating
up on the database.

I am thinking I would need to store the changes into variables local to
the class and then maybe calling a SendSQL() function or something like
that to generated SQL changes of the data to the database.

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");

Anyhow - opinions and experiences are welcome!

[Jerry Stuckle]

Scott Auge wrote:

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

Lemme explain what I am thinking:

Lets say I have an object Person with...

SetFirstName()
SetLastName()

etc.

(Basically the goal is for the development team to use the objects and
not try to write to the database themselves.)

Well, what I am trying to avoid is when I call SetFirstName() and then
call SetLastName() the execution of two UPDATE statements. I would like
to combine it all into one single UPDATE or INSERT so I am not beating
up on the database.

I am thinking I would need to store the changes into variables local to
the class and then maybe calling a SendSQL() function or something like
that to generated SQL changes of the data to the database.

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");

Anyhow - opinions and experiences are welcome!

Scott,

Generally, classes do not access the database unless specifically requested.
You might have four class members, for instance - fetch(), update(), insert()
and delete(). Each does its respective operations based on the data in the
current object (fetch() usually requires a key).

Another common class is the list class, which fetches an array of objects based
on select criteria.

But no, you do NOT want to update the database every time you issue a
GetFirstName() or SetFirstName(). The former needlessly requires access to the
database, and the latter may update the database when you don't want it updated
(i.e. change the name - but verify the change with the user before actually
updating the database).

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[ZeldorBlat]

You're on the right track. What I like to do is have some 'isDirty'
flag in my class that is initially set to false. When someone calls
$p->setFirstName($ newName) see if the new value is in fact different
from the old. If it is, set isDirty to true.

A commit method of some sort can create and execute the update
statement. First thing he can do is check if isDirty is true. If not,
he doesn't need to do anything.

Additionally, you can call your commit() method in the class destructor
so that any pending changes are written automatically before the object
is destroyed.

[Tony Marston]

take a look at http://www.tonymarston.co.uk/php-mys...seobjects.html
and http://www.tonymarston.co.uk/php-mys...eobjects2.html which
describe how to use objects to perform all your database accessing for you.
The complete code can be found in
http://www.tonymarston.net/php-mysql...plication.html which you can
download and examine at your heart's content.

--
Tony Marston

http://www.tonymarston.net

"Scott Auge" <sc********@yah oo.com> wrote in message
news:sc******** *************** *******@news1.w est.earthlink.n et...

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

Lemme explain what I am thinking:

Lets say I have an object Person with...

SetFirstName()
SetLastName()

etc.

(Basically the goal is for the development team to use the objects and
not try to write to the database themselves.)

Well, what I am trying to avoid is when I call SetFirstName() and then
call SetLastName() the execution of two UPDATE statements. I would like
to combine it all into one single UPDATE or INSERT so I am not beating
up on the database.

I am thinking I would need to store the changes into variables local to
the class and then maybe calling a SendSQL() function or something like
that to generated SQL changes of the data to the database.

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");

Anyhow - opinions and experiences are welcome!

[R. Rajesh Jeba Anbiah]

[FUP: comp.lang.php]

Scott Auge wrote:
<snip>

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

IIRC, I have seen similar DB wrappers somewhere, but couldn't
recollect the exact place. And I'm not for this version. FWIW, PHP 5
supports overloading of properties using __set() and __get(); so you
may easily use something like:
$Person->FirstName = 'Something'; instead
$Person->SetFirstName(' Something');

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com

[richard]

On 2005-07-15, Scott Auge <sc********@yah oo.com> 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('RstU sers',DATASOURC E);

$oRst->SetDefinitionF romDatasource(a rray('Users'=>' '));

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

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

$aSQL=array('Co mmand'=>'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.RstT est.ANYRECORDS>
<table>
<tr><td><$RST.R stTest.CAPTION. username></td></tr>
<$ITERATE.RST.R stTest>
<tr><td><$RST.R stTest.FIELD.us ername></td></tr>
<$/ITERATE>
</table>
<$TEST.ELSE>
<p> No records</p>
<$/TEST>
But we do more as well. Guess what this does.

<RST.RstTest.PA GER>

<$TEST.RST.RstT est.ANYRECORDS>
<table>
<$RST.RstTest.A LLCAPTIONSORTER >
<$ITERATE.RST.R stTest>
<tr><$RST.RstTe st.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(arra y('Command'=>'S elect','Limit'= >2));
$oRst->SetSorterPager FromURL();
$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..

[Jerry Stuckle]

richard wrote:

On 2005-07-15, Scott Auge <sc********@yah oo.com> 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('RstU sers',DATASOURC E);

$oRst->SetDefinitionF romDatasource(a rray('Users'=>' '));

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

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

$aSQL=array('Co mmand'=>'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.RstT est.ANYRECORDS>
<table>
<tr><td><$RST.R stTest.CAPTION. username></td></tr>
<$ITERATE.RST.R stTest>
<tr><td><$RST.R stTest.FIELD.us ername></td></tr>
<$/ITERATE>
</table>
<$TEST.ELSE>
<p> No records</p>
<$/TEST>
But we do more as well. Guess what this does.

<RST.RstTest.PA GER>

<$TEST.RST.RstT est.ANYRECORDS>
<table>
<$RST.RstTest.A LLCAPTIONSORTER >
<$ITERATE.RST.R stTest>
<tr><$RST.RstTe st.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(arra y('Command'=>'S elect','Limit'= >2));
$oRst->SetSorterPager FromURL();
$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.
js*******@attgl obal.net
=============== ===

[richard]

Jerry Stuckle wrote:

richard wrote:

On 2005-07-15, Scott Auge <sc********@yah oo.com> 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
$AnotherOB j->SomeMethod ("Foo");
$AnotherOB j->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('RstU sers',DATASOURC E);

$oRst->SetDefinitionF romDatasource(a rray('Users'=>' '));

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

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

$aSQL=array('Co mmand'=>'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.RstT est.ANYRECORDS>
<table>
<tr><td><$RST.R stTest.CAPTION. username></td></tr>
<$ITERATE.RST.R stTest>
<tr><td><$RST.R stTest.FIELD.us ername></td></tr>
<$/ITERATE>
</table>
<$TEST.ELSE>
<p> No records</p>
<$/TEST>
But we do more as well. Guess what this does.

<RST.RstTest.PA GER>

<$TEST.RST.RstT est.ANYRECORDS>
<table>
<$RST.RstTest.A LLCAPTIONSORTER >
<$ITERATE.RST.R stTest>
<tr><$RST.RstTe st.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(arra y('Command'=>'S elect','Limit'= >2));
$oRst->SetSorterPager FromURL();
$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>

We have never used it. Really?
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.
As far as database abstraction. You have to have a select or equeivelent,
there has to be a where or equivelent, and there should be somekind of
offset and of limit, and even or orderby. The only place I can see SQL
snaek in is in the 'where's. But that would surely be easy to extend. You
could even parse the sql out it only contains "field operation value" type
stuff.

Then the array is passed via the recordset to some database class that
figures out what it needs to do, and provides that back to the recordset.

The recordset can contain fields from >1 tables, and the sql is abstracted
such that we cover field level securty, automated joins and outer joins.
ther recordset can also contain other recordsets to cover the one to many
relationship and even related recordsets covering the many to many
relationship.

$oRst= new Recordset('Rst' ,DATASOURCE);
$oRst->SetDefinitionF romDatasource(a rray('Users'=>' ','RoleMembers' =>''));

where the following is approximately true:
$gaDatasources[DATASOURCE]['Tables']['Users']=array(
'username'=>arr ay('Type'=>'Tex t','Size'=>'30' ,'Caption'=>'Us er
Name','Validata tion'=>array('N otBlank'),'PriK ey'=>1,'StrToUp per'=>1),
'email'=>array( 'Type'=>'Text', 'Size'=>'200',' Caption'=>'Emai l Address',
'Validation'=>a rray('NotBlank' ,'EmailAddress' ,'Unique'))
);

$gaDatasources[DATASOURCE]['Tables']['Role']=array(
'roleid' =>array('Type'= >'Text','Size'= >10,'Caption'=> 'RoleID'),
'roledesc'=>arr ay('Type'=>'Tex t','Size'=>200, 'Caption'=>'Rol eDesc'),
);

$gaDatasources[DATASOURCE]['Tables']['RoleMembers']=array(
'roleid' =>array('Type'= >'Reference','R efTable'=>'Role ',
'RefField'=>'ro leid', 'RefDisplayFiel d'=>'roledesc') ,
'username'=>arr ay('Type'=>'Ref erence','RefTab le'=>'Users',
'RefField'=>'us ername','RefDis playField'=>'Us erName'),
);

$oRst->ExecuteSQLArr( array('Command' =>'Select'));
So I wrote a couple of classes (i.e. Object and ObjectList) which used a
flat file.Â*Â*TookÂ* meÂ*anÂ*hourÂ*o rÂ*soÂ*andÂ*IÂ* haveÂ*something Â*whichÂ*works
OK, flat file stuff, that's easier.
What happens if the column "username" changes, for
instance? A proper abstraction layer removes all dependencies from the
database.
Well, you need to have some unique thing that represents the column
username, why not use it's name?
Oh, and BTW - my company makes money writing PHP, also. I suspect most of
us here work are in the same boat.

I thought most here have never programmed at all before and would quite like
to make a cms.

[Tim Van Wassenhove]

On 2005-07-30, richard <ri*****@localh ost.localdomain > wrote:

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.

[snip code]

These are a couple of questions i have with a lot of classes/functions
that proclaim the query generation:

- Does it allow you add a column LIKE 'value%' instead of
column='value'?

- Does it allow you to have a "complex" condition like
column=MAX(colu mn) or is that translated to column='MAX(col umn)'?

- How does it handle with values like O'Reilly? (It would be silly if
you have to escape the value yourself.)

--
Met vriendelijke groeten,
Tim Van Wassenhove <http://timvw.madoka.be >

[Jerry Stuckle]

richard wrote:

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.

As far as database abstraction. You have to have a select or equeivelent,
there has to be a where or equivelent, and there should be somekind of
offset and of limit, and even or orderby. The only place I can see SQL
snaek in is in the 'where's. But that would surely be easy to extend. You
could even parse the sql out it only contains "field operation value" type
stuff.

Yes, you need functions to retrieve data, add new data, change the data that is
there and remove old data. There should be some mechanism to sort the data and
possibly even limit the amount of data returned.
Then the array is passed via the recordset to some database class that
figures out what it needs to do, and provides that back to the recordset.

The recordset can contain fields from >1 tables, and the sql is abstracted
such that we cover field level securty, automated joins and outer joins.
ther recordset can also contain other recordsets to cover the one to many
relationship and even related recordsets covering the many to many
relationship.

$oRst= new Recordset('Rst' ,DATASOURCE);
$oRst->SetDefinitionF romDatasource(a rray('Users'=>' ','RoleMembers' =>''));

where the following is approximately true:
$gaDatasources[DATASOURCE]['Tables']['Users']=array(
'username'=>arr ay('Type'=>'Tex t','Size'=>'30' ,'Caption'=>'Us er
Name','Validata tion'=>array('N otBlank'),'PriK ey'=>1,'StrToUp per'=>1),
'email'=>array( 'Type'=>'Text', 'Size'=>'200',' Caption'=>'Emai l Address',
'Validation'=>a rray('NotBlank' ,'EmailAddress' ,'Unique'))
);

$gaDatasources[DATASOURCE]['Tables']['Role']=array(
'roleid' =>array('Type'= >'Text','Size'= >10,'Caption'=> 'RoleID'),
'roledesc'=>arr ay('Type'=>'Tex t','Size'=>200, 'Caption'=>'Rol eDesc'),
);

$gaDatasources[DATASOURCE]['Tables']['RoleMembers']=array(
'roleid' =>array('Type'= >'Reference','R efTable'=>'Role ',
'RefField'=>'ro leid', 'RefDisplayFiel d'=>'roledesc') ,
'username'=>arr ay('Type'=>'Ref erence','RefTab le'=>'Users',
'RefField'=>'us ername','RefDis playField'=>'Us erName'),
);

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


Damn that looks needlessly complicated!

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

OK, flat file stuff, that's easier.

I find it much harder than database stuff. You need to parse the file, ensuring
that the data is "clean". Searching and sorting must be done manually.
Inserting can be even more difficult. BTW - the same MySQL code took me about
20 minutes to write (it's not a huge table).

Give me a good database any day.

What happens if the column "username" changes, for
instance? A proper abstraction layer removes all dependencies from the
database.

Well, you need to have some unique thing that represents the column
username, why not use it's name?

Again - what if the database changes? For instance - what if someone decides it
should be "user"? Or what happens if they want it as "FirstName" and "LastName"?

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

I thought most here have never programmed at all before and would quite like
to make a cms.

I think most here HAVE programmed. There are more questions from the newbies -
but that's because they HAVE more questions.

Personally, I've been programming for almost 40 years. Started doing SQL
programming around 1983 and OO programming somewhere near 1988. Only been doing
PHP for five years or so.
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===