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 }