1 /++ 2 Helper functions for generating database stuff. 3 4 Note: this is heavily biased toward Postgres 5 +/ 6 module arsd.database_generation; 7 8 /* 9 10 FIXME: support partial indexes and maybe "using" 11 FIXME: support views 12 13 Let's put indexes in there too and make index functions be the preferred way of doing a query 14 by making them convenient af. 15 */ 16 17 private enum UDA; 18 19 @UDA struct PrimaryKey { 20 string sql; 21 } 22 23 @UDA struct Default { 24 string sql; 25 } 26 27 @UDA struct Unique { } 28 29 @UDA struct ForeignKey(alias toWhat, string behavior) { 30 alias ReferencedTable = __traits(parent, toWhat); 31 } 32 33 enum CASCADE = "ON UPDATE CASCADE ON DELETE CASCADE"; 34 enum NULLIFY = "ON UPDATE CASCADE ON DELETE SET NULL"; 35 enum RESTRICT = "ON UPDATE CASCADE ON DELETE RESTRICT"; 36 37 @UDA struct DBName { string name; } 38 39 struct Nullable(T) { 40 bool isNull = true; 41 T value; 42 43 void opAssign(typeof(null)) { 44 isNull = true; 45 } 46 47 void opAssign(T v) { 48 isNull = false; 49 value = v; 50 } 51 52 T toArsdJsvar() { return value; } 53 54 string toString() { 55 import std.conv; 56 57 if (isNull) { 58 return "Nullable.null"; 59 } 60 else { 61 return to!string(this.value); 62 } 63 } 64 } 65 66 struct Timestamp { 67 string value; 68 string toArsdJsvar() { return value; } 69 70 // FIXME: timezone 71 static Timestamp fromStrings(string date, string time) { 72 if(time.length < 6) 73 time ~= ":00"; 74 import std.datetime; 75 return Timestamp(SysTime.fromISOExtString(date ~ "T" ~ time).toISOExtString()); 76 } 77 } 78 79 SysTime parseDbTimestamp(Timestamp s) { 80 return parseDbTimestamp(s.value); 81 } 82 83 SysTime parseDbTimestamp(string s) { 84 if(s.length == 0) return SysTime.init; 85 auto date = s[0 .. 10]; 86 auto time = s[11 .. 20]; 87 auto tz = s[20 .. $]; 88 return SysTime.fromISOExtString(date ~ "T" ~ time ~ tz); 89 } 90 91 struct Constraint(string sql) {} 92 93 struct Index(Fields...) {} 94 struct UniqueIndex(Fields...) {} 95 96 struct Serial { 97 int value; 98 int toArsdJsvar() { return value; } 99 int getValue() { return value; } 100 alias getValue this; 101 } 102 103 104 string generateCreateTableFor(alias O)() { 105 enum tableName = tableNameFor!O(); 106 string sql = "CREATE TABLE " ~ tableName ~ " ("; 107 string postSql; 108 bool outputtedPostSql = false; 109 110 string afterTableSql; 111 112 void addAfterTableSql(string s) { 113 afterTableSql ~= s; 114 afterTableSql ~= "\n"; 115 } 116 117 void addPostSql(string s) { 118 if(outputtedPostSql) { 119 postSql ~= ","; 120 } 121 postSql ~= "\n"; 122 postSql ~= "\t" ~ s; 123 outputtedPostSql = true; 124 } 125 126 bool outputted = false; 127 static foreach(memberName; __traits(allMembers, O)) {{ 128 alias member = __traits(getMember, O, memberName); 129 static if(is(typeof(member) == Constraint!constraintSql, string constraintSql)) { 130 version(dbgenerate_sqlite) {} else { // FIXME: make it work here too, it is the specifics of the constraint strings 131 if(outputted) { 132 sql ~= ","; 133 } 134 sql ~= "\n"; 135 sql ~= "\tCONSTRAINT " ~ memberName; 136 sql ~= " "; 137 sql ~= constraintSql; 138 outputted = true; 139 } 140 } else static if(is(typeof(member) == Index!Fields, Fields...)) { 141 string fields = ""; 142 static foreach(field; Fields) { 143 if(fields.length) 144 fields ~= ", "; 145 fields ~= __traits(identifier, field); 146 } 147 addAfterTableSql("CREATE INDEX " ~ tableName ~ "_" ~ memberName ~ " ON " ~ tableName ~ "("~fields~")"); 148 } else static if(is(typeof(member) == UniqueIndex!Fields, Fields...)) { 149 string fields = ""; 150 static foreach(field; Fields) { 151 if(fields.length) 152 fields ~= ", "; 153 fields ~= __traits(identifier, field); 154 } 155 addAfterTableSql("CREATE UNIQUE INDEX " ~ tableName ~ "_" ~ memberName ~ " ON " ~ tableName ~ "("~fields~")"); 156 } else static if(is(typeof(member) T)) { 157 if(outputted) { 158 sql ~= ","; 159 } 160 sql ~= "\n"; 161 sql ~= "\t" ~ memberName; 162 163 static if(is(T == Nullable!P, P)) { 164 static if(is(P == int)) 165 sql ~= " INTEGER NULL"; 166 else static if(is(P == string)) 167 sql ~= " TEXT NULL"; 168 else static if(is(P == double)) 169 sql ~= " FLOAT NULL"; 170 else static if(is(P == Timestamp)) 171 sql ~= " TIMESTAMPTZ NULL"; 172 else static assert(0, P.stringof); 173 } else static if(is(T == int)) 174 sql ~= " INTEGER NOT NULL"; 175 else static if(is(T == Serial)) { 176 version(dbgenerate_sqlite) 177 sql ~= " INTEGER PRIMARY KEY AUTOINCREMENT"; 178 else 179 sql ~= " SERIAL"; // FIXME postgresism 180 } else static if(is(T == string)) 181 sql ~= " TEXT NOT NULL"; 182 else static if(is(T == double)) 183 sql ~= " FLOAT NOT NULL"; 184 else static if(is(T == bool)) 185 sql ~= " BOOLEAN NOT NULL"; 186 else static if(is(T == Timestamp)) { 187 version(dbgenerate_sqlite) 188 sql ~= " TEXT NOT NULL"; 189 else 190 sql ~= " TIMESTAMPTZ NOT NULL"; // FIXME: postgresism 191 } else static if(is(T == enum)) 192 sql ~= " INTEGER NOT NULL"; // potentially crap but meh 193 194 static foreach(attr; __traits(getAttributes, member)) { 195 static if(is(typeof(attr) == Default)) { 196 // FIXME: postgresism there, try current_timestamp in sqlite 197 version(dbgenerate_sqlite) { 198 import std.string; 199 sql ~= " DEFAULT " ~ std..string.replace(attr.sql, "now()", "current_timestamp"); 200 } else 201 sql ~= " DEFAULT " ~ attr.sql; 202 } else static if(is(attr == Unique)) { 203 sql ~= " UNIQUE"; 204 } else static if(is(attr == PrimaryKey)) { 205 version(dbgenerate_sqlite) { 206 static if(is(T == Serial)) {} // skip, it is done above 207 else 208 addPostSql("PRIMARY KEY(" ~ memberName ~ ")"); 209 } else 210 addPostSql("PRIMARY KEY(" ~ memberName ~ ")"); 211 } else static if(is(attr == ForeignKey!(to, sqlPolicy), alias to, string sqlPolicy)) { 212 string refTable = tableNameFor!(__traits(parent, to))(); 213 string refField = to.stringof; 214 addPostSql("FOREIGN KEY(" ~ memberName ~ ") REFERENCES "~refTable~"("~refField~(sqlPolicy.length ? ") " : ")") ~ sqlPolicy); 215 } 216 } 217 218 outputted = true; 219 } 220 }} 221 222 if(postSql.length && outputted) 223 sql ~= ",\n"; 224 225 sql ~= postSql; 226 sql ~= "\n);\n"; 227 sql ~= afterTableSql; 228 229 return sql; 230 } 231 232 string tableNameFor(T)(string def = toTableName(T.stringof)) { 233 foreach(attr; __traits(getAttributes, T)) 234 static if(is(typeof(attr) == DBName)) 235 def = attr.name; 236 return def; 237 } 238 239 string toTableName(string t) { 240 return plural(50, beautify(t, '_', true)); 241 } 242 243 // copy/pasted from english.d 244 private string plural(int count, string word, string pluralWord = null) { 245 if(count == 1 || word.length == 0) 246 return word; // it isn't actually plural 247 248 if(pluralWord !is null) 249 return pluralWord; 250 251 switch(word[$ - 1]) { 252 case 's': 253 return word ~ "es"; 254 case 'f': 255 return word[0 .. $-1] ~ "ves"; 256 case 'y': 257 return word[0 .. $-1] ~ "ies"; 258 case 'a', 'e', 'i', 'o', 'u': 259 default: 260 return word ~ "s"; 261 } 262 } 263 264 // copy/pasted from cgi 265 private string beautify(string name, char space = ' ', bool allLowerCase = false) { 266 if(name == "id") 267 return allLowerCase ? name : "ID"; 268 269 char[160] buffer; 270 int bufferIndex = 0; 271 bool shouldCap = true; 272 bool shouldSpace; 273 bool lastWasCap; 274 foreach(idx, char ch; name) { 275 if(bufferIndex == buffer.length) return name; // out of space, just give up, not that important 276 277 if((ch >= 'A' && ch <= 'Z') || ch == '_') { 278 if(lastWasCap) { 279 // two caps in a row, don't change. Prolly acronym. 280 } else { 281 if(idx) 282 shouldSpace = true; // new word, add space 283 } 284 285 lastWasCap = true; 286 } else { 287 lastWasCap = false; 288 } 289 290 if(shouldSpace) { 291 buffer[bufferIndex++] = space; 292 if(bufferIndex == buffer.length) return name; // out of space, just give up, not that important 293 shouldSpace = false; 294 } 295 if(shouldCap) { 296 if(ch >= 'a' && ch <= 'z') 297 ch -= 32; 298 shouldCap = false; 299 } 300 if(allLowerCase && ch >= 'A' && ch <= 'Z') 301 ch += 32; 302 buffer[bufferIndex++] = ch; 303 } 304 return buffer[0 .. bufferIndex].idup; 305 } 306 307 import arsd.database; 308 /++ 309 310 +/ 311 void save(O)(ref O t, Database db) { 312 t.insert(db); 313 } 314 315 /++ 316 317 +/ 318 void insert(O)(ref O t, Database db) { 319 auto builder = new InsertBuilder; 320 builder.setTable(tableNameFor!O()); 321 322 static foreach(memberName; __traits(allMembers, O)) {{ 323 alias member = __traits(getMember, O, memberName); 324 static if(is(typeof(member) T)) { 325 326 static if(is(T == Nullable!P, P)) { 327 auto v = __traits(getMember, t, memberName); 328 if(v.isNull) 329 builder.addFieldWithSql(memberName, "NULL"); 330 else 331 builder.addVariable(memberName, v.value); 332 } else static if(is(T == int)) 333 builder.addVariable(memberName, __traits(getMember, t, memberName)); 334 else static if(is(T == Serial)) { 335 auto v = __traits(getMember, t, memberName).value; 336 if(v) { 337 builder.addVariable(memberName, v); 338 } else { 339 // skip and let it auto-fill 340 } 341 } else static if(is(T == string)) { 342 builder.addVariable(memberName, __traits(getMember, t, memberName)); 343 } else static if(is(T == double)) 344 builder.addVariable(memberName, __traits(getMember, t, memberName)); 345 else static if(is(T == bool)) 346 builder.addVariable(memberName, __traits(getMember, t, memberName)); 347 else static if(is(T == Timestamp)) { 348 auto v = __traits(getMember, t, memberName).value; 349 if(v.length) 350 builder.addVariable(memberName, v); 351 } else static if(is(T == enum)) 352 builder.addVariable(memberName, cast(int) __traits(getMember, t, memberName)); 353 } 354 }} 355 356 import std.conv; 357 version(dbgenerate_sqlite) { 358 builder.execute(db); 359 foreach(row; db.query("SELECT max(id) FROM " ~ tableNameFor!O())) 360 t.id.value = to!int(row[0]); 361 } else { 362 static if (__traits(hasMember, O, "id")) 363 { 364 foreach(row; builder.execute(db, "RETURNING id")) // FIXME: postgres-ism 365 t.id.value = to!int(row[0]); 366 } 367 else 368 { 369 builder.execute(db); 370 } 371 } 372 } 373 374 // Check that insert doesn't require an `id` 375 unittest 376 { 377 static struct NoPK 378 { 379 int a; 380 } 381 382 alias test = insert!NoPK; 383 } 384 /// 385 class RecordNotFoundException : Exception { 386 this() { super("RecordNotFoundException"); } 387 } 388 389 /++ 390 Returns a given struct populated from the database. Assumes types known to this module. 391 392 MyItem item = db.find!(MyItem.id)(3); 393 394 If you just give a type, it assumes the relevant index is "id". 395 396 +/ 397 static auto find(alias T)(Database db, int id) { 398 // FIXME: 399 // if it is unique, return an individual item. 400 // if not, return the array 401 static if (!is(T)) { 402 static const string fieldName = T.stringof; 403 alias FType = typeof(T); // field type 404 alias TType = __traits(parent, T); // Table type 405 } 406 else { 407 static const string fieldName = "id"; 408 alias FType = int; 409 alias TType = T; 410 } 411 412 static assert(is(FType : int), 413 TType.stringof ~ "." ~ fieldName ~ " should be an Integral field"); 414 415 string q = "SELECT * FROM " ~ tableNameFor!TType() ~ " WHERE " ~ fieldName ~ " = ?"; 416 foreach(record; db.query(q, id)) { 417 TType t; 418 populateFromDbRow(t, record); 419 420 return t; 421 // if there is ever a second record, that's a wtf, but meh. 422 } 423 throw new RecordNotFoundException(); 424 } 425 426 private void populateFromDbRow(T)(ref T t, Row record) { 427 foreach(field, value; record) { 428 sw: switch(field) { 429 static foreach(const idx, alias mem; T.tupleof) { 430 case __traits(identifier, mem): 431 populateFromDbVal(t.tupleof[idx], value); 432 break sw; 433 } 434 default: 435 // intentionally blank 436 } 437 } 438 } 439 440 private void populateFromDbVal(V)(ref V val, string value) { 441 import std.conv; 442 static if(is(V == Constraint!constraintSql, string constraintSql)) { 443 444 } else static if(is(V == Nullable!P, P)) { 445 // FIXME 446 if(value.length && value != "null") { 447 val.isNull = false; 448 val.value = to!P(value); 449 } 450 } else static if(is(V == bool)) { 451 val = value == "t" || value == "1" || value == "true"; 452 } else static if(is(V == int) || is(V == string) || is(V == double)) { 453 val = to!V(value); 454 } else static if(is(V == enum)) { 455 val = cast(V) to!int(value); 456 } else static if(is(V == Timestamp)) { 457 val.value = value; 458 } else static if(is(V == Serial)) { 459 val.value = to!int(value); 460 } 461 } 462 463 unittest 464 { 465 static struct SomeStruct 466 { 467 int a; 468 void foo() {} 469 int b; 470 } 471 472 auto rs = new PredefinedResultSet( 473 [ "a", "b" ], 474 [ Row([ "1", "2" ]) ] 475 ); 476 477 SomeStruct s; 478 populateFromDbRow(s, rs.front); 479 480 assert(s.a == 1); 481 assert(s.b == 2); 482 } 483 /++ 484 Gets all the children of that type. Specifically, it looks in T for a ForeignKey referencing B and queries on that. 485 486 To do a join through a many-to-many relationship, you could get the children of the join table, then get the children of that... 487 Or better yet, use real sql. This is more intended to get info where there is one parent row and then many child 488 rows, not for a combined thing. 489 +/ 490 QueryBuilderHelper!(T[]) children(T, B)(B base) { 491 int countOfAssociations() { 492 int count = 0; 493 static foreach(memberName; __traits(allMembers, T)) 494 static foreach(attr; __traits(getAttributes, __traits(getMember, T, memberName))) {{ 495 static if(is(attr == ForeignKey!(K, policy), alias K, string policy)) { 496 static if(is(attr.ReferencedTable == B)) 497 count++; 498 } 499 }} 500 return count; 501 } 502 static assert(countOfAssociations() == 1, T.stringof ~ " does not have exactly one foreign key of type " ~ B.stringof); 503 string keyName() { 504 static foreach(memberName; __traits(allMembers, T)) 505 static foreach(attr; __traits(getAttributes, __traits(getMember, T, memberName))) {{ 506 static if(is(attr == ForeignKey!(K, policy), alias K, string policy)) { 507 static if(is(attr.ReferencedTable == B)) 508 return memberName; 509 } 510 }} 511 } 512 513 // return QueryBuilderHelper!(T[])(toTableName(T.stringof)).where!(mixin(keyName ~ " => base.id")); 514 515 // changing mixin cuz of regression in dmd 2.088 516 mixin("return QueryBuilderHelper!(T[])(tableNameFor!T()).where!("~keyName ~ " => base.id);"); 517 } 518 519 /++ 520 Finds the single row associated with a foreign key in `base`. 521 522 `T` is used to find the key, unless ambiguous, in which case you must pass `key`. 523 524 To do a join through a many-to-many relationship, go to [children] or use real sql. 525 +/ 526 T associated(B, T, string key = null)(B base, Database db) { 527 int countOfAssociations() { 528 int count = 0; 529 static foreach(memberName; __traits(allMembers, B)) 530 static foreach(attr; __traits(getAttributes, __traits(getMember, B, memberName))) { 531 static if(is(attr == ForeignKey!(K, policy), alias K, string policy)) { 532 static if(is(attr.ReferencedTable == T)) 533 static if(key is null || key == memberName) 534 count++; 535 } 536 } 537 return count; 538 } 539 540 static if(key is null) { 541 enum coa = countOfAssociations(); 542 static assert(coa != 0, B.stringof ~ " has no association of type " ~ T); 543 static assert(coa == 1, B.stringof ~ " has multiple associations of type " ~ T ~ "; please specify the key you want"); 544 static foreach(memberName; __traits(allMembers, B)) 545 static foreach(attr; __traits(getAttributes, __traits(getMember, B, memberName))) { 546 static if(is(attr == ForeignKey!(K, policy), alias K, string policy)) { 547 static if(is(attr.ReferencedTable == T)) 548 return db.find!T(__traits(getMember, base, memberName)); 549 } 550 } 551 } else { 552 static assert(countOfAssociations() == 1, B.stringof ~ " does not have a key named " ~ key ~ " of type " ~ T); 553 static foreach(attr; __traits(getAttributes, __traits(getMember, B, memberName))) { 554 static if(is(attr == ForeignKey!(K, policy), alias K, string policy)) { 555 static if(is(attr.ReferencedTable == T)) { 556 return db.find!T(__traits(getMember, base, key)); 557 } 558 } 559 } 560 assert(0); 561 } 562 } 563 564 565 /++ 566 It will return an aggregate row with a member of type of each table in the join. 567 568 Could do an anonymous object for other things in the sql... 569 +/ 570 auto join(TableA, TableB, ThroughTable = void)() {} 571 572 /++ 573 574 +/ 575 struct QueryBuilderHelper(T) { 576 static if(is(T == R[], R)) 577 alias TType = R; 578 else 579 alias TType = T; 580 581 SelectBuilder selectBuilder; 582 583 this(string tableName) { 584 selectBuilder = new SelectBuilder(); 585 selectBuilder.table = tableName; 586 selectBuilder.fields = ["*"]; 587 } 588 589 T execute(Database db) { 590 selectBuilder.db = db; 591 static if(is(T == R[], R)) { 592 593 } else { 594 selectBuilder.limit = 1; 595 } 596 597 T ret; 598 bool first = true; 599 foreach(row; db.query(selectBuilder.toString())) { 600 TType t; 601 populateFromDbRow(t, row); 602 603 static if(is(T == R[], R)) 604 ret ~= t; 605 else { 606 if(first) { 607 ret = t; 608 first = false; 609 } else { 610 assert(0); 611 } 612 } 613 } 614 return ret; 615 } 616 617 /// 618 typeof(this) orderBy(string criterion)() { 619 string name() { 620 int idx = 0; 621 while(idx < criterion.length && criterion[idx] != ' ') 622 idx++; 623 return criterion[0 .. idx]; 624 } 625 626 string direction() { 627 int idx = 0; 628 while(idx < criterion.length && criterion[idx] != ' ') 629 idx++; 630 import std.string; 631 return criterion[idx .. $].strip; 632 } 633 634 static assert(is(typeof(__traits(getMember, TType, name()))), TType.stringof ~ " has no field " ~ name()); 635 static assert(direction().length == 0 || direction() == "ASC" || direction() == "DESC", "sort direction must be empty, ASC, or DESC"); 636 637 selectBuilder.orderBys ~= criterion; 638 return this; 639 } 640 } 641 642 QueryBuilderHelper!(T[]) from(T)() { 643 return QueryBuilderHelper!(T[])(tableNameFor!T()); 644 } 645 646 /// ditto 647 template where(conditions...) { 648 Qbh where(Qbh)(Qbh this_, string[] sqlCondition...) { 649 assert(this_.selectBuilder !is null); 650 651 static string extractName(string s) { 652 if(s.length == 0) assert(0); 653 auto i = s.length - 1; 654 while(i) { 655 if(s[i] == ')') { 656 // got to close paren, now backward to non-identifier char to get name 657 auto end = i; 658 while(i) { 659 if(s[i] == ' ') 660 return s[i + 1 .. end]; 661 i--; 662 } 663 assert(0); 664 } 665 i--; 666 } 667 assert(0); 668 } 669 670 static foreach(idx, cond; conditions) {{ 671 // I hate this but __parameters doesn't work here for some reason 672 // see my old thread: https://forum.dlang.org/post/awjuoemsnmxbfgzhgkgx@forum.dlang.org 673 enum name = extractName(typeof(cond!int).stringof); 674 auto value = cond(null); 675 676 // FIXME: convert the value as necessary 677 static if(is(typeof(value) == Serial)) 678 auto dbvalue = value.value; 679 else static if(is(typeof(value) == enum)) 680 auto dbvalue = cast(int) value; 681 else 682 auto dbvalue = value; 683 684 import std.conv; 685 686 static assert(is(typeof(__traits(getMember, Qbh.TType, name))), Qbh.TType.stringof ~ " has no member " ~ name); 687 static if(is(typeof(__traits(getMember, Qbh.TType, name)) == int)) { 688 static if(is(typeof(value) : const(int)[])) { 689 string s; 690 foreach(v; value) { 691 if(s.length) s ~= ", "; 692 s ~= to!string(v); 693 } 694 this_.selectBuilder.wheres ~= name ~ " IN (" ~ s ~ ")"; 695 } else { 696 static assert(is(typeof(value) : const(int)) || is(typeof(value) == Serial), Qbh.TType.stringof ~ " is a integer key, but you passed an incompatible " ~ typeof(value).stringof); 697 698 auto placeholder = "?_internal" ~ to!string(idx); 699 this_.selectBuilder.wheres ~= name ~ " = " ~ placeholder; 700 this_.selectBuilder.setVariable(placeholder, dbvalue); 701 } 702 } else static if(is(typeof(__traits(getMember, Qbh.TType, name)) == Nullable!int)) { 703 static if(is(typeof(value) : const(int)[])) { 704 string s; 705 foreach(v; value) { 706 if(s.length) s ~= ", "; 707 s ~= to!string(v); 708 } 709 this_.selectBuilder.wheres ~= name ~ " IN (" ~ s ~ ")"; 710 } else { 711 static assert(is(typeof(value) : const(int)) || is(typeof(value) == Serial), Qbh.TType.stringof ~ " is a integer key, but you passed an incompatible " ~ typeof(value).stringof); 712 713 auto placeholder = "?_internal" ~ to!string(idx); 714 this_.selectBuilder.wheres ~= name ~ " = " ~ placeholder; 715 this_.selectBuilder.setVariable(placeholder, dbvalue); 716 } 717 } else static if(is(typeof(__traits(getMember, Qbh.TType, name)) == Serial)) { 718 static if(is(typeof(value) : const(int)[])) { 719 string s; 720 foreach(v; value) { 721 if(s.length) s ~= ", "; 722 s ~= to!string(v); 723 } 724 this_.selectBuilder.wheres ~= name ~ " IN (" ~ s ~ ")"; 725 } else { 726 static assert(is(typeof(value) : const(int)) || is(typeof(value) == Serial), Qbh.TType.stringof ~ " is a integer key, but you passed an incompatible " ~ typeof(value).stringof); 727 728 auto placeholder = "?_internal" ~ to!string(idx); 729 this_.selectBuilder.wheres ~= name ~ " = " ~ placeholder; 730 this_.selectBuilder.setVariable(placeholder, dbvalue); 731 } 732 733 734 } else { 735 static assert(is(typeof(__traits(getMember, Qbh.TType, name)) == typeof(value)), Qbh.TType.stringof ~ "." ~ name ~ " is not of type " ~ typeof(value).stringof); 736 737 auto placeholder = "?_internal" ~ to!string(idx); 738 this_.selectBuilder.wheres ~= name ~ " = " ~ placeholder; 739 this_.selectBuilder.setVariable(placeholder, dbvalue); 740 } 741 }} 742 743 this_.selectBuilder.wheres ~= sqlCondition; 744 return this_; 745 } 746 } 747 748 // Basically a wrapper for a ResultSet 749 struct TabResultSet(T) 750 { 751 this(ResultSet result) 752 { 753 this.result = result; 754 } 755 756 bool empty() @property 757 { 758 return this.result.empty; 759 } 760 761 T front() @property 762 { 763 T row; 764 row.populateFromDbRow(this.result.front); 765 return row; 766 } 767 768 void popFront() 769 { 770 this.result.popFront(); 771 } 772 773 size_t length() @property 774 { 775 return this.result.length; 776 } 777 778 private ResultSet result; 779 } 780 781 // ditto 782 TabResultSet!T to_table_rows(T)(ResultSet res) 783 { 784 return TabResultSet!T(res); 785 } 786 787 private template FieldReference(alias field_) 788 { 789 alias Table = __traits(parent, field_); 790 alias field = field_; 791 } 792 793 private template isFieldRefInAttributes(Attributes...) 794 { 795 static if (Attributes.length == 0) { 796 static immutable bool isFieldRefInAttributes = false; 797 } 798 else { 799 alias attr = Attributes[0]; 800 static if (is(attr == ForeignKey!(field, s), alias field, string s)) { 801 static immutable bool isFieldRefInAttributes = true; 802 } 803 else { 804 static immutable bool fieldRefInAttributes = 805 isFieldRefInAttributes!(Attributes[1..$]); 806 } 807 } 808 } 809 810 private template getFieldRefInAttributes(Attributes...) 811 { 812 alias attr = Attributes[0]; 813 static if (is(attr == ForeignKey!(field, s), alias field, string s)) { 814 alias getFieldRefInAttributes = FieldReference!(field); 815 } 816 else { 817 alias fieldRefInAttributes = 818 getFieldRefInAttributes!(RT, Attributes[1..$]); 819 } 820 } 821 822 private alias getRefToField(alias fk_field) = 823 getFieldRefInAttributes!(__traits(getAttributes, fk_field)); 824 825 unittest 826 { 827 struct Role { int id; } 828 829 struct User 830 { 831 int id; 832 @ForeignKey!(Role.id, "") int role_id; 833 } 834 835 alias FieldRef = getRefToField!(User.role_id); 836 assert(is(FieldRef.Table == Role)); 837 assert(__traits(isSame, FieldRef.field, Role.id)); 838 } 839 840 string toFieldName(T)(string s, bool isPlural = false) 841 { 842 int cnt = isPlural ? 2 : 1; 843 if (s is null) 844 return plural(cnt, beautify(tableNameFor!T(), '_', true)); 845 return s; 846 } 847 848 /++ 849 generates get functions for a one-to-many relationship with the form 850 `T2 get_<t2>(T1 row, Database db)` and 851 `TabResultSet!T1 get_<t1>(T2 row, Database db)` 852 853 854 [children] also works with a one-to-many relationship, but they are different in that [children] only gives you the many in the one-to-many relationship and only works with a single foreign key at a time. 855 856 Say you have a User and Role tables where each User has a role and a Role can be used by multiple users, with: 857 858 --- 859 /* 860 This would give you all of the users with the Role `role`. 861 */ 862 auto res = role.children!(User, Role).execute(db); 863 --- 864 865 However if you wanted to get the Role of a user there would be no way of doing so with children. It doesn't work the other way around. 866 867 Also the big thing that one_to_many can do and children can not do is handle multiple relationships(Multiple foreign keys pointing to the same Table for example: 868 869 --- 870 import std.stdio; 871 import arsd.sqlite; 872 import arsd.database_generation; 873 874 alias FK(alias toWhat) = ForeignKey!(toWhat, null); 875 876 @DBName("Professor") struct Professor 877 { 878 int id; 879 string name; 880 } 881 882 @DBName("Course") struct Course 883 { 884 int id; 885 @FK!(Professor.id) int professor_id; 886 @FK!(Professor.id) int assistant_id; 887 } 888 889 mixin(one_to_many!(Course.professor_id, "prof", "courses_taught")); 890 mixin(one_to_many!(Course.assistant_id, "assistant", "courses_assisted")); 891 892 void main() 893 { 894 Database db = new Sqlite("test2.db"); 895 896 Course course = db.find!Course(1); 897 Professor prof = course.get_prof(db); 898 899 writeln(prof.get_courses_taught(db)); 900 writeln(prof.get_courses_assisted(db)); 901 } 902 --- 903 904 Here there are 2 relationships from Course to Professor here. One of them you can get from get_courses_taught and the other one with get_courses_assisted. 905 If you attempt to use children like so 906 907 --- 908 writeln(prof.children!(Course, Professor).execute(db)); 909 --- 910 911 You would get: 912 $(CONSOLE 913 source/arsd/database_generation.d(489,2): Error: static assert: "Course does not have exactly one foreign key of type Professor" 914 ) 915 916 In conclusion, children is nice in that its simple, doesn't require mixins to create extra symbols(functions). However it doesn't handle the one in one-to-many relationships at all, and it also doesn't work in tables with more than one relationship to a table. And finally, you might prefer the syntax of `prof.get_courses(db)` over `prof.children!(Course, Professor).execute(db)`. 917 918 Examples: 919 920 --- 921 Struct Role { int id; } 922 struct User { 923 @ForeignKey!(Role.id, "") int role_id; 924 } 925 926 mixin(one_to_many!(User.role_id, "role", "users")); 927 void main() 928 { 929 Database db = ... 930 User user = db.find!User(1); 931 Role role = user.get_role(db); 932 auto users = role.get_users(db); 933 } 934 --- 935 936 if t2 or t1 are set as "" the get function will not be generated 937 (the name will not be inferred), if set as null they will be inferred from 938 either the `DBName` attribute or from the name of the Table. 939 940 History: 941 Added November 5, 2022 (dub v10.10) 942 +/ 943 template one_to_many(alias fk_field, string t2 = null, string t1 = null) 944 { 945 alias T1 = __traits(parent, fk_field); 946 947 static assert( 948 isFieldRefInAttributes!(__traits(getAttributes, fk_field)), 949 T1.stringof ~ "." ~ fk_field.stringof ~ " does't have a ForeignKey"); 950 951 alias FieldRef = getRefToField!(fk_field); 952 alias T2 = FieldRef.Table; 953 alias ref_field = FieldRef.field; 954 955 immutable string t2_name = toFieldName!T2(t2); 956 immutable string t1_name = toFieldName!T1(t1, true); 957 958 static immutable string one = (t2 is "") ? "" : 959 T2.stringof~` get_`~t2_name~`(`~T1.stringof~` row, Database db) 960 { 961 import std.exception; 962 963 enforce(db !is null, "Database must not be null"); 964 auto fk_id = row.`~fk_field.stringof~`; 965 966 auto res = db.query( 967 "select * from `~tableNameFor!T2()~`" ~ 968 " where `~ref_field.stringof~` = ?", fk_id 969 ).to_table_rows!`~T2.stringof~`; 970 971 return res.front(); 972 }`; 973 static immutable string many = (t1 is "") ? "" : ` 974 TabResultSet!`~T1.stringof~` get_`~t1_name~`(`~T2.stringof~` row, Database db) 975 { 976 import std.exception; 977 978 enforce(db !is null, "Database must not be null"); 979 auto id = row.`~ref_field.stringof~`; 980 981 auto res = db.query( 982 "select * from `~tableNameFor!T1()~`"~ 983 " where `~fk_field.stringof~` = ?", id 984 ).to_table_rows!`~T1.stringof~`; 985 986 return res; 987 }`; 988 static immutable string one_to_many = one ~ many; 989 }