Support for Oracle DB

[kenshuri]

Dear developers of {dittodb},

I've been using {dittodb} for mocking an Oracle database. Even though it is not listed as one of the supported DB, it is possible to use it, even though it needs some manual actions.
The issue is that the names of the fixtures generated while recording are not the same ones as those that {dittodb} is looking for when running the tests. However, thanks to the error messages (such as Error: Couldn't find the file DATA-HANDLER/SELECT-556993.R in any of the mock directories.), it is possible to rename the fixtures so that the global process would work. This would work as long as the number of generated fixtures during the recording remain small (less than 3 fixtures).
When the numbers of generated fixtures gets bigger, it become really difficult and tedious to match the generated fixtures to the good name.
So I have three questions:

• Is this bug expected as Oracle DB are not listed as supported?
• Would you think of a workaround (better that trying to match the names manually) to make {dittodb} works with Oracle DB?
• If changing names is the best workaround, would you think of a tip to make this easier (especially when more than 3 fixtures are generated)?

{dittodb} is really perfect for what I need to do (I had troubles with my RSLite test DB), and it would be great if I could use it to its max potential!

Thanks,

PS: it is the first time I create an issue in github, please tell me if I should do otherwise for this kind of questions.

[jonkeane]

Hello, thanks for opening an issue and sorry this took me a little while to get to. I haven't tested against Oracle mainly because I don't have a license / access to one. Though now that I dig a bit, it looks like there is a free(ish) edition in XE that I might be able to wire up in CI+tests / confirm what's going on here. There's even a few docker images that should make setting this up (relatively) easy.

I will see what I can do about adding that into the CI, though I'm not sure when I'll have a chance to do so. If you have experience with this, I would be more than happy to help point you in the right direction / review a PR.

[kenshuri]

Hello,

Thanks a lot for your answer and for taking the time to dig into it. Unfortunately, I do not think I will be able to help myself, having never used docker and being a "simple package developer" only, I think this is a bit too advanced for me...

However, please let me know if I can be of any help in describing more precisely the current behavior I can observe.

[jonkeane]

Ok, I've got the docker setup more-or-less working, though I'm not familiar enough with Orcale's syntax to quickly get through the setup steps that are necessary in the time I had to look at this. Would it be possible for you to translate the two sql scripts that we use in our CI to something that would work with orcale?

https://github.com/ropensci/dittodb/blob/main/db-setup/postgres-reset.sql and https://github.com/ropensci/dittodb/blob/main/db-setup/postgres-nycflights.sql are the two that need translating (The second one is basically "just" a database backup that gets instantiated, so should be slightly easier for me to recreate, but the setup steps weren't super obvious what the oracle equivalents of those are (or if it's a limitation of XE that they weren't working).

Another thing that we could do is if you could send me debug output when recording your queries. To do that you can set_dittodb_debug_level(1) and then run the recording step.

[kenshuri]

Hello, thanks for your work on this issue.

I will start working on the translation of these two scripts and share it with you as soon as possible in addition to the debug output!

[alexverse]

Hello and thanks for this great package!
Any news on this issue?

[kenshuri]

No, I've been stuck on this, limited by my too small Oracle knowledge... So far, I've used the workaround I described in the first message.
Please let us know if you'd like to start the translation of the scripts as proposed by jonkeane.

[rlaragonIvie]

Hi @jonkeane, I'm looking for a way to test connections to Oracle Database. I can clone my database to a MariaDB but it is really tedious. I've seen in the conversation there are two scripts to translate to helping. Are they still useful?

[jonkeane]

I am happy to try and get better support for Oracle, but to do that we need a way to test that support on our github actions setup. I found a docker image that can be used without a license + have a script that will set that up (see this file + branch for that: https://github.com/ropensci/dittodb/blob/oracle-xe/db-setup/oracle-xe-docker-container-only.sh )

But after that's running (which I've got fine) the setup scripts that are used with other databases don't work directly (likely due to slight differences in Oracle's SQL syntax — see the quote below for links, etc.)

https://github.com/ropensci/dittodb/blob/main/db-setup/postgres-reset.sql and https://github.com/ropensci/dittodb/blob/main/db-setup/postgres-nycflights.sql are the two that need translating (The second one is basically "just" a database backup that gets instantiated, so should be slightly easier for me to recreate, but the setup steps weren't super obvious what the oracle equivalents of those are (or if it's a limitation of XE that they weren't working).

If someone with more familiarity with Oracle SQL could help me translate those two sql scripts to something that works with Oracle (XE) that will make it possible to test the possible changes and make them in dittodb. Is anyone here familiar enough with Oracle SQL to get these setup scripts working on that Oracle XE docker container?

[jonkeane]

Here's that issue I was talking with you about @ablack3