diff --git a/sqliter/sqliter.cpp b/sqliter/sqliter.cpp --- a/sqliter/sqliter.cpp +++ b/sqliter/sqliter.cpp @@ -1,97 +1,243 @@ #include "sqliter.h" #include #include +/* +** Implementation of the like() SQL function. This function implements +** the build-in LIKE operator. The first argument to the function is the +** pattern and the second argument is the string. So, the SQL statements: +** +** A LIKE B +** +** is implemented as like(B,A). +** +** This same function (with a different compareInfo structure) computes +** the GLOB operator. +*/ +static void like(sqlite3_context* a_context, int a_argc, sqlite3_value** a_argv) +{ + if (sqlite3_value_type(a_argv[0]) == SQLITE_BLOB || sqlite3_value_type(a_argv[1]) == SQLITE_BLOB) + { + sqlite3_result_int(a_context, 0); + return; + } + + int size = sqlite3_value_bytes16(a_argv[0]) / sizeof(ushort); + const QString pattern = QString::fromRawData(static_cast(sqlite3_value_text16(a_argv[0])), size); + size = sqlite3_value_bytes16(a_argv[1]) / sizeof(ushort); + const QString text = QString::fromRawData(static_cast(sqlite3_value_text16(a_argv[1])), size); + + QChar escape; + if (a_argc == 3) + { + // The escape character string must consist of a single UTF-8 character. + // Otherwise, return an error. + const QString escStr = QString::fromUtf8(reinterpret_cast(sqlite3_value_text(a_argv[2]))); + if (escStr.isEmpty()) + return; + if (escStr.size() != 1) + { + sqlite3_result_error(a_context, "ESCAPE expression must be a single character", -1); + return; + } + escape = escStr.at(0); + } + + auto pp = pattern.cbegin(); + auto tp = text.cbegin(); + auto tanchor = tp; + auto panchor = pp; + const auto pend = pattern.cend(); + const auto tend = text.cend(); + bool fixedTextAnchor = true; + while (pp != pend) + { + //запоминаем символ обраца и переходим к следующему + QChar c = *pp; + if (c == QLatin1Char('%')) + { //ноль или более символов + fixedTextAnchor = false; + panchor = ++pp; + tanchor = tp; + } + //если в образце есть обязательный символ, а текст уже кончился + else if (tp == tend) + break; + else + { //литерал в образце + //escape, следующий символ образца рассматривать как литерал + bool escaped = c == escape; + if (escaped) + { + //если последний символ образца, то это неправильно, не совпадает + if (++pp == pend) + break; + c = *pp; + } + bool hasMatch = (c == *tp || c.toCaseFolded() == tp->toCaseFolded() || (!escaped && c == QLatin1Char('_'))); + ++tp; + ++pp; + if (!hasMatch || (pp == pend && tp != tend)) + { //не совпадает или совпадает не в том месте (привязка совпадения к концу строки) + //откат к якорю в образце, откатываем обязательно, чтобы сработало условие вычисления возвращаемого значения + pp = panchor; + //если нельзя переместить якорь в тексте, то дальше искать совпадения не надо + if (fixedTextAnchor) + break; + //откатимся к якорю и повторим поиск со следующей позиции в тексте + tp = ++tanchor; + } + } + } + sqlite3_result_int(a_context, (pp == pend) ? 1 : 0); +} + void upper(sqlite3_context* a_context, int a_argc, sqlite3_value** a_argv) { - static QString result; switch (sqlite3_value_type(a_argv[0])) { case SQLITE_TEXT: { - const ushort* unicode = static_cast(sqlite3_value_text16(a_argv[0])); - int size = 0; - while (unicode[size]) - ++size; - result = QString::fromRawData(static_cast(sqlite3_value_text16(a_argv[0])), size).toUpper(); - sqlite3_result_text16(a_context, result.data(), -1, nullptr); + int size = sqlite3_value_bytes16(a_argv[0]) / sizeof(ushort); + QString result = QString::fromRawData(static_cast(sqlite3_value_text16(a_argv[0])), size); + sqlite3_result_text16(a_context, result.toUpper().constData(), -1, SQLITE_TRANSIENT); break; } case SQLITE_NULL: sqlite3_result_null(a_context); break; default: sqlite3_result_error_code(a_context, SQLITE_MISMATCH); } Q_UNUSED(a_argc); } void lower(sqlite3_context* a_context, int a_argc, sqlite3_value** a_argv) { - static QString result; switch (sqlite3_value_type(a_argv[0])) { case SQLITE_TEXT: { - const ushort* unicode = static_cast(sqlite3_value_text16(a_argv[0])); - int size = 0; - while (unicode[size]) - ++size; - result = QString::fromRawData(static_cast(sqlite3_value_text16(a_argv[0])), size).toLower(); - sqlite3_result_text16(a_context, result.data(), -1, nullptr); + int size = sqlite3_value_bytes16(a_argv[0]) / sizeof(ushort); + QString result = QString::fromRawData(static_cast(sqlite3_value_text16(a_argv[0])), size); + sqlite3_result_text16(a_context, result.toLower().constData(), -1, SQLITE_TRANSIENT); break; } case SQLITE_NULL: sqlite3_result_null(a_context); break; default: sqlite3_result_error_code(a_context, SQLITE_MISMATCH); } Q_UNUSED(a_argc); } int compareNocase8(void* a_pArg, int a_size1, const void* a_data1, int a_size2, const void* a_data2) { return QString::compare(QString::fromUtf8(static_cast(a_data1), a_size1), QString::fromUtf8(static_cast(a_data2), a_size2), Qt::CaseInsensitive); Q_UNUSED(a_pArg); } int compareNocase16(void* a_pArg, int a_size1, const void* a_data1, int a_size2, const void* a_data2) { return QString::compare(QString::fromRawData(static_cast(a_data1), a_size1 / sizeof(QChar)), QString::fromRawData(static_cast(a_data2), a_size2 / sizeof(QChar)), Qt::CaseInsensitive); Q_UNUSED(a_pArg); } +void destroyString(void* a_pArg) +{ + delete static_cast(a_pArg); +} + +int match(const QString& pattern, const QString& text, const QChar escape = QChar()) +{ + auto pp = pattern.cbegin(); + auto tp = text.cbegin(); + auto tanchor = tp; + auto panchor = pp; + const auto pend = pattern.cend(); + const auto tend = text.cend(); + bool fixedTextAnchor = true; + while (pp != pend) + { + //запоминаем символ обраца и переходим к следующему + QChar c = *pp; + if (c == QLatin1Char('%')) + { //ноль или более символов + fixedTextAnchor = false; + panchor = ++pp; + tanchor = tp; + } + //если в образце есть обязательный символ, а текст уже кончился + else if (tp == tend) + break; + else + { //литерал в образце + //escape, следующий символ образца рассматривать как литерал + bool escaped = c == escape; + if (escaped) + { + //если последний символ образца, то это неправильно, не совпадает + if (++pp == pend) + break; + c = *pp; + } + bool hasMatch = (c == *tp || c.toCaseFolded() == tp->toCaseFolded() || (!escaped && c == QLatin1Char('_'))); + ++tp; + ++pp; + if (!hasMatch || (pp == pend && tp != tend)) + { //не совпадает или совпадает не в том месте (привязка совпадения к концу строки) + //откат к якорю в образце, откатываем обязательно, чтобы сработало условие вычисления возвращаемого значения + pp = panchor; + //если нельзя переместить якорь в тексте, то дальше искать совпадения не надо + if (fixedTextAnchor) + break; + //откатимся к якорю и повторим поиск со следующей позиции в тексте + tp = ++tanchor; + } + } + } + return (pp == pend) ? 1 : 0; +} + 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, nullptr, upper, nullptr, nullptr)) return false; if (SQLITE_OK != sqlite3_create_function(db, "lower", 1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, lower, nullptr, nullptr)) return false; if (SQLITE_OK != sqlite3_create_collation(db, "nocase", SQLITE_UTF8, nullptr, compareNocase8)) return false; if (SQLITE_OK != sqlite3_create_collation(db, "nocase", SQLITE_UTF16, nullptr, compareNocase16)) return false; + if (SQLITE_OK != sqlite3_create_function(db, "like", 2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, + nullptr, like, nullptr, nullptr)) + return false; + + if (SQLITE_OK != sqlite3_create_function(db, "like", 3, SQLITE_UTF16 | SQLITE_DETERMINISTIC, + nullptr, like, nullptr, nullptr)) + return false; + return true; } diff --git a/sqliter/sqliter.pro b/sqliter/sqliter.pro --- a/sqliter/sqliter.pro +++ b/sqliter/sqliter.pro @@ -1,44 +1,44 @@ - #------------------------------------------------- +#------------------------------------------------- # # 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 SOURCES += sqliter.cpp HEADERS += sqliter.h unix { LIBS += -lsqlite3 } win32 { INCLUDEPATH += sqlite DEFINES += SQLITE_THREADSAFE=0 HEADERS += sqlite/sqlite3.h SOURCES += sqlite/sqlite3.c } target.path = /lib headers.path = /include headers.files = $$HEADERS INSTALLS += target headers diff --git a/tests/liketest.cpp b/tests/liketest.cpp new file mode 100644 --- /dev/null +++ b/tests/liketest.cpp @@ -0,0 +1,749 @@ +#include +#include +#include +#include +#include +#include +#include + +#include "sqliter.h" +#include "liketest.h" + +// Тесты перенесены из исходников sqlite 3.22, файл like.test,v 1.13 2009/06/07 + +QStringList select(const QString& a_query) +{ + QStringList result; + + QSqlQuery q(a_query); + int nf = q.record().count(); + while (q.next()) + for (int i = 0; i < nf; ++i) + result.push_back(q.value(i).toString()); + + return result; +} + +std::vector selectInt(const QString& a_query) +{ + std::vector result; + + QSqlQuery q(a_query); + while (q.next()) + result.emplace_back(q.value(0).toInt()); + + return result; +} + +void LikeTest::initTestCase() +{ + QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); + db.setDatabaseName(":memory:"); + QVERIFY(db.open()); + QSqlQuery q; + q.exec(R"(PRAGMA encoding = "UTF-8")"); + QVERIFY(db.transaction()); + + QStringList str {"a", "ab", "abc", "abcd", "acd", "abd", "bc", "bcd", "xyz", "ABC", "CDE", "ABC abc xyz"}; + QVERIFY(q.exec("CREATE TABLE t1(x TEXT)")); + + for (const auto& s : str) + QVERIFY(q.exec(QStringLiteral("INSERT INTO t1 VALUES ('%1')").arg(s))); + + QVERIFY(db.commit()); + + QVERIFY(q.exec("SELECT count(*) FROM t1") && q.next() && q.value(0).toInt() == 12); +} + +void LikeTest::cleanupTestCase() +{ + QString connName; + { + QSqlDatabase db = QSqlDatabase::database(); + connName = db.connectionName(); + } + QSqlDatabase::removeDatabase(connName); +} + +//Test that both case sensitive and insensitive version of LIKE work. +// +void LikeTest::like1_1() +{ + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1") == QStringList({"ABC", "abc"})); +} + +void LikeTest::like1_2() +{ + QVERIFY(select("SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1") == QStringList({"abc"})); +} + +void LikeTest::like1_3() +{ + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1") == QStringList({"ABC", "abc"})); +} + +void LikeTest::like1_4() +{ + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1") == QStringList({"ABC", "abc"})); +} + +void LikeTest::like1_5_1() +{ + QSqlQuery q; + QVERIFY(q.exec("PRAGMA case_sensitive_like=on;")); +} + +void LikeTest::like1_5_2() +{ + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1") == QStringList({"abc"})); +} + +void LikeTest::like1_5_3() +{ + QSqlQuery q; + QVERIFY(q.exec("PRAGMA case_sensitive_like")); //-- no argument; does not change setting + QVERIFY(select("SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1") == QStringList({"abc"})); +} + +void LikeTest::like1_6() +{ + QVERIFY(select("SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1") == QStringList({"abc"})); +} + +void LikeTest::like1_7() +{ + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1") == QStringList({"ABC"})); +} + +void LikeTest::like1_8() +{ + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1").size() == 0); +} + +void LikeTest::like1_9() +{ + QSqlQuery q; + QVERIFY(q.exec("PRAGMA case_sensitive_like=off")); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1") == QStringList({"ABC", "abc"})); +} + +void LikeTest::like1_10() +{ + QSqlQuery q; + QVERIFY(q.exec("PRAGMA case_sensitive_like")); //-- no argument; does not change setting + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1") == QStringList({"ABC", "abc"})); +} + + +void LikeTest2::initTestCase() +{ + QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); + db.setDatabaseName(":memory:"); + QVERIFY(db.open()); + QSqlQuery q; + q.exec(R"(PRAGMA encoding = "UTF-8")"); + QVERIFY(db.transaction()); + + QVERIFY(q.exec("CREATE TABLE t1(x INT, y COLLATE NOCASE)")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(1,CAST(x'01' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(2,CAST(x'02' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(3,CAST(x'03' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(4,CAST(x'04' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(5,CAST(x'05' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(6,CAST(x'06' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(7,CAST(x'07' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(8,CAST(x'08' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(9,CAST(x'09' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(10,CAST(x'0a' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(11,CAST(x'0b' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(12,CAST(x'0c' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(13,CAST(x'0d' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(14,CAST(x'0e' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(15,CAST(x'0f' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(16,CAST(x'10' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(17,CAST(x'11' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(18,CAST(x'12' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(19,CAST(x'13' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(20,CAST(x'14' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(21,CAST(x'15' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(22,CAST(x'16' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(23,CAST(x'17' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(24,CAST(x'18' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(25,CAST(x'19' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(26,CAST(x'1a' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(27,CAST(x'1b' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(28,CAST(x'1c' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(29,CAST(x'1d' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(30,CAST(x'1e' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(31,CAST(x'1f' AS TEXT))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(32,' ')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(33,'!')")); + QVERIFY(q.exec(R"(INSERT INTO t1(x,y) VALUES(34,'"'))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(35,'#')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(36,'$')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(37,'%')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(38,'&')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(39,'''')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(40,'(')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(41,')')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(42,'*')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(43,'+')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(44,',')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(45,'-')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(46,'.')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(47,'/')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(48,'0')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(49,'1')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(50,'2')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(51,'3')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(52,'4')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(53,'5')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(54,'6')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(55,'7')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(56,'8')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(57,'9')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(58,':')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(59,';')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(60,'<')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(61,'=')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(62,'>')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(63,'?')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(64,'@')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(65,'A')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(66,'B')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(67,'C')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(68,'D')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(69,'E')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(70,'F')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(71,'G')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(72,'H')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(73,'I')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(74,'J')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(75,'K')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(76,'L')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(77,'M')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(78,'N')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(79,'O')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(80,'P')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(81,'Q')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(82,'R')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(83,'S')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(84,'T')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(85,'U')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(86,'V')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(87,'W')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(88,'X')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(89,'Y')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(90,'Z')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(91,'[')")); + QVERIFY(q.exec(R"(INSERT INTO t1(x,y) VALUES(92,'\'))")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(93,']')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(94,'^')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(95,'_')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(96,'`')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(97,'a')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(98,'b')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(99,'c')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(100,'d')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(101,'e')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(102,'f')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(103,'g')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(104,'h')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(105,'i')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(106,'j')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(107,'k')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(108,'l')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(109,'m')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(110,'n')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(111,'o')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(112,'p')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(113,'q')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(114,'r')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(115,'s')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(116,'t')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(117,'u')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(118,'v')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(119,'w')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(120,'x')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(121,'y')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(122,'z')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(123,'{')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(124,'|')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(125,'}')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(126,'~')")); + QVERIFY(q.exec("INSERT INTO t1(x,y) VALUES(127,CAST(x'7f' AS TEXT))")); + + QVERIFY(db.commit()); + + QVERIFY(q.exec("SELECT count(*) FROM t1") && q.next() && q.value(0).toInt() == 127); +} + +void LikeTest2::cleanupTestCase() +{ + QString connName; + { + QSqlDatabase db = QSqlDatabase::database(); + connName = db.connectionName(); + } + QSqlDatabase::removeDatabase(connName); +} + +void LikeTest2::installSqliter() +{ + QVERIFY(Sqliter::install(QSqlDatabase::database().driver()->handle())); +} + +void LikeTest2::like1_2() +{ + QSqlQuery q; + QVERIFY(q.exec("CREATE TABLE t2(x INT, y COLLATE NOCASE)")); + QVERIFY(q.exec("INSERT INTO t2 SELECT * FROM t1")); + QVERIFY(q.exec("CREATE INDEX i2 ON t2(y)")); + QVERIFY(q.exec("SELECT count(*) FROM t2") && q.next() && q.value(0).toInt() == 127); +} + +void LikeTest2::like1_3() +{ + QSqlQuery q; + QVERIFY(q.exec("CREATE TABLE t3(x INT, y COLLATE NOCASE)")); + QVERIFY(q.exec("INSERT INTO t3 SELECT x, 'abc' || y || 'xyz' FROM t1")); + QVERIFY(q.exec("CREATE INDEX i3 ON t3(y)")); + QVERIFY(q.exec("SELECT count(*) FROM t3") && q.next() && q.value(0).toInt() == 127); +} + +void LikeTest2::like2() +{ + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE ' %'") == std::vector{32}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE ' %'") == std::vector{32}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc %'") == std::vector{32}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '!%'") == std::vector{33}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '!%'") == std::vector{33}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc!%'") == std::vector{33}); + QVERIFY(selectInt(R"(SELECT x FROM t1 WHERE y LIKE '"%')") == std::vector{34}); + QVERIFY(selectInt(R"(SELECT x FROM t2 WHERE y LIKE '"%')") == std::vector{34}); + QVERIFY(selectInt(R"(SELECT x FROM t3 WHERE y LIKE 'abc"%')") == std::vector{34}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '#%'") == std::vector{35}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '#%'") == std::vector{35}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc#%'") == std::vector{35}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '$%'") == std::vector{36}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '$%'") == std::vector{36}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc$%'") == std::vector{36}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '&%'") == std::vector{38}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '&%'") == std::vector{38}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc&%'") == std::vector{38}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '''%'") == std::vector{39}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '''%'") == std::vector{39}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc''%'") == std::vector{39}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '(%'") == std::vector{40}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '(%'") == std::vector{40}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc(%'") == std::vector{40}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE ')%'") == std::vector{41}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE ')%'") == std::vector{41}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc)%'") == std::vector{41}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '*%'") == std::vector{42}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '*%'") == std::vector{42}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc*%'") == std::vector{42}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '+%'") == std::vector{43}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '+%'") == std::vector{43}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc+%'") == std::vector{43}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE ',%'") == std::vector{44}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE ',%'") == std::vector{44}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc,%'") == std::vector{44}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '-%'") == std::vector{45}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '-%'") == std::vector{45}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc-%'") == std::vector{45}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '.%'") == std::vector{46}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '.%'") == std::vector{46}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc.%'") == std::vector{46}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '/%'") == std::vector{47}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '/%'") == std::vector{47}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc/%'") == std::vector{47}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '0%'") == std::vector{48}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '0%'") == std::vector{48}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc0%'") == std::vector{48}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '1%'") == std::vector{49}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '1%'") == std::vector{49}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc1%'") == std::vector{49}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '2%'") == std::vector{50}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '2%'") == std::vector{50}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc2%'") == std::vector{50}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '3%'") == std::vector{51}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '3%'") == std::vector{51}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc3%'") == std::vector{51}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '4%'") == std::vector{52}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '4%'") == std::vector{52}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc4%'") == std::vector{52}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '5%'") == std::vector{53}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '5%'") == std::vector{53}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc5%'") == std::vector{53}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '6%'") == std::vector{54}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '6%'") == std::vector{54}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc6%'") == std::vector{54}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '7%'") == std::vector{55}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '7%'") == std::vector{55}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc7%'") == std::vector{55}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '8%'") == std::vector{56}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '8%'") == std::vector{56}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc8%'") == std::vector{56}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '9%'") == std::vector{57}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '9%'") == std::vector{57}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc9%'") == std::vector{57}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE ':%'") == std::vector{58}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE ':%'") == std::vector{58}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc:%'") == std::vector{58}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE ';%'") == std::vector{59}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE ';%'") == std::vector{59}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc;%'") == std::vector{59}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '<%'") == std::vector{60}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '<%'") == std::vector{60}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc<%'") == std::vector{60}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '=%'") == std::vector{61}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '=%'") == std::vector{61}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc=%'") == std::vector{61}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '>%'") == std::vector{62}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '>%'") == std::vector{62}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc>%'") == std::vector{62}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '?%'") == std::vector{63}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '?%'") == std::vector{63}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc?%'") == std::vector{63}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '@%'") == std::vector{64}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '@%'") == std::vector{64}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc@%'") == std::vector{64}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'A%'") == std::vector({65,97})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'A%'") == std::vector({65,97})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcA%'") == std::vector({65,97})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'B%'") == std::vector({66,98})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'B%'") == std::vector({66,98})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcB%'") == std::vector({66,98})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'C%'") == std::vector({67,99})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'C%'") == std::vector({67,99})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcC%'") == std::vector({67,99})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'D%'") == std::vector({68,100})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'D%'") == std::vector({68,100})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcD%'") == std::vector({68,100})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'E%'") == std::vector({69,101})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'E%'") == std::vector({69,101})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcE%'") == std::vector({69,101})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'F%'") == std::vector({70,102})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'F%'") == std::vector({70,102})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcF%'") == std::vector({70,102})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'G%'") == std::vector({71,103})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'G%'") == std::vector({71,103})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcG%'") == std::vector({71,103})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'H%'") == std::vector({72,104})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'H%'") == std::vector({72,104})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcH%'") == std::vector({72,104})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'I%'") == std::vector({73,105})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'I%'") == std::vector({73,105})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcI%'") == std::vector({73,105})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'J%'") == std::vector({74,106})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'J%'") == std::vector({74,106})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcJ%'") == std::vector({74,106})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'K%'") == std::vector({75,107})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'K%'") == std::vector({75,107})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcK%'") == std::vector({75,107})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'L%'") == std::vector({76,108})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'L%'") == std::vector({76,108})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcL%'") == std::vector({76,108})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'M%'") == std::vector({77,109})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'M%'") == std::vector({77,109})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcM%'") == std::vector({77,109})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'N%'") == std::vector({78,110})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'N%'") == std::vector({78,110})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcN%'") == std::vector({78,110})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'O%'") == std::vector({79,111})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'O%'") == std::vector({79,111})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcO%'") == std::vector({79,111})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'P%'") == std::vector({80,112})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'P%'") == std::vector({80,112})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcP%'") == std::vector({80,112})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'Q%'") == std::vector({81,113})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'Q%'") == std::vector({81,113})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcQ%'") == std::vector({81,113})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'R%'") == std::vector({82,114})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'R%'") == std::vector({82,114})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcR%'") == std::vector({82,114})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'S%'") == std::vector({83,115})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'S%'") == std::vector({83,115})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcS%'") == std::vector({83,115})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'T%'") == std::vector({84,116})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'T%'") == std::vector({84,116})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcT%'") == std::vector({84,116})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'U%'") == std::vector({85,117})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'U%'") == std::vector({85,117})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcU%'") == std::vector({85,117})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'V%'") == std::vector({86,118})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'V%'") == std::vector({86,118})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcV%'") == std::vector({86,118})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'W%'") == std::vector({87,119})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'W%'") == std::vector({87,119})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcW%'") == std::vector({87,119})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'X%'") == std::vector({88,120})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'X%'") == std::vector({88,120})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcX%'") == std::vector({88,120})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'Y%'") == std::vector({89,121})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'Y%'") == std::vector({89,121})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcY%'") == std::vector({89,121})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'Z%'") == std::vector({90,122})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'Z%'") == std::vector({90,122})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcZ%'") == std::vector({90,122})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '[%'") == std::vector{91}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '[%'") == std::vector{91}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc[%'") == std::vector{91}); + QVERIFY(selectInt(R"(SELECT x FROM t1 WHERE y LIKE '\%')") == std::vector{92}); + QVERIFY(selectInt(R"(SELECT x FROM t2 WHERE y LIKE '\%')") == std::vector{92}); + QVERIFY(selectInt(R"(SELECT x FROM t3 WHERE y LIKE 'abc\%')") == std::vector{92}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE ']%'") == std::vector{93}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE ']%'") == std::vector{93}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc]%'") == std::vector{93}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '^%'") == std::vector{94}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '^%'") == std::vector{94}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc^%'") == std::vector{94}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '`%'") == std::vector{96}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '`%'") == std::vector{96}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc`%'") == std::vector{96}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'a%'") == std::vector({65,97})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'a%'") == std::vector({65,97})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abca%'") == std::vector({65,97})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'b%'") == std::vector({66,98})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'b%'") == std::vector({66,98})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcb%'") == std::vector({66,98})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'c%'") == std::vector({67,99})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'c%'") == std::vector({67,99})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcc%'") == std::vector({67,99})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'd%'") == std::vector({68,100})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'd%'") == std::vector({68,100})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcd%'") == std::vector({68,100})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'e%'") == std::vector({69,101})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'e%'") == std::vector({69,101})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abce%'") == std::vector({69,101})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'f%'") == std::vector({70,102})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'f%'") == std::vector({70,102})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcf%'") == std::vector({70,102})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'g%'") == std::vector({71,103})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'g%'") == std::vector({71,103})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcg%'") == std::vector({71,103})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'h%'") == std::vector({72,104})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'h%'") == std::vector({72,104})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abch%'") == std::vector({72,104})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'i%'") == std::vector({73,105})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'i%'") == std::vector({73,105})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abci%'") == std::vector({73,105})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'j%'") == std::vector({74,106})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'j%'") == std::vector({74,106})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcj%'") == std::vector({74,106})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'k%'") == std::vector({75,107})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'k%'") == std::vector({75,107})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abck%'") == std::vector({75,107})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'l%'") == std::vector({76,108})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'l%'") == std::vector({76,108})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcl%'") == std::vector({76,108})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'm%'") == std::vector({77,109})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'm%'") == std::vector({77,109})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcm%'") == std::vector({77,109})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'n%'") == std::vector({78,110})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'n%'") == std::vector({78,110})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcn%'") == std::vector({78,110})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'o%'") == std::vector({79,111})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'o%'") == std::vector({79,111})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abco%'") == std::vector({79,111})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'p%'") == std::vector({80,112})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'p%'") == std::vector({80,112})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcp%'") == std::vector({80,112})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'q%'") == std::vector({81,113})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'q%'") == std::vector({81,113})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcq%'") == std::vector({81,113})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'r%'") == std::vector({82,114})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'r%'") == std::vector({82,114})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcr%'") == std::vector({82,114})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 's%'") == std::vector({83,115})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 's%'") == std::vector({83,115})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcs%'") == std::vector({83,115})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 't%'") == std::vector({84,116})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 't%'") == std::vector({84,116})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abct%'") == std::vector({84,116})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'u%'") == std::vector({85,117})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'u%'") == std::vector({85,117})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcu%'") == std::vector({85,117})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'v%'") == std::vector({86,118})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'v%'") == std::vector({86,118})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcv%'") == std::vector({86,118})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'w%'") == std::vector({87,119})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'w%'") == std::vector({87,119})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcw%'") == std::vector({87,119})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'x%'") == std::vector({88,120})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'x%'") == std::vector({88,120})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcx%'") == std::vector({88,120})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'y%'") == std::vector({89,121})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'y%'") == std::vector({89,121})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcy%'") == std::vector({89,121})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE 'z%'") == std::vector({90,122})); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE 'z%'") == std::vector({90,122})); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abcz%'") == std::vector({90,122})); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '\173%'") == std::vector{123}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '\173%'") == std::vector{123}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc\173%'") == std::vector{123}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '|%'") == std::vector{124}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '|%'") == std::vector{124}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc|%'") == std::vector{124}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '\175%'") == std::vector{125}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '\175%'") == std::vector{125}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc\175%'") == std::vector{125}); + QVERIFY(selectInt("SELECT x FROM t1 WHERE y LIKE '~%'") == std::vector{126}); + QVERIFY(selectInt("SELECT x FROM t2 WHERE y LIKE '~%'") == std::vector{126}); + QVERIFY(selectInt("SELECT x FROM t3 WHERE y LIKE 'abc~%'") == std::vector{126}); +} + + +void LikeTestR::initTestCase() +{ + QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); + db.setDatabaseName(":memory:"); + QVERIFY(db.open()); + QSqlQuery q; + q.exec(R"(PRAGMA encoding = "UTF-8")"); + QVERIFY(db.transaction()); + + QStringList str {"aab", "aaab", "aabaab", "abc", "xyz", "AAB", "AAAB", "AABAAB", "ABC", "XYZ", "AAB aab xyz", + "ккл", "кккл", "кклккл", "клк", "абв", "ККЛ", "КККЛ", "ККЛККЛ", "КЛК", "АБВ", "ККЛ ккл абв"}; + _rowcount = str.size(); + QVERIFY(q.exec("CREATE TABLE t1(x TEXT)")); + + for (const auto& s : str) + QVERIFY(q.exec(QStringLiteral("INSERT INTO t1 VALUES ('%1')").arg(s))); + + QVERIFY(db.commit()); + + QVERIFY(q.exec("SELECT count(*) FROM t1") && q.next() && q.value(0).toInt() == _rowcount); +} + +void LikeTestR::cleanupTestCase() +{ + QString connName; + { + QSqlDatabase db = QSqlDatabase::database(); + connName = db.connectionName(); + } + QSqlDatabase::removeDatabase(connName); +} + +void LikeTestR::installSqliter() +{ + QVERIFY(Sqliter::install(QSqlDatabase::database().driver()->handle())); +} + +void LikeTestR::test() +{ + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'AAB' ORDER BY 1") == + QStringList({"AAB", "aab",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'ККЛ' ORDER BY 1") == + QStringList({"ККЛ", "ккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%AAB%' ORDER BY 1") == + QStringList({"AAAB", "AAB", "AAB aab xyz", "AABAAB", "aaab", "aab", "aabaab",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%ККЛ%' ORDER BY 1") == + QStringList({"КККЛ", "ККЛ", "ККЛ ккл абв", "ККЛККЛ", "кккл", "ккл", "кклккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%AAB' ORDER BY 1") == + QStringList({"AAAB", "AAB", "AABAAB", "aaab", "aab", "aabaab",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%ККЛ' ORDER BY 1") == + QStringList({"КККЛ", "ККЛ", "ККЛККЛ", "кккл", "ккл", "кклккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '_AAB' ORDER BY 1") == + QStringList({"AAAB", "aaab",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '_ККЛ' ORDER BY 1") == + QStringList({"КККЛ", "кккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'AAA_' ORDER BY 1") == + QStringList({"AAAB", "aaab",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'ККК_' ORDER BY 1") == + QStringList({"КККЛ", "кккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%_AAB' ORDER BY 1") == + QStringList({"AAAB", "AABAAB", "aaab", "aabaab",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%_ККЛ' ORDER BY 1") == + QStringList({"КККЛ", "ККЛККЛ", "кккл", "кклккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%AAB_' ORDER BY 1").size() == 0); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%ККЛ_' ORDER BY 1").size() == 0); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%AAB%_' ORDER BY 1") == + QStringList({"AAB aab xyz", "AABAAB", "aabaab",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%ККЛ%_' ORDER BY 1") == + QStringList({"ККЛ ккл абв", "ККЛККЛ", "кклккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'AA_B' ORDER BY 1") == + QStringList({"AAAB", "aaab",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'КК_Л' ORDER BY 1") == + QStringList({"КККЛ", "кккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'AA_%B' ORDER BY 1") == + QStringList({"AAAB", "AABAAB", "aaab", "aabaab",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'КК_%Л' ORDER BY 1") == + QStringList({"КККЛ", "ККЛККЛ", "кккл", "кклккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%_%_%_%_%_' ORDER BY 1") == + QStringList({"AAB aab xyz", "AABAAB", "aabaab", "ККЛ ккл абв", "ККЛККЛ", "кклккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '______' ORDER BY 1") == + QStringList({"AABAAB", "aabaab", "ККЛККЛ", "кклккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '___%___' ORDER BY 1") == + QStringList({"AAB aab xyz", "AABAAB", "aabaab", "ККЛ ккл абв", "ККЛККЛ", "кклккл",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%AAB_DEAL' ORDER BY 1").size() == 0); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%AAB%DEAL' ORDER BY 1").size() == 0); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%AA' ORDER BY 1").size() == 0); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%' ORDER BY 1").size() == _rowcount); +} + +void LikeTestEscapedR::initTestCase() +{ + QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); + db.setDatabaseName(":memory:"); + QVERIFY(db.open()); + QSqlQuery q; + q.exec(R"(PRAGMA encoding = "UTF-8")"); + QVERIFY(db.transaction()); + + QStringList str {"XX_", "_XX", "xx%", "%xx", "к_К", "к%К"}; + int rowcount = str.size(); + QVERIFY(q.exec("CREATE TABLE t1(x TEXT)")); + + for (const auto& s : str) + QVERIFY(q.exec(QStringLiteral("INSERT INTO t1 VALUES ('%1')").arg(s))); + + QVERIFY(db.commit()); + + QVERIFY(q.exec("SELECT count(*) FROM t1") && q.next() && q.value(0).toInt() == rowcount); +} + +void LikeTestEscapedR::cleanupTestCase() +{ + QString connName; + { + QSqlDatabase db = QSqlDatabase::database(); + connName = db.connectionName(); + } + QSqlDatabase::removeDatabase(connName); +} + +void LikeTestEscapedR::installSqliter() +{ + QVERIFY(Sqliter::install(QSqlDatabase::database().driver()->handle())); +} + +void LikeTestEscapedR::test() +{ + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'xx_' ORDER BY 1") == + QStringList({"XX_", "xx%",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'xx#_' ESCAPE '#' ORDER BY 1") == + QStringList({"XX_"})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'xx%' ORDER BY 1") == + QStringList({"XX_", "xx%",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'xx#%' ESCAPE '#' ORDER BY 1") == + QStringList({"xx%"})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '_xx' ORDER BY 1") == + QStringList({"%xx", "_XX",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '#_xx' ESCAPE '#' ORDER BY 1") == + QStringList({"_XX"})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '%xx' ORDER BY 1") == + QStringList({"%xx", "_XX"})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE '#%xx' ESCAPE '#' ORDER BY 1") == + QStringList({"%xx"})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'К_К' ORDER BY 1") == + QStringList({"к%К", "к_К",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'К#_К' ESCAPE '#' ORDER BY 1") == + QStringList({"к_К"})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'к%к' ORDER BY 1") == + QStringList({"к%К", "к_К",})); + QVERIFY(select("SELECT x FROM t1 WHERE x LIKE 'к#%к' ESCAPE '#' ORDER BY 1") == + QStringList({"к%К"})); +} diff --git a/tests/liketest.h b/tests/liketest.h new file mode 100644 --- /dev/null +++ b/tests/liketest.h @@ -0,0 +1,84 @@ +#ifndef LIKETEST_H +#define LIKETEST_H + +#include + +class LikeTest : public QObject +{ + Q_OBJECT + +public: + LikeTest() = default; + +private Q_SLOTS: + void initTestCase(); + void cleanupTestCase(); + //Test that both case sensitive and insensitive version of LIKE work. + void like1_1(); + void like1_2(); + void like1_3(); + void like1_4(); + void like1_5_1(); + void like1_5_2(); + void like1_5_3(); + void like1_6(); + void like1_7(); + void like1_8(); + void like1_9(); + void like1_10(); + //Tests of the REGEXP operator + void like2_1() {} + void like2_2() {} + //Tests of the MATCH operator + void like2_3() {} + void like2_4() {} +}; + +class LikeTest2 : public QObject +{ + Q_OBJECT + +public: + LikeTest2() = default; + +private Q_SLOTS: + void initTestCase(); + void cleanupTestCase(); + void installSqliter(); + void like1_2(); + void like1_3(); + void like2(); +}; + +class LikeTestR : public QObject +{ + Q_OBJECT + +public: + LikeTestR() = default; + +private Q_SLOTS: + void initTestCase(); + void cleanupTestCase(); + void installSqliter(); + void test(); + +private: + int _rowcount; +}; + +class LikeTestEscapedR : public QObject +{ + Q_OBJECT + +public: + LikeTestEscapedR() = default; + +private Q_SLOTS: + void initTestCase(); + void cleanupTestCase(); + void installSqliter(); + void test(); +}; + +#endif // LIKETEST_H diff --git a/tests/main.cpp b/tests/main.cpp new file mode 100644 --- /dev/null +++ b/tests/main.cpp @@ -0,0 +1,24 @@ +#include +#include "sqrtest.h" +#include "liketest.h" + +int main(int argc, char *argv[]) +{ + QTEST_SET_MAIN_SOURCE_PATH + SqliterTest8 tc8; + int rc1 = QTest::qExec(&tc8, argc, argv); + SqliterTest16 tc16; + int rc2 = QTest::qExec(&tc16, argc, argv); + SqliterThreadedTest tt; + int rc3 = QTest::qExec(&tt, argc, argv); + LikeTest lt; + int rc4 = QTest::qExec(<, argc, argv); + LikeTest2 lt2; + int rc5 = QTest::qExec(<2, argc, argv); + LikeTestR ltr; + int rc6 = QTest::qExec(<r, argc, argv); + LikeTestEscapedR lter; + int rc7 = QTest::qExec(<er, argc, argv); + return rc1 + rc2 + rc3 + rc4 + rc5 + rc6 + rc7; +} + diff --git a/tests/sqrtest.cpp b/tests/sqrtest.cpp --- a/tests/sqrtest.cpp +++ b/tests/sqrtest.cpp @@ -1,263 +1,299 @@ #include #include #include #include #include +#include #include #include "sqliter.h" - -class SqliterTest : public QObject -{ - Q_OBJECT - -public: - 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")) {} -}; +#include "sqrtest.h" 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(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] == "шесть"); } 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[]) + +void SqliterThreadedTest::initTestCase() { - 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); + QThreadPool::globalInstance()->setMaxThreadCount(2); + + const int iterations = 100000; + + QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); + db.setDatabaseName(":memory:"); + QVERIFY(db.open()); + QSqlQuery q; + q.exec(QStringLiteral(R"(PRAGMA encoding = "UTF-16")")); + QVERIFY(db.transaction()); + + QVERIFY(q.exec("CREATE TABLE p (id INTEGER PRIMARY KEY, t TEXT)")); + for (int i = 0; i < iterations; ++i) + QVERIFY(q.exec(QStringLiteral("INSERT INTO p (t) VALUES ('Pedestrian')"))); + QVERIFY(q.exec("CREATE TABLE e (id INTEGER PRIMARY KEY, t TEXT)")); + for (int i = 0; i < iterations; ++i) + QVERIFY(q.exec(QStringLiteral("INSERT INTO e (t) VALUES ('Equestrian')"))); + QVERIFY(db.commit()); +} + +void SqliterThreadedTest::cleanupTestCase() +{ + QString connName; + { + QSqlDatabase db = QSqlDatabase::database(); + connName = db.connectionName(); + } + QSqlDatabase::removeDatabase(connName); +} + +void SqliterThreadedTest::installSqliter() +{ + QVERIFY(Sqliter::install(QSqlDatabase::database().driver()->handle())); } -#include "sqrtest.moc" +void SqliterThreadedTest::testUpperR() +{ + QFuture fe = QtConcurrent::run(this, &SqliterThreadedTest::threadUpperR, QStringLiteral("e"), QStringLiteral("equestrian")); + QFuture fp = QtConcurrent::run(this, &SqliterThreadedTest::threadUpperR, QStringLiteral("p"), QStringLiteral("pedestrian")); + QVERIFY(fp.result()); + QVERIFY(fe.result()); +} + +void SqliterThreadedTest::testLowerR() +{ + QFuture fe = QtConcurrent::run(this, &SqliterThreadedTest::threadLowerR, QStringLiteral("e"), QStringLiteral("equestrian")); + QFuture fp = QtConcurrent::run(this, &SqliterThreadedTest::threadLowerR, QStringLiteral("p"), QStringLiteral("pedestrian")); + QVERIFY(fp.result()); + QVERIFY(fe.result()); +} + +bool SqliterThreadedTest::threadUpperR(const QString& a_tablename, const QString& a_value) const +{ + QString value = a_value.toUpper(); + QSqlQuery q; + + if (!q.exec("SELECT UPPER(t) FROM " + a_tablename)) + return false; + + while (q.next()) + if (q.value(0).toString() != value) + { + qDebug() << q.value(0).toString() << "!=" << value; + return false; + } + return true; +} + +bool SqliterThreadedTest::threadLowerR(const QString& a_tablename, const QString& a_value) const +{ + QString value = a_value.toLower(); + QSqlQuery q; + + if (!q.exec("SELECT LOWER(t) FROM " + a_tablename)) + return false; + + while (q.next()) + if (q.value(0).toString() != value) + { + qDebug() << q.value(0).toString() << "!=" << value; + return false; + } + return true; +} diff --git a/tests/sqrtest.h b/tests/sqrtest.h new file mode 100644 --- /dev/null +++ b/tests/sqrtest.h @@ -0,0 +1,66 @@ +#ifndef SQRTEST_H +#define SQRTEST_H + +#include + +class SqliterTest : public QObject +{ + Q_OBJECT + +public: + 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")) {} +}; + +class SqliterThreadedTest : public QObject +{ + Q_OBJECT + +public: + SqliterThreadedTest() = default; + +private Q_SLOTS: + void initTestCase(); + void cleanupTestCase(); + void installSqliter(); + void testUpperR(); + void testLowerR(); + +private: + bool threadUpperR(const QString& a_tablename, const QString& a_value) const; + bool threadLowerR(const QString& a_tablename, const QString& a_value) const; +}; + +#endif // SQRTEST_H diff --git a/tests/tests.pro b/tests/tests.pro --- a/tests/tests.pro +++ b/tests/tests.pro @@ -1,43 +1,49 @@ #------------------------------------------------- # # Project created by QtCreator 2018-03-22T16:40:26 # #------------------------------------------------- TEMPLATE = app TARGET = sqrtest -QT += testlib sql +QT += testlib sql concurrent QT -= gui CONFIG += console testcase CONFIG -= app_bundle # 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 -SOURCES += sqrtest.cpp +SOURCES += sqrtest.cpp \ + main.cpp \ + liketest.cpp win32:CONFIG(release, debug|release): LIBS += -L$$OUT_PWD/../sqliter/release/ else:win32:CONFIG(debug, debug|release): LIBS += -L$$OUT_PWD/../sqliter/debug/ else:mac:CONFIG(debug, debug|release): LIBS += -L$$OUT_PWD/../sqliter/debug/ else:unix: LIBS += -L$$OUT_PWD/../sqliter/ LIBS += -lsqliter CONFIG(staticlib) { DEFINES += SQLITER_STATIC unix:LIBS += -lsqlite3 } INCLUDEPATH += $$PWD/../sqliter DEPENDPATH += $$PWD/../sqliter INSTALLS -= target + +HEADERS += \ + sqrtest.h \ + liketest.h