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

On 18 Apr 2000, Manuel Lemos 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.

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.

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

> 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
are using PHP, you are probably not worried about performance that much.

> 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.
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.

> As I explained before, with this Metabase PHP package you only need to
> describe the database schema in a custom XML format that looks like this:
>
> <?xml version="1.0" encoding="ISO-8859-1" ?>
> <database>
>
> <name>test</name>
> <create>1</create>
>
> <table>
> <name>users</name>
> <declaration>
> <field> <name>user_id</name> <type>integer</type> <default>0</default> <notnull>1</notnull> </field>
> <field> <name>user_name</name> <type>text</type> </field>
> <field> <name>password</name> <type>text</type> </field>
> <field> <name>reminder</name> <type>text</type> </field>
> <field> <name>name</name> <type>text</type> </field>
> <field> <name>email</name> <type>text</type> </field>
> <index>
> <name>users_id_index</name>
> <unique>1</unique>
> <field> <name>user_id</name> </field>
> </index>
> </declaration>
> </table>
>
> <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).

> 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).

> 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.

-alex