Unofficial Dub Package Documentation 
1 module tests.orm_mariadb.tests;
2 
3 import oceandrift.db.dbal;
4 import oceandrift.db.mariadb;
5 import oceandrift.db.orm;
6 
7 @safe:
8 
9 MariaDB getDB()
10 {
11     auto db = new MariaDB("localhost", "u_oceandrift", "p_oceandrift", null);
12     db.connect();
13     dropRecreateUseDB(db);
14     return db;
15 }
16 
17 void dropRecreateUseDB(MariaDB driver)
18 {
19     driver.execute("DROP DATABASE IF EXISTS `d_oceandrift`");
20     driver.execute("CREATE DATABASE `d_oceandrift`");
21     driver.execute("USE `d_oceandrift`");
22 }
23 
24 struct Person
25 {
26     ulong id = 0;
27     string name;
28     ubyte age;
29 }
30 
31 unittest
32 {
33     auto driver = getDB();
34     scope (exit)
35         driver.close();
36 
37     driver.execute(
38         "CREATE TABLE `person` (id INTEGER PRIMARY KEY AUTO_INCREMENT, name TEXT, age INTEGER UNSIGNED)"
39     );
40 
41     auto em = EntityManager!MariaDB(driver);
42 
43     {
44         Person p;
45         immutable bool success = driver.get!(Person)(1, p);
46         assert(!success);
47     }
48 
49     {
50         Person p;
51         immutable bool success = em.get!(Person)(1, p);
52         assert(!success);
53     }
54 
55     driver.execute(
56         "INSERT INTO `person` (name, age) VALUES ('Walter', 65), ('Tom', 30), ('Carl', 60)"
57     );
58 
59     {
60         Person p;
61         immutable bool success = em.get!Person(1, p);
62         assert(success);
63         assert(p.name == "Walter");
64         assert(p.age == 65);
65     }
66 
67     {
68         EntityCollection!Person ec =
69             em.find!Person()
70             .where("age", ComparisonOperator.greaterThanOrEquals, 60)
71             .select()
72             .via(driver);
73         assert(!ec.empty);
74 
75         Person p1 = ec.front;
76         assert(p1.name == "Walter");
77         assert(p1.age >= 60);
78 
79         ec.popFront();
80         assert(!ec.empty);
81 
82         Person p2 = ec.front;
83         assert(p2.name == "Carl", p2.name);
84         assert(p2.age >= 60);
85 
86         ec.popFront();
87         assert(ec.empty);
88     }
89 
90     {
91         EntityCollection!Person ec = em
92             .find!Person()
93             .where("age", ComparisonOperator.greaterThanOrEquals, 60)
94             .select()
95             .via(driver);
96         assert(!ec.empty);
97 
98         Person p1 = ec.front;
99         assert(p1.name == "Walter");
100         assert(p1.age >= 60);
101 
102         ec.popFront();
103         assert(!ec.empty);
104 
105         Person p2 = ec.front;
106         assert(p2.name == "Carl");
107         assert(p2.age >= 60);
108 
109         ec.popFront();
110         assert(ec.empty);
111     }
112 
113     {
114         EntityCollection!Person ec = em
115             .find!Person()
116             .whereParentheses((Query q) => q
117                     .where("age", ComparisonOperator.greaterThanOrEquals, 60)
118             )
119             .select()
120             .via(driver);
121         assert(!ec.empty);
122 
123         Person p1 = ec.front;
124         assert(p1.name == "Walter");
125         assert(p1.age >= 60);
126 
127         ec.popFront();
128         assert(!ec.empty);
129 
130         Person p2 = ec.front;
131         assert(p2.name == "Carl");
132         assert(p2.age >= 60);
133 
134         ec.popFront();
135         assert(ec.empty);
136     }
137 
138     {
139         EntityCollection!Person ec = em
140             .find!Person()
141             .whereParentheses(q => q
142                     .where("age", ComparisonOperator.greaterThanOrEquals, 60)
143                     .limit(1)
144             )
145             .select()
146             .via(driver);
147         assert(!ec.empty);
148 
149         Person p1 = ec.front;
150         assert(p1.name == "Walter");
151         assert(p1.age >= 60);
152 
153         ec.popFront();
154         assert(ec.empty);
155     }
156 
157     {
158         EntityCollection!Person ec = em
159             .find!Person()
160             .whereParentheses(
161                 (Query q) => q
162                     .where("age", ComparisonOperator.greaterThanOrEquals, 60)
163                     .limit(1, 1)
164             )
165             .select()
166             .via(driver);
167         assert(!ec.empty);
168 
169         Person p1 = ec.front;
170         assert(p1.name == "Carl");
171         assert(p1.age >= 60);
172 
173         ec.popFront();
174         assert(ec.empty);
175     }
176 
177     {
178         Person p;
179         immutable bool success = em.get!Person(2, p);
180         assert(success);
181         assert(p.name == "Tom");
182         assert(p.age == 30);
183 
184         em.remove(p);
185         Person p2;
186         immutable bool notRemoved = em.get!Person(2, p2);
187         assert(!notRemoved);
188 
189         Person p3;
190         immutable bool p3stillThere = em.get!Person(3, p3);
191         assert(p3stillThere);
192         assert(p3.name == "Carl");
193     }
194 
195     {
196         auto p = Person(0, "Peter", 45);
197 
198         em.save(p);
199         assert(p.id == 4); // 1-4 is used by previous rows
200 
201         Person tmp;
202         immutable personFound = em.get!Person(4, tmp);
203         assert(personFound);
204 
205         p.age += 1;
206         em.save(p);
207         assert(p.id == 4); // ID remains unchanged
208 
209         Person tmp2;
210         immutable personFoundAgain = em.get!Person(p.id, tmp2);
211         assert(personFoundAgain);
212         assert(tmp2.age == 46); // update propagated
213 
214         immutable idxCopy = em.store(p); // store a copy
215         assert(idxCopy == 5);
216 
217         em.update(p);
218     }
219 }
220 
221 class Mountain
222 {
223     ulong id = 0;
224     string name;
225     string location;
226     uint height;
227 
228     public this() // ORM
229     {
230     }
231 
232     public this(string name, string location, uint height)
233     {
234         this.name = name;
235         this.location = location;
236         this.height = height;
237     }
238 }
239 
240 static assert(isEntityType!Mountain);
241 
242 unittest
243 {
244     auto db = getDB();
245     scope (exit)
246         db.close();
247 
248     db.execute(
249         "CREATE TABLE `mountain` (
250             `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
251             `name` TEXT, location TEXT,
252             `height` INTEGER UNSIGNED
253         )"
254     );
255 
256     auto em = EntityManager!MariaDB(db);
257 
258     {
259         auto mt1 = new Mountain("Hill 1", "Nowhere", 3000);
260         em.save(mt1);
261         auto mt2 = new Mountain("Snowmountain", "Elsewhere", 4111);
262         em.save(mt2);
263         auto mt3 = new Mountain("Mt. Skyscrape", "Somewhere", 4987);
264         em.save(mt3);
265         auto mt4 = new Mountain("Little Hill", "Nowhere", 1200);
266         em.save(mt4);
267         auto mt5 = new Mountain("K2010", "Nowhere", 2010);
268         em.save(mt5);
269         auto mt6 = new Mountain("Icy Heights", "Elsewhere", 3201);
270         em.save(mt6);
271         auto mt7 = new Mountain("Mt. Nowhere", "Nowhere", 6408);
272         em.save(mt7);
273 
274         Statement stmt = db.prepare("SELECT COUNT(*) FROM mountain");
275         stmt.execute();
276         assert(stmt.front[0].getAs!int == 7);
277     }
278 
279     {
280         enum PreCollection!(Mountain, MariaDB) pcMt4000 =
281             em.find!Mountain()
282                 .where("height", ComparisonOperator.greaterThanOrEquals, 4000);
283 
284         enum BuiltPreCollection!Mountain bpcMt4000 = pcMt4000.select();
285 
286         EntityCollection!Mountain mt4000 = db.map(bpcMt4000);
287 
288         assert(!mt4000.empty);
289 
290         int n = 0;
291         foreach (Mountain mt; mt4000)
292         {
293             assert(mt.height >= 4000);
294             ++n;
295         }
296         assert(n == 3);
297     }
298 
299     {
300         static immutable int cap = 3000;
301         static immutable string loc = "Nowhere";
302         enum PreCollection!(Mountain, MariaDB) pc = em.find!Mountain()
303                 .where("height", '<', cap)
304                 .where("location", '=', loc);
305 
306         immutable ulong cnt = pc.countVia(db);
307         assert(cnt == 2);
308 
309         enum BuiltQuery qCntCT = pc.count();
310         Statement stmt = db.prepareBuiltQuery(qCntCT);
311         stmt.execute();
312         immutable cnt2 = stmt.front[0].getAs!ulong;
313         assert(cnt2 == 2);
314     }
315 
316     {
317         static immutable string loc = "Elsewhere";
318         enum PreCollection!(Mountain, MariaDB) pc = em.find!Mountain().where("location", '=', loc);
319 
320         immutable DBValue maxV = pc.aggregateVia(AggregateFunction.max, "height", db);
321 
322         immutable int max = maxV.getAs!int;
323         assert(max == 4111);
324     }
325 
326     {
327         BuiltQuery bq = em
328             .find!Mountain()
329             .aggregate(AggregateFunction.min, "height");
330 
331         Statement stmt = db.prepareBuiltQuery(bq);
332         stmt.execute();
333         assert(!stmt.empty);
334 
335         immutable int min = stmt.front[0].getAs!int;
336         assert(min == 1200);
337     }
338 
339     {
340         enum BuiltPreCollection!Mountain bpc = em
341                 .find!Mountain()
342                 .limit(1)
343                 .select();
344 
345         EntityCollection!Mountain ec = bpc.via(db);
346         assert(!ec.empty);
347 
348         ec.popFront();
349         assert(ec.empty);
350     }
351 
352     {
353         enum BuiltPreCollection!Mountain bpc = em
354                 .find!Mountain()
355                 .limit(3)
356                 .select();
357 
358         EntityCollection!Mountain ec = bpc.via(db);
359         assert(!ec.empty);
360 
361         ec.popFront();
362         assert(!ec.empty);
363 
364         ec.popFront();
365         assert(!ec.empty);
366 
367         ec.popFront();
368         assert(ec.empty);
369     }
370 
371     {
372         enum BuiltPreCollection!Mountain bpc = em
373                 .find!Mountain()
374                 .orderBy("height")
375                 .limit(2, 3)
376                 .select();
377 
378         EntityCollection!Mountain ec = bpc.via(db);
379         assert(!ec.empty);
380 
381         const Mountain m1 = ec.front;
382         assert(m1.height == 3201);
383 
384         ec.popFront();
385         assert(!ec.empty);
386         const Mountain m2 = ec.front;
387         assert(m2.name == "Snowmountain");
388 
389         ec.popFront();
390         assert(ec.empty);
391     }
392 
393     {
394         enum BuiltPreCollection!Mountain bpc = em
395             .find!Mountain()
396             .where("height", '<')
397             .orderBy("height")
398             .select();
399 
400         PreparedCollection!Mountain pc = bpc.prepareCollection(db);
401         pc.bind(0, 3000);
402         EntityCollection!Mountain ec = pc.execute();
403         assert(!ec.empty);
404 
405         const Mountain m1 = ec.front;
406         assert(m1.height == 1200);
407 
408         ec.popFront();
409         assert(!ec.empty);
410         const Mountain m2 = ec.front;
411         assert(m2.name == "K2010");
412 
413         ec.popFront();
414         assert(ec.empty);
415     }
416 
417     {
418         EntityCollection!Mountain ec = em
419             .find!Mountain()
420             .orderBy("height", desc)
421             .limit(1)
422             .selectVia(db);
423 
424         assert(!ec.empty);
425 
426         const Mountain highest = ec.front;
427         assert(highest.name == "Mt. Nowhere");
428 
429         ec.popFront();
430         assert(ec.empty);
431     }
432 
433     {
434         static immutable string loc = "Elsewhere";
435         enum PreCollection!(Mountain, MariaDB) pc = em.find!Mountain().where("location", '=', loc);
436         enum BuiltQuery bq = pc.delete_();
437 
438         Statement stmt = db.prepareBuiltQuery(bq);
439         stmt.execute();
440         assert(stmt.empty);
441     }
442 
443     {
444         em.find!Mountain().where("height", '<', 3000).deleteVia(db);
445 
446         ulong left = em.find!Mountain().countVia(db);
447         assert(left == 3);
448 
449         EntityCollection!Mountain mtsLeft = em.find!Mountain().orderBy("height").selectVia(db);
450 
451         assert(!mtsLeft.empty);
452         assert(mtsLeft.front.name == "Hill 1");
453 
454         mtsLeft.popFront();
455         assert(!mtsLeft.empty);
456         assert(mtsLeft.front.name == "Mt. Skyscrape");
457 
458         mtsLeft.popFront();
459         assert(!mtsLeft.empty);
460         assert(mtsLeft.front.name == "Mt. Nowhere");
461 
462         mtsLeft.popFront();
463         assert(mtsLeft.empty);
464     }
465 }
466 
467 unittest  // many2one + one2many
468 {
469     auto db = getDB();
470     scope (exit)
471         db.close();
472 
473     db.execute(
474         "CREATE TABLE `author` (
475             `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
476             `name` TEXT,
477             `birthday` DATETIME
478         );"
479     );
480 
481     db.execute(
482         "CREATE TABLE `book` (
483             `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
484             `name` TEXT,
485             `published` DATE,
486             `author_id` INTEGER,
487             FOREIGN KEY (`author_id`) REFERENCES `author`(`id`)
488         );"
489     );
490 
491     auto em = EntityManager!MariaDB(db);
492 
493     struct Author
494     {
495         string name;
496         mixin EntityID;
497     }
498 
499     struct Book
500     {
501         string name;
502         Date published;
503         ulong authorID;
504         mixin EntityID;
505     }
506 
507     {
508         auto adam = Author("Adam D. Ruppe");
509         em.save(adam);
510         auto someone = Author("Some One");
511         em.save(someone);
512         auto nobody = Author("No Body");
513         em.save(nobody);
514 
515         em.store(Book("D Cookbook", Date(2014, 5, 26), adam.id));
516         em.store(Book("Funny Things", Date(2016, 2, 4), someone.id));
517         em.store(Book("Very funny Things", Date(2017, 3, 3), someone.id));
518         em.store(Book("Fancy Things", Date(2017, 9, 9), someone.id));
519         em.store(Book("Other Things", Date(2018, 12, 20), someone.id));
520         em.store(Book("Stories", Date(2012, 7, 1), nobody.id));
521         em.store(Book("Stories, vol.2", Date(2018, 4, 8), nobody.id));
522         em.store(Book("Stories, vol.3", Date(2019, 8, 10), nobody.id));
523         em.store(Book("Much more Stories", Date(2020, 11, 11), nobody.id));
524     }
525 
526     {
527         Book b;
528         immutable bookFound = em.get(4, b);
529         assert(bookFound);
530 
531         Author a;
532         immutable authorFound = em.manyToOne!Author(b, a);
533         assert(authorFound);
534         assert(a.name == "Some One");
535 
536         assert(a.id == b.authorID);
537         assert(a.name == "Some One");
538     }
539 
540     {
541         auto authorsFound = em.find!Author.where("name", '=', "No Body").selectVia(db);
542         assert(!authorsFound.empty);
543 
544         Author a = authorsFound.front;
545         assert(a.name == "No Body");
546 
547         authorsFound.popFront();
548         assert(authorsFound.empty);
549 
550         auto pcBooks = em.oneToMany!Book(a);
551 
552         immutable ulong cntBooks = pcBooks.countVia(db);
553         assert(cntBooks == 4);
554 
555         EntityCollection!Book booksSince2019 = pcBooks
556             .where("published", '>', Date(2019, 1, 1))
557             .orderBy("published")
558             .selectVia(db);
559         assert(!booksSince2019.empty);
560 
561         assert(booksSince2019.front.authorID == a.id);
562         assert(booksSince2019.front.published.year >= 2019);
563         assert(booksSince2019.front.name == "Stories, vol.3");
564 
565         booksSince2019.popFront();
566         assert(!booksSince2019.empty);
567 
568         assert(booksSince2019.front.authorID == a.id);
569         assert(booksSince2019.front.published.year >= 2019);
570         assert(booksSince2019.front.name == "Much more Stories");
571 
572         booksSince2019.popFront();
573         assert(booksSince2019.empty);
574     }
575 }
576 
577 unittest  // many2many
578 {
579     auto db = getDB();
580     scope (exit)
581         db.close();
582 
583     struct Thing
584     {
585         string name;
586         mixin EntityID;
587     }
588 
589     struct Tag
590     {
591         string name;
592         mixin EntityID;
593     }
594 
595     assert(joinTableName!(Thing, Tag) == "tag_thing");
596     assert(joinTableName!(Tag, Thing) == "tag_thing");
597 
598     db.execute(
599         "CREATE TABLE `thing` (
600             `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
601             `name` TEXT
602         );"
603     );
604 
605     db.execute(
606         "CREATE TABLE `tag` (
607             `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
608             `name` TEXT
609         );"
610     );
611 
612     db.execute(
613         "CREATE TABLE `tag_thing` (
614             `tag_id` INTEGER NOT NULL,
615             `thing_id` INTEGER NOT NULL,
616             PRIMARY KEY (`tag_id`, `thing_id`),
617             FOREIGN KEY (`tag_id`) REFERENCES `tag`(`id`),
618             FOREIGN KEY (`thing_id`) REFERENCES `thing`(`id`)
619         );"
620     );
621 
622     auto em = EntityManager!MariaDB(db);
623 
624     auto fruit = Tag("Fruit");
625     em.save(fruit);
626     auto berry = Tag("Berry");
627     em.save(berry);
628     auto red = Tag("red");
629     em.save(red);
630 
631     auto apple = Thing("Apple");
632     em.save(apple);
633     em.manyToManyAssign(fruit, apple);
634     em.manyToManyAssign(red, apple);
635     auto pear = Thing("Pear");
636     em.save(pear);
637     em.manyToManyAssign(fruit, pear);
638     auto plum = Thing("Plum");
639     em.save(plum);
640     em.manyToManyAssign(fruit, plum);
641     auto banana = Thing("Banana");
642     em.save(banana);
643     em.manyToManyAssign(fruit, banana);
644     auto raspberry = Thing("Raspberry");
645     em.save(raspberry);
646     em.manyToManyAssign(raspberry, fruit);
647     em.manyToManyAssign(raspberry, berry);
648     auto blueberry = Thing("Blueberry");
649     em.save(blueberry);
650     em.manyToManyAssign(blueberry, fruit);
651     em.manyToManyAssign(blueberry, berry);
652     auto strawberry = Thing("Strawberry");
653     em.save(strawberry);
654     em.manyToManyAssign(strawberry, fruit);
655     em.manyToManyAssign(strawberry, berry);
656     em.manyToManyAssign(strawberry, red);
657     auto book = Thing("Book");
658     em.save(book);
659 
660     assert(em.manyToMany!Tag(apple).countVia(db) == 2);
661     assert(em.manyToMany!Tag(pear).countVia(db) == 1);
662     assert(em.manyToMany!Tag(strawberry).countVia(db) == 3);
663     assert(em.manyToMany!Tag(book).countVia(db) == 0);
664 
665     assert(em.manyToMany!Thing(fruit).countVia(db) == 7);
666     assert(em.manyToMany!Thing(berry).countVia(db) == 3);
667     assert(em.manyToMany!Thing(red).countVia(db) == 2);
668 
669     {
670         em.manyToManyAssign(book, fruit); // actually wrong…
671         assert(em.manyToMany!Tag(book).countVia(db) == 1);
672 
673         em.manyToManyUnassign(book, fruit); // …so unassign
674         assert(em.manyToMany!Tag(book).countVia(db) == 0);
675     }
676 }

Page generated by adrdox