1 /++ 2 Implementation of the [arsd.database.Database] interface for 3 accessing MySQL (and MariaDB) databases. Uses the official MySQL client 4 library, and thus needs that installed to compile and run. 5 6 $(PITFALL 7 If you're using MySQL client library v5.0 or less, 8 you must pass this to dmd: `-version=Less_Than_MySQL_51` 9 This is important - otherwise you will see bizarre segfaults! 10 ) 11 +/ 12 module arsd.mysql; 13 14 15 //version(MySQL_51) { 16 // we good 17 /*} else*/ version(Less_Than_MySQL_51) { 18 // we good 19 } else { 20 // default now is mysql 5.1 or up - it has been long 21 // enough that surely most everyone uses it and we don't 22 // need the pragma warning anymore. Of course, the old is 23 // still available if you need to explicitly opt in. 24 version = MySQL_51; 25 } 26 27 version(Windows) { 28 pragma(lib, "libmysql"); 29 } 30 else { 31 pragma(lib, "mysqlclient"); 32 } 33 34 public import arsd.database; 35 36 import std.stdio; 37 import std.exception; 38 import std.string; 39 import std.conv; 40 import std.typecons; 41 import core.stdc.config; 42 43 /++ 44 Represents a query result. You can loop over this with a 45 `foreach` statement to access individual [Row|rows]. 46 47 [Row]s expose both an index and associative array interface, 48 so you can get `row[0]` for the first item, or `row["name"]` 49 to get a column by name from the result set. 50 +/ 51 class MySqlResult : ResultSet { 52 private int[string] mapping; 53 private MYSQL_RES* result; 54 55 private int itemsTotal; 56 private int itemsUsed; 57 58 string sql; 59 60 this(MYSQL_RES* r, string sql) { 61 result = r; 62 itemsTotal = cast(int) length(); 63 itemsUsed = 0; 64 65 this.sql = sql; 66 67 // prime it 68 if(itemsTotal) 69 fetchNext(); 70 } 71 72 ~this() { 73 if(result !is null) 74 mysql_free_result(result); 75 } 76 77 78 MYSQL_FIELD[] fields() { 79 int numFields = mysql_num_fields(result); 80 auto fields = mysql_fetch_fields(result); 81 82 MYSQL_FIELD[] ret; 83 for(int i = 0; i < numFields; i++) { 84 ret ~= fields[i]; 85 } 86 87 return ret; 88 } 89 90 91 /// The number of returned rows 92 override size_t length() { 93 if(result is null) 94 return 0; 95 return cast(int) mysql_num_rows(result); 96 } 97 98 /// Range primitive used by `foreach` 99 /// You may also use this to check if there was any result. 100 override bool empty() { 101 return itemsUsed == itemsTotal; 102 } 103 104 /// Range primitive used by `foreach` 105 override Row front() { 106 return row; 107 } 108 109 /// Range primitive used by `foreach` 110 override void popFront() { 111 itemsUsed++; 112 if(itemsUsed < itemsTotal) { 113 fetchNext(); 114 } 115 } 116 117 override int getFieldIndex(string field) { 118 if(mapping is null) 119 makeFieldMapping(); 120 debug { 121 if(field !in mapping) 122 throw new Exception(field ~ " not in result"); 123 } 124 return mapping[field]; 125 } 126 127 private void makeFieldMapping() { 128 int numFields = mysql_num_fields(result); 129 auto fields = mysql_fetch_fields(result); 130 131 if(fields is null) 132 return; 133 134 for(int i = 0; i < numFields; i++) { 135 if(fields[i].name !is null) 136 mapping[fromCstring(fields[i].name, fields[i].name_length)] = i; 137 } 138 } 139 140 private void fetchNext() { 141 assert(result); 142 auto r = mysql_fetch_row(result); 143 if(r is null) 144 throw new Exception("there is no next row"); 145 uint numFields = mysql_num_fields(result); 146 auto lengths = mysql_fetch_lengths(result); 147 string[] row; 148 // potential FIXME: not really binary safe 149 150 columnIsNull.length = numFields; 151 for(int a = 0; a < numFields; a++) { 152 if(*(r+a) is null) { 153 row ~= null; 154 columnIsNull[a] = true; 155 } else { 156 row ~= fromCstring(*(r+a), *(lengths + a)); 157 columnIsNull[a] = false; 158 } 159 } 160 161 this.row.row = row; 162 this.row.resultSet = this; 163 } 164 165 166 override string[] fieldNames() { 167 int numFields = mysql_num_fields(result); 168 auto fields = mysql_fetch_fields(result); 169 170 string[] names; 171 for(int i = 0; i < numFields; i++) { 172 names ~= fromCstring(fields[i].name, fields[i].name_length); 173 } 174 175 return names; 176 } 177 178 179 180 bool[] columnIsNull; 181 Row row; 182 } 183 184 /++ 185 The main class for accessing the MySql database. 186 187 --- 188 // connect to database with the constructor 189 auto db = new MySql("localhost", "my_user", "my_password", "my_database_name"); 190 // use the query function to execute sql... 191 // use ? for data placeholders... 192 db.query("INSERT INTO people (id, name) VALUES (?, ?)", 10, "My Name"); 193 // and use foreach to loop over result sets 194 foreach(row; db.query("SELECT id, name FROM people ORDER BY name LIMIT 10")) 195 writeln(row[0], " ", row["name"]); // index and name supported 196 --- 197 +/ 198 class MySql : Database { 199 this(string host, string user, string pass, string db, uint port = 0) { 200 mysql = enforce!(DatabaseException)( 201 mysql_init(null), 202 "Couldn't init mysql"); 203 enforce!(DatabaseException)( 204 mysql_real_connect(mysql, toCstring(host), toCstring(user), toCstring(pass), toCstring(db), port, null, 0), 205 error()); 206 207 dbname = db; 208 209 // we want UTF8 for everything 210 211 query("SET NAMES 'utf8'"); 212 //query("SET CHARACTER SET utf8"); 213 } 214 215 string dbname; 216 217 /// 218 override void startTransaction() { 219 query("START TRANSACTION"); 220 } 221 222 223 string sysTimeToValue(SysTime s) { 224 return "cast('" ~ escape(s.toISOExtString()) ~ "' as datetime)"; 225 } 226 227 string error() { 228 return fromCstring(mysql_error(mysql)); 229 } 230 231 void close() { 232 mysql_close(mysql); 233 } 234 235 ~this() { 236 close(); 237 } 238 239 /// 240 int lastInsertId() { 241 return cast(int) mysql_insert_id(mysql); 242 } 243 244 245 246 /// Builds and executes an INERT INTO statement 247 int insert(string table, MySqlResult result, string[string] columnsToModify, string[] columnsToSkip) { 248 assert(!result.empty); 249 string sql = "INSERT INTO `" ~ table ~ "` "; 250 251 string cols = "("; 252 string vals = "("; 253 bool outputted = false; 254 255 string[string] columns; 256 auto cnames = result.fieldNames; 257 foreach(i, col; result.front.toStringArray) { 258 bool skipMe = false; 259 foreach(skip; columnsToSkip) { 260 if(cnames[i] == skip) { 261 skipMe = true; 262 break; 263 } 264 } 265 if(skipMe) 266 continue; 267 268 if(outputted) { 269 cols ~= ","; 270 vals ~= ","; 271 } else 272 outputted = true; 273 274 cols ~= cnames[i]; 275 276 if(result.columnIsNull[i] && cnames[i] !in columnsToModify) 277 vals ~= "NULL"; 278 else { 279 string v = col; 280 if(cnames[i] in columnsToModify) 281 v = columnsToModify[cnames[i]]; 282 283 vals ~= "'" ~ escape(v) ~ "'"; 284 285 } 286 } 287 288 cols ~= ")"; 289 vals ~= ")"; 290 291 sql ~= cols ~ " VALUES " ~ vals; 292 293 query(sql); 294 295 result.popFront; 296 297 return lastInsertId; 298 } 299 300 string escape(string str) { 301 ubyte[] buffer = new ubyte[str.length * 2 + 1]; 302 buffer.length = mysql_real_escape_string(mysql, buffer.ptr, cast(cstring) str.ptr, cast(uint) str.length); 303 304 return cast(string) buffer; 305 } 306 307 string escaped(T...)(string sql, T t) { 308 static if(t.length > 0) { 309 string fixedup; 310 int pos = 0; 311 312 313 void escAndAdd(string str, int q) { 314 ubyte[] buffer = new ubyte[str.length * 2 + 1]; 315 buffer.length = mysql_real_escape_string(mysql, buffer.ptr, cast(cstring) str.ptr, str.length); 316 317 fixedup ~= sql[pos..q] ~ '\'' ~ cast(string) buffer ~ '\''; 318 319 } 320 321 foreach(a; t) { 322 int q = sql[pos..$].indexOf("?"); 323 if(q == -1) 324 break; 325 q += pos; 326 327 static if(__traits(compiles, t is null)) { 328 if(t is null) 329 fixedup ~= sql[pos..q] ~ "NULL"; 330 else 331 escAndAdd(to!string(*a), q); 332 } else { 333 string str = to!string(a); 334 escAndAdd(str, q); 335 } 336 337 pos = q+1; 338 } 339 340 fixedup ~= sql[pos..$]; 341 342 sql = fixedup; 343 344 //writefln("\n\nExecuting sql: %s", sql); 345 } 346 347 return sql; 348 } 349 350 351 /// Gets a minimal ORM object from a query 352 ResultByDataObject!R queryDataObject(R = DataObject, T...)(string sql, T t) { 353 // modify sql for the best data object grabbing 354 sql = fixupSqlForDataObjectUse(sql); 355 356 auto magic = query(sql, t); 357 return ResultByDataObject!R(cast(MySqlResult) magic, this); 358 } 359 360 361 /// ditto 362 ResultByDataObject!R queryDataObjectWithCustomKeys(R = DataObject, T...)(string[string] keyMapping, string sql, T t) { 363 sql = fixupSqlForDataObjectUse(sql, keyMapping); 364 365 auto magic = query(sql, t); 366 return ResultByDataObject!R(cast(MySqlResult) magic, this); 367 } 368 369 370 371 /// 372 int affectedRows() { 373 return cast(int) mysql_affected_rows(mysql); 374 } 375 376 override ResultSet queryImpl(string sql, Variant[] args...) { 377 sql = escapedVariants(this, sql, args); 378 379 enforce!(DatabaseException)( 380 !mysql_query(mysql, toCstring(sql)), 381 error() ~ " :::: " ~ sql); 382 383 return new MySqlResult(mysql_store_result(mysql), sql); 384 } 385 /+ 386 Result queryOld(T...)(string sql, T t) { 387 sql = escaped(sql, t); 388 389 if(sql.length == 0) 390 throw new DatabaseException("empty query"); 391 /* 392 static int queryCount = 0; 393 queryCount++; 394 if(sql.indexOf("INSERT") != -1) 395 stderr.writefln("%d: %s", queryCount, sql.replace("\n", " ").replace("\t", "")); 396 */ 397 398 version(dryRun) { 399 pragma(msg, "This is a dry run compile, no queries will be run"); 400 writeln(sql); 401 return Result(null, null); 402 } 403 404 enforceEx!(DatabaseException)( 405 !mysql_query(mysql, toCstring(sql)), 406 error() ~ " :::: " ~ sql); 407 408 return Result(mysql_store_result(mysql), sql); 409 } 410 +/ 411 /+ 412 struct ResultByAssoc { 413 this(Result* r) { 414 result = r; 415 fields = r.fieldNames(); 416 } 417 418 ulong length() { return result.length; } 419 bool empty() { return result.empty; } 420 void popFront() { result.popFront(); } 421 string[string] front() { 422 auto r = result.front; 423 string[string] ret; 424 foreach(i, a; r) { 425 ret[fields[i]] = a; 426 } 427 428 return ret; 429 } 430 431 @disable this(this) { } 432 433 string[] fields; 434 Result* result; 435 } 436 437 438 struct ResultByStruct(T) { 439 this(Result* r) { 440 result = r; 441 fields = r.fieldNames(); 442 } 443 444 ulong length() { return result.length; } 445 bool empty() { return result.empty; } 446 void popFront() { result.popFront(); } 447 T front() { 448 auto r = result.front; 449 string[string] ret; 450 foreach(i, a; r) { 451 ret[fields[i]] = a; 452 } 453 454 T s; 455 // FIXME: should use tupleOf 456 foreach(member; s.tupleof) { 457 if(member.stringof in ret) 458 member = to!(typeof(member))(ret[member]); 459 } 460 461 return s; 462 } 463 464 @disable this(this) { } 465 466 string[] fields; 467 Result* result; 468 } 469 +/ 470 471 /+ 472 473 474 struct Result { 475 private Result* heaped() { 476 auto r = new Result(result, sql, false); 477 478 r.tupleof = this.tupleof; 479 480 this.itemsTotal = 0; 481 this.result = null; 482 483 return r; 484 } 485 486 this(MYSQL_RES* r, string sql, bool prime = true) { 487 result = r; 488 itemsTotal = length; 489 itemsUsed = 0; 490 this.sql = sql; 491 // prime it here 492 if(prime && itemsTotal) 493 fetchNext(); 494 } 495 496 string sql; 497 498 ~this() { 499 if(result !is null) 500 mysql_free_result(result); 501 } 502 503 /+ 504 string[string][] fetchAssoc() { 505 506 } 507 +/ 508 509 ResultByAssoc byAssoc() { 510 return ResultByAssoc(&this); 511 } 512 513 ResultByStruct!(T) byStruct(T)() { 514 return ResultByStruct!(T)(&this); 515 } 516 517 string[] fieldNames() { 518 int numFields = mysql_num_fields(result); 519 auto fields = mysql_fetch_fields(result); 520 521 string[] names; 522 for(int i = 0; i < numFields; i++) { 523 names ~= fromCstring(fields[i].name); 524 } 525 526 return names; 527 } 528 529 MYSQL_FIELD[] fields() { 530 int numFields = mysql_num_fields(result); 531 auto fields = mysql_fetch_fields(result); 532 533 MYSQL_FIELD[] ret; 534 for(int i = 0; i < numFields; i++) { 535 ret ~= fields[i]; 536 } 537 538 return ret; 539 } 540 541 ulong length() { 542 if(result is null) 543 return 0; 544 return mysql_num_rows(result); 545 } 546 547 bool empty() { 548 return itemsUsed == itemsTotal; 549 } 550 551 Row front() { 552 return row; 553 } 554 555 void popFront() { 556 itemsUsed++; 557 if(itemsUsed < itemsTotal) { 558 fetchNext(); 559 } 560 } 561 562 void fetchNext() { 563 auto r = mysql_fetch_row(result); 564 uint numFields = mysql_num_fields(result); 565 uint* lengths = mysql_fetch_lengths(result); 566 row.length = 0; 567 // potential FIXME: not really binary safe 568 569 columnIsNull.length = numFields; 570 for(int a = 0; a < numFields; a++) { 571 if(*(r+a) is null) { 572 row ~= null; 573 columnIsNull[a] = true; 574 } else { 575 row ~= fromCstring(*(r+a), *(lengths + a)); 576 columnIsNull[a] = false; 577 } 578 } 579 } 580 581 @disable this(this) {} 582 private MYSQL_RES* result; 583 584 ulong itemsTotal; 585 ulong itemsUsed; 586 587 alias string[] Row; 588 589 Row row; 590 bool[] columnIsNull; // FIXME: should be part of the row 591 } 592 +/ 593 private: 594 MYSQL* mysql; 595 } 596 597 struct ResultByDataObject(ObjType) if (is(ObjType : DataObject)) { 598 this(MySqlResult r, MySql mysql) { 599 result = r; 600 auto fields = r.fields(); 601 this.mysql = mysql; 602 603 foreach(i, f; fields) { 604 string tbl = fromCstring(f.org_table is null ? f.table : f.org_table, f.org_table is null ? f.table_length : f.org_table_length); 605 mappings[fromCstring(f.name)] = tuple( 606 tbl, 607 fromCstring(f.org_name is null ? f.name : f.org_name, f.org_name is null ? f.name_length : f.org_name_length)); 608 } 609 610 611 } 612 613 Tuple!(string, string)[string] mappings; 614 615 ulong length() { return result.length; } 616 bool empty() { return result.empty; } 617 void popFront() { result.popFront(); } 618 ObjType front() { 619 return new ObjType(mysql, result.front.toAA, mappings); 620 } 621 // would it be good to add a new() method? would be valid even if empty 622 // it'd just fill in the ID's at random and allow you to do the rest 623 624 @disable this(this) { } 625 626 MySqlResult result; 627 MySql mysql; 628 } 629 630 extern(System) { 631 struct MYSQL; 632 struct MYSQL_RES; 633 /* typedef */ alias const(ubyte)* cstring; 634 635 struct MYSQL_FIELD { 636 cstring name; /* Name of column */ 637 cstring org_name; /* Original column name, if an alias */ 638 cstring table; /* Table of column if column was a field */ 639 cstring org_table; /* Org table name, if table was an alias */ 640 cstring db; /* Database for table */ 641 cstring catalog; /* Catalog for table */ 642 cstring def; /* Default value (set by mysql_list_fields) */ 643 c_ulong length; /* Width of column (create length) */ 644 c_ulong max_length; /* Max width for selected set */ 645 uint name_length; 646 uint org_name_length; 647 uint table_length; 648 uint org_table_length; 649 uint db_length; 650 uint catalog_length; 651 uint def_length; 652 uint flags; /* Div flags */ 653 uint decimals; /* Number of decimals in field */ 654 uint charsetnr; /* Character set */ 655 uint type; /* Type of field. See mysql_com.h for types */ 656 // type is actually an enum btw 657 658 version(MySQL_51) { 659 void* extension; 660 } 661 } 662 663 /* typedef */ alias cstring* MYSQL_ROW; 664 665 cstring mysql_get_client_info(); 666 MYSQL* mysql_init(MYSQL*); 667 uint mysql_errno(MYSQL*); 668 cstring mysql_error(MYSQL*); 669 670 MYSQL* mysql_real_connect(MYSQL*, cstring, cstring, cstring, cstring, uint, cstring, c_ulong); 671 672 int mysql_query(MYSQL*, cstring); 673 674 void mysql_close(MYSQL*); 675 676 ulong mysql_num_rows(MYSQL_RES*); 677 uint mysql_num_fields(MYSQL_RES*); 678 bool mysql_eof(MYSQL_RES*); 679 680 ulong mysql_affected_rows(MYSQL*); 681 ulong mysql_insert_id(MYSQL*); 682 683 MYSQL_RES* mysql_store_result(MYSQL*); 684 MYSQL_RES* mysql_use_result(MYSQL*); 685 686 MYSQL_ROW mysql_fetch_row(MYSQL_RES *); 687 c_ulong* mysql_fetch_lengths(MYSQL_RES*); 688 MYSQL_FIELD* mysql_fetch_field(MYSQL_RES*); 689 MYSQL_FIELD* mysql_fetch_fields(MYSQL_RES*); 690 691 uint mysql_real_escape_string(MYSQL*, ubyte* to, cstring from, c_ulong length); 692 693 void mysql_free_result(MYSQL_RES*); 694 695 } 696 697 import std.string; 698 cstring toCstring(string c) { 699 return cast(cstring) toStringz(c); 700 } 701 702 import std.array; 703 string fromCstring(cstring c, size_t len = size_t.max) { 704 string ret; 705 if(c is null) 706 return null; 707 if(len == 0) 708 return ""; 709 if(len == size_t.max) { 710 auto iterator = c; 711 len = 0; 712 while(*iterator) { 713 iterator++; 714 len++; 715 } 716 assert(len >= 0); 717 } 718 719 ret = cast(string) (c[0 .. len].idup); 720 721 return ret; 722 } 723 724 725 // FIXME: this should work generically with all database types and them moved to database.d 726 /// 727 Ret queryOneRow(Ret = Row, DB, string file = __FILE__, size_t line = __LINE__, T...)(DB db, string sql, T t) if( 728 (is(DB : Database)) 729 // && (is(Ret == Row) || is(Ret : DataObject))) 730 ) 731 { 732 static if(is(Ret : DataObject) && is(DB == MySql)) { 733 auto res = db.queryDataObject!Ret(sql, t); 734 if(res.empty) 735 throw new EmptyResultException("result was empty", file, line); 736 return res.front; 737 } else static if(is(Ret == Row)) { 738 auto res = db.query(sql, t); 739 if(res.empty) 740 throw new EmptyResultException("result was empty", file, line); 741 return res.front; 742 } else static assert(0, "Unsupported single row query return value, " ~ Ret.stringof); 743 } 744 745 /// 746 class EmptyResultException : Exception { 747 this(string message, string file = __FILE__, size_t line = __LINE__) { 748 super(message, file, line); 749 } 750 } 751 752 753 /* 754 void main() { 755 auto mysql = new MySql("localhost", "uname", "password", "test"); 756 scope(exit) delete mysql; 757 758 mysql.query("INSERT INTO users (id, password) VALUES (?, ?)", 10, "lol"); 759 760 foreach(row; mysql.query("SELECT * FROM users")) { 761 writefln("%s %s %s %s", row["id"], row[0], row[1], row["username"]); 762 } 763 } 764 */ 765 766 /* 767 struct ResultByStruct(T) { 768 this(MySql.Result* r) { 769 result = r; 770 fields = r.fieldNames(); 771 } 772 773 ulong length() { return result.length; } 774 bool empty() { return result.empty; } 775 void popFront() { result.popFront(); } 776 T front() { 777 auto r = result.front; 778 T ret; 779 foreach(i, a; r) { 780 ret[fields[i]] = a; 781 } 782 783 return ret; 784 } 785 786 @disable this(this) { } 787 788 string[] fields; 789 MySql.Result* result; 790 } 791 */ 792 793 794 /+ 795 mysql.linq.tablename.field[key] // select field from tablename where id = key 796 797 mysql.link["name"].table.field[key] // select field from table where name = key 798 799 800 auto q = mysql.prepQuery("select id from table where something"); 801 q.sort("name"); 802 q.limit(start, count); 803 q.page(3, pagelength = ?); 804 805 q.execute(params here); // returns the same Result range as query 806 +/ 807 808 /* 809 void main() { 810 auto db = new MySql("localhost", "uname", "password", "test"); 811 foreach(item; db.queryDataObject("SELECT users.*, username 812 FROM users, password_manager_accounts 813 WHERE password_manager_accounts.user_id = users.id LIMIT 5")) { 814 writefln("item: %s, %s", item.id, item.username); 815 item.first = "new"; 816 item.last = "new2"; 817 item.username = "kill"; 818 //item.commitChanges(); 819 } 820 } 821 */ 822 823 824 /* 825 Copyright: Adam D. Ruppe, 2009 - 2011 826 License: <a href="http://www.boost.org/LICENSE_1_0.txt">Boost License 1.0</a>. 827 Authors: Adam D. Ruppe, with contributions from Nick Sabalausky 828 829 Copyright Adam D. Ruppe 2009 - 2011. 830 Distributed under the Boost Software License, Version 1.0. 831 (See accompanying file LICENSE_1_0.txt or copy at 832 http://www.boost.org/LICENSE_1_0.txt) 833 */ 834