On Tue, Jun 6, 2006, Geo Carncross said: > On Tue, 2006-06-06 at 13:37 -0700, Aaron Stone wrote: > 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.
Ok. It would also be good to keep the compiled regex across calls; I'm sure recompiling it for every possibly matching row of the table is less than ideal. Actually. There's no point here -- the REGEX keyword is not supported at all until SQLite 3.3. So I may have to switch to your LIKE/ILIKE method below. > 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. My understanding is that we need a whole new module, dbsqlite3.c, to make the version 3 calls and to compile with the version 3 library. Is it even worth keeping the version 2 code around for DBMail 2.2.x? What's the migration path to get an existing SQLite 2.x database updated to SQLite 3.x? > 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 %.... If this approach is a lot faster, then I don't mind rewriting the function to produce a long bunch of LIKE's. "Would you like one loop or two?" ;-) Aaron
