List for discussions between the maintainers of database abstraction layers written in PHP ()

[phpdba] DBAL definition

Hi,

I have some questions regarding the terms from the DBAL world, respective their roles, meanings and essentials in PHP. What I want to know is what's essential for a DBAL and what's useless and should be implemented elsewhere, because it has nothing to do with an abstraction layer or it is better abrogated elsewhere.

DBAL - Database Abstraction Layer:

Description:

A database abstraction layer provides a common API to access data located in a RDBMS.

Meaning:

A common API for connecting and disconnecting to a RDBMS.
We don't have to remember how mysqli can connect to a server, neither how PDO connections are established.
A common API for sending queries to the database.
This may include implementations for the different kinds of queries, e.g. SELECT, INSERT, UPDATE, DELETE to provide later usage of the results (e.g. return the last inserted id, the number of affected rows or the result set).
A common API for transaction handling.
Methods to begin, commit and rollback a transaction, including nested transactions.
Features/Quoting/Wildcards/etc
A list of features the connected RDBMS supports, e.g. if auto_increment or sequences are supported, how table/field names and their values have to be quoted/escaped, which wildcards can be used for several query operations (e.g. MySQL's LIKE) and so on. They could be used by other components to build queries.
Primary Key handling (auto_increment/sequences)
Some kind of an ID generator like Creole has, so we can easily handle IDs without knowing which mechanism is used by the RDBMS.
Charset conversation.
Ensures that the charset used is always correct and no wrong encoded data will find its way into the database.

Don'ts:

Field type conversation. Any piece of data is handled as it is delivired by the RDBMS.
Query builder. A DBAL only accepts plain text (strings) which will be send to the RDBMS directly.
RDBMS specific query syntax checking. The queries will be executed as provided.

I'd appreciate if you would correct me if I understand anything wrong, if you have suggestions or if something is missing in that definition.

Regards,
Pierre

headers

Lukas Kahwe Smith | 21 May 20:52

Re: [phpdba] DBAL definition

Pierre Minnieur wrote:

>     * Field type conversation. Any piece of data is handled as it is
>       delivired by the RDBMS.

Here I disagree. To me the key point of a DBAL compared to just a DBL 
(database layer) is that it provides almost everything necessary to 
manually build portable SQL. This means that instead of passing of an 
array that magically builds my SQL, the user remains in the driver seat 
and is provided with a rich enough API to build almost everything in the 
SQL spec (and a bit beyond that) in a portable manner.

As such I feel that data type abstraction is very much part of the 
scope. It gets a bit more fuzzy with schema reading/writing, as this is 
very hard to provide in a way to enable users to build the queries 
themselves.

regards,
Lukas

headers

Pierre Minnieur | 22 May 12:30

AW: [phpdba] DBAL definition

Lukas Kahwe Smith wrote:

Here I disagree. To me the key point of a DBAL compared to just a DBL
(database layer) is that it provides almost everything necessary to
manually build portable SQL. This means that instead of passing of an
array that magically builds my SQL, the user remains in the driver seat
and is provided with a rich enough API to build almost everything in the
SQL spec (and a bit beyond that) in a portable manner.

What you want to say is that a DBAL must additionally provide:

a Query Builder, maybe extendable by the driver to provide RDBMS specific options/methods
Field/Data Type Abstraction (e.g. "String" in PHP means "Text, Varchar or Char" in MySQL, to say it lazy)

Must a DBAL then provide OO access to executed statements, prepared statements and results sets, too (like PDO does)?

Cheers,

Pierre

headers

Lukas Kahwe Smith | 22 May 13:46

Re: AW: [phpdba] DBAL definition

Pierre Minnieur wrote:
> Lukas Kahwe Smith wrote:
> 
>     Here I disagree. To me the key point of a DBAL compared to just a DBL
>     (database layer) is that it provides almost everything necessary to
>     manually build portable SQL. This means that instead of passing of an
>     array that magically builds my SQL, the user remains in the driver seat
>     and is provided with a rich enough API to build almost everything in
>     the
>     SQL spec (and a bit beyond that) in a portable manner.
> 
>  
> What you want to say is that a DBAL must additionally provide:
> 
>     *
>       a Query Builder, maybe extendable by the driver to provide RDBMS
>       specific options/methods

no .. not a query builder .. that would imply that you just pass of some 
data and it magically constructs the entire SQL for you .. that is the 
job for some OO<->SQL tool. what I think a DBAL should provide is 
methods to generate pieces of SQL that are then assembled by the user.

so for example setLimit() would either return or automatically append to 
an supplied SQL string. same applies to field abstraction, you still 
call quote() to get the necessary SQL fragment to embedd in your SQL 
statement.

regards,
Lukas

headers

Pierre Minnieur | 22 May 14:33

AW: AW: [phpdba] DBAL definition

Lukas Kahwe Smith wrote:

no .. not a query builder .. that would imply that you just pass of some
data and it magically constructs the entire SQL for you .. that is the
job for some OO<->SQL tool. what I think a DBAL should provide is
methods to generate pieces of SQL that are then assembled by the user.

so for example setLimit() would either return or automatically append to
an supplied SQL string. same applies to field abstraction, you still
call quote() to get the necessary SQL fragment to embedd in your SQL

Okay, just a missunderstanding. I tried to package that already under the term "Features/Quoting/Wildcards/etc" in my first email ;-)

Cheers,

Pierre

Von: Lukas Kahwe Smith
Gesendet: Di 22.05.2007 13:46
An: phpdbabstraction <at> lists.pengus.net
Betreff: Re: AW: [phpdba] DBAL definition

Pierre Minnieur wrote: > Lukas Kahwe Smith wrote: > > Here I disagree. To me the key point of a DBAL compared to just a DBL > (database layer) is that it provides almost everything necessary to > manually build portable SQL. This means that instead of passing of an > array that magically builds my SQL, the user remains in the driver seat > and is provided with a rich enough API to build almost everything in > the > SQL spec (and a bit beyond that) in a portable manner. > > > What you want to say is that a DBAL must additionally provide: > > * > a Query Builder, maybe extendable by the driver to provide RDBMS > specific options/methods no .. not a query builder .. that would imply that you just pass of some data and it magically constructs the entire SQL for you .. that is the job for some OO<->SQL tool. what I think a DBAL should provide is methods to generate pieces of SQL that are then assembled by the user. so for example setLimit() would either return or automatically append to an supplied SQL string. same applies to field abstraction, you still call quote() to get the necessary SQL fragment to embedd in your SQL statement. regards, Lukas

headers

Lukas Kahwe Smith | 22 May 13:48

Re: AW: [phpdba] DBAL definition

Pierre Minnieur wrote:

> Must a DBAL then provide OO access to executed statements, prepared 
> statements and results sets, too (like PDO does)?

absolutely .. a DBAL also needs to cover the reading part. so you need 
to have a portable way to read data from the database (including data 
type abstraction, handling of trimming off empty spaces etc).

regards,
Lukas

headers

Pierre Minnieur | 22 May 14:36

AW: AW: [phpdba] DBAL definition

Lukas Kahwe Smith wrote:

> Must a DBAL then provide OO access to executed statements, prepared
> statements and results sets, too (like PDO does)?

absolutely .. a DBAL also needs to cover the reading part. so you need
to have a portable way to read data from the database (including data
type abstraction, handling of trimming off empty spaces etc).

Okay, thank you for that information, that was something I've missed in previous docs about DBAL.

Cheers,

Pierre