1 /**
2  * Orthanc - A Lightweight, RESTful DICOM Store
3  * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
4  * Department, University Hospital of Liege, Belgium
5  * Copyright (C) 2017-2021 Osimis S.A., Belgium
6  *
7  * This program is free software: you can redistribute it and/or
8  * modify it under the terms of the GNU Affero General Public License
9  * as published by the Free Software Foundation, either version 3 of
10  * the License, or (at your option) any later version.
11  *
12  * This program is distributed in the hope that it will be useful, but
13  * WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
15  * Affero General Public License for more details.
16  *
17  * You should have received a copy of the GNU Affero General Public License
18  * along with this program. If not, see <http://www.gnu.org/licenses/>.
19  **/
20 
21 
22 #include "PostgreSQLIncludes.h"  // Must be the first
23 #include "PostgreSQLStatement.h"
24 
25 #include "../Common/BinaryStringValue.h"
26 #include "../Common/InputFileValue.h"
27 #include "../Common/Integer64Value.h"
28 #include "../Common/NullValue.h"
29 #include "../Common/ResultBase.h"
30 #include "../Common/Utf8StringValue.h"
31 #include "PostgreSQLResult.h"
32 
33 #include <Compatibility.h>  // For std::unique_ptr<>
34 #include <Logging.h>
35 #include <OrthancException.h>
36 #include <Toolbox.h>
37 #include <Endianness.h>
38 
39 #include <cassert>
40 
41 
42 namespace OrthancDatabases
43 {
44   class PostgreSQLStatement::Inputs : public boost::noncopyable
45   {
46   private:
47     std::vector<char*> values_;
48     std::vector<int> sizes_;
49 
Allocate(const void * source,int size)50     static char* Allocate(const void* source, int size)
51     {
52       if (size == 0)
53       {
54         return NULL;
55       }
56       else
57       {
58         char* ptr = reinterpret_cast<char*>(malloc(size));
59 
60         if (source != NULL)
61         {
62           memcpy(ptr, source, size);
63         }
64 
65         return ptr;
66       }
67     }
68 
Resize(size_t size)69     void Resize(size_t size)
70     {
71       // Shrinking of the vector
72       for (size_t i = size; i < values_.size(); i++)
73       {
74         if (values_[i] != NULL)
75           free(values_[i]);
76       }
77 
78       values_.resize(size, NULL);
79       sizes_.resize(size, 0);
80     }
81 
EnlargeForIndex(size_t index)82     void EnlargeForIndex(size_t index)
83     {
84       if (index >= values_.size())
85       {
86         // The vector is too small
87         Resize(index + 1);
88       }
89     }
90 
91   public:
Inputs()92     Inputs()
93     {
94     }
95 
~Inputs()96     ~Inputs()
97     {
98       Resize(0);
99     }
100 
SetItem(size_t pos,const void * source,int size)101     void SetItem(size_t pos, const void* source, int size)
102     {
103       EnlargeForIndex(pos);
104 
105       if (sizes_[pos] == size)
106       {
107         if (source && size != 0)
108         {
109           memcpy(values_[pos], source, size);
110         }
111       }
112       else
113       {
114         if (values_[pos] != NULL)
115         {
116           free(values_[pos]);
117         }
118 
119         values_[pos] = Allocate(source, size);
120         sizes_[pos] = size;
121       }
122     }
123 
SetItem(size_t pos,int size)124     void SetItem(size_t pos, int size)
125     {
126       SetItem(pos, NULL, size);
127     }
128 
GetItem(size_t pos) const129     void* GetItem(size_t pos) const
130     {
131       if (pos >= values_.size())
132       {
133         throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
134       }
135 
136       return values_[pos];
137     }
138 
GetValues() const139     const std::vector<char*>& GetValues() const
140     {
141       return values_;
142     }
143 
GetSizes() const144     const std::vector<int>& GetSizes() const
145     {
146       return sizes_;
147     }
148   };
149 
150 
Prepare()151   void PostgreSQLStatement::Prepare()
152   {
153     if (id_.size() > 0)
154     {
155       // Already prepared
156       return;
157     }
158 
159     for (size_t i = 0; i < oids_.size(); i++)
160     {
161       if (oids_[i] == 0)
162       {
163         // The type of an input parameter was not set
164         throw Orthanc::OrthancException(Orthanc::ErrorCode_BadSequenceOfCalls);
165       }
166     }
167 
168     id_ = Orthanc::Toolbox::GenerateUuid();
169 
170     const unsigned int* tmp = oids_.size() ? &oids_[0] : NULL;
171 
172     PGresult* result = PQprepare(reinterpret_cast<PGconn*>(database_.pg_),
173                                  id_.c_str(), sql_.c_str(), oids_.size(), tmp);
174 
175     if (result == NULL)
176     {
177       id_.clear();
178       database_.ThrowException(true);
179     }
180 
181     bool ok = (PQresultStatus(result) == PGRES_COMMAND_OK);
182     if (ok)
183     {
184       PQclear(result);
185     }
186     else
187     {
188       std::string message = PQresultErrorMessage(result);
189       PQclear(result);
190       id_.clear();
191       LOG(ERROR) << "PostgreSQL error: " << message;
192       database_.ThrowException(false);
193     }
194   }
195 
196 
Unprepare()197   void PostgreSQLStatement::Unprepare()
198   {
199     if (id_.size() > 0)
200     {
201       // "Although there is no libpq function for deleting a
202       // prepared statement, the SQL DEALLOCATE statement can be
203       // used for that purpose."
204       database_.ExecuteMultiLines("DEALLOCATE \"" + id_ + "\"");
205     }
206 
207     id_.clear();
208   }
209 
210 
DeclareInputInternal(unsigned int param,unsigned int type)211   void PostgreSQLStatement::DeclareInputInternal(unsigned int param,
212                                                  unsigned int /*Oid*/ type)
213   {
214     Unprepare();
215 
216     if (oids_.size() <= param)
217     {
218       oids_.resize(param + 1, 0);
219       binary_.resize(param + 1);
220     }
221 
222     oids_[param] = type;
223     binary_[param] = (type == TEXTOID || type == BYTEAOID || type == OIDOID) ? 0 : 1;
224   }
225 
226 
DeclareInputInteger(unsigned int param)227   void PostgreSQLStatement::DeclareInputInteger(unsigned int param)
228   {
229     DeclareInputInternal(param, INT4OID);
230   }
231 
232 
DeclareInputInteger64(unsigned int param)233   void PostgreSQLStatement::DeclareInputInteger64(unsigned int param)
234   {
235     DeclareInputInternal(param, INT8OID);
236   }
237 
238 
DeclareInputString(unsigned int param)239   void PostgreSQLStatement::DeclareInputString(unsigned int param)
240   {
241     DeclareInputInternal(param, TEXTOID);
242   }
243 
244 
DeclareInputBinary(unsigned int param)245   void PostgreSQLStatement::DeclareInputBinary(unsigned int param)
246   {
247     DeclareInputInternal(param, BYTEAOID);
248   }
249 
250 
DeclareInputLargeObject(unsigned int param)251   void PostgreSQLStatement::DeclareInputLargeObject(unsigned int param)
252   {
253     DeclareInputInternal(param, OIDOID);
254   }
255 
256 
Execute()257   void* /* PGresult* */ PostgreSQLStatement::Execute()
258   {
259     Prepare();
260 
261     PGresult* result;
262 
263     if (oids_.size() == 0)
264     {
265       // No parameter
266       result = PQexecPrepared(reinterpret_cast<PGconn*>(database_.pg_),
267                               id_.c_str(), 0, NULL, NULL, NULL, 1);
268     }
269     else
270     {
271       // At least 1 parameter
272       result = PQexecPrepared(reinterpret_cast<PGconn*>(database_.pg_),
273                               id_.c_str(),
274                               oids_.size(),
275                               &inputs_->GetValues()[0],
276                               &inputs_->GetSizes()[0],
277                               &binary_[0],
278                               1);
279     }
280 
281     if (PQtransactionStatus(reinterpret_cast<PGconn*>(database_.pg_)) == PQTRANS_INERROR)
282     {
283       if (result != NULL)
284       {
285         PQclear(result);
286       }
287 
288 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 9, 2)
289       throw Orthanc::OrthancException(Orthanc::ErrorCode_DatabaseCannotSerialize);
290 #else
291       throw Orthanc::OrthancException(Orthanc::ErrorCode_Database, "Collision between multiple writers");
292 #endif
293     }
294     else if (result == NULL)
295     {
296       database_.ThrowException(true);
297     }
298 
299     return result;
300   }
301 
302 
PostgreSQLStatement(PostgreSQLDatabase & database,const std::string & sql)303   PostgreSQLStatement::PostgreSQLStatement(PostgreSQLDatabase& database,
304                                            const std::string& sql) :
305     database_(database),
306     sql_(sql),
307     inputs_(new Inputs),
308     formatter_(Dialect_PostgreSQL)
309   {
310     LOG(TRACE) << "PostgreSQL: " << sql;
311   }
312 
313 
PostgreSQLStatement(PostgreSQLDatabase & database,const Query & query)314   PostgreSQLStatement::PostgreSQLStatement(PostgreSQLDatabase& database,
315                                            const Query& query) :
316     database_(database),
317     inputs_(new Inputs),
318     formatter_(Dialect_PostgreSQL)
319   {
320     query.Format(sql_, formatter_);
321     LOG(TRACE) << "PostgreSQL: " << sql_;
322 
323     for (size_t i = 0; i < formatter_.GetParametersCount(); i++)
324     {
325       switch (formatter_.GetParameterType(i))
326       {
327         case ValueType_Integer64:
328           DeclareInputInteger64(i);
329           break;
330 
331         case ValueType_Utf8String:
332           DeclareInputString(i);
333           break;
334 
335         case ValueType_BinaryString:
336           DeclareInputBinary(i);
337           break;
338 
339         case ValueType_InputFile:
340           DeclareInputLargeObject(i);
341           break;
342 
343         case ValueType_Null:
344         default:
345           throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
346       }
347     }
348   }
349 
350 
~PostgreSQLStatement()351   PostgreSQLStatement::~PostgreSQLStatement()
352   {
353     try
354     {
355       Unprepare();
356     }
357     catch (Orthanc::OrthancException&)
358     {
359       // Ignore possible exceptions due to connection loss
360     }
361   }
362 
363 
Run()364   void PostgreSQLStatement::Run()
365   {
366     PGresult* result = reinterpret_cast<PGresult*>(Execute());
367     assert(result != NULL);   // An exception would have been thrown otherwise
368 
369     bool ok = (PQresultStatus(result) == PGRES_COMMAND_OK ||
370                PQresultStatus(result) == PGRES_TUPLES_OK);
371     if (ok)
372     {
373       PQclear(result);
374     }
375     else
376     {
377       std::string error = PQresultErrorMessage(result);
378       PQclear(result);
379       LOG(ERROR) << "PostgreSQL error: " << error;
380       database_.ThrowException(false);
381     }
382   }
383 
384 
BindNull(unsigned int param)385   void PostgreSQLStatement::BindNull(unsigned int param)
386   {
387     if (param >= oids_.size())
388     {
389       throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
390     }
391 
392     inputs_->SetItem(param, 0);
393   }
394 
395 
BindInteger(unsigned int param,int value)396   void PostgreSQLStatement::BindInteger(unsigned int param,
397                                         int value)
398   {
399     if (param >= oids_.size())
400     {
401       throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
402     }
403 
404     if (oids_[param] != INT4OID)
405     {
406       throw Orthanc::OrthancException(Orthanc::ErrorCode_BadParameterType);
407     }
408 
409     assert(sizeof(int32_t) == 4);
410     int32_t v = htobe32(static_cast<int32_t>(value));
411     inputs_->SetItem(param, &v, sizeof(int32_t));
412   }
413 
414 
BindInteger64(unsigned int param,int64_t value)415   void PostgreSQLStatement::BindInteger64(unsigned int param,
416                                           int64_t value)
417   {
418     if (param >= oids_.size())
419     {
420       throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
421     }
422 
423     if (oids_[param] != INT8OID)
424     {
425       throw Orthanc::OrthancException(Orthanc::ErrorCode_BadParameterType);
426     }
427 
428     assert(sizeof(int64_t) == 8);
429     int64_t v = htobe64(value);
430     inputs_->SetItem(param, &v, sizeof(int64_t));
431   }
432 
433 
BindString(unsigned int param,const std::string & value)434   void PostgreSQLStatement::BindString(unsigned int param,
435                                        const std::string& value)
436   {
437     if (param >= oids_.size())
438     {
439       throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
440     }
441 
442     if (oids_[param] != TEXTOID && oids_[param] != BYTEAOID)
443     {
444       throw Orthanc::OrthancException(Orthanc::ErrorCode_BadParameterType);
445     }
446 
447     if (value.size() == 0)
448     {
449       inputs_->SetItem(param, "", 1 /* end-of-string character */);
450     }
451     else
452     {
453       inputs_->SetItem(param, value.c_str(),
454                        value.size() + 1);  // "+1" for end-of-string character
455     }
456   }
457 
458 
BindLargeObject(unsigned int param,const PostgreSQLLargeObject & value)459   void PostgreSQLStatement::BindLargeObject(unsigned int param,
460                                             const PostgreSQLLargeObject& value)
461   {
462     if (param >= oids_.size())
463     {
464       throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
465     }
466 
467     if (oids_[param] != OIDOID)
468     {
469       throw Orthanc::OrthancException(Orthanc::ErrorCode_BadParameterType);
470     }
471 
472     inputs_->SetItem(param, value.GetOid().c_str(),
473                      value.GetOid().size() + 1);  // "+1" for end-of-string character
474   }
475 
476 
477   class PostgreSQLStatement::ResultWrapper : public ResultBase
478   {
479   private:
480     std::unique_ptr<PostgreSQLResult>  result_;
481 
482   protected:
FetchField(size_t index)483     virtual IValue* FetchField(size_t index)
484     {
485       return result_->GetValue(index);
486     }
487 
488   public:
ResultWrapper(PostgreSQLStatement & statement)489     explicit ResultWrapper(PostgreSQLStatement& statement) :
490       result_(new PostgreSQLResult(statement))
491     {
492       SetFieldsCount(result_->GetColumnsCount());
493       FetchFields();
494     }
495 
Next()496     virtual void Next()
497     {
498       result_->Next();
499       FetchFields();
500     }
501 
IsDone() const502     virtual bool IsDone() const
503     {
504       return result_->IsDone();
505     }
506   };
507 
508 
Execute(ITransaction & transaction,const Dictionary & parameters)509   IResult* PostgreSQLStatement::Execute(ITransaction& transaction,
510                                         const Dictionary& parameters)
511   {
512     for (size_t i = 0; i < formatter_.GetParametersCount(); i++)
513     {
514       const std::string& name = formatter_.GetParameterName(i);
515 
516       switch (formatter_.GetParameterType(i))
517       {
518         case ValueType_Integer64:
519           BindInteger64(i, dynamic_cast<const Integer64Value&>(parameters.GetValue(name)).GetValue());
520           break;
521 
522         case ValueType_Null:
523           BindNull(i);
524           break;
525 
526         case ValueType_Utf8String:
527           BindString(i, dynamic_cast<const Utf8StringValue&>
528                      (parameters.GetValue(name)).GetContent());
529           break;
530 
531         case ValueType_BinaryString:
532           BindString(i, dynamic_cast<const BinaryStringValue&>
533                      (parameters.GetValue(name)).GetContent());
534           break;
535 
536         case ValueType_InputFile:
537         {
538           const InputFileValue& blob =
539             dynamic_cast<const InputFileValue&>(parameters.GetValue(name));
540 
541           PostgreSQLLargeObject largeObject(database_, blob.GetContent());
542           BindLargeObject(i, largeObject);
543           break;
544         }
545 
546         default:
547           throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
548       }
549     }
550 
551     return new ResultWrapper(*this);
552   }
553 
554 
ExecuteWithoutResult(ITransaction & transaction,const Dictionary & parameters)555   void PostgreSQLStatement::ExecuteWithoutResult(ITransaction& transaction,
556                                                  const Dictionary& parameters)
557   {
558     std::unique_ptr<IResult> dummy(Execute(transaction, parameters));
559   }
560 }
561