SQLite in C and supporting REGEXP

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.

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.

Is there sample code for a C regexp() function?

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

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 : %sn", text);
printf("Reg : %sn", 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;
}