By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,282 Members | 1,518 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,282 IT Pros & Developers. It's quick & easy.

Abstracting SQL Statements In An Object

P: n/a

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!
Jul 17 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
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*******@attglobal.net
==================
Jul 17 '05 #2

P: n/a
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.

Jul 17 '05 #3

P: n/a
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********@yahoo.com> wrote in message
news:sc******************************@news1.west.e arthlink.net...

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!

Jul 17 '05 #4

P: n/a
[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

Jul 17 '05 #5

P: n/a
On 2005-07-15, Scott Auge <sc********@yahoo.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('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..

Jul 30 '05 #6

P: n/a
richard wrote:
On 2005-07-15, Scott Auge <sc********@yahoo.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('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.
js*******@attglobal.net
==================
Jul 30 '05 #7

P: n/a
Jerry Stuckle wrote:
richard wrote:
On 2005-07-15, Scott Auge <sc********@yahoo.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('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>


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->SetDefinitionFromDatasource(array('Users'=>'','Ro leMembers'=>''));

where the following is approximately true:
$gaDatasources[DATASOURCE]['Tables']['Users']=array(
'username'=>array('Type'=>'Text','Size'=>'30','Cap tion'=>'User
Name','Validatation'=>array('NotBlank'),'PriKey'=> 1,'StrToUpper'=>1),
'email'=>array('Type'=>'Text','Size'=>'200','Capti on'=>'Email Address',
'Validation'=>array('NotBlank','EmailAddress','Uni que'))
);

$gaDatasources[DATASOURCE]['Tables']['Role']=array(
'roleid' =>array('Type'=>'Text','Size'=>10,'Caption'=>'Role ID'),
'roledesc'=>array('Type'=>'Text','Size'=>200,'Capt ion'=>'RoleDesc'),
);

$gaDatasources[DATASOURCE]['Tables']['RoleMembers']=array(
'roleid' =>array('Type'=>'Reference','RefTable'=>'Role',
'RefField'=>'roleid', 'RefDisplayField'=>'roledesc'),
'username'=>array('Type'=>'Reference','RefTable'=> 'Users',
'RefField'=>'username','RefDisplayField'=>'UserNam e'),
);

$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¬*or¬*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.

Jul 30 '05 #8

P: n/a
On 2005-07-30, richard <ri*****@localhost.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(column) or is that translated to column='MAX(column)'?

- 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>
Jul 30 '05 #9

P: n/a
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->SetDefinitionFromDatasource(array('Users'=>'','Ro leMembers'=>''));

where the following is approximately true:
$gaDatasources[DATASOURCE]['Tables']['Users']=array(
'username'=>array('Type'=>'Text','Size'=>'30','Cap tion'=>'User
Name','Validatation'=>array('NotBlank'),'PriKey'=> 1,'StrToUpper'=>1),
'email'=>array('Type'=>'Text','Size'=>'200','Capti on'=>'Email Address',
'Validation'=>array('NotBlank','EmailAddress','Uni que'))
);

$gaDatasources[DATASOURCE]['Tables']['Role']=array(
'roleid' =>array('Type'=>'Text','Size'=>10,'Caption'=>'Role ID'),
'roledesc'=>array('Type'=>'Text','Size'=>200,'Capt ion'=>'RoleDesc'),
);

$gaDatasources[DATASOURCE]['Tables']['RoleMembers']=array(
'roleid' =>array('Type'=>'Reference','RefTable'=>'Role',
'RefField'=>'roleid', 'RefDisplayField'=>'roledesc'),
'username'=>array('Type'=>'Reference','RefTable'=> 'Users',
'RefField'=>'username','RefDisplayField'=>'UserNam e'),
);

$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*******@attglobal.net
==================
Jul 30 '05 #10

P: n/a
Tim Van Wassenhove wrote:
On 2005-07-30, richard <ri*****@localhost.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(column) or is that translated to column='MAX(column)'?

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


Well, the where bit is a little so so. But the sql array tells a recordset
object that contains field objects to load from the db. Each field object
has function GetValue() GetHTMLValue() GetSQLValue() etc. But sometimes you
want like sometimes you want ilike, sometimes you want like, sometimes you
want =. This is not a field dependent thing.

"Where"s are complex for that very reason. MySQL,Postgresql and SQLLite are
all convergent on the where's (except with sub queries) so you can follow
them across.

When doing complex searching for recordset based stuff - where say a field
is an integer and the search is a range between two values you have two
cases. One, is that the query is a system query and so a little bit of
query building may be required. The other is that the search is based on
user input. In which case we build a form using an administration block (a
type of class) that can also show the results in a Sorter-Pager.

We have a couple of these, and you specify like or whatever in a definition,
which of course, you can override after object creation should you so
decide.

It is worth saying that we have some complex queries that are crammed into
the sql array (as using our sql arrays allows sorter - pagers to work
correctly), building up a tool to autogenerate such queries would be
counter productive.

for example:
(SELECT url.*, docs.blockid, docs.title, docs.startdatetime, docs.ref,
docs.summary, docs.keywords, docs.filename, docs.filesize, docs.filecode ,
lower(docs.title) as titlelower, 0 as isurllink, lower(docs.ref) as
reflower, blocks.createddatetime, blocks.updateddatetime, blocks.def FROM
(url JOIN docs USING (blockid)) JOIN blocks USING (blockid) WHERE
(docs.usestopdatetime=false OR docs.stopdatetime>current_date) and
url.type='Page' AND url.subtype LIKE 'Document%' AND url.parenturlid=1744
UNION SELECT url.*, docs.blockid, docs.title, docs.startdatetime, docs.ref,
docs.summary, docs.keywords, docs.filename, docs.filesize, docs.filecode ,
lower(docs.title) as titlelower, 1 as isurllink, lower(docs.ref) as
reflower, blocks.createddatetime, blocks.updateddatetime, blocks.def FROM
((urllinks JOIN url ON (urllinks.urlid=url.urlid AND
urllinks.parenturlid=1744)) JOIN docs USING (blockid)) JOIN blocks USING
(blockid) WHERE (docs.usestopdatetime=false OR
docs.stopdatetime>current_date) and url.type='Page' AND url.subtype LIKE
'Document%') ORDER BY startdatetime DESC, titlelower DESC, reflower DESC

Thats 5 joins and a union using 4 tables. Sometimes its hard enough to come
up with the sql let alone code something to build it.

Jul 30 '05 #11

P: n/a
In article <sl********************@localhost.localdomain>,
richard <ri*****@localhost.localdomain> wrote:
On 2005-07-15, Scott Auge <sc********@yahoo.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('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..

That is certainly a way to do it and peaks my interest...

But the whole idea was to abstract away from the developer the need to
know the database relations, field names, etc.

I want them to know the objects to perform a certain piece of
functionality. The people who maintain the database (a more advanced
group of developers) will maintain these objects so the publicly
available attributes and methods are the same or added to.

If I hire someone, I can make them more productive faster by saying "If
you want to create a sales order, use the object SalesOrder, "call this
method/fill these attributes with" with these values and then call
Create(). In short, it is easier to learn three lines of code than it
is to craft an SQL statement with knowledge of the database (and it's
denormalized quirks), etc.

I have been getting some ideas rolling around in my head and will make
them public when they are better formed.


--
Available for Hire! http://amduus.com/Resumes/
Jul 30 '05 #12

P: n/a
Jerry Stuckle wrote:
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->SetDefinitionFromDatasource(array('Users'=>'','Ro leMembers'=>''));

where the following is approximately true:
$gaDatasources[DATASOURCE]['Tables']['Users']=array(
'username'=>array('Type'=>'Text','Size'=>'30','Cap tion'=>'User
Name','Validatation'=>array('NotBlank'),'PriKey'=> 1,'StrToUpper'=>1),
'email'=>array('Type'=>'Text','Size'=>'200','Capti on'=>'Email Address',
'Validation'=>array('NotBlank','EmailAddress','Uni que'))
);

$gaDatasources[DATASOURCE]['Tables']['Role']=array(
'roleid' =>array('Type'=>'Text','Size'=>10,'Caption'=>'Role ID'),
'roledesc'=>array('Type'=>'Text','Size'=>200,'Capt ion'=>'RoleDesc'),
);

$gaDatasources[DATASOURCE]['Tables']['RoleMembers']=array(
'roleid' =>array('Type'=>'Reference','RefTable'=>'Role',
'RefField'=>'roleid', 'RefDisplayField'=>'roledesc'),
'username'=>array('Type'=>'Reference','RefTable'=> 'Users',
'RefField'=>'username','RefDisplayField'=>'UserNam e'),
);

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


Damn that looks needlessly complicated!


Well its doing joins, you have all the form validation
($oForm->AddRecordsetFields(&$oRst))
you have captions and you have the relationships. And that's without me
mentioning WidgetTypes..
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"?


Then you have to change things. Are you suggesting we start refering to
columns as something unrelated to their function. Sounds like a recipe for
disaster.

perl -p -i -e 's/username/user/' *
might in some cases do the job if username is guarentted to be unique.

You can't go changing database schemas willy nilly without expecting some
fall back.

From my example the only time you hit grief is with where clauses. Usually
you need to know what is going on with the column to do a where clause
involving it. If the column name changes you have to do a bit of work on a
few where clauses. And with our code, depending how well you used the
templating you may have to change the templates. Although that would be a
case of
perl -p -i -e 's/RST\.RstName\.FIELD\.username/RST.RstName.FIELD.user/g' *

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.


So are you a guru? Or stuck in your ways? Or somewhere between. It seems odd
to find someone who was probably doing cobol while I was in nappies doing
PHP. Surely someone with that much experience is working for IBM as some
kind of god put on some kind of plinth to show the younger programmers what
they could achieve.
Jul 30 '05 #13

P: n/a
Scott Auge wrote:
In article <sl********************@localhost.localdomain>,
richard <ri*****@localhost.localdomain> wrote:
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..

That is certainly a way to do it and peaks my interest...

But the whole idea was to abstract away from the developer the need to
know the database relations, field names, etc.

I want them to know the objects to perform a certain piece of
functionality. The people who maintain the database (a more advanced
group of developers) will maintain these objects so the publicly
available attributes and methods are the same or added to.

If I hire someone, I can make them more productive faster by saying "If
you want to create a sales order, use the object SalesOrder, "call this
method/fill these attributes with" with these values and then call
Create(). In short, it is easier to learn three lines of code than it
is to craft an SQL statement with knowledge of the database (and it's
denormalized quirks), etc.

Well yes, I think my suggestion helps you create a SalesOrder Object, or any
other object more quickly...

Getting people to like our code is going to take some effort I think. And
upon thinking that I feel that its going to take another 3 months than it
was going to anyway...
I have been getting some ideas rolling around in my head and will make
them public when they are better formed.


Jul 30 '05 #14

P: n/a
On 2005-07-30, richard <Ri*****@localhost.localdomain> wrote:
Tim Van Wassenhove wrote:
On 2005-07-30, richard <ri*****@localhost.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(column) or is that translated to column='MAX(column)'?

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

When doing complex searching for recordset based stuff - where say a field
is an integer and the search is a range between two values you have two
cases. One, is that the query is a system query and so a little bit of
query building may be required. The other is that the search is based on
user input. In which case we build a form using an administration block (a
type of class) that can also show the results in a Sorter-Pager.


I'm aware of the approaches. I was just curious to see implementations
;)

Currently i prefer to accept a WHERE clause but as a prepared statement.
So the code looks like: $model->get("foo > ?", array(10));
--
Met vriendelijke groeten,
Tim Van Wassenhove <http://timvw.madoka.be>
Jul 30 '05 #15

P: n/a
richard wrote:

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"?

Then you have to change things. Are you suggesting we start refering to
columns as something unrelated to their function. Sounds like a recipe for
disaster.

The whole purpose of abstraction is to make the program independent of the
storage of the data. User name may, for instance, be in one, two, three or even
four columns.

perl -p -i -e 's/username/user/' *
might in some cases do the job if username is guarentted to be unique.

Maybe it is, maybe it isn't.
You can't go changing database schemas willy nilly without expecting some
fall back.

With true abstraction you can. Only the abstract layer changes. Nothing
extends beyond it.
From my example the only time you hit grief is with where clauses. Usually
you need to know what is going on with the column to do a where clause
involving it. If the column name changes you have to do a bit of work on a
few where clauses. And with our code, depending how well you used the
templating you may have to change the templates. Although that would be a
case of
perl -p -i -e 's/RST\.RstName\.FIELD\.username/RST.RstName.FIELD.user/g' *

No, I really don't need to know what's going on in the column for a where
clause. I can completely abstract it.

So are you a guru? Or stuck in your ways? Or somewhere between. It seems odd
to find someone who was probably doing cobol while I was in nappies doing
PHP. Surely someone with that much experience is working for IBM as some
kind of god put on some kind of plinth to show the younger programmers what
they could achieve.


Several of my corporate customers consider me a guru. And no, I never did
Cobol. I did, however, start with Fortran II. In the almost 40 years since
I've done probably at least 20 other languages, of which I'm still fluent in a
few - C, C++, Java, JavaScript, PHP, SQL. I'm conversant in a few other
languages, but quite frankly I've forgotten more than I remember.

And why should it surprise you that someone is keeping up with the times? It's
called progress.

Also - I have shown a LOT of programmers what they could achieve. I've spent 15
years doing corporate training and consulting. In that time I've trained
thousands of programmers. And I've been paid quite well for it.

But I got tired of all the travel - Germany to Hong Kong and all over the U.S.
Running almost 100K air miles per year, staying in motels 125-150 nights per
year. I've done my dues. I want to spend my nights at home. So I do some
consulting around this area and some web sites. I also have another unrelated
business I'm starting.

And I spend time on the forums helping people who's don't think they know
everything.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jul 30 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.