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