[Qt-interest] Qt Sqlite UserDefinedFunction

[Sylvain Pointeau]

Hi Bill,

please find below my code to do this, the major problem was the compilation.
I compiled Qt with the SQLite plugin, then I recompile the plugin with the
latest source of SQLite (not the amalgamation one that I am 99% sure that it
was the problem) then I also link my application to the latest source / lib
of SQLite as well... and it worked fine!

Cheers,
Sylvain Pointeau

extern "C" static void sqlite3_regexp(sqlite3_context *context, int argc,
sqlite3_value **argv){
    assert( argc==2 );

    const unsigned char* pattern_utf8 = sqlite3_value_text(argv[0]);
    const unsigned char* lhs_utf8 = sqlite3_value_text(argv[1]);

    if( !lhs_utf8 || !pattern_utf8 ) return;

    QString pattern = QString::fromUtf8( (const char*)pattern_utf8 );
    QString lhs = QString::fromUtf8( (const char*)lhs_utf8 );

    QRegExp regexp ( pattern, Qt::CaseSensitive, QRegExp::RegExp2 );
    bool contains = lhs.contains( regexp );

    sqlite3_result_int( context, contains?1:0 );
}

int main(int argc, char** argv)
{
    QCoreApplication app( argc, argv );
    QTextStream qout (stdout);

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("testcase.db");
    bool ok = db.open();

    QVariant v = db.driver()->handle();
    if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*")==0) {
        // v.data() returns a pointer to the handle

        sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
        if (handle != 0) { // check that it is not NULL
            sqlite3_create_function(handle, "regexp", 2, SQLITE_UTF8, 0,
sqlite3_regexp, 0, 0);
        }
    }

....

}



On Thu, Dec 11, 2008 at 2:48 AM, Bill KING <bill.king at trolltech.com> wrote:

> Sylvain Pointeau wrote:
> > Hello,
> >
> > How do I create a user defined function for SQLite using Qt?
> > I would like to define the REGEXP using Qt behind.
> >
> > Thanks in advance for your help,
> >
> > Cheers,
> > Sylvain
> > ------------------------------------------------------------------------
> >
> > _______________________________________________
> > Qt-interest mailing list
> > Qt-interest at trolltech.com
> > http://lists.trolltech.com/mailman/listinfo/qt-interest
> >
> Sorry for the delay, have been busy, but, a code snippet may lead you
> down the right track, in this case, we're installing custom sorting, but
> the steps are the same:
>
> void installSorting( QSqlDatabase &db)
> {
>        int sqliteLocaleAwareCompare(void *, int ll, const void *l, int
> rl, const void *r);
>        QVariant v = db.driver()->handle();
>        if (v.isValid() && strcmp(v.typeName(), "sqlite3*") == 0) {
>            // v.data() returns a pointer to the handle
>            sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
>            if (handle != 0) { // check that it is not NULL
>                int result = sqlite3_create_collation(
>                        handle,
>                        "localeAwareCompare",
>                        SQLITE_UTF16, // ANY would be nice, but we only
> encode in 16 anyway.
>                        0,
>                        sqliteLocaleAwareCompare);
>                if (result != SQLITE_OK)
>                    qWarning() << "Could not add string collation
> function: " << result;
>            } else {
>                qWarning() << "Could not get sqlite handle";
>            }
>        } else {
>            qWarning() << "handle variant returned typename " <<
> v.typeName();
>        }
> }
>
> --
> Bill King, Software Engineer
> Qt Software, Nokia Pty Ltd
> Brisbane Office
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.qt-project.org/pipermail/qt-interest-old/attachments/20081211/f3d3a561/attachment.html