Mercurial > hg > orthanc

comparison OrthancServer/SQLiteDatabaseWrapper.cpp @ 3031:18a2d196414b db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
simplification
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 19 Dec 2018 13:08:04 +0100
parents 25afa7b8cb51
children 5da6d1063d8f
comparison
equal deleted inserted replaced
3030:25afa7b8cb51 3031:18a2d196414b
1226 throw OrthancException(ErrorCode_InternalError); 1226 throw OrthancException(ErrorCode_InternalError);
1227 } 1227 }
1228 } 1228 }
1229 1229
1230 1230
1231 static void FormatJoin(std::string& target,
1232 const DatabaseConstraint& constraint,
1233 size_t index)
1234 {
1235 std::string tag = "t" + boost::lexical_cast<std::string>(index);
1236
1237 if (constraint.IsMandatory())
1238 {
1239 target = " INNER JOIN ";
1240 }
1241 else
1242 {
1243 target = " LEFT JOIN ";
1244 }
1245
1246 if (constraint.IsIdentifier())
1247 {
1248 target += "DicomIdentifiers ";
1249 }
1250 else
1251 {
1252 target += "MainDicomTags ";
1253 }
1254
1255 target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) +
1256 ".internalId AND " + tag + ".tagGroup = " +
1257 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) +
1258 " AND " + tag + ".tagElement = " +
1259 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
1260 }
1261
1262
1263 static bool FormatComparison(std::string& target, 1231 static bool FormatComparison(std::string& target,
1264 const DatabaseConstraint& constraint, 1232 const DatabaseConstraint& constraint,
1265 size_t index, 1233 size_t index,
1266 std::vector<std::string>& parameters) 1234 std::vector<std::string>& parameters)
1267 { 1235 {
1342 } 1310 }
1343 1311
1344 case ConstraintType_Wildcard: 1312 case ConstraintType_Wildcard:
1345 { 1313 {
1346 const std::string value = constraint.GetSingleValue(); 1314 const std::string value = constraint.GetSingleValue();
1347 std::string escaped; 1315
1348 escaped.reserve(value.size()); 1316 if (value == "*")
1349
1350 for (size_t i = 0; i < value.size(); i++)
1351 { 1317 {
1352 if (value[i] == '*') 1318 if (!constraint.IsMandatory())
1353 { 1319 {
1354 escaped += "%"; 1320 // Universal constraint on an optional tag, ignore it
1321 return false;
1355 } 1322 }
1356 else if (value[i] == '?') 1323 }
1324 else
1325 {
1326 std::string escaped;
1327 escaped.reserve(value.size());
1328
1329 for (size_t i = 0; i < value.size(); i++)
1357 { 1330 {
1358 escaped += "_"; 1331 if (value[i] == '*')
1332 {
1333 escaped += "%";
1334 }
1335 else if (value[i] == '?')
1336 {
1337 escaped += "_";
1338 }
1339 else if (value[i] == '%')
1340 {
1341 escaped += "\\%";
1342 }
1343 else if (value[i] == '_')
1344 {
1345 escaped += "\\_";
1346 }
1347 else if (value[i] == '\\')
1348 {
1349 escaped += "\\\\";
1350 }
1351 else
1352 {
1353 escaped += value[i];
1354 }
1359 } 1355 }
1360 else if (value[i] == '%') 1356
1357 parameters.push_back(escaped);
1358
1359 if (constraint.IsCaseSensitive())
1361 { 1360 {
1362 escaped += "\\%"; 1361 comparison = tag + ".value LIKE ? ESCAPE '\\'";
1363 }
1364 else if (value[i] == '_')
1365 {
1366 escaped += "\\_";
1367 }
1368 else if (value[i] == '\\')
1369 {
1370 escaped += "\\\\";
1371 } 1362 }
1372 else 1363 else
1373 { 1364 {
1374 escaped += value[i]; 1365 comparison = "lower(" + tag + ".value) LIKE lower(?) ESCAPE '\\'";
1375 } 1366 }
1376 }
1377
1378 parameters.push_back(escaped);
1379
1380 if (constraint.IsCaseSensitive())
1381 {
1382 comparison = tag + ".value LIKE ? ESCAPE '\\'";
1383 }
1384 else
1385 {
1386 comparison = "lower(" + tag + ".value) LIKE lower(?) ESCAPE '\\'";
1387 } 1367 }
1388 1368
1389 break; 1369 break;
1390 } 1370 }
1391 1371
1394 return false; 1374 return false;
1395 } 1375 }
1396 1376
1397 if (constraint.IsMandatory()) 1377 if (constraint.IsMandatory())
1398 { 1378 {
1399 target += comparison; 1379 target = comparison;
1380 }
1381 else if (comparison.empty())
1382 {
1383 target = tag + ".value IS NULL";
1400 } 1384 }
1401 else 1385 else
1402 { 1386 {
1403 target += tag + ".value IS NULL OR " + comparison; 1387 target = tag + ".value IS NULL OR " + comparison;
1404 } 1388 }
1405 1389
1406 return true; 1390 return true;
1407 } 1391 }
1408 1392
1409 1393
1410 static void PrepareLookup(SQLite::Connection& db) 1394 static void FormatJoin(std::string& target,
1411 { 1395 const DatabaseConstraint& constraint,
1412 SQLite::Statement s(db, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup"); 1396 size_t index)
1413 s.Run(); 1397 {
1414 } 1398 std::string tag = "t" + boost::lexical_cast<std::string>(index);
1415 1399
1400 if (constraint.IsMandatory())
1401 {
1402 target = " INNER JOIN ";
1403 }
1404 else
1405 {
1406 target = " LEFT JOIN ";
1407 }
1408
1409 if (constraint.IsIdentifier())
1410 {
1411 target += "DicomIdentifiers ";
1412 }
1413 else
1414 {
1415 target += "MainDicomTags ";
1416 }
1417
1418 target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) +
1419 ".internalId AND " + tag + ".tagGroup = " +
1420 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) +
1421 " AND " + tag + ".tagElement = " +
1422 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
1423 }
1424
1416 1425
1417 static void AnswerLookup(std::vector<std::string>& resourcesId, 1426 static void AnswerLookup(std::vector<std::string>& resourcesId,
1418 std::vector<std::string>& instancesId, 1427 std::vector<std::string>& instancesId,
1419 SQLite::Connection& db, 1428 SQLite::Connection& db,
1420 ResourceType level) 1429 ResourceType level)
1486 instancesId.push_back(statement->ColumnString(1)); 1495 instancesId.push_back(statement->ColumnString(1));
1487 } 1496 }
1488 } 1497 }
1489 1498
1490 1499
1491 static void FormatConstraints(std::string& joins,
1492 std::string& comparisons,
1493 std::vector<std::string>& parameters,
1494 const std::vector<DatabaseConstraint>& lookup)
1495 {
1496 size_t count = 0;
1497
1498 for (size_t i = 0; i < lookup.size(); i++)
1499 {
1500 std::string comparison;
1501
1502 if (FormatComparison(comparison, lookup[i], count, parameters))
1503 {
1504 std::string join;
1505 FormatJoin(join, lookup[i], count);
1506 joins += join;
1507
1508 comparisons += " AND (" + comparison + ")";
1509
1510 count ++;
1511 }
1512 }
1513 }
1514
1515
1516
1517 void SQLiteDatabaseWrapper::ApplyLookupPatients(std::vector<std::string>& patientsId,
1518 std::vector<std::string>& instancesId,
1519 const std::vector<DatabaseConstraint>& lookup,
1520 size_t limit)
1521 {
1522 PrepareLookup(db_);
1523
1524 std::string joins, comparisons;
1525 std::vector<std::string> parameters;
1526 FormatConstraints(joins, comparisons, parameters, lookup);
1527
1528 {
1529 std::string sql = ("CREATE TEMPORARY TABLE Lookup AS "
1530 "SELECT patients.publicId, patients.internalId FROM Resources AS patients" +
1531 joins + " WHERE patients.resourceType = " +
1532 boost::lexical_cast<std::string>(ResourceType_Patient) + comparisons);
1533
1534 if (limit != 0)
1535 {
1536 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
1537 }
1538
1539 printf("[%s]\n", sql.c_str());
1540
1541 SQLite::Statement s(db_, sql);
1542
1543 for (size_t i = 0; i < parameters.size(); i++)
1544 {
1545 s.BindString(i, parameters[i]);
1546 }
1547
1548 s.Run();
1549 }
1550
1551 AnswerLookup(patientsId, instancesId, db_, ResourceType_Patient);
1552 }
1553
1554
1555 void SQLiteDatabaseWrapper::ApplyLookupResources(std::vector<std::string>& resourcesId, 1500 void SQLiteDatabaseWrapper::ApplyLookupResources(std::vector<std::string>& resourcesId,
1556 std::vector<std::string>& instancesId, 1501 std::vector<std::string>& instancesId,
1557 const std::vector<DatabaseConstraint>& lookup, 1502 const std::vector<DatabaseConstraint>& lookup,
1558 ResourceType queryLevel, 1503 ResourceType queryLevel,
1559 size_t limit) 1504 size_t limit)
1560 { 1505 {
1506 for (size_t i = 0; i < lookup.size(); i++)
1507 {
1508 std::cout << i << ": " << lookup[i].GetTag() << " - " << EnumerationToString(lookup[i].GetLevel());
1509 std::cout << std::endl;
1510 }
1511
1561 assert(ResourceType_Patient < ResourceType_Study && 1512 assert(ResourceType_Patient < ResourceType_Study &&
1562 ResourceType_Study < ResourceType_Series && 1513 ResourceType_Study < ResourceType_Series &&
1563 ResourceType_Series < ResourceType_Instance); 1514 ResourceType_Series < ResourceType_Instance);
1564 1515
1565 ResourceType upperLevel = queryLevel; 1516 ResourceType upperLevel = queryLevel;
1580 } 1531 }
1581 } 1532 }
1582 1533
1583 printf("ICI 2: [%s] -> [%s]\n", EnumerationToString(upperLevel), EnumerationToString(lowerLevel)); 1534 printf("ICI 2: [%s] -> [%s]\n", EnumerationToString(upperLevel), EnumerationToString(lowerLevel));
1584 1535
1585 PrepareLookup(db_); 1536 {
1537 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup");
1538 s.Run();
1539 }
1586 1540
1587 std::string joins, comparisons; 1541 std::string joins, comparisons;
1588 std::vector<std::string> parameters; 1542 std::vector<std::string> parameters;
1589 FormatConstraints(joins, comparisons, parameters, lookup); 1543
1544 size_t count = 0;
1545
1546 for (size_t i = 0; i < lookup.size(); i++)
1547 {
1548 std::string comparison;
1549
1550 if (FormatComparison(comparison, lookup[i], count, parameters))
1551 {
1552 std::string join;
1553 FormatJoin(join, lookup[i], count);
1554 joins += join;
1555
1556 if (!comparison.empty())
1557 {
1558 comparisons += " AND " + comparison;
1559 }
1560
1561 count ++;
1562 }
1563 }
1590 1564
1591 { 1565 {
1592 std::string sql = ("CREATE TEMPORARY TABLE Lookup AS SELECT " + 1566 std::string sql = ("CREATE TEMPORARY TABLE Lookup AS SELECT " +
1593 FormatLevel(queryLevel) + ".publicId, " + 1567 FormatLevel(queryLevel) + ".publicId, " +
1594 FormatLevel(queryLevel) + ".internalId" + 1568 FormatLevel(queryLevel) + ".internalId" +
1603 } 1577 }
1604 1578
1605 for (int level = queryLevel + 1; level <= lowerLevel; level++) 1579 for (int level = queryLevel + 1; level <= lowerLevel; level++)
1606 { 1580 {
1607 sql += (" INNER JOIN Resources " + 1581 sql += (" INNER JOIN Resources " +
1608 FormatLevel(static_cast<ResourceType>(level - 1)) + " ON " + 1582 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
1609 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + 1583 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
1610 FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); 1584 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
1611 } 1585 }
1612 1586
1613 sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " + 1587 sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " +