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