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