Re: Abstracting SQL Statements In An Object

Jerry Stuckle wrote:

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

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.

.

Relevant Pages

  • Re: Validate logins with ASP, MS Access and Cookies error
    ... ' Opens the returned values from the SQL as a recordset, ... ' validate variables against database ...
    (microsoft.public.inetserver.asp.db)
  • Re: HELP PLEASE: Populate certain userform fields from access database
    ... Opening a database you don't need to open a file like opening an excel ... This includes a SQL (Script Query Language). ... Move the Recordset data to the workbook. ...
    (microsoft.public.excel.programming)
  • Re: HELP PLEASE: Populate certain userform fields from access data
    ... Subjects to auto populate,, and all this details are in access database. ... '2) Open a recordset which is a set of instructions of what data you ... This includes a SQL (Script Query Language). ...
    (microsoft.public.excel.programming)
  • Re: use of CRecordset
    ... If you are inserting well behaved data, the SQL INSERT ... Do not delegate the management of the database connection ... How can I delegate the management of the database without Recordset ... > Never open a query using the SQL INSERT comand. ...
    (microsoft.public.vc.mfc)
  • Re: Cursor type changed: What the #!@$%#?
    ... Something I found in this code is that in no instance is a recordset object ... All database calls are made by making a call to an included ... MsgBox cn.Errors.Count ... Which service pack of the SQL Server is using? ...
    (microsoft.public.data.odbc)