Add option to hook into cohort SQL for customization #180

anthonysena · 2024-11-05T17:01:52Z

Aims to close #179 by adding a new option strategus.cohortSqlOptimizationFunction to pass a function to customize SQL. Here is how you may use this option and define a function that takes a single parameter sql:

library(Strategus)
options("strategus.cohortSqlOptimizationFunction"= function(sql) { return(paste0("-- Test\n", sql))})
tempDir <- tempfile()
tempDir <- gsub("\\\\", "/", tempDir) # Correct windows path

analysisSpecifications <- ParallelLogger::loadSettingsFromJson(
  fileName = system.file("testdata/cdmModulesAnalysisSpecifications.json",
                         package = "Strategus"
  )
)
studyRootFolder <- file.path(tempDir, "EunomiaTestStudy")
workFolder <- file.path(studyRootFolder, "work_folder")
resultsFolder <- file.path(studyRootFolder, "results_folder")
if (!dir.exists(studyRootFolder)) {
  dir.create(studyRootFolder, recursive = TRUE)
}

# Execute the study ---------------------------
executionSettings <- createCdmExecutionSettings(
  workDatabaseSchema = "main",
  cdmDatabaseSchema = "main",
  cohortTableNames = CohortGenerator::getCohortTableNames(cohortTable = "test_cohort"),
  workFolder = workFolder,
  resultsFolder = resultsFolder,
  modulesToExecute = "CohortGeneratorModule"
)

connectionDetails <- Eunomia::getEunomiaConnectionDetails()
#> attempting to download GiBleed
#> attempting to extract and load: C:\Users\ADMIN_~3\AppData\Local\Temp\Rtmpo1Frmz/GiBleed_5.3.zip to: C:\Users\ADMIN_~3\AppData\Local\Temp\Rtmpo1Frmz/GiBleed_5.3.sqlite

Strategus::execute(
  connectionDetails = connectionDetails,
  analysisSpecifications = analysisSpecifications,
  executionSettings = executionSettings
)
#> ℹ Runnning a subset of modules: CohortGeneratorModule
#> Currently in a tryCatch or withCallingHandlers block, so unable to add global calling handlers. ParallelLogger will not capture R messages, errors, and warnings, only explicit calls to ParallelLogger. (This message will not be shown again this R session)
#> Connecting using SQLite driver
#> EXECUTING: CohortGeneratorModule
#> Constructing cohort definition set and using strategus.cohortSqlOptimizationFunction
#> Connecting using SQLite driver
#> Creating cohort tables
#> - Created table main.test_cohort
#> - Created table main.test_cohort
#> - Created table main.test_cohort_inclusion
#> - Created table main.test_cohort_inclusion_result
#> - Created table main.test_cohort_inclusion_stats
#> - Created table main.test_cohort_summary_stats
#> - Created table main.test_cohort_censor_stats
#> Creating cohort tables took 0.16secs
#> 1/10- Generating cohort: Celecoxib (id = 1)
#>   |                                                                              |                                                                      |   0%  |                                                                              |==                                                                    |   3%  |                                                                              |====                                                                  |   6%  |                                                                              |======                                                                |   9%  |                                                                              |========                                                              |  11%  |                                                                              |==========                                                            |  14%  |                                                                              |============                                                          |  17%  |                                                                              |==============                                                        |  20%  |                                                                              |================                                                      |  23%  |                                                                              |==================                                                    |  26%  |                                                                              |====================                                                  |  29%  |                                                                              |======================                                                |  31%  |                                                                              |========================                                              |  34%  |                                                                              |==========================                                            |  37%  |                                                                              |============================                                          |  40%  |                                                                              |==============================                                        |  43%  |                                                                              |================================                                      |  46%  |                                                                              |==================================                                    |  49%  |                                                                              |====================================                                  |  51%  |                                                                              |======================================                                |  54%  |                                                                              |========================================                              |  57%  |                                                                              |==========================================                            |  60%  |                                                                              |============================================                          |  63%  |                                                                              |==============================================                        |  66%  |                                                                              |================================================                      |  69%  |                                                                              |==================================================                    |  71%  |                                                                              |====================================================                  |  74%  |                                                                              |======================================================                |  77%  |                                                                              |========================================================              |  80%  |                                                                              |==========================================================            |  83%  |                                                                              |============================================================          |  86%  |                                                                              |==============================================================        |  89%  |                                                                              |================================================================      |  91%  |                                                                              |==================================================================    |  94%  |                                                                              |====================================================================  |  97%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.081 secs
#> 2/10- Generating cohort: Diclofenac (id = 2)
#>   |                                                                              |                                                                      |   0%  |                                                                              |==                                                                    |   3%  |                                                                              |====                                                                  |   6%  |                                                                              |======                                                                |   9%  |                                                                              |========                                                              |  11%  |                                                                              |==========                                                            |  14%  |                                                                              |============                                                          |  17%  |                                                                              |==============                                                        |  20%  |                                                                              |================                                                      |  23%  |                                                                              |==================                                                    |  26%  |                                                                              |====================                                                  |  29%  |                                                                              |======================                                                |  31%  |                                                                              |========================                                              |  34%  |                                                                              |==========================                                            |  37%  |                                                                              |============================                                          |  40%  |                                                                              |==============================                                        |  43%  |                                                                              |================================                                      |  46%  |                                                                              |==================================                                    |  49%  |                                                                              |====================================                                  |  51%  |                                                                              |======================================                                |  54%  |                                                                              |========================================                              |  57%  |                                                                              |==========================================                            |  60%  |                                                                              |============================================                          |  63%  |                                                                              |==============================================                        |  66%  |                                                                              |================================================                      |  69%  |                                                                              |==================================================                    |  71%  |                                                                              |====================================================                  |  74%  |                                                                              |======================================================                |  77%  |                                                                              |========================================================              |  80%  |                                                                              |==========================================================            |  83%  |                                                                              |============================================================          |  86%  |                                                                              |==============================================================        |  89%  |                                                                              |================================================================      |  91%  |                                                                              |==================================================================    |  94%  |                                                                              |====================================================================  |  97%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.0682 secs
#> 3/10- Generating cohort: GI bleed (id = 3)
#>   |                                                                              |                                                                      |   0%  |                                                                              |==                                                                    |   3%  |                                                                              |=====                                                                 |   6%  |                                                                              |=======                                                               |  10%  |                                                                              |=========                                                             |  13%  |                                                                              |===========                                                           |  16%  |                                                                              |==============                                                        |  19%  |                                                                              |================                                                      |  23%  |                                                                              |==================                                                    |  26%  |                                                                              |====================                                                  |  29%  |                                                                              |=======================                                               |  32%  |                                                                              |=========================                                             |  35%  |                                                                              |===========================                                           |  39%  |                                                                              |=============================                                         |  42%  |                                                                              |================================                                      |  45%  |                                                                              |==================================                                    |  48%  |                                                                              |====================================                                  |  52%  |                                                                              |======================================                                |  55%  |                                                                              |=========================================                             |  58%  |                                                                              |===========================================                           |  61%  |                                                                              |=============================================                         |  65%  |                                                                              |===============================================                       |  68%  |                                                                              |==================================================                    |  71%  |                                                                              |====================================================                  |  74%  |                                                                              |======================================================                |  77%  |                                                                              |========================================================              |  81%  |                                                                              |===========================================================           |  84%  |                                                                              |=============================================================         |  87%  |                                                                              |===============================================================       |  90%  |                                                                              |=================================================================     |  94%  |                                                                              |====================================================================  |  97%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.0459 secs
#> 4/10- Generating cohort: Celecoxib age 30 or older (id = 4)
#>   |                                                                              |                                                                      |   0%  |                                                                              |=                                                                     |   2%  |                                                                              |==                                                                    |   3%  |                                                                              |====                                                                  |   5%  |                                                                              |=====                                                                 |   7%  |                                                                              |======                                                                |   8%  |                                                                              |=======                                                               |  10%  |                                                                              |========                                                              |  12%  |                                                                              |=========                                                             |  13%  |                                                                              |==========                                                            |  15%  |                                                                              |============                                                          |  17%  |                                                                              |=============                                                         |  18%  |                                                                              |==============                                                        |  20%  |                                                                              |===============                                                       |  22%  |                                                                              |================                                                      |  23%  |                                                                              |==================                                                    |  25%  |                                                                              |===================                                                   |  27%  |                                                                              |====================                                                  |  28%  |                                                                              |=====================                                                 |  30%  |                                                                              |======================                                                |  32%  |                                                                              |=======================                                               |  33%  |                                                                              |========================                                              |  35%  |                                                                              |==========================                                            |  37%  |                                                                              |===========================                                           |  38%  |                                                                              |============================                                          |  40%  |                                                                              |=============================                                         |  42%  |                                                                              |==============================                                        |  43%  |                                                                              |================================                                      |  45%  |                                                                              |=================================                                     |  47%  |                                                                              |==================================                                    |  48%  |                                                                              |===================================                                   |  50%  |                                                                              |====================================                                  |  52%  |                                                                              |=====================================                                 |  53%  |                                                                              |======================================                                |  55%  |                                                                              |========================================                              |  57%  |                                                                              |=========================================                             |  58%  |                                                                              |==========================================                            |  60%  |                                                                              |===========================================                           |  62%  |                                                                              |============================================                          |  63%  |                                                                              |==============================================                        |  65%  |                                                                              |===============================================                       |  67%  |                                                                              |================================================                      |  68%  |                                                                              |=================================================                     |  70%  |                                                                              |==================================================                    |  72%  |                                                                              |===================================================                   |  73%  |                                                                              |====================================================                  |  75%  |                                                                              |======================================================                |  77%  |                                                                              |=======================================================               |  78%  |                                                                              |========================================================              |  80%  |                                                                              |=========================================================             |  82%  |                                                                              |==========================================================            |  83%  |                                                                              |============================================================          |  85%  |                                                                              |=============================================================         |  87%  |                                                                              |==============================================================        |  88%  |                                                                              |===============================================================       |  90%  |                                                                              |================================================================      |  92%  |                                                                              |=================================================================     |  93%  |                                                                              |==================================================================    |  95%  |                                                                              |====================================================================  |  97%  |                                                                              |===================================================================== |  98%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.193 secs
#> 5/10- Generating cohort: Diclofenac age 30 or older (id = 5)
#>   |                                                                              |                                                                      |   0%  |                                                                              |=                                                                     |   2%  |                                                                              |==                                                                    |   3%  |                                                                              |====                                                                  |   5%  |                                                                              |=====                                                                 |   7%  |                                                                              |======                                                                |   8%  |                                                                              |=======                                                               |  10%  |                                                                              |========                                                              |  12%  |                                                                              |=========                                                             |  13%  |                                                                              |==========                                                            |  15%  |                                                                              |============                                                          |  17%  |                                                                              |=============                                                         |  18%  |                                                                              |==============                                                        |  20%  |                                                                              |===============                                                       |  22%  |                                                                              |================                                                      |  23%  |                                                                              |==================                                                    |  25%  |                                                                              |===================                                                   |  27%  |                                                                              |====================                                                  |  28%  |                                                                              |=====================                                                 |  30%  |                                                                              |======================                                                |  32%  |                                                                              |=======================                                               |  33%  |                                                                              |========================                                              |  35%  |                                                                              |==========================                                            |  37%  |                                                                              |===========================                                           |  38%  |                                                                              |============================                                          |  40%  |                                                                              |=============================                                         |  42%  |                                                                              |==============================                                        |  43%  |                                                                              |================================                                      |  45%  |                                                                              |=================================                                     |  47%  |                                                                              |==================================                                    |  48%  |                                                                              |===================================                                   |  50%  |                                                                              |====================================                                  |  52%  |                                                                              |=====================================                                 |  53%  |                                                                              |======================================                                |  55%  |                                                                              |========================================                              |  57%  |                                                                              |=========================================                             |  58%  |                                                                              |==========================================                            |  60%  |                                                                              |===========================================                           |  62%  |                                                                              |============================================                          |  63%  |                                                                              |==============================================                        |  65%  |                                                                              |===============================================                       |  67%  |                                                                              |================================================                      |  68%  |                                                                              |=================================================                     |  70%  |                                                                              |==================================================                    |  72%  |                                                                              |===================================================                   |  73%  |                                                                              |====================================================                  |  75%  |                                                                              |======================================================                |  77%  |                                                                              |=======================================================               |  78%  |                                                                              |========================================================              |  80%  |                                                                              |=========================================================             |  82%  |                                                                              |==========================================================            |  83%  |                                                                              |============================================================          |  85%  |                                                                              |=============================================================         |  87%  |                                                                              |==============================================================        |  88%  |                                                                              |===============================================================       |  90%  |                                                                              |================================================================      |  92%  |                                                                              |=================================================================     |  93%  |                                                                              |==================================================================    |  95%  |                                                                              |====================================================================  |  97%  |                                                                              |===================================================================== |  98%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.162 secs
#> 6/10- Generating cohort: Celecoxib - test definition Demographic Criteria (id = 1001)
#>   |                                                                              |                                                                      |   0%  |                                                                              |=======                                                               |  10%  |                                                                              |==============                                                        |  20%  |                                                                              |=====================                                                 |  30%  |                                                                              |============================                                          |  40%  |                                                                              |===================================                                   |  50%  |                                                                              |==========================================                            |  60%  |                                                                              |=================================================                     |  70%  |                                                                              |========================================================              |  80%  |                                                                              |===============================================================       |  90%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.0229 secs
#> 7/10- Generating cohort: Diclofenac - test definition Demographic Criteria (id = 2001)
#>   |                                                                              |                                                                      |   0%  |                                                                              |=======                                                               |  10%  |                                                                              |==============                                                        |  20%  |                                                                              |=====================                                                 |  30%  |                                                                              |============================                                          |  40%  |                                                                              |===================================                                   |  50%  |                                                                              |==========================================                            |  60%  |                                                                              |=================================================                     |  70%  |                                                                              |========================================================              |  80%  |                                                                              |===============================================================       |  90%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.0227 secs
#> 8/10- Generating cohort: GI bleed - test definition Demographic Criteria (id = 3001)
#>   |                                                                              |                                                                      |   0%  |                                                                              |=======                                                               |  10%  |                                                                              |==============                                                        |  20%  |                                                                              |=====================                                                 |  30%  |                                                                              |============================                                          |  40%  |                                                                              |===================================                                   |  50%  |                                                                              |==========================================                            |  60%  |                                                                              |=================================================                     |  70%  |                                                                              |========================================================              |  80%  |                                                                              |===============================================================       |  90%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.0249 secs
#> 9/10- Generating cohort: Celecoxib age 30 or older - test definition Demographic Criteria (id = 4001)
#>   |                                                                              |                                                                      |   0%  |                                                                              |=======                                                               |  10%  |                                                                              |==============                                                        |  20%  |                                                                              |=====================                                                 |  30%  |                                                                              |============================                                          |  40%  |                                                                              |===================================                                   |  50%  |                                                                              |==========================================                            |  60%  |                                                                              |=================================================                     |  70%  |                                                                              |========================================================              |  80%  |                                                                              |===============================================================       |  90%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.0224 secs
#> 10/10- Generating cohort: Diclofenac age 30 or older - test definition Demographic Criteria (id = 5001)
#>   |                                                                              |                                                                      |   0%  |                                                                              |=======                                                               |  10%  |                                                                              |==============                                                        |  20%  |                                                                              |=====================                                                 |  30%  |                                                                              |============================                                          |  40%  |                                                                              |===================================                                   |  50%  |                                                                              |==========================================                            |  60%  |                                                                              |=================================================                     |  70%  |                                                                              |========================================================              |  80%  |                                                                              |===============================================================       |  90%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.021 secs
#> Generating cohort set took 1.54 secs
#> Counting cohorts took 0.0336 secs
#> Saving cohort generation information
#> Saving cohort counts
#>     censoring 0 values (0%) from cohortEntries because value below minimum
#> 
#>     censoring 0 values (0%) from cohortSubjects because value below minimum
#> 
#> Saving cohort statistics
#> - Saving data to - C:\Users\ADMIN_~3\AppData\Local\Temp\Rtmpo1Frmz\file2cf86c4e47f3\EunomiaTestStudy\results_folder/CohortGeneratorModule/cg_cohort_inclusion.csv
#> - Fetching data from test_cohort_inclusion
#> - Fetching data from test_cohort_inclusion_result
#> - Fetching data from test_cohort_inclusion_stats
#> - Fetching data from test_cohort_inclusion_stats
#> - Fetching data from test_cohort_summary_stats
#> - Fetching data from test_cohort_censor_stats
#> - Saving data to - C:\Users\ADMIN_~3\AppData\Local\Temp\Rtmpo1Frmz\file2cf86c4e47f3\EunomiaTestStudy\results_folder/CohortGeneratorModule/cg_cohort_inc_result.csv
#>     censoring 0 values (0%) from person_count because value below minimum
#> 
#> - Saving data to - C:\Users\ADMIN_~3\AppData\Local\Temp\Rtmpo1Frmz\file2cf86c4e47f3\EunomiaTestStudy\results_folder/CohortGeneratorModule/cg_cohort_inc_stats.csv
#>     censoring 0 values (0%) from person_count because value below minimum
#> 
#>     censoring 0 values (0%) from person_total because value below minimum
#> 
#> - Saving data to - C:\Users\ADMIN_~3\AppData\Local\Temp\Rtmpo1Frmz\file2cf86c4e47f3\EunomiaTestStudy\results_folder/CohortGeneratorModule/cg_cohort_summary_stats.csv
#>     censoring 0 values (0%) from base_count because value below minimum
#> 
#>     censoring 0 values (0%) from final_count because value below minimum
#> 
#> - Saving data to - C:\Users\ADMIN_~3\AppData\Local\Temp\Rtmpo1Frmz\file2cf86c4e47f3\EunomiaTestStudy\results_folder/CohortGeneratorModule/cg_cohort_censor_stats.csv
#>     censoring 0 values (NaN%) from lost_count because value below minimum
#> 
#> Saving cohort definition set
#> Generating negative control outcome cohorts
#>   |                                                                              |                                                                      |   0%  |                                                                              |======                                                                |   8%  |                                                                              |============                                                          |  17%  |                                                                              |==================                                                    |  25%  |                                                                              |=======================                                               |  33%  |                                                                              |=============================                                         |  42%  |                                                                              |===================================                                   |  50%  |                                                                              |=========================================                             |  58%  |                                                                              |===============================================                       |  67%  |                                                                              |====================================================                  |  75%  |                                                                              |==========================================================            |  83%  |                                                                              |================================================================      |  92%  |                                                                              |======================================================================| 100%
#> Executing SQL took 0.0363 secs
#> Generating negative control outcomes set took 0.08 secs
#> Counting cohorts took 0.0347 secs
#> Saving negative control outcome cohort definition
#> Saving negative control outcome cohort counts
#>     censoring 0 values (0%) from cohortEntries because value below minimum
#> 
#>     censoring 0 values (0%) from cohortSubjects because value below minimum
#> 
#> Cohort generation complete.
#> Results available at: C:\Users\ADMIN_~3\AppData\Local\Temp\Rtmpo1Frmz\file2cf86c4e47f3\EunomiaTestStudy\results_folder/CohortGeneratorModule
#> 
#> 
#> ── EXECUTION SUMMARY ───────────────────────────────────────────────────────────
#> 
#> ✔ CohortGeneratorModule  (Execution Time: 6.31 secs)

unlink(studyRootFolder, recursive = TRUE, force = TRUE)

^{Created on 2024-11-05 with reprex v2.1.1}

Note that when this option is set the output will emit a message:

Constructing cohort definition set and using strategus.cohortSqlOptimizationFunction

Tagging @msuchard for feedback

codecov · 2024-11-05T17:25:34Z

Codecov Report

All modified and coverable lines are covered by tests ✅

Project coverage is 98.61%. Comparing base (612859f) to head (a05ea07).
Report is 2 commits behind head on develop.

Additional details and impacted files

@@           Coverage Diff            @@
##           develop     #180   +/-   ##
========================================
  Coverage    98.61%   98.61%           
========================================
  Files           14       14           
  Lines         3401     3404    +3     
========================================
+ Hits          3354     3357    +3     
  Misses          47       47

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.

anthonysena · 2024-11-26T20:10:03Z

Merging this in an we can adjust later as needed.

Add option to hook into cohort SQL for customization

a05ea07

anthonysena linked an issue Nov 26, 2024 that may be closed by this pull request

Provide hook to optimize cohort SQL construction #179

Open

anthonysena merged commit ae52a3e into develop Nov 26, 2024
10 checks passed

anthonysena deleted the cohort-sql-hook branch November 26, 2024 20:10

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add option to hook into cohort SQL for customization #180

Add option to hook into cohort SQL for customization #180

anthonysena commented Nov 5, 2024

codecov bot commented Nov 5, 2024 •

edited

Loading

anthonysena commented Nov 26, 2024

Add option to hook into cohort SQL for customization #180

Add option to hook into cohort SQL for customization #180

Conversation

anthonysena commented Nov 5, 2024

codecov bot commented Nov 5, 2024 • edited Loading

Codecov Report

anthonysena commented Nov 26, 2024

codecov bot commented Nov 5, 2024 •

edited

Loading