Mercurial > hg > orthanc
annotate OrthancServer/Sources/Database/InstallRevisionAndCustomData.sql @ 5085:79f98ee4f04b attach-custom-data
cosmetic
author | Alain Mazy <am@osimis.io> |
---|---|
date | Thu, 15 Sep 2022 18:13:17 +0200 |
parents | 9770d537880d |
children |
rev | line source |
---|---|
5080
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
1 -- Orthanc - A Lightweight, RESTful DICOM Store |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
2 -- Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
3 -- Department, University Hospital of Liege, Belgium |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
4 -- Copyright (C) 2017-2022 Osimis S.A., Belgium |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
5 -- Copyright (C) 2021-2022 Sebastien Jodogne, ICTEAM UCLouvain, Belgium |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
6 -- |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
7 -- This program is free software: you can redistribute it and/or |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
8 -- modify it under the terms of the GNU General Public License as |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
9 -- published by the Free Software Foundation, either version 3 of the |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
10 -- License, or (at your option) any later version. |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
11 -- |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
12 -- This program is distributed in the hope that it will be useful, but |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
13 -- WITHOUT ANY WARRANTY; without even the implied warranty of |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
14 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
15 -- General Public License for more details. |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
16 -- |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
17 -- You should have received a copy of the GNU General Public License |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
18 -- along with this program. If not, see <http://www.gnu.org/licenses/>. |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
19 |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
20 |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
21 -- |
5085 | 22 -- This SQLite script installs revision and customData without changing the Orthanc database version |
5080
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
23 -- |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
24 |
5084
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
25 -- Add new columns for revision |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
26 ALTER TABLE Metadata ADD COLUMN revision INTEGER; |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
27 ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER; |
5080
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
28 |
5084
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
29 -- Add new column for customData |
5080
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
30 ALTER TABLE AttachedFiles ADD COLUMN customData TEXT; |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
31 |
5084
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
32 |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
33 -- add another AttachedFileDeleted trigger |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
34 -- We want to keep backward compatibility and avoid changing the database version number (which would force |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
35 -- users to upgrade the DB). By keeping backward compatibility, we mean "allow a user to run a previous Orthanc |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
36 -- version after it has run this update script". |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
37 -- We must keep the signature of the initial trigger (it is impossible to have 2 triggers on the same event). |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
38 -- We tried adding a trigger on "BEFORE DELETE" but then it is being called when running the previous Orthanc |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
39 -- which makes it fail. |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
40 -- But, we need the customData in the C++ function that is called when a AttachedFiles is deleted. |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
41 -- The trick is then to save the customData in a DeletedFiles table. |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
42 -- The SignalFileDeleted C++ function will then get the customData from this table and delete the entry. |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
43 -- Drawback: if you downgrade Orthanc, the DeletedFiles table will remain and will be populated by the trigger |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
44 -- but not consumed by the C++ function -> we consider this is an acceptable drawback for a few people compared |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
45 -- to the burden of upgrading the DB. |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
46 |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
47 CREATE TABLE DeletedFiles( |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
48 uuid TEXT NOT NULL, -- 0 |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
49 customData TEXT -- 1 |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
50 ); |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
51 |
5080
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
52 DROP TRIGGER AttachedFileDeleted; |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
53 |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
54 CREATE TRIGGER AttachedFileDeleted |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
55 AFTER DELETE ON AttachedFiles |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
56 BEGIN |
5084
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
57 INSERT INTO DeletedFiles VALUES(old.uuid, old.customData); |
5080
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
58 SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize, |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
59 old.compressionType, old.compressedSize, |
5084
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
60 old.uncompressedMD5, old.compressedMD5 |
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
61 ); |
5080
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
62 END; |
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
63 |
5084
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
64 -- Record that this upgrade has been performed |
5080
d7274e43ea7c
allow plugins to store a customData in the Attachments table to e.g. store custom paths without requiring an external DB
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
65 |
5084
9770d537880d
added support for revision in SQLite + avoid upgrading DB version
Alain Mazy <am@osimis.io>
parents:
5080
diff
changeset
|
66 INSERT INTO GlobalProperties VALUES (7, 1); -- GlobalProperty_SQLiteHasCustomDataAndRevision |