SQLite - how to make own COLLATION?

[Kobid]

Hi,

I'm searching all day how to make own collaction function. Finally I'm able to compile project using "sqlite3.h" but sqlite3_create_collation16() function is rising SIGSEGV exception and I don't know why. Here is my code:

.pri:

LIBS += -lsqlite3

.cpp:

#include <sqlite3.h>

static int localeCompare( void* /*arg*/, int len1, const void* data1,
    int len2, const void* data2 )
{
    qDebug() << "localeCompare";
    QString string1 = QString::fromRawData( reinterpret_cast<const QChar*>( data1 ),
        len1 / sizeof( QChar ) );
    QString string2 = QString::fromRawData( reinterpret_cast<const QChar*>( data2 ),
        len2 / sizeof( QChar ) );

    return QString::localeAwareCompare( string1, string2 );
}

void DataManager::initDatabase()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

    QString path = getConfigDir(false);
    if (!QDir(path).exists()) {
        QDir(path).mkdir(path);
    }
    db.setDatabaseName(getDBFileName());

    db.open();

    QVariant handle = db.driver()->handle();
    sqlite3* db2 = *static_cast<sqlite3**>( handle.data() );
    if (db2) {
        qDebug() << "creating locale";
        sqlite3_create_collation16(db2, "MYCOLLATION", SQLITE_UTF16, nullptr, &localeCompare );
        qDebug() << "created locale";
    }

    db.exec("PRAGMA foreign_keys = ON");
}

Any idea?

Regards

[fcarney]

Because reasons:

    // init things
    sqlite3_initialize();

Edit:
I don't know what a collation is, but here is working code for talking to the installed version of sqlite3 to do memory copies:
https://forum.qt.io/topic/101018/qsqldatabase-sqlite-how-to-load-into-memory-and-save-memory-to-disk/17

[Kobid]

Thanks a lot! But problem was not only with missing sqlite3_initialize() . Saw this solutions in my google research but it didn't change anything. The main problem was with different sqlite version. In my linux system I have 3.22.0 but Qt 5.12.3 has sqlite version 3.26.0. I downloaded 3.26.0 version, compiled and included in my project's source:

win32:!win32-g++: PRE_TARGETDEPS += $$PWD/libs/sqlite.lib
else:unix|win32-g++: PRE_TARGETDEPS += $$PWD/libs/libsqlite3.a

After all, sqlite3_initialize() is necessary and also LIBS += -ldl, otherwise you get compiler error

BTW: I'm kinda newbie in c++, why libsqlite3.a has 8MB and libsqlite3.so has 2MB? I see that my executable file is bloated. When I try to include libsqlite3.so in my project with the same way as above then sqlite3_libversion() return 3.22 and still having same issue

[SGaist]

Hi,

libsqlite3.a is a static library hence it contains everything it needs.
Your application is loading the system libsqlite3 because it finds it before your own compiled version.

[Kobid]

Can I force compiler to load my own local libsqlite3.so?

[SGaist]

You can use the LD_LIBRARY_PATH environment variable containing the path of the folder containing your custom libsqlite3.so. Go to the Run part of the Project panel.

[Kobid]

Yes, but I guess it load it only when I run my app from Qt Creator. Can I force it permanently? I want to deploy my own libsqlite3.so with my app (in local "libs" dir) to make sure that my app use correct sqlite version. How can I force compiler / linker to looks for libs first in local ./libs?

[fcarney]

I think the way I did it was to load the version of sqlite that came with Qt. It is the single source file version as there is only the one c file. It might be easier to just include the source file rather than a separate lib. The license for sqlite3 is such that you don't have to do an "so" and still keep your source code under whatever license you want. At least that is how I understand it.

[Kobid]

Ok. Solved it and can use my own libsqlite.so v3.26 in my executable local "libs" dir:

unix:!macx{
  QMAKE_LFLAGS_RPATH=
  QMAKE_LFLAGS += "-Wl,-rpath,\'\$$ORIGIN/libs\'"
}

[Kobid]

Note also that on linux system you have to put also all symlinks points to libsqlite, the libsqlite.so alone is not enough and your app will still load system libsqlite, not your own. So in my case, in local libs I need to have:

libsqlite3.so
libsqlite3.so.0
libsqlite3.so.0.8.6

[Kobid]

Mmmm, but now having free() invalid pointer in my app when closing it. In debug mode call stack is:

sqlite3_free
sqlite3HashClear
sqlite3LeaveMutexAndCloseZombie
sqlite3Close
QSQLiteDriver::close()

In sqlite3_free it points on sqlite3GlobalConfig.m.xFree(p);:

SQLITE_API void sqlite3_free(void *p){
  if( p==0 ) return;  /* IMP: R-49053-54554 */
  assert( sqlite3MemdebugHasType(p, MEMTYPE_HEAP) );
  assert( sqlite3MemdebugNoType(p, (u8)~MEMTYPE_HEAP) );
  if( sqlite3GlobalConfig.bMemstat ){
    sqlite3_mutex_enter(mem0.mutex);
    sqlite3StatusDown(SQLITE_STATUS_MEMORY_USED, sqlite3MallocSize(p));
    sqlite3StatusDown(SQLITE_STATUS_MALLOC_COUNT, 1);
    sqlite3GlobalConfig.m.xFree(p);
    sqlite3_mutex_leave(mem0.mutex);
  }else{
    sqlite3GlobalConfig.m.xFree(p);
  }
}

I'm getting this no matter if I build app with DEFINES += SQLITE_THREADSAFE=0 or DEFINES += SQLITE_THREADSAFE=1. I think i have to ask this on sqlite forum but if someone have clue then it would be great. I think it has something related with necessary sqlite3_initialize();. Tried also call sqlite3_shutdown(); before closing my DB but it didn't help