SQLite in C and REGEXP Support

Question

I am using sqlite3 in C and I would like to add support for the REGEXP statement. By default, the user-defined function regexp() absent, and a REGEXP call usually results in an error (according to SQLite pages).

• How to add REGEXP function to support REGEXP ? Presumably, I will do this by calling sqlite3_create_function , but I do not know what the regexp() application will look like.

• Is it possible to use a function from regex.h with sqlite3_create_function and how? Any function passed to SQLite must take three arguments of type sqlite3_context *, int, sqlite3_value **. However, SQLite docs do not seem to explain the meaning of these parameters.

• Is there any sample code for the C regexp() function?

I could not find much using Google or SQLite pages.

Answer 1

You can also try the following:

 #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) 

Answer 2

It will 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); } } 

Answer 3

Ok, it's too late for this, but I am tempted to publish this for all people who use C ++ Wrapper for the SQLITE C API, for example [SQLiteCpp] , which I use. This answer assumes you are using SQLiteCpp.

• Install Regex for Windows binaries from [here] . This gives you enough files, that is, the regex.h include file and regex2.dll . Remember to add the regex.h path to your project and have a copy of the DLL in the folder containing the client executables.

• Before creating [SQLiteCpp] , we need to make some changes to add regex features to SELECT queries. To do this, open the Database.cpp file from the [SQLiteCpp] project and

  • Include regex.h header from regex for windows

  • After everything is included, add below (of course, you can customize it for your own needs!) A part of the code is a bit lower.

     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's time to change the constructors defined in the file. Change the ones 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); } } 

• You now have serious regex features with your sqlite . Just create a project.

• Write a client program to test functionality. It may 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. . It assumes the database is in the same folder as your cpp}