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