Re: [firebird-support] Computed index for case insensitive queries
Joel, Although I would like to use UTF-8, my application uses several functions that only work with single byte ASCII characters. Sorry for responding so late, my group messages are going into the spam folder for some reason. On Mon, Aug 25, 2014 at 11:16 AM, Joel do Prado Junior big.rid.lis...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Qual a versão do firebird você está usando ? Porque não usa utf-8 ? Em 25/08/2014 13:07, Caroline Beltran caroline.d.belt...@gmail.com [firebird-support] escreveu: Alexandre, thank you for suggesting! I am thinking of using the ISO8859_1 characterset and ES_ES_CI_AI collation. Since I need support for English and Spanish, would this collation be the best option for case/accent insensitive searching? Thanks Again!
Re: [firebird-support] Computed index for case insensitive queries
Qual a versão do firebird você está usando ? Porque não usa utf-8 ? Em 25/08/2014 13:07, Caroline Beltran caroline.d.belt...@gmail.com [firebird-support] escreveu: Alexandre, thank you for suggesting! I am thinking of using the ISO8859_1 characterset and ES_ES_CI_AI collation. Since I need support for English and Spanish, would this collation be the best option for case/accent insensitive searching? Thanks Again!
Re: [firebird-support] Computed index for case insensitive queries
Em 25/8/2014 00:59, Caroline Beltran caroline.d.belt...@gmail.com [firebird-support] escreveu: Mark, thank you for responding. I ended up downloading FlameRobin and it does show computed indexes in the 'Index' tab as well as in the DDL, i.e.: CREATE INDEX IDX_FNAME ON PEOPLE COMPUTED BY (lower(fname)); P.S. I found your response in my Google 'Sent' folder, it never made it into my inbox, but anyway, I am glad I located your message. Why not use a case insensitve collation ? see you !
Re: [firebird-support] Computed index for case insensitive queries
Alexandre, thank you for suggesting! I am thinking of using the ISO8859_1 characterset and ES_ES_CI_AI collation. Since I need support for English and Spanish, would this collation be the best option for case/accent insensitive searching? Thanks Again!
Re: [firebird-support] Computed index for case insensitive queries
Mark, thank you for responding. I ended up downloading FlameRobin and it does show computed indexes in the 'Index' tab as well as in the DDL, i.e.: CREATE INDEX IDX_FNAME ON PEOPLE COMPUTED BY (lower(fname)); P.S. I found your response in my Google 'Sent' folder, it never made it into my inbox, but anyway, I am glad I located your message.
[firebird-support] Computed index for case insensitive queries
I created the following computed index: CREATE INDEX IDX_FNAME ON PEOPLE COMPUTED BY (lower(fname)) This works fine and instead of traversing the entire table when performing a case insensitive query, an 'index read' for matching records is performed. The question I have is that the database management utility I use does not show the true index DDL for my computed index. This is what the utility shows: CREATE INDEX IDX_FNAME ON PEOPLE(); Is my database management tool not showing this correctly or is this what the Firebird server returns?
Re: [firebird-support] Computed index for case insensitive queries
On Fri, 22 Aug 2014 01:18:17 -0500, Caroline Beltran caroline.d.belt...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: I created the following computed index: CREATE INDEX IDX_FNAME ON PEOPLE COMPUTED BY (lower(fname)) This works fine and instead of traversing the entire table when performing a case insensitive query, an 'index read' for matching records is performed. The question I have is that the database management utility I use does not show the true index DDL for my computed index. This is what the utility shows: CREATE INDEX IDX_FNAME ON PEOPLE(); Is my database management tool not showing this correctly or is this what the Firebird server returns? Firebird server itself doesn't have any functionality to return the DDL of database objects. It is up to tools to implement this themselves (eg like isql does for its extract functionality). So probably your tool doesn't support extracting computed indices. By the looks of it, it assumes that an index always has one or more 'index segments' (the columns of the index), and it doesn't look at RDB$SEGMENT_COUNT (value 0 indicates computed) and/or RDB$EXPRESSION_BLR and RDB$EXPRESSION_SOURCE in table RDB$INDICES to determine if it is a normal or computed index. Mark
