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,
