Originally Posted by tripy

Not necessarily, but because of db engines specificity, not of the connection layer.

I'm getting a little confused, I think. My impression was that something like ADODB would create the query for me according to the database being used. So, for example, if I was using something that's been tested like ADODB, if the query ADODB constructed for me worked for MySQL, I was assuming that it should also work for the other databases that ADODB supports. Am I totally out in left field on that?

I really do appreciate everyone's help on this. Thank you.

Originally Posted by VirtuosiMedia

If I use something that's been around for a while, like ADODB or Zend_Db, instead of creating my own, if it works with MySQL, shouldn't it also work for the others? That was my thinking, though I've probably gone back and forth a bit in this thread because I'm still learning.

I see. I've got many years experience with ADODB, and I can say with 100 % certainty that it doesn't provide the type of abstraction you're talking about. The ADODB library lets you send a SQL command from your application into the database engine, but it simply passes along whatever command you send. It doesn't translate it from one SQL dialect into another.

I really do appreciate everyone's help on this. Thank you.

you're welcome, we all learn from discussions like that one.

My impression was that something like ADODB would create the query for me according to the database being used.

I never used ADODB, but every other db abstraction layer I ever used was a db connection abstraction layer.
You still had to write your queries, and you used the framework to send the query to the db.

No rewriting of the content of the query would be done.
It's simply way too complicated, and the result is not predictable on complicated queries.

Sure, you could extend one yourself, replacing some keywords (like SYSDATE, NOW() and the like) before sending the query to the db, but when it comes to more intricate options or capacity of a database, how would you handle them ?

For example, in Oracle, you cannot use the limit xx offset yy syntax in the where clause.
You have to use an inner query.
Look:
For mysql:

select *
from tbl1
where active=1
order by dtActivation desc
limit 20 offset 300

for oracle:

select * from(
  select * from tbl1
  where active=1
  order by dtActivation desc
)
where rownum >=300
and rownum <=320

This, because Oracle put number on rows BEFORE ordering them.

A framework that would rewrite the queries for different DB knowing what you are trying to achieve, would be a major brick in artificial intelligence (in my opinion).

Originally Posted by Learning Newbie

I see. I've got many years experience with ADODB, and I can say with 100 % certainty that it doesn't provide the type of abstraction you're talking about. The ADODB library lets you send a SQL command from your application into the database engine, but it simply passes along whatever command you send. It doesn't translate it from one SQL dialect into another.

I guess I'm still a little confused. I've been reading the docs for the Zend_Db class and it made me think that it accomplished something similar. Not that it generated the SQL on the fly, necessarily, but that it allowed you to set up a database specific adapter in the config that would use the class and methods and apply them to that database. The abstraction part came in by the way the different database adapters interpreted the class and methods. Because I saw that you could substitute ADODB for Zend_Db, I assumed that they were similar. If they don't do that, what does they do exactly?

Originally Posted by tripy

you're welcome, we all learn from discussions like that one.

On that note, to get the current system date in Microsoft SQL Server, which you mentioned how to do earlier for MySQL and Oracle, you would use either of these built in functions

• GetDate()
• GetUtcDate()
• DateAdd(hh, -7, GetUtcDate())

The last would give you the current system time in California.

Originally Posted by tripy

I never used ADODB, but every other db abstraction layer I ever used was a db connection abstraction layer.
You still had to write your queries, and you used the framework to send the query to the db.

Exactly. That's what ADODB is. There are facilities in ADO.NET that will write simple queries for you, to do object relational mapping. I don't think they're featured or flexible enough for production use. More can be seen on MSDN's "Weaning Developers From the CommandBuilder".

Originally Posted by tripy

No rewriting of the content of the query would be done.

Think of the giant potential for error! Would you want to be responsible for rewriting somebody's DELETE query that uses joins and subqueries, from MySQL to Oracle?

Originally Posted by tripy

Sure, you could extend one yourself, replacing some keywords (like SYSDATE, NOW() and the like) before sending the query to the db, but when it comes to more intricate options or capacity of a database, how would you handle them ?

How would that work, tho? Suppose I have a command like this?

PHP Code:

INSERT INTO dbo.PendingDataCommands ( CommandText )
VALUES ( 'Select Now()' ) 


Should the pseudo call to the Now() function be rewritten?


I don't know if this returns us to the question at hand, but I think it's valuable to communicate with the database in a modular and generic way, so that you can redirect a MySQL connection to SQL Server. But I also think it makes sense not to generate database commands on the fly, but rather to use stored procedures as a database's API. I don't see how it can be avoided to write and also test SQL for each specific back end, and that being the case, I think little or nothing is gained by not separating database code from calling application code. On the other hand, I think intermingling the two will have very real costs.

To put it another way, following my example before, if your MySQL database has a sproc called "AddUser" that takes a set of parameters, and your SQL Server database, when you get to needing one, has a sproc called "AddUser" that takes the same set of parameters, and your Oracle database, when the time comes, also has an "AddUser" sproc that again has the same calling signature, then you can easily repoint your connection string to address whichever system you'd like. These procedures would be tested in very much the same way as the code that generates the individual SQL commands would be, except that procedures are more easily understood, and because of it, are slightly less work to test. This is mainly because they're complete and functional code units. You get to test the SQL as it is, and not the PHP code that, given the right inputs, generates SQL code that may or may not be what you expect.

Originally Posted by VirtuosiMedia

I guess I'm still a little confused. I've been reading the docs for the Zend_Db class and it made me think that it accomplished something similar. Not that it generated the SQL on the fly, necessarily, but that it allowed you to set up a database specific adapter in the config that would use the class and methods and apply them to that database. The abstraction part came in by the way the different database adapters interpreted the class and methods. Because I saw that you could substitute ADODB for Zend_Db, I assumed that they were similar. If they don't do that, what does they do exactly?

They do the same thing the classes I've uploaded do.
They abstract the connection, and the retrieval/processing of the data.

Some methods have been added, like

PHP Code:

$n = $db->delete('bugs', 'bug_id = 3'); 


which is just a shortcut to a simple "delete from bugs where bug_id = 3" query, but in no way a query rewriting tool

Originally Posted by tripy

For example, in Oracle, you cannot use the limit xx offset yy syntax in the where clause.
You have to use an inner query.
Look:
For mysql:

select *
from tbl1
where active=1
order by dtActivation desc
limit 20 offset 300

for oracle:

select * from(
  select * from tbl1
  where active=1
  order by dtActivation desc
)
where rownum >=300
and rownum <=320

This, because Oracle put number on rows BEFORE ordering them.

But couldn't you get around that problem pretty easily with something like the following:

Your file that executes the query:

PHP Code:

$query = new AbstractQuery;
$query->select();
$query->from(tbl1);
$query->where(active=1);
$query->orderBy(dtActivation desc);
$query->startLimit(300, 20);
$query->execute(); 


Then your config file would include the the proper database type through a file include:

PHP Code:

//A MySQL database
include(class_mysql.php); 
//Or an Oracle DB
//include(class_oracle.php); 


Then, just using the startLimit method as an example, for class_mysql.php, it would be:

PHP Code:

class AbstractQuery {

     function startLimit($start, $limit) {
          $startLimit = " LIMIT $limit OFFSET $start";
          return $startLimit;
     }

} 


But if the class_oracle.php were included instead, the startLimit method would read from that file as:

PHP Code:

class AbstractQuery {

     function startLimit($start, $limit) {
          $limit = $start + $limit;
          $startLimit =  "where rownum >= $start and rownum <=$limit";
          return $startLimit;
     }

} 


Once all parts of the query are written, the execute() function will string them together and execute them with the proper function like mysql_query (or oci_parse...?).

So, if you built your query with the functions, it would read from the config file which class to use to interpret the functions based on which class was included and each class will construct the query in the proper format for that database.

This is an overly simplified example, but from reading the docs, that's the kind of abstraction I thought something like Zend_Db did. I assumed that they would have already tested out all their functions to work on various databases so that if you have built the function so that it works on one database type, that it would work with all that it claimed to support. Where that would benefit me, I was thinking, was that when a third party develops a module for my application (think Joomla as an example), they could build their queries in the same way, once, and expect that it could work across multiple database types without having to test them all.

Obviously, though, there is something that I'm not quite understanding here and I'm not sure what it is. What I wrote above is similar to what I was thought I was getting (and it would work for me). I'm not an expert on the differences between database types (I'm not even familiar with everything that MySQL can do, much less other databases), but the above doesn't seem like it would be super difficult. Granted, there are some features in some databases that simply don't exist in others and some queries can be complicated, but for my application, I just don't think I'll need anything that complicated.

I'm really not sure what it is that I'm not getting for this concept, but unfortunately, I still feel a bit lost...

Edit: I realize after looking at your example again that my "solution" didn't solve the inner query problem, but couldn't that be solved by checking to see if certain methods were used and adjusting the structure of the query before the execute() method executes?

Originally Posted by VirtuosiMedia

Granted, there are some features in some databases that simply don't exist in others and some queries can be complicated, but for my application, I just don't think I'll need anything that complicated.

I think you'll have a much harder time getting people from 3rd parties to write queries against a potentially unfamiliar data access library than to use stored procedures already provided, or other means.

ADODB doesn't provide anything like this

$query = new AbstractQuery;
$query->select();
$query->from(tbl1);
$query->where(active=1);
$query->orderBy(dtActivation desc);
$query->startLimit(300, 20);
$query->execute();

And if it did, many of us wouldn't use it.

How would this type of API layer handle joins between multiple tables? What if I need an outer join? What if I need a subquery? What if I need to join two tables using three columns? ( FROM x INNER JOIN y ON x.A = y.A And x.B = y.B ) What if I have several predicates that need to be applied in the where clause, with a combination of AND and OR operators?

The 20, 300 limit would be applied in a radically different way depending on what data engine you use. In MySQL there's the limit clause, in Oracle rownum becomes part of the where clause, in SQL Server 2000 and lower you would need to either use a somewhat complicated subquery or a temp table with an identity column to generate a value for rownum to filter by, although in SQL Server 2005 and 2008 you can use a ranking function.

I'm not saying this to be negative. I think there's more than a little danger in going down the road you're describing. Even if you can code your entire application without any of the concepts I mentioned above (and the others I didn't) your 3rd party developers might need one or more of them. Also, I have a hard time believing that any non trivial application can be written without using multi table joins and at least some outer joins. I realize your application server can mimic these things, but that will sand bag your performance, while at the same time bloating and overcomplicating your PHP code.

I also think that by trying to put all of the complexity and intelligence of dealing with multiple database engines and SQL dialects into a PHP module, this greatly increases the chance of bugs. And greatly extends the testing that must be done, compared to other methods of generating and executing SQL queries. I know I'm beginning to sound like a broken record, but code that executes in its native form is so much easier to dev, test, and if necessary debug than code that writes another kind of code. Obviously there's less potential error in SELECT x FROM y WHERE z LIMIT a, b compared to what lives behind each line of code in the example above.

Originally Posted by Learning Newbie

How would this type of API layer handle joins between multiple tables? What if I need an outer join? What if I need a subquery? What if I need to join two tables using three columns? ( FROM x INNER JOIN y ON x.A = y.A And x.B = y.B ) What if I have several predicates that need to be applied in the where clause, with a combination of AND and OR operators?

I think I may have poorly explained myself. My application won't quite be that simple where I wouldn't need to make use of what you wrote, I was referring more to the limits of MySQL in comparison to other database types. I will be using joins, etc.

I don't think at this point I would attempt to write my own class that does what I was describing...there's just too much that I don't know. I was basing that code from the way I thought the Zend_Db class (the newest version of which just came out a few weeks ago) works, which seems to support joins and everything else you mentioned. Here are a few samples of Zend's syntax, copied directly from this page:


Using a join:

PHP Code:

<?php
// Build this query:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p JOIN "line_items" AS l
//     ON p.product_id = l.product_id

$select = $db->select()
    ->from(array('p' => 'products'),
        array('product_id', 'product_name'))
    ->join(array('l' => 'line_items'),
        'p.product_id = l.product_id',
        array() ); // empty list of columns

Using AND and OR:

PHP Code:

<?php
// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (price < 100.00 OR price > 500.00)
//     AND (product_name = 'Apple')

$minimumPrice = 100;
$maximumPrice = 500;
$prod = 'Apple';

$select = $db->select()
    ->from('products',
        array('product_id', 'product_name', 'price'))
    ->where("price < $minimumPrice OR price > $maximumPrice")
    ->where('product_name = ?', $prod);

After looking at ADODB's docs, you're completely right, it doesn't look at all like this. I had assumed it was similar to Zend_Db and maybe that's where some of my confusion was coming in. Reading ADODB's syntax, I see now why you were recommending stored procedures so strongly.

Sorry for so many questions, everyone, but I really am learning a lot. Looking around and listening to your advice has caused me to weigh a lot of different options. It's also made me think about what my application really needs as well. I've gone back and forth a lot and have probably misunderstood quite a few things along the way, but I appreciate your patience. I have just a few more questions.

Tripy, I've taken a look at what you uploaded. Am I right in thinking that your functions are basically shorthand for writing the PDO functions? Do you think that you could post just a simple sample query using your functions?

I think that I'm leaning toward the PDO method, but I'd like to learn just a little more about it first. In order to switch from one database type to another, is the query itself the only thing (other than the config file) that you would have to change? Would the other functions like fetch(), etc. still work the same way across databases provided that the query is correct? If not, which ones are database specific?

Perhaps this is silly, but what I'm considering is this: Using the PDO extension and preparing all of the queries and binding their parameters at the top of each file and then executing them where appropriate. This won't provide the type of database abstraction I was talking about earlier, but it would make it easier to port them later. It would also cut down the overhead and complexity that would come from generating SQL statements. For any modules that are available for my software, they'll simply have to be labeled with the appropriate database type.

What do you think?

Tripy, I've taken a look at what you uploaded. Am I right in thinking that your functions are basically shorthand for writing the PDO functions?

Yes they are now. It was not the case at first, but I migrated them to be.

Do you think that you could post just a simple sample query using your functions?

Directly from my currently working page.
This is a concatenation of 4 different php files, so, it's the reason that some shortcut that could be used are not.
And DOC_ROOT is a constant with $_SERVER['DOCUMENT_ROOT'] value.

PHP Code:

$dbBackend="pgsql";
require_once(DOC_ROOT."/libs/db/$dbBackend.php");
$GLOBALS['objDb']=new clDb();

$q=<<<SQL

SELECT 'profile' as source, p.i_uid as pkey, p.x_username as match
FROM user