Re: Connecting website with SQL-database.....

Hello Alex,

On 19-Apr-00 02:08:04, you wrote:

>> I may be mistaken, but the last time that I looked at Perl DBI, it didn't
>> seem to a complete database abstraction layer than it is needed. For
>> instance, you want retrieve data from date fields the results come
>> formatted in a database dependent way. This means that your DBI
>> applications can't really be that much database independent as you still
>> have to handle datatype differences in the application code.

>I have used another database abstraction layer, that wants to be
>all-singing, all-dancing. It is called ODBC. It sucked.

Because there is to much overhead in datatype conversion among other
things. Not all database abstraction layers do that because often it
is not needed.

>There are add-ons to DBI which allow you to further abstract from your
>database, if you choose so. For most of them, you need to still write
>database-specific code yourself, it just gives you a cleaner interface on
>how to do it. I believe that in general, this is the superior approach
>instead of trying to abstract it all in the system/driver code.

What you are saying is that DBI "could" be more confortable for the
programmer but it currently it isn't. I don't see how that makes DBI
superior. It's like saying that bicycles are superior to cars because they
have less wheels!?!

>The developer always knows what database-dependent features he is using,
>and should appropriately abstract them into different file).

Web developers are not DBA. Most of them where forced into being database
developing just because they needed some persistent storage that can be
searched efficiently from their Web sites. Most Web developers are not that
experienced so they are not expected to know how all database dependent
features work in each DBMS.

Database abstraction layers are good because they save Web developers from
the pain of learning more than they need to know about each DBMS.

>> With this Metabase package in PHP date fields are always returned formatted
>> in the industry standard ISO 3166 (YYYY-MM-DD HH:MI:SS). Then you do
>> whatever processing you want with dates formatted this way, but it's always
>> DBMS independent.
>Reformatting things every time kills performance. Then again, since you

I don't thing you thought througly about what you said. Look at the format
again. Fields have fixed sizes and so they are always in the same
position. If you want to extract sub fields from it, you just need to
fetch a substring of the same size from the same position furthermore.

Other than that, if you just want to compare dates you just need to do
string comparision as the fields appear in descreasing order of relevance
for time computations. This means among other things that for databases that
don't support native date/time fields, you may compare and sort as fixed
size text fields.

>are using PHP, you are probably not worried about performance that much.

I am concerned about performance but I am not obcessed. If I were obcessed
I would not be using nor PHP nor Perl. I would be developing Web
applications as server modules in C.

Anyway, I suppose you don't know PHP that well these days. I don't need to
be obcessed by performance when using PHP because I only need to use single
native commands to process dates.

Besides, if I was that much worried today you have PHP/Zend
optimizer/compiler engine that boosts the performance of CPU intensive PHP
scripts so high, that in Web based database applications you would more
worried with database server and network I/O performance.

>> Another thing that seems to be lacking in DBI and other database
>> abstraction layers is support for DBMS independent schema installation. I
>> mean if you want to install a given database schema (tables, fields,
>> indexes, sequences) you still have to hand code database dependent SQL
>> commands to create them.
>Because of the great variety in types, refint restrictions and other
>restrictions supported by databases (and don't get me started on SQL
>standards), its hard for _driver_ to know what exactly you want to create.

Only if your database applications need to use any other types than else
than: text, integer, boolean, float, decimal, date, time, timestamp . Maybe
I am forgetting something.

>DBI drivers now provide information on types the database supports and
>more-or-less standardized 'description' of them, but its up to you to make
>a use of it.

That's the pain of job. If your application have to deal with data type
differences at that level, your application will still be too much database
dependent.

>> <sequence>
>> <name>user_id</name>
>> <start>1</start>
>> <on> <table>users</table> <field>user_id</field> </on>
>> </sequence>
>> </database>
>What if database doesn't support named sequences? (i.e. it only has
>'sequence' as column type, but you can't create a sequence with a name).

Use a single table to emulate the sequence. That's how the MySQL and MS
SQL Metabase drivers implement sequences: using separate tables with auto
incremented fields. If a database does not have those two, sequence calls
fail returning an error.

>> Metabase will install this schema description on any SQL based database.
>> Furthermore, if you change the schema later you may tell Metabase to apply
>> the changes without affected any data that was added to the database
>> afterwards.
>Sounds like a pipedream. (Or like ER/win tool, which is probably what you
>_really_ want to use if you have tens of tables which periodically need
>revision).

Metabase does this now and well. It's not a pipedream.

Like you said, Perl/DBI could implement but it doesn't. Anyway, if there
is interest by the Perl/DBI or other communities that use other languages I
am willing to cooperate so the knowlegde built-in Metabase could be reused
and people could do other things like expanding applications in different
languages but using the database schemas.

Other interesting things could be done like reverse-engineering the schemas
of installed databases so they can be easily ported to other DBMS.

These are just ideas that could be constructively implemented if people is
willing to cooperate instead of wasting energies arguing which is the best
language or database abstraction layer.

Metabase is available in PHP code for free here:

http://phpclasses.UpperDesign.com/browse.html/package/20

You just need to mail me if you are willing to cooperate.

>> There are other neat features like support for requesting just a range of
>> rows of a SELECT query. In some DBMS it would be as simple as specifying
>> the LIMIT clause, but it is not that simple in many others. Metabase
>> abstracts all that for you because those are desirable features that all
>> database abstraction layers should provide.
>If database doesn't support something, it is not necessarily a feature to
>transparently provide emulation for it. Sometimes failing with an error
>and forcing programmer to provide emulation code or forcing programmer to
>ASK for emulation is the right thing.

Metabase does not abstract everything in all supported databases. If
some feature like sequences as I mentioned above, or for instance
transactions are not supported at all, they are not emulated. For
instance, if you need transactions, there is no magic that will emulate
that for instance in MySQL. So, use another database.

You can query any Metabase driver if a particular feature is supported or
not. If you still try to call functions that depend on unsupported feature
they will fail returning an error.

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos(at)acm(dot)org
--
E-mail: mlemos(at)acm(dot)org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--