TestSqlStatementExecution.cpp 27 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680
  1. /*
  2. * Copyright (c) 2021, Jan de Visser <jan@de-visser.net>
  3. *
  4. * SPDX-License-Identifier: BSD-2-Clause
  5. */
  6. #include <unistd.h>
  7. #include <AK/ScopeGuard.h>
  8. #include <LibSQL/AST/Parser.h>
  9. #include <LibSQL/Database.h>
  10. #include <LibSQL/Row.h>
  11. #include <LibSQL/SQLResult.h>
  12. #include <LibSQL/Value.h>
  13. #include <LibTest/TestCase.h>
  14. namespace {
  15. constexpr const char* db_name = "/tmp/test.db";
  16. RefPtr<SQL::SQLResult> execute(NonnullRefPtr<SQL::Database> database, String const& sql)
  17. {
  18. auto parser = SQL::AST::Parser(SQL::AST::Lexer(sql));
  19. auto statement = parser.next_statement();
  20. EXPECT(!parser.has_errors());
  21. if (parser.has_errors())
  22. outln("{}", parser.errors()[0].to_string());
  23. auto result = statement->execute(move(database));
  24. if (result->error().code != SQL::SQLErrorCode::NoError)
  25. outln("{}", result->error().to_string());
  26. return result;
  27. }
  28. void create_schema(NonnullRefPtr<SQL::Database> database)
  29. {
  30. auto result = execute(database, "CREATE SCHEMA TestSchema;");
  31. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  32. EXPECT(result->inserted() == 1);
  33. }
  34. void create_table(NonnullRefPtr<SQL::Database> database)
  35. {
  36. create_schema(database);
  37. auto result = execute(database, "CREATE TABLE TestSchema.TestTable ( TextColumn text, IntColumn integer );");
  38. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  39. EXPECT(result->inserted() == 1);
  40. }
  41. void create_two_tables(NonnullRefPtr<SQL::Database> database)
  42. {
  43. create_schema(database);
  44. auto result = execute(database, "CREATE TABLE TestSchema.TestTable1 ( TextColumn1 text, IntColumn integer );");
  45. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  46. EXPECT(result->inserted() == 1);
  47. result = execute(database, "CREATE TABLE TestSchema.TestTable2 ( TextColumn2 text, IntColumn integer );");
  48. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  49. EXPECT(result->inserted() == 1);
  50. }
  51. TEST_CASE(create_schema)
  52. {
  53. ScopeGuard guard([]() { unlink(db_name); });
  54. auto database = SQL::Database::construct(db_name);
  55. EXPECT(!database->open().is_error());
  56. create_schema(database);
  57. auto schema_or_error = database->get_schema("TESTSCHEMA");
  58. EXPECT(!schema_or_error.is_error());
  59. EXPECT(schema_or_error.value());
  60. }
  61. TEST_CASE(create_table)
  62. {
  63. ScopeGuard guard([]() { unlink(db_name); });
  64. auto database = SQL::Database::construct(db_name);
  65. EXPECT(!database->open().is_error());
  66. create_table(database);
  67. auto table_or_error = database->get_table("TESTSCHEMA", "TESTTABLE");
  68. EXPECT(!table_or_error.is_error());
  69. EXPECT(table_or_error.value());
  70. }
  71. TEST_CASE(insert_into_table)
  72. {
  73. ScopeGuard guard([]() { unlink(db_name); });
  74. auto database = SQL::Database::construct(db_name);
  75. EXPECT(!database->open().is_error());
  76. create_table(database);
  77. auto result = execute(database, "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES ( 'Test', 42 );");
  78. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  79. EXPECT(result->inserted() == 1);
  80. auto table_or_error = database->get_table("TESTSCHEMA", "TESTTABLE");
  81. EXPECT(!table_or_error.is_error());
  82. auto table = table_or_error.value();
  83. int count = 0;
  84. auto rows_or_error = database->select_all(*table);
  85. EXPECT(!rows_or_error.is_error());
  86. for (auto& row : rows_or_error.value()) {
  87. EXPECT_EQ(row["TEXTCOLUMN"].to_string(), "Test");
  88. EXPECT_EQ(row["INTCOLUMN"].to_int().value(), 42);
  89. count++;
  90. }
  91. EXPECT_EQ(count, 1);
  92. }
  93. TEST_CASE(insert_into_table_wrong_data_types)
  94. {
  95. ScopeGuard guard([]() { unlink(db_name); });
  96. auto database = SQL::Database::construct(db_name);
  97. EXPECT(!database->open().is_error());
  98. create_table(database);
  99. auto result = execute(database, "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES (43, 'Test_2');");
  100. EXPECT(result->inserted() == 0);
  101. EXPECT(result->error().code == SQL::SQLErrorCode::InvalidValueType);
  102. }
  103. TEST_CASE(insert_into_table_multiple_tuples_wrong_data_types)
  104. {
  105. ScopeGuard guard([]() { unlink(db_name); });
  106. auto database = SQL::Database::construct(db_name);
  107. EXPECT(!database->open().is_error());
  108. create_table(database);
  109. auto result = execute(database, "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES ('Test_1', 42), (43, 'Test_2');");
  110. EXPECT(result->inserted() == 0);
  111. EXPECT(result->error().code == SQL::SQLErrorCode::InvalidValueType);
  112. }
  113. TEST_CASE(insert_wrong_number_of_values)
  114. {
  115. ScopeGuard guard([]() { unlink(db_name); });
  116. auto database = SQL::Database::construct(db_name);
  117. EXPECT(!database->open().is_error());
  118. create_table(database);
  119. auto result = execute(database, "INSERT INTO TestSchema.TestTable VALUES ( 42 );");
  120. EXPECT(result->error().code == SQL::SQLErrorCode::InvalidNumberOfValues);
  121. EXPECT(result->inserted() == 0);
  122. }
  123. TEST_CASE(insert_identifier_as_value)
  124. {
  125. ScopeGuard guard([]() { unlink(db_name); });
  126. auto database = SQL::Database::construct(db_name);
  127. EXPECT(!database->open().is_error());
  128. create_table(database);
  129. auto result = execute(database, "INSERT INTO TestSchema.TestTable VALUES ( identifier, 42 );");
  130. EXPECT(result->error().code == SQL::SQLErrorCode::SyntaxError);
  131. EXPECT(result->inserted() == 0);
  132. }
  133. TEST_CASE(insert_quoted_identifier_as_value)
  134. {
  135. ScopeGuard guard([]() { unlink(db_name); });
  136. auto database = SQL::Database::construct(db_name);
  137. EXPECT(!database->open().is_error());
  138. create_table(database);
  139. auto result = execute(database, "INSERT INTO TestSchema.TestTable VALUES ( \"QuotedIdentifier\", 42 );");
  140. EXPECT(result->error().code == SQL::SQLErrorCode::SyntaxError);
  141. EXPECT(result->inserted() == 0);
  142. }
  143. TEST_CASE(insert_without_column_names)
  144. {
  145. ScopeGuard guard([]() { unlink(db_name); });
  146. auto database = SQL::Database::construct(db_name);
  147. EXPECT(!database->open().is_error());
  148. create_table(database);
  149. auto result = execute(database, "INSERT INTO TestSchema.TestTable VALUES ('Test_1', 42), ('Test_2', 43);");
  150. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  151. EXPECT(result->inserted() == 2);
  152. auto table_or_error = database->get_table("TESTSCHEMA", "TESTTABLE");
  153. EXPECT(!table_or_error.is_error());
  154. auto rows_or_error = database->select_all(*(table_or_error.value()));
  155. EXPECT(!rows_or_error.is_error());
  156. EXPECT_EQ(rows_or_error.value().size(), 2u);
  157. }
  158. TEST_CASE(select_from_table)
  159. {
  160. ScopeGuard guard([]() { unlink(db_name); });
  161. auto database = SQL::Database::construct(db_name);
  162. EXPECT(!database->open().is_error());
  163. create_table(database);
  164. auto result = execute(database,
  165. "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES "
  166. "( 'Test_1', 42 ), "
  167. "( 'Test_2', 43 ), "
  168. "( 'Test_3', 44 ), "
  169. "( 'Test_4', 45 ), "
  170. "( 'Test_5', 46 );");
  171. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  172. EXPECT(result->inserted() == 5);
  173. result = execute(database, "SELECT * FROM TestSchema.TestTable;");
  174. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  175. EXPECT(result->has_results());
  176. EXPECT_EQ(result->results().size(), 5u);
  177. }
  178. TEST_CASE(select_with_column_names)
  179. {
  180. ScopeGuard guard([]() { unlink(db_name); });
  181. auto database = SQL::Database::construct(db_name);
  182. EXPECT(!database->open().is_error());
  183. create_table(database);
  184. auto result = execute(database,
  185. "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES "
  186. "( 'Test_1', 42 ), "
  187. "( 'Test_2', 43 ), "
  188. "( 'Test_3', 44 ), "
  189. "( 'Test_4', 45 ), "
  190. "( 'Test_5', 46 );");
  191. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  192. EXPECT(result->inserted() == 5);
  193. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable;");
  194. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  195. EXPECT(result->has_results());
  196. EXPECT_EQ(result->results().size(), 5u);
  197. EXPECT_EQ(result->results()[0].row.size(), 1u);
  198. }
  199. TEST_CASE(select_with_nonexisting_column_name)
  200. {
  201. ScopeGuard guard([]() { unlink(db_name); });
  202. auto database = SQL::Database::construct(db_name);
  203. EXPECT(!database->open().is_error());
  204. create_table(database);
  205. auto result = execute(database,
  206. "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES "
  207. "( 'Test_1', 42 ), "
  208. "( 'Test_2', 43 ), "
  209. "( 'Test_3', 44 ), "
  210. "( 'Test_4', 45 ), "
  211. "( 'Test_5', 46 );");
  212. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  213. EXPECT(result->inserted() == 5);
  214. result = execute(database, "SELECT Bogus FROM TestSchema.TestTable;");
  215. EXPECT(result->error().code == SQL::SQLErrorCode::ColumnDoesNotExist);
  216. }
  217. TEST_CASE(select_with_where)
  218. {
  219. ScopeGuard guard([]() { unlink(db_name); });
  220. auto database = SQL::Database::construct(db_name);
  221. EXPECT(!database->open().is_error());
  222. create_table(database);
  223. auto result = execute(database,
  224. "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES "
  225. "( 'Test_1', 42 ), "
  226. "( 'Test_2', 43 ), "
  227. "( 'Test_3', 44 ), "
  228. "( 'Test_4', 45 ), "
  229. "( 'Test_5', 46 );");
  230. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  231. EXPECT(result->inserted() == 5);
  232. result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable WHERE IntColumn > 44;");
  233. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  234. EXPECT(result->has_results());
  235. EXPECT_EQ(result->results().size(), 2u);
  236. for (auto& row : result->results()) {
  237. EXPECT(row.row[1].to_int().value() > 44);
  238. }
  239. }
  240. TEST_CASE(select_cross_join)
  241. {
  242. ScopeGuard guard([]() { unlink(db_name); });
  243. auto database = SQL::Database::construct(db_name);
  244. EXPECT(!database->open().is_error());
  245. create_two_tables(database);
  246. auto result = execute(database,
  247. "INSERT INTO TestSchema.TestTable1 ( TextColumn1, IntColumn ) VALUES "
  248. "( 'Test_1', 42 ), "
  249. "( 'Test_2', 43 ), "
  250. "( 'Test_3', 44 ), "
  251. "( 'Test_4', 45 ), "
  252. "( 'Test_5', 46 );");
  253. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  254. EXPECT(result->inserted() == 5);
  255. result = execute(database,
  256. "INSERT INTO TestSchema.TestTable2 ( TextColumn2, IntColumn ) VALUES "
  257. "( 'Test_10', 40 ), "
  258. "( 'Test_11', 41 ), "
  259. "( 'Test_12', 42 ), "
  260. "( 'Test_13', 47 ), "
  261. "( 'Test_14', 48 );");
  262. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  263. EXPECT(result->inserted() == 5);
  264. result = execute(database, "SELECT * FROM TestSchema.TestTable1, TestSchema.TestTable2;");
  265. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  266. EXPECT(result->has_results());
  267. EXPECT_EQ(result->results().size(), 25u);
  268. for (auto& row : result->results()) {
  269. EXPECT(row.row.size() == 4);
  270. EXPECT(row.row[1].to_int().value() >= 42);
  271. EXPECT(row.row[1].to_int().value() <= 46);
  272. EXPECT(row.row[3].to_int().value() >= 40);
  273. EXPECT(row.row[3].to_int().value() <= 48);
  274. }
  275. }
  276. TEST_CASE(select_inner_join)
  277. {
  278. ScopeGuard guard([]() { unlink(db_name); });
  279. auto database = SQL::Database::construct(db_name);
  280. EXPECT(!database->open().is_error());
  281. create_two_tables(database);
  282. auto result = execute(database,
  283. "INSERT INTO TestSchema.TestTable1 ( TextColumn1, IntColumn ) VALUES "
  284. "( 'Test_1', 42 ), "
  285. "( 'Test_2', 43 ), "
  286. "( 'Test_3', 44 ), "
  287. "( 'Test_4', 45 ), "
  288. "( 'Test_5', 46 );");
  289. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  290. EXPECT(result->inserted() == 5);
  291. result = execute(database,
  292. "INSERT INTO TestSchema.TestTable2 ( TextColumn2, IntColumn ) VALUES "
  293. "( 'Test_10', 40 ), "
  294. "( 'Test_11', 41 ), "
  295. "( 'Test_12', 42 ), "
  296. "( 'Test_13', 47 ), "
  297. "( 'Test_14', 48 );");
  298. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  299. EXPECT(result->inserted() == 5);
  300. result = execute(database,
  301. "SELECT TestTable1.IntColumn, TextColumn1, TextColumn2 "
  302. "FROM TestSchema.TestTable1, TestSchema.TestTable2 "
  303. "WHERE TestTable1.IntColumn = TestTable2.IntColumn;");
  304. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  305. EXPECT(result->has_results());
  306. EXPECT_EQ(result->results().size(), 1u);
  307. auto& row = result->results()[0];
  308. EXPECT_EQ(row.row.size(), 3u);
  309. EXPECT_EQ(row.row[0].to_int().value(), 42);
  310. EXPECT_EQ(row.row[1].to_string(), "Test_1");
  311. EXPECT_EQ(row.row[2].to_string(), "Test_12");
  312. }
  313. TEST_CASE(select_with_like)
  314. {
  315. ScopeGuard guard([]() { unlink(db_name); });
  316. auto database = SQL::Database::construct(db_name);
  317. EXPECT(!database->open().is_error());
  318. create_table(database);
  319. auto result = execute(database,
  320. "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES "
  321. "( 'Test+1', 42 ), "
  322. "( 'Test+2', 43 ), "
  323. "( 'Test+3', 44 ), "
  324. "( 'Test+4', 45 ), "
  325. "( 'Test+5', 46 ), "
  326. "( 'Another+Test_6', 47 );");
  327. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  328. EXPECT(result->inserted() == 6);
  329. // Simple match
  330. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn LIKE 'Test+1';");
  331. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  332. EXPECT(result->has_results());
  333. EXPECT_EQ(result->results().size(), 1u);
  334. // Use % to match most rows
  335. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn LIKE 'T%';");
  336. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  337. EXPECT(result->has_results());
  338. EXPECT_EQ(result->results().size(), 5u);
  339. // Same as above but invert the match
  340. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn NOT LIKE 'T%';");
  341. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  342. EXPECT(result->has_results());
  343. EXPECT_EQ(result->results().size(), 1u);
  344. // Use _ and % to match all rows
  345. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn LIKE '%e_t%';");
  346. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  347. EXPECT(result->has_results());
  348. EXPECT_EQ(result->results().size(), 6u);
  349. // Use escape to match a single row. The escape character happens to be a
  350. // Regex metacharacter, let's make sure we don't get confused by that.
  351. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn LIKE '%Test^_%' ESCAPE '^';");
  352. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  353. EXPECT(result->has_results());
  354. EXPECT_EQ(result->results().size(), 1u);
  355. // Same as above but escape the escape character happens to be a SQL
  356. // metacharacter - we want to make sure it's treated as an escape.
  357. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn LIKE '%Test__%' ESCAPE '_';");
  358. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  359. EXPECT(result->has_results());
  360. EXPECT_EQ(result->results().size(), 1u);
  361. // (Unnecessarily) escaping a character that happens to be a Regex
  362. // metacharacter should have no effect.
  363. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn LIKE 'Test:+_' ESCAPE ':';");
  364. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  365. EXPECT(result->has_results());
  366. EXPECT_EQ(result->results().size(), 5u);
  367. // Make sure we error out if the ESCAPE is empty
  368. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn LIKE '%' ESCAPE '';");
  369. EXPECT(result->error().code == SQL::SQLErrorCode::SyntaxError);
  370. EXPECT(!result->has_results());
  371. // Make sure we error out if the ESCAPE has more than a single character
  372. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn LIKE '%' ESCAPE 'whf';");
  373. EXPECT(result->error().code == SQL::SQLErrorCode::SyntaxError);
  374. EXPECT(!result->has_results());
  375. }
  376. TEST_CASE(select_with_order)
  377. {
  378. ScopeGuard guard([]() { unlink(db_name); });
  379. auto database = SQL::Database::construct(db_name);
  380. EXPECT(!database->open().is_error());
  381. create_table(database);
  382. auto result = execute(database,
  383. "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES "
  384. "( 'Test_5', 44 ), "
  385. "( 'Test_2', 42 ), "
  386. "( 'Test_1', 47 ), "
  387. "( 'Test_3', 40 ), "
  388. "( 'Test_4', 41 );");
  389. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  390. EXPECT(result->inserted() == 5);
  391. result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable ORDER BY IntColumn;");
  392. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  393. EXPECT(result->has_results());
  394. auto rows = result->results();
  395. EXPECT_EQ(rows.size(), 5u);
  396. EXPECT_EQ(rows[0].row[1].to_int().value(), 40);
  397. EXPECT_EQ(rows[1].row[1].to_int().value(), 41);
  398. EXPECT_EQ(rows[2].row[1].to_int().value(), 42);
  399. EXPECT_EQ(rows[3].row[1].to_int().value(), 44);
  400. EXPECT_EQ(rows[4].row[1].to_int().value(), 47);
  401. result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable ORDER BY TextColumn;");
  402. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  403. EXPECT(result->has_results());
  404. rows = result->results();
  405. EXPECT_EQ(rows.size(), 5u);
  406. EXPECT_EQ(rows[0].row[0].to_string(), "Test_1");
  407. EXPECT_EQ(rows[1].row[0].to_string(), "Test_2");
  408. EXPECT_EQ(rows[2].row[0].to_string(), "Test_3");
  409. EXPECT_EQ(rows[3].row[0].to_string(), "Test_4");
  410. EXPECT_EQ(rows[4].row[0].to_string(), "Test_5");
  411. }
  412. TEST_CASE(select_with_regexp)
  413. {
  414. ScopeGuard guard([]() { unlink(db_name); });
  415. auto database = SQL::Database::construct(db_name);
  416. EXPECT(!database->open().is_error());
  417. create_table(database);
  418. auto result = execute(database,
  419. "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES "
  420. "( 'Test+1', 42 ), "
  421. "( 'Pröv+2', 43 ), "
  422. "( 'Test(3)', 44 ), "
  423. "( 'Test[4]', 45 ), "
  424. "( 'Test+5', 46 ), "
  425. "( 'Another-Test_6', 47 );");
  426. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  427. EXPECT(result->inserted() == 6);
  428. // Simple match
  429. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn REGEXP 'Test\\+1';");
  430. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  431. EXPECT(result->has_results());
  432. EXPECT_EQ(result->results().size(), 1u);
  433. // Match all
  434. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn REGEXP '.*';");
  435. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  436. EXPECT(result->has_results());
  437. EXPECT_EQ(result->results().size(), 6u);
  438. // Match with wildcards
  439. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn REGEXP '^Test.+';");
  440. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  441. EXPECT(result->has_results());
  442. EXPECT_EQ(result->results().size(), 4u);
  443. // Match with case insensitive basic Latin and case sensitive Swedish ö
  444. // FIXME: If LibRegex is changed to support case insensitive matches of Unicode characters
  445. // This test should be updated and changed to match 'PRÖV'.
  446. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn REGEXP 'PRöV.*';");
  447. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  448. EXPECT(result->has_results());
  449. EXPECT_EQ(result->results().size(), 1u);
  450. }
  451. TEST_CASE(handle_regexp_errors)
  452. {
  453. ScopeGuard guard([]() { unlink(db_name); });
  454. auto database = SQL::Database::construct(db_name);
  455. EXPECT(!database->open().is_error());
  456. create_table(database);
  457. auto result = execute(database,
  458. "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES "
  459. "( 'Test', 0 );");
  460. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  461. EXPECT(result->inserted() == 1);
  462. // Malformed regex, unmatched square bracket
  463. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable WHERE TextColumn REGEXP 'Test\\+[0-9.*';");
  464. EXPECT(result->error().code != SQL::SQLErrorCode::NoError);
  465. EXPECT(!result->has_results());
  466. }
  467. TEST_CASE(select_with_order_two_columns)
  468. {
  469. ScopeGuard guard([]() { unlink(db_name); });
  470. auto database = SQL::Database::construct(db_name);
  471. EXPECT(!database->open().is_error());
  472. create_table(database);
  473. auto result = execute(database,
  474. "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES "
  475. "( 'Test_5', 44 ), "
  476. "( 'Test_2', 42 ), "
  477. "( 'Test_1', 47 ), "
  478. "( 'Test_2', 40 ), "
  479. "( 'Test_4', 41 );");
  480. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  481. EXPECT(result->inserted() == 5);
  482. result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable ORDER BY TextColumn, IntColumn;");
  483. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  484. EXPECT(result->has_results());
  485. auto rows = result->results();
  486. EXPECT_EQ(rows.size(), 5u);
  487. EXPECT_EQ(rows[0].row[0].to_string(), "Test_1");
  488. EXPECT_EQ(rows[0].row[1].to_int().value(), 47);
  489. EXPECT_EQ(rows[1].row[0].to_string(), "Test_2");
  490. EXPECT_EQ(rows[1].row[1].to_int().value(), 40);
  491. EXPECT_EQ(rows[2].row[0].to_string(), "Test_2");
  492. EXPECT_EQ(rows[2].row[1].to_int().value(), 42);
  493. EXPECT_EQ(rows[3].row[0].to_string(), "Test_4");
  494. EXPECT_EQ(rows[3].row[1].to_int().value(), 41);
  495. EXPECT_EQ(rows[4].row[0].to_string(), "Test_5");
  496. EXPECT_EQ(rows[4].row[1].to_int().value(), 44);
  497. }
  498. TEST_CASE(select_with_order_by_column_not_in_result)
  499. {
  500. ScopeGuard guard([]() { unlink(db_name); });
  501. auto database = SQL::Database::construct(db_name);
  502. EXPECT(!database->open().is_error());
  503. create_table(database);
  504. auto result = execute(database,
  505. "INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES "
  506. "( 'Test_5', 44 ), "
  507. "( 'Test_2', 42 ), "
  508. "( 'Test_1', 47 ), "
  509. "( 'Test_3', 40 ), "
  510. "( 'Test_4', 41 );");
  511. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  512. EXPECT(result->inserted() == 5);
  513. result = execute(database, "SELECT TextColumn FROM TestSchema.TestTable ORDER BY IntColumn;");
  514. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  515. EXPECT(result->has_results());
  516. auto rows = result->results();
  517. EXPECT_EQ(rows.size(), 5u);
  518. EXPECT_EQ(rows[0].row[0].to_string(), "Test_3");
  519. EXPECT_EQ(rows[1].row[0].to_string(), "Test_4");
  520. EXPECT_EQ(rows[2].row[0].to_string(), "Test_2");
  521. EXPECT_EQ(rows[3].row[0].to_string(), "Test_5");
  522. EXPECT_EQ(rows[4].row[0].to_string(), "Test_1");
  523. }
  524. TEST_CASE(select_with_limit)
  525. {
  526. ScopeGuard guard([]() { unlink(db_name); });
  527. auto database = SQL::Database::construct(db_name);
  528. EXPECT(!database->open().is_error());
  529. create_table(database);
  530. for (auto count = 0; count < 100; count++) {
  531. auto result = execute(database,
  532. String::formatted("INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES ( 'Test_{}', {} );", count, count));
  533. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  534. EXPECT(result->inserted() == 1);
  535. }
  536. auto result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable LIMIT 10;");
  537. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  538. EXPECT(result->has_results());
  539. auto rows = result->results();
  540. EXPECT_EQ(rows.size(), 10u);
  541. }
  542. TEST_CASE(select_with_limit_and_offset)
  543. {
  544. ScopeGuard guard([]() { unlink(db_name); });
  545. auto database = SQL::Database::construct(db_name);
  546. EXPECT(!database->open().is_error());
  547. create_table(database);
  548. for (auto count = 0; count < 100; count++) {
  549. auto result = execute(database,
  550. String::formatted("INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES ( 'Test_{}', {} );", count, count));
  551. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  552. EXPECT(result->inserted() == 1);
  553. }
  554. auto result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable LIMIT 10 OFFSET 10;");
  555. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  556. EXPECT(result->has_results());
  557. auto rows = result->results();
  558. EXPECT_EQ(rows.size(), 10u);
  559. }
  560. TEST_CASE(select_with_order_limit_and_offset)
  561. {
  562. ScopeGuard guard([]() { unlink(db_name); });
  563. auto database = SQL::Database::construct(db_name);
  564. EXPECT(!database->open().is_error());
  565. create_table(database);
  566. for (auto count = 0; count < 100; count++) {
  567. auto result = execute(database,
  568. String::formatted("INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES ( 'Test_{}', {} );", count, count));
  569. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  570. EXPECT(result->inserted() == 1);
  571. }
  572. auto result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable ORDER BY IntColumn LIMIT 10 OFFSET 10;");
  573. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  574. EXPECT(result->has_results());
  575. auto rows = result->results();
  576. EXPECT_EQ(rows.size(), 10u);
  577. EXPECT_EQ(rows[0].row[1].to_int().value(), 10);
  578. EXPECT_EQ(rows[1].row[1].to_int().value(), 11);
  579. EXPECT_EQ(rows[2].row[1].to_int().value(), 12);
  580. EXPECT_EQ(rows[3].row[1].to_int().value(), 13);
  581. EXPECT_EQ(rows[4].row[1].to_int().value(), 14);
  582. EXPECT_EQ(rows[5].row[1].to_int().value(), 15);
  583. EXPECT_EQ(rows[6].row[1].to_int().value(), 16);
  584. EXPECT_EQ(rows[7].row[1].to_int().value(), 17);
  585. EXPECT_EQ(rows[8].row[1].to_int().value(), 18);
  586. EXPECT_EQ(rows[9].row[1].to_int().value(), 19);
  587. }
  588. TEST_CASE(select_with_limit_out_of_bounds)
  589. {
  590. ScopeGuard guard([]() { unlink(db_name); });
  591. auto database = SQL::Database::construct(db_name);
  592. EXPECT(!database->open().is_error());
  593. create_table(database);
  594. for (auto count = 0; count < 100; count++) {
  595. auto result = execute(database,
  596. String::formatted("INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES ( 'Test_{}', {} );", count, count));
  597. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  598. EXPECT(result->inserted() == 1);
  599. }
  600. auto result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable LIMIT 500;");
  601. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  602. EXPECT(result->has_results());
  603. auto rows = result->results();
  604. EXPECT_EQ(rows.size(), 100u);
  605. }
  606. TEST_CASE(select_with_offset_out_of_bounds)
  607. {
  608. ScopeGuard guard([]() { unlink(db_name); });
  609. auto database = SQL::Database::construct(db_name);
  610. EXPECT(!database->open().is_error());
  611. create_table(database);
  612. for (auto count = 0; count < 100; count++) {
  613. auto result = execute(database,
  614. String::formatted("INSERT INTO TestSchema.TestTable ( TextColumn, IntColumn ) VALUES ( 'Test_{}', {} );", count, count));
  615. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  616. EXPECT(result->inserted() == 1);
  617. }
  618. auto result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable LIMIT 10 OFFSET 200;");
  619. EXPECT(result->error().code == SQL::SQLErrorCode::NoError);
  620. EXPECT(result->has_results());
  621. auto rows = result->results();
  622. EXPECT_EQ(rows.size(), 0u);
  623. }
  624. }