postgresql_and_access_casesense

This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.



Subject: Re: [HACKERS] Why Not MySQL?
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
Date: Wed, 03 May 2000 04:12:37 +0000


> > If I can't get my questions answered about case sensitivity issues here
> > (no help so far) I will most likely to use it myself.

My recollection is that it involved needing non-standard
case-insensitive LIKE comparisons to get transparent behavior with an
existing M$ Access app. So far, we were too polite to ask why one is
working so hard to maintain compatibility with a non-standard
interface, rather than writing the app to be portable. But I'll ask
now. Tim?

                     - Thomas

btw, it seems to be the case that problems such as these, which might
be interesting  during slow times (from a theoretical standpoint at
least), are decidely less so during the final stages of a release
cycle.

===

Subject: Re: [HACKERS] Why Not MySQL?
From: Malcontent null <malcontent@msgto.com>
Date: Wed May 03 01:04:53 EDT 2000


Thomas Lockhart <lockhart@alumni.caltech.edu> wrote:

>existing M$ Access app. So far, we were too polite to ask why one is
>working so hard to maintain compatibility with a non-standard
>interface, rather than writing the app to be portable. But I'll ask
>now. Tim?

Fair enough question. I agree with you that this is non
standard typical MS lock in crap. But I have an application
that is written in access and has outgrown the data engine
in access (which is pretty pathetic). Unfortunately this
application is very large with over 300 tables and over 1400
saved queries (views). The MS solution to this problem is to
upgrade to MS-SQL server (vendor lock in) which processes
the queries in the exact same case insensitive manner. SQL
server does not break my application. I on the other hand
want to avoid upsizing to SQL server. I could use sybase
which also allows for case insensitive collation (no
surprise there) but I really-really want to use an open
source database server.

So. So right now I have a few choices.
1) Buckle into the vendor lock and be stuck with NT and SQL server
2) Buy sybase and spend way more then I want to.
3) Completely rewrite all 1400 queries and write all kinds of new code make sure the SQL emitted by access gets intercepted and translated properly.
4) Make Postgres process queries case insensitively.

Well the third one is out of the question really I don't
have that kind of time or money. It would take me a the rest
of the year to accomplish that goal and the database would
have to be taken out of commision in the meantime.


>btw, it seems to be the case that problems such as these, which might
>be interesting  during slow times (from a theoretical standpoint at
>least), are decidely less so during the final stages of a release
>cycle.

I fully understand that you guys have your own set of
priorities. I also appreciate the work you guys have put
into making postgres into a database I want to use. Having
said all that I did wait 4 to 5 days without a reply of any
sort. It would have been perfectly fine for somebody to say
"It's not possible don't waste your time", "Don't ask this
question here", "we are really entirely too busy to deal
with this" or even "go away and don't ever bother us ever
again".

===

Subject: Re: [HACKERS] Why Not MySQL?
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
Date: Wed, 03 May 2000 05:07:56 +0000

Anyway, so the larger class of problem is for the Sybase/M$
user who relies on case insensitive queries (which *are*
available in Postgres) which are indistinguishable from the
SQL92-mandated case-sensitive ones. So we might explore the
possibilities for a contrib/ module which does this, though
because it touches on replacing existing backend code it may
not quite fly since there are some function lookup
optimizations which may keep you from overwriting the
existing routines. But it would be a neat capability to
have; I wonder if it would work right away or if we could
tweak the backend to allow this in the future??

Of course the alternative is to just dive in and hack and
slash at the backend code. Look in parser/gram.y and
utils/adt/like.c for starters...

===

Subject: Re: [HACKERS] Why Not MySQL?
From: Malcontent null <malcontent@msgto.com>
Date: Wed May 03 02:37:10 EDT 2000


Thomas Lockhart <lockhart@alumni.caltech.edu> wrote:
>
>clear that it may solve problems for a larger class of user than the
>one who managed to grow a M$ Access app to 300 tables and 1400 queries
>before deciding that Access might be a little light in performance to
>be suitable. But that's water under the bridge, eh?

Actually I did post twice I had hoped that I was being more
clear the second time. As for growing the access database
well sometimes apps take a life of their own. Database apps
in general tend to be too critical to business to just scrap
and rewrite so they just keep growing.

>Anyway, so the larger class of problem is for the Sybase/M$ user who
>relies on case insensitive queries (which *are* available in Postgres)

If I may.

MS Access for all of it's damnable faults is the single most
popular database in the world. There are a whole slew of
people who do nothing except access programming and make
very good money at it. Postgres is a great candidate as a
possible back end database engine for access.  This is a big
possible application for postgres. To be usable for this
purpose however it needs a few things.

1) Longer object names (I guess this is possible via a DEFINE)
2) Case insensitive queries.
3) Outer joins (coming soon!).
4) Maybe ADO drivers for the VB users of the world.
 
I don't know how important access integration is to the
postgres community as a whole though.

>Of course the alternative is to just dive in and hack and slash at the
>backend code. Look in parser/gram.y and utils/adt/like.c for
>starters...

Thanks for the tip I'll start looking at this right away.

===

Subject: RE: [HACKERS] Why Not MySQL?
From: Magnus Hagander <mha@sollentuna.net>
Date: Wed, 3 May 2000 13:43:11 +0200 


> >existing M$ Access app. So far, we were too polite to ask why one is
> >working so hard to maintain compatibility with a non-standard
> >interface, rather than writing the app to be portable. But I'll ask
> >now. Tim?
> 
> Fair enough question. I agree with you that this is non 
> standard typical MS lock in crap. But I have an application 
> that is written in access and has outgrown the data engine in 
> access (which is pretty pathetic). Unfortunately this 
> application is very large with over 300 tables and over 1400 
> saved queries (views). The MS solution to this problem is to 
> upgrade to MS-SQL server (vendor lock in) which processes the 
> queries in the exact same case insensitive manner. SQL server 
> does not break my application. I on the other hand want to 
> avoid upsizing to SQL server. 

Not to turn you away from PostgreSQL, but you might want to look at MSDE
(Microsoft Data Engine) as an easier step. It has the same query processor
as SQL Server, but scales much better (and includes transaction logs etc).
See for example
http://msdn.microsoft.com/library/backgrnd/html/msdeforvs.htm.
The license for MSDE is also included in Office 2000 Pro/Premium, so chances
are you may already have the required licenses.
Still leaves you in the Microsoft box, though.

===

Subject: Re: [HACKERS] Why Not MySQL?
From: Hannu Krosing <hannu@tm.ee>
Date: Wed, 03 May 2000 13:58:40 +0300


Malcontent null wrote:
> 
> >Anyway, so the larger class of problem is for the Sybase/M$ user who
> >relies on case insensitive queries (which *are* available in Postgres)

Maybe the right place to introduce case-insensitiveness would be in ODBC 
driver then ?

> If I may.
> MS Access for all of it's damnable faults is the single most popular
> database in the world. There are a whole slew of people who do nothing
> except access programming and make very good money at it. Postgres is
> a great candidate as a possible  back end database engine for access.
> This is a big possible application for postgres. To be usable for this
> purpose however it needs a few things.
> 1) Longer object names (I guess this is possible via a DEFINE)

How long should they be ?

> 2) Case insensitive queries.

Probably only the Access subset ("like", "order by", maybe even "=" ?)

> 3) Outer joins (coming soon!).
> 4) Maybe ADO drivers for the VB users of the world.

AFAIK MS moves fast and ADO will be soon (or is already) officially obsolete.

The technology du jour is XML.

> I don't know how important access integration is to the postgres
> community as a whole though.

Probably not a top priority. Oracle is much more often seen as the target.

===

Subject: Re: [HACKERS] Why Not MySQL?
From: The Hermit Hacker <scrappy@hub.org>
Date: Wed, 3 May 2000 09:27:16 -0300 (ADT)


On Wed, 3 May 2000, Hannu Krosing wrote:

> Malcontent null wrote:
> > 
> > >Anyway, so the larger class of problem is for the Sybase/M$ user who
> > >relies on case insensitive queries (which *are* available in Postgres)
> 
> Maybe the right place to introduce case-insensitiveness would be in ODBC 
> driver then ?
> 
> > If I may.
> > MS Access for all of it's damnable faults is the single most popular
> > database in the world. There are a whole slew of people who do nothing
> > except access programming and make very good money at it. Postgres is
> > a great candidate as a possible  back end database engine for access.
> > This is a big possible application for postgres. To be usable for this
> > purpose however it needs a few things.
> > 1) Longer object names (I guess this is possible via a DEFINE)
> 
> How long should they be ?
> 
> > 2) Case insensitive queries.
> 
> Probably only the Access subset ("like", "order by", maybe even "=" ?)

don't we have a 'lower()' function?

SELECT * FROM <table> WHERE field ~* 'this string' ORDER BY lower(field);?

or

SELECT * FROM <table> WHERE lower(field) = lower('StriNg');


===

Subject: Re: [HACKERS] Why Not MySQL?
From: Hannu Krosing <hannu@tm.ee>
Date: Wed, 03 May 2000 14:51:44 +0300


The Hermit Hacker wrote:
> 
> On Wed, 3 May 2000, Hannu Krosing wrote:
> 
> > Malcontent null wrote:
> > >
> > > >Anyway, so the larger class of problem is for the Sybase/M$ user who
> > > >relies on case insensitive queries (which *are* available in Postgres)
> >
> > Maybe the right place to introduce case-insensitiveness would be in ODBC
> > driver then ?
> >

...

> > > 2) Case insensitive queries.
> >
> > Probably only the Access subset ("like", "order by", maybe even "=" ?)
> 
> don't we have a 'lower()' function?
> 
> SELECT * FROM <table> WHERE field ~* 'this string' ORDER BY lower(field);?
> 
> or
> 
> SELECT * FROM <table> WHERE lower(field) = lower('StriNg');

That's what I meant by introducing pushing the case-insensitiveness into ODBC, 
so that the MS Access program can be made case-insensitive automatically by

A) rewriting the queries to use lower()

or

B) by using case-insensitive operators where possible.


===

Subject: Re: [HACKERS] Why Not MySQL?
From: Bruce Momjian <pgman@candle.pha.pa.us>
Date: Wed, 3 May 2000 09:06:57 -0400 (EDT)


> Of course the alternative is to just dive in and hack and slash at the
> backend code. Look in parser/gram.y and utils/adt/like.c for
> starters...

That would be my recommendation.  It is open source, so you can modify
it however you like.

===

Subject: Re: [HACKERS] Why Not MySQL?
From: "Mitch Vincent" <mitch@huntsvilleal.com>
Date: Wed, 3 May 2000 10:18:36 -0400


Hi Don, thanks for your reply...

> First, have you been having the same problems with PG 7.0?  I recall that
> you had it up on a test system but nothing more.

No, I was afraid to run 7.0 beta on the production server. I do have it on
my development server however and haven't had any problems with it (of
course the devel server is only used by me, the production server is used by
about 600 people)...

 > It's a pity that you've reached this point, because PG is so much better
> than it was 18 months ago (and before, of course, I mention that timeframe
> because that's roughly when I first investigated its suitability for
> the web toolkit project) and the trajectory is definitely in the right
> direction.

Well, we've started to look into it. My boss is worried about the stability
and speed of Postgres in the long run, personally I love PG, I would like to
stay with it until at least 7.1, if the problems still continue then maybe
look elsewhere.

> It's also a loss to the development effort, as people with bugs in many
> ways are more useful than people who have no problems (though of course
> having no bugs for users to stumble across is the best situation!)
> Still, I understand the need to solve your problems today, not tomorrow.


> Interbase is a possible solution.   They have a pretty good reputation,
> and their "super server" (threaded with connections sharing a buffer
> cache) should scale well.  My rough estimate is that they're at about
> the place PG will be when 7.1 comes out.  I don't know if they support
> FreeBSD, though.  Any reason you can't just put up a box with Linux?

Ooooo. Mitch hates Linux. It's a long, boring story but lets just say that

===

Subject: Re: [HACKERS] Why Not MySQL? 
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed, 03 May 2000 11:51:27 -0400


Hannu Krosing <hannu@tm.ee> writes:
> That's what I meant by introducing pushing the case-insensitiveness
> into ODBC,

I don't believe ODBC parses the query carefully enough to recognize
operators that would need to be altered to become case-insensitive.
I'm not even sure that it could do that --- does "WHERE f1 = f2"
need to be changed to "WHERE lower(f1) = lower(f2)"?  No way to know
that unless you know the datatypes of f1 and f2, which would mean
(a) a moderately complete SQL parser/analyzer and (b) a copy of the
system catalogs inside ODBC.  Doesn't sound like a promising avenue
of attack...


===


the rest of The Pile (a partial mailing list archive)

doom@kzsu.stanford.edu