Mercurial > hg > orthanc-databases
annotate Framework/PostgreSQL/PostgreSQLDatabase.cpp @ 59:318c1ccb787c
fix build with postgresql-11
| author | s.jodogne@gmail.com |
|---|---|
| date | Sun, 28 Oct 2018 12:29:22 +0100 |
| parents | 6a574d810b98 |
| children | 714c5d2bee76 |
| rev | line source |
|---|---|
| 0 | 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-2018 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 "PostgreSQLDatabase.h" | |
| 23 | |
| 24 #include "PostgreSQLResult.h" | |
| 25 #include "PostgreSQLStatement.h" | |
| 26 #include "PostgreSQLTransaction.h" | |
|
23
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
27 #include "../Common/ImplicitTransaction.h" |
| 0 | 28 |
| 59 | 29 #include <pg_config.h> |
| 30 | |
| 31 #if PG_VERSION_NUM >= 110000 | |
| 32 # include <postgres.h> | |
| 33 # undef LOG // This one comes from <postgres.h>, and conflicts with <Core/Logging.h> | |
| 34 #endif | |
| 35 | |
| 0 | 36 #include <Core/Logging.h> |
| 37 #include <Core/OrthancException.h> | |
| 38 | |
| 39 #include <boost/lexical_cast.hpp> | |
| 40 | |
| 41 // PostgreSQL includes | |
| 42 #include <libpq-fe.h> | |
| 43 #include <c.h> | |
| 44 #include <catalog/pg_type.h> | |
| 45 | |
| 46 | |
| 47 namespace OrthancDatabases | |
| 48 { | |
| 49 void PostgreSQLDatabase::ThrowException(bool log) | |
| 50 { | |
| 51 if (log) | |
| 52 { | |
| 53 LOG(ERROR) << "PostgreSQL error: " | |
| 54 << PQerrorMessage(reinterpret_cast<PGconn*>(pg_)); | |
| 55 } | |
| 56 | |
| 57 if (PQstatus(reinterpret_cast<PGconn*>(pg_)) == CONNECTION_OK) | |
| 58 { | |
| 59 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); | |
| 60 } | |
| 61 else | |
| 62 { | |
| 63 throw Orthanc::OrthancException(Orthanc::ErrorCode_DatabaseUnavailable); | |
| 64 } | |
| 65 } | |
| 66 | |
| 67 | |
| 68 void PostgreSQLDatabase::Close() | |
| 69 { | |
| 70 if (pg_ != NULL) | |
| 71 { | |
| 72 LOG(INFO) << "Closing connection to PostgreSQL"; | |
| 73 PQfinish(reinterpret_cast<PGconn*>(pg_)); | |
| 74 pg_ = NULL; | |
| 75 } | |
| 76 } | |
| 77 | |
|
46
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
78 |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
79 PostgreSQLDatabase::~PostgreSQLDatabase() |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
80 { |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
81 try |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
82 { |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
83 Close(); |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
84 } |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
85 catch (Orthanc::OrthancException&) |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
86 { |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
87 // Ignore possible exceptions due to connection loss |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
88 } |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
89 } |
|
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
29
diff
changeset
|
90 |
| 0 | 91 |
| 92 void PostgreSQLDatabase::Open() | |
| 93 { | |
| 94 if (pg_ != NULL) | |
| 95 { | |
| 96 // Already connected | |
| 97 return; | |
| 98 } | |
| 99 | |
| 100 std::string s; | |
| 101 parameters_.Format(s); | |
| 102 | |
| 103 pg_ = PQconnectdb(s.c_str()); | |
| 104 | |
| 105 if (pg_ == NULL || | |
| 106 PQstatus(reinterpret_cast<PGconn*>(pg_)) != CONNECTION_OK) | |
| 107 { | |
| 108 std::string message; | |
| 109 | |
| 110 if (pg_) | |
| 111 { | |
| 112 message = PQerrorMessage(reinterpret_cast<PGconn*>(pg_)); | |
| 113 PQfinish(reinterpret_cast<PGconn*>(pg_)); | |
| 114 pg_ = NULL; | |
| 115 } | |
| 116 | |
| 117 LOG(ERROR) << "PostgreSQL error: " << message; | |
| 118 throw Orthanc::OrthancException(Orthanc::ErrorCode_DatabaseUnavailable); | |
| 119 } | |
|
12
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
120 } |
| 0 | 121 |
| 122 | |
|
12
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
123 void PostgreSQLDatabase::AdvisoryLock(int32_t lock) |
|
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
124 { |
|
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
125 PostgreSQLTransaction transaction(*this); |
| 0 | 126 |
| 29 | 127 Query query("select pg_try_advisory_lock(" + |
| 128 boost::lexical_cast<std::string>(lock) + ");", false); | |
| 129 PostgreSQLStatement s(*this, query); | |
| 0 | 130 |
|
12
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
131 PostgreSQLResult result(s); |
|
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
132 if (result.IsDone() || |
|
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
133 !result.GetBoolean(0)) |
|
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
134 { |
|
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
135 LOG(ERROR) << "The PostgreSQL database is locked by another instance of Orthanc"; |
|
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
136 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); |
|
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
137 } |
| 0 | 138 |
|
12
41543239072d
transactions for storage area
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
139 transaction.Commit(); |
| 0 | 140 } |
| 141 | |
| 142 | |
| 143 void PostgreSQLDatabase::Execute(const std::string& sql) | |
| 144 { | |
| 145 LOG(TRACE) << "PostgreSQL: " << sql; | |
| 146 Open(); | |
| 147 | |
| 148 PGresult* result = PQexec(reinterpret_cast<PGconn*>(pg_), sql.c_str()); | |
| 149 if (result == NULL) | |
| 150 { | |
| 151 ThrowException(true); | |
| 152 } | |
| 153 | |
| 154 bool ok = (PQresultStatus(result) == PGRES_COMMAND_OK || | |
| 155 PQresultStatus(result) == PGRES_TUPLES_OK); | |
| 156 | |
| 157 if (ok) | |
| 158 { | |
| 159 PQclear(result); | |
| 160 } | |
| 161 else | |
| 162 { | |
| 163 std::string message = PQresultErrorMessage(result); | |
| 164 PQclear(result); | |
| 165 | |
| 166 LOG(ERROR) << "PostgreSQL error: " << message; | |
| 167 ThrowException(false); | |
| 168 } | |
| 169 } | |
| 170 | |
| 171 | |
| 172 bool PostgreSQLDatabase::DoesTableExist(const char* name) | |
| 173 { | |
| 174 std::string lower(name); | |
| 175 std::transform(lower.begin(), lower.end(), lower.begin(), tolower); | |
| 176 | |
| 177 // http://stackoverflow.com/a/24089729/881731 | |
| 178 | |
| 179 PostgreSQLStatement statement(*this, | |
| 180 "SELECT 1 FROM pg_catalog.pg_class c " | |
| 181 "JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " | |
| 182 "WHERE n.nspname = 'public' AND c.relkind='r' " | |
| 183 "AND c.relname=$1", true); | |
| 184 | |
| 185 statement.DeclareInputString(0); | |
| 186 statement.BindString(0, lower); | |
| 187 | |
| 188 PostgreSQLResult result(statement); | |
| 189 return !result.IsDone(); | |
| 190 } | |
| 191 | |
| 192 | |
| 193 void PostgreSQLDatabase::ClearAll() | |
| 194 { | |
| 195 PostgreSQLTransaction transaction(*this); | |
| 196 | |
| 197 // Remove all the large objects | |
| 198 Execute("SELECT lo_unlink(loid) FROM (SELECT DISTINCT loid FROM pg_catalog.pg_largeobject) as loids;"); | |
| 199 | |
| 200 // http://stackoverflow.com/a/21247009/881731 | |
| 201 Execute("DROP SCHEMA public CASCADE;"); | |
| 202 Execute("CREATE SCHEMA public;"); | |
| 203 Execute("GRANT ALL ON SCHEMA public TO postgres;"); | |
| 204 Execute("GRANT ALL ON SCHEMA public TO public;"); | |
| 205 Execute("COMMENT ON SCHEMA public IS 'standard public schema';"); | |
| 206 | |
| 207 transaction.Commit(); | |
| 208 } | |
| 209 | |
| 210 | |
| 211 IPrecompiledStatement* PostgreSQLDatabase::Compile(const Query& query) | |
| 212 { | |
| 213 return new PostgreSQLStatement(*this, query); | |
| 214 } | |
| 215 | |
| 216 | |
|
23
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
217 namespace |
| 0 | 218 { |
|
23
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
219 class PostgreSQLImplicitTransaction : public ImplicitTransaction |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
220 { |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
221 private: |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
222 PostgreSQLDatabase& db_; |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
223 |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
224 protected: |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
225 virtual IResult* ExecuteInternal(IPrecompiledStatement& statement, |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
226 const Dictionary& parameters) |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
227 { |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
228 return dynamic_cast<PostgreSQLStatement&>(statement).Execute(*this, parameters); |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
229 } |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
230 |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
231 virtual void ExecuteWithoutResultInternal(IPrecompiledStatement& statement, |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
232 const Dictionary& parameters) |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
233 { |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
234 dynamic_cast<PostgreSQLStatement&>(statement).ExecuteWithoutResult(*this, parameters); |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
235 } |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
236 |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
237 public: |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
238 PostgreSQLImplicitTransaction(PostgreSQLDatabase& db) : |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
239 db_(db) |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
240 { |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
241 } |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
242 }; |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
243 } |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
244 |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
245 |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
246 ITransaction* PostgreSQLDatabase::CreateTransaction(bool isImplicit) |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
247 { |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
248 if (isImplicit) |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
249 { |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
250 return new PostgreSQLImplicitTransaction(*this); |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
251 } |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
252 else |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
253 { |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
254 return new PostgreSQLTransaction(*this); |
|
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
12
diff
changeset
|
255 } |
| 0 | 256 } |
| 257 } |