Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/PostgreSQLIndex.cpp @ 437:d979f25e60cf pg-transactions
Re-organized DB creation/upgrade into standalone files
author | Alain Mazy <am@osimis.io> |
---|---|
date | Mon, 18 Dec 2023 18:50:01 +0100 |
parents | f16faa1fdc46 |
children | f2427f94d879 |
comparison
equal
deleted
inserted
replaced
436:f16faa1fdc46 | 437:d979f25e60cf |
---|---|
59 IDatabaseFactory* PostgreSQLIndex::CreateDatabaseFactory() | 59 IDatabaseFactory* PostgreSQLIndex::CreateDatabaseFactory() |
60 { | 60 { |
61 return PostgreSQLDatabase::CreateDatabaseFactory(parameters_); | 61 return PostgreSQLDatabase::CreateDatabaseFactory(parameters_); |
62 } | 62 } |
63 | 63 |
64 void PostgreSQLIndex::ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager) | |
65 { | |
66 std::string query; | |
67 | |
68 Orthanc::EmbeddedResources::GetFileResource | |
69 (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX_V2); | |
70 t.GetDatabaseTransaction().ExecuteMultiLines(query); | |
71 | |
72 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion, 6); | |
73 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, 2); | |
74 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 3); // this is the 3rd version ! | |
75 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 1); | |
76 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1); | |
77 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasFastCountResources, 1); | |
78 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1); | |
79 } | |
64 | 80 |
65 void PostgreSQLIndex::ConfigureDatabase(DatabaseManager& manager, | 81 void PostgreSQLIndex::ConfigureDatabase(DatabaseManager& manager, |
66 bool hasIdentifierTags, | 82 bool hasIdentifierTags, |
67 const std::list<IdentifierTag>& identifierTags) | 83 const std::list<IdentifierTag>& identifierTags) |
68 { | 84 { |
75 | 91 |
76 // Check the expected version of the database | 92 // Check the expected version of the database |
77 if (expectedVersion != 6) | 93 if (expectedVersion != 6) |
78 { | 94 { |
79 LOG(ERROR) << "This database plugin is incompatible with your version of Orthanc " | 95 LOG(ERROR) << "This database plugin is incompatible with your version of Orthanc " |
80 << "expecting the DB schema version " << expectedVersion | 96 << "expecting the Orthanc DB schema version " << expectedVersion |
81 << ", but this plugin is only compatible with version 6"; | 97 << ", but this plugin is only compatible with version 6"; |
82 throw Orthanc::OrthancException(Orthanc::ErrorCode_Plugin); | 98 throw Orthanc::OrthancException(Orthanc::ErrorCode_Plugin); |
83 } | 99 } |
84 | 100 |
85 PostgreSQLDatabase& db = dynamic_cast<PostgreSQLDatabase&>(manager.GetDatabase()); | 101 PostgreSQLDatabase& db = dynamic_cast<PostgreSQLDatabase&>(manager.GetDatabase()); |
88 { | 104 { |
89 db.AdvisoryLock(POSTGRESQL_LOCK_INDEX); | 105 db.AdvisoryLock(POSTGRESQL_LOCK_INDEX); |
90 } | 106 } |
91 | 107 |
92 { | 108 { |
109 // lock the full DB while checking if it needs to be create/ugraded | |
93 PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP); | 110 PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP); |
94 bool shouldInstallFastTotalStats2 = false; | |
95 | 111 |
96 if (clearAll_) | 112 if (clearAll_) |
97 { | 113 { |
98 db.ClearAll(); | 114 db.ClearAll(); |
99 } | 115 } |
101 { | 117 { |
102 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); | 118 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); |
103 | 119 |
104 if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) | 120 if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) |
105 { | 121 { |
106 std::string query; | 122 LOG(WARNING) << "PostgreSQL is creating the database schema"; |
107 | 123 |
108 Orthanc::EmbeddedResources::GetFileResource | 124 ApplyPrepareIndex(t, manager); |
109 (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX); | 125 |
110 t.GetDatabaseTransaction().ExecuteMultiLines(query); | 126 if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) |
111 | 127 { |
112 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion, expectedVersion); | 128 LOG(ERROR) << "Corrupted PostgreSQL database or failed to create the database schema"; |
113 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, 1); | 129 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); |
114 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 0); | |
115 } | |
116 | |
117 if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) | |
118 { | |
119 LOG(ERROR) << "Corrupted PostgreSQL database"; | |
120 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); | |
121 } | |
122 | |
123 int version = 0; | |
124 if (!LookupGlobalIntegerProperty(version, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion) || | |
125 version != 6) | |
126 { | |
127 LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema version: " << version; | |
128 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); | |
129 } | |
130 | |
131 int revision; | |
132 if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) | |
133 { | |
134 revision = 1; | |
135 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); | |
136 } | |
137 | |
138 if (revision == 1) | |
139 { | |
140 { | |
141 LOG(WARNING) << "PostgreSQL plugin: adding UNIQUE(publicId) constraint to the 'Resources' table "; | |
142 t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Resources ADD UNIQUE (publicId);"); | |
143 } | |
144 | |
145 { | |
146 LOG(WARNING) << "PostgreSQL plugin: adding or replacing ResourceDeletedFunc"; | |
147 | |
148 std::string query; | |
149 Orthanc::EmbeddedResources::GetFileResource | |
150 (query, Orthanc::EmbeddedResources::POSTGRESQL_RESOURCE_DELETED_FUNC); | |
151 t.GetDatabaseTransaction().ExecuteMultiLines(query); | |
152 } | |
153 | |
154 { | |
155 LOG(WARNING) << "PostgreSQL plugin: adding or replacing InsertOrUpdateMetadata"; | |
156 | |
157 std::string query; | |
158 Orthanc::EmbeddedResources::GetFileResource | |
159 (query, Orthanc::EmbeddedResources::POSTGRESQL_INSERT_UPDATE_METADATA); | |
160 t.GetDatabaseTransaction().ExecuteMultiLines(query); | |
161 } | |
162 | |
163 shouldInstallFastTotalStats2 = true; | |
164 | |
165 revision = 2; | |
166 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); | |
167 } | |
168 | |
169 if (revision != 2) | |
170 { | |
171 LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema revision: " << revision; | |
172 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); | |
173 } | |
174 | |
175 t.Commit(); | |
176 } | |
177 | |
178 { | |
179 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); | |
180 | |
181 int hasTrigram = 0; | |
182 if (!LookupGlobalIntegerProperty(hasTrigram, manager, MISSING_SERVER_IDENTIFIER, | |
183 Orthanc::GlobalProperty_HasTrigramIndex) || | |
184 hasTrigram != 1) | |
185 { | |
186 /** | |
187 * Apply fix for performance issue (speed up wildcard search | |
188 * by using GIN trigrams). This implements the patch suggested | |
189 * in issue #47, BUT we also keep the original | |
190 * "DicomIdentifiersIndexValues", as it leads to better | |
191 * performance for "strict" searches (i.e. searches involving | |
192 * no wildcard). | |
193 * https://www.postgresql.org/docs/current/static/pgtrgm.html | |
194 * https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=47 | |
195 **/ | |
196 try | |
197 { | |
198 // We've observed 9 minutes on DB with 100000 studies | |
199 LOG(WARNING) << "Trying to enable trigram matching on the PostgreSQL database " | |
200 << "to speed up wildcard searches. This may take several minutes"; | |
201 | |
202 t.GetDatabaseTransaction().ExecuteMultiLines( | |
203 "CREATE EXTENSION IF NOT EXISTS pg_trgm; " | |
204 "CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);"); | |
205 | |
206 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 1); | |
207 LOG(WARNING) << "Trigram index has been created"; | |
208 | |
209 t.Commit(); | |
210 } | |
211 catch (Orthanc::OrthancException&) | |
212 { | |
213 LOG(WARNING) << "Performance warning: Your PostgreSQL server does " | |
214 << "not support trigram matching"; | |
215 LOG(WARNING) << "-> Consider installing the \"pg_trgm\" extension on the " | |
216 << "PostgreSQL server, e.g. on Debian: sudo apt install postgresql-contrib"; | |
217 } | 130 } |
218 } | 131 } |
219 else | 132 else |
220 { | 133 { |
221 t.Commit(); | 134 LOG(WARNING) << "The database schema already exists, checking if it needs to be updated"; |
222 } | 135 |
223 } | 136 int version = 0; |
224 | 137 if (!LookupGlobalIntegerProperty(version, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion) || |
225 { | 138 version != 6) |
226 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); | 139 { |
227 | 140 LOG(ERROR) << "PostgreSQL plugin is incompatible with Orthanc database schema version: " << version; |
228 int property = 0; | 141 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); |
229 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, | 142 } |
230 Orthanc::GlobalProperty_HasCreateInstance) || | 143 |
231 property != 3) | 144 bool needToRunUpgradeFromUnknownToV1 = false; |
232 { | 145 bool needToRunUpgradeV1toV2 = false; |
233 LOG(INFO) << "Installing the CreateInstance extension"; | 146 |
234 | 147 int revision; |
235 if (property == 1) | 148 if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) |
236 { | 149 { |
237 // Drop older, experimental versions of this extension | 150 LOG(WARNING) << "No DatabasePatchLevel found, assuming it's 1"; |
238 t.GetDatabaseTransaction().ExecuteMultiLines("DROP FUNCTION CreateInstance(" | 151 revision = 1; |
239 "IN patient TEXT, IN study TEXT, IN series TEXT, in instance TEXT)"); | 152 needToRunUpgradeFromUnknownToV1 = true; |
240 } | 153 needToRunUpgradeV1toV2 = true; |
241 | 154 } |
242 // property == 2 was a first version of the CreateInstance -> we need to replace it by the new one | 155 else if (revision == 1) |
243 // property == 3 is a new version (in v5.2) with same signature and CREATE OR UPDATE | 156 { |
244 // -> we can replace the previous one without deleting it | 157 needToRunUpgradeV1toV2 = true; |
245 // and we can create it if it has never been created. | 158 } |
246 std::string query; | 159 |
247 Orthanc::EmbeddedResources::GetFileResource | 160 int hasTrigram = 0; |
248 (query, Orthanc::EmbeddedResources::POSTGRESQL_CREATE_INSTANCE); | 161 if (!LookupGlobalIntegerProperty(hasTrigram, manager, MISSING_SERVER_IDENTIFIER, |
249 t.GetDatabaseTransaction().ExecuteMultiLines(query); | 162 Orthanc::GlobalProperty_HasTrigramIndex) || |
250 | 163 hasTrigram != 1) |
251 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 3); | 164 { |
252 } | 165 // We've observed 9 minutes on DB with 100000 studies |
253 | 166 LOG(WARNING) << "The DB schema update will try to enable trigram matching on the PostgreSQL database " |
254 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, | 167 << "to speed up wildcard searches. This may take several minutes"; |
255 Orthanc::GlobalProperty_GetTotalSizeIsFast) || | 168 needToRunUpgradeV1toV2 = true; |
256 property != 1) | 169 } |
257 { | 170 |
258 LOG(INFO) << "Installing the FastTotalSize extension"; | 171 int property = 0; |
259 | 172 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, |
260 std::string query; | 173 Orthanc::GlobalProperty_HasFastCountResources) || |
261 Orthanc::EmbeddedResources::GetFileResource | 174 property != 1) |
262 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_SIZE); | 175 { |
263 t.GetDatabaseTransaction().ExecuteMultiLines(query); | 176 needToRunUpgradeV1toV2 = true; |
264 | 177 } |
265 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1); | 178 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, |
266 } | 179 Orthanc::GlobalProperty_GetTotalSizeIsFast) || |
267 | 180 property != 1) |
268 | 181 { |
269 // Installing this extension requires the "GlobalIntegers" table | 182 needToRunUpgradeV1toV2 = true; |
270 // created by the "FastTotalSize" extension | 183 } |
271 property = 0; | 184 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, |
272 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, | 185 Orthanc::GlobalProperty_GetLastChangeIndex) || |
273 Orthanc::GlobalProperty_HasFastCountResources) || | 186 property != 1) |
274 property != 1) | 187 { |
275 { | 188 needToRunUpgradeV1toV2 = true; |
276 LOG(INFO) << "Installing the FastCountResources extension"; | 189 } |
277 | 190 |
278 std::string query; | 191 if (needToRunUpgradeFromUnknownToV1) |
279 Orthanc::EmbeddedResources::GetFileResource | 192 { |
280 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_COUNT_RESOURCES); | 193 LOG(WARNING) << "Upgrading DB schema from unknown to revision 1"; |
281 t.GetDatabaseTransaction().ExecuteMultiLines(query); | 194 std::string query; |
282 | 195 |
283 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasFastCountResources, 1); | 196 Orthanc::EmbeddedResources::GetFileResource |
284 } | 197 (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_UNKNOWN_TO_V1); |
285 | 198 t.GetDatabaseTransaction().ExecuteMultiLines(query); |
286 | 199 } |
287 // Installing this extension requires the "GlobalIntegers" table | 200 |
288 // created by the "GetLastChangeIndex" extension | 201 if (needToRunUpgradeV1toV2) |
289 property = 0; | 202 { |
290 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, | 203 LOG(WARNING) << "Upgrading DB schema from revision 1 to revision 2"; |
291 Orthanc::GlobalProperty_GetLastChangeIndex) || | 204 |
292 property != 1) | 205 std::string query; |
293 { | 206 |
294 LOG(INFO) << "Installing the GetLastChangeIndex extension"; | 207 Orthanc::EmbeddedResources::GetFileResource |
295 | 208 (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_V1_TO_V2); |
296 std::string query; | 209 t.GetDatabaseTransaction().ExecuteMultiLines(query); |
297 Orthanc::EmbeddedResources::GetFileResource | 210 |
298 (query, Orthanc::EmbeddedResources::POSTGRESQL_GET_LAST_CHANGE_INDEX); | 211 // apply all idempotent changes that are in the PrepareIndexV2 |
299 t.GetDatabaseTransaction().ExecuteMultiLines(query); | 212 ApplyPrepareIndex(t, manager); |
300 | 213 } |
301 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1); | |
302 } | |
303 | |
304 if (shouldInstallFastTotalStats2) | |
305 { | |
306 LOG(WARNING) << "PostgreSQL plugin: installing FastTotalStats2 to replace FastTotalSize and FastCountResources"; | |
307 | |
308 std::string query; | |
309 Orthanc::EmbeddedResources::GetFileResource | |
310 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_STATS_2); | |
311 t.GetDatabaseTransaction().ExecuteMultiLines(query); | |
312 } | |
313 | |
314 t.Commit(); | |
315 } | |
316 | |
317 | |
318 { | |
319 // New in release 4.0 to deal with multiple writers | |
320 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); | |
321 | |
322 if (!t.GetDatabaseTransaction().DoesTableExist("ServerProperties")) | |
323 { | |
324 t.GetDatabaseTransaction().ExecuteMultiLines("CREATE TABLE ServerProperties(server VARCHAR(64) NOT NULL, " | |
325 "property INTEGER, value TEXT, PRIMARY KEY(server, property))"); | |
326 } | |
327 | |
328 /** | |
329 * PostgreSQL 9.5: "Adding a column with a default requires | |
330 * updating each row of the table (to store the new column | |
331 * value). However, if no default is specified, PostgreSQL is | |
332 * able to avoid the physical update." => We set no default | |
333 * for performance (older entries will be NULL) | |
334 * https://www.postgresql.org/docs/9.5/ddl-alter.html | |
335 **/ | |
336 if (!db.DoesColumnExist("Metadata", "revision")) | |
337 { | |
338 t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Metadata ADD COLUMN revision INTEGER"); | |
339 } | |
340 | |
341 if (!db.DoesColumnExist("AttachedFiles", "revision")) | |
342 { | |
343 t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER"); | |
344 } | |
345 | |
346 t.Commit(); | |
347 } | |
348 | |
349 | |
350 { | |
351 // New in release 5.0 to deal with labels | |
352 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); | |
353 | |
354 if (!t.GetDatabaseTransaction().DoesTableExist("Labels")) | |
355 { | |
356 t.GetDatabaseTransaction().ExecuteMultiLines( | |
357 "CREATE TABLE Labels(" | |
358 "id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE," | |
359 "label TEXT, PRIMARY KEY(id, label));" | |
360 "CREATE INDEX LabelsIndex1 ON LABELS(id);" | |
361 "CREATE INDEX LabelsIndex2 ON LABELS(label);"); | |
362 } | 214 } |
363 | 215 |
364 t.Commit(); | 216 t.Commit(); |
365 } | 217 } |
366 } | 218 } |