1 module dpq2.result; 2 3 public import dpq2.conv.to_d_types; 4 public import dpq2.conv.to_bson; 5 public import dpq2.oids; 6 public import dpq2.value; 7 8 import dpq2; 9 import dpq2.exception; 10 11 import core.vararg; 12 import std..string: toStringz, fromStringz; 13 import std.exception: enforceEx; 14 import core.exception: OutOfMemoryError; 15 import std.bitmanip: bigEndianToNative; 16 import std.conv: to; 17 18 /// Result table's cell coordinates 19 private struct Coords 20 { 21 size_t row; /// Row 22 size_t col; /// Column 23 } 24 25 package immutable final class ResultContainer 26 { 27 // ResultContainer allows only one copy of PGresult* due to avoid double free. 28 // For the same reason this class is declared as final. 29 private PGresult* result; 30 alias result this; 31 32 nothrow invariant() 33 { 34 assert( result != null ); 35 } 36 37 package this(immutable PGresult* r) 38 { 39 assert(r); 40 41 result = r; 42 } 43 44 ~this() 45 { 46 assert(result != null); 47 48 PQclear(result); 49 } 50 } 51 52 /// Contains result of query regardless of whether it contains an error or data answer 53 immutable class Result 54 { 55 private ResultContainer result; 56 57 package this(immutable ResultContainer r) 58 { 59 result = r; 60 } 61 62 ExecStatusType status() nothrow 63 { 64 return PQresultStatus(result); 65 } 66 67 string statusString() 68 { 69 return to!string(fromStringz(PQresStatus(status))); 70 } 71 72 string resultErrorMessage() 73 { 74 return to!string( PQresultErrorMessage(result) ); 75 } 76 77 string resultErrorField(int fieldcode) 78 { 79 return PQresultErrorField(result, fieldcode).to!string; 80 } 81 82 immutable(Answer) getAnswer() 83 { 84 return new immutable Answer(result); 85 } 86 87 debug string toString() 88 { 89 import std.ascii: newline; 90 91 string err = resultErrorMessage(); 92 93 return statusString()~(err.length != 0 ? newline~err : ""); 94 } 95 } 96 97 /// Contains result of query with valid data answer 98 immutable class Answer : Result 99 { 100 package this(immutable ResultContainer r) 101 { 102 super(r); 103 104 checkAnswerForErrors(); 105 } 106 107 private void checkAnswerForErrors() 108 { 109 switch(status) 110 { 111 case PGRES_COMMAND_OK: 112 case PGRES_TUPLES_OK: 113 break; 114 115 default: 116 throw new AnswerCreationException(this, __FILE__, __LINE__); 117 } 118 } 119 120 /// Returns the command status tag from the SQL command that generated the PGresult 121 /** 122 * Commonly this is just the name of the command, but it might include 123 * additional data such as the number of rows processed. The caller should 124 * not free the result directly. It will be freed when the associated 125 * PGresult handle is passed to PQclear. 126 */ 127 string cmdStatus() 128 { 129 return to!string( PQcmdStatus(result) ); 130 } 131 132 /// Returns row count 133 size_t length() nothrow { return PQntuples(result); } 134 135 /// Returns column count 136 size_t columnCount() nothrow { return PQnfields(result); } 137 138 /// Returns column format 139 ValueFormat columnFormat( const size_t colNum ) 140 { 141 assertCol( colNum ); 142 return cast(ValueFormat) PQfformat(result, to!int(colNum)); 143 } 144 145 /// Returns column Oid 146 OidType OID( size_t colNum ) 147 { 148 assertCol( colNum ); 149 150 return PQftype(result, to!int(colNum)).oid2oidType; 151 } 152 153 bool isSupportedArray( const size_t colNum ) 154 { 155 assertCol(colNum); 156 157 return dpq2.oids.isSupportedArray(OID(colNum)); 158 } 159 160 /// Returns column number by field name 161 size_t columnNum( string columnName ) 162 { 163 size_t n = PQfnumber(result, toStringz(columnName)); 164 165 if( n == -1 ) 166 throw new AnswerException(ExceptionType.COLUMN_NOT_FOUND, 167 "Column '"~columnName~"' is not found", __FILE__, __LINE__); 168 169 return n; 170 } 171 172 /// Returns column name by field number 173 string columnName( in size_t colNum ) 174 { 175 const char* s = PQfname(result, colNum.to!int); 176 177 if( s == null ) 178 throw new AnswerException( 179 ExceptionType.OUT_OF_RANGE, 180 "Column "~to!string(colNum)~" is out of range 0.."~to!string(columnCount), 181 __FILE__, __LINE__ 182 ); 183 184 return to!string(fromStringz(s)); 185 } 186 187 /// Returns true if the column exists, false if not 188 bool columnExists( string columnName ) 189 { 190 size_t n = PQfnumber(result, columnName.toStringz); 191 192 return n != -1; 193 } 194 195 /// Returns row of cells 196 immutable (Row) opIndex(in size_t row) 197 { 198 return immutable Row( 199 cast(immutable)(this), // legal because this.ctor is immutable 200 row 201 ); 202 } 203 204 /** 205 Returns the number of parameters of a prepared statement. 206 This function is only useful when inspecting the result of describePrepared. 207 For other types of queries it will return zero. 208 */ 209 uint nParams() 210 { 211 return PQnparams(result); 212 } 213 214 /** 215 Returns the data type of the indicated statement parameter. 216 Parameter numbers start at 0. 217 This function is only useful when inspecting the result of describePrepared. 218 For other types of queries it will return zero. 219 */ 220 OidType paramType(T)(T paramNum) 221 { 222 return PQparamtype(result, paramNum.to!uint).oid2oidType; 223 } 224 225 debug override string toString() 226 { 227 import std.ascii: newline; 228 229 string res; 230 231 foreach(n; 0 .. columnCount) 232 res ~= columnName(n)~"::"~OID(n).to!string~"\t"; 233 234 res ~= newline; 235 236 foreach(row; rangify(this)) 237 res ~= row.toString~newline; 238 239 return super.toString~newline~res; 240 } 241 242 private void assertCol( const size_t c ) 243 { 244 if(!(c < columnCount)) 245 throw new AnswerException( 246 ExceptionType.OUT_OF_RANGE, 247 "Column "~to!string(c)~" is out of range 0.."~to!string(columnCount)~" of result columns", 248 __FILE__, __LINE__ 249 ); 250 } 251 252 private void assertRow( const size_t r ) 253 { 254 if(!(r < length)) 255 throw new AnswerException( 256 ExceptionType.OUT_OF_RANGE, 257 "Row "~to!string(r)~" is out of range 0.."~to!string(length)~" of result rows", 258 __FILE__, __LINE__ 259 ); 260 } 261 262 private void assertCoords( const Coords c ) 263 { 264 assertRow( c.row ); 265 assertCol( c.col ); 266 } 267 } 268 269 auto rangify(T)(T obj) 270 { 271 struct Rangify(T) 272 { 273 T obj; 274 alias obj this; 275 276 private int curr; 277 278 this(T o) 279 { 280 obj = o; 281 } 282 283 auto front(){ return obj[curr]; } 284 void popFront(){ ++curr; } 285 bool empty(){ return curr >= obj.length; } 286 } 287 288 return Rangify!(T)(obj); 289 } 290 291 /// Represents one row from the answer table 292 immutable struct Row 293 { 294 private Answer answer; 295 private size_t row; 296 297 this(immutable Answer answer, in size_t row) 298 { 299 answer.assertRow( row ); 300 301 this.answer = answer; 302 this.row = row; 303 } 304 305 /// Returns cell size 306 size_t size( const size_t col ) 307 { 308 answer.assertCol(col); 309 return PQgetlength(answer.result, to!int(row), to!int(col)); 310 } 311 312 /// Value NULL checking 313 /// Do not confuse it with Nullable's isNull method 314 bool isNULL( const size_t col ) 315 { 316 answer.assertCol(col); 317 318 return PQgetisnull(answer.result, to!int(row), to!int(col)) != 0; 319 } 320 321 immutable (Value) opIndex(in size_t col) 322 { 323 answer.assertCoords( Coords( row, col ) ); 324 325 // The pointer returned by PQgetvalue points to storage that is part of the PGresult structure. 326 // One should not modify the data it points to, and one must explicitly copy the data into other 327 // storage if it is to be used past the lifetime of the PGresult structure itself. 328 const ubyte* v = cast(immutable) PQgetvalue(answer.result, to!int(row), to!int(col)); 329 size_t s = size(col); 330 331 // it is legal to cast here because immutable value will be returned 332 Value r = Value(cast(ubyte[]) v[0..s], answer.OID(col), isNULL(col), answer.columnFormat(col)); 333 334 return cast(immutable) r; 335 } 336 337 immutable (Value) opIndex(in string column) 338 { 339 return opIndex(columnNum(column)); 340 } 341 342 /// Returns column number by field name 343 size_t columnNum( string columnName ) 344 { 345 return answer.columnNum( columnName ); 346 } 347 348 /// Returns column name by field number 349 string columnName( in size_t colNum ) 350 { 351 return answer.columnName( colNum ); 352 } 353 354 /// Returns column count 355 size_t length() { return answer.columnCount(); } 356 357 debug string toString() 358 { 359 string res; 360 361 foreach(val; rangify(this)) 362 res ~= dpq2.result.toString(val)~"\t"; 363 364 return res; 365 } 366 } 367 368 immutable (Array) asArray(immutable(Value) v) 369 { 370 if(v.format == ValueFormat.TEXT) 371 throw new AnswerConvException(ConvExceptionType.NOT_ARRAY, 372 "Value internal format is text", 373 __FILE__, __LINE__ 374 ); 375 376 if(!v.isSupportedArray) 377 throw new AnswerConvException(ConvExceptionType.NOT_ARRAY, 378 "Format of the value is "~to!string(v.oidType)~", isn't supported array", 379 __FILE__, __LINE__ 380 ); 381 382 return immutable Array(v); 383 } 384 385 debug string toString(immutable Value v) 386 { 387 import vibe.data.bson: Bson; 388 389 return v.isNull ? "NULL" : v.as!Bson.toString; 390 } 391 392 package struct ArrayHeader_net // network byte order 393 { 394 ubyte[4] ndims; // number of dimensions of the array 395 ubyte[4] dataoffset_ign; // offset for data, removed by libpq. may be it contains isNULL flag! 396 ubyte[4] OID; // element type OID 397 } 398 399 package struct Dim_net // network byte order 400 { 401 ubyte[4] dim_size; // number of elements in dimension 402 ubyte[4] lbound; // unknown 403 } 404 405 struct ArrayProperties 406 { 407 OidType OID = OidType.Undefined; 408 int[] dimsSize; /// Dimensions sizes info 409 size_t nElems; /// Total elements 410 package size_t dataOffset; 411 412 this(in Value cell) 413 { 414 const ArrayHeader_net* h = cast(ArrayHeader_net*) cell.data.ptr; 415 int nDims = bigEndianToNative!int(h.ndims); 416 OID = oid2oidType(bigEndianToNative!Oid(h.OID)); 417 418 if(nDims < 0) 419 throw new AnswerException(ExceptionType.FATAL_ERROR, 420 "Array dimensions number is negative ("~to!string(nDims)~")", 421 __FILE__, __LINE__ 422 ); 423 424 dataOffset = ArrayHeader_net.sizeof + Dim_net.sizeof * nDims; 425 426 dimsSize = new int[nDims]; 427 428 // Recognize dimensions of array 429 for( auto i = 0; i < nDims; ++i ) 430 { 431 Dim_net* d = (cast(Dim_net*) (h + 1)) + i; 432 433 const dim_size = bigEndianToNative!int(d.dim_size); 434 const lbound = bigEndianToNative!int(d.lbound); 435 436 if(dim_size < 0) 437 throw new AnswerException(ExceptionType.FATAL_ERROR, 438 "Dimension size is negative ("~to!string(dim_size)~")", 439 __FILE__, __LINE__ 440 ); 441 442 // FIXME: What is lbound in postgresql array reply? 443 if(!(lbound == 1)) 444 throw new AnswerException(ExceptionType.FATAL_ERROR, 445 "Please report if you came across this error! lbound=="~to!string(lbound), 446 __FILE__, __LINE__ 447 ); 448 449 dimsSize[i] = dim_size; 450 451 if(i == 0) // first dimension 452 nElems = dim_size; 453 else 454 nElems *= dim_size; 455 } 456 } 457 } 458 459 /// Link to the cell of the answer table 460 immutable struct Array 461 { 462 ArrayProperties ap; 463 alias ap this; 464 465 private ubyte[][] elements; 466 private bool[] elementIsNULL; 467 468 this(immutable Value cell) 469 { 470 if(!(cell.format == ValueFormat.BINARY)) 471 throw new AnswerConvException(ConvExceptionType.NOT_BINARY, 472 msg_NOT_BINARY, __FILE__, __LINE__); 473 474 ap = cast(immutable) ArrayProperties(cell); 475 476 // Looping through all elements and fill out index of them 477 { 478 auto elements = new immutable (ubyte)[][ nElems ]; 479 auto elementIsNULL = new bool[ nElems ]; 480 481 size_t curr_offset = ap.dataOffset; 482 483 for(uint i = 0; i < nElems; ++i) 484 { 485 ubyte[int.sizeof] size_net; // network byte order 486 size_net[] = cell.data[ curr_offset .. curr_offset + size_net.sizeof ]; 487 uint size = bigEndianToNative!uint( size_net ); 488 if( size == size.max ) // NULL magic number 489 { 490 elementIsNULL[i] = true; 491 size = 0; 492 } 493 else 494 { 495 elementIsNULL[i] = false; 496 } 497 curr_offset += size_net.sizeof; 498 elements[i] = cell.data[curr_offset .. curr_offset + size]; 499 curr_offset += size; 500 } 501 502 this.elements = elements.idup; 503 this.elementIsNULL = elementIsNULL.idup; 504 } 505 } 506 507 /// Returns number of elements in array 508 /// Useful for one-dimensional arrays 509 size_t length() 510 { 511 return nElems; 512 } 513 514 /// Returns Value struct by index 515 /// Useful for one-dimensional arrays 516 immutable (Value) opIndex(size_t n) 517 { 518 return opIndex(n.to!int); 519 } 520 521 /// Returns Value struct by index 522 /// Useful for one-dimensional arrays 523 immutable (Value) opIndex(int n) 524 { 525 return getValue(n); 526 } 527 528 /// Returns Value struct 529 /// Useful for multidimensional arrays 530 immutable (Value) getValue( ... ) 531 { 532 auto n = coords2Serial( _argptr, _arguments ); 533 534 // it is legal to cast here because immutable value will be returned 535 Value r = Value(cast(ubyte[]) elements[n], OID, elementIsNULL[n], ValueFormat.BINARY); 536 537 return cast(immutable) r; 538 } 539 540 /// Value NULL checking 541 bool isNULL( ... ) 542 { 543 auto n = coords2Serial( _argptr, _arguments ); 544 return elementIsNULL[n]; 545 } 546 547 private size_t coords2Serial( va_list _argptr, TypeInfo[] _arguments ) 548 { 549 assert( _arguments.length > 0, "Number of the arguments must be more than 0" ); 550 551 // Variadic args parsing 552 auto args = new int[ _arguments.length ]; 553 554 if(!(dimsSize.length == args.length)) 555 throw new AnswerException( 556 ExceptionType.OUT_OF_RANGE, 557 "Mismatched dimensions number in arguments and server reply", 558 __FILE__, __LINE__ 559 ); 560 561 for( uint i; i < args.length; ++i ) 562 { 563 assert( _arguments[i] == typeid(int) ); 564 args[i] = va_arg!(int)(_argptr); 565 566 if(!(dimsSize[i] > args[i])) 567 throw new AnswerException( 568 ExceptionType.OUT_OF_RANGE, 569 "Out of range", 570 __FILE__, __LINE__ 571 ); 572 } 573 574 // Calculates serial number of the element 575 auto inner = args.length - 1; // inner dimension 576 auto element_num = args[inner]; // serial number of the element 577 uint s = 1; // perpendicular to a vector which size is calculated currently 578 for( auto i = inner; i > 0; --i ) 579 { 580 s *= dimsSize[i]; 581 element_num += s * args[i-1]; 582 } 583 584 assert( element_num <= nElems ); 585 return element_num; 586 } 587 } 588 589 /// Notify 590 class Notify 591 { 592 private immutable PGnotify* n; 593 594 this(immutable PGnotify* pgn ) 595 { 596 n = pgn; 597 cast(void) enforceEx!OutOfMemoryError(n, "Can't write notify"); 598 } 599 600 ~this() 601 { 602 PQfreemem( cast(void*) n ); 603 } 604 605 /// Returns notification condition name 606 string name() { return to!string( n.relname ); } 607 608 /// Returns notification parameter 609 string extra() { return to!string( n.extra ); } 610 611 /// Returns process ID of notifying server process 612 size_t pid() { return n.be_pid; } 613 614 nothrow invariant() 615 { 616 assert( n != null ); 617 } 618 } 619 620 /// Answer creation exception 621 /// Useful for analyze error data 622 class AnswerCreationException : Dpq2Exception 623 { 624 immutable(Result) result; 625 alias result this; 626 627 this(immutable(Result) result, string file, size_t line) 628 { 629 this.result = result; 630 631 super(result.resultErrorMessage(), file, line); 632 } 633 } 634 635 /// Answer exception types 636 enum ExceptionType 637 { 638 FATAL_ERROR, 639 COLUMN_NOT_FOUND, /// Column is not found 640 OUT_OF_RANGE 641 } 642 643 /// Exception 644 class AnswerException : Dpq2Exception 645 { 646 const ExceptionType type; /// Exception type 647 648 this(ExceptionType t, string msg, string file, size_t line) pure @safe 649 { 650 type = t; 651 super(msg, file, line); 652 } 653 } 654 655 package immutable msg_NOT_BINARY = "Format of the column is not binary"; 656 657 void _integration_test( string connParam ) 658 { 659 import core.exception: AssertError; 660 661 auto conn = new Connection(connParam); 662 663 // Text type results testing 664 { 665 string sql_query = 666 "select now() as time, 'abc'::text as field_name, 123, 456.78\n"~ 667 "union all\n"~ 668 669 "select now(), 'def'::text, 456, 910.11\n"~ 670 "union all\n"~ 671 672 "select NULL, 'ijk_АБВГД'::text, 789, 12345.115345"; 673 674 auto e = conn.exec(sql_query); 675 676 assert( e[1][2].as!PGtext == "456" ); 677 assert( e[2][1].as!PGtext == "ijk_АБВГД" ); 678 assert( !e[0].isNULL(0) ); 679 assert( e[2].isNULL(0) ); 680 assert( e.columnNum( "field_name" ) == 1 ); 681 assert( e[1]["field_name"].as!PGtext == "def" ); 682 assert(e.columnExists("field_name")); 683 assert(!e.columnExists("foo")); 684 } 685 686 // Binary type arguments testing: 687 QueryParams p; 688 p.resultFormat = ValueFormat.BINARY; 689 p.sqlCommand = "SELECT "~ 690 "-32761::smallint, "~ 691 "-2147483646::integer as integer_value, "~ 692 "'first line\nsecond line'::text, "~ 693 "array[[[1, 2, 3], "~ 694 "[4, 5, 6]], "~ 695 696 "[[7, 8, 9], "~ 697 "[10, 11,12]], "~ 698 699 "[[13,14,NULL], "~ 700 "[16,17,18]]]::integer[] as test_array, "~ 701 "NULL::smallint,"~ 702 "array[11,22,NULL,44]::integer[] as small_array, "~ 703 "array['1','23',NULL,'789A']::text[] as text_array, "~ 704 "array[]::text[] as empty_array"; 705 706 auto r = conn.execParams(p); 707 708 { 709 assert( r[0].isNULL(4) ); 710 assert( !r[0].isNULL(2) ); 711 712 assert( r.OID(3) == OidType.Int4Array ); 713 assert( r.isSupportedArray(3) ); 714 assert( !r.isSupportedArray(2) ); 715 auto v = r[0]["test_array"]; 716 assert( v.isSupportedArray ); 717 assert( !r[0][2].isSupportedArray ); 718 auto a = v.asArray; 719 assert( a.OID == OidType.Int4 ); 720 assert( a.getValue(2,1,2).as!PGinteger == 18 ); 721 assert( a.isNULL(2,0,2) ); 722 assert( !a.isNULL(2,1,2) ); 723 assert( r[0]["small_array"].asArray[1].as!PGinteger == 22 ); 724 assert( r[0]["small_array"].asArray[2].isNull ); 725 assert( r[0]["text_array"].asArray[2].isNull ); 726 assert( r.columnName(3) == "test_array" ); 727 assert( r[0].columnName(3) == "test_array" ); 728 assert( r[0]["empty_array"].asArray.nElems == 0 ); 729 assert( r[0]["empty_array"].asArray.dimsSize.length == 0 ); 730 assert( r[0]["empty_array"].asArray.length == 0 ); 731 assert( r[0]["text_array"].asArray.length == 4 ); 732 assert( r[0]["test_array"].asArray.length == 18 ); 733 734 // Access to NULL cell 735 { 736 bool isNullFlag = false; 737 try 738 cast(void) r[0][4].as!PGsmallint; 739 catch(AssertError) 740 isNullFlag = true; 741 finally 742 assert(isNullFlag); 743 } 744 745 // Access to NULL array element 746 { 747 bool isNullFlag = false; 748 try 749 cast(void) r[0]["small_array"].asArray[2].as!PGinteger; 750 catch(AssertError) 751 isNullFlag = true; 752 finally 753 assert(isNullFlag); 754 } 755 } 756 757 // Notifies test 758 { 759 conn.exec( "listen test_notify; notify test_notify, 'test payload'" ); 760 auto notify = conn.getNextNotify; 761 762 assert( notify.name == "test_notify" ); 763 assert( notify.extra == "test payload" ); 764 } 765 766 // Async query test 1 767 conn.sendQuery( "select 123; select 456; select 789" ); 768 while( conn.getResult() !is null ){} 769 assert( conn.getResult() is null ); // removes null answer at the end 770 771 // Async query test 2 772 conn.sendQueryParams(p); 773 while( conn.getResult() !is null ){} 774 assert( conn.getResult() is null ); // removes null answer at the end 775 776 { 777 // Range test 778 auto rowsRange = rangify(r); 779 size_t count = 0; 780 781 foreach(row; rowsRange) 782 foreach(elem; rangify(row)) 783 count++; 784 785 assert(count == 8); 786 } 787 788 { 789 bool exceptionFlag = false; 790 791 try r[0]["integer_value"].as!PGtext; 792 catch(AnswerConvException e) 793 { 794 exceptionFlag = true; 795 assert(e.msg.length > 5); // error message check 796 } 797 finally 798 assert(exceptionFlag); 799 } 800 801 { 802 bool exceptionFlag = false; 803 804 try conn.exec("WRONG SQL QUERY"); 805 catch(AnswerCreationException e) 806 { 807 exceptionFlag = true; 808 assert(e.msg.length > 20); // error message check 809 810 version(LDC) destroy(e); // before Derelict unloads its bindings (prevents SIGSEGV) 811 } 812 finally 813 assert(exceptionFlag); 814 } 815 }