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