C语言中的SQLite和支持REGEXP

如何添加正则regexp函数来支持REGEXP ？ 据推测，我会通过sqlite3_create_function调用来做到这一点，但我不知道应用程序定义的regexp()会是什么样子。

我可以使用regex.h的函数与sqlite3_create_function以及如何？ 我传递给SQLite的任何函数都必须带有三个类型为sqlite3_context *，int，sqlite3_value **的参数。 但是，SQLite文档似乎没有解释这些参数的含义。

是否有一个C regexp()函数的示例代码？

在构建[SQLiteCpp]之前，我们需要进行一些更改以将正则表达式功能添加到SELECT查询。 为此，从[SQLiteCpp]项目中打开Database.cpp文件

在所有包含之后，在下面添加（当然，您可以自定义以适应您的需求！）它下面的一段代码。

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

现在该更改文件中定义的构造函数了。 改变如下所示的那些。

// 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);
}
}

到目前为止，你的sqlite有一些严肃的regex能力。 只需建立该项目。

编写一个客户端程序来测试功能。 它可以像下面这样（从SQLiteCpp示例中借用而不会感到羞耻）。

#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;
}