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