From: Alex Pilosov <alex@pilosoft.com>
To: Manuel Lemos <mlemos@acm.org>
Cc: Thomas Good <tomg@admin.nrnet.org>
Cc: pgsql-general@postgresql.org
Subject: Re: Connecting website with SQL-database.....
Date: Wed, 19 Apr 2000 00:08:04 -0400 (EDT)
Message-ID: <Pine.BSO.4.10.10004182103170.9685-100000@spider.pilosoft.com> (raw)
In-Reply-To: <3758.143T2716T9396025mlemos@acm.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: alex@pilosoft.com, mlemos@acm.org, tomg@admin.nrnet.org, pgsql-general@postgresql.org
Subject: Re: Connecting website with SQL-database.....
In-Reply-To: <Pine.BSO.4.10.10004182103170.9685-100000@spider.pilosoft.com>
References: <3758.143T2716T9396025mlemos@acm.org> <Pine.BSO.4.10.10004182103170.9685-100000@spider.pilosoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox