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