Add SQL query verification

[anwarfg]

Is this the right issue type?

• Yes, I'm planning work for this project team.

Summary

Add a method of SQL query verification for the user query. One way may be to create a small db on the client side, like duckDB, using the OMOP DDL, then running a limit 0 query to get the metadata, number of columns and data types. The required values for these can be stored in the analysis class.

Can validate:
Column count - SELECT COUNT(*) FROM information_schema.columns
Data types - Check column types in result set
Column names - Verify expected column names exist
Table existence - Tables referenced exist
Column existence - Columns referenced exist
SQL syntax - Query parses and executes
JOIN validity - JOIN conditions are valid
WHERE clause - Conditions reference valid columns

Acceptance Criteria

• Function returns whether data type is correct.
• Function returns whether number of columns is correct.
• Tests pass.
• Documentation added.

Tasks

• Duck db database creation using omop dd.
• SQL queries can be run against the db.
• Data returned can be checked against the analysis type.
• Unit tests.
• Documentation.

Confirm creation

• Ready to create this deliverable?