1 module tests.dbal_mariadb.tests; 2 3 import oceandrift.db.dbal; 4 import oceandrift.db.mariadb; 5 6 @safe: 7 8 MariaDBDatabaseDriver getDB() 9 { 10 return new MariaDBDatabaseDriver("localhost", "u_oceandrift", "p_oceandrift", null); 11 } 12 13 void dropRecreateUseDB(MariaDBDatabaseDriver driver) 14 { 15 driver.execute("DROP DATABASE IF EXISTS `d_oceandrift`"); 16 driver.execute("CREATE DATABASE `d_oceandrift`"); 17 driver.execute("USE `d_oceandrift`"); 18 } 19 20 unittest 21 { 22 MariaDBDatabaseDriver driver = getDB(); 23 assert(!driver.connected); 24 25 driver.connect(); 26 assert(driver.connected); 27 28 driver.dropRecreateUseDB(); 29 30 driver.execute("CREATE TABLE `demo`(`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY)"); 31 32 Statement count = driver.prepare("SELECT COUNT(*) FROM `demo`"); 33 scope (exit) 34 count.close(); 35 36 count.execute(); 37 assert(!count.empty); 38 assert(count.front[0].get!ulong == 0); 39 40 Statement insert = driver.prepare("INSERT INTO `demo`() VALUES()"); 41 scope (exit) 42 insert.close(); 43 44 { 45 driver.autoCommit = true; 46 assert(driver.autoCommit); 47 48 insert.execute(); 49 assert(insert.empty); 50 51 driver.transactionRollback(); 52 53 driver.autoCommit = false; 54 assert(!driver.autoCommit); 55 56 count.execute(); 57 assert(!count.empty); 58 assert(count.front[0].get!ulong == 1); 59 60 { 61 driver.transactionStart(); 62 63 insert.execute(); 64 assert(insert.empty); 65 insert.execute(); 66 assert(insert.empty); 67 68 count.execute(); 69 assert(!count.empty); 70 assert(count.front[0].get!ulong == 3); 71 72 driver.transactionRollback(); 73 count.execute(); 74 assert(!count.empty); 75 assert(count.front[0].get!ulong == 1); 76 } 77 78 { 79 driver.transactionStart(); 80 81 insert.execute(); 82 assert(insert.empty); 83 insert.execute(); 84 assert(insert.empty); 85 insert.execute(); 86 assert(insert.empty); 87 88 count.execute(); 89 assert(!count.empty); 90 assert(count.front[0].get!ulong == 4); 91 92 driver.transactionCommit(); 93 count.execute(); 94 assert(!count.empty); 95 assert(count.front[0].get!ulong == 4); 96 } 97 } 98 99 driver.execute("DROP DATABASE `d_oceandrift`"); 100 driver.close(); 101 assert(!driver.connected); 102 } 103 104 unittest 105 { 106 MariaDBDatabaseDriver driver = getDB(); 107 driver.connect(); 108 scope (exit) 109 driver.close(); 110 111 driver.dropRecreateUseDB(); 112 113 driver.execute(" 114 CREATE TABLE `demo` ( 115 `id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, 116 `col1` VARCHAR(4) NOT NULL, 117 `col2` VARCHAR(4) NOT NULL 118 ) 119 "); 120 121 driver.transactionStart(); 122 123 Statement insert = driver.prepare("INSERT INTO `demo`(`col1`, `col2`) VALUES(?, ?)"); 124 scope (exit) 125 insert.close(); 126 127 insert.bind(0, "asdf"); 128 insert.bind(1, "jklö"); 129 insert.execute(); 130 131 insert.bind(0, "qwer"); 132 insert.bind(1, "uiop"); 133 insert.execute(); 134 135 insert.bind(0, "yxcv"); 136 insert.bind(1, "bnm,"); 137 insert.execute(); 138 139 insert.bind(0, "qaz"); 140 insert.bind(1, "wsx"); 141 insert.execute(); 142 143 insert.bind(0, "edc"); 144 insert.bind(1, "rfv"); 145 insert.execute(); 146 147 Statement select = driver.prepare(" 148 SELECT * FROM `demo` 149 WHERE 150 `id` > ? 151 AND `id` < ? 152 ORDER BY 153 `id` ASC 154 "); 155 scope (exit) 156 select.close(); 157 158 select.bind(0, 2); 159 select.bind(1, 5); 160 select.execute(); 161 assert(!select.empty); 162 163 Row row1 = select.front(); 164 assert(row1[0].get!ulong == 3); 165 assert(row1[1].get!string == "yxcv"); 166 assert(row1[2].get!string == "bnm,"); 167 168 select.popFront(); 169 assert(!select.empty); 170 Row row2 = select.front(); 171 assert(row2[0].get!ulong == 4); 172 assert(row2[1].get!string == "qaz"); 173 assert(row2[2].get!string == "wsx"); 174 175 driver.transactionRollback(); 176 177 select.execute(); 178 assert(select.empty, "Rollback broken"); 179 } 180 181 unittest 182 { 183 MariaDBDatabaseDriver driver = getDB(); 184 driver.connect(); 185 scope (exit) 186 driver.close(); 187 188 driver.autoCommit = true; 189 driver.dropRecreateUseDB(); 190 191 driver.execute(" 192 CREATE TABLE `person` ( 193 `id` INTEGER AUTO_INCREMENT PRIMARY KEY, 194 `name` VARCHAR(6) NOT NULL, 195 `nickname` VARCHAR(8), 196 `age` TINYINT UNSIGNED NOT NULL 197 ) 198 "); 199 200 Statement insert = driver.prepare( 201 "INSERT INTO `person` (`name`, `age`, `nickname`) VALUES (?, ?, ?)" 202 ); 203 scope (exit) 204 insert.close(); 205 206 insert.executeWith("Thomas", 21, "Tom"); 207 insert.executeWith("Daniel", 30, "Dan"); 208 209 insert.bind(0, "Jan"); 210 insert.bind(1, 22); 211 insert.bind(2, "WebFreak"); 212 insert.execute(); 213 214 { 215 Statement select = driver.prepare(" 216 SELECT `id`, `age` FROM `person` 217 WHERE 218 `age` >= ? 219 ORDER BY 220 `age` ASC 221 "); 222 scope (exit) 223 select.close(); 224 225 select.bind(0, 22); 226 select.execute(); 227 assert(!select.empty); 228 229 Row row1 = select.front(); 230 assert(row1[0].get!int == 3); 231 232 select.popFront(); 233 assert(!select.empty); 234 Row row2 = select.front(); 235 assert(row2[0].get!int == 2); 236 assert(row2[1].get!ubyte == 30); 237 238 select.popFront(); 239 assert(select.empty); 240 } 241 242 { 243 insert.executeWith("David", 35, "Dave"); 244 245 Statement stmtCount = driver.prepare("SELECT COUNT(*) FROM `person`"); 246 stmtCount.execute(); 247 assert(stmtCount.front()[0].get!long == 4); 248 stmtCount.popFront(); 249 assert(stmtCount.empty); 250 } 251 252 { 253 Statement select = driver.prepare( 254 "SELECT `nickname` FROM `person` WHERE `nickname` LIKE ?" 255 ); 256 scope (exit) 257 select.close(); 258 259 immutable string pattern = "Da%"; 260 select.executeWith(pattern); 261 262 size_t cnt = 0; 263 foreach (Row row; select) 264 { 265 string nickname = row[0].get!string; 266 assert(nickname[0 .. 2] == "Da"); 267 ++cnt; 268 } 269 assert(cnt == 2); 270 } 271 } 272 273 unittest 274 { 275 import std.algorithm : canFind; 276 import std.random : rndGen; 277 278 MariaDBDatabaseDriver driver = getDB(); 279 driver.connect(); 280 scope (exit) 281 driver.close(); 282 283 driver.autoCommit = true; 284 driver.dropRecreateUseDB(); 285 286 driver.execute(" 287 CREATE TABLE `misc` ( 288 `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 289 `blob` BINARY(20) 290 ) 291 "); 292 293 ubyte[] blablabla; 294 for (size_t n = 0; n < 20; ++n) 295 { 296 blablabla ~= ubyte(rndGen.front % ubyte.max); 297 rndGen.popFront(); 298 } 299 300 { 301 Statement insert = driver.prepare("INSERT INTO `misc`(`blob`) VALUES(?)"); 302 insert.executeWith(blablabla); 303 } 304 305 { 306 Statement select = driver.prepare("SELECT `blob` FROM `misc`"); 307 scope (exit) 308 select.close(); 309 310 select.execute(); 311 assert(!select.empty); 312 313 Row row = select.front; 314 assert(row[0].get!string == blablabla); 315 316 ubyte[] bla2 = blablabla.dup; 317 bla2[2] += 1; 318 assert(row[0].get!string != bla2); 319 320 select.popFront(); 321 assert(select.empty); 322 } 323 } 324 325 unittest 326 { 327 MariaDBDatabaseDriver driver = getDB(); 328 driver.connect(); 329 scope (exit) 330 driver.close(); 331 332 driver.autoCommit = true; 333 driver.dropRecreateUseDB(); 334 335 driver.execute(" 336 CREATE TABLE `holiday` ( 337 `id` INTEGER AUTO_INCREMENT PRIMARY KEY, 338 `date` DATE NOT NULL, 339 `name` VARCHAR(20) NOT NULL DEFAULT 'national' 340 ) 341 "); 342 343 Statement insert = driver.prepare("INSERT INTO `holiday`(`date`, `name`) VALUES(?, ?)"); 344 scope (exit) 345 insert.close(); 346 347 Statement insertDateOnly = driver.prepare("INSERT INTO `holiday`(`date`) VALUES(?)"); 348 scope (exit) 349 insertDateOnly.close(); 350 351 Statement countAny = driver.prepare("SELECT COUNT(*) FROM `holiday`"); 352 scope (exit) 353 countAny.close(); 354 355 Statement countByName = driver.prepare("SELECT COUNT(*) FROM `holiday` WHERE `name` = ?"); 356 scope (exit) 357 countByName.close(); 358 359 Statement getByName = driver.prepare("SELECT `date` FROM `holiday` WHERE `name` = ?"); 360 scope (exit) 361 getByName.close(); 362 363 insert.executeWith(Date(2022, 1, 1), "New Year"); 364 insert.executeWith(Date(2022, 4, 18), "Easter Monday"); 365 insertDateOnly.executeWith(Date(2022, 5, 1)); 366 insertDateOnly.executeWith(Date(2022, 10, 26)); 367 368 countAny.execute(); 369 assert(!countAny.empty); 370 assert(countAny.front[0].get!long == 4); 371 372 countByName.executeWith("national"); 373 assert(!countByName.empty); 374 assert(countByName.front[0].get!long == 2); 375 376 getByName.executeWith("New Year"); 377 assert(!getByName.empty); 378 Date newYear = getByName.front[0].get!Date; 379 assert(newYear.day == 1); 380 assert(newYear.month == 1); 381 assert(newYear.year == 2022); 382 383 getByName.popFront(); 384 assert(getByName.empty); 385 } 386 387 unittest 388 { 389 MariaDBDatabaseDriver driver = getDB(); 390 driver.connect(); 391 scope (exit) 392 driver.close(); 393 394 driver.autoCommit = true; 395 driver.dropRecreateUseDB(); 396 397 driver.execute(" 398 CREATE TABLE `moment` ( 399 `id` INTEGER AUTO_INCREMENT PRIMARY KEY, 400 `date` DATE NOT NULL, 401 `time` TIME NOT NULL, 402 `redundant` DATETIME NOT NULL 403 ) 404 "); 405 406 auto date = Date(2022, 7, 24); 407 auto time = TimeOfDay(22, 30, 1); 408 409 { 410 Statement insert = driver.prepare( 411 "INSERT INTO `moment`(`date`, `time`, `redundant`) VALUES(?, ?, ?)"); 412 scope (exit) 413 insert.close(); 414 415 insert.executeWith(date, time, DateTime(date, time)); 416 assert(insert.empty); 417 } 418 419 { 420 Statement test = driver.prepare(" 421 SELECT 422 COUNT(*) 423 FROM `moment` 424 WHERE 425 `date` = DATE(`redundant`) 426 AND `time` = TIME(`redundant`) 427 "); 428 scope (exit) 429 test.close(); 430 431 test.execute(); 432 assert(!test.empty); 433 assert(test.front[0].get!long == 1); 434 } 435 436 { 437 Statement select = driver.prepare("SELECT `date`, `time`, `redundant` FROM `moment`"); 438 scope (exit) 439 select.close(); 440 441 select.execute(); 442 assert(select.front[0].get!Date == date); 443 assert(select.front[1].get!TimeOfDay == time); 444 assert(select.front[2].get!DateTime == DateTime(date, time)); 445 } 446 }