1 /++ 2 Generic interface for RDBMS access. Use with one of the implementations in [arsd.mysql], [arsd.sqlite], [arsd.postgres], or [arsd.mssql]. I'm sorry the docs are not good, but a little bit goes a long way: 3 4 --- 5 auto db = new Sqlite("file.db"); // see the implementations for constructors 6 // then the interface, for any impl can be as simple as: 7 8 foreach(row; db.query("SELECT id, name FROM people")) { 9 string id = row[0]; 10 string name = row[1]; 11 } 12 13 db.query("INSERT INTO people (id, name) VALUES (?, ?)", 5, "Adam"); 14 --- 15 16 To convert to other types, just use [std.conv.to] since everything comes out of this as simple strings with the exception of binary data, 17 which you'll want to cast to const(ubyte)[]. 18 19 History: 20 Originally written prior to 2011. 21 22 On August 2, 2022, the behavior of BLOB (or BYTEA in postgres) changed significantly. 23 Before, it would convert to strings with `to!string(bytes)` on insert and platform specific 24 on query. It didn't really work at all. 25 26 It now actually stores ubyte[] as a blob and retrieves it without modification. Note you need to 27 cast it. 28 29 This is potentially breaking, but since it didn't work much before I doubt anyone was using it successfully 30 but this might be a problem. I advise you to retest. 31 32 Be aware I don't like this string interface much anymore and want to change it significantly but idk 33 how to work it in without breaking a decade of code. 34 35 On June 7, 2023 (dub 11.0), I started the process of moving away from strings as the inner storage unit. This is a potentially breaking change, but you can use `.toString` to convert as needed and `alias this` will try to do this automatically in many situations. See [DatabaseDatum] for details. This transition is not yet complete. 36 +/ 37 module arsd.database; 38 39 // I should do a prepared statement as a template string arg 40 41 public import std.variant; 42 import std.string; 43 public import std.datetime; 44 45 static import arsd.core; 46 private import arsd.core : LimitedVariant; 47 48 /* 49 Database 2.0 plan, WIP: 50 51 // Do I want to do some kind of RAII? 52 auto database = Database(new MySql("connection info")); 53 54 * Prepared statement support 55 * Queries with separate args whenever we can with consistent interface 56 * Query returns some typed info when we can. 57 * ....? 58 59 60 PreparedStatement prepareStatement(string sql); 61 62 Might be worth looking at doing the preparations in static ctors 63 so they are always done once per program... 64 */ 65 66 /// 67 interface Database { 68 /// Actually implements the query for the database. The query() method 69 /// below might be easier to use. 70 ResultSet queryImpl(string sql, Variant[] args...); 71 72 /// Escapes data for inclusion into an sql string literal 73 string escape(string sqlData); 74 /// Escapes binary data for inclusion into a sql string. Note that unlike `escape`, the returned string here SHOULD include the quotes. 75 string escapeBinaryString(const(ubyte)[] sqlData); 76 77 /// query to start a transaction, only here because sqlite is apparently different in syntax... 78 void startTransaction(); 79 80 /// Just executes a query. It supports placeholders for parameters 81 final ResultSet query(T...)(string sql, T t) { 82 Variant[] args; 83 foreach(arg; t) { 84 Variant a; 85 static if(__traits(compiles, a = arg)) 86 a = arg; 87 else 88 a = to!string(t); 89 args ~= a; 90 } 91 return queryImpl(sql, args); 92 } 93 94 /// turns a systime into a value understandable by the target database as a timestamp to be concated into a query. so it should be quoted and escaped etc as necessary 95 string sysTimeToValue(SysTime); 96 97 /// Prepared statement api 98 /* 99 PreparedStatement prepareStatement(string sql, int numberOfArguments); 100 101 */ 102 } 103 import std.stdio; 104 105 // Added Oct 26, 2021 106 Row queryOneRow(string file = __FILE__, size_t line = __LINE__, T...)(Database db, string sql, T t) { 107 auto res = db.query(sql, t); 108 if(res.empty) 109 throw new Exception("no row in result", file, line); 110 auto row = res.front; 111 return row; 112 } 113 114 Ret queryOneColumn(Ret, string file = __FILE__, size_t line = __LINE__, T...)(Database db, string sql, T t) { 115 auto row = queryOneRow(db, sql, t); 116 return to!Ret(row[0]); 117 } 118 119 struct Query { 120 ResultSet result; 121 this(T...)(Database db, string sql, T t) if(T.length!=1 || !is(T[0]==Variant[])) { 122 result = db.query(sql, t); 123 } 124 // Version for dynamic generation of args: (Needs to be a template for coexistence with other constructor. 125 this(T...)(Database db, string sql, T args) if (T.length==1 && is(T[0] == Variant[])) { 126 result = db.queryImpl(sql, args); 127 } 128 129 int opApply(T)(T dg) if(is(T == delegate)) { 130 import std.traits; 131 foreach(row; result) { 132 ParameterTypeTuple!dg tuple; 133 134 foreach(i, item; tuple) { 135 tuple[i] = to!(typeof(item))(row[i]); 136 } 137 138 if(auto result = dg(tuple)) 139 return result; 140 } 141 142 return 0; 143 } 144 } 145 146 /++ 147 Represents a single item in a result. A row is a set of these `DatabaseDatum`s. 148 149 History: 150 Added June 2, 2023 (dub v11.0). Prior to this, it would always use `string`. This has `alias toString this` to try to maintain compatibility. 151 +/ 152 struct DatabaseDatum { 153 int platformSpecificTag; 154 LimitedVariant storage; 155 156 /++ 157 These are normally constructed by the library, so you shouldn't need these constructors. If you're writing a new database implementation though, here it is. 158 +/ 159 package this(string s) { 160 storage = s; 161 } 162 163 /++ 164 Returns `true` if the item was `NULL` in the database. 165 +/ 166 bool isNull() { 167 return storage.contains == LimitedVariant.Contains.null_; 168 } 169 170 /++ 171 Converts the datum to a string in a format specified by the database. 172 +/ 173 string toString() { 174 return storage.toString(); 175 } 176 /++ 177 For compatibility with earlier versions of the api, all data can easily convert to string implicitly. 178 +/ 179 alias toString this; 180 } 181 182 /++ 183 A row in a result set from a query. 184 185 You can access this as either an array or associative array: 186 187 --- 188 foreach(Row row; db.query("SELECT id, name FROM mytable")) { 189 // can access by index or by name 190 row[0] == row["id"]; 191 row[1] == row["name"]; 192 193 // can also iterate over the results 194 foreach(name, data; row) { 195 // will send name = "id", data = the thing 196 // and then next loop will be name = "name", data = the thing 197 } 198 } 199 --- 200 +/ 201 struct Row { 202 package DatabaseDatum[] row; 203 package ResultSet resultSet; 204 205 /++ 206 Allows for access by index or column name. 207 +/ 208 DatabaseDatum opIndex(size_t idx, string file = __FILE__, int line = __LINE__) { 209 if(idx >= row.length) 210 throw new Exception(text("index ", idx, " is out of bounds on result"), file, line); 211 return row[idx]; 212 } 213 214 /// ditto 215 DatabaseDatum opIndex(string name, string file = __FILE__, int line = __LINE__) { 216 auto idx = resultSet.getFieldIndex(name); 217 if(idx >= row.length) 218 throw new Exception(text("no field ", name, " in result"), file, line); 219 return row[idx]; 220 } 221 222 /++ 223 Provides a string representation of the row, for quick eyeball debugging. You probably won't want the format this prints in (and don't rely upon it, as it is subject to change at any time without notice!), but it might be useful for use with `writeln`. 224 +/ 225 string toString() { 226 return to!string(row); 227 } 228 229 /++ 230 Allows iteration over the columns with the `foreach` statement. 231 232 History: 233 Prior to June 11, 2023 (dub v11.0), the order of iteration was undefined. It is now guaranteed to be in the same order as it was returned by the database (which is determined by your original query). Additionally, prior to this date, the datum was typed `string`. `DatabaseDatum` should implicitly convert to string, so your code is unlikely to break, but if you did specify the type explicitly you may need to update your code. 234 235 The overload with one argument, having just the datum without the name, was also added on June 11, 2023 (dub v11.0). 236 +/ 237 int opApply(int delegate(string, DatabaseDatum) dg) { 238 string[] fn = resultSet.fieldNames(); 239 foreach(idx, item; row) 240 mixin(yield("fn[idx], item")); 241 242 return 0; 243 } 244 245 /// ditto 246 int opApply(int delegate(DatabaseDatum) dg) { 247 foreach(item; row) 248 mixin(yield("item")); 249 return 0; 250 } 251 252 /++ 253 Hacky conversion to simpler types. 254 255 I'd recommend against using these in new code. I wrote them back around 2011 as a hack for something I was doing back then. Among the downsides of these is type information loss in both functions (since strings discard the information tag) and column order loss in `toAA` (since D associative arrays do not maintain any defined order). Additionally, they to make an additional copy of the result row, which you may be able to avoid by looping over it directly. 256 257 I may formally deprecate them in a future release. 258 +/ 259 string[] toStringArray() { 260 string[] row; 261 foreach(item; this.row) 262 row ~= item; 263 return row; 264 } 265 266 /// ditto 267 string[string] toAA() { 268 string[string] a; 269 270 string[] fn = resultSet.fieldNames(); 271 272 foreach(i, r; row) 273 a[fn[i]] = r; 274 275 return a; 276 } 277 278 } 279 import std.conv; 280 281 interface ResultSet { 282 // name for associative array to result index 283 int getFieldIndex(string field); 284 string[] fieldNames(); 285 286 // this is a range that can offer other ranges to access it 287 bool empty() @property; 288 Row front() @property; 289 void popFront() ; 290 size_t length() @property; 291 292 /* deprecated */ final ResultSet byAssoc() { return this; } 293 } 294 295 class DatabaseException : Exception { 296 this(string msg, string file = __FILE__, size_t line = __LINE__) { 297 super(msg, file, line); 298 } 299 } 300 301 302 303 abstract class SqlBuilder { } 304 305 class InsertBuilder : SqlBuilder { 306 private string table; 307 private string[] fields; 308 private string[] fieldsSetSql; 309 private Variant[] values; 310 311 /// 312 void setTable(string table) { 313 this.table = table; 314 } 315 316 /// same as adding the arr as values one by one. assumes DB column name matches AA key. 317 void addVariablesFromAssociativeArray(in string[string] arr, string[] names...) { 318 foreach(name; names) { 319 fields ~= name; 320 if(name in arr) { 321 fieldsSetSql ~= "?"; 322 values ~= Variant(arr[name]); 323 } else { 324 fieldsSetSql ~= "null"; 325 } 326 } 327 } 328 329 /// 330 void addVariable(T)(string name, T value) { 331 fields ~= name; 332 fieldsSetSql ~= "?"; 333 values ~= Variant(value); 334 } 335 336 /// if you use a placeholder, be sure to [addValueForHandWrittenPlaceholder] immediately 337 void addFieldWithSql(string name, string sql) { 338 fields ~= name; 339 fieldsSetSql ~= sql; 340 } 341 342 /// for addFieldWithSql that includes a placeholder 343 void addValueForHandWrittenPlaceholder(T)(T value) { 344 values ~= Variant(value); 345 } 346 347 /// executes the query 348 auto execute(Database db, string supplementalSql = null) { 349 return db.queryImpl(this.toSql() ~ supplementalSql, values); 350 } 351 352 string toSql() { 353 string sql = "INSERT INTO\n"; 354 sql ~= "\t" ~ table ~ " (\n"; 355 foreach(idx, field; fields) { 356 sql ~= "\t\t" ~ field ~ ((idx != fields.length - 1) ? ",\n" : "\n"); 357 } 358 sql ~= "\t) VALUES (\n"; 359 foreach(idx, field; fieldsSetSql) { 360 sql ~= "\t\t" ~ field ~ ((idx != fieldsSetSql.length - 1) ? ",\n" : "\n"); 361 } 362 sql ~= "\t)\n"; 363 return sql; 364 } 365 } 366 367 /// WARNING: this is as susceptible to SQL injections as you would be writing it out by hand 368 class SelectBuilder : SqlBuilder { 369 string[] fields; 370 string table; 371 string[] joins; 372 string[] wheres; 373 string[] orderBys; 374 string[] groupBys; 375 376 int limit; 377 int limitStart; 378 379 Variant[string] vars; 380 void setVariable(T)(string name, T value) { 381 assert(name.length); 382 if(name[0] == '?') 383 name = name[1 .. $]; 384 vars[name] = Variant(value); 385 } 386 387 Database db; 388 this(Database db = null) { 389 this.db = db; 390 } 391 392 /* 393 It would be nice to put variables right here in the builder 394 395 ?name 396 397 will prolly be the syntax, and we'll do a Variant[string] of them. 398 399 Anything not translated here will of course be in the ending string too 400 */ 401 402 SelectBuilder cloned() { 403 auto s = new SelectBuilder(this.db); 404 s.fields = this.fields.dup; 405 s.table = this.table; 406 s.joins = this.joins.dup; 407 s.wheres = this.wheres.dup; 408 s.orderBys = this.orderBys.dup; 409 s.groupBys = this.groupBys.dup; 410 s.limit = this.limit; 411 s.limitStart = this.limitStart; 412 413 foreach(k, v; this.vars) 414 s.vars[k] = v; 415 416 return s; 417 } 418 419 override string toString() { 420 string sql = "SELECT "; 421 422 // the fields first 423 { 424 bool outputted = false; 425 foreach(field; fields) { 426 if(outputted) 427 sql ~= ", "; 428 else 429 outputted = true; 430 431 sql ~= field; // "`" ~ field ~ "`"; 432 } 433 } 434 435 sql ~= " FROM " ~ table; 436 437 if(joins.length) { 438 foreach(join; joins) 439 sql ~= " " ~ join; 440 } 441 442 if(wheres.length) { 443 bool outputted = false; 444 sql ~= " WHERE "; 445 foreach(w; wheres) { 446 if(outputted) 447 sql ~= " AND "; 448 else 449 outputted = true; 450 sql ~= "(" ~ w ~ ")"; 451 } 452 } 453 454 if(groupBys.length) { 455 bool outputted = false; 456 sql ~= " GROUP BY "; 457 foreach(o; groupBys) { 458 if(outputted) 459 sql ~= ", "; 460 else 461 outputted = true; 462 sql ~= o; 463 } 464 } 465 466 if(orderBys.length) { 467 bool outputted = false; 468 sql ~= " ORDER BY "; 469 foreach(o; orderBys) { 470 if(outputted) 471 sql ~= ", "; 472 else 473 outputted = true; 474 sql ~= o; 475 } 476 } 477 478 if(limit) { 479 sql ~= " LIMIT "; 480 if(limitStart) 481 sql ~= to!string(limitStart) ~ ", "; 482 sql ~= to!string(limit); 483 } 484 485 if(db is null) 486 return sql; 487 488 return escapedVariants(db, sql, vars); 489 } 490 } 491 492 493 // /////////////////////sql////////////////////////////////// 494 495 package string tohexsql(const(ubyte)[] b) { 496 char[] x; 497 x.length = b.length * 2 + 3; 498 int pos = 0; 499 x[pos++] = 'x'; 500 x[pos++] = '\''; 501 502 char tohex(ubyte a) { 503 if(a < 10) 504 return cast(char)(a + '0'); 505 else 506 return cast(char)(a - 10 + 'A'); 507 } 508 509 foreach(item; b) { 510 x[pos++] = tohex(item >> 4); 511 x[pos++] = tohex(item & 0x0f); 512 } 513 514 x[pos++] = '\''; 515 516 return cast(string) x; 517 } 518 519 // used in the internal placeholder thing 520 string toSql(Database db, Variant a) { 521 522 string binary(const(ubyte)[] b) { 523 if(b is null) 524 return "NULL"; 525 else 526 return db.escapeBinaryString(b); 527 } 528 529 auto v = a.peek!(void*); 530 if(v && (*v is null)) { 531 return "NULL"; 532 } else if(auto t = a.peek!(SysTime)) { 533 return db.sysTimeToValue(*t); 534 } else if(auto t = a.peek!(DateTime)) { 535 // FIXME: this might be broken cuz of timezones! 536 return db.sysTimeToValue(cast(SysTime) *t); 537 } else if(auto t = a.peek!(ubyte[])) { 538 return binary(*t); 539 } else if(auto t = a.peek!(immutable(ubyte)[])) { 540 return binary(*t); 541 } else if(auto t = a.peek!string) { 542 auto str = *t; 543 if(str is null) 544 return "NULL"; 545 else 546 return '\'' ~ db.escape(str) ~ '\''; 547 } else { 548 string str = to!string(a); 549 return '\'' ~ db.escape(str) ~ '\''; 550 } 551 552 assert(0); 553 } 554 555 // just for convenience; "str".toSql(db); 556 string toSql(string s, Database db) { 557 //if(s is null) 558 //return "NULL"; 559 return '\'' ~ db.escape(s) ~ '\''; 560 } 561 562 string toSql(long s, Database db) { 563 return to!string(s); 564 } 565 566 string escapedVariants(Database db, in string sql, Variant[string] t) { 567 if(t.keys.length <= 0 || sql.indexOf("?") == -1) { 568 return sql; 569 } 570 571 string fixedup; 572 int currentStart = 0; 573 // FIXME: let's make ?? render as ? so we have some escaping capability 574 foreach(i, dchar c; sql) { 575 if(c == '?') { 576 fixedup ~= sql[currentStart .. i]; 577 578 int idxStart = cast(int) i + 1; 579 int idxLength; 580 581 bool isFirst = true; 582 583 while(idxStart + idxLength < sql.length) { 584 char C = sql[idxStart + idxLength]; 585 586 if((C >= 'a' && C <= 'z') || (C >= 'A' && C <= 'Z') || C == '_' || (!isFirst && C >= '0' && C <= '9')) 587 idxLength++; 588 else 589 break; 590 591 isFirst = false; 592 } 593 594 auto idx = sql[idxStart .. idxStart + idxLength]; 595 596 if(idx in t) { 597 fixedup ~= toSql(db, t[idx]); 598 currentStart = idxStart + idxLength; 599 } else { 600 // just leave it there, it might be done on another layer 601 currentStart = cast(int) i; 602 } 603 } 604 } 605 606 fixedup ~= sql[currentStart .. $]; 607 608 return fixedup; 609 } 610 611 /// Note: ?n params are zero based! 612 string escapedVariants(Database db, in string sql, Variant[] t) { 613 // FIXME: let's make ?? render as ? so we have some escaping capability 614 // if nothing to escape or nothing to escape with, don't bother 615 if(t.length > 0 && sql.indexOf("?") != -1) { 616 string fixedup; 617 int currentIndex; 618 int currentStart = 0; 619 foreach(i, dchar c; sql) { 620 if(c == '?') { 621 fixedup ~= sql[currentStart .. i]; 622 623 int idx = -1; 624 currentStart = cast(int) i + 1; 625 if((i + 1) < sql.length) { 626 auto n = sql[i + 1]; 627 if(n >= '0' && n <= '9') { 628 currentStart = cast(int) i + 2; 629 idx = n - '0'; 630 } 631 } 632 if(idx == -1) { 633 idx = currentIndex; 634 currentIndex++; 635 } 636 637 if(idx < 0 || idx >= t.length) 638 throw new Exception("SQL Parameter index is out of bounds: " ~ to!string(idx) ~ " at `"~sql[0 .. i]~"`"); 639 640 fixedup ~= toSql(db, t[idx]); 641 } 642 } 643 644 fixedup ~= sql[currentStart .. $]; 645 646 return fixedup; 647 /* 648 string fixedup; 649 int pos = 0; 650 651 652 void escAndAdd(string str, int q) { 653 fixedup ~= sql[pos..q] ~ '\'' ~ db.escape(str) ~ '\''; 654 655 } 656 657 foreach(a; t) { 658 int q = sql[pos..$].indexOf("?"); 659 if(q == -1) 660 break; 661 q += pos; 662 663 auto v = a.peek!(void*); 664 if(v && (*v is null)) 665 fixedup ~= sql[pos..q] ~ "NULL"; 666 else { 667 string str = to!string(a); 668 escAndAdd(str, q); 669 } 670 671 pos = q+1; 672 } 673 674 fixedup ~= sql[pos..$]; 675 676 sql = fixedup; 677 */ 678 } 679 680 return sql; 681 } 682 683 684 685 686 687 688 enum UpdateOrInsertMode { 689 CheckForMe, 690 AlwaysUpdate, 691 AlwaysInsert 692 } 693 694 695 // BIG FIXME: this should really use prepared statements 696 int updateOrInsert(Database db, string table, string[string] values, string where, UpdateOrInsertMode mode = UpdateOrInsertMode.CheckForMe, string key = "id") { 697 698 string identifierQuote = ""; 699 700 bool insert = false; 701 702 final switch(mode) { 703 case UpdateOrInsertMode.CheckForMe: 704 auto res = db.query("SELECT "~key~" FROM "~identifierQuote~db.escape(table)~identifierQuote~" WHERE " ~ where); 705 insert = res.empty; 706 707 break; 708 case UpdateOrInsertMode.AlwaysInsert: 709 insert = true; 710 break; 711 case UpdateOrInsertMode.AlwaysUpdate: 712 insert = false; 713 break; 714 } 715 716 717 if(insert) { 718 string insertSql = "INSERT INTO " ~identifierQuote ~ db.escape(table) ~ identifierQuote ~ " "; 719 720 bool outputted = false; 721 string vs, cs; 722 foreach(column, value; values) { 723 if(column is null) 724 continue; 725 if(outputted) { 726 vs ~= ", "; 727 cs ~= ", "; 728 } else 729 outputted = true; 730 731 //cs ~= "`" ~ db.escape(column) ~ "`"; 732 cs ~= identifierQuote ~ column ~ identifierQuote; // FIXME: possible insecure 733 if(value is null) 734 vs ~= "NULL"; 735 else 736 vs ~= "'" ~ db.escape(value) ~ "'"; 737 } 738 739 if(!outputted) 740 return 0; 741 742 743 insertSql ~= "(" ~ cs ~ ")"; 744 insertSql ~= " VALUES "; 745 insertSql ~= "(" ~ vs ~ ")"; 746 747 db.query(insertSql); 748 749 return 0; // db.lastInsertId; 750 } else { 751 string updateSql = "UPDATE "~identifierQuote~db.escape(table)~identifierQuote~" SET "; 752 753 bool outputted = false; 754 foreach(column, value; values) { 755 if(column is null) 756 continue; 757 if(outputted) 758 updateSql ~= ", "; 759 else 760 outputted = true; 761 762 if(value is null) 763 updateSql ~= identifierQuote ~ db.escape(column) ~ identifierQuote ~ " = NULL"; 764 else 765 updateSql ~= identifierQuote ~ db.escape(column) ~ identifierQuote ~ " = '" ~ db.escape(value) ~ "'"; 766 } 767 768 if(!outputted) 769 return 0; 770 771 updateSql ~= " WHERE " ~ where; 772 773 db.query(updateSql); 774 return 0; 775 } 776 } 777 778 779 780 781 782 string fixupSqlForDataObjectUse(string sql, string[string] keyMapping = null) { 783 784 string[] tableNames; 785 786 string piece = sql; 787 sizediff_t idx; 788 while((idx = piece.indexOf("JOIN")) != -1) { 789 auto start = idx + 5; 790 auto i = start; 791 while(piece[i] != ' ' && piece[i] != '\n' && piece[i] != '\t' && piece[i] != ',') 792 i++; 793 auto end = i; 794 795 tableNames ~= strip(piece[start..end]); 796 797 piece = piece[end..$]; 798 } 799 800 idx = sql.indexOf("FROM"); 801 if(idx != -1) { 802 auto start = idx + 5; 803 auto i = start; 804 start = i; 805 while(i < sql.length && !(sql[i] > 'A' && sql[i] <= 'Z')) // if not uppercase, except for A (for AS) to avoid SQL keywords (hack) 806 i++; 807 808 auto from = sql[start..i]; 809 auto pieces = from.split(","); 810 foreach(p; pieces) { 811 p = p.strip(); 812 start = 0; 813 i = 0; 814 while(i < p.length && p[i] != ' ' && p[i] != '\n' && p[i] != '\t' && p[i] != ',') 815 i++; 816 817 tableNames ~= strip(p[start..i]); 818 } 819 820 string sqlToAdd; 821 foreach(tbl; tableNames) { 822 if(tbl.length) { 823 string keyName = "id"; 824 if(tbl in keyMapping) 825 keyName = keyMapping[tbl]; 826 sqlToAdd ~= ", " ~ tbl ~ "." ~ keyName ~ " AS " ~ "id_from_" ~ tbl; 827 } 828 } 829 830 sqlToAdd ~= " "; 831 832 sql = sql[0..idx] ~ sqlToAdd ~ sql[idx..$]; 833 } 834 835 return sql; 836 } 837 838 839 840 841 842 /* 843 This is like a result set 844 845 846 DataObject res = [...]; 847 848 res.name = "Something"; 849 850 res.commit; // runs the actual update or insert 851 852 853 res = new DataObject(fields, tables 854 855 856 857 858 859 860 861 when doing a select, we need to figure out all the tables and modify the query to include the ids we need 862 863 864 search for FROM and JOIN 865 the next token is the table name 866 867 right before the FROM, add the ids of each table 868 869 870 given: 871 SELECT name, phone FROM customers LEFT JOIN phones ON customer.id = phones.cust_id 872 873 we want: 874 SELECT name, phone, customers.id AS id_from_customers, phones.id AS id_from_phones FROM customers LEFT JOIN phones ON customer.id[...]; 875 876 */ 877 878 mixin template DataObjectConstructors() { 879 this(Database db, string[string] res, Tuple!(string, string)[string] mappings) { 880 super(db, res, mappings); 881 } 882 } 883 884 private string yield(string what) { return `if(auto result = dg(`~what~`)) return result;`; } 885 886 import std.typecons; 887 import std.json; // for json value making 888 class DataObject { 889 // lets you just free-form set fields, assuming they all come from the given table 890 // note it doesn't try to handle joins for new rows. you've gotta do that yourself 891 this(Database db, string table, UpdateOrInsertMode mode = UpdateOrInsertMode.CheckForMe) { 892 assert(db !is null); 893 this.db = db; 894 this.table = table; 895 896 this.mode = mode; 897 } 898 899 JSONValue makeJsonValue() { 900 JSONValue val; 901 JSONValue[string] valo; 902 //val.type = JSON_TYPE.OBJECT; 903 foreach(k, v; fields) { 904 JSONValue s; 905 //s.type = JSON_TYPE.STRING; 906 s.str = v; 907 valo[k] = s; 908 } 909 val = valo; 910 return val; 911 } 912 913 this(Database db, string[string] res, Tuple!(string, string)[string] mappings) { 914 this.db = db; 915 this.mappings = mappings; 916 this.fields = res; 917 918 mode = UpdateOrInsertMode.AlwaysUpdate; 919 } 920 921 string table; 922 // table, column [alias] 923 Tuple!(string, string)[string] mappings; 924 925 // value [field] [table] 926 string[string][string] multiTableKeys; // note this is not set internally tight now 927 // but it can be set manually to do multi table mappings for automatic update 928 929 930 string opDispatch(string field, string file = __FILE__, size_t line = __LINE__)() 931 if((field.length < 8 || field[0..8] != "id_from_") && field != "popFront") 932 { 933 if(field !in fields) 934 throw new Exception("no such field " ~ field, file, line); 935 936 return fields[field]; 937 } 938 939 string opDispatch(string field, T)(T t) 940 if((field.length < 8 || field[0..8] != "id_from_") && field != "popFront") 941 { 942 static if(__traits(compiles, t is null)) { 943 if(t is null) 944 setImpl(field, null); 945 else 946 setImpl(field, to!string(t)); 947 } else 948 setImpl(field, to!string(t)); 949 950 return fields[field]; 951 } 952 953 954 private void setImpl(string field, string value) { 955 if(field in fields) { 956 if(fields[field] != value) 957 changed[field] = true; 958 } else { 959 changed[field] = true; 960 } 961 962 fields[field] = value; 963 } 964 965 public void setWithoutChange(string field, string value) { 966 fields[field] = value; 967 } 968 969 int opApply(int delegate(ref string) dg) { 970 foreach(a; fields) 971 mixin(yield("a")); 972 973 return 0; 974 } 975 976 int opApply(int delegate(ref string, ref string) dg) { 977 foreach(a, b; fields) 978 mixin(yield("a, b")); 979 980 return 0; 981 } 982 983 984 string opIndex(string field, string file = __FILE__, size_t line = __LINE__) { 985 if(field !in fields) 986 throw new DatabaseException("No such field in data object: " ~ field, file, line); 987 return fields[field]; 988 } 989 990 string opIndexAssign(string value, string field) { 991 setImpl(field, value); 992 return value; 993 } 994 995 string* opBinary(string op)(string key) if(op == "in") { 996 return key in fields; 997 } 998 999 string[string] fields; 1000 bool[string] changed; 1001 1002 void commitChanges() { 1003 commitChanges(cast(string) null, null); 1004 } 1005 1006 void commitChanges(string key, string keyField) { 1007 commitChanges(key is null ? null : [key], keyField is null ? null : [keyField]); 1008 } 1009 1010 void commitChanges(string[] keys, string[] keyFields = null) { 1011 string[string][string] toUpdate; 1012 int updateCount = 0; 1013 foreach(field, c; changed) { 1014 if(c) { 1015 string tbl, col; 1016 if(mappings is null) { 1017 tbl = this.table; 1018 col = field; 1019 } else { 1020 if(field !in mappings) 1021 assert(0, "no such mapping for " ~ field); 1022 auto m = mappings[field]; 1023 tbl = m[0]; 1024 col = m[1]; 1025 } 1026 1027 toUpdate[tbl][col] = fields[field]; 1028 updateCount++; 1029 } 1030 } 1031 1032 if(updateCount) { 1033 db.startTransaction(); 1034 scope(success) db.query("COMMIT"); 1035 scope(failure) db.query("ROLLBACK"); 1036 1037 foreach(tbl, values; toUpdate) { 1038 string where, keyFieldToPass; 1039 1040 if(keys is null) { 1041 keys = [null]; 1042 } 1043 1044 if(multiTableKeys is null || tbl !in multiTableKeys) 1045 foreach(i, key; keys) { 1046 string keyField; 1047 1048 if(key is null) { 1049 key = "id_from_" ~ tbl; 1050 if(key !in fields) 1051 key = "id"; 1052 } 1053 1054 if(i >= keyFields.length || keyFields[i] is null) { 1055 if(key == "id_from_" ~ tbl) 1056 keyField = "id"; 1057 else 1058 keyField = key; 1059 } else { 1060 keyField = keyFields[i]; 1061 } 1062 1063 1064 if(where.length) 1065 where ~= " AND "; 1066 1067 auto f = key in fields ? fields[key] : null; 1068 if(f is null) 1069 where ~= keyField ~ " = NULL"; 1070 else 1071 where ~= keyField ~ " = '"~db.escape(f)~"'" ; 1072 if(keyFieldToPass.length) 1073 keyFieldToPass ~= ", "; 1074 1075 keyFieldToPass ~= keyField; 1076 } 1077 else { 1078 foreach(keyField, v; multiTableKeys[tbl]) { 1079 if(where.length) 1080 where ~= " AND "; 1081 1082 where ~= keyField ~ " = '"~db.escape(v)~"'" ; 1083 if(keyFieldToPass.length) 1084 keyFieldToPass ~= ", "; 1085 1086 keyFieldToPass ~= keyField; 1087 } 1088 } 1089 1090 1091 1092 updateOrInsert(db, tbl, values, where, mode, keyFieldToPass); 1093 } 1094 1095 changed = null; 1096 } 1097 } 1098 1099 void commitDelete() { 1100 if(mode == UpdateOrInsertMode.AlwaysInsert) 1101 throw new Exception("Cannot delete an item not in the database"); 1102 1103 assert(table.length); // FIXME, should work with fancy items too 1104 1105 // FIXME: escaping and primary key questions 1106 db.query("DELETE FROM " ~ table ~ " WHERE id = '" ~ db.escape(fields["id"]) ~ "'"); 1107 } 1108 1109 string getAlias(string table, string column) { 1110 string ali; 1111 if(mappings is null) { 1112 if(this.table is null) { 1113 mappings[column] = tuple(table, column); 1114 return column; 1115 } else { 1116 assert(table == this.table); 1117 ali = column; 1118 } 1119 } else { 1120 foreach(a, what; mappings) 1121 if(what[0] == table && what[1] == column 1122 && a.indexOf("id_from_") == -1) { 1123 ali = a; 1124 break; 1125 } 1126 } 1127 1128 return ali; 1129 } 1130 1131 void set(string table, string column, string value) { 1132 string ali = getAlias(table, column); 1133 //assert(ali in fields); 1134 setImpl(ali, value); 1135 } 1136 1137 string select(string table, string column) { 1138 string ali = getAlias(table, column); 1139 //assert(ali in fields); 1140 if(ali in fields) 1141 return fields[ali]; 1142 return null; 1143 } 1144 1145 DataObject addNew() { 1146 auto n = new DataObject(db, null); 1147 1148 n.db = this.db; 1149 n.table = this.table; 1150 n.mappings = this.mappings; 1151 1152 foreach(k, v; this.fields) 1153 if(k.indexOf("id_from_") == -1) 1154 n.fields[k] = v; 1155 else 1156 n.fields[k] = null; // don't copy ids 1157 1158 n.mode = UpdateOrInsertMode.AlwaysInsert; 1159 1160 return n; 1161 } 1162 1163 Database db; 1164 UpdateOrInsertMode mode; 1165 } 1166 1167 /** 1168 You can subclass DataObject if you want to 1169 get some compile time checks or better types. 1170 1171 You'll want to disable opDispatch, then forward your 1172 properties to the super opDispatch. 1173 */ 1174 1175 /*mixin*/ string DataObjectField(T, string table, string column, string aliasAs = null)() { 1176 string aliasAs_; 1177 if(aliasAs is null) 1178 aliasAs_ = column; 1179 else 1180 aliasAs_ = aliasAs; 1181 return ` 1182 @property void `~aliasAs_~`(`~T.stringof~` setTo) { 1183 super.set("`~table~`", "`~column~`", to!string(setTo)); 1184 } 1185 1186 @property `~T.stringof~` `~aliasAs_~` () { 1187 return to!(`~T.stringof~`)(super.select("`~table~`", "`~column~`")); 1188 } 1189 `; 1190 } 1191 1192 mixin template StrictDataObject() { 1193 // disable opdispatch 1194 string opDispatch(string name)(...) if (0) {} 1195 } 1196 1197 1198 string createDataObjectFieldsFromAlias(string table, fieldsToUse)() { 1199 string ret; 1200 1201 fieldsToUse f; 1202 foreach(member; __traits(allMembers, fieldsToUse)) { 1203 ret ~= DataObjectField!(typeof(__traits(getMember, f, member)), table, member); 1204 } 1205 1206 return ret; 1207 } 1208 1209 1210 /** 1211 This creates an editable data object out of a simple struct. 1212 1213 struct MyFields { 1214 int id; 1215 string name; 1216 } 1217 1218 alias SimpleDataObject!("my_table", MyFields) User; 1219 1220 1221 User a = new User(db); 1222 1223 a.id = 30; 1224 a.name = "hello"; 1225 a.commitChanges(); // tries an update or insert on the my_table table 1226 1227 1228 Unlike the base DataObject class, this template provides compile time 1229 checking for types and names, based on the struct you pass in: 1230 1231 a.id = "aa"; // compile error 1232 1233 a.notAField; // compile error 1234 */ 1235 class SimpleDataObject(string tableToUse, fieldsToUse) : DataObject { 1236 mixin StrictDataObject!(); 1237 1238 mixin(createDataObjectFieldsFromAlias!(tableToUse, fieldsToUse)()); 1239 1240 this(Database db) { 1241 super(db, tableToUse); 1242 } 1243 } 1244 1245 /** 1246 Given some SQL, it finds the CREATE TABLE 1247 instruction for the given tableName. 1248 (this is so it can find one entry from 1249 a file with several SQL commands. But it 1250 may break on a complex file, so try to only 1251 feed it simple sql files.) 1252 1253 From that, it pulls out the members to create a 1254 simple struct based on it. 1255 1256 It's not terribly smart, so it will probably 1257 break on complex tables. 1258 1259 Data types handled: 1260 1261 ``` 1262 INTEGER, SMALLINT, MEDIUMINT -> D's int 1263 TINYINT -> D's bool 1264 BIGINT -> D's long 1265 TEXT, VARCHAR -> D's string 1266 FLOAT, DOUBLE -> D's double 1267 ``` 1268 1269 It also reads DEFAULT values to pass to D, except for NULL. 1270 It ignores any length restrictions. 1271 1272 Bugs: 1273 $(LIST 1274 * Skips all constraints 1275 * Doesn't handle nullable fields, except with strings 1276 * It only handles SQL keywords if they are all caps 1277 ) 1278 1279 This, when combined with SimpleDataObject!(), 1280 can automatically create usable D classes from 1281 SQL input. 1282 */ 1283 struct StructFromCreateTable(string sql, string tableName) { 1284 mixin(getCreateTable(sql, tableName)); 1285 } 1286 1287 string getCreateTable(string sql, string tableName) { 1288 skip: 1289 while(readWord(sql) != "CREATE") {} 1290 1291 assert(readWord(sql) == "TABLE"); 1292 1293 if(readWord(sql) != tableName) 1294 goto skip; 1295 1296 assert(readWord(sql) == "("); 1297 1298 int state; 1299 int parens; 1300 1301 struct Field { 1302 string name; 1303 string type; 1304 string defaultValue; 1305 } 1306 Field*[] fields; 1307 1308 string word = readWord(sql); 1309 Field* current = new Field(); // well, this is interesting... under new DMD, not using new breaks it in CTFE because it overwrites the one entry! 1310 while(word != ")" || parens) { 1311 if(word == ")") { 1312 parens --; 1313 word = readWord(sql); 1314 continue; 1315 } 1316 if(word == "(") { 1317 parens ++; 1318 word = readWord(sql); 1319 continue; 1320 } 1321 switch(state) { 1322 default: assert(0); 1323 case 0: 1324 if(word[0] >= 'A' && word[0] <= 'Z') { 1325 state = 4; 1326 break; // we want to skip this since it starts with a keyword (we hope) 1327 } 1328 current.name = word; 1329 state = 1; 1330 break; 1331 case 1: 1332 current.type ~= word; 1333 state = 2; 1334 break; 1335 case 2: 1336 if(word == "DEFAULT") 1337 state = 3; 1338 else if (word == ",") { 1339 fields ~= current; 1340 current = new Field(); 1341 state = 0; // next 1342 } 1343 break; 1344 case 3: 1345 current.defaultValue = word; 1346 state = 2; // back to skipping 1347 break; 1348 case 4: 1349 if(word == ",") 1350 state = 0; 1351 } 1352 1353 word = readWord(sql); 1354 } 1355 1356 if(current.name !is null) 1357 fields ~= current; 1358 1359 1360 string structCode; 1361 foreach(field; fields) { 1362 structCode ~= "\t"; 1363 1364 switch(field.type) { 1365 case "INTEGER": 1366 case "SMALLINT": 1367 case "MEDIUMINT": 1368 case "SERIAL": // added Oct 23, 2021 1369 structCode ~= "int"; 1370 break; 1371 case "BOOLEAN": 1372 case "TINYINT": 1373 structCode ~= "bool"; 1374 break; 1375 case "BIGINT": 1376 structCode ~= "long"; 1377 break; 1378 case "CHAR": 1379 case "char": 1380 case "VARCHAR": 1381 case "varchar": 1382 case "TEXT": 1383 case "text": 1384 case "TIMESTAMPTZ": // added Oct 23, 2021 1385 structCode ~= "string"; 1386 break; 1387 case "FLOAT": 1388 case "DOUBLE": 1389 structCode ~= "double"; 1390 break; 1391 default: 1392 assert(0, "unknown type " ~ field.type ~ " for " ~ field.name); 1393 } 1394 1395 structCode ~= " "; 1396 structCode ~= field.name; 1397 1398 if(field.defaultValue !is null) { 1399 structCode ~= " = " ~ field.defaultValue; 1400 } 1401 1402 structCode ~= ";\n"; 1403 } 1404 1405 return structCode; 1406 } 1407 1408 string readWord(ref string src) { 1409 reset: 1410 while(src[0] == ' ' || src[0] == '\t' || src[0] == '\n') 1411 src = src[1..$]; 1412 if(src.length >= 2 && src[0] == '-' && src[1] == '-') { // a comment, skip it 1413 while(src[0] != '\n') 1414 src = src[1..$]; 1415 goto reset; 1416 } 1417 1418 int start, pos; 1419 if(src[0] == '`') { 1420 src = src[1..$]; 1421 while(src[pos] != '`') 1422 pos++; 1423 goto gotit; 1424 } 1425 1426 1427 while( 1428 (src[pos] >= 'A' && src[pos] <= 'Z') 1429 || 1430 (src[pos] >= 'a' && src[pos] <= 'z') 1431 || 1432 (src[pos] >= '0' && src[pos] <= '9') 1433 || 1434 src[pos] == '_' 1435 ) 1436 pos++; 1437 gotit: 1438 if(pos == 0) 1439 pos = 1; 1440 1441 string tmp = src[0..pos]; 1442 1443 if(src[pos] == '`') 1444 pos++; // skip the ending quote; 1445 1446 src = src[pos..$]; 1447 1448 return tmp; 1449 } 1450 1451 /// Combines StructFromCreateTable and SimpleDataObject into a one-stop template. 1452 /// alias DataObjectFromSqlCreateTable(import("file.sql"), "my_table") MyTable; 1453 template DataObjectFromSqlCreateTable(string sql, string tableName) { 1454 alias SimpleDataObject!(tableName, StructFromCreateTable!(sql, tableName)) DataObjectFromSqlCreateTable; 1455 } 1456 1457 /+ 1458 class MyDataObject : DataObject { 1459 this() { 1460 super(new Database("localhost", "root", "pass", "social"), null); 1461 } 1462 1463 mixin StrictDataObject!(); 1464 1465 mixin(DataObjectField!(int, "users", "id")); 1466 } 1467 1468 void main() { 1469 auto a = new MyDataObject; 1470 1471 a.fields["id"] = "10"; 1472 1473 a.id = 34; 1474 1475 a.commitChanges; 1476 } 1477 +/ 1478 1479 /* 1480 alias DataObjectFromSqlCreateTable!(import("db.sql"), "users") Test; 1481 1482 void main() { 1483 auto a = new Test(null); 1484 1485 a.cool = "way"; 1486 a.value = 100; 1487 } 1488 */ 1489 1490 void typeinfoBugWorkaround() { 1491 assert(0, to!string(typeid(immutable(char[])[immutable(char)[]]))); 1492 } 1493 1494 mixin template DatabaseOperations(string table) { 1495 DataObject getAsDb(Database db) { 1496 return objectToDataObject!(typeof(this))(this, db, table); 1497 } 1498 1499 static typeof(this) fromRow(Row row) { 1500 return rowToObject!(typeof(this))(row); 1501 } 1502 1503 static typeof(this) fromId(Database db, long id) { 1504 auto query = new SelectBuilder(db); 1505 query.table = table; 1506 query.fields ~= "*"; 1507 query.wheres ~= "id = ?0"; 1508 auto res = db.query(query.toString(), id); 1509 if(res.empty) 1510 throw new Exception("no such row"); 1511 return fromRow(res.front); 1512 } 1513 1514 } 1515 1516 string toDbName(string s) { 1517 import std.string; 1518 return s.toLower ~ "s"; 1519 } 1520 1521 /++ 1522 Easy interop with [arsd.cgi] serveRestObject classes. 1523 1524 History: 1525 Added October 31, 2021. 1526 1527 Warning: not stable/supported at this time. 1528 +/ 1529 mixin template DatabaseRestObject(alias getDb) { 1530 override void save() { 1531 this.id = this.saveToDatabase(getDb()); 1532 } 1533 1534 override void load(string urlId) { 1535 import std.conv; 1536 this.id = to!int(urlId); 1537 this.loadFromDatabase(getDb()); 1538 } 1539 } 1540 1541 void loadFromDatabase(T)(T t, Database database, string tableName = toDbName(__traits(identifier, T))) { 1542 static assert(is(T == class), "structs wont work for this function, try rowToObject instead for now and complain to me adding struct support is easy enough"); 1543 auto query = new SelectBuilder(database); 1544 query.table = tableName; 1545 query.fields ~= "*"; 1546 query.wheres ~= "id = ?0"; 1547 auto res = database.query(query.toString(), t.id); 1548 if(res.empty) 1549 throw new Exception("no such row"); 1550 1551 rowToObject(res.front, t); 1552 } 1553 1554 auto saveToDatabase(T)(T t, Database database, string tableName = toDbName(__traits(identifier, T))) { 1555 DataObject obj = objectToDataObject(t, database, tableName, t.id ? UpdateOrInsertMode.AlwaysUpdate : UpdateOrInsertMode.AlwaysInsert); 1556 if(!t.id) { 1557 import std.random; // omg i hate htis 1558 obj.id = uniform(2, int.max); 1559 } 1560 obj.commitChanges; 1561 return t.id; 1562 } 1563 1564 /+ + 1565 auto builder = UpdateBuilder("rooms"); 1566 builder.player_one_selection = challenge; 1567 builder.execute(db, id); 1568 +/ 1569 private struct UpdateBuilder { 1570 this(T)(string table, T id) { 1571 this.table = table; 1572 import std.conv; 1573 this.id = to!string(id); 1574 } 1575 1576 } 1577 1578 import std.traits, std.datetime; 1579 enum DbSave; 1580 enum DbNullable; 1581 alias AliasHelper(alias T) = T; 1582 1583 T rowToObject(T)(Row row) { 1584 T t; 1585 static if(is(T == class)) 1586 t = new T(); 1587 rowToObject(row, t); 1588 return t; 1589 } 1590 1591 void rowToObject(T)(Row row, ref T t) { 1592 import arsd.dom, arsd.cgi; 1593 1594 foreach(memberName; __traits(allMembers, T)) { 1595 alias member = AliasHelper!(__traits(getMember, t, memberName)); 1596 foreach(attr; __traits(getAttributes, member)) { 1597 static if(is(attr == DbSave)) { 1598 static if(is(typeof(member) == enum)) 1599 __traits(getMember, t, memberName) = cast(typeof(member)) to!int(row[memberName]); 1600 else static if(is(typeof(member) == bool)) { 1601 __traits(getMember, t, memberName) = row[memberName][0] == 't'; 1602 } else static if(is(typeof(member) == Html)) { 1603 __traits(getMember, t, memberName).source = row[memberName]; 1604 } else static if(is(typeof(member) == DateTime)) 1605 __traits(getMember, t, memberName) = cast(DateTime) dTimeToSysTime(to!long(row[memberName])); 1606 else { 1607 if(row[memberName].length) 1608 __traits(getMember, t, memberName) = to!(typeof(member))(row[memberName]); 1609 // otherwise, we'll leave it as .init - most likely null 1610 } 1611 } 1612 } 1613 } 1614 } 1615 1616 DataObject objectToDataObject(T)(T t, Database db, string table, UpdateOrInsertMode mode = UpdateOrInsertMode.CheckForMe) { 1617 import arsd.dom, arsd.cgi; 1618 1619 DataObject obj = new DataObject(db, table, mode); 1620 foreach(memberName; __traits(allMembers, T)) { 1621 alias member = AliasHelper!(__traits(getMember, t, memberName)); 1622 foreach(attr; __traits(getAttributes, member)) { 1623 static if(is(attr == DbSave)) { 1624 static if(is(typeof(member) == enum)) 1625 obj.opDispatch!memberName(cast(int) __traits(getMember, t, memberName)); 1626 else static if(is(typeof(member) == Html)) { 1627 obj.opDispatch!memberName(__traits(getMember, t, memberName).source); 1628 } else static if(is(typeof(member) == DateTime)) 1629 obj.opDispatch!memberName(dateTimeToDTime(__traits(getMember, t, memberName))); 1630 else { 1631 bool done; 1632 foreach(attr2; __traits(getAttributes, member)) { 1633 static if(is(attr2 == DbNullable)) { 1634 if(__traits(getMember, t, memberName) == 0) 1635 done = true; 1636 } 1637 } 1638 1639 if(!done) { 1640 static if(memberName == "id") { 1641 if(__traits(getMember, t, memberName)) { 1642 // maybe i shouldn't actually set the id but idk 1643 obj.opDispatch!memberName(__traits(getMember, t, memberName)); 1644 } else { 1645 // it is null, let the system do something about it like auto increment 1646 1647 } 1648 } else 1649 obj.opDispatch!memberName(__traits(getMember, t, memberName)); 1650 } 1651 } 1652 } 1653 } 1654 } 1655 return obj; 1656 } 1657 1658 1659 1660 void fillData(T)(string delegate(string, string) setter, T obj, string name) { 1661 fillData( (k, v) { setter(k, v); }, obj, name); 1662 } 1663 1664 void fillData(T)(void delegate(string, string) setter, T obj, string name) { 1665 import arsd.dom, arsd.cgi; 1666 1667 import std.traits; 1668 static if(!isSomeString!T && isArray!T) { 1669 // FIXME: indexing 1670 foreach(o; obj) 1671 fillData(setter, o, name); 1672 } else static if(is(T == DateTime)) { 1673 fillData(setter, obj.toISOExtString(), name); 1674 } else static if(is(T == Html)) { 1675 fillData(setter, obj.source, name); 1676 } else static if(is(T == struct)) { 1677 foreach(idx, memberName; __traits(allMembers, T)) { 1678 alias member = AliasHelper!(__traits(getMember, obj, memberName)); 1679 static if(!is(typeof(member) == function)) 1680 fillData(setter, __traits(getMember, obj, memberName), name ~ "." ~ memberName); 1681 else static if(is(typeof(member) == function)) { 1682 static if(functionAttributes!member & FunctionAttribute.property) { 1683 fillData(setter, __traits(getMember, obj, memberName)(), name ~ "." ~ memberName); 1684 } 1685 } 1686 } 1687 } else { 1688 auto value = to!string(obj); 1689 setter(name, value); 1690 } 1691 } 1692 1693 struct varchar(size_t max) { 1694 private string payload; 1695 1696 this(string s, string file = __FILE__, size_t line = __LINE__) { 1697 opAssign(s, file, line); 1698 } 1699 1700 typeof(this) opAssign(string s, string file = __FILE__, size_t line = __LINE__) { 1701 if(s.length > max) 1702 throw new Exception(s ~ " :: too long", file, line); 1703 payload = s; 1704 1705 return this; 1706 } 1707 1708 string asString() { 1709 return payload; 1710 1711 } 1712 alias asString this; 1713 } 1714 1715 version (unittest) 1716 { 1717 /// Unittest utility that returns a predefined set of values 1718 package (arsd) final class PredefinedResultSet : ResultSet 1719 { 1720 string[] fields; 1721 Row[] rows; 1722 size_t current; 1723 1724 this(string[] fields, Row[] rows) 1725 { 1726 this.fields = fields; 1727 this.rows = rows; 1728 foreach (ref row; rows) 1729 row.resultSet = this; 1730 } 1731 1732 int getFieldIndex(const string field) const 1733 { 1734 foreach (const idx, const val; fields) 1735 if (val == field) 1736 return cast(int) idx; 1737 1738 assert(false, "No field with name: " ~ field); 1739 } 1740 1741 string[] fieldNames() 1742 { 1743 return fields; 1744 } 1745 1746 @property bool empty() const 1747 { 1748 return current == rows.length; 1749 } 1750 1751 Row front() @property 1752 { 1753 assert(!empty); 1754 return rows[current]; 1755 } 1756 1757 void popFront() 1758 { 1759 assert(!empty); 1760 current++; 1761 } 1762 1763 size_t length() @property 1764 { 1765 return rows.length - current; 1766 } 1767 } 1768 }