Leaf with Oracle

[william-shuman]

Hi,

Has anyone had success getting Leaf to work with a clinical Oracle database? I've stood up an environment but I'm running into a few issues, some of which I have been able to resolve.

1.) System.Data.OracleClient is linked against a windows shared library (libocc) so it won't work in a Linux environment. I was able to switch to Oracle.ManagedDataAccess.Core to resolve that issue.
2.) Parts of the queries are generated using LINQ to SQL which generates invalid Oracle syntax
3.) Other code paths add generated SQL with invalid Oracle syntax, i.e. invalid characters and keywords

I've been able to run queries by modifying the generated SQL before it is executed but I wanted to reach out to see if others have come up with more robust solutions for a Oracle clinical database.

Thanks!
Billy

[ndobb]

Hi @william-shuman,

Thanks so much for the info above. I'm not aware of other sites using Oracle (yet), but would also welcome anyone doing so to jump in here.

Unfortunately, Oracle is perhaps the one RDMBS which my team has almost no direct experience with, so the recent support we added for it was based entirely on online documentation regarding syntax, libraries, etc. I tried using Docker with Oracle for a bit but eventually gave up as, if I recall correctly, it was trickier to setup than I expected, but I don't recall the details.

Long story short I'd be happy to work with you on each of these items, and thank you already doing some fixes. If you can let us know specific syntax issues you're running into I can work to correct those. We could also talk via email or have a Zoom debug if that would be helpful.

Best,
-nic

[nixj14]

@ndobb,

I work with @william-shuman at Cincinnati Childrens. From our investigation so far, there appears to be a few places where the implementation breaks down:

1. Use of "AS" statement with table aliases. This is not supported in Oracle.
2. Use of special characters in alias names "_S1234" does not work but "S1234" does.

We've identified a handful of changes in Leaf that would be needed. Additionally it appears that the urit/composure project would also need to be updated as well so that it generates SQL that is Oracle compliant.

We're continuing to review. Assuming we are able to get Leaf to be fully functional, we'll create a Issue/PR with the changes for both Leaf and Composure.

[ndobb]

@nixj14 you bet, that works. Those issues make sense. And you're right, Composure attempts to write only ANSI-compliant SQL, though I had assumed AS was so but it may not be.

[william-shuman]

@ndobb it looks like the version of Composure published on GitHub is not version 1.1.0, when I use what is on GitHub as the Leaf dependency I am seeing API changes. Is that expected? Should I base my changes on what is published and refactor Leaf to those changes?

[ndobb]

Hi @william-shuman, re: Composure version, it should be v1.1.

Regarding Composure changes, I'd suggest making a fork and focusing on this method. To my mind how we handle syntax really comes down to

1. What does the ANSI standard say?
2. Does the syntax work with all RDBMSs Leaf supports? (and if not, how painful is the workaround)

EDIT: oh, re: GitHub version do you mean literally the repo at https://github.com/uwrit/composure? You may be right but that seems strange to me. Let me take a look and see if I didn't properly update that for some reason.

[william-shuman]

@ndobb if you look at the source for ExpressedColumn, the method signature is (string name, IExpression expression), in the Leaf code anything using ExpressedColumn is using (IExpression expression, string name).

I'm happy to update the code the the new api but wanted to make sure I'm using the correct version.

[ndobb]

Nice find @william-shuman . My apologies for somehow not keeping the GitHub repo in sync.

I've just

1. Updated the GitHub repo (see ExpressedColumn constructor)
2. Pushed a new version (1.2) to Nuget (though this is not yet used by any Leaf instance, it's available and was a good refresher for me of how to do so)

[ndobb]

Hi @nixj14 and @william-shuman - just checking in. How are things going with the Oracle testing? Anything my team can advise on?

[william-shuman]

We were able to make some progress, but eventually hit a blocker around Oracle not having a guid type and them being represented as strings. This caused issues with the way LeafType.cs flowed and ended up returning a type of String for things that needed to be Guids in the code. We also ran into an issue where the oracle connection was used to query the app schema tables, but I didn't get too much time to dig into that.

Eventually we just switched to using SQL Server for the data.

[Suzeeray]

Hi, Has anyone gotten the LEAF app to run against an ORACLE database (OMOP Schema)? We are scoping out installing LEAF on Kubernetes against ORACLE. Is a Windows VM better? Are the errors discussed above regarding Oracle Syntax easily adjusted or am I looking at a large time investment to get this app working against ORACLE? Any help at all is appreciated.
All the best,
Sue Myers

BI Appplication Engineer
Research Analytics and Big Data, BIAA
Oregon Health & Science University (OHSU)

[ndobb]

Hi @Suzeeray, as of version 3.11.3, the syntax issues discussed above should no longer be an issue. Example from prod UW Leaf instance:

• The FROM <table> <alias> syntax no longer has "AS" before <alias>
• There are no underscores in the <alias>

Those changes were made to make Leaf play well with Oracle. However as we don't use Oracle at UW, we unfortunately can't easily test beyond this.

What specific errors are you running into? As to Windows vs. Linux, either should be fine, though we internally at UW generally use Linux and can better advise in that context.

Best,
-nic

[Suzeeray]

Hi Nic, Thank You so much for your quick reponse. I am doing research at this point to determine how much time and cost there might be for my organization, (OCTRI group @ OHSU). We want to stand LEAF up against an ORACLE db and put the API/WEBSERVER on a KUBERNETES container (linux). If there is any special accomodations that need to be made for using ORACLE (OMOP) as the source database that would also be good to know ahead of time. Much Thanks --Sue

[ndobb]

Hi Sue - happy to help. Running Leaf with Kubernetes in a Linux environment is definitely doable (our friends at Johns Hopkins run Leaf this way, for example). Also, OMOP and Leaf work together just fine and we can help advise on setup (we have scripts for populating the Leaf UI for an OMOP DB, for example).

The main question is Oracle. While we've done our best to address every issue with using Oracle mentioned by the community (such as the syntax issues above), as I said, we don't use Oracle here at UW so I can't say for sure everything will work smoothly, as I can't test it myself. Oracle at this point may work fine, but I just don't know.

If for any reason Oracle didn't work, do you have a fallback RDBMS option? (eg PostgreSQL, MySQL, MSSQL, etc)