Mercurial > hg > orthanc-databases

comparison Framework/Plugins/IndexBackend.cpp @ 266:cc7af42d4f23

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Store revisions for metadata and attachments in PostgreSQL
author Sebastien Jodogne <s.jodogne@gmail.com>
date Tue, 20 Apr 2021 17:41:44 +0200
parents cd73e34d5411
children 6b18d3fbee82
comparison
equal deleted inserted replaced
265:cd73e34d5411 266:cc7af42d4f23
1035 "SELECT uuid, uncompressedSize, compressionType, compressedSize, uncompressedHash, " 1035 "SELECT uuid, uncompressedSize, compressionType, compressedSize, uncompressedHash, "
1036 "compressedHash, revision FROM AttachedFiles WHERE id=${id} AND fileType=${type}"); 1036 "compressedHash, revision FROM AttachedFiles WHERE id=${id} AND fileType=${type}");
1037 1037
1038 if (ExecuteLookupAttachment(statement, output, id, contentType)) 1038 if (ExecuteLookupAttachment(statement, output, id, contentType))
1039 { 1039 {
1040 revision = statement.ReadInteger64(6); 1040 if (statement.GetResultField(6).GetType() == ValueType_Null)
1041 {
1042 // "NULL" can happen with a database created by PostgreSQL
1043 // plugin <= 3.3 (because of "ALTER TABLE AttachedFiles")
1044 revision = 0;
1045 }
1046 else
1047 {
1048 revision = statement.ReadInteger64(6);
1049 }
1050
1041 return true; 1051 return true;
1042 } 1052 }
1043 else 1053 else
1044 { 1054 {
1045 return false; 1055 return false;
1257 int64_t id, 1267 int64_t id,
1258 int32_t metadataType) 1268 int32_t metadataType)
1259 { 1269 {
1260 std::unique_ptr<DatabaseManager::CachedStatement> statement; 1270 std::unique_ptr<DatabaseManager::CachedStatement> statement;
1261 1271
1262 switch (manager.GetDialect()) 1272 if (HasRevisionsSupport())
1263 { 1273 {
1264 case Dialect_MySQL: 1274 statement.reset(new DatabaseManager::CachedStatement(
1265 case Dialect_PostgreSQL: 1275 STATEMENT_FROM_HERE, manager,
1266 statement.reset(new DatabaseManager::CachedStatement( 1276 "SELECT value, revision FROM Metadata WHERE id=${id} and type=${type}"));
1267 STATEMENT_FROM_HERE, manager, 1277 }
1268 "SELECT value FROM Metadata WHERE id=${id} and type=${type}")); 1278 else
1269 break; 1279 {
1270 1280 statement.reset(new DatabaseManager::CachedStatement(
1271 case Dialect_SQLite: 1281 STATEMENT_FROM_HERE, manager,
1272 statement.reset(new DatabaseManager::CachedStatement( 1282 "SELECT value FROM Metadata WHERE id=${id} and type=${type}"));
1273 STATEMENT_FROM_HERE, manager, 1283 }
1274 "SELECT value, revision FROM Metadata WHERE id=${id} and type=${type}"));
1275 break;
1276
1277 default:
1278 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
1279 }
1280
1281 1284
1282 statement->SetReadOnly(true); 1285 statement->SetReadOnly(true);
1283 statement->SetParameterType("id", ValueType_Integer64); 1286 statement->SetParameterType("id", ValueType_Integer64);
1284 statement->SetParameterType("type", ValueType_Integer64); 1287 statement->SetParameterType("type", ValueType_Integer64);
1285 1288
1295 } 1298 }
1296 else 1299 else
1297 { 1300 {
1298 target = statement->ReadString(0); 1301 target = statement->ReadString(0);
1299 1302
1300 if (manager.GetDialect() == Dialect_SQLite) 1303 if (HasRevisionsSupport())
1301 { 1304 {
1302 revision = statement->ReadInteger64(1); 1305 if (statement->GetResultField(1).GetType() == ValueType_Null)
1306 {
1307 // "NULL" can happen with a database created by PostgreSQL
1308 // plugin <= 3.3 (because of "ALTER TABLE AttachedFiles")
1309 revision = 0;
1310 }
1311 else
1312 {
1313 revision = statement->ReadInteger64(1);
1314 }
1303 } 1315 }
1304 else 1316 else
1305 { 1317 {
1306 revision = 0; // TODO - REVISIONS 1318 revision = 0; // No support for revisions
1307 } 1319 }
1308 1320
1309 return true; 1321 return true;
1310 } 1322 }
1311 } 1323 }
1579 "INSERT INTO DicomIdentifiers VALUES(${id}, ${group}, ${element}, ${value})"); 1591 "INSERT INTO DicomIdentifiers VALUES(${id}, ${group}, ${element}, ${value})");
1580 1592
1581 ExecuteSetTag(statement, id, group, element, value); 1593 ExecuteSetTag(statement, id, group, element, value);
1582 } 1594 }
1583 1595
1596
1597 static void ExecuteSetMetadata(DatabaseManager::CachedStatement& statement,
1598 Dictionary& args,
1599 int64_t id,
1600 int32_t metadataType,
1601 const char* value)
1602 {
1603 statement.SetParameterType("id", ValueType_Integer64);
1604 statement.SetParameterType("type", ValueType_Integer64);
1605 statement.SetParameterType("value", ValueType_Utf8String);
1606
1607 args.SetIntegerValue("id", id);
1608 args.SetIntegerValue("type", metadataType);
1609 args.SetUtf8Value("value", value);
1610
1611 statement.Execute(args);
1612 }
1584 1613
1585 void IndexBackend::SetMetadata(DatabaseManager& manager, 1614 void IndexBackend::SetMetadata(DatabaseManager& manager,
1586 int64_t id, 1615 int64_t id,
1587 int32_t metadataType, 1616 int32_t metadataType,
1588 const char* value, 1617 const char* value,
1589 int64_t revision) 1618 int64_t revision)
1590 { 1619 {
1591 if (manager.GetDialect() == Dialect_SQLite) 1620 if (manager.GetDialect() == Dialect_SQLite)
1592 { 1621 {
1622 assert(HasRevisionsSupport());
1593 DatabaseManager::CachedStatement statement( 1623 DatabaseManager::CachedStatement statement(
1594 STATEMENT_FROM_HERE, manager, 1624 STATEMENT_FROM_HERE, manager,
1595 "INSERT OR REPLACE INTO Metadata VALUES (${id}, ${type}, ${value}, ${revision})"); 1625 "INSERT OR REPLACE INTO Metadata VALUES (${id}, ${type}, ${value}, ${revision})");
1596 1626
1597 statement.SetParameterType("id", ValueType_Integer64); 1627 Dictionary args;
1598 statement.SetParameterType("type", ValueType_Integer64);
1599 statement.SetParameterType("value", ValueType_Utf8String);
1600 statement.SetParameterType("revision", ValueType_Integer64); 1628 statement.SetParameterType("revision", ValueType_Integer64);
1601
1602 Dictionary args;
1603 args.SetIntegerValue("id", id);
1604 args.SetIntegerValue("type", metadataType);
1605 args.SetUtf8Value("value", value);
1606 args.SetIntegerValue("revision", revision); 1629 args.SetIntegerValue("revision", revision);
1607 1630
1608 statement.Execute(args); 1631 ExecuteSetMetadata(statement, args, id, metadataType, value);
1609 } 1632 }
1610 else 1633 else
1611 { 1634 {
1612 // TODO - REVISIONS
1613 { 1635 {
1614 DatabaseManager::CachedStatement statement( 1636 DatabaseManager::CachedStatement statement(
1615 STATEMENT_FROM_HERE, manager, 1637 STATEMENT_FROM_HERE, manager,
1616 "DELETE FROM Metadata WHERE id=${id} AND type=${type}"); 1638 "DELETE FROM Metadata WHERE id=${id} AND type=${type}");
1617 1639
1623 args.SetIntegerValue("type", metadataType); 1645 args.SetIntegerValue("type", metadataType);
1624 1646
1625 statement.Execute(args); 1647 statement.Execute(args);
1626 } 1648 }
1627 1649
1650 if (HasRevisionsSupport())
1651 {
1652 DatabaseManager::CachedStatement statement(
1653 STATEMENT_FROM_HERE, manager,
1654 "INSERT INTO Metadata VALUES (${id}, ${type}, ${value}, ${revision})");
1655
1656 Dictionary args;
1657 statement.SetParameterType("revision", ValueType_Integer64);
1658 args.SetIntegerValue("revision", revision);
1659
1660 ExecuteSetMetadata(statement, args, id, metadataType, value);
1661 }
1662 else
1628 { 1663 {
1629 DatabaseManager::CachedStatement statement( 1664 DatabaseManager::CachedStatement statement(
1630 STATEMENT_FROM_HERE, manager, 1665 STATEMENT_FROM_HERE, manager,
1631 "INSERT INTO Metadata VALUES (${id}, ${type}, ${value})"); 1666 "INSERT INTO Metadata VALUES (${id}, ${type}, ${value})");
1632 1667
1633 statement.SetParameterType("id", ValueType_Integer64);
1634 statement.SetParameterType("type", ValueType_Integer64);
1635 statement.SetParameterType("value", ValueType_Utf8String);
1636
1637 Dictionary args; 1668 Dictionary args;
1638 args.SetIntegerValue("id", id); 1669 ExecuteSetMetadata(statement, args, id, metadataType, value);
1639 args.SetIntegerValue("type", metadataType);
1640 args.SetUtf8Value("value", value);
1641
1642 statement.Execute(args);
1643 } 1670 }
1644 } 1671 }
1645 } 1672 }
1646 1673
1647 1674
2063 2090
2064 2091
2065 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 2092 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
2066 static void ExecuteSetResourcesContentMetadata( 2093 static void ExecuteSetResourcesContentMetadata(
2067 DatabaseManager& manager, 2094 DatabaseManager& manager,
2095 bool hasRevisionsSupport,
2068 uint32_t count, 2096 uint32_t count,
2069 const OrthancPluginResourcesContentMetadata* metadata) 2097 const OrthancPluginResourcesContentMetadata* metadata)
2070 { 2098 {
2071 std::string sqlRemove; // To overwrite 2099 std::string sqlRemove; // To overwrite
2072 std::string sqlInsert; 2100 std::string sqlInsert;
2077 std::string name = "m" + boost::lexical_cast<std::string>(i); 2105 std::string name = "m" + boost::lexical_cast<std::string>(i);
2078 2106
2079 args.SetUtf8Value(name, metadata[i].value); 2107 args.SetUtf8Value(name, metadata[i].value);
2080 2108
2081 std::string revisionSuffix; 2109 std::string revisionSuffix;
2082 if (manager.GetDialect() == Dialect_SQLite) 2110 if (hasRevisionsSupport)
2083 { 2111 {
2084 revisionSuffix = ", 0"; // TODO - REVISIONS 2112 revisionSuffix = ", 0";
2085 } 2113 }
2086 2114
2087 std::string insert = ("(" + boost::lexical_cast<std::string>(metadata[i].resource) + ", " + 2115 std::string insert = ("(" + boost::lexical_cast<std::string>(metadata[i].resource) + ", " +
2088 boost::lexical_cast<std::string>(metadata[i].metadata) + ", " + 2116 boost::lexical_cast<std::string>(metadata[i].metadata) + ", " +
2089 "${" + name + "}" + revisionSuffix + ")"); 2117 "${" + name + "}" + revisionSuffix + ")");
2155 countIdentifierTags, identifierTags); 2183 countIdentifierTags, identifierTags);
2156 2184
2157 ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t", 2185 ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t",
2158 countMainDicomTags, mainDicomTags); 2186 countMainDicomTags, mainDicomTags);
2159 2187
2160 ExecuteSetResourcesContentMetadata(manager, countMetadata, metadata); 2188 ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata);
2161 } 2189 }
2162 #endif 2190 #endif
2163 2191
2164 2192
2165 // New primitive since Orthanc 1.5.2 2193 // New primitive since Orthanc 1.5.2