January 7th, 2003, 04:02 PM
Case-insensitive indexes should be an elementary feature. I would be very surprised if Firebird really works this way. But, then again... let's look for some further information. I couldn't find any reference to this on the www.ibphoenix.com website. Anyone have any links or developer mail messages on this one?
Also, is any of this any help to you, Ctb, or have we gone way off-topic?
January 7th, 2003, 04:35 PM
I was thinking that we (I?) had hijacked this thread. But once you get rolling its hard to stop.
Like I said in an earlier post, I hope I'm misunderstanding what I've read. For Firebird in the Requested Features section is a request for Case Insensitive Indexes. There is also a CONTAINING function to do case-insensitive searching (it doesn't use an index):
Also posts like this:
Another thing that may have confused me was Functional Indexes in PostgreSQL. I had read about them a while back and maybe not understanding their exact purpose, tried to apply the same technique improperly to Firebird:
An alternate type of index is one called a functional index. A functional index is an index based on the returned value of a function applied to one or more columns. Such an index is useful for queries that use the same function in a WHERE clause frequently.
For example, if you have a query that always references upper(last_name) in its WHERE clause, you could optimized that query by creating
Last edited by dcaillouet; January 7th, 2003 at 04:40 PM.
January 7th, 2003, 05:04 PM
So you're back to validating the data on input.
Will code for food.
January 7th, 2003, 05:30 PM
I don't understand how this would solve my problem. If I force the data to upper case when inserting, I lose the proper case data I desire. If I force the data to upper case when searching, I lose the indexing I need.
I must be missing something. How do you force the data to all upper or lower case, retain proper case data and keep your indexing in a database that doesn't allow case-insensitive collation?
January 7th, 2003, 07:18 PM
First, I don't know what data is being stored, but if the input is validated for case and stored only as lower case you'd elimiate the problem of what case to search for. Once you have retrieved the desired data it can be reformatted for display purposes if desired.
Will code for food.
January 7th, 2003, 09:15 PM
Wuhuh? I'm sorry... was I supposed to be paying attention to any of this?
Did you download the Super Server or the classic version? Does anyone know if that makes a difference?
I'd try all of this out, but the ibphoenix site is out of commission at the moment, as is the old Interbase ftp server, so I can't seem to get any documentation on this blasted thing (though I managed to get it installed and running... I think -- plbbt... no wait, just found it).
/* seriously... why the hell did this portion of dcaillouets post:
"Correct. But in the absence of a practical solution, I would have to choose the least offensive workable solution. My question was, "Is there a practical solution?"."
Just wind up on my console???
It doesn't appear to have even installed any docs when I ran the rpm.... bummer.
But actually, yes, all of this is (to varying extents) good input. If Interbase/Firebird is too tough to use, however, I'll fear .... no... wait... I just figured it all out, now I just need to get the reference docs
Alright never mind my gibbering. I just figured it out so I'm gonna go play with it!
January 8th, 2003, 12:15 AM
I don't know what data is being stored
Theoretically, any data. I'm just bouncing ideas around looking for feedback. I want to like Firebird. I use Borland tools and Interbase (the source of Firebird) is a Borland product. Their development tools and the database work well together. Plus Firebird has some impressive features with transactions, trigger, stored procedures, UDF's, referential integrity, two-phase commit, etc. But having worked with all upper case mainframe data for years, I can tell you that storing the data in upper / lower case and then trying to cast it to proper case using logic and intuition is an ugly hack at best. You always have some wrong cases that makes your users look illiterate or your software look buggy. The solution of course is to get case-insensitve collation working on the *nix platform.
...Super Server or the classic version...
Classic behaves like Access. Super behaves like SQL Server. (sorry about the M$ comparison)
The classic architecture allows for programs to directly open the database file, It is architected to allow the same database to be opened by several programs at once. The classic engine also allows remote connections to local databases by providing an inetd or xinetd service (This spawns a seperate task per user connection).
The super server architecture provides a server process, and client process cannot directly open the database file and all SQL requests are done via the server using a socket. The super server makes use of lightweight theads to process the requests.
In June, Brookstone released a case-insensitive collation for Windows. They say that Linux / Solaris is a "future project". Hopefully it will appear soon.
January 8th, 2003, 01:01 AM
Wow. So basically, Firebird allows for searching in a case-insensitive manner, but if you want the performance of an index, you will have to choose a case . That would definitely prevent me from migrating to it at the moment.
Functional indexes are just another example of the elegance and overall "togetherness" of how PostgreSQL is built. What it means is that not only can you index columns and even groups of columns, but you can index the results of any user-defined function (I.E. stored procedure) that is used in the [WHERE|HAVING|etc...] clause during a query.
Thus, if you have a stored procedure that is an important part of a query, there is no reason to lose out on the value of an index. This is not a common feature in DBMS systems.
This is just one reason I am so passionate about PostgreSQL . I have spent my time messing around with other DBMS systems. So many of them are either deficient in some critical area (MySQL, Firebird), or simply too complex and bloated for my taste (Oracle, DB2). (And, they don't support native FreeBSD operation). PostgreSQL hits the sweet spot for me, because it gives so many flexible options for controlling the data, but other than that, it stays out of the way!!! You can install that thing in 15 minutes and be creating databases. The only other system that installs and sets up that easily is MySQL, but we've already beat that horse.
Anyway, If I were Ctb, rather than try to run the database on a Windows server, I would try to convince the powers that be to run in true enterprise style: separate the database from the application server. Put the database on an inexpensive Unix box (at least to start with), and you can connect to it from any Windows, Unix, or Mac clients and application servers. The PostgreSQL server may not run great on Windows, but the Windows client access libraries (C/C++, ODBC,JDBC, etc...) all work flawlessly. It's an elegant separation of labor, and your database will simply crank, because the machine will have only one purpose: handle the data.
January 31st, 2003, 07:50 AM
When you find out how much Crystal Reports server costs, that'll kill the idea.
February 6th, 2003, 07:12 PM