|
|
Posted by richard on 07/30/05 16:09
Jerry Stuckle wrote:
> richard wrote:
>> On 2005-07-15, Scott Auge <scott_auge@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'=>'','RoleMembers'=>''));
where the following is approximately true:
$gaDatasources[DATASOURCE]['Tables']['Users']=array(
'username'=>array('Type'=>'Text','Size'=>'30','Caption'=>'User
Name','Validatation'=>array('NotBlank'),'PriKey'=>1,'StrToUpper'=>1),
'email'=>array('Type'=>'Text','Size'=>'200','Caption'=>'Email Address',
'Validation'=>array('NotBlank','EmailAddress','Unique'))
);
$gaDatasources[DATASOURCE]['Tables']['Role']=array(
'roleid' =>array('Type'=>'Text','Size'=>10,'Caption'=>'RoleID'),
'roledesc'=>array('Type'=>'Text','Size'=>200,'Caption'=>'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'=>'UserName'),
);
$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.
Navigation:
[Reply to this message]
|