Mercurial > hg > orthanc-databases
annotate NOTES @ 520:e1d3da372805 OrthancMySQL-5.2
mainline -> MySQL 5.2
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Thu, 06 Jun 2024 15:12:01 +0200 |
| parents | bf4b9c7cf338 |
| children |
| rev | line source |
|---|---|
| 431 | 1 Resources: |
| 2 ********* | |
| 3 - PG transaction modes explained: https://www.postgresql.org/files/developer/concurrency.pdf | |
|
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
4 - Isolation level explained (PG + MySQL): https://amirsoleimani.medium.com/understanding-database-isolation-level-via-examples-mysql-and-postgres-a86b5502d404 |
|
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
5 - Message queuing in PG: https://www.crunchydata.com/blog/message-queuing-using-native-postgresql |
|
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
6 |
| 431 | 7 |
|
432
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
8 Create and delete instances Internals: |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
9 ************************************* |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
10 |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
11 isNewInstance = CreateInstance(...) |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
12 |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
13 if (!isNewInstance && overwriteInstances) |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
14 DeleteResource(instance) |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
15 -> ClearDeletedFiles(manager); |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
16 DELETE FROM DeletedFiles ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction -> it is empty when taking a snapshot of the DB in READ COMMITTED mode!!! |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
17 ClearDeletedResources(manager); |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
18 DELETE FROM DeletedResources ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction !!! |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
19 |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
20 DELETE FROM RemainingAncestor ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction !!! |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
21 DELETE FROM Resources WHERE internalId=.. |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
22 -> cascades delete the MainDicomTags, the Metadata and the AttachedFiles |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
23 -> this triggers AttachedFileDeletedFunc |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
24 INSERT INTO DeletedFiles VALUES |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
25 (old.uuid, old.filetype, old.compressedSize, |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
26 old.uncompressedSize, old.compressionType, |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
27 old.uncompressedHash, old.compressedHash); |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
28 RETURN NULL; |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
29 -> this triggers a SQL trigger: ResourceDeletedFunc |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
30 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
31 IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
32 -- Signal that the deleted resource has a remaining parent |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
33 -- (a parent that must not be deleted but whose LastUpdate must be updated) |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
34 INSERT INTO RemainingAncestor |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
35 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
36 ELSE |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
37 -- Delete a parent resource when its unique child is deleted |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
38 DELETE FROM Resources WHERE internalId = old.parentId; |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
39 END IF; |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
40 |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
41 SELECT * FROM RemainingAncestor |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
42 -> SignalRemainingAncestor() // There is at most 1 remaining ancestor |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
43 -> ServerIndex::TransactionContext::SignalRemainingAncestor() |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
44 -> stores remainingType and remainingPublicId (this is used in StatelessDatabaseOperations::DeleteResource to build the Rest Response of /delete |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
45 and to update the LastUpdate of all parent (only when deleted from /delete)) |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
46 |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
47 SignalDeletedFiles(output, manager); |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
48 SELECT * FROM DeletedFiles |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
49 -> SignalDeletedAttachment() |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
50 -> ServerIndex::TransactionContext::SignalAttachmentDeleted() |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
51 -> pendingFilesToRemove_.push_back(FileToRemove(info)) (files are deleted in CommitFilesToRemove in the ServerIndex::TransactionContext::Commit) |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
52 |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
53 SignalDeletedResources(output, manager); |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
54 SELECT resourceType, publicId FROM DeletedResources |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
55 -> SignalDeletedResource() |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
56 -> Emit DeletedResource event (lua) |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
57 |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
58 |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
59 if (!CreateInstance(...)) |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
60 Error: "No new instance while overwriting; this should not happen" |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
61 |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
62 if isNewInstance -> LogChange |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
63 if isNewSeries -> LogChange |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
64 .... |
| 431 | 65 |
| 66 Sample SQL code that you can execute in DBeaver to test new functions/procedures: | |
| 67 | |
| 68 CreateInstance | |
| 69 ************************************************************************ | |
| 70 | |
| 444 | 71 CREATE OR REPLACE FUNCTION CreateInstance( |
| 72 IN patient_public_id TEXT, | |
| 73 IN study_public_id TEXT, | |
| 74 IN series_public_id TEXT, | |
| 75 IN instance_public_id TEXT, | |
| 76 OUT is_new_patient BIGINT, | |
| 77 OUT is_new_study BIGINT, | |
| 78 OUT is_new_series BIGINT, | |
| 79 OUT is_new_instance BIGINT, | |
| 80 OUT patient_internal_id BIGINT, | |
| 81 OUT study_internal_id BIGINT, | |
| 82 OUT series_internal_id BIGINT, | |
| 83 OUT instance_internal_id BIGINT) AS $body$ | |
| 84 | |
| 85 BEGIN | |
| 86 is_new_patient := 1; | |
| 87 is_new_study := 1; | |
| 88 is_new_series := 1; | |
| 89 is_new_instance := 1; | |
| 90 | |
| 91 | |
| 92 BEGIN | |
| 93 INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL); | |
| 94 EXCEPTION | |
| 95 WHEN unique_violation THEN | |
| 96 is_new_patient := 0; | |
| 97 END; | |
| 98 SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id AND resourcetype = 0 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction | |
| 431 | 99 |
| 100 BEGIN | |
| 444 | 101 INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id); |
| 102 EXCEPTION | |
| 103 WHEN unique_violation THEN | |
| 104 is_new_study := 0; | |
| 105 END; | |
| 106 SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id AND resourcetype = 1 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction | |
| 431 | 107 |
| 108 BEGIN | |
| 444 | 109 INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id); |
| 110 EXCEPTION | |
| 111 WHEN unique_violation THEN | |
| 112 is_new_series := 0; | |
| 113 END; | |
| 114 SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id AND resourcetype = 2 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction | |
| 431 | 115 |
| 116 BEGIN | |
| 444 | 117 INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id); |
| 118 EXCEPTION | |
| 119 WHEN unique_violation THEN | |
| 120 is_new_instance := 0; | |
| 121 END; | |
| 122 SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id AND resourcetype = 3 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction | |
| 123 | |
| 124 IF is_new_instance > 0 THEN | |
| 125 -- Move the patient to the end of the recycling order. | |
| 126 PERFORM PatientAddedOrUpdated(patient_internal_id, 1); | |
| 127 END IF; | |
| 431 | 128 END; |
| 444 | 129 |
| 130 $body$ LANGUAGE plpgsql; | |
| 131 | |
| 431 | 132 |
| 133 DO $$ | |
| 134 DECLARE | |
| 135 result record; | |
| 136 begin | |
| 137 delete from "resources"; | |
| 138 | |
| 139 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance1'); | |
| 140 | |
| 444 | 141 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patient_internal_id, result.is_new_patient; |
| 142 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.study_internal_id, result.is_new_study; | |
| 143 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.series_internal_id, result.is_new_series; | |
| 144 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instance_internal_id, result.is_new_instance; | |
| 431 | 145 RAISE NOTICE '--------------'; |
| 146 | |
| 147 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); | |
| 148 | |
| 444 | 149 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patient_internal_id, result.is_new_patient; |
| 150 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.study_internal_id, result.is_new_study; | |
| 151 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.series_internal_id, result.is_new_series; | |
| 152 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instance_internal_id, result.is_new_instance; | |
| 431 | 153 RAISE NOTICE '--------------'; |
| 154 | |
| 155 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); | |
| 156 | |
| 444 | 157 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patient_internal_id, result.is_new_patient; |
| 158 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.study_internal_id, result.is_new_study; | |
| 159 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.series_internal_id, result.is_new_series; | |
| 160 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instance_internal_id, result.is_new_instance; | |
| 431 | 161 RAISE NOTICE '--------------'; |
| 162 | |
| 163 END $$; | |
| 164 | |
| 444 | 165 |
| 431 | 166 -- \set patient_key 'patient_key' |
| 167 -- SELECT CreateInstance('patient', 'study', 'series', 'instance', patient_key) ; | |
| 168 | |
| 169 -- drop function CreateInstance | |
| 170 -- select * from "resources"; | |
| 171 -- delete from "resources"; | |
| 172 -- INSERT INTO "resources" VALUES (DEFAULT, 0, 'patient', NULL) | |
| 173 | |
| 174 | |
| 175 | |
| 176 ************************************************************************ | |
| 177 | |
| 443 | 178 In debug, no verbose logs, 10 connections |
|
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
179 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 43.957 s |
|
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
180 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 15.744 s |
|
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
181 test_concurrent_anonymize_same_study deletion took: 18.8 s |
|
435
326f8304daa1
new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents:
434
diff
changeset
|
182 |
|
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
183 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s |
| 446 | 184 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 9.514 s |
|
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
185 |
| 443 | 186 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 23.016 s |
| 187 Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 7.129 s | |
|
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
188 |
| 444 | 189 Orthanc mainline + PG mainline (read-committed mode) : test_upload_multiple_studies_from_multiple_threads with 10 workers and 25 files and repeat 3x: 6.454 s |
| 190 | |
|
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
191 With Docker with 10 connections SQL: |
| 446 | 192 osimis/orthanc:24.1.2 : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 18.956 s FAIL !!! |
| 193 test_concurrent_anonymize_same_study deletion took: NA | |
| 194 osimis/orthanc:current: test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 6.867 s | |
| 195 test_concurrent_anonymize_same_study deletion took: 9.095 s | |
|
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
196 |
| 446 | 197 osimis/orthanc:24.1.2 : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 9.822 s |
| 198 osimis/orthanc:current: test_concurrent_uploads_same_study with 20 workers and 1x repeat: 16.027 s up to 38s ! (slower but the test is not representative of a real life scenario !!!!!) | |
|
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
199 |
| 446 | 200 osimis/orthanc:24.1.2 : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 12.966 s |
| 201 osimis/orthanc:current: test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 4.196 s | |
| 202 | |
| 203 osimis/orthanc:24.1.2 : test_upload_multiple_studies_from_multiple_threads with 10 workers and 25 files and repeat 3x: 8.957 s | |
| 204 osimis/orthanc:current: test_upload_multiple_studies_from_multiple_threads with 10 workers and 25 files and repeat 3x: 2.671 s | |
|
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
205 |
| 443 | 206 Testing the connecions (note: Orthanc and PG server running on the same server) |
| 207 10 connections : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 15.744 s | |
| 208 1 connection : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 21.341 s | |
| 209 10 connections : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 6.57 s | |
| 210 1 connection : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 10.223 s | |
| 211 10 connections : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 7.129 s | |
| 212 1 connection : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 11.172 s | |
| 213 | |
| 431 | 214 |
| 502 | 215 LARGE QUERIES |
| 216 ------------- | |
| 453 | 217 |
| 502 | 218 new extended-api-v1/tools/find requirements (note: extended-api-v1 is a code name:-) ): |
| 219 - be able to list child-resources like in /studies/.../instances?expand | |
| 220 => we need a "ParentStudy" in the query. Note: a single "Parent" field is not enough since we sometimes want to specify a "Parent" | |
| 221 that is 2 or 3 levels higher (e.g /patients/../instances) | |
| 222 e.g: { | |
| 223 "Level": "Instance", | |
| 224 "Query": { | |
| 225 "ParentStudy": "orthanc-study-id", | |
| 226 }, | |
| 227 } | |
| 228 - be able to search on labels like in the current tools/find implementation | |
| 229 => we need a "ParentStudy" in the query. Note: a single "Parent" field is not enough since we sometimes want to specify a "Parent" | |
| 230 that is 2 or 3 levels higher (e.g /patients/../instances) | |
| 231 e.g: { | |
| 232 "Level": "Study", | |
| 233 "Query": { | |
| 234 "Labels": ["hello"], | |
| 235 "LabelsConstraint" : "All", | |
| 236 }, | |
| 237 } | |
| 238 - be able to select what are the expected fields in the response | |
| 239 => we need a "ExpandContent" field in the query. | |
| 240 If there are no "ExpandContent", we simply return a list of IDs | |
| 241 e.g: { | |
| 242 "Level": "Series", | |
| 243 "Query": { | |
| 244 "ParentStudy": "orthanc-study-id", | |
| 245 }, | |
| 246 "ExpandContent": [ | |
| 247 "Children", // the list of orthanc-ids of the child of the resources (equivalent to e.g /series/../instances) | |
| 248 "Parent", // the resource parent id (e.g. "ParentStudy" for a series) | |
| 249 "Labels", // the resources labels (equivalent to e.g /series/../labels) | |
| 250 "Metadata", // the resources metadata (equivalent to e.g. /series/../metadata?expand) | |
| 251 "MainDicomTags", // the resources MainDicomTags | |
| 252 "FileInfo", // "FileSize" + "FileUuid", applicable only to instances | |
| 253 // TODO: what about "IsStable", "Status", "LastUpdate", "IndexInSeries", "ExpectedNumberOfInstances" ??? | |
| 254 // TODO: right now, we have a "RequestedTags" option that can list the DICOM TAGS and tools/find can fetch the tags from disk if they are not in the ExtraMainDicomTags | |
| 255 ] | |
| 256 } | |
| 257 - be able to filter on DICOM Tags (and compose with a Parent orthanc id) | |
| 258 e.g: get all US series from this study | |
| 259 { | |
| 260 "Level": "Series", | |
| 261 "Query": { | |
| 262 "ParentStudy": "orthanc-study-id", | |
| 263 "DicomTags": { | |
| 264 "Modality": "US" | |
| 265 } | |
| 266 } | |
| 267 } | |
| 268 - be able to filter on a metadata (and compose with a Parent orthanc id) | |
| 269 e.g: to get all PDF instances of a study | |
| 270 { | |
| 271 "Level": "Instance", | |
| 272 "Query": { | |
| 273 "ParentStudy": "orthanc-study-id", | |
| 274 "Metadata": { | |
| 275 "SopClassUid": "1.2.840.10008.5.1.4.1.1.104.1" | |
| 276 } | |
| 277 } | |
| 278 } | |
| 279 - be able to order results on one or more DicomTags or Metadata + limit results + offset (for pagination) | |
| 280 e.g: to get page 5 of all studies from this month | |
| 281 { | |
| 282 "Level": "Study", | |
| 283 "Query": { | |
| 284 "DicomTags": { | |
| 285 "StudyDate": "20240101-20240131" | |
| 286 }, | |
| 287 "OrderBy": [ | |
| 288 { | |
| 289 "DicomTag": "StudyDate", | |
| 290 "Order": "Desc" | |
| 291 }, | |
| 292 { | |
| 293 "Metadata": "LastUpdate", | |
| 294 "Order": "Desc" | |
| 295 } | |
| 296 ], | |
| 297 "Limit": 1000, | |
| 298 "Since": 5000 | |
| 299 } | |
| 300 } | |
| 301 | |
| 302 | |
| 303 current queries - single level: | |
| 304 ------------------------------ | |
| 305 | |
| 306 Search: | |
| 307 sql = ("SELECT publicId, internalId " | |
| 308 "FROM Resources " | |
| 309 "WHERE resourceType = " + formatter.FormatResourceType(queryLevel) | |
| 310 + " "); | |
| 311 | |
| 312 if (dicomIdentifiersComparisons.size() > 0) | |
| 313 sql += (" AND internalId IN (SELECT id FROM DicomIdentifiers WHERE " + *it + ") "); | |
| 314 | |
| 315 if (mainDicomTagsComparisons.size() > 0) | |
| 316 sql += (" AND internalId IN (SELECT id FROM MainDicomTags WHERE " + *it + ") "); | |
| 317 | |
| 318 if (!labels.empty()) | |
| 319 sql += (" AND internalId " + inOrNotIn + " (SELECT id" | |
| 320 " FROM (SELECT id, COUNT(1) AS labelsCount " | |
| 321 "FROM Labels " | |
| 322 "WHERE label IN (" + Join(formattedLabels, "", ", ") + ") GROUP BY id" | |
| 323 ") AS temp " | |
| 324 " WHERE labelsCount " + condition + ")"); | |
| 325 if (limit != 0) | |
| 326 sql += " LIMIT " + boost::lexical_cast<std::string>(limit); | |
| 327 | |
| 328 | |
| 329 Patients: | |
| 330 "SELECT patients_studies.patients_public_id, MIN(instances.publicId) AS instances_public_id " | |
| 331 "FROM (SELECT patients.publicId AS patients_public_id, MIN(studies.internalId) AS studies_internal_id " | |
| 332 "FROM (" + sql + | |
| 333 ") AS patients " | |
| 334 "INNER JOIN Resources studies ON studies.parentId = patients.internalId " | |
| 335 "GROUP BY patients.publicId " | |
| 336 ") AS patients_studies " | |
| 337 "INNER JOIN Resources series ON series.parentId = patients_studies.studies_internal_id " | |
| 338 "INNER JOIN Resources instances ON instances.parentId = series.internalId " | |
| 339 "GROUP BY patients_studies.patients_public_id" | |
| 340 | |
| 341 Studies: | |
| 342 "SELECT studies_series.studies_public_id, MIN(instances.publicId) AS instances_public_id " | |
| 343 "FROM (SELECT studies.publicId AS studies_public_id, MIN(series.internalId) AS series_internal_id " | |
| 344 "FROM (" + sql + | |
| 345 ") AS studies " | |
| 346 "INNER JOIN Resources series ON series.parentId = studies.internalId " | |
| 347 "GROUP BY studies.publicId " | |
| 348 ") AS studies_series " | |
| 349 "INNER JOIN Resources instances ON instances.parentId = studies_series.series_internal_id " | |
| 350 "GROUP BY studies_series.studies_public_id" | |
| 443 | 351 |
| 502 | 352 Series: |
| 353 "SELECT series.publicId AS series_public_id, MIN(instances.publicId) AS instances_public_id " | |
| 354 "FROM (" + sql + | |
| 355 ") AS series " | |
| 356 "INNER JOIN Resources instances ON instances.parentId = series.internalId " | |
| 357 "GROUP BY series.publicId " | |
| 358 | |
| 359 Instances: | |
| 360 "SELECT instances.publicId, instances.publicId FROM (" + sql + ") instances" | |
| 361 | |
| 362 | |
| 363 current queries - multi level: | |
| 364 ------------------------------ | |
| 365 Search: ISqlLookupFormatter::Apply | |
| 366 | |
| 367 | |
| 368 | |
| 369 New queries: | |
| 370 ----------- | |
| 371 | |
| 372 - Get all series ids where PatientID (16, 32)="ID-DYL-WAL-00000008" | |
| 373 StudyDescription (8, 4144) %LIKE% "000000" | |
| 374 order by StudyDate(8, 32) DESC | |
| 375 | |
| 376 Current: curl http://localhost:8044/tools/find -d '{"Level": "Series", "Query": {"StudyDescription": "*000000*", "PatientID": "ID-DYL-WAL-00000008"}}' | |
| 377 SELECT series.publicId, MIN(instances.publicId) | |
| 378 FROM (SELECT series.publicId, series.internalId | |
| 379 FROM Resources AS series | |
| 380 INNER JOIN Resources studies ON studies.internalId=series.parentId | |
| 381 INNER JOIN DicomIdentifiers t0 ON t0.id = studies.internalId AND t0.tagGroup = 16 AND t0.tagElement = 32 -- Patient tags are copied at study level ! | |
| 382 INNER JOIN DicomIdentifiers t1 ON t1.id = studies.internalId AND t1.tagGroup = 8 AND t1.tagElement = 4144 | |
| 383 WHERE series.resourceType = 2 AND t0.value = 'ID-DYL-WAL-00000008' AND t1.value like '%000000%' ESCAPE '\' LIMIT 101) series | |
| 384 INNER JOIN Resources instances ON instances.parentId = series.internalId GROUP BY series.publicId | |
| 385 | |
| 386 | |
| 387 With order by, we must not retrieve the instance anymore: | |
| 388 SELECT series.publicId, series.internalId | |
| 389 FROM Resources AS series | |
| 390 INNER JOIN Resources studies ON studies.internalId=series.parentId | |
| 391 INNER JOIN DicomIdentifiers t0 ON t0.id = studies.internalId AND t0.tagGroup = 16 AND t0.tagElement = 32 -- Patient tags are copied at study level ! | |
| 392 INNER JOIN DicomIdentifiers t1 ON t1.id = studies.internalId AND t1.tagGroup = 8 AND t1.tagElement = 4144 | |
| 393 INNER JOIN DicomIdentifiers t2 ON t2.id = studies.internalId AND t2.tagGroup = 8 AND t2.tagElement = 32 | |
| 394 WHERE series.resourceType = 2 AND t0.value = 'ID-DYL-WAL-00000008' AND t1.value like '%000000%' ESCAPE '\' | |
| 395 ORDER BY t2 DESC | |
| 396 LIMIT 101 | |
| 397 | |
| 398 Combine multiple search criteria: | |
| 399 Search studies with a label constraints + a Metadata filter + a DICOM Tag filter + a ParentPatient filter | |
| 400 | |
| 401 SELECT studies.publicId, studies.internalId | |
| 402 FROM Resources AS studies | |
| 403 INNER JOIN | |
| 404 | |
| 405 WHERE resourceType = 1 AND internalId IN (SELECT id FROM DicomIdentifiers WHERE tagGroup = 16 AND tagElement = 32 AND value = $1) AND internalId IN (SELECT id FROM DicomIdentifiers WHERE tagGroup = 8 AND tagElement = 32 AND value >= $2) AND internalId IN (SELECT id FROM DicomIdentifiers WHERE tagGroup = 8 AND tagElement = 4144 AND value LIKE $3 ESCAPE '\') LIMIT 101) AS studies INNER JOIN Resources series ON series.parentId = studies.internalId GROUP BY studies.publicId ) AS studies_series INNER JOIN Resources instances ON instances.parentId = studies_series.series_internal_id GROUP BY studies_series.studies_public_id | |
| 406 | |
| 407 | |
| 408 -- current search at study level (single level -> we use IN instead of INNER JOIN) | |
| 409 SELECT publicId, internalId | |
| 410 FROM Resources | |
| 411 WHERE resourceType = 1 | |
| 412 AND internalId IN (SELECT id | |
| 413 FROM DicomIdentifiers WHERE tagGroup = 16 AND tagElement = 32 AND value = 'ID-DYL-WAL-00000008') | |
| 414 AND internalId IN (SELECT id | |
| 415 FROM DicomIdentifiers WHERE tagGroup = 8 AND tagElement = 32 AND value >= '19700101') | |
| 416 AND internalId IN (SELECT id | |
| 417 FROM DicomIdentifiers WHERE tagGroup = 8 AND tagElement = 4144 AND value LIKE '%000000%' ESCAPE '\') | |
| 418 AND internalId IN (SELECT id | |
| 419 FROM (SELECT id, COUNT(1) AS labelsCount FROM Labels WHERE label IN ('Label2', 'Label3') GROUP BY id) AS temp | |
| 420 WHERE labelsCount >= 1) | |
| 421 AND internalId IN (SELECT id | |
| 422 FROM Metadata WHERE type=7 AND value >= '20240415T120000') | |
| 423 LIMIT 101 | |
| 424 | |
| 425 | |
| 426 -- same query with INNER JOIN to compare performance | |
| 427 SELECT studies.publicId, studies.internalId | |
| 428 FROM Resources AS studies | |
| 429 INNER JOIN DicomIdentifiers t0 ON t0.id = studies.internalId AND t0.tagGroup = 16 AND t0.tagElement = 32 AND t0.value = 'ID-DYL-WAL-00000008' -- Patient tags are copied at study level ! | |
| 430 INNER JOIN DicomIdentifiers t1 ON t1.id = studies.internalId AND t1.tagGroup = 8 AND t1.tagElement = 4144 AND t1.value like '%000000%' ESCAPE '\' | |
| 431 INNER JOIN DicomIdentifiers t2 ON t2.id = studies.internalId AND t2.tagGroup = 8 AND t2.tagElement = 32 AND t2.value >= '19700101' | |
| 432 INNER JOIN Metadata m ON m.id = studies.internalId AND m.type = 7 AND m.value >= '20240415T120000' | |
| 433 WHERE studies.resourceType = 1 | |
| 434 -- AND studies.internalId IN (SELECT id FROM (SELECT id, COUNT(1) AS labelsCount FROM labels WHERE label IN ('Label2', 'Label6') GROUP BY id) AS temp WHERE labelsCount = 2) -- All labels | |
| 435 -- AND studies.internalId IN (SELECT id FROM (SELECT id, COUNT(1) AS labelsCount FROM labels WHERE label IN ('Label2', 'Label6') GROUP BY id) AS temp WHERE labelsCount >= 1) -- Any labels | |
| 436 AND studies.internalId NOT IN (SELECT id FROM (SELECT id, COUNT(1) AS labelsCount FROM labels WHERE label IN ('Label2', 'Label6') GROUP BY id) AS temp WHERE labelsCount >= 1) -- None of labels | |
| 437 ORDER BY t2.value DESC, m.value ASC | |
| 438 LIMIT 100 OFFSET 0 | |
| 439 | |
| 440 | |
| 441 We may store this in a temporary table that we can reuse e.g to retrieve, the labels, the metadata, the MainDicomTags ... | |
| 442 CREATE TEMPORARY TABLE FilteredResourcesIds AS | |
| 443 SELECT series.publicId, series.internalId | |
| 444 FROM Resources AS series | |
| 445 INNER JOIN Resources studies ON studies.internalId=series.parentId | |
| 446 INNER JOIN DicomIdentifiers t0 ON t0.id = studies.internalId AND t0.tagGroup = 16 AND t0.tagElement = 32 -- Patient tags are copied at study level ! | |
| 447 INNER JOIN DicomIdentifiers t1 ON t1.id = studies.internalId AND t1.tagGroup = 8 AND t1.tagElement = 4144 | |
| 448 INNER JOIN DicomIdentifiers t2 ON t2.id = studies.internalId AND t2.tagGroup = 8 AND t2.tagElement = 32 | |
| 449 WHERE series.resourceType = 2 AND t0.value = 'ID-DYL-WAL-00000008' AND t1.value like '%000000%' ESCAPE '\' | |
| 450 ORDER BY t2 DESC | |
| 451 LIMIT 101; | |
| 452 | |
| 453 Note: we can probably only retrieve the internalId as the key -> since it is an integer, it is more efficient than a uuid to populate the responses | |
| 454 Retrieve all MainDicomTags at the series level | |
| 455 SELECT internalId, publicId, tagGroup, tagElement, value FROM MainDicomTags AS tags | |
| 456 INNER JOIN FilteredResourcesIds ON tags.id = FilteredResourcesIds.internalId; | |
| 457 | |
| 458 Retrieve all MainDicomTags at the study level too but attach them to the series ids | |
| 459 SELECT series.internalId, series.publicId, tagGroup, tagElement, value | |
| 460 FROM Resources as series | |
| 461 INNER JOIN Resources studies ON studies.internalId=series.parentId | |
| 462 INNER JOIN FilteredResourcesIds filtered ON filtered.internalId = series.internalId | |
| 463 INNER JOIN MainDicomTags tags ON tags.id=studies.internalId | |
| 464 | |
| 465 Retrieve all Metadata from all the series | |
| 466 SELECT metadata.id, series.publicId, metadata.type, metadata.value, metadata.revision | |
| 467 FROM Metadata | |
| 468 INNER JOIN FilteredResourcesIds filtered ON filtered.internalId = Metadata.id | |
| 469 | |
| 470 Retrieve all Labels from all the series (not tested yet) | |
| 471 SELECT labels.id, series.publicId, label | |
| 472 FROM Labels | |
| 473 INNER JOIN FilteredResourcesIds filtered ON filtered.internalId = Labels.id | |
| 474 | |
| 475 Retrieve all parents from all the series | |
| 476 SELECT filtered.internalId, filtered.publicId, parentLevel.internalId as parentInternalId, parentLevel.publicId as parentPublicId | |
| 477 FROM Resources as currentLevel | |
| 478 INNER JOIN FilteredResourcesIds filtered ON filtered.internalId = currentLevel.internalId | |
| 479 INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId | |
| 480 | |
| 481 Retrieve all children from all the series | |
| 482 SELECT filtered.internalId, filtered.publicId, childLevel.internalId as childInternalId, childLevel.publicId as childPublicId | |
| 483 FROM Resources as currentLevel | |
| 484 INNER JOIN FilteredResourcesIds filtered ON filtered.internalId = currentLevel.internalId | |
| 485 INNER JOIN Resources childLevel ON childLevel.parentId = currentLevel.internalId | |
| 486 | |
| 487 Retrieve one instanceId by series | |
| 488 SELECT filtered.internalId, filtered.publicId, MIN(c0.publicId) as instancePublicId | |
| 489 FROM Resources as currentLevel | |
| 490 INNER JOIN FilteredResourcesIds filtered ON filtered.internalId = currentLevel.internalId | |
| 491 INNER JOIN Resources c0 ON c0.parentId = currentLevel.internalId | |
| 492 GROUP BY filtered.internalId, filtered.publicId | |
| 493 | |
| 494 TODO Retrieve all attachments from all the series |