Re: [PHP-PEAR] PHPLib and Pear merge

In netuse.lists.php-pear you write:
>i know both of the db abstractions, and i must say, the phplib abstraction 
>has very big holes in points of needed features (e.g. transactions).
>( everybody knows the problems or missed possibilities of phplib, who wrote 
>code which is not only so basic like 'select a from b' )

The DB_Sql class was not written as an abstraction in the first
place, but as a wrapper for the MySQL PHP database connections.
Only later people started porting the API to other databases,
but did not backcheck if that API is applicable.

I started DB_Sql to make my database connection code as short as
possible and properly error checked every time. That is, I
wanted to write everything needed for database connection and
error checking only once per application, in a DB_Sql subclass.
I wanted then code as short as possible and as fast as possible
compared to "naked PHP" for my MySQL stuff. That boiled down to

class DB_myapplication extends DB_Sql {
  var $Host = ...;
  var $User = ...;
  var $Password = ....;
  var $Database = ...;

  function haltmsg($msg) {
    /* some application specific error message */
  }
}

once per project I wrote and

  $db = new DB_Sql("select ...");
  while($db->next_record()) {
    $db2 = new DB_Sql("select something from othertable where ...");
    $v = $db->value("something");
  }

as the query interface. The class itself knows whether it is
already connected, and PHP connection reuse does take care of
the rest. The class itself also knows everything it needs to
know about where to connect on a per-application basis
statically, so the connection target is "compiled in". Also,
each and every query is properly error checked and a sensible
debugging output can be easily produced.

Why I don't consider DB_Sql a database abstraction:
DB_Sql was not intended as a database abstraction at all. I said
that in the past in another discussion on [email protected],
when the topic of database abstraction build into PHP came up:
To properly abstract databases, you cannot work at SQL level.
The various SQL dialects and advantages of the different SQL
databases are to diverse to abstract at SQL level. You need to
generate database specific SQL and have database specific
methods of interacting with the database to be efficient.

Thus, a database abstraction interface can only work properly
above the SQL level, at the ER-level. You'd have a network of
objects modelling your ER model, and you'd query that network of
objects. The ER object network would then generate lower level
SQL accesses, cache query results and handle your result sets.
The former Nextstep/Apple WebObjects EOF (enterprise object
framework) contains an excellent example of such an API, and has
a proven performance and scalability record
(http://developer.apple.com/techpubs/webobjects/System/Documentation/Developer/EnterpriseObjects/DevGuide/WhatsEOF.html
gives you an overview and an idea, and
http://developer.apple.com/techpubs/webobjects/webobjects.html
for the complete documentation).

Unfortunately, the EOF model does not port well to the current
execution model of PHP. It would show performance
characteristics similar to Ulf Wendels form handling classes,
and for similar reasons. The EOF model requires that you have a
large number of classes permanently loaded and compiled, which
makes PHP4 very slow. 

While this can be taken care of by the Zend Cache, the EOF model
also requires that you have a large number of class instances,
objects, readily interconnected to generate your queries
efficiently. This can only be taken care of by NOT throwing away
your object network at the end of a request, but keeping it
around for futher requests.

WebObjects solves this problem and (unlike the Zend cache) gains
webserver independence by taking the WebObjects execution out of
the web server and running as an independent coprocess, with a
data lifetime and process lifetime larger than a single
request/reply lifetime. That's what is generally called an
application server. 

Abstractions such as Ulf's form handling framework or the EOF
database access abstraction framework generally require such a
persistent execution infrastructure in order to minimize the
prohibitively high setup costs for code and data setup (and the
Zend cache only takes care of code setup costs). PHP at the
moment falls short of providing such an execution framework, and
that's what is currently limiting the scalability of PHP the
most. 

It is also why I never came around to code more than a prototype
for such an ER model representation and SQL generator. I tested
my prototype and got setup times of ~2 seconds with PHP on a 500
MHz P3 machine, evenly split into code and data setup times. I
decided that PHP is not made for such a framework at the time
and tried to persuade the php-dev list that an application
server version of PHP is needed (but that somehow failed, too).

At the moment I am looking at different application server
frameworks and how they handle the issue of database abstraction
and "keeping the applications objects alive". That is, I am
looking for alternatives that scale better than PHP, with the
scaling dimension being "code complexity", not "number of
accesses per day". 

>the speed i think is here no point to discuss, thats a problem,
>which you can fix with a faster cpu oder additional tools e.g.
>cache.

You cannot fix speed problems, if they boil down to
architectural problems. That is, PEAR::DB would probably be a
fine database abstraction, if it was running within an
application server framework, as would be Ulf's form handling.
But as things stand at the moment, PEAR::DB does not cut it,
because it is simply to much to compile, and sets up to many
things, before actually doing anything and then at the end of
the page just cannot help it but to throw everything away again,
data and code -- Alex Black has the same criticism for PHPLIB,
and he is right: At the moment demand loading or the Zend cache
is required for PHP applications to gain speed. But of course
demand loading or caching code does not help data setup at all,
not throwing away your precious structures at all is the
solution.

>i you want it faster just write it in c or use the nativ
>api to each database.

Writing it in C, or building a native database abstraction also
is not a proper solution of the inherent problem. Consider for
example an oracle database driven by an Apache webserver with
mod_php and using pconnects(). You have a bunch of apache server
processes, and since they are distinct processes, each of them
needs at least one connection (actually, one connection per open
transaction and/or different set of access permissions) to the
database, eating up 5 MB per Oracle client process and a license
at the Oracle server.
                    
httpd (master)
  httpd (worker) <-----------> oracle
  httpd (worker) <-----------> oracle
  httpd (worker) <-----------> oracle
  .
  . more httpd under
  . increasing load
  .
  httpd (worker) <-----------> oracle

Consider then the application server approach

httpd (master)    appserver (process)
 httpd (worker)     appserver (thread)
 httpd (worker)     appserver (thread) oracle connection
 httpd (worker)     appserver (thread) pool (less than
 .                  appserver (thread) #appserver thds, if desired)
 .
 . more httpd under
 . increasing load
 .
 httpd (worker)

In such a scenario, you'd have many httpd worker processes, and
an appropriate number of appserver worker threads to handle the
number of requests coming in from the httpd. The appserver
worker threads are running in the processes context of the
appserver process, which may be differently privileged than the
httpd. In fact, nothing is stopping you from implementing
different applications in different appserver process instances,
each running with different privilege, and access them from a
single webserver.

Also, the oracle database connection are part of the appserver
process, thus accessible to all appserver threads and
interchangeable between all threads. Thus, you can build a
database connection pool, and any thread can take an idle
connection out of that pool, use it as it sees fit, then put it
back into the pool. This will greatly decrease the number of
concurrent connections to the database, saving on your memory
and license budget.

This not possible in the current mod_php in Apache scenario, it
may or may not be possible in Apache 2.0, if it ever becomes
stable, but the schema above is actually independent of any
webserver and will also deploy on IIS, iPlanet, or any other
webserver you can think of.

To sum it up: This solution solves the

	- code caching problem, because compiled code is
	  kept in the appserver as long as the appserver
	  runs. Code is shared between appserver threads,
	  thus keeping memory usage down massively.

	- data setup problem, because readily setup object
	  structures can be kept between requests and can
	  on demand be shared between worker threads. Thus,
	  execution latency due to structure initialization
	  is down next to nothing.
	
	- connection pooling problem, because readily connected
	  database connections are being kept around and are
	  being shared between ALL appserver worker threads,
	  thus keeping the number of database connections
	  down massively.
	
	- security problem and safe_mode limitation, because
	  the appserver may run in a different security context
	  than the webserver, including a different UID/GID than
	  the webserver, different ressource limits than the
	  webserver, and a different filesystem root than the
	  webserver.

Also, this can be implemented fairly easily in a way that does
not break existing PHP code so that existing PHP can be run in
such an environment, taking advantage of some of the benefits of
an appserver automatically and taking advantage of all of the
benefits with only small changes to the code.

PEAR::DB speed and OOH forms speed are just symptoms of PHP
outgrowing an architecture that does no longer scale. Trying to
downsize PEAR::DB and/or OOH forms is one approach, but the
proper approach is to add to the current PHP execution models
another one that does scale better.

DB_Sql was designed with the current PHP execution model in
mind, and for one purpose: Accessing MySQL databases with a
minimum of application specific lines and a maximum of
automatism and error checking. It has grown beyond that, but it
is not well adapted for some other databases, with the Oracle
interface being the pathological case (variable binding,
statement preparation and the like).