Patient Profile fails when CDM and Vocabulary are configured in separate schemas (WebAPI 2.15.1)

未关闭
#2,596 0 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

维护者通常 3 天内回复

还没有人认领这个 Issue。

评估

难度
2/5
预计耗时
1-3 小时
新手友好度
55/100
Issue 类型
缺陷
描述清晰度
基本清楚
活跃度
活跃
技术栈
java, postgresql
领域
backend, databases

调研方向

Read src/main/resources/resources/person/sql/personInfo.sql and compare its vocabulary table qualifier with the master version linked in the issue. Then inspect the other Patient Profile SQL templates for the same schema assumption and check the related tests. Done means profile queries use the configured Vocabulary schema consistently; the issue does not identify specific tests.

由索引模型根据 Issue 内容生成。

描述

Description

We encountered an issue in OHDSI ATLAS when attempting to view patient profiles, either directly through the Profiles section or through the Samples tab of a generated cohort.

Although the patients exist in the CDM and cohort generation and sampling work correctly, ATLAS displays the following error:

Can't find [patientID]

Environment

  • WebAPI version: 2.15.1
  • Build: 2.15.1-SNAPSHOT
  • Commit: ba50f13a
  • Database: PostgreSQL 16.4
  • Deployment: Docker

Data source configuration

Our CDM and Vocabulary are configured in separate schemas using webapi.source_daimon:

Daimon type Schema
CDM (0) clinical_cdm
Vocabulary (1) shared_vocabulary
Results (2) clinical_cdm_results

The vocabulary schema is shared across multiple CDM data sources.

Steps to reproduce

  1. Configure a data source with separate CDM and Vocabulary schemas.
  2. Ensure that the person table exists in the CDM schema and the concept table exists only in the Vocabulary schema.
  3. Open the Profiles section in ATLAS.
  4. Select the configured data source.
  5. Search for a valid person ID.

Actual behavior

WebAPI returns HTTP 500, and ATLAS displays Can't find [patientID].

The WebAPI logs show:

org.springframework.jdbc.BadSqlGrammarException

ERROR: relation "clinical_cdm.concept" does not exist

at org.ohdsi.webapi.service.PersonService.getPersonProfile(PersonService.java:81)

The executed SQL is:

SELECT gender_concept_id,
       year_of_birth,
       concept_name AS gender
FROM clinical_cdm.person p
JOIN clinical_cdm.concept c
    ON p.gender_concept_id = c.concept_id
WHERE person_id = ?;

Root cause

The SQL template personInfo.sql uses the same @tableQualifier parameter for both the person and concept tables, ignoring the separately configured Vocabulary schema.

Affected source file:

https://github.com/OHDSI/WebAPI/blob/ba50f13a/src/main/resources/resources/person/sql/personInfo.sql

Expected behavior

Patient Profile queries should use the CDM schema for clinical tables and the configured Vocabulary schema for vocabulary tables.

The expected SQL template would be:

SELECT gender_concept_id,
       year_of_birth,
       concept_name AS gender
FROM @tableQualifier.person p
JOIN @vocabularyTableQualifier.concept c
    ON p.gender_concept_id = c.concept_id
WHERE person_id = @personId;

Confirmed workaround

We created a PostgreSQL view in the CDM schema pointing to the shared vocabulary table:

CREATE VIEW clinical_cdm.concept AS
SELECT *
FROM shared_vocabulary.concept;

After creating this view, patient profiles were successfully displayed in ATLAS.

Additional observations

The same SQL template in the v2.15.2 tag still references the CDM schema for the concept table:

https://github.com/OHDSI/WebAPI/blob/v2.15.2/src/main/resources/resources/person/sql/personInfo.sql

However, the current master branch appears to contain a correction using @vocabularyTableQualifier:

https://github.com/OHDSI/WebAPI/blob/master/src/main/resources/resources/person/sql/personInfo.sql

Could you please confirm whether this issue has already been addressed and whether the fix is planned to be included in a stable release of the 2.15.x series?

It would also be helpful to verify that all other Patient Profile queries consistently respect the configured Vocabulary schema.

Thank you for maintaining WebAPI and ATLAS.

主要语言
Java
星标
154
派生
185
平均合并
3 天 57 分钟
30 天内合并 PR
14

环境准备

  • 提供 Dockerfile 或 Docker Compose 文件
  • 没有 Pull Request 模板
  • 没有贡献指南

从这里开始

  1. 先读完整个 Issue,再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库,在一个分支上完成修改。
  4. 提交 Pull Request,并在描述里引用这个 Issue 编号。

OHDSI/WebAPI 的其他 Issue

查看 OHDSI/WebAPI 的全部 Issue

相似的 Issue

更多 Java Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。