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