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