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 { 388 string str = to!string(a); 389 return '\'' ~ db.escape(str) ~ '\''; 390 } 391 392 assert(0); 393 } 394 395 // just for convenience; "str".toSql(db); 396 string toSql(string s, Database db) { 397 //if(s is null) 398 //return "NULL"; 399 return '\'' ~ db.escape(s) ~ '\''; 400 } 401 402 string toSql(long s, Database db) { 403 return to!string(s); 404 } 405 406 string escapedVariants(Database db, in string sql, Variant[string] t) { 407 if(t.keys.length <= 0 || sql.indexOf("?") == -1) { 408 return sql; 409 } 410 411 string fixedup; 412 int currentStart = 0; 413 // FIXME: let's make ?? render as ? so we have some escaping capability 414 foreach(i, dchar c; sql) { 415 if(c == '?') { 416 fixedup ~= sql[currentStart .. i]; 417 418 int idxStart = cast(int) i + 1; 419 int idxLength; 420 421 bool isFirst = true; 422 423 while(idxStart + idxLength < sql.length) { 424 char C = sql[idxStart + idxLength]; 425 426 if((C >= 'a' && C <= 'z') || (C >= 'A' && C <= 'Z') || C == '_' || (!isFirst && C >= '0' && C <= '9')) 427 idxLength++; 428 else 429 break; 430 431 isFirst = false; 432 } 433 434 auto idx = sql[idxStart .. idxStart + idxLength]; 435 436 if(idx in t) { 437 fixedup ~= toSql(db, t[idx]); 438 currentStart = idxStart + idxLength; 439 } else { 440 // just leave it there, it might be done on another layer 441 currentStart = cast(int) i; 442 } 443 } 444 } 445 446 fixedup ~= sql[currentStart .. $]; 447 448 return fixedup; 449 } 450 451 /// Note: ?n params are zero based! 452 string escapedVariants(Database db, in string sql, Variant[] t) { 453 // FIXME: let's make ?? render as ? so we have some escaping capability 454 // if nothing to escape or nothing to escape with, don't bother 455 if(t.length > 0 && sql.indexOf("?") != -1) { 456 string fixedup; 457 int currentIndex; 458 int currentStart = 0; 459 foreach(i, dchar c; sql) { 460 if(c == '?') { 461 fixedup ~= sql[currentStart .. i]; 462 463 int idx = -1; 464 currentStart = cast(int) i + 1; 465 if((i + 1) < sql.length) { 466 auto n = sql[i + 1]; 467 if(n >= '0' && n <= '9') { 468 currentStart = cast(int) i + 2; 469 idx = n - '0'; 470 } 471 } 472 if(idx == -1) { 473 idx = currentIndex; 474 currentIndex++; 475 } 476 477 if(idx < 0 || idx >= t.length) 478 throw new Exception("SQL Parameter index is out of bounds: " ~ to!string(idx) ~ " at `"~sql[0 .. i]~"`"); 479 480 fixedup ~= toSql(db, t[idx]); 481 } 482 } 483 484 fixedup ~= sql[currentStart .. $]; 485 486 return fixedup; 487 /* 488 string fixedup; 489 int pos = 0; 490 491 492 void escAndAdd(string str, int q) { 493 fixedup ~= sql[pos..q] ~ '\'' ~ db.escape(str) ~ '\''; 494 495 } 496 497 foreach(a; t) { 498 int q = sql[pos..$].indexOf("?"); 499 if(q == -1) 500 break; 501 q += pos; 502 503 auto v = a.peek!(void*); 504 if(v && (*v is null)) 505 fixedup ~= sql[pos..q] ~ "NULL"; 506 else { 507 string str = to!string(a); 508 escAndAdd(str, q); 509 } 510 511 pos = q+1; 512 } 513 514 fixedup ~= sql[pos..$]; 515 516 sql = fixedup; 517 */ 518 } 519 520 return sql; 521 } 522 523 524 525 526 527 528 enum UpdateOrInsertMode { 529 CheckForMe, 530 AlwaysUpdate, 531 AlwaysInsert 532 } 533 534 535 // BIG FIXME: this should really use prepared statements 536 int updateOrInsert(Database db, string table, string[string] values, string where, UpdateOrInsertMode mode = UpdateOrInsertMode.CheckForMe, string key = "id") { 537 538 string identifierQuote = ""; 539 540 bool insert = false; 541 542 final switch(mode) { 543 case UpdateOrInsertMode.CheckForMe: 544 auto res = db.query("SELECT "~key~" FROM "~identifierQuote~db.escape(table)~identifierQuote~" WHERE " ~ where); 545 insert = res.empty; 546 547 break; 548 case UpdateOrInsertMode.AlwaysInsert: 549 insert = true; 550 break; 551 case UpdateOrInsertMode.AlwaysUpdate: 552 insert = false; 553 break; 554 } 555 556 557 if(insert) { 558 string insertSql = "INSERT INTO " ~identifierQuote ~ db.escape(table) ~ identifierQuote ~ " "; 559 560 bool outputted = false; 561 string vs, cs; 562 foreach(column, value; values) { 563 if(column is null) 564 continue; 565 if(outputted) { 566 vs ~= ", "; 567 cs ~= ", "; 568 } else 569 outputted = true; 570 571 //cs ~= "`" ~ db.escape(column) ~ "`"; 572 cs ~= identifierQuote ~ column ~ identifierQuote; // FIXME: possible insecure 573 if(value is null) 574 vs ~= "NULL"; 575 else 576 vs ~= "'" ~ db.escape(value) ~ "'"; 577 } 578 579 if(!outputted) 580 return 0; 581 582 583 insertSql ~= "(" ~ cs ~ ")"; 584 insertSql ~= " VALUES "; 585 insertSql ~= "(" ~ vs ~ ")"; 586 587 db.query(insertSql); 588 589 return 0; // db.lastInsertId; 590 } else { 591 string updateSql = "UPDATE "~identifierQuote~db.escape(table)~identifierQuote~" SET "; 592 593 bool outputted = false; 594 foreach(column, value; values) { 595 if(column is null) 596 continue; 597 if(outputted) 598 updateSql ~= ", "; 599 else 600 outputted = true; 601 602 if(value is null) 603 updateSql ~= identifierQuote ~ db.escape(column) ~ identifierQuote ~ " = NULL"; 604 else 605 updateSql ~= identifierQuote ~ db.escape(column) ~ identifierQuote ~ " = '" ~ db.escape(value) ~ "'"; 606 } 607 608 if(!outputted) 609 return 0; 610 611 updateSql ~= " WHERE " ~ where; 612 613 db.query(updateSql); 614 return 0; 615 } 616 } 617 618 619 620 621 622 string fixupSqlForDataObjectUse(string sql, string[string] keyMapping = null) { 623 624 string[] tableNames; 625 626 string piece = sql; 627 sizediff_t idx; 628 while((idx = piece.indexOf("JOIN")) != -1) { 629 auto start = idx + 5; 630 auto i = start; 631 while(piece[i] != ' ' && piece[i] != '\n' && piece[i] != '\t' && piece[i] != ',') 632 i++; 633 auto end = i; 634 635 tableNames ~= strip(piece[start..end]); 636 637 piece = piece[end..$]; 638 } 639 640 idx = sql.indexOf("FROM"); 641 if(idx != -1) { 642 auto start = idx + 5; 643 auto i = start; 644 start = i; 645 while(i < sql.length && !(sql[i] > 'A' && sql[i] <= 'Z')) // if not uppercase, except for A (for AS) to avoid SQL keywords (hack) 646 i++; 647 648 auto from = sql[start..i]; 649 auto pieces = from.split(","); 650 foreach(p; pieces) { 651 p = p.strip(); 652 start = 0; 653 i = 0; 654 while(i < p.length && p[i] != ' ' && p[i] != '\n' && p[i] != '\t' && p[i] != ',') 655 i++; 656 657 tableNames ~= strip(p[start..i]); 658 } 659 660 string sqlToAdd; 661 foreach(tbl; tableNames) { 662 if(tbl.length) { 663 string keyName = "id"; 664 if(tbl in keyMapping) 665 keyName = keyMapping[tbl]; 666 sqlToAdd ~= ", " ~ tbl ~ "." ~ keyName ~ " AS " ~ "id_from_" ~ tbl; 667 } 668 } 669 670 sqlToAdd ~= " "; 671 672 sql = sql[0..idx] ~ sqlToAdd ~ sql[idx..$]; 673 } 674 675 return sql; 676 } 677 678 679 680 681 682 /* 683 This is like a result set 684 685 686 DataObject res = [...]; 687 688 res.name = "Something"; 689 690 res.commit; // runs the actual update or insert 691 692 693 res = new DataObject(fields, tables 694 695 696 697 698 699 700 701 when doing a select, we need to figure out all the tables and modify the query to include the ids we need 702 703 704 search for FROM and JOIN 705 the next token is the table name 706 707 right before the FROM, add the ids of each table 708 709 710 given: 711 SELECT name, phone FROM customers LEFT JOIN phones ON customer.id = phones.cust_id 712 713 we want: 714 SELECT name, phone, customers.id AS id_from_customers, phones.id AS id_from_phones FROM customers LEFT JOIN phones ON customer.id[...]; 715 716 */ 717 718 mixin template DataObjectConstructors() { 719 this(Database db, string[string] res, Tuple!(string, string)[string] mappings) { 720 super(db, res, mappings); 721 } 722 } 723 724 string yield(string what) { return `if(auto result = dg(`~what~`)) return result;`; } 725 726 import std.typecons; 727 import std.json; // for json value making 728 class DataObject { 729 // lets you just free-form set fields, assuming they all come from the given table 730 // note it doesn't try to handle joins for new rows. you've gotta do that yourself 731 this(Database db, string table) { 732 assert(db !is null); 733 this.db = db; 734 this.table = table; 735 736 mode = UpdateOrInsertMode.CheckForMe; 737 } 738 739 JSONValue makeJsonValue() { 740 JSONValue val; 741 JSONValue[string] valo; 742 //val.type = JSON_TYPE.OBJECT; 743 foreach(k, v; fields) { 744 JSONValue s; 745 //s.type = JSON_TYPE.STRING; 746 s.str = v; 747 valo[k] = s; 748 } 749 val = valo; 750 return val; 751 } 752 753 this(Database db, string[string] res, Tuple!(string, string)[string] mappings) { 754 this.db = db; 755 this.mappings = mappings; 756 this.fields = res; 757 758 mode = UpdateOrInsertMode.AlwaysUpdate; 759 } 760 761 string table; 762 // table, column [alias] 763 Tuple!(string, string)[string] mappings; 764 765 // value [field] [table] 766 string[string][string] multiTableKeys; // note this is not set internally tight now 767 // but it can be set manually to do multi table mappings for automatic update 768 769 770 string opDispatch(string field, string file = __FILE__, size_t line = __LINE__)() 771 if((field.length < 8 || field[0..8] != "id_from_") && field != "popFront") 772 { 773 if(field !in fields) 774 throw new Exception("no such field " ~ field, file, line); 775 776 return fields[field]; 777 } 778 779 string opDispatch(string field, T)(T t) 780 if((field.length < 8 || field[0..8] != "id_from_") && field != "popFront") 781 { 782 static if(__traits(compiles, t is null)) { 783 if(t is null) 784 setImpl(field, null); 785 else 786 setImpl(field, to!string(t)); 787 } else 788 setImpl(field, to!string(t)); 789 790 return fields[field]; 791 } 792 793 794 private void setImpl(string field, string value) { 795 if(field in fields) { 796 if(fields[field] != value) 797 changed[field] = true; 798 } else { 799 changed[field] = true; 800 } 801 802 fields[field] = value; 803 } 804 805 public void setWithoutChange(string field, string value) { 806 fields[field] = value; 807 } 808 809 int opApply(int delegate(ref string) dg) { 810 foreach(a; fields) 811 mixin(yield("a")); 812 813 return 0; 814 } 815 816 int opApply(int delegate(ref string, ref string) dg) { 817 foreach(a, b; fields) 818 mixin(yield("a, b")); 819 820 return 0; 821 } 822 823 824 string opIndex(string field, string file = __FILE__, size_t line = __LINE__) { 825 if(field !in fields) 826 throw new DatabaseException("No such field in data object: " ~ field, file, line); 827 return fields[field]; 828 } 829 830 string opIndexAssign(string value, string field) { 831 setImpl(field, value); 832 return value; 833 } 834 835 string* opBinary(string op)(string key) if(op == "in") { 836 return key in fields; 837 } 838 839 string[string] fields; 840 bool[string] changed; 841 842 void commitChanges() { 843 commitChanges(cast(string) null, null); 844 } 845 846 void commitChanges(string key, string keyField) { 847 commitChanges(key is null ? null : [key], keyField is null ? null : [keyField]); 848 } 849 850 void commitChanges(string[] keys, string[] keyFields = null) { 851 string[string][string] toUpdate; 852 int updateCount = 0; 853 foreach(field, c; changed) { 854 if(c) { 855 string tbl, col; 856 if(mappings is null) { 857 tbl = this.table; 858 col = field; 859 } else { 860 if(field !in mappings) 861 assert(0, "no such mapping for " ~ field); 862 auto m = mappings[field]; 863 tbl = m[0]; 864 col = m[1]; 865 } 866 867 toUpdate[tbl][col] = fields[field]; 868 updateCount++; 869 } 870 } 871 872 if(updateCount) { 873 db.startTransaction(); 874 scope(success) db.query("COMMIT"); 875 scope(failure) db.query("ROLLBACK"); 876 877 foreach(tbl, values; toUpdate) { 878 string where, keyFieldToPass; 879 880 if(keys is null) { 881 keys = [null]; 882 } 883 884 if(multiTableKeys is null || tbl !in multiTableKeys) 885 foreach(i, key; keys) { 886 string keyField; 887 888 if(key is null) { 889 key = "id_from_" ~ tbl; 890 if(key !in fields) 891 key = "id"; 892 } 893 894 if(i >= keyFields.length || keyFields[i] is null) { 895 if(key == "id_from_" ~ tbl) 896 keyField = "id"; 897 else 898 keyField = key; 899 } else { 900 keyField = keyFields[i]; 901 } 902 903 904 if(where.length) 905 where ~= " AND "; 906 907 auto f = key in fields ? fields[key] : null; 908 if(f is null) 909 where ~= keyField ~ " = NULL"; 910 else 911 where ~= keyField ~ " = '"~db.escape(f)~"'" ; 912 if(keyFieldToPass.length) 913 keyFieldToPass ~= ", "; 914 915 keyFieldToPass ~= keyField; 916 } 917 else { 918 foreach(keyField, v; multiTableKeys[tbl]) { 919 if(where.length) 920 where ~= " AND "; 921 922 where ~= keyField ~ " = '"~db.escape(v)~"'" ; 923 if(keyFieldToPass.length) 924 keyFieldToPass ~= ", "; 925 926 keyFieldToPass ~= keyField; 927 } 928 } 929 930 931 932 updateOrInsert(db, tbl, values, where, mode, keyFieldToPass); 933 } 934 935 changed = null; 936 } 937 } 938 939 void commitDelete() { 940 if(mode == UpdateOrInsertMode.AlwaysInsert) 941 throw new Exception("Cannot delete an item not in the database"); 942 943 assert(table.length); // FIXME, should work with fancy items too 944 945 // FIXME: escaping and primary key questions 946 db.query("DELETE FROM " ~ table ~ " WHERE id = '" ~ db.escape(fields["id"]) ~ "'"); 947 } 948 949 string getAlias(string table, string column) { 950 string ali; 951 if(mappings is null) { 952 if(this.table is null) { 953 mappings[column] = tuple(table, column); 954 return column; 955 } else { 956 assert(table == this.table); 957 ali = column; 958 } 959 } else { 960 foreach(a, what; mappings) 961 if(what[0] == table && what[1] == column 962 && a.indexOf("id_from_") == -1) { 963 ali = a; 964 break; 965 } 966 } 967 968 return ali; 969 } 970 971 void set(string table, string column, string value) { 972 string ali = getAlias(table, column); 973 //assert(ali in fields); 974 setImpl(ali, value); 975 } 976 977 string select(string table, string column) { 978 string ali = getAlias(table, column); 979 //assert(ali in fields); 980 if(ali in fields) 981 return fields[ali]; 982 return null; 983 } 984 985 DataObject addNew() { 986 auto n = new DataObject(db, null); 987 988 n.db = this.db; 989 n.table = this.table; 990 n.mappings = this.mappings; 991 992 foreach(k, v; this.fields) 993 if(k.indexOf("id_from_") == -1) 994 n.fields[k] = v; 995 else 996 n.fields[k] = null; // don't copy ids 997 998 n.mode = UpdateOrInsertMode.AlwaysInsert; 999 1000 return n; 1001 } 1002 1003 Database db; 1004 UpdateOrInsertMode mode; 1005 } 1006 1007 /** 1008 You can subclass DataObject if you want to 1009 get some compile time checks or better types. 1010 1011 You'll want to disable opDispatch, then forward your 1012 properties to the super opDispatch. 1013 */ 1014 1015 /*mixin*/ string DataObjectField(T, string table, string column, string aliasAs = null)() { 1016 string aliasAs_; 1017 if(aliasAs is null) 1018 aliasAs_ = column; 1019 else 1020 aliasAs_ = aliasAs; 1021 return ` 1022 @property void `~aliasAs_~`(`~T.stringof~` setTo) { 1023 super.set("`~table~`", "`~column~`", to!string(setTo)); 1024 } 1025 1026 @property `~T.stringof~` `~aliasAs_~` () { 1027 return to!(`~T.stringof~`)(super.select("`~table~`", "`~column~`")); 1028 } 1029 `; 1030 } 1031 1032 mixin template StrictDataObject() { 1033 // disable opdispatch 1034 string opDispatch(string name)(...) if (0) {} 1035 } 1036 1037 1038 string createDataObjectFieldsFromAlias(string table, fieldsToUse)() { 1039 string ret; 1040 1041 fieldsToUse f; 1042 foreach(member; __traits(allMembers, fieldsToUse)) { 1043 ret ~= DataObjectField!(typeof(__traits(getMember, f, member)), table, member); 1044 } 1045 1046 return ret; 1047 } 1048 1049 1050 /** 1051 This creates an editable data object out of a simple struct. 1052 1053 struct MyFields { 1054 int id; 1055 string name; 1056 } 1057 1058 alias SimpleDataObject!("my_table", MyFields) User; 1059 1060 1061 User a = new User(db); 1062 1063 a.id = 30; 1064 a.name = "hello"; 1065 a.commitChanges(); // tries an update or insert on the my_table table 1066 1067 1068 Unlike the base DataObject class, this template provides compile time 1069 checking for types and names, based on the struct you pass in: 1070 1071 a.id = "aa"; // compile error 1072 1073 a.notAField; // compile error 1074 */ 1075 class SimpleDataObject(string tableToUse, fieldsToUse) : DataObject { 1076 mixin StrictDataObject!(); 1077 1078 mixin(createDataObjectFieldsFromAlias!(tableToUse, fieldsToUse)()); 1079 1080 this(Database db) { 1081 super(db, tableToUse); 1082 } 1083 } 1084 1085 /** 1086 Given some SQL, it finds the CREATE TABLE 1087 instruction for the given tableName. 1088 (this is so it can find one entry from 1089 a file with several SQL commands. But it 1090 may break on a complex file, so try to only 1091 feed it simple sql files.) 1092 1093 From that, it pulls out the members to create a 1094 simple struct based on it. 1095 1096 It's not terribly smart, so it will probably 1097 break on complex tables. 1098 1099 Data types handled: 1100 INTEGER, SMALLINT, MEDIUMINT -> D's int 1101 TINYINT -> D's bool 1102 BIGINT -> D's long 1103 TEXT, VARCHAR -> D's string 1104 FLOAT, DOUBLE -> D's double 1105 1106 It also reads DEFAULT values to pass to D, except for NULL. 1107 It ignores any length restrictions. 1108 1109 Bugs: 1110 Skips all constraints 1111 Doesn't handle nullable fields, except with strings 1112 It only handles SQL keywords if they are all caps 1113 1114 This, when combined with SimpleDataObject!(), 1115 can automatically create usable D classes from 1116 SQL input. 1117 */ 1118 struct StructFromCreateTable(string sql, string tableName) { 1119 mixin(getCreateTable(sql, tableName)); 1120 } 1121 1122 string getCreateTable(string sql, string tableName) { 1123 skip: 1124 while(readWord(sql) != "CREATE") {} 1125 1126 assert(readWord(sql) == "TABLE"); 1127 1128 if(readWord(sql) != tableName) 1129 goto skip; 1130 1131 assert(readWord(sql) == "("); 1132 1133 int state; 1134 int parens; 1135 1136 struct Field { 1137 string name; 1138 string type; 1139 string defaultValue; 1140 } 1141 Field*[] fields; 1142 1143 string word = readWord(sql); 1144 Field* current = new Field(); // well, this is interesting... under new DMD, not using new breaks it in CTFE because it overwrites the one entry! 1145 while(word != ")" || parens) { 1146 if(word == ")") { 1147 parens --; 1148 word = readWord(sql); 1149 continue; 1150 } 1151 if(word == "(") { 1152 parens ++; 1153 word = readWord(sql); 1154 continue; 1155 } 1156 switch(state) { 1157 default: assert(0); 1158 case 0: 1159 if(word[0] >= 'A' && word[0] <= 'Z') { 1160 state = 4; 1161 break; // we want to skip this since it starts with a keyword (we hope) 1162 } 1163 current.name = word; 1164 state = 1; 1165 break; 1166 case 1: 1167 current.type ~= word; 1168 state = 2; 1169 break; 1170 case 2: 1171 if(word == "DEFAULT") 1172 state = 3; 1173 else if (word == ",") { 1174 fields ~= current; 1175 current = new Field(); 1176 state = 0; // next 1177 } 1178 break; 1179 case 3: 1180 current.defaultValue = word; 1181 state = 2; // back to skipping 1182 break; 1183 case 4: 1184 if(word == ",") 1185 state = 0; 1186 } 1187 1188 word = readWord(sql); 1189 } 1190 1191 if(current.name !is null) 1192 fields ~= current; 1193 1194 1195 string structCode; 1196 foreach(field; fields) { 1197 structCode ~= "\t"; 1198 1199 switch(field.type) { 1200 case "INTEGER": 1201 case "SMALLINT": 1202 case "MEDIUMINT": 1203 structCode ~= "int"; 1204 break; 1205 case "BOOLEAN": 1206 case "TINYINT": 1207 structCode ~= "bool"; 1208 break; 1209 case "BIGINT": 1210 structCode ~= "long"; 1211 break; 1212 case "CHAR": 1213 case "char": 1214 case "VARCHAR": 1215 case "varchar": 1216 case "TEXT": 1217 case "text": 1218 structCode ~= "string"; 1219 break; 1220 case "FLOAT": 1221 case "DOUBLE": 1222 structCode ~= "double"; 1223 break; 1224 default: 1225 assert(0, "unknown type " ~ field.type ~ " for " ~ field.name); 1226 } 1227 1228 structCode ~= " "; 1229 structCode ~= field.name; 1230 1231 if(field.defaultValue !is null) { 1232 structCode ~= " = " ~ field.defaultValue; 1233 } 1234 1235 structCode ~= ";\n"; 1236 } 1237 1238 return structCode; 1239 } 1240 1241 string readWord(ref string src) { 1242 reset: 1243 while(src[0] == ' ' || src[0] == '\t' || src[0] == '\n') 1244 src = src[1..$]; 1245 if(src.length >= 2 && src[0] == '-' && src[1] == '-') { // a comment, skip it 1246 while(src[0] != '\n') 1247 src = src[1..$]; 1248 goto reset; 1249 } 1250 1251 int start, pos; 1252 if(src[0] == '`') { 1253 src = src[1..$]; 1254 while(src[pos] != '`') 1255 pos++; 1256 goto gotit; 1257 } 1258 1259 1260 while( 1261 (src[pos] >= 'A' && src[pos] <= 'Z') 1262 || 1263 (src[pos] >= 'a' && src[pos] <= 'z') 1264 || 1265 (src[pos] >= '0' && src[pos] <= '9') 1266 || 1267 src[pos] == '_' 1268 ) 1269 pos++; 1270 gotit: 1271 if(pos == 0) 1272 pos = 1; 1273 1274 string tmp = src[0..pos]; 1275 1276 if(src[pos] == '`') 1277 pos++; // skip the ending quote; 1278 1279 src = src[pos..$]; 1280 1281 return tmp; 1282 } 1283 1284 /// Combines StructFromCreateTable and SimpleDataObject into a one-stop template. 1285 /// alias DataObjectFromSqlCreateTable(import("file.sql"), "my_table") MyTable; 1286 template DataObjectFromSqlCreateTable(string sql, string tableName) { 1287 alias SimpleDataObject!(tableName, StructFromCreateTable!(sql, tableName)) DataObjectFromSqlCreateTable; 1288 } 1289 1290 /+ 1291 class MyDataObject : DataObject { 1292 this() { 1293 super(new Database("localhost", "root", "pass", "social"), null); 1294 } 1295 1296 mixin StrictDataObject!(); 1297 1298 mixin(DataObjectField!(int, "users", "id")); 1299 } 1300 1301 void main() { 1302 auto a = new MyDataObject; 1303 1304 a.fields["id"] = "10"; 1305 1306 a.id = 34; 1307 1308 a.commitChanges; 1309 } 1310 +/ 1311 1312 /* 1313 alias DataObjectFromSqlCreateTable!(import("db.sql"), "users") Test; 1314 1315 void main() { 1316 auto a = new Test(null); 1317 1318 a.cool = "way"; 1319 a.value = 100; 1320 } 1321 */ 1322 1323 void typeinfoBugWorkaround() { 1324 assert(0, to!string(typeid(immutable(char[])[immutable(char)[]]))); 1325 } 1326 1327 mixin template DatabaseOperations(string table) { 1328 DataObject getAsDb(Database db) { 1329 return objectToDataObject!(typeof(this))(this, db, table); 1330 } 1331 1332 static typeof(this) fromRow(Row row) { 1333 return rowToObject!(typeof(this))(row); 1334 } 1335 1336 static typeof(this) fromId(Database db, long id) { 1337 auto query = new SelectBuilder(db); 1338 query.table = table; 1339 query.fields ~= "*"; 1340 query.wheres ~= "id = ?0"; 1341 auto res = db.query(query.toString(), id); 1342 if(res.empty) 1343 throw new Exception("no such row"); 1344 return fromRow(res.front); 1345 } 1346 1347 } 1348 1349 import std.traits, std.datetime; 1350 enum DbSave; 1351 enum DbNullable; 1352 alias AliasHelper(alias T) = T; 1353 1354 T rowToObject(T)(Row row) { 1355 import arsd.dom, arsd.cgi; 1356 1357 T t; 1358 foreach(memberName; __traits(allMembers, T)) { 1359 alias member = AliasHelper!(__traits(getMember, t, memberName)); 1360 foreach(attr; __traits(getAttributes, member)) { 1361 static if(is(attr == DbSave)) { 1362 static if(is(typeof(member) == enum)) 1363 __traits(getMember, t, memberName) = cast(typeof(member)) to!int(row[memberName]); 1364 else static if(is(typeof(member) == bool)) { 1365 __traits(getMember, t, memberName) = row[memberName][0] == 't'; 1366 } else static if(is(typeof(member) == Html)) { 1367 __traits(getMember, t, memberName).source = row[memberName]; 1368 } else static if(is(typeof(member) == DateTime)) 1369 __traits(getMember, t, memberName) = cast(DateTime) dTimeToSysTime(to!long(row[memberName])); 1370 else { 1371 if(row[memberName].length) 1372 __traits(getMember, t, memberName) = to!(typeof(member))(row[memberName]); 1373 // otherwise, we'll leave it as .init - most likely null 1374 } 1375 } 1376 } 1377 } 1378 return t; 1379 1380 } 1381 1382 DataObject objectToDataObject(T)(T t, Database db, string table) { 1383 import arsd.dom, arsd.cgi; 1384 1385 DataObject obj = new DataObject(db, table); 1386 foreach(memberName; __traits(allMembers, T)) { 1387 alias member = AliasHelper!(__traits(getMember, t, memberName)); 1388 foreach(attr; __traits(getAttributes, member)) { 1389 static if(is(attr == DbSave)) { 1390 static if(is(typeof(member) == enum)) 1391 obj.opDispatch!memberName(cast(int) __traits(getMember, t, memberName)); 1392 else static if(is(typeof(member) == Html)) { 1393 obj.opDispatch!memberName(__traits(getMember, t, memberName).source); 1394 } else static if(is(typeof(member) == DateTime)) 1395 obj.opDispatch!memberName(dateTimeToDTime(__traits(getMember, t, memberName))); 1396 else { 1397 bool done; 1398 foreach(attr2; __traits(getAttributes, member)) { 1399 static if(is(attr2 == DbNullable)) { 1400 if(__traits(getMember, t, memberName) == 0) 1401 done = true; 1402 } 1403 } 1404 1405 if(!done) 1406 obj.opDispatch!memberName(__traits(getMember, t, memberName)); 1407 } 1408 } 1409 } 1410 } 1411 return obj; 1412 } 1413 1414 1415 1416 void fillData(T)(string delegate(string, string) setter, T obj, string name) { 1417 fillData( (k, v) { setter(k, v); }, obj, name); 1418 } 1419 1420 void fillData(T)(void delegate(string, string) setter, T obj, string name) { 1421 import arsd.dom, arsd.cgi; 1422 1423 import std.traits; 1424 static if(!isSomeString!T && isArray!T) { 1425 // FIXME: indexing 1426 foreach(o; obj) 1427 fillData(setter, o, name); 1428 } else static if(is(T == DateTime)) { 1429 fillData(setter, obj.toISOExtString(), name); 1430 } else static if(is(T == Html)) { 1431 fillData(setter, obj.source, name); 1432 } else static if(is(T == struct)) { 1433 foreach(idx, memberName; __traits(allMembers, T)) { 1434 alias member = AliasHelper!(__traits(getMember, obj, memberName)); 1435 static if(!is(typeof(member) == function)) 1436 fillData(setter, __traits(getMember, obj, memberName), name ~ "." ~ memberName); 1437 else static if(is(typeof(member) == function)) { 1438 static if(functionAttributes!member & FunctionAttribute.property) { 1439 fillData(setter, __traits(getMember, obj, memberName)(), name ~ "." ~ memberName); 1440 } 1441 } 1442 } 1443 } else { 1444 auto value = to!string(obj); 1445 setter(name, value); 1446 } 1447 } 1448 1449 struct varchar(size_t max) { 1450 private string payload; 1451 1452 this(string s, string file = __FILE__, size_t line = __LINE__) { 1453 opAssign(s, file, line); 1454 } 1455 1456 typeof(this) opAssign(string s, string file = __FILE__, size_t line = __LINE__) { 1457 if(s.length > max) 1458 throw new Exception(s ~ " :: too long", file, line); 1459 payload = s; 1460 1461 return this; 1462 } 1463 1464 string asString() { 1465 return payload; 1466 1467 } 1468 alias asString this; 1469 } 1470 1471 1472