On Tue, 2006-06-20 at 09:45 +0200, Paul J Stevens wrote: > > Geo Carncross wrote: > > > Just FYI: VACUUM is no longer required in sqlite3- the database can > > AutoVacuum as necessary. > > Great.
This change is in the DIFF. > > ... and I thought we were stopping the REGEX stuff to take advantage of > > the indexes where possible? > > Yes, but I've just been porting dbsqlite.c as-is, and the regex stuff > was there. I guess Aaron's to blame there :-) Okay good. Then I have an excellent (I hope) solution to your sensitive/insensitive problem. I haven't tested this (just built it)- but I wanted to get the idea reviewed as soon as possible... If it's not the definition of hackish, I don't know what is :) -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
Index: modules/dbsqlite.c =================================================================== --- modules/dbsqlite.c (revision 2183) +++ modules/dbsqlite.c (working copy) @@ -29,8 +29,13 @@ db_param_t _db_params; -/* native format is the character string */ +static sqlite3 *conn; +/* SQLITE3 internals... */ +extern int sqlite3ReadUtf8(const unsigned char *); +extern const unsigned char sqlite3UpperToLower[]; +extern int sqlite3utf8CharLen(const char *pData, int nByte); + const char * db_get_sql(sql_fragment_t frag) { switch(frag) { @@ -50,14 +55,20 @@ return ""; break; case SQL_REGEXP: - return "REGEXP"; + trace(TRACE_ERROR, "We deliberately don't support REGEXP operations."); + sqlite3_close(conn); + exit(255); break; - // FIXME: To get this right, we have to supply a - // a replacement user defined like function. According - // to the SQLite manual, LIKE is case insensitive for - // US-ASCII characters and case sensitive elsewhere. + /* some explaining: + * + * sqlite3 has a limited number of A x B operators: LIKE, GLOB, REGEXP. + * we need two until this function knows how to go %s LIKE %s so that we can + * instead use LIKE_INSENSITIVE(%s,%s) or whatnot. + * + * until then.... + */ case SQL_SENSITIVE_LIKE: - return "LIKE"; + return "REGEXP"; break; case SQL_INSENSITIVE_LIKE: return "LIKE"; @@ -66,11 +77,9 @@ return NULL; } -static sqlite3 *conn; - struct qtmp { char **resp; - unsigned int rows, cols; + int rows, cols; }; struct qtmp *lastq = 0, *saveq = 0, *tempq = 0; @@ -95,26 +104,144 @@ (void)sqlite3_result_text(f,buf,-1,SQLITE_TRANSIENT); } -static void dbsqlite_regexp(sqlite3_context *f, int argc, const char **argv) -{ - int res = 0; - regex_t re; - char *pattern, *string; +/* this is lifted directly from sqlite -- cut here -- */ +struct compareInfo { + unsigned char matchAll; + unsigned char matchOne; + unsigned char matchSet; + unsigned char noCase; +}; +static const struct compareInfo likeInfo = { '%', '_', 0, 0 }; +#define sqliteNextChar(X) while( (0xc0&*++(X))==0x80 ){} +#define sqliteCharVal(X) sqlite3ReadUtf8(X) - if (argc == 2) { - pattern = (char *)argv[0]; - string = (char *)argv[1]; +static int patternCompare( + const unsigned char *zPattern, /* The glob pattern */ + const unsigned char *zString, /* The string to compare against the glob */ + const struct compareInfo *pInfo, /* Information about how to do the compare */ + const int esc /* The escape character */ +){ + register int c; + int invert; + int seen; + int c2; + unsigned char matchOne = pInfo->matchOne; + unsigned char matchAll = pInfo->matchAll; + unsigned char matchSet = pInfo->matchSet; + unsigned char noCase = pInfo->noCase; + int prevEscape = 0; /* True if the previous character was 'escape' */ - if (regcomp(&re, pattern, REG_NOSUB) == 0) { - if (regexec(&re, string, 0, NULL, 0) == 0) { - res = 1; - } - regfree(&re); + while( (c = *zPattern)!=0 ){ + if( !prevEscape && c==matchAll ){ + while( (c=zPattern[1]) == matchAll || c == matchOne ){ + if( c==matchOne ){ + if( *zString==0 ) return 0; + sqliteNextChar(zString); + } + zPattern++; + } + if( c && esc && sqlite3ReadUtf8(&zPattern[1])==esc ){ + unsigned char const *zTemp = &zPattern[1]; + sqliteNextChar(zTemp); + c = *zTemp; + } + if( c==0 ) return 1; + if( c==matchSet ){ + assert( esc==0 ); /* This is GLOB, not LIKE */ + while( *zString && patternCompare(&zPattern[1],zString,pInfo,esc)==0 ){ + sqliteNextChar(zString); + } + return *zString!=0; + }else{ + while( (c2 = *zString)!=0 ){ + if( noCase ){ + c2 = sqlite3UpperToLower[c2]; + c = sqlite3UpperToLower[c]; + while( c2 != 0 && c2 != c ){ c2 = sqlite3UpperToLower[*++zString]; } + }else{ + while( c2 != 0 && c2 != c ){ c2 = *++zString; } + } + if( c2==0 ) return 0; + if( patternCompare(&zPattern[1],zString,pInfo,esc) ) return 1; + sqliteNextChar(zString); + } + return 0; + } + }else if( !prevEscape && c==matchOne ){ + if( *zString==0 ) return 0; + sqliteNextChar(zString); + zPattern++; + }else if( c==matchSet ){ + int prior_c = 0; + assert( esc==0 ); /* This only occurs for GLOB, not LIKE */ + seen = 0; + invert = 0; + c = sqliteCharVal(zString); + if( c==0 ) return 0; + c2 = *++zPattern; + if( c2=='^' ){ invert = 1; c2 = *++zPattern; } + if( c2==']' ){ + if( c==']' ) seen = 1; + c2 = *++zPattern; + } + while( (c2 = sqliteCharVal(zPattern))!=0 && c2!=']' ){ + if( c2=='-' && zPattern[1]!=']' && zPattern[1]!=0 && prior_c>0 ){ + zPattern++; + c2 = sqliteCharVal(zPattern); + if( c>=prior_c && c<=c2 ) seen = 1; + prior_c = 0; + }else if( c==c2 ){ + seen = 1; + prior_c = c2; + }else{ + prior_c = c2; + } + sqliteNextChar(zPattern); + } + if( c2==0 || (seen ^ invert)==0 ) return 0; + sqliteNextChar(zString); + zPattern++; + }else if( esc && !prevEscape && sqlite3ReadUtf8(zPattern)==esc){ + prevEscape = 1; + sqliteNextChar(zPattern); + }else{ + if( noCase ){ + if( sqlite3UpperToLower[c] != sqlite3UpperToLower[*zString] ) return 0; + }else{ + if( c != *zString ) return 0; + } + zPattern++; + zString++; + prevEscape = 0; + } + } + return *zString==0; +} +/* this is lifted directly from sqlite -- cut here -- */ +/* this is lifted "almost" :) directly from sqlite -- cut here -- */ +static void dbsqlite_cslike(sqlite3_context *context, int argc, sqlite3_value **argv) +{ + /* this code comes from SQLite's built-in LIKE function */ + const unsigned char *zA = sqlite3_value_text(argv[0]); + const unsigned char *zB = sqlite3_value_text(argv[1]); + int escape = 0; + if( argc==3 ){ + /* The escape character string must consist of a single UTF-8 character. + ** Otherwise, return an error. + */ + const unsigned char *zEsc = sqlite3_value_text(argv[2]); + if( sqlite3utf8CharLen(zEsc, -1)!=1 ){ + sqlite3_result_error(context, + "ESCAPE expression must be a single character", -1); + return; } + escape = sqlite3ReadUtf8(zEsc); } - - (void)sqlite3_result_int(f, res); + if( zA && zB ){ + sqlite3_result_int(context, patternCompare(zA, zB, &likeInfo, escape)); + } } +/* this is lifted "almost" :) directly from sqlite -- cut here -- */ int db_connect() { @@ -137,7 +264,7 @@ trace(TRACE_ERROR, "%s,%s: sqlite3_create_function failed", __FILE__,__func__); return -1; } - if (sqlite3_create_function(conn, "REGEXP", 2, SQLITE_ANY, NULL, (void *)dbsqlite_regexp, NULL, NULL) != SQLITE_OK) { + if (sqlite3_create_function(conn, "REGEXP", 2, SQLITE_ANY, NULL, (void *)dbsqlite_cslike, NULL, NULL) != SQLITE_OK) { sqlite3_close(conn); trace(TRACE_ERROR, "%s,%s: sqlite3_create_function failed", __FILE__,__func__); return -1; @@ -210,15 +337,18 @@ return -1; } } -/* - if (sqlite3_get_table(conn, the_query, &lastq->resp, (int *)&lastq->rows, (int *)&lastq->cols, &errmsg) != SQLITE_OK) { + if (sqlite3_get_table(conn, the_query, &lastq->resp, + (int *)&lastq->rows, (int *)&lastq->cols, &errmsg) != SQLITE_OK) { trace(TRACE_ERROR, - "%si,%s: sqlite3_exec failed: %s", + "%si,%s: sqlite3_get_table failed: %s", __FILE__, __func__, errmsg); sqlite3_free(errmsg); return -1; } -*/ + if (lastq->rows < 0 || lastq->cols < 0) { + lastq->rows = 0; + lastq->cols = 0; + } return 0; } unsigned long db_escape_string(char *to, const char *from, unsigned long length) @@ -243,11 +373,13 @@ { char *errmsg; if (!conn) return -1; + /* while VACUUM doesn't do anything when PRAGMA auto_vacuum=1 + * (which happens to be the new default), it also doesn't hurt + * either... + */ if (sqlite3_exec(conn, "VACUUM", NULL, NULL, &errmsg) != SQLITE_OK) { - trace(TRACE_ERROR, - "%s,%s: error vacuuming database: %s", - __FILE__, __func__, errmsg); - return -1; + /* no reporting... */ + sqlite3_free(errmsg); } return 0; } Index: sql/sqlite/create_tables.sqlite =================================================================== --- sql/sqlite/create_tables.sqlite (revision 2183) +++ sql/sqlite/create_tables.sqlite (working copy) @@ -19,6 +19,8 @@ BEGIN TRANSACTION; +PRAGMA auto_vacuum = 1; + CREATE TABLE dbmail_aliases ( alias_idnr INTEGER PRIMARY KEY, alias TEXT NOT NULL,