diff --git a/sqliter/sqliter.cpp b/sqliter/sqliter.cpp --- a/sqliter/sqliter.cpp +++ b/sqliter/sqliter.cpp @@ -1,65 +1,87 @@ #include "sqliter.h" #include #include bool Sqliter::install(QVariant a_driverHandle) { if (!a_driverHandle.isValid() || qstrcmp(a_driverHandle.typeName(), "sqlite3*") != 0) return false; sqlite3* db = *static_cast(a_driverHandle.data()); if (!db) return false; - if (SQLITE_OK != sqlite3_create_function(db, "upper", 1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, + if (SQLITE_OK != sqlite3_create_function(db, "upper", 1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, Sqliter::upper, nullptr, nullptr)) return false; - if (SQLITE_OK != sqlite3_create_function(db, "lower", 1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, + if (SQLITE_OK != sqlite3_create_function(db, "lower", 1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, Sqliter::lower, nullptr, nullptr)) return false; + if (SQLITE_OK != sqlite3_create_collation(db, "nocase", SQLITE_UTF8, nullptr, Sqliter::compareNocase8)) + return false; + + if (SQLITE_OK != sqlite3_create_collation(db, "nocase", SQLITE_UTF16, nullptr, Sqliter::compareNocase16)) + return false; + return true; } -void Sqliter::upper(sqlite3_context* context, int argc, sqlite3_value** argv) +void Sqliter::upper(sqlite3_context* a_context, int a_argc, sqlite3_value** a_argv) { static QString result; - switch (sqlite3_value_type(argv[0])) + switch (sqlite3_value_type(a_argv[0])) { case SQLITE_TEXT: - result = QString::fromUtf16(static_cast(sqlite3_value_text16(argv[0]))).toUpper(); - sqlite3_result_text16(context, result.data(), -1, nullptr); + result = QString::fromUtf16(static_cast(sqlite3_value_text16(a_argv[0]))).toUpper(); + sqlite3_result_text16(a_context, result.data(), -1, nullptr); break; case SQLITE_NULL: - sqlite3_result_null(context); + sqlite3_result_null(a_context); break; default: - sqlite3_result_error_code(context, SQLITE_MISMATCH); + sqlite3_result_error_code(a_context, SQLITE_MISMATCH); } - Q_UNUSED(argc); + Q_UNUSED(a_argc); } -void Sqliter::lower(sqlite3_context* context, int argc, sqlite3_value** argv) +void Sqliter::lower(sqlite3_context* a_context, int a_argc, sqlite3_value** a_argv) { static QString result; - switch (sqlite3_value_type(argv[0])) + switch (sqlite3_value_type(a_argv[0])) { case SQLITE_TEXT: - result = QString::fromUtf16(static_cast(sqlite3_value_text16(argv[0]))).toLower(); - sqlite3_result_text16(context, result.data(), -1, nullptr); + result = QString::fromUtf16(static_cast(sqlite3_value_text16(a_argv[0]))).toLower(); + sqlite3_result_text16(a_context, result.data(), -1, nullptr); break; case SQLITE_NULL: - sqlite3_result_null(context); + sqlite3_result_null(a_context); break; default: - sqlite3_result_error_code(context, SQLITE_MISMATCH); + sqlite3_result_error_code(a_context, SQLITE_MISMATCH); } - Q_UNUSED(argc); + Q_UNUSED(a_argc); } + +int Sqliter::compareNocase8(void* a_pArg, int a_size1, const void* a_data1, int a_size2, const void* a_data2) +{ + QString s1 = QString::fromUtf8(static_cast(a_data1), a_size1); + QString s2 = QString::fromUtf8(static_cast(a_data2), a_size2); + return s1.compare(s2, Qt::CaseInsensitive); + Q_UNUSED(a_pArg); +} + +int Sqliter::compareNocase16(void* a_pArg, int a_size1, const void* a_data1, int a_size2, const void* a_data2) +{ + QString s1 = QString::fromRawData(static_cast(a_data1), a_size1 / sizeof(QChar)); + QString s2 = QString::fromRawData(static_cast(a_data2), a_size2 / sizeof(QChar)); + return s1.compare(s2, Qt::CaseInsensitive); + Q_UNUSED(a_pArg); +} diff --git a/sqliter/sqliter.h b/sqliter/sqliter.h --- a/sqliter/sqliter.h +++ b/sqliter/sqliter.h @@ -1,21 +1,29 @@ #ifndef SQLITER_H #define SQLITER_H -#include "sqliter_global.h" +#include -#include +#if defined (SQLITER_STATIC) +# define SQLITER_EXPORT +#elif defined(SQLITER_LIBRARY) +# define SQLITERSHARED_EXPORT Q_DECL_EXPORT +#else +# define SQLITERSHARED_EXPORT Q_DECL_IMPORT +#endif struct sqlite3_context; struct sqlite3_value; class SQLITERSHARED_EXPORT Sqliter { public: static bool install(QVariant a_driverHandle); private: Sqliter() = default; static void upper(sqlite3_context* a_context, int a_argc, sqlite3_value** a_argv); static void lower(sqlite3_context* a_context, int a_argc, sqlite3_value** a_argv); + static int compareNocase8(void* a_pArg, int a_size1, const void* a_data1, int a_size2, const void* a_data2); + static int compareNocase16(void* a_pArg, int a_size1, const void* a_data1, int a_size2, const void* a_data2); }; #endif // SQLITER_H diff --git a/sqliter/sqliter.pro b/sqliter/sqliter.pro --- a/sqliter/sqliter.pro +++ b/sqliter/sqliter.pro @@ -1,37 +1,37 @@ #------------------------------------------------- # # Project created by QtCreator 2018-03-22T16:38:23 # #------------------------------------------------- QT -= gui TARGET = sqliter TEMPLATE = lib DEFINES += SQLITER_LIBRARY +CONFIG(staticlib): DEFINES += SQLITER_STATIC # The following define makes your compiler emit warnings if you use # any feature of Qt which has been marked as deprecated (the exact warnings # depend on your compiler). Please consult the documentation of the # deprecated API in order to know how to port your code away from it. DEFINES += QT_DEPRECATED_WARNINGS # You can also make your code fail to compile if you use deprecated APIs. # In order to do so, uncomment the following line. # You can also select to disable deprecated APIs only up to a certain version of Qt. #DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000 # disables all the APIs deprecated before Qt 6.0.0 LIBS += -lsqlite3 SOURCES += \ sqliter.cpp HEADERS += \ - sqliter.h \ - sqliter_global.h + sqliter.h unix { target.path = /usr/lib INSTALLS += target } diff --git a/sqliter/sqliter_global.h b/sqliter/sqliter_global.h deleted file mode 100644 --- a/sqliter/sqliter_global.h +++ /dev/null @@ -1,12 +0,0 @@ -#ifndef SQLITER_GLOBAL_H -#define SQLITER_GLOBAL_H - -#include - -#if defined(SQLITER_LIBRARY) -# define SQLITERSHARED_EXPORT Q_DECL_EXPORT -#else -# define SQLITERSHARED_EXPORT Q_DECL_IMPORT -#endif - -#endif // SQLITER_GLOBAL_H diff --git a/tests/sqrtest.cpp b/tests/sqrtest.cpp --- a/tests/sqrtest.cpp +++ b/tests/sqrtest.cpp @@ -1,127 +1,263 @@ #include #include #include #include #include #include #include "sqliter.h" class SqliterTest : public QObject { Q_OBJECT public: - SqliterTest() = default; + SqliterTest(const QString& a_encoding) + : encoding(a_encoding) + { + } private Q_SLOTS: void initTestCase(); + void cleanupTestCase(); void testUpperDefault(); void testLowerDefault(); + void testOrderDefault(); + void testOrderDefaultNocase(); void installSqliter(); void testUpperR(); void testLowerR(); + void testOrderR(); + void testOrderRNocase(); + +protected: + QString encoding; +}; + +class SqliterTest8 : public SqliterTest +{ + Q_OBJECT +public: + SqliterTest8() : SqliterTest(QStringLiteral("UTF-8")) {} +}; + +class SqliterTest16 : public SqliterTest +{ + Q_OBJECT +public: + SqliterTest16() : SqliterTest(QStringLiteral("UTF-16")) {} }; void SqliterTest::initTestCase() { QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); db.setDatabaseName(":memory:"); QVERIFY(db.open()); QSqlQuery q; + q.exec(QStringLiteral(R"(PRAGMA encoding = "%1")").arg(encoding)); + QVERIFY(db.transaction()); + QVERIFY(q.exec("CREATE TABLE tt1 (id INTEGER PRIMARY KEY, t TEXT)")); - QVERIFY(db.transaction()); QVERIFY(q.exec(QStringLiteral("INSERT INTO tt1 (id, t) VALUES (0, NULL)"))); QVERIFY(q.exec(QStringLiteral("INSERT INTO tt1 (id, t) VALUES (1, 'One')"))); QVERIFY(q.exec(QStringLiteral("INSERT INTO tt1 (id, t) VALUES (2, 'two')"))); QVERIFY(q.exec(QStringLiteral("INSERT INTO tt1 (id, t) VALUES (3, 'THREE')"))); QVERIFY(q.exec(QStringLiteral("INSERT INTO tt1 (id, t) VALUES (4, 'Четыре')"))); QVERIFY(q.exec(QStringLiteral("INSERT INTO tt1 (id, t) VALUES (5, 'пять')"))); QVERIFY(q.exec(QStringLiteral("INSERT INTO tt1 (id, t) VALUES (6, 'ШЕСТЬ')"))); + + QVERIFY(q.exec("CREATE TABLE tt2eng (t TEXT)")); + QVERIFY(q.exec(QStringLiteral("INSERT INTO tt2eng VALUES ('TREE')"))); + QVERIFY(q.exec(QStringLiteral("INSERT INTO tt2eng VALUES ('abs')"))); + QVERIFY(q.exec(QStringLiteral("INSERT INTO tt2eng VALUES ('Optimal')"))); + QVERIFY(q.exec("CREATE TABLE tt2rus (t TEXT)")); + QVERIFY(q.exec(QStringLiteral("INSERT INTO tt2rus VALUES ('пять')"))); + QVERIFY(q.exec(QStringLiteral("INSERT INTO tt2rus VALUES ('ШЕСТЬ')"))); + QVERIFY(db.commit()); } +void SqliterTest::cleanupTestCase() +{ + QString connName; + { + QSqlDatabase db = QSqlDatabase::database(); + connName = db.connectionName(); + } + QSqlDatabase::removeDatabase(connName); +} + void SqliterTest::testUpperDefault() { QStringList list; QSqlQuery q; QVERIFY(q.exec("SELECT UPPER(t) FROM tt1 ORDER BY id")); q.next(); QVERIFY(q.value(0).isNull()); while (q.next()) list.append(q.value(0).toString()); QVERIFY(list[0] == "ONE"); QVERIFY(list[1] == "TWO"); QVERIFY(list[2] == "THREE"); QVERIFY(list[3] == "Четыре"); QVERIFY(list[4] == "пять"); QVERIFY(list[5] == "ШЕСТЬ"); } void SqliterTest::testLowerDefault() { QStringList list; QSqlQuery q; QVERIFY(q.exec("SELECT LOWER(t) FROM tt1 ORDER BY id")); q.next(); QVERIFY(q.value(0).isNull()); while (q.next()) list.append(q.value(0).toString()); QVERIFY(list[0] == "one"); QVERIFY(list[1] == "two"); QVERIFY(list[2] == "three"); QVERIFY(list[3] == "Четыре"); QVERIFY(list[4] == "пять"); QVERIFY(list[5] == "ШЕСТЬ"); } +void SqliterTest::testOrderDefault() +{ + QStringList list; + QSqlQuery q; + QVERIFY(q.exec("SELECT t FROM tt2eng ORDER BY t")); + + while (q.next()) + list.append(q.value(0).toString()); + + QVERIFY(q.exec("SELECT t FROM tt2rus ORDER BY t")); + + while (q.next()) + list.append(q.value(0).toString()); + + QVERIFY(list[0] == "Optimal"); + QVERIFY(list[1] == "TREE"); + QVERIFY(list[2] == "abs"); + QVERIFY(list[3] == "ШЕСТЬ"); + QVERIFY(list[4] == "пять"); +} + +void SqliterTest::testOrderDefaultNocase() +{ + QStringList list; + QSqlQuery q; + QVERIFY(q.exec("SELECT t FROM tt2eng ORDER BY t COLLATE NOCASE")); + + while (q.next()) + list.append(q.value(0).toString()); + + QVERIFY(q.exec("SELECT t FROM tt2rus ORDER BY t COLLATE NOCASE")); + + while (q.next()) + list.append(q.value(0).toString()); + QVERIFY(list[0] == "abs"); + QVERIFY(list[1] == "Optimal"); + QVERIFY(list[2] == "TREE"); + QVERIFY(list[3] == "ШЕСТЬ"); + QVERIFY(list[4] == "пять"); +} + void SqliterTest::installSqliter() { QVERIFY(Sqliter::install(QSqlDatabase::database().driver()->handle())); } void SqliterTest::testUpperR() { QStringList list; QSqlQuery q; QVERIFY(q.exec("SELECT UPPER(t) FROM tt1 ORDER BY id")); q.next(); QVERIFY(q.value(0).isNull()); while (q.next()) list.append(q.value(0).toString()); QVERIFY(list[0] == "ONE"); QVERIFY(list[1] == "TWO"); QVERIFY(list[2] == "THREE"); QVERIFY(list[3] == "ЧЕТЫРЕ"); QVERIFY(list[4] == "ПЯТЬ"); QVERIFY(list[5] == "ШЕСТЬ"); } void SqliterTest::testLowerR() { QStringList list; QSqlQuery q; QVERIFY(q.exec("SELECT LOWER(t) FROM tt1 ORDER BY id")); q.next(); QVERIFY(q.value(0).isNull()); while (q.next()) list.append(q.value(0).toString()); QVERIFY(list[0] == "one"); QVERIFY(list[1] == "two"); QVERIFY(list[2] == "three"); QVERIFY(list[3] == "четыре"); QVERIFY(list[4] == "пять"); QVERIFY(list[5] == "шесть"); } -QTEST_APPLESS_MAIN(SqliterTest) +void SqliterTest::testOrderR() +{ + QStringList list; + QSqlQuery q; + QVERIFY(q.exec("SELECT t FROM tt2eng ORDER BY t")); + + while (q.next()) + list.append(q.value(0).toString()); + + QVERIFY(q.exec("SELECT t FROM tt2rus ORDER BY t")); + + while (q.next()) + list.append(q.value(0).toString()); + QVERIFY(list[0] == "Optimal"); + QVERIFY(list[1] == "TREE"); + QVERIFY(list[2] == "abs"); + QVERIFY(list[3] == "ШЕСТЬ"); + QVERIFY(list[4] == "пять"); +} + +void SqliterTest::testOrderRNocase() +{ + QStringList list; + QSqlQuery q; + QVERIFY(q.exec("SELECT t FROM tt2eng ORDER BY t COLLATE NOCASE")); + + while (q.next()) + list.append(q.value(0).toString()); + + QVERIFY(q.exec("SELECT t FROM tt2rus ORDER BY t COLLATE NOCASE")); + + while (q.next()) + list.append(q.value(0).toString()); + QVERIFY(list[0] == "abs"); + QVERIFY(list[1] == "Optimal"); + QVERIFY(list[2] == "TREE"); + QVERIFY(list[3] == "пять"); + QVERIFY(list[4] == "ШЕСТЬ"); +} + +int main(int argc, char *argv[]) +{ + SqliterTest8 tc8; + QTEST_SET_MAIN_SOURCE_PATH + int rc = QTest::qExec(&tc8, argc, argv); + if (rc != 0) + return rc; + SqliterTest16 tc16; + return QTest::qExec(&tc16, argc, argv); +} #include "sqrtest.moc"