Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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?
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  2. #17
  3. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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):

    http://sourceforge.net/tracker/?func...&group_id=9028

    Also posts like this:
    http://www.experts-exchange.com/Data..._20393578.html


    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:

    http://www.commandprompt.com/ppbook/r24254.htm

    Functional index
    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 03:40 PM.
  4. #18
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    13
    So you're back to validating the data on input.
    - Walt

    Will code for food.
  6. #19
  7. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    ...could validate the data on input to force all upper or lower case as needed.

    So you're back to validating the data on input.
    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?
  8. #20
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    13
    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.
    - Walt

    Will code for food.
  10. #21
  11. An Ominous Coward
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2002
    Posts
    4,425
    Rep Power
    0
    Wuhuh? I'm sorry... was I supposed to be paying attention to any of this?

    dcaillouet -

    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!
  12. #22
  13. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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)

    Classic
    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).

    Super
    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.
  14. #23
  15. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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.

    dcaillouet-

    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  16. #24
  17. No Profile Picture
    Just another member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Location
    New Zealand
    Posts
    18
    Rep Power
    0
    When you find out how much Crystal Reports server costs, that'll kill the idea.
  18. #25
  19. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2001
    Location
    New Zealand
    Posts
    638
    Rep Power
    14
    http://agata.codigolivre.org.br

    Agata Report is a Database Reporting Tool, like Crystal Reports. Its written in PHP-GTK with PEAR and allows you to edit and get SQL results from several databases. There is a merge tool that merges DB results into a document, there's a tool for address label generation, ER diagrams are generated automatically from DB too. Agata allows you to create reports with many levels of grouped data with subtotals and grand totals, beyond
    run-time parameters. There are versions in English, Portuguese, Spanish, Italian, French, Deutsch and Swedish. Agata support different themes too.

    It connects to database, which can be PostgreSQL, MySQL, Oracle, DB2, MsSql, Informix or InterBase, lists its tables, fields and allows linking among tables, make constraints, orders, to group data, to apply functions and make queries. It generates results to PostScript or Text file.
    I'd want support for XML like apache cocoon

    Z.

    PS> its Open Source, so the price wont kill the idea
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo