Mercurial > hg > orthanc

comparison OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp @ 5751:5d78e5cafabc find-refactoring

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
ExtendedFind in SQLite continued (all basic integration tests OK)
author Alain Mazy <am@orthanc.team>
date Tue, 03 Sep 2024 11:09:49 +0200
parents f39406a9eda4
children 717acb0ea546
comparison
equal deleted inserted replaced
5750:f39406a9eda4 5751:5d78e5cafabc
384 384
385 virtual void ExecuteFind(FindResponse& response, 385 virtual void ExecuteFind(FindResponse& response,
386 const FindRequest& request, 386 const FindRequest& request,
387 const Capabilities& capabilities) ORTHANC_OVERRIDE 387 const Capabilities& capabilities) ORTHANC_OVERRIDE
388 { 388 {
389 LookupFormatter formatter; 389 const ResourceType requestLevel = request.GetLevel();
390
391 std::string sql; 390 std::string sql;
392 LookupFormatter::Apply(sql, formatter, request); 391
393 392 {
394 sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; 393 // clean previous lookup table
395
396 {
397 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup"); 394 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup");
398 s.Run(); 395 s.Run();
399 } 396 }
400 397
401 { 398 {
399 // extract the resource id of interest by executing the lookup
400 LookupFormatter formatter;
401 LookupFormatter::Apply(sql, formatter, request);
402
403 sql = "CREATE TEMPORARY TABLE Lookup AS " + sql;
404
402 SQLite::Statement statement(db_, sql); 405 SQLite::Statement statement(db_, sql);
403 formatter.Bind(statement); 406 formatter.Bind(statement);
404 statement.Run(); 407 statement.Run();
405 } 408
406
407 {
408 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId, internalId FROM Lookup"); 409 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId, internalId FROM Lookup");
409 while (s.Step()) 410 while (s.Step())
410 { 411 {
411 response.Add(new FindResponse::Resource(request.GetLevel(), s.ColumnInt64(1), s.ColumnString(0))); 412 response.Add(new FindResponse::Resource(requestLevel, s.ColumnInt64(1), s.ColumnString(0)));
412 } 413 }
413 } 414 }
414 415
416 // need MainDicomTags from resource ?
415 if (request.IsRetrieveMainDicomTags()) 417 if (request.IsRetrieveMainDicomTags())
416 { 418 {
417 sql = "SELECT id, tagGroup, tagElement, value " 419 sql = "SELECT id, tagGroup, tagElement, value "
418 "FROM MainDicomTags " 420 "FROM MainDicomTags "
419 "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId"; 421 "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId";
420 422
421 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 423 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
422 while (s.Step()) 424 while (s.Step())
423 { 425 {
424 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 426 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
425 res.AddStringDicomTag(request.GetLevel(), 427 res.AddStringDicomTag(requestLevel,
426 static_cast<uint16_t>(s.ColumnInt(1)), 428 static_cast<uint16_t>(s.ColumnInt(1)),
427 static_cast<uint16_t>(s.ColumnInt(2)), 429 static_cast<uint16_t>(s.ColumnInt(2)),
428 s.ColumnString(3)); 430 s.ColumnString(3));
429 } 431 }
430 } 432 }
431 433
432 // need MainDicomTags from parent 434 // need MainDicomTags from parent ?
433 if (request.GetLevel() > ResourceType_Patient && request.GetParentSpecification(static_cast<ResourceType>(request.GetLevel() - 1)).IsRetrieveMainDicomTags()) 435 if (requestLevel > ResourceType_Patient && request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 1)).IsRetrieveMainDicomTags())
434 { 436 {
435 sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value " 437 sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value "
436 "FROM MainDicomTags " 438 "FROM MainDicomTags "
437 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " 439 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
438 "INNER JOIN Lookup ON MainDicomTags.id = currentLevel.parentId"; 440 "INNER JOIN Lookup ON MainDicomTags.id = currentLevel.parentId";
439 441
440 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 442 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
441 while (s.Step()) 443 while (s.Step())
442 { 444 {
443 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 445 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
444 res.AddStringDicomTag(static_cast<ResourceType>(request.GetLevel() - 1), 446 res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 1),
445 static_cast<uint16_t>(s.ColumnInt(1)), 447 static_cast<uint16_t>(s.ColumnInt(1)),
446 static_cast<uint16_t>(s.ColumnInt(2)), 448 static_cast<uint16_t>(s.ColumnInt(2)),
447 s.ColumnString(3)); 449 s.ColumnString(3));
448 } 450 }
449 } 451 }
450 452
451 // need MainDicomTags from grandparent 453 // need MainDicomTags from grandparent ?
452 if (request.GetLevel() > ResourceType_Study && request.GetParentSpecification(static_cast<ResourceType>(request.GetLevel() - 2)).IsRetrieveMainDicomTags()) 454 if (requestLevel > ResourceType_Study && request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMainDicomTags())
453 { 455 {
454 sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value " 456 sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value "
455 "FROM MainDicomTags " 457 "FROM MainDicomTags "
456 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " 458 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
457 "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId " 459 "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "
459 461
460 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 462 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
461 while (s.Step()) 463 while (s.Step())
462 { 464 {
463 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 465 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
464 res.AddStringDicomTag(static_cast<ResourceType>(request.GetLevel() - 2), 466 res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 2),
465 static_cast<uint16_t>(s.ColumnInt(1)), 467 static_cast<uint16_t>(s.ColumnInt(1)),
466 static_cast<uint16_t>(s.ColumnInt(2)), 468 static_cast<uint16_t>(s.ColumnInt(2)),
467 s.ColumnString(3)); 469 s.ColumnString(3));
468 } 470 }
469 } 471 }
470 472
471 // need MainDicomTags from children 473 // need MainDicomTags from children ?
472 if (request.GetLevel() <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 1)).GetMainDicomTags().size() > 0) 474 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMainDicomTags().size() > 0)
473 { 475 {
474 sql = "SELECT Lookup.internalId, tagGroup, tagElement, value " 476 sql = "SELECT Lookup.internalId, tagGroup, tagElement, value "
475 "FROM MainDicomTags " 477 "FROM MainDicomTags "
476 "INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " 478 "INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
477 "INNER JOIN Lookup ON MainDicomTags.id = childLevel.internalId "; 479 "INNER JOIN Lookup ON MainDicomTags.id = childLevel.internalId ";
478 480
479 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 481 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
480 while (s.Step()) 482 while (s.Step())
481 { 483 {
482 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 484 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
483 res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(request.GetLevel() + 1), 485 res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 1),
484 DicomTag(static_cast<uint16_t>(s.ColumnInt(1)), 486 DicomTag(static_cast<uint16_t>(s.ColumnInt(1)),
485 static_cast<uint16_t>(s.ColumnInt(2))), 487 static_cast<uint16_t>(s.ColumnInt(2))),
486 s.ColumnString(3)); 488 s.ColumnString(3));
487 } 489 }
488 } 490 }
489 491
490 // need MainDicomTags from grandchildren 492 // need MainDicomTags from grandchildren ?
491 if (request.GetLevel() <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 2)).GetMainDicomTags().size() > 0) 493 if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).GetMainDicomTags().size() > 0)
492 { 494 {
493 sql = "SELECT Lookup.internalId, tagGroup, tagElement, value " 495 sql = "SELECT Lookup.internalId, tagGroup, tagElement, value "
494 "FROM MainDicomTags " 496 "FROM MainDicomTags "
495 "INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " 497 "INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
496 "INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId " 498 "INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId "
498 500
499 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 501 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
500 while (s.Step()) 502 while (s.Step())
501 { 503 {
502 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 504 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
503 res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(request.GetLevel() + 2), 505 res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 2),
504 DicomTag(static_cast<uint16_t>(s.ColumnInt(1)), 506 DicomTag(static_cast<uint16_t>(s.ColumnInt(1)),
505 static_cast<uint16_t>(s.ColumnInt(2))), 507 static_cast<uint16_t>(s.ColumnInt(2))),
506 s.ColumnString(3)); 508 s.ColumnString(3));
507 } 509 }
508 } 510 }
509 511
512 // need parent identifier ?
510 if (request.IsRetrieveParentIdentifier()) 513 if (request.IsRetrieveParentIdentifier())
511 { 514 {
512 sql = "SELECT currentLevel.internalId, parentLevel.publicId " 515 sql = "SELECT currentLevel.internalId, parentLevel.publicId "
513 "FROM Resources AS currentLevel " 516 "FROM Resources AS currentLevel "
514 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " 517 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
520 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 523 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
521 res.SetParentIdentifier(s.ColumnString(1)); 524 res.SetParentIdentifier(s.ColumnString(1));
522 } 525 }
523 } 526 }
524 527
528 // need resource metadata ?
525 if (request.IsRetrieveMetadata()) 529 if (request.IsRetrieveMetadata())
526 { 530 {
527 sql = "SELECT id, type, value " 531 sql = "SELECT id, type, value "
528 "FROM Metadata " 532 "FROM Metadata "
529 "INNER JOIN Lookup ON Metadata.id = Lookup.internalId"; 533 "INNER JOIN Lookup ON Metadata.id = Lookup.internalId";
530 534
531 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 535 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
532 while (s.Step()) 536 while (s.Step())
533 { 537 {
534 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 538 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
535 res.AddMetadata(request.GetLevel(), 539 res.AddMetadata(requestLevel,
536 static_cast<MetadataType>(s.ColumnInt(1)), 540 static_cast<MetadataType>(s.ColumnInt(1)),
537 s.ColumnString(2)); 541 s.ColumnString(2));
538 } 542 }
539 } 543 }
540 544
545 // need resource labels ?
541 if (request.IsRetrieveLabels()) 546 if (request.IsRetrieveLabels())
542 { 547 {
543 sql = "SELECT id, label " 548 sql = "SELECT id, label "
544 "FROM Labels " 549 "FROM Labels "
545 "INNER JOIN Lookup ON Labels.id = Lookup.internalId"; 550 "INNER JOIN Lookup ON Labels.id = Lookup.internalId";
550 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 555 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
551 res.AddLabel(s.ColumnString(1)); 556 res.AddLabel(s.ColumnString(1));
552 } 557 }
553 } 558 }
554 559
560 // need one instance identifier ? TODO: it might be actually more interesting to retrieve directly the attachment ids ....
555 if (request.IsRetrieveOneInstanceIdentifier()) 561 if (request.IsRetrieveOneInstanceIdentifier())
556 { 562 {
557 if (request.GetLevel() == ResourceType_Series) 563 if (requestLevel == ResourceType_Series)
558 { 564 {
559 sql = "SELECT Lookup.internalId, childLevel.publicId " 565 sql = "SELECT Lookup.internalId, childLevel.publicId "
560 "FROM Resources AS childLevel " 566 "FROM Resources AS childLevel "
561 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId "; 567 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId ";
562 568
565 { 571 {
566 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 572 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
567 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); 573 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1));
568 } 574 }
569 } 575 }
570 else if (request.GetLevel() == ResourceType_Study) 576 else if (requestLevel == ResourceType_Study)
571 { 577 {
572 sql = "SELECT Lookup.internalId, grandChildLevel.publicId " 578 sql = "SELECT Lookup.internalId, grandChildLevel.publicId "
573 "FROM Resources AS grandChildLevel " 579 "FROM Resources AS grandChildLevel "
574 "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " 580 "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
575 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId "; 581 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId ";
579 { 585 {
580 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 586 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
581 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); 587 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1));
582 } 588 }
583 } 589 }
584 else if (request.GetLevel() == ResourceType_Patient) 590 else if (requestLevel == ResourceType_Patient)
585 { 591 {
586 sql = "SELECT Lookup.internalId, grandGrandChildLevel.publicId " 592 sql = "SELECT Lookup.internalId, grandGrandChildLevel.publicId "
587 "FROM Resources AS grandGrandChildLevel " 593 "FROM Resources AS grandGrandChildLevel "
588 "INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId " 594 "INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId "
589 "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " 595 "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
600 { 606 {
601 throw OrthancException(ErrorCode_InternalError); 607 throw OrthancException(ErrorCode_InternalError);
602 } 608 }
603 } 609 }
604 610
605 if (request.GetLevel() <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 1)).IsRetrieveIdentifiers()) 611 // need children metadata ?
612 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMetadata().size() > 0)
613 {
614 sql = "SELECT Lookup.internalId, type, value "
615 "FROM Metadata "
616 "INNER JOIN Lookup ON Lookup.internalId = childLevel.parentId "
617 "INNER JOIN Resources childLevel ON childLevel.internalId = Metadata.id";
618
619 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
620 while (s.Step())
621 {
622 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
623 res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 1),
624 static_cast<MetadataType>(s.ColumnInt(1)),
625 s.ColumnString(2));
626 }
627 }
628
629 // need grandchildren metadata ?
630 if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).GetMetadata().size() > 0)
631 {
632 sql = "SELECT Lookup.internalId, type, value "
633 "FROM Metadata "
634 "INNER JOIN Lookup ON Lookup.internalId = childLevel.parentId "
635 "INNER JOIN Resources childLevel ON childLevel.internalId = grandChildLevel.parentId "
636 "INNER JOIN Resources grandChildLevel ON grandChildLevel.internalId = Metadata.id";
637
638 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
639 while (s.Step())
640 {
641 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
642 res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 2),
643 static_cast<MetadataType>(s.ColumnInt(1)),
644 s.ColumnString(2));
645 }
646 }
647
648 // need children identifiers ?
649 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).IsRetrieveIdentifiers())
606 { 650 {
607 sql = "SELECT currentLevel.internalId, childLevel.publicId " 651 sql = "SELECT currentLevel.internalId, childLevel.publicId "
608 "FROM Resources AS currentLevel " 652 "FROM Resources AS currentLevel "
609 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " 653 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
610 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "; 654 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId ";
611 655
612 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 656 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
613 while (s.Step()) 657 while (s.Step())
614 { 658 {
615 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 659 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
616 res.AddChildIdentifier(static_cast<ResourceType>(request.GetLevel() + 1), s.ColumnString(1)); 660 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 1), s.ColumnString(1));
617 } 661 }
618 } 662 }
619 663
620 if (request.GetLevel() <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 2)).IsRetrieveIdentifiers()) 664 // need grandchildren identifiers ?
665 if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).IsRetrieveIdentifiers())
621 { 666 {
622 sql = "SELECT currentLevel.internalId, grandChildLevel.publicId " 667 sql = "SELECT currentLevel.internalId, grandChildLevel.publicId "
623 "FROM Resources AS currentLevel " 668 "FROM Resources AS currentLevel "
624 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " 669 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
625 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId " 670 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "
627 672
628 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 673 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
629 while (s.Step()) 674 while (s.Step())
630 { 675 {
631 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 676 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
632 res.AddChildIdentifier(static_cast<ResourceType>(request.GetLevel() + 2), s.ColumnString(1)); 677 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 2), s.ColumnString(1));
633 } 678 }
634 } 679 }
635 680
681 // need resource attachments ?
636 if (request.IsRetrieveAttachments()) 682 if (request.IsRetrieveAttachments())
637 { 683 {
638 sql = "SELECT id, fileType, uuid, uncompressedSize, compressedSize, compressionType, uncompressedMD5, compressedMD5 " 684 sql = "SELECT id, fileType, uuid, uncompressedSize, compressedSize, compressionType, uncompressedMD5, compressedMD5 "
639 "FROM AttachedFiles " 685 "FROM AttachedFiles "
640 "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId"; 686 "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId";