Page 1 of 1

ADOdb feedback?

Posted: Fri Oct 26, 2007 12:52 pm
by GeXus
Currently, I am just using straight built-in functions for handling data access, I'd like to switch over to using something a little more simplified and with more features....

Has anyone used ADOdb http://adodb.sourceforge.net/ and is happy with it? Or, do you have other suggestions... there are tons of classes out there.. and I just want to pick something that will be scalable for the long term.

Posted: Fri Oct 26, 2007 12:56 pm
by feyd
It's been mentioned a lot here....

You can also look up ADOdbLite by our own (AKA) Panama Jack.

Posted: Fri Oct 26, 2007 1:11 pm
by alex.barylski
Personally I have always found database abstraction layers less portable than they dictate...you still have to know the nuances of the different SQL platforms to use a abstraciton layer effectively, otherwise you will still write MySQL specific code like this in the query functions:

Code: Select all

SELECT * FROM table LIMIT $offset, $length
MSSQL from what I remember doesn't support LIMIT so obviously the above hardcoded SQL statement will choke when you switch over to a MSSQL from a MySQL server. Thus the database abstraction layer does little to save the day.

They all offer little speed up functions and helpers to prevent the above scenario from occuring but IMHO that is a waste of time. Not to mention the waste of clock cycles consumed by most bloated DB abstraction layers...even AdoBD lite is overkill IMHO.

I say your better off studying the data access layer/table data gateway.

Cheers :)

Posted: Fri Oct 26, 2007 2:19 pm
by GeXus
Hockey wrote:Personally I have always found database abstraction layers less portable than they dictate...you still have to know the nuances of the different SQL platforms to use a abstraciton layer effectively, otherwise you will still write MySQL specific code like this in the query functions:

Code: Select all

SELECT * FROM table LIMIT $offset, $length
MSSQL from what I remember doesn't support LIMIT so obviously the above hardcoded SQL statement will choke when you switch over to a MSSQL from a MySQL server. Thus the database abstraction layer does little to save the day.

They all offer little speed up functions and helpers to prevent the above scenario from occuring but IMHO that is a waste of time. Not to mention the waste of clock cycles consumed by most bloated DB abstraction layers...even AdoBD lite is overkill IMHO.

I say your better off studying the data access layer/table data gateway.

Cheers :)
Well I'm only using MySQL... I've always thought of these to be overkill as well... but as I start to add a lot of queries, i'm basically doing tons of

Code: Select all

$query ="select.."
$results = mysql_fetch_array($query);
etc. etc.
It would be nice to simply do $db->Execute("") $db->Fetch("")... then on top of that, it has better error handling, debugging, performance metrics, etc...

Maybe I'll just create a simple layer for the shorter syntax... I dunno....

Posted: Fri Oct 26, 2007 2:52 pm
by onion2k
Hockey wrote:Personally I have always found database abstraction layers less portable than they dictate...you still have to know the nuances of the different SQL platforms to use a abstraciton layer effectively, otherwise you will still write MySQL specific code like this in the query functions:

Code: Select all

SELECT * FROM table LIMIT $offset, $length
MSSQL from what I remember doesn't support LIMIT so obviously the above hardcoded SQL statement will choke when you switch over to a MSSQL from a MySQL server. Thus the database abstraction layer does little to save the day.
If you'd bothered to read the documentation for ADODB and ADODB Lite you'd know that they both support the $db->SelectLimit method to add native select limiting code for MySql, MySqli, MySqlt, SqLite, PostGres7, MsSql, MsSqlpo, and Sybase. But hey, I'm sure it's easier to think you know it all and spread a little FUD here than do a bit of research first.

On topic, I use ADODB Lite and I bloody love it. It's well written, quick, and it makes doing database stuff easier to debug. All good reasons to learn it and use it on production sites in my opinion.

Posted: Fri Oct 26, 2007 3:01 pm
by alex.barylski
onion2k wrote:If you'd bothered to read the documentation for ADODB and ADODB Lite you'd know that they both support the $db->SelectLimit method to add native select limiting code for MySql, MySqli, MySqlt, SqLite, PostGres7, MsSql, MsSqlpo, and Sybase. But hey, I'm sure it's easier to think you know it all and spread a little FUD here than do a bit of research first.
Calm down...

I was aware of SelectLimit() but in my experience most people end up using the generic Execute() where I often see MySQL specific code, not portable...

I thought I insinuated that when I said:
They all offer little speed up functions and helpers to prevent the above scenario from occuring but IMHO that is a waste of time.
I wasn't spreading FUD I was just voicing my professional opinion and personal experience...apparently the truth hurts as you seem to use it and seem to have been irritated by my statements... :P

There is not FUD in that abstraction layers do ad a lot of overhead, especially if he's going to use JUST MySQL...

He could simply implement an object wrapper around the MySQL API to reduce calls, etc...

Posted: Fri Oct 26, 2007 3:12 pm
by RobertGonzalez
ADODBLite for the win.

Posted: Fri Oct 26, 2007 3:14 pm
by pickle
Programmers in the 60's & 70's saved a lot of time & space on their servers by JUST using 2 digits for the year field. When they were programming, they thought "this program will only be used in this century" ;). Point being is that you shouldn't paint yourself into a corner by assuming your situation in the future will be the same as it is now. In short - use a DB abstraction layer (DBAL).

~hockey you're right that a lot of DBALs add overhead & if you were coding while considering nothing but speed, you should definitely hard code everything. However the amount of overhead they add (of course depending on the DBAL you choose) becomes trivial when you are changing database systems. What if right now he uses the mysql... functions but decides to go with the mysqli... functions in the future? I'd rather only have to change one class & the functions it references than have to pour through my entire project looking for mysql_query(), mysql_fetch_row(), etc.

I've never used ADOdb, but I have used ADOdb Lite in the past. Personally I find ADOdb Lite a tiny bit more cumbersome than my own, home grown DBAL, but that could easily be because... I wrote my own.

If you're somewhat new to PHP & databases, I'd recommend using ADOdb Lite - it's probably one of the best out there.

Posted: Fri Oct 26, 2007 3:32 pm
by onion2k
Hockey wrote:I was aware of SelectLimit() but in my experience most people end up using the generic Execute() where I often see MySQL specific code, not portable...

I thought I insinuated that when I said:
They all offer little speed up functions and helpers to prevent the above scenario from occuring but IMHO that is a waste of time.
I wasn't spreading FUD I was just voicing my professional opinion and personal experience...apparently the truth hurts as you seem to use it and seem to have been irritated by my statements... :P
You said, and I quote:
MSSQL from what I remember doesn't support LIMIT so obviously the above hardcoded SQL statement will choke when you switch over to a MSSQL from a MySQL server. Thus the database abstraction layer does little to save the day.
An abstraction layer is exactly the right solution to the LIMIT problem you came up with, yet in your "professional" opinion you're trying to say it isn't the right way to interact with a database and using an abstraction layer is a bad idea. That's FUD. Plain and simple.

Posted: Fri Oct 26, 2007 7:23 pm
by Maugrim_The_Reaper
Adodb Lite - I use it regularly and switch across mysql, mysqli and postgres. mysql native driver is also inbound very soon. Then there's PDO...;). The main feature of ADOdb Lite is speed it's faster and uses less memory than the stock ADOdb, and it's also a *lot* easier to modify when needed since it follows a much cleaner design.

Of course a next possible step up the abstraction ladder is an ORM, ActiveRecord, DataMapper, or other similar scheme for moving towards a more Object oriented abstraction layer. I've built such in the past on top of ADOdb Lite, and PDO. Or you can use the existing solutions like Propel.

Posted: Fri Oct 26, 2007 8:37 pm
by alex.barylski
onion2k wrote:An abstraction layer is exactly the right solution to the LIMIT problem you came up with, yet in your "professional" opinion you're trying to say it isn't the right way to interact with a database and using an abstraction layer is a bad idea. That's FUD. Plain and simple.
I never said it wasn't the right way to interact with a database I just mean it's not the BEST way to deal with databases.

My only point with the statement you quoted. Was that many times people use database abstraction layers and still use hardcoded MySQL statements, in this case porting over to MSSQL is just as much, if not more work than using a low over head table data gateway.

Posted: Fri Oct 26, 2007 9:52 pm
by GeXus
Would ADOdb lite make switching to stored procedures any easier? I could see that as a pretty big benifit.

Also, I could see using both ADOdb and built-in functions for mysql... let's say you have a site that produces a widget and there is one set of code that is used for the widget which receives the bulk of the traffic... use the built in functions for that.. then ADOdb for everything else... (admin)

Posted: Sat Oct 27, 2007 4:32 am
by onion2k
GeXus wrote:Would ADOdb lite make switching to stored procedures any easier? I could see that as a pretty big benifit.
It wouldn't help, but it wouldn't hinder you either. Accessing stored procedures is a change to the SQL you write, not a change to the way you run such SQL statements.
GeXus wrote:Also, I could see using both ADOdb and built-in functions for mysql... let's say you have a site that produces a widget and there is one set of code that is used for the widget which receives the bulk of the traffic... use the built in functions for that.. then ADOdb for everything else... (admin)
That sounds like a very bad idea to me. You'd have to maintain two separate sets of code which are both doing the same thing (connection to the database), and you'd have two different paradigms within the PHP code (interacting with the database with the library and interacting with the database with the native functions). It'd be a lot tidier, easier to maintain, and easier to produce good code if you pick one option. Personally that option would be the use of a library, namely ADODB Lite.

Here's an example of why I use ADODB Lite:

Using ADODB Lite I can loop through it's query list array and produce a list of all the SQL queries in a page with the time each one took and any error it produced and pass it to my error handler object. This produces a nicely formatted <div> in the page that shows me at a glance which SQL statement took a long time.

Code: Select all

    $errorHandler->addDebug("Database queries: ".count($databaseLink->query_list));

    if (ADVANCED_SQL_DEBUG==true) {

        for ($x=0;$x<count($databaseLink->query_list);$x++) {

            if ((ADVANCED_SQL_DEBUG_LONG_QUERY_ONLY==true and $databaseLink->query_list_time[$x]>ADVANCED_SQL_DEBUG_LONG_QUERY_TIME) or ADVANCED_SQL_DEBUG_LONG_QUERY_ONLY!=true) {

                $errorHandler->addDebug("<small><i><span style="color: red;">".$databaseLink->query_list[$x]."</span> (Time:".number_format($databaseLink->query_list_time[$x],4).") ".(!empty($databaseLink->query_list_error[$x])?"<br />(Errors:".$databaseLink->query_list_error[$x].")":"")."</i></small>");

            }

        }

    }

    echo $errorHandler->drawErrors();
What this produces looks like this:
Image
On a dev site it dumps them to the browser, on a production site I can change the config to log slow SQL queries into a database table. For debugging a slow page it's a godsend.

Doing that using only native functions is impossible. You could write your own wrapper to do the same thing ... but why bother when ADODB Lite already does it?

Posted: Sat Oct 27, 2007 10:00 am
by GeXus
onion2k wrote:
GeXus wrote:Would ADOdb lite make switching to stored procedures any easier? I could see that as a pretty big benifit.
It wouldn't help, but it wouldn't hinder you either. Accessing stored procedures is a change to the SQL you write, not a change to the way you run such SQL statements.
GeXus wrote:Also, I could see using both ADOdb and built-in functions for mysql... let's say you have a site that produces a widget and there is one set of code that is used for the widget which receives the bulk of the traffic... use the built in functions for that.. then ADOdb for everything else... (admin)
That sounds like a very bad idea to me. You'd have to maintain two separate sets of code which are both doing the same thing (connection to the database), and you'd have two different paradigms within the PHP code (interacting with the database with the library and interacting with the database with the native functions). It'd be a lot tidier, easier to maintain, and easier to produce good code if you pick one option. Personally that option would be the use of a library, namely ADODB Lite.

Here's an example of why I use ADODB Lite:

Using ADODB Lite I can loop through it's query list array and produce a list of all the SQL queries in a page with the time each one took and any error it produced and pass it to my error handler object. This produces a nicely formatted <div> in the page that shows me at a glance which SQL statement took a long time.

Code: Select all

    $errorHandler->addDebug("Database queries: ".count($databaseLink->query_list));

    if (ADVANCED_SQL_DEBUG==true) {

        for ($x=0;$x<count($databaseLink->query_list);$x++) {

            if ((ADVANCED_SQL_DEBUG_LONG_QUERY_ONLY==true and $databaseLink->query_list_time[$x]>ADVANCED_SQL_DEBUG_LONG_QUERY_TIME) or ADVANCED_SQL_DEBUG_LONG_QUERY_ONLY!=true) {

                $errorHandler->addDebug("<small><i><span style="color: red;">".$databaseLink->query_list[$x]."</span> (Time:".number_format($databaseLink->query_list_time[$x],4).") ".(!empty($databaseLink->query_list_error[$x])?"<br />(Errors:".$databaseLink->query_list_error[$x].")":"")."</i></small>");

            }

        }

    }

    echo $errorHandler->drawErrors();
What this produces looks like this:
Image
On a dev site it dumps them to the browser, on a production site I can change the config to log slow SQL queries into a database table. For debugging a slow page it's a godsend.

Doing that using only native functions is impossible. You could write your own wrapper to do the same thing ... but why bother when ADODB Lite already does it?

Ok I'm sold :) Thanks for that response, that is actually real sweet.