On Tue, 2006-06-06 at 13:37 -0700, Aaron Stone wrote: > On Tue, 2006-06-06 at 16:13 -0400, Geo Carncross wrote: > > Not all databases support REGEXP as an operator. > > Everybody's got some keyword that performs this type of match. I added > entries to the db_get_sql fragment function to grab the right operator. > I also added a user function to dbsqlite.c, but it still doesn't compile > with my SQLite 3 installation. I have no clue what the deal is there.
You probably want REG_NOSUB|REG_EXTENDED as most of the other engines use POSIX EXTENDED regular expressions, and not BASIC ones (which have \( and \) and stuff backwards. You also don't need to include sqlite3.h -- it's only using the SQLite2 API, even if the database format itself can be SQLite3. > > LIKE is either case-insensitive, or can be made so with a > > (database-specific) operator on most databases. > > The base 64 portions of the modified utf 7 encoding are case sensitive, > while the US-ASCII portions are not. The only way I can see to match > like this is using a case sensitive regular expression with explicit > case insensitivity where it is allowed. Oh, I guess I missed that. But see below: > One idea might be using an OR, such that a direct perfect match returns > almost instantly from the index; I bet this will be the case 75% of the > time -- IMAP clients are highly likely to preserve case. That's a good one- but another point might be to pass the code generator to the SQL wrapper. PostgreSQL is faster using SIMILAR TO than REGEXP- as SIMILAR TO can still use the index AND SIMILAR TO also supports mixing case sensitive/insensitive parts. Likewise, you can use both LIKE and ILIKE together to get the various parts, as in: x ILIKE 'SENT_ITEMS+______' AND x LIKE '__________+xab414'; or: x LIKE 'SENT_ITEMS+______' AND x BINARY LIKE '__________+xab414'; or using SUBSTR() if you aren't going to prefix and postfix with %.... -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
