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