Add support for Oracle: implementation and tests

[Ste1io]

PetaPoco has "unofficially" supported Oracle for quite some time now... I'd like to implement the integration tests for Oracle so that can change finally, and we can claim official support without a big "but" after. This will also allow us to confidently investigate, address, and close multiple issues that are unclosed, and can most likely be readily fixed provided tests are possible.

Happy to spearhead this myself alongside the remaining test refactoring.

• Get a working Oracle image setup for docker (thanks @Curlack!): Feature/oracle support #702
• Add said image to docker-compose.yml for integration tests runner (thanks @Curlack!): Feature/oracle support #702
• Update connection strings in "PetaPoco.Tests.Integration\appsettings.json" and "PetaPoco.Tests.Integration\app.config" files (thanks @Curlack!): Feature/oracle support #702
• Create a build script at "PetaPoco.Tests.Integration\Scripts\OracleBuildDatabase.sql" and port all common scripts contained in the others that are supported by Oracle (thanks @Curlack!): Feature/oracle support #702
• Create a base OracleTestProvider class in "PetaPoco.Tests.Integration\Providers\OracleTestProvider.cs" (thanks @Curlack!): Feature/oracle support #702
• Add remaining Oracle tests #706
• Upgrade tests projects target framework to net472,netcoreapp3.1 #703
• Fix some more failing integration tests #707
  Closes Page query is very slow when use a long sql statement #255
  Closes PetaPoco. Providers: When using Oracle, calling FetchAsync will throw exception #691
• Fix remaining failing integration tests

Addresses #626
Addresses #613

[Curlack]

Busy setting up everything. Have used Oracle for a while couple of years back. Might be ready to lend a hand soon 😁

…

On Sat, 30 Sep 2023, 10:38 pm Stelio Kontos ***@***.***> wrote: PetaPoco has "unofficially" supported Oracle for quite some time now... I'd like to implement the integration tests for Oracle so that can change finally, and we can claim official support without a big *"but"* after. This will also allow us to confidently investigate, address, and close multiple issues that are unclosed, and can most likely be readily fixed provided tests are possible. Happy to handle this myself alongside the remaining test refactoring. Closes #691 <#691> Addresses #626 <#626> Addresses #613 <#613> — Reply to this email directly, view it on GitHub <#699>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AACI4HPKR372WYN433R4R5LX5B7LJANCNFSM6AAAAAA5NZDEFU> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[Ste1io]

Let me know how setup goes with your local environment, @Curlack, and if you encounter anything you think should be added to the updated wiki page.

Given your background in Oracle, I don't mind passing this one over to you if you want, or collaborating together on it, whichever is best for you.

[Curlack]

Setup was smooth. After following all the instructions. The code compiled and all tests passed 😎. Collaborating at first at least. Need to find my bearings

…

On Mon, 02 Oct 2023, 4:33 pm Stelio Kontos ***@***.***> wrote: Let me know how setup goes with your local environment, @Curlack <https://github.com/Curlack>, and if you encounter anything you think should be added to the updated wiki page. Given your background in Oracle, I don't mind passing this one over to you if you want, or collaborating together on it, whichever is best for you. — Reply to this email directly, view it on GitHub <#699 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AACI4HOH4MRAE6EZ47CIEWTX5LGCZAVCNFSM6AAAAAA5NZDEFWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONBTGEZTIMZYG4> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[Curlack]

I'm gonna need the docker setup for oracle. I can see a comment for oracle pointing to "sath89/oracle-12c:latest" image, but to be honest, I have no clue how all the docker stuff works. Will figure it out as I go. Not sure if we'll start with 12c and go up to 23c or for all intents and purposes 12c will always be enough.

PS: Also wanted to ask if this will be our primary medium of communication and (since we're 6 hours apart) what times would suite you best?

[Ste1io]

For the docker images, most of it's been trial and error and checking docs... the sathu89 image (that commented service predates my recent PR) is no longer available when I checked (apparently due to a dmca from Oracle ~2019). afaik, 19c is current LTS, so that would be my suggestion. Including 12c for backwards compatibility with the older Oracle provider (the orphan in the root directory, I suppose) may be necessary.

Matrix testing hasn't really been brought up yet, but you'll notice I added comments about firebird also as the current image used for it is a couple major versions behind, like 12c.

Just noticed I neglected to update the .vscode extensions with it, but if you install the the Docker extension for VSCode, testing images is fairly straightforward simply by right-clicking on the compose.yml after making your changes and selecting the services you want to fire up.

[Curlack]

So far I've been hitting brick walls. I'm trying to build a docker image using Docker Images for Oracle repository. Should be simple only need to execute buildContainerImage.sh file, but keep getting "not found" error in terminal. My VSCode session is connected to WSL and Desktop Docker is running. Not sure what I'm missing.
Want to build the "Oracle Database Free" (23c) image with 19c compatibility. Any pointers would be welcome.

[Curlack]

Success at last! Used installation described on the Oracle Container Registry. Opted for version 23c (free). Also installed Oracle SQLDeveloper ide (less "consoley" hehehe). Turns out compatibility has nothing to do with database features, only disk format. Also I just needed to read the same thing enough times for it to "click" :)

The command I ended up using is:

docker run --name oracle-free-v23c -d -p 1522:1521 -p 5700:5500 -e ORACLE_PWD=petapoco -e ORACLE_CHARACTERSET=AL32UTF8 container-registry.oracle.com/database/free:latest

One thing I'm not sure of, is how the docker-compose.yml file should be changed.

Now that I'm able to connect I can maybe begin with small scaffold of oracle test group and work my way up as I go.

[Ste1io]

Nicely done @Curlack. Translating from cmd line to yml is typically fairly straightforward (-e as environment variables, -p being ports). Something like this should work, added under the services...untested:

oracle-free-v23c:
  image: container-registry.oracle.com/database/free:latest
  container_name: oracle23c_petapoco
  environment:
    - ORACLE_PWD=petapoco
    - ORACLE_CHARACTERSET=AL32UTF8
  ports:
    - "1522:1521"
    - "5700:5500"

[Ste1io]

Now that I'm able to connect I can maybe begin with small scaffold of oracle test group and work my way up as I go.

The SqlServer and SQLite tests, and the accompanying test providers provide a fairly straightforward bare-bones multi-driver implementation. As with the other tests, instantiate a class inheriting from one of the abstract classes to activate those tests; override or skip any as needed, or add additional test methods if specific to oracle.

If you want me to help scaffold an initial oracle test class just lmk.

I'm waiting on @pleb to review my changes to the tests, but you should be safe rebasing onto the changes from my feature branch for #701 (Ste1io:Ste1io/separate-dbms-driver-tests) and building up the oracle tests from the changes I did there.

[edit] PR approved ^, thanks @pleb! :)

@Curlack, I've setup an upstream branch, feature/oracle-support, which you can fork to your local. Any changes you or I make can be PR'd back to this central feature branch. This should streamline our collaboration and help keep things organized.

I'll merge any changes on Development into this feature branch as needed, so you can fetch and rebase onto this branch (upstream/feature/oracle-support) to keep in sync.

[Curlack]

Quick update from my side. Btw, thanks for the branch, good idea.
docker-compose.yml updated and working (thanks for the tip). Beginning to understand docker a little better now.
app.config and appsettings.json file changes in place and oracle database connection successful. Using Oracle.ManagedDataAccess 21.12.0 nuget package.
Created OracleTestProvider.cs, generated a setup script and "et voila!" I was ready to start converting build scripts to their oracle equivalent....until I got to the Stored Procedures :-(.

CREATE PROCEDURE dbo.SelectPeople
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM [People]
END

This simple procedure returns a dataset as if it's a table. Only difference is we execute it instead of selecting from it. After doing some reading and even consulting Bing AI, they all mention an output parameter of type Ref Cursor, but the caller has to explicitly bind the parameter.

As of v12c and above they boast to be able to return query results to the caller implicitly via DBMS_SQL.RETURN_RESULT and DBMS_SQL.GET_NEXT_RESULT.

I suppose if it's possible to do by any means, it's fine as long as the end result prevents the caller from modifying the underlying table, which is why these kind of procs exist in the first place imo. Just having bit of a tough time trying to get around this one.

Any ideas or alternatives guys?

[Ste1io]

Nicely done, @Curlack.

My initial inclination would be to implement an Oracle-specific override of

PetaPoco/PetaPoco/Database.cs

Lines 990 to 994 in 53e928c

	/// <param name="commandType">The type of command to execute.</param>
	/// <param name="sql">The SQL statement.</param>
	/// <param name="args">The parameters to embed in the SQL statement.</param>
	/// <inheritdoc cref="IQuery.Query{T}(string, object[])"/>
	protected virtual IEnumerable<T> ExecuteReader<T>(CommandType commandType, string sql, params object[] args)

Ref Cursor would need passed in, and of course this would also require overrides of the Fetch/Query helpers as well, Then inside, push the cursor param containing the output from the stored proc into our command...

A naïve approach being something to the affect of

using (var cmd = CreateCommand(_sharedConnection, commandType, sql, args))
{
    if (_provider is Providers.OracleDatabaseProvider && commandType == CommandType.StoredProcedure)
    {
        var refCursorParam = /* create parameter here */;
        cmd.Parameters.Add(refCursorParam);
    }
    //...

So given the stored procedure in question,

CREATE OR REPLACE PROCEDURE SelectPeople (p_cursor OUT SYS_REFCURSOR)
AS -- or IS, not sure off hand
BEGIN
    OPEN p_cursor FOR
    SELECT * FROM People;
END;

We'd end up with something akin to

[Fact]
public virtual void QueryProc_NoParam_ShouldReturnAll()
{
    var results = DB.QueryProc<Person>("SelectPeople", "p_cursor").ToArray();
    results.Length.ShouldBe(6);
}

[Ste1io]

docker-compose.yml updated and working (thanks for the tip). Beginning to understand docker a little better now.
app.config and appsettings.json file changes in place and oracle database connection successful. Using Oracle.ManagedDataAccess 21.12.0 nuget package.

When you get a chance, if you want to open a PR against the upstream's oracle feature branch with these changes (excluding the unfinished changes being discussed, ofc), I'll pull them in so we're both working in the same test environment, and lock your contribution with these changes.

[Curlack]

What I was aiming for was to also have working build scripts in place before the PR. So the database is not only setup, but also in starting state. Off course a lot of the tests might/will fail, but at least step 1 in typical TDD done :-) If you're happy with ref cursor, I'll finish the rest of the build script then submit a PR. Thanks so much.

…

On Wed, 18 Oct 2023, 6:15 am Stelio Kontos ***@***.***> wrote: docker-compose.yml updated and working (thanks for the tip). Beginning to understand docker a little better now. app.config and appsettings.json file changes in place and oracle database connection successful. Using Oracle.ManagedDataAccess 21.12.0 <https://www.nuget.org/packages/Oracle.ManagedDataAccess> nuget package. When you get a chance, if you want to open a PR against the upstream's oracle feature branch with these changes (excluding the unfinished changes being discussed, ofc), I'll pull them in so we're both working in the same test environment. — Reply to this email directly, view it on GitHub <#699 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AACI4HLCIEUUPMER6RMYYFDX75JWVAVCNFSM6AAAAAA5NZDEFWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONRXGYYDSOBXGI> . You are receiving this because you were mentioned.Message ID: ***@***.***>