From: | Alex Pilosov <alex(at)pilosoft(dot)com> |
---|---|
To: | Manuel Lemos <mlemos(at)acm(dot)org> |
Cc: | Thomas Good <tomg(at)admin(dot)nrnet(dot)org>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Connecting website with SQL-database..... |
Date: | 2000-04-19 04:08:04 |
Message-ID: | Pine.BSO.4.10.10004182103170.9685-100000@spider.pilosoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-interfaces pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Graeme Merrall | 2000-04-19 04:38:09 | On functions and stored procs |
Previous Message | Brett W. McCoy | 2000-04-19 03:45:12 | Re: full-text indexing |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-04-19 04:21:05 | Re: lo_import problems |
Previous Message | Joe Shevland | 2000-04-19 01:11:18 | Re: psql & java |
From | Date | Subject | |
---|---|---|---|
Next Message | Shek Ying Kit | 2000-04-19 04:25:19 | How could I use trigger to call a Java function |
Previous Message | Joe Shevland | 2000-04-19 01:11:18 | Re: psql & java |