[FEATURE] Make a Default Dummy SQL Representation of an OMOP CDM Database

[TheCedarPrince]

One workflow that I often need is the ability to create SQL queries out of OMOPCDMCohortCreator as more of a generator rather than a strict interface to a given database. This is because some of my work involves other programming languages like R or Python. So rather than using Julia directly, I am using Julia to generate a bunch of helpful queries that can then be ported to other languages and run within one's respective ecosystem.

The problem is, is that I have to first generate a connection to some bespoke database, generate connection details, and then use my desired commands. Instead, we should use OMOPCommonDataModel as a basement level package to have FunSQL representations of a standard OMOP CDM. Then, this can be used in the backend of OMOPCDMCohortCreator as a representation for developing against a standard OMOP CDM database.

I think it will also help with #50 as we could then have defaults as needed. Then, when a new connection is made, we only have to worry about creating a connection. Possibly the only thing left to worry about is just making an initial connection object.

[Jay-sanjay]

Hello @TheCedarPrince Sir I would be to happy to help for this issue , can you tell what I need to do...
As far as I see you want to use the OMOPCommonDataModel package as a foundational layer to represent the standard OMOP CDM.

what can I do for it ...?

[TheCedarPrince]

Hey @Jay-sanjay! Sorry for the delay in a response -- been busy settling into a new apartment and graduate program amongst other things. :)

That said, SUPER excited to hear about your interest! In short, here is what I was imagining to tackle this issue:

1. See the following issue discussion: [FEATURE] FunSQL Representation of Schemas OMOPCommonDataModel.jl#37
2. Understand how OMOPCommonDataModel.jl works:
   1. How does it do auto-generation of a given schema version?
   2. Add support for OMOP CDM version 5.4
   3. Create a function in OMOPCommonDataModel.jl that allows one to generate information for a given OMOPCDM version
3. Hook into the autogeneration process to generate FunSQL representations of the schema

I don't think 2.2 and 2.3 are strictly necessary as a prototype, but this would be the general process. An example of a FunSQL representation of a table would be something that looks like this:

import FunSQL: SQLTable
person = SQLTable(schema = "public",
                         name = "person",
                         columns = ["person_id", "year_of_birth"])

As I am writing this, I realize this is rather complex with a lot of moving parts frankly. If you're interested still, happy to jump on a call and I could show you more about all these pieces and how they come together. My email is jacobszelko@gmail.com and we could coordinate a call there if you want to reach out. 😄

Thanks!

~ tcp 🌳

[divital-coder]

Hiya Jacob, here from the slack channel,
I commit to embark on this issue for a meaningful contribution.
Within a day or 2 I will be trying to figure out how to cement a working solution.

[TheCedarPrince]

Sounds good @divital-coder ! I am a little busy right now with some end of semester work but will follow-up further as soon as possible (both here and on Slack). Feel free to post questions here as you continue exploring this problem!

~ tcp 🌳