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 }