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 if(mysql) { 233 mysql_close(mysql); 234 mysql = null; 235 } 236 } 237 238 ~this() { 239 close(); 240 } 241 242 /// 243 int lastInsertId() { 244 return cast(int) mysql_insert_id(mysql); 245 } 246 247 248 249 /// Builds and executes an INERT INTO statement 250 int insert(string table, MySqlResult result, string[string] columnsToModify, string[] columnsToSkip) { 251 assert(!result.empty); 252 string sql = "INSERT INTO `" ~ table ~ "` "; 253 254 string cols = "("; 255 string vals = "("; 256 bool outputted = false; 257 258 string[string] columns; 259 auto cnames = result.fieldNames; 260 foreach(i, col; result.front.toStringArray) { 261 bool skipMe = false; 262 foreach(skip; columnsToSkip) { 263 if(cnames[i] == skip) { 264 skipMe = true; 265 break; 266 } 267 } 268 if(skipMe) 269 continue; 270 271 if(outputted) { 272 cols ~= ","; 273 vals ~= ","; 274 } else 275 outputted = true; 276 277 cols ~= cnames[i]; 278 279 if(result.columnIsNull[i] && cnames[i] !in columnsToModify) 280 vals ~= "NULL"; 281 else { 282 string v = col; 283 if(cnames[i] in columnsToModify) 284 v = columnsToModify[cnames[i]]; 285 286 vals ~= "'" ~ escape(v) ~ "'"; 287 288 } 289 } 290 291 cols ~= ")"; 292 vals ~= ")"; 293 294 sql ~= cols ~ " VALUES " ~ vals; 295 296 query(sql); 297 298 result.popFront; 299 300 return lastInsertId; 301 } 302 303 string escape(string str) { 304 ubyte[] buffer = new ubyte[str.length * 2 + 1]; 305 buffer.length = mysql_real_escape_string(mysql, buffer.ptr, cast(cstring) str.ptr, cast(uint) str.length); 306 307 return cast(string) buffer; 308 } 309 310 string escaped(T...)(string sql, T t) { 311 static if(t.length > 0) { 312 string fixedup; 313 int pos = 0; 314 315 316 void escAndAdd(string str, int q) { 317 ubyte[] buffer = new ubyte[str.length * 2 + 1]; 318 buffer.length = mysql_real_escape_string(mysql, buffer.ptr, cast(cstring) str.ptr, str.length); 319 320 fixedup ~= sql[pos..q] ~ '\'' ~ cast(string) buffer ~ '\''; 321 322 } 323 324 foreach(a; t) { 325 int q = sql[pos..$].indexOf("?"); 326 if(q == -1) 327 break; 328 q += pos; 329 330 static if(__traits(compiles, t is null)) { 331 if(t is null) 332 fixedup ~= sql[pos..q] ~ "NULL"; 333 else 334 escAndAdd(to!string(*a), q); 335 } else { 336 string str = to!string(a); 337 escAndAdd(str, q); 338 } 339 340 pos = q+1; 341 } 342 343 fixedup ~= sql[pos..$]; 344 345 sql = fixedup; 346 347 //writefln("\n\nExecuting sql: %s", sql); 348 } 349 350 return sql; 351 } 352 353 354 /// Gets a minimal ORM object from a query 355 ResultByDataObject!R queryDataObject(R = DataObject, T...)(string sql, T t) { 356 // modify sql for the best data object grabbing 357 sql = fixupSqlForDataObjectUse(sql); 358 359 auto magic = query(sql, t); 360 return ResultByDataObject!R(cast(MySqlResult) magic, this); 361 } 362 363 364 /// ditto 365 ResultByDataObject!R queryDataObjectWithCustomKeys(R = DataObject, T...)(string[string] keyMapping, string sql, T t) { 366 sql = fixupSqlForDataObjectUse(sql, keyMapping); 367 368 auto magic = query(sql, t); 369 return ResultByDataObject!R(cast(MySqlResult) magic, this); 370 } 371 372 373 374 /// 375 int affectedRows() { 376 return cast(int) mysql_affected_rows(mysql); 377 } 378 379 override ResultSet queryImpl(string sql, Variant[] args...) { 380 sql = escapedVariants(this, sql, args); 381 382 enforce!(DatabaseException)( 383 !mysql_query(mysql, toCstring(sql)), 384 error() ~ " :::: " ~ sql); 385 386 return new MySqlResult(mysql_store_result(mysql), sql); 387 } 388 /+ 389 Result queryOld(T...)(string sql, T t) { 390 sql = escaped(sql, t); 391 392 if(sql.length == 0) 393 throw new DatabaseException("empty query"); 394 /* 395 static int queryCount = 0; 396 queryCount++; 397 if(sql.indexOf("INSERT") != -1) 398 stderr.writefln("%d: %s", queryCount, sql.replace("\n", " ").replace("\t", "")); 399 */ 400 401 version(dryRun) { 402 pragma(msg, "This is a dry run compile, no queries will be run"); 403 writeln(sql); 404 return Result(null, null); 405 } 406 407 enforceEx!(DatabaseException)( 408 !mysql_query(mysql, toCstring(sql)), 409 error() ~ " :::: " ~ sql); 410 411 return Result(mysql_store_result(mysql), sql); 412 } 413 +/ 414 /+ 415 struct ResultByAssoc { 416 this(Result* r) { 417 result = r; 418 fields = r.fieldNames(); 419 } 420 421 ulong length() { return result.length; } 422 bool empty() { return result.empty; } 423 void popFront() { result.popFront(); } 424 string[string] front() { 425 auto r = result.front; 426 string[string] ret; 427 foreach(i, a; r) { 428 ret[fields[i]] = a; 429 } 430 431 return ret; 432 } 433 434 @disable this(this) { } 435 436 string[] fields; 437 Result* result; 438 } 439 440 441 struct ResultByStruct(T) { 442 this(Result* r) { 443 result = r; 444 fields = r.fieldNames(); 445 } 446 447 ulong length() { return result.length; } 448 bool empty() { return result.empty; } 449 void popFront() { result.popFront(); } 450 T front() { 451 auto r = result.front; 452 string[string] ret; 453 foreach(i, a; r) { 454 ret[fields[i]] = a; 455 } 456 457 T s; 458 // FIXME: should use tupleOf 459 foreach(member; s.tupleof) { 460 if(member.stringof in ret) 461 member = to!(typeof(member))(ret[member]); 462 } 463 464 return s; 465 } 466 467 @disable this(this) { } 468 469 string[] fields; 470 Result* result; 471 } 472 +/ 473 474 /+ 475 476 477 struct Result { 478 private Result* heaped() { 479 auto r = new Result(result, sql, false); 480 481 r.tupleof = this.tupleof; 482 483 this.itemsTotal = 0; 484 this.result = null; 485 486 return r; 487 } 488 489 this(MYSQL_RES* r, string sql, bool prime = true) { 490 result = r; 491 itemsTotal = length; 492 itemsUsed = 0; 493 this.sql = sql; 494 // prime it here 495 if(prime && itemsTotal) 496 fetchNext(); 497 } 498 499 string sql; 500 501 ~this() { 502 if(result !is null) 503 mysql_free_result(result); 504 } 505 506 /+ 507 string[string][] fetchAssoc() { 508 509 } 510 +/ 511 512 ResultByAssoc byAssoc() { 513 return ResultByAssoc(&this); 514 } 515 516 ResultByStruct!(T) byStruct(T)() { 517 return ResultByStruct!(T)(&this); 518 } 519 520 string[] fieldNames() { 521 int numFields = mysql_num_fields(result); 522 auto fields = mysql_fetch_fields(result); 523 524 string[] names; 525 for(int i = 0; i < numFields; i++) { 526 names ~= fromCstring(fields[i].name); 527 } 528 529 return names; 530 } 531 532 MYSQL_FIELD[] fields() { 533 int numFields = mysql_num_fields(result); 534 auto fields = mysql_fetch_fields(result); 535 536 MYSQL_FIELD[] ret; 537 for(int i = 0; i < numFields; i++) { 538 ret ~= fields[i]; 539 } 540 541 return ret; 542 } 543 544 ulong length() { 545 if(result is null) 546 return 0; 547 return mysql_num_rows(result); 548 } 549 550 bool empty() { 551 return itemsUsed == itemsTotal; 552 } 553 554 Row front() { 555 return row; 556 } 557 558 void popFront() { 559 itemsUsed++; 560 if(itemsUsed < itemsTotal) { 561 fetchNext(); 562 } 563 } 564 565 void fetchNext() { 566 auto r = mysql_fetch_row(result); 567 uint numFields = mysql_num_fields(result); 568 uint* lengths = mysql_fetch_lengths(result); 569 row.length = 0; 570 // potential FIXME: not really binary safe 571 572 columnIsNull.length = numFields; 573 for(int a = 0; a < numFields; a++) { 574 if(*(r+a) is null) { 575 row ~= null; 576 columnIsNull[a] = true; 577 } else { 578 row ~= fromCstring(*(r+a), *(lengths + a)); 579 columnIsNull[a] = false; 580 } 581 } 582 } 583 584 @disable this(this) {} 585 private MYSQL_RES* result; 586 587 ulong itemsTotal; 588 ulong itemsUsed; 589 590 alias string[] Row; 591 592 Row row; 593 bool[] columnIsNull; // FIXME: should be part of the row 594 } 595 +/ 596 MYSQL* getHandle() { 597 return mysql; 598 } 599 600 private: 601 MYSQL* mysql; 602 } 603 604 struct ResultByDataObject(ObjType) if (is(ObjType : DataObject)) { 605 this(MySqlResult r, MySql mysql) { 606 result = r; 607 auto fields = r.fields(); 608 this.mysql = mysql; 609 610 foreach(i, f; fields) { 611 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); 612 mappings[fromCstring(f.name)] = tuple( 613 tbl, 614 fromCstring(f.org_name is null ? f.name : f.org_name, f.org_name is null ? f.name_length : f.org_name_length)); 615 } 616 617 618 } 619 620 Tuple!(string, string)[string] mappings; 621 622 ulong length() { return result.length; } 623 bool empty() { return result.empty; } 624 void popFront() { result.popFront(); } 625 ObjType front() { 626 return new ObjType(mysql, result.front.toAA, mappings); 627 } 628 // would it be good to add a new() method? would be valid even if empty 629 // it'd just fill in the ID's at random and allow you to do the rest 630 631 @disable this(this) { } 632 633 MySqlResult result; 634 MySql mysql; 635 } 636 637 638 // thanks to 0xEAB on discord for sending me initial prepared statement support 639 640 struct Statement 641 { 642 ~this() 643 { 644 if (this.statement !is null) 645 { 646 this.statement.mysql_stmt_close(); 647 this.statement = null; 648 } 649 } 650 651 void reset() 652 { 653 mysql_stmt_reset(statement); 654 } 655 656 private: 657 MYSQL_STMT* statement; 658 MYSQL_BIND[] params; 659 } 660 661 Statement* prepare(MySql m, string query) @trusted 662 { 663 MYSQL_STMT* s = m.getHandle.mysql_stmt_init(); 664 immutable x = s.mysql_stmt_prepare(query.toStringz, cast(int) query.length); 665 666 if (x != 0) 667 { 668 throw new Exception(m.getHandle.mysql_error.fromCstring); 669 } 670 671 return new Statement(s); 672 } 673 674 import std.traits : isNumeric; 675 676 void bindParameter(T)(Statement* s, ref T value) if (isNumeric!T) 677 { 678 import std.traits : isUnsigned; 679 680 MYSQL_BIND p = MYSQL_BIND(); 681 682 p.buffer = &value; 683 p.buffer_type = mySqlType!T; 684 p.is_unsigned = isUnsigned!T; 685 686 s.params ~= p; 687 immutable x = s.statement.mysql_stmt_bind_param(&(s.params[$ - 1])); 688 689 if (x != 0) 690 { 691 throw new Exception(s.statement.mysql_stmt_error.fromStringz.to!string); 692 } 693 } 694 695 void bindParameterNull(Statement* s) 696 { 697 MYSQL_BIND p = MYSQL_BIND(); 698 699 p.buffer_type = enum_field_types.MYSQL_TYPE_NULL; 700 701 s.params ~= p; 702 immutable x = s.statement.mysql_stmt_bind_param(null); 703 704 if (x != 0) 705 { 706 throw new Exception(s.statement.mysql_stmt_error.fromStringz.to!string); 707 } 708 } 709 710 void bindParameter(T)(Statement* s, T value) if (is(T == string)) 711 { 712 import std.traits : isUnsigned; 713 714 MYSQL_BIND p = MYSQL_BIND(); 715 716 p.buffer = cast(void*) value.toCstring(); 717 p.buffer_type = mySqlType!string; 718 p.buffer_length = value.length; 719 720 s.params ~= p; 721 immutable x = s.statement.mysql_stmt_bind_param(&s.params[$ - 1]); 722 723 if (x != 0) 724 { 725 throw new Exception(s.statement.mysql_stmt_error.fromStringz.to!string); 726 } 727 } 728 729 void execute(Statement* s) @trusted 730 { 731 immutable x = s.statement.mysql_stmt_execute(); 732 733 if (x != 0) 734 { 735 throw new Exception(s.statement.mysql_stmt_error.fromStringz.to!string); 736 } 737 } 738 739 740 extern(System) { 741 /* 742 from <my_alloc.h> 743 original header actually contains members, 744 but guess we don't need them here 745 */ 746 struct USED_MEM; 747 748 /* 749 from <my_alloc.h> 750 */ 751 struct MEM_ROOT 752 { 753 USED_MEM* free; /* blocks with free memory in it */ 754 USED_MEM* used; /* blocks almost without free memory */ 755 USED_MEM* pre_alloc; /* preallocated block */ 756 /* if block have less memory it will be put in 'used' list */ 757 size_t min_malloc; 758 size_t block_size; /* initial block size */ 759 uint block_num; /* allocated blocks counter */ 760 /* 761 first free block in queue test counter (if it exceed 762 MAX_BLOCK_USAGE_BEFORE_DROP block will be dropped in 'used' list) 763 */ 764 uint first_block_usage; 765 766 void function () error_handler; 767 } 768 769 /* 770 from <mysql_com.h> 771 772 original header actually contains members, 773 but guess we don't need them here 774 */ 775 struct NET; 776 777 /* from <mysql_com.h> */ 778 enum MYSQL_ERRMSG_SIZE = 512; 779 780 /* from <mysql_com.h> */ 781 enum enum_field_types { 782 MYSQL_TYPE_DECIMAL, MYSQL_TYPE_TINY, 783 MYSQL_TYPE_SHORT, MYSQL_TYPE_LONG, 784 MYSQL_TYPE_FLOAT, MYSQL_TYPE_DOUBLE, 785 MYSQL_TYPE_NULL, MYSQL_TYPE_TIMESTAMP, 786 MYSQL_TYPE_LONGLONG,MYSQL_TYPE_INT24, 787 MYSQL_TYPE_DATE, MYSQL_TYPE_TIME, 788 MYSQL_TYPE_DATETIME, MYSQL_TYPE_YEAR, 789 MYSQL_TYPE_NEWDATE, MYSQL_TYPE_VARCHAR, 790 MYSQL_TYPE_BIT, 791 792 /* 793 mysql-5.6 compatibility temporal types. 794 They're only used internally for reading RBR 795 mysql-5.6 binary log events and mysql-5.6 frm files. 796 They're never sent to the client. 797 */ 798 MYSQL_TYPE_TIMESTAMP2, 799 MYSQL_TYPE_DATETIME2, 800 MYSQL_TYPE_TIME2, 801 802 MYSQL_TYPE_NEWDECIMAL=246, 803 804 MYSQL_TYPE_ENUM=247, 805 MYSQL_TYPE_SET=248, 806 MYSQL_TYPE_TINY_BLOB=249, 807 MYSQL_TYPE_MEDIUM_BLOB=250, 808 MYSQL_TYPE_LONG_BLOB=251, 809 MYSQL_TYPE_BLOB=252, 810 MYSQL_TYPE_VAR_STRING=253, 811 MYSQL_TYPE_STRING=254, 812 MYSQL_TYPE_GEOMETRY=255 813 } 814 815 /* from <my_list.h>*/ 816 struct LIST 817 { 818 LIST* prev; 819 LIST* next; 820 void* data; 821 } 822 823 struct MYSQL; 824 struct MYSQL_RES; 825 /* typedef */ alias const(ubyte)* cstring; 826 827 alias my_bool = char; 828 alias my_ulonglong = ulong; 829 830 struct MYSQL_FIELD { 831 cstring name; /* Name of column */ 832 cstring org_name; /* Original column name, if an alias */ 833 cstring table; /* Table of column if column was a field */ 834 cstring org_table; /* Org table name, if table was an alias */ 835 cstring db; /* Database for table */ 836 cstring catalog; /* Catalog for table */ 837 cstring def; /* Default value (set by mysql_list_fields) */ 838 c_ulong length; /* Width of column (create length) */ 839 c_ulong max_length; /* Max width for selected set */ 840 uint name_length; 841 uint org_name_length; 842 uint table_length; 843 uint org_table_length; 844 uint db_length; 845 uint catalog_length; 846 uint def_length; 847 uint flags; /* Div flags */ 848 uint decimals; /* Number of decimals in field */ 849 uint charsetnr; /* Character set */ 850 uint type; /* Type of field. See mysql_com.h for types */ 851 // type is actually an enum btw 852 853 version(MySQL_51) { 854 void* extension; 855 } 856 } 857 858 struct MYSQL_ROWS 859 { 860 MYSQL_ROWS* next; /* list of rows */ 861 MYSQL_ROW data; 862 c_ulong length; 863 } 864 865 alias MYSQL_ROW_OFFSET = MYSQL_ROWS*; /* offset to current row */ 866 867 struct EMBEDDED_QUERY_RESULT; 868 869 struct MYSQL_DATA 870 { 871 MYSQL_ROWS* data; 872 EMBEDDED_QUERY_RESULT* embedded_info; 873 MEM_ROOT alloc; 874 my_ulonglong rows; 875 uint fields; 876 877 version(MySQL_51) { 878 /* extra info for embedded library */ 879 void* extension; 880 } 881 } 882 883 /* statement state */ 884 enum enum_mysql_stmt_state 885 { 886 MYSQL_STMT_INIT_DONE = 1, 887 MYSQL_STMT_PREPARE_DONE = 2, 888 MYSQL_STMT_EXECUTE_DONE = 3, 889 MYSQL_STMT_FETCH_DONE = 4 890 } 891 892 enum enum_stmt_attr_type 893 { 894 /** 895 When doing mysql_stmt_store_result calculate max_length attribute 896 of statement metadata. This is to be consistent with the old API, 897 where this was done automatically. 898 In the new API we do that only by request because it slows down 899 mysql_stmt_store_result sufficiently. 900 */ 901 STMT_ATTR_UPDATE_MAX_LENGTH = 0, 902 /** 903 unsigned long with combination of cursor flags (read only, for update, etc) 904 */ 905 STMT_ATTR_CURSOR_TYPE = 1, 906 /** 907 Amount of rows to retrieve from server per one fetch if using cursors. 908 Accepts unsigned long attribute in the range 1 - ulong_max 909 */ 910 STMT_ATTR_PREFETCH_ROWS = 2 911 } 912 913 struct MYSQL_BIND 914 { 915 c_ulong* length; /* output length pointer */ 916 my_bool* is_null; /* Pointer to null indicator */ 917 void* buffer; /* buffer to get/put data */ 918 /* set this if you want to track data truncations happened during fetch */ 919 my_bool* error; 920 ubyte* row_ptr; /* for the current data position */ 921 void function (NET* net, MYSQL_BIND* param) store_param_func; 922 void function (MYSQL_BIND*, MYSQL_FIELD*, ubyte** row) fetch_result; 923 void function (MYSQL_BIND*, MYSQL_FIELD*, ubyte** row) skip_result; 924 /* output buffer length, must be set when fetching str/binary */ 925 c_ulong buffer_length; 926 c_ulong offset; /* offset position for char/binary fetch */ 927 c_ulong length_value; /* Used if length is 0 */ 928 uint param_number; /* For null count and error messages */ 929 uint pack_length; /* Internal length for packed data */ 930 enum_field_types buffer_type; /* buffer type */ 931 my_bool error_value; /* used if error is 0 */ 932 my_bool is_unsigned; /* set if integer type is unsigned */ 933 my_bool long_data_used; /* If used with mysql_send_long_data */ 934 my_bool is_null_value; /* Used if is_null is 0 */ 935 void* extension; 936 } 937 938 struct st_mysql_stmt_extension; 939 940 /* statement handler */ 941 struct MYSQL_STMT 942 { 943 MEM_ROOT mem_root; /* root allocations */ 944 LIST list; /* list to keep track of all stmts */ 945 MYSQL* mysql; /* connection handle */ 946 MYSQL_BIND* params; /* input parameters */ 947 MYSQL_BIND* bind; /* output parameters */ 948 MYSQL_FIELD* fields; /* result set metadata */ 949 MYSQL_DATA result; /* cached result set */ 950 MYSQL_ROWS* data_cursor; /* current row in cached result */ 951 /* 952 mysql_stmt_fetch() calls this function to fetch one row (it's different 953 for buffered, unbuffered and cursor fetch). 954 */ 955 int function (MYSQL_STMT* stmt, ubyte** row) read_row_func; 956 /* copy of mysql->affected_rows after statement execution */ 957 my_ulonglong affected_rows; 958 my_ulonglong insert_id; /* copy of mysql->insert_id */ 959 c_ulong stmt_id; /* Id for prepared statement */ 960 c_ulong flags; /* i.e. type of cursor to open */ 961 c_ulong prefetch_rows; /* number of rows per one COM_FETCH */ 962 /* 963 Copied from mysql->server_status after execute/fetch to know 964 server-side cursor status for this statement. 965 */ 966 uint server_status; 967 uint last_errno; /* error code */ 968 uint param_count; /* input parameter count */ 969 uint field_count; /* number of columns in result set */ 970 enum_mysql_stmt_state state; /* statement state */ 971 char[MYSQL_ERRMSG_SIZE] last_error; /* error message */ 972 char[6] sqlstate; 973 /* Types of input parameters should be sent to server */ 974 my_bool send_types_to_server; 975 my_bool bind_param_done; /* input buffers were supplied */ 976 ubyte bind_result_done; /* output buffers were supplied */ 977 /* mysql_stmt_close() had to cancel this result */ 978 my_bool unbuffered_fetch_cancelled; 979 /* 980 Is set to true if we need to calculate field->max_length for 981 metadata fields when doing mysql_stmt_store_result. 982 */ 983 my_bool update_max_length; 984 st_mysql_stmt_extension* extension; 985 } 986 987 /* typedef */ alias cstring* MYSQL_ROW; 988 989 cstring mysql_get_client_info(); 990 MYSQL* mysql_init(MYSQL*); 991 uint mysql_errno(MYSQL*); 992 cstring mysql_error(MYSQL*); 993 994 MYSQL* mysql_real_connect(MYSQL*, cstring, cstring, cstring, cstring, uint, cstring, c_ulong); 995 996 int mysql_query(MYSQL*, cstring); 997 998 void mysql_close(MYSQL*); 999 1000 ulong mysql_num_rows(MYSQL_RES*); 1001 uint mysql_num_fields(MYSQL_RES*); 1002 bool mysql_eof(MYSQL_RES*); 1003 1004 ulong mysql_affected_rows(MYSQL*); 1005 ulong mysql_insert_id(MYSQL*); 1006 1007 MYSQL_RES* mysql_store_result(MYSQL*); 1008 MYSQL_RES* mysql_use_result(MYSQL*); 1009 1010 MYSQL_ROW mysql_fetch_row(MYSQL_RES *); 1011 c_ulong* mysql_fetch_lengths(MYSQL_RES*); 1012 MYSQL_FIELD* mysql_fetch_field(MYSQL_RES*); 1013 MYSQL_FIELD* mysql_fetch_fields(MYSQL_RES*); 1014 1015 uint mysql_real_escape_string(MYSQL*, ubyte* to, cstring from, c_ulong length); 1016 1017 void mysql_free_result(MYSQL_RES*); 1018 1019 MYSQL_STMT* mysql_stmt_init (MYSQL* mysql); 1020 int mysql_stmt_prepare (MYSQL_STMT* stmt, const(char)* query, c_ulong length); 1021 int mysql_stmt_execute (MYSQL_STMT* stmt); 1022 my_bool mysql_stmt_bind_param (MYSQL_STMT* stmt, MYSQL_BIND* bnd); 1023 my_bool mysql_stmt_close (MYSQL_STMT* stmt); 1024 my_bool mysql_stmt_free_result (MYSQL_STMT* stmt); 1025 my_bool mysql_stmt_reset (MYSQL_STMT* stmt); 1026 uint mysql_stmt_errno (MYSQL_STMT* stmt); 1027 const(char)* mysql_stmt_error (MYSQL_STMT* stmt); 1028 const(char)* mysql_stmt_sqlstate (MYSQL_STMT* stmt); 1029 my_ulonglong mysql_stmt_num_rows (MYSQL_STMT* stmt); 1030 my_ulonglong mysql_stmt_affected_rows (MYSQL_STMT* stmt); 1031 my_ulonglong mysql_stmt_insert_id (MYSQL_STMT* stmt); 1032 1033 } 1034 1035 import std.string; 1036 cstring toCstring(string c) { 1037 return cast(cstring) toStringz(c); 1038 } 1039 1040 import std.array; 1041 string fromCstring(cstring c, size_t len = size_t.max) { 1042 string ret; 1043 if(c is null) 1044 return null; 1045 if(len == 0) 1046 return ""; 1047 if(len == size_t.max) { 1048 auto iterator = c; 1049 len = 0; 1050 while(*iterator) { 1051 iterator++; 1052 len++; 1053 } 1054 assert(len >= 0); 1055 } 1056 1057 ret = cast(string) (c[0 .. len].idup); 1058 1059 return ret; 1060 } 1061 1062 enum_field_types getMySqlType(T)() { 1063 static if (is(T == bool)) 1064 return enum_field_types.MYSQL_TYPE_TINY; 1065 1066 static if (is(T == char)) 1067 return enum_field_types.MYSQL_TYPE_TINY; 1068 1069 static if (is(T == byte) || is(T == ubyte)) 1070 return enum_field_types.MYSQL_TYPE_TINY; 1071 1072 else static if (is(T == short) || is(T == ushort)) 1073 return enum_field_types.MYSQL_TYPE_SHORT; 1074 1075 else static if (is(T == int) || is(T == uint)) 1076 return enum_field_types.MYSQL_TYPE_LONG; 1077 1078 else static if (is(T == long) || is(T == ulong)) 1079 return enum_field_types.MYSQL_TYPE_LONGLONG; 1080 1081 else static if (is(T == string)) 1082 return enum_field_types.MYSQL_TYPE_STRING; 1083 1084 else static if (is(T == float)) 1085 return enum_field_types.MYSQL_TYPE_FLOAT; 1086 1087 else static if (is(T == double)) 1088 return enum_field_types.MYSQL_TYPE_DOUBLE; 1089 1090 //else static if (is(T == byte[])) 1091 // return enum_field_types.MYSQL_TYPE_BLOB; 1092 1093 else 1094 static assert("No MySQL equivalent known for " ~ T); 1095 } 1096 1097 enum enum_field_types mySqlType(T) = getMySqlType!T; 1098 1099 // FIXME: this should work generically with all database types and them moved to database.d 1100 /// 1101 Ret queryOneRow(Ret = Row, DB, string file = __FILE__, size_t line = __LINE__, T...)(DB db, string sql, T t) if( 1102 (is(DB : Database)) 1103 // && (is(Ret == Row) || is(Ret : DataObject))) 1104 ) 1105 { 1106 static if(is(Ret : DataObject) && is(DB == MySql)) { 1107 auto res = db.queryDataObject!Ret(sql, t); 1108 if(res.empty) 1109 throw new EmptyResultException("result was empty", file, line); 1110 return res.front; 1111 } else static if(is(Ret == Row)) { 1112 auto res = db.query(sql, t); 1113 if(res.empty) 1114 throw new EmptyResultException("result was empty", file, line); 1115 return res.front; 1116 } else static assert(0, "Unsupported single row query return value, " ~ Ret.stringof); 1117 } 1118 1119 /// 1120 class EmptyResultException : Exception { 1121 this(string message, string file = __FILE__, size_t line = __LINE__) { 1122 super(message, file, line); 1123 } 1124 } 1125 1126 1127 /* 1128 void main() { 1129 auto mysql = new MySql("localhost", "uname", "password", "test"); 1130 scope(exit) delete mysql; 1131 1132 mysql.query("INSERT INTO users (id, password) VALUES (?, ?)", 10, "lol"); 1133 1134 foreach(row; mysql.query("SELECT * FROM users")) { 1135 writefln("%s %s %s %s", row["id"], row[0], row[1], row["username"]); 1136 } 1137 } 1138 */ 1139 1140 /* 1141 struct ResultByStruct(T) { 1142 this(MySql.Result* r) { 1143 result = r; 1144 fields = r.fieldNames(); 1145 } 1146 1147 ulong length() { return result.length; } 1148 bool empty() { return result.empty; } 1149 void popFront() { result.popFront(); } 1150 T front() { 1151 auto r = result.front; 1152 T ret; 1153 foreach(i, a; r) { 1154 ret[fields[i]] = a; 1155 } 1156 1157 return ret; 1158 } 1159 1160 @disable this(this) { } 1161 1162 string[] fields; 1163 MySql.Result* result; 1164 } 1165 */ 1166 1167 1168 /+ 1169 mysql.linq.tablename.field[key] // select field from tablename where id = key 1170 1171 mysql.link["name"].table.field[key] // select field from table where name = key 1172 1173 1174 auto q = mysql.prepQuery("select id from table where something"); 1175 q.sort("name"); 1176 q.limit(start, count); 1177 q.page(3, pagelength = ?); 1178 1179 q.execute(params here); // returns the same Result range as query 1180 +/ 1181 1182 /* 1183 void main() { 1184 auto db = new MySql("localhost", "uname", "password", "test"); 1185 foreach(item; db.queryDataObject("SELECT users.*, username 1186 FROM users, password_manager_accounts 1187 WHERE password_manager_accounts.user_id = users.id LIMIT 5")) { 1188 writefln("item: %s, %s", item.id, item.username); 1189 item.first = "new"; 1190 item.last = "new2"; 1191 item.username = "kill"; 1192 //item.commitChanges(); 1193 } 1194 } 1195 */ 1196 1197 1198 /* 1199 Copyright: Adam D. Ruppe, 2009 - 2011 1200 License: <a href="http://www.boost.org/LICENSE_1_0.txt">Boost License 1.0</a>. 1201 Authors: Adam D. Ruppe, with contributions from Nick Sabalausky 1202 1203 Copyright Adam D. Ruppe 2009 - 2011. 1204 Distributed under the Boost Software License, Version 1.0. 1205 (See accompanying file LICENSE_1_0.txt or copy at 1206 http://www.boost.org/LICENSE_1_0.txt) 1207 */ 1208