Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/PostgreSQLIndex.cpp @ 437:d979f25e60cf pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 }