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