Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > Pro PHP
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Pro PHP Advanced PHP coding discussions. Beginning-level questions will be redirected to the Beginning PHP forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro PHP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 3rd, 2005, 05:49 PM
Registered User
 
Join Date: Dec 2004
Location: , TX, .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Data Abstraction Layer

Hello,

I have set up a Data Abstraction Layer w/ the following interface:

class Database {
...
    select($sql)
    update($table, $arUpdates, $sWhere = null)
    insert($table, $arValues, $wantID = false)
    delete($table, $sWhere)
...
}

When either the 'update' or 'insert' function is called, the $arUpdates/$arValues array contains field => value pairs of the fields to be set.


Ex:

$arValues = array(
        'var1' => 'value1',
        'var2' => 'value2'
    );

$database->insert('some_table', $arValues);


Now here is the important part. In order to keep the update & insert usage simple, the caller of the functions is not required to put quotations (" or ') around STRINGS. The insert & update functions automatically take care of this if the value is a string. The problem, then, is what to do when the value is an SQL function ("NOW()" for example). I could have the functions explicitly check for "NOW()" functions, and a few others, but this obviously isn't the best solution, nor is it desirable.


Ex of the problem:

$arValues = array(
        'var1' => 'some string',
        'var2' => 1,
        'var3' => 'NOW()'
    );

$database->insert('some_table', $arValues);


The 'var1' variable is a string, so it is automatically surrounded by quotations. The 'var2' variable is a number, which is also detected and inserted properly. The 'var3' variable is a string, so it is surrounded in quotations. This is where the problem lies, since we don't want 'NOW()' surrounded by quotations in the SQL query.

Any suggestions?

Thanks.

Reply With Quote
  #2 (permalink)  
Old January 3rd, 2005, 08:02 PM
Registered User
 
Join Date: Dec 2004
Location: , TX, .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I guess what I am implementing is more of a "Data Access Layer", but I do have an abstraction layer elsewhere in my project through the use of Data Access Objects.

Just thought I'd clear up any misunderstandings :)

One possible solution is to just avoid using the 'NOW()' function and use PHP's built in 'date(...)' function. This temporarily solves my problem. I may eventually need to use other built-in SQL functions, however, so other suggestions would be appreciated. :)

Reply With Quote
  #3 (permalink)  
Old January 13th, 2005, 06:59 AM
Registered User
 
Join Date: Jan 2005
Location: , , USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I had the same problem myself when I implemented a database abstraction layer using Pear::DB

when i pass an array to be processed by my insert, update, and delete methods, i pass in an array of values like you're suggesting.
for instance:

//caller
$arrUpdates['timestamp'] = "now()";
databaseHandlerInstance->update($otherparameters, $arrUpdates);

//in your data abstraction method for updating
foreach($arrUpdates as $name => $value) {
    $addString = $name . " = ";
    if ($value == 'now()') {
        $addString .= $value;
    } else {
        $addString .= $this->_connection->quoteSmart($value);
    }
    $arrSet[] = $addString;
}
//further instructions to pass your query statement

I was getting mysql syntax errors until I added that conditional. It works after that and you don't have to have the callers pre-format their strings this way. I hope this helps! I'm halfway delirious from lack of sleep, so I hope I didn't miss the mark on your question completely.

Reply With Quote
  #4 (permalink)  
Old January 13th, 2005, 12:58 PM
Registered User
 
Join Date: Dec 2004
Location: , TX, .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, the problem with doing that is that it only works with the 'now()' function. What if I were to save a password as 'md5($password)'? It would then be stored as 'md5([un-encrypted password])'. The only solution I can think of to accomodate ALL functions would be to search the string for '(' and ')', but even that is not safe. Values like 'This is some text (with parenthesized explanations)' would not work properly. The only real way I can think of to fool-proof it would be to explicitly tell the abstraction layer what each value is. This could be done as follows:

array (
    'username' => array( 'type' => 'string', 'value' => 'somebody' ),
    'password' => array( 'type' => 'function', 'value' => 'md5(somepass)' )
)

Of course, this is extremely annoying considering most of your inputs will not be functions, and so this a rare case. Therefore, maybe you could assume that the value is a string/int unless specifically told that it isn't:

$arUpdates = array (
    'username' => 'somebody',
    'password' => array( 'type' => 'function', 'value' => 'md5(somepass)' )
)

The db layer can then check each index to see if it is an array. If it is, check the 'type' index:

$fields = array();
$values = array();

foreach ($arUpdates as $key => $value) {
    $type = 'string'; // default
    $val = $value;

    $fields[] = $key;

    if (is_array($value)) {
        $type = $value['type'];
        $val = $value['value'];

        if ($type == 'function') {
            $values[] = $val;
        }
        else {
            if ($val == null) {
                $values[] = 'NULL';
            }
            else {
                $values[] = '"'.$val.'"';
            }
        }
    }
    else {
        $values[] = '"'.$val.'"';
    }
}

Of course, I ignored the special quoting that is required. Note that quoting will be a problem if the value is a 'function', because you must quote whatever the parameter of the function is. This could be difficult to find, considering the parameter could have parenthesis in it.

All in all, I think it is a pretty ridiculous task to accomplish. It is probably best just to require the calling function to deal with it. Considering that most modern languages can do anything that a database can, this shouldn't be too much of a problem. For example, PHP has a 'date(...)' function, as well as an 'md5(...)' function.

Reply With Quote
  #5 (permalink)  
Old January 15th, 2005, 01:44 AM
Registered User
 
Join Date: Jan 2005
Location: , , USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

first off, you're right about that being a problem if you want to pass those in more than one database function as a value. however, md5() is a PHP function that you can call from your script so the value you pass to your database is actually just the md5 result, which is how i have mine set up. then you don't have to worry about parsing parentheses and all that. if you were concerned about a limited set of database functions as values, you can always set up an associative array and check the value against each of those in a foreach loop. if you're calling a large suite of database functions in your application, that points to a mismanagement of your n-tier architecture.

instead of $arrValues['password'] = 'md5($password)':
//use PHP's function instead
$arrValues['password'] = md5($password);

i can't really think of a reason why you would want to call that function at the DBMS.

furthermore,change the:
if ($value == "now()") {//build your query}

to something like this:

foreach ($arrDBFunctions as $dbFunction) {
  if ($value == $dbFunction) {//build your query}
}

how you populate $arrDBFunctions is up to you. you should be using php to do most of the functional logic and thinking and use your database to just manage data storage since that is how middleware technology and backend technology are optimized. PHP provides you with an incredible amount of powerful processing features and scales much better.

what i called a "database abstraction layer" is a static "data access layer" component. all of my domain layer is derived from generic objects that depend on the component to populate themselves, so correct me if i'm wrong but i believe we were talking about the same thing.
Reply With Quote
  #6 (permalink)  
Old January 15th, 2005, 02:05 PM
Registered User
 
Join Date: Dec 2004
Location: , TX, .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Currently, I am using md5 (actually, sha1) in PHP rather than in the DBMS. I don't think its really worth it in the long run to throw in the extra functionality, because, as you said, PHP can do pretty much everything the DBMS can do, and probably faster.

Also, it is a Data Access Layer. When I first posted this topic, I was unaware of the difference :) I do have a Data Abstraction Layer through the use of objects that access the db. That has a term, too, but I have forgotten it. Database Access Objects or something. But anyway, I have decided to just not worry about the problem for now because it isn't really worth it.

However, I do have another interesting problem that might warrant some discussion.

If you have read the book "Professional PHP5", then you will notice that the GenericObject has a 'save()' method that must be called in order to save changes that have been made to an object.

Example:

$go = new GenericObjectSubclass(1);
$go->setSomeValue($var);
$go->setAnotherValue($var);
$go->save();

To make my DAO's hide the database even more, I was trying to find a way to have the objects automatically save themselves without having to explicitly call the 'save' method every time. Obviously, I don't want a database access EVERY time a value is changed. My initial idea was to access the database in the destructor. This turned out to be a stupid idea because the Database object usually destructs before the DAO's (note: the Database uses the singleton pattern). I then tried an event-handler that would tell the objects to save themselves when the database was being destructed. The problem with this was (of course) that the database can be destroyed after the objects. This can be fixed in various ways, but we are still left with another major problem: When there is an error or exception, in which case we DON'T want the objects to be saved, they are saved anyway since the __destruct() method is called regardless of errors. One solution would be to just catch any exceptions that occur within the GenericObject class, set some flag that tells it not to save itself, and rethrow the exception. Unfortunatly, subclasses would also have to realize this and catch all exceptions...leading to a very annoying cycle. I eventually gave up and considered the problem too difficult to solve for the benefits that it brings.

It still remains an interesting problem, though. Any ideas?

Reply With Quote
  #7 (permalink)  
Old March 18th, 2006, 04:29 PM
Authorized User
Points: 209, Level: 4
Points: 209, Level: 4 Points: 209, Level: 4 Points: 209, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2005
Location: , , .
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi there,

I came across this post whilst I was browsing around for ideas on my own Data Access Layer in .Net

I don't know much about the structure of PHP and if my solution will work, but I'll mention it just in case.

The solution I use is to create a new structure for database functions.

So as well as the usual stuctures (Integer, String, Decimal, etc.) I have a DatabaseFunction structure which in effect is identical to a string.

Using your methodology, what I'd do is...

In the code where I iterate through $arUpdates (in Database.update() ) to discover the type of each value, I'd handle DatabaseFuction differently from String and differently from Integer.

Having said all of that. It's probably bad practice to be implementing database specific functions in a generic data access layer. I assume judging by your title you intend to abstract your data access layer so it is not tied to a specific database vendor.

Therefore, rather than implementing the proprietary mySql function, you should implement only the SQL99 functions (eg. CURRENT_TIMESTAMP) instead.

Then if you want to use another database in future, you'll find it much easier.


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Handle error from database layer to applican layer khatu_jec ASP.NET 2.0 Basics 1 November 9th, 2008 03:51 PM
UI Abstraction layer navdeep General .NET 10 October 9th, 2007 06:22 PM
Independent Data Access Layer Sumith Asanka .NET Framework 2.0 2 September 28th, 2007 02:20 AM
Database abstraction layer - OOP style w. paginati raul.ionescu PHP Databases 4 May 26th, 2006 03:19 PM
Database Abstraction Layer browngb BOOK: Professional PHP 5 ISBN: 978-0-7645-7282-1 4 April 30th, 2005 03:34 PM



All times are GMT -4. The time now is 04:46 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.