SQLite in C and supporting REGEXP

I'm using sqlite3 in C and I'd like to add support for the REGEXP operator. By default, a user defined function regexp() is not present and calling REGEXP will usually result in an error (according to the SQLite pages).

1. How do I add a regexp function to support REGEXP? Presumably I will do this via the sqlite3_create_function call, but I don't know what the application-defined regexp() will look like.

2. Can I use a function from regex.h with sqlite3_create_function and how? Any function I pass to SQLite has to take three arguments of type sqlite3_context*, int, sqlite3_value**. However, the SQLite documents don't seem to explain the meaning of these parameters.

3. Is there sample co开发者_StackOverflowde for a C regexp() function?

I've not been able to find much on this using Google or the SQLite pages.

You can also try this:

#include <regex.h>

...

void sqlite_regexp(sqlite3_context* context, int argc, sqlite3_value** values) {
    int ret;
    regex_t regex;
    char* reg = (char*)sqlite3_value_text(values[0]);
    char* text = (char*)sqlite3_value_text(values[1]);

    if ( argc != 2 || reg == 0 || text == 0) {
        sqlite3_result_error(context, "SQL function regexp() called with invalid arguments.\n", -1);
        return;
    }

    ret = regcomp(&regex, reg, REG_EXTENDED | REG_NOSUB);
    if ( ret != 0 ) {
        sqlite3_result_error(context, "error compiling regular expression", -1);
        return;
    }

    ret = regexec(&regex, text , 0, NULL, 0);
    regfree(&regex);

    sqlite3_result_int(context, (ret != REG_NOMATCH));
}

...

sqlite3_create_function(*db, "regexp", 2, SQLITE_ANY,0, &sqlite_regexp,0,0)

It would look something like this:

static void user_regexp(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    struct re_pattern_buffer buffer;
    const char *out;
    char *pattern;
    char *input_string;
    char *result;
    struct re_registers regs;

    if ((sqlite3_value_type(argv[0]) != SQLITE_TEXT )
         || ((sqlite3_value_type(argv[1]) != SQLITE_TEXT ))
    {
        sqlite3_result_err("Improper argument types");
        return;
    }

    re_set_syntax(RE_SYNTAX_POSIX_EGREP);
    memset(&buffer, 0, sizeof (buffer));
    if (!(pattern = strdupa(sqlite3_value_text(argv[0])))
        || !(input_string = strdupa(sqlite3_value_text(argv[1]))))
    {
        sqlite3_result_err_nomem("Could not allocate memory for strings");
        return;
    }

    if ((out = re_compile_pattern(pattern, strlen(pattern), &buffer))
    {
        sqlite3_result_err("Could not compile pattern!");
        return;
    }

    if (re_match(&buffer, input_string, strlen(input_string), 0, &regs) < 0) 
        sqlite3_result_int64(context, 0);
    else 
    {
        result = strndupa(input_string + regs.start[0], regs.end[0] - regs.start[0]);    
        sqlite3_result_text(context, result, NULL, SQLITE_TRANSIENT);
    }
}

Okay, a bit too late for this but I'm tempted to post this for all people who are using a C++ Wrapper for the C SQLITE API like the [ SQLiteCpp ] which I am using. This answer assumes that you use SQLiteCpp.

• Install Regex for windows binaries from [ here ]. This gives you just enough files ie the regex.h include file and regex2.dll. Do remember to add the path regex.h in your project and have a copy of the dll in the folder containing client executables.

• Before building the [ SQLiteCpp ], we need to make some changes to add the regex capabilities to SELECT queries. For this open the Database.cpp file from the [ SQLiteCpp ] project and

  • Include the regex.h header from the Regex for windows

  • After all the includes, add below ( of course you can customize to custom fit your needs!) piece of code just below it.

    extern "C" {
    void sqlite_regexp(sqlite3_context* context, int argc, sqlite3_value** values) {
    int ret;
    regex_t regex;
    char regtext[100];
    char* reg = (char*)sqlite3_value_text(values[0]);
    sprintf(regtext, ".*%s.*", reg);
    //printf("Regtext : %s", regtext);
    char* text = (char*)sqlite3_value_text(values[1]);
    /*  printf("Text : %s\n", text);
    printf("Reg : %s\n", reg); */
    if (argc != 2 || reg == 0 || text == 0) {
        sqlite3_result_error(context, "SQL function regexp() called with invalid arguments.\n", -1);
        return;
    }
    
    ret = regcomp(&regex, regtext, REG_EXTENDED | REG_NOSUB | REG_ICASE);
    if (ret != 0) {
        sqlite3_result_error(context, "error compiling regular expression", -1);
        return;
    }
    
    ret = regexec(&regex, text, 0, NULL, 0);
    /*  if (ret == 0) {
    printf("Found a match. Press any key to continue");
    getc(stdin);
    }*/
    regfree(&regex);
    
    sqlite3_result_int(context, (ret != REG_NOMATCH));
    }
    }
    

  • Now it is time to change the constructors defined in the file. Change those like shown below.

    // Open the provided database UTF-8 filename with SQLite::OPEN_xxx provided flags.
    Database::Database(const char* apFilename,
    const int   aFlags /*= SQLite::OPEN_READONLY*/,
    const int   aBusyTimeoutMs/* = 0 */,
    const char* apVfs/*= NULL*/) :
    mpSQLite(NULL),
    mFilename(apFilename)
    {
    const int ret = sqlite3_open_v2(apFilename, &mpSQLite, aFlags, apVfs);
    //std::cout << "Reached here";
    //sqlite3_create_function_v2(mpSQLite, "REGEXP", 2, SQLITE_ANY,&sqlite_regexp, NULL, NULL, NULL,NULL);
    sqlite3_create_function(mpSQLite, "regexp", 2, SQLITE_ANY, 0, &sqlite_regexp, 0, 0);
    if (SQLITE_OK != ret)
    {
    const SQLite::Exception exception(mpSQLite, ret); // must create before closing
    sqlite3_close(mpSQLite); // close is required even in case of error on opening
    throw exception;
    }
    else {
    
    }
    if (aBusyTimeoutMs > 0)
    {
    setBusyTimeout(aBusyTimeoutMs);
    }
    }
    
    // Open the provided database UTF-8 filename with SQLite::OPEN_xxx provided flags.
    Database::Database(const std::string& aFilename,
    const int          aFlags     /*    = SQLite::OPEN_READONLY*/,
    const int          aBusyTimeoutMs/*  = 0*/,
    const std::string& aVfs/* = "" */) :
    mpSQLite(NULL),
    mFilename(aFilename)
    {
    
    const int ret = sqlite3_open_v2(aFilename.c_str(), &mpSQLite, aFlags, aVfs.empty() ? NULL : aVfs.c_str());
    sqlite3_create_function(mpSQLite, "regexp", 2, SQLITE_ANY, 0, &sqlite_regexp, 0, 0);
    if (SQLITE_OK != ret)
    {
    const SQLite::Exception exception(mpSQLite, ret); // must create before closing
    sqlite3_close(mpSQLite); // close is required even in case of error on opening
    throw exception;
    }
    if (aBusyTimeoutMs > 0)
    {
    setBusyTimeout(aBusyTimeoutMs);
    }
    }
    

• By now, you've some serious regex capabilities with your sqlite. Just build the project.

• Write a client program to test the functionality. It can be something like below ( borrowed without shame from SQLiteCpp Example ).

  #include <iostream>
  #include <cstdio>
  #include <cstdlib>
  #include <string>
  #include <SQLiteCpp/SQLiteCpp.h>
  #include <SQLiteCpp/VariadicBind.h>
  // Notice no sqlite3.h huh?
  // Well, this is a C++ wrapper for the SQLITE CAPI afterall.
  
  #ifdef SQLITECPP_ENABLE_ASSERT_HANDLER
  namespace SQLite
  {
  /// definition of the assertion handler enabled when SQLITECPP_ENABLE_ASSERT_HANDLER is defined in the project (CMakeList.txt)
  void assertion_failed(const char* apFile, const long apLine, const char* apFunc, const char* apExpr, const char* apMsg)
  {
  // Print a message to the standard error output stream, and abort the program.
  std::cerr << apFile << ":" << apLine << ":" << " error: assertion failed (" << apExpr << ") in " << apFunc << "() with message \"" << apMsg << "\"\n";
  std::abort();
  }
  }
  #endif
  
  /// Get example path
  static inline std::string getExamplePath()
  {
  std::string filePath(__FILE__);
  return filePath.substr(0, filePath.length() - std::string("Client.cpp").length());
  }
  
  /// Example Database
  static const std::string filename_example_db3 = getExamplePath() + "/example.db3";
  /// Image
  static const std::string filename_logo_png = getExamplePath() + "/logo.png";
  
  
  /// Object Oriented Basic example
  class Example
  {
  public:
  //Constructor
  Example() :
  mDb(filename_example_db3),
  // User change the db and tables accordingly
  mQuery(mDb, "SELECT id,name FROM lookup WHERE name REGEXP :keyword")
  // Open a database file in readonly mode
  {       
  }
  virtual ~Example()
  {
  }
  
  /// List the rows where the "weight" column is greater than the provided aParamValue
  void namehaskeyword(const std::string searchfor)
  {
  std::cout << "Matching results for " << searchfor << "\n";
  
  // Bind the integer value provided to the first parameter of the SQL query
  mQuery.bind(1,searchfor); // same as mQuery.bind(1, aParamValue);
  
   // Loop to execute the query step by step, to get one a row of results at a time
  while (mQuery.executeStep())
  {
  std::cout<<mQuery.getColumn(0) << "\t" << mQuery.getColumn(1) << "\n";
  }
  
  // Reset the query to be able to use it again later
  mQuery.reset();
  }
  
  private:
  SQLite::Database    mDb;    ///< Database connection
  SQLite::Statement   mQuery; ///< Database prepared SQL query
  };
  
  int main()
  {
  // Using SQLITE_VERSION would require #include <sqlite3.h> which we want to avoid: use SQLite::VERSION if possible.
  // std::cout << "SQlite3 version " << SQLITE_VERSION << std::endl;
  std::cout << "SQlite3 version " << SQLite::VERSION << " (" << SQLite::getLibVersion() << ")" << std::endl;
  std::cout << "SQliteC++ version " << SQLITECPP_VERSION << std::endl;
  
  try
  {
  // Doing  a regex query.
  Example example;
  char wannaquit = 'n';
  std::string keyword;
  // Deliberate unlimited loop. You implement something sensible here.
  while (wannaquit != 'y') {
  // Demonstrates the way to use the same query with different parameter values
  std::cout << "Enter the keyword to search for : ";
  std::getline(std::cin, keyword);
  example.namehaskeyword(keyword);
  }
  }
  catch (std::exception& e)
  {
  std::cout << "SQLite exception : " << e.what() << std::endl;
  return EXIT_FAILURE; // unexpected error : exit the example program
  }
  return EXIT_SUCCESS;
  }
  

^{Note : This assumes that the database is in the same folder as your cpp}