Re: [sqlite] Create a function to limit results based on conditions

[Brad Campbell]

George Ionescu wrote:
Hello SQLite users,
Hello Dr. Hipp,
I would like to implement my own RegExp functions (to limit results based on conditions). From my understanding, sqlite_create_function cannot be used for this (the function can only return a value; it cannot tell sqlite not to return a row). What is the approach you would recommend?

Regards,
George Ionescu
I have done just this.. relevant code below (It's C in a C++ project, messy and relatively 
inefficient but it works a treat).

The killer bit is in the open database routine where it creates the user define function.

/*
 * Functions to patch a regex search into sqlite
 */
int sqliteRlikeCompare(const char *zPattern, const char *zString, sqregex *reg){
        int res;
        if (reg->regex_raw == NULL || (strcmp (zPattern, reg->regex_raw) != 0)){
                if (reg->regex_raw != NULL) {
                    free(reg->regex_raw);
                    regfree(&reg->regex_c);
                }
                reg->regex_raw = (char *)malloc(strlen(zPattern)+1);
                strncpy(reg->regex_raw, zPattern, strlen(zPattern)+1);
                res = regcomp(&reg->regex_c, zPattern, REG_EXTENDED);
                if ( res != 0 ) {
                    printf("Regcomp failed with code %u on string %s\n",res,zPattern);
                    free(reg->regex_raw);
                    reg->regex_raw=NULL;
                return 0;
                }
        }
        res = (regexec(&reg->regex_c, zString, 0, NULL, 0)==0);
        return res;
}
void rlikeFunc(sqlite_func *context, int arg, const char **argv){
        if( argv[0]==0 || argv[1]==0 ){
                printf("One of arguments Null!!\n");
                return;
        }
                sqlite_set_result_int(context,
                sqliteRlikeCompare((const char*)argv[0],
        (const char*)argv[1], (sqregex *)sqlite_user_data(context) ));
}
/*
 * try to open a db specified via setUrl
 * and options
 */
bool OSQLiteDriver::open() {
    char *error;
 qDebug("OSQLiteDriver::open: about to open");
    m_sqlite = sqlite_open(m_url.local8Bit(),
                           0,
                           &error );
    /* failed to open */
    if (m_sqlite == 0l ) {
        // FIXME set the last error
        qWarning("OSQLiteDriver::open: %s", error );
        free( error );
        return false;
    }
    if (sqlite_create_function(m_sqlite,"rlike",2,rlikeFunc,&sqreg) != 0)
            odebug << "Unable to create user defined function!" << oendl;
    if (sqlite_function_type(m_sqlite,"rlike",SQLITE_NUMERIC) != 0)
            odebug << "Unable to set rlike function result type!" << oendl;
    sqreg.regex_raw = NULL;
    return true;
}
Regards,
Brad
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]