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.

• [x] Get a working Oracle image setup for docker (thanks @Curlack!): #702
• [x] Add said image to docker-compose.yml for integration tests runner (thanks @Curlack!): #702
• [x] Update connection strings in "PetaPoco.Tests.Integration\appsettings.json" and "PetaPoco.Tests.Integration\app.config" files (thanks @Curlack!): #702
• [x] 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!): #702
• [x] Create a base OracleTestProvider class in "PetaPoco.Tests.Integration\Providers\OracleTestProvider.cs" (thanks @Curlack!): #702
• [x] #706
• [x] #703
• [x] #707 Closes #255 Closes #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 https://github.com/CollaboratingPlatypus/PetaPoco/issues/691 Addresses #626 https://github.com/CollaboratingPlatypus/PetaPoco/issues/626 Addresses #613 https://github.com/CollaboratingPlatypus/PetaPoco/issues/613

— Reply to this email directly, view it on GitHub https://github.com/CollaboratingPlatypus/PetaPoco/issues/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 https://github.com/CollaboratingPlatypus/PetaPoco/issues/699#issuecomment-1743134387, 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

https://github.com/CollaboratingPlatypus/PetaPoco/blob/53e928c6dc44819aa834683abcd108029f960b77/PetaPoco/Database.cs#L990-L994

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 https://github.com/CollaboratingPlatypus/PetaPoco/issues/699#issuecomment-1767609872, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACI4HLCIEUUPMER6RMYYFDX75JWVAVCNFSM6AAAAAA5NZDEFWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONRXGYYDSOBXGI . You are receiving this because you were mentioned.Message ID: @.***>

[Ste1io]

If you're happy with ref cursor, I'll finish the rest of the build script then submit a PR.

Using Ref Cursor seems to me the least invasive option and most viable direction given the scope of this branch, and should be implementable by extending the current code without introducing any major breaking changes to the API.

I do think it would be prudent to leave the door open to the newer approach, however, I see that more as a future extension of the library's Oracle support than a requirement for official support.

You have more experience working with Oracle than me, also, so if you feel strongly either way, I'm keen to hear. I'd like to hear @pleb and @asherber's thoughts as well.

[Curlack]

I agree. On further investigation, it seems the C# implementation is the same either way. So I'm gonna go with that. Code base will, inadvertently, cater for the new way as well....developers dream!

On Wed, 18 Oct 2023, 7:35 am Stelio Kontos @.***> wrote:

If you're happy with ref cursor, I'll finish the rest of the build script then submit a PR.

Using Ref Cursor seems to me the least invasive option and most viable direction given the scope of this branch, and should be implementable by extending the current code without introducing any major breaking changes to the API.

I do think it would be prudent to leave the door open to the newer approach, however, I see that more as a future extension of the library's Oracle support than a requirement for official support.

You have more experience working with Oracle than me, also, so if you feel strongly either way, I'm keen to hear. I'd like to hear @pleb https://github.com/pleb and @asherber https://github.com/asherber's thoughts as well.

— Reply to this email directly, view it on GitHub https://github.com/CollaboratingPlatypus/PetaPoco/issues/699#issuecomment-1767675524, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACI4HPHF5JJEYRXBEPK3VDX75TDFAVCNFSM6AAAAAA5NZDEFWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONRXGY3TKNJSGQ . You are receiving this because you were mentioned.Message ID: @.***>

[pleb]

Love your work @Ste1io and @Curlack.

Don't forget to update the readme for oracle support and add yourselves to the PetaPoco is maintained and extended by line :)

[Ste1io]

Don't forget to update the readme for oracle support

There's a few other places throughout the docs as well I'm in the process of updating already, Will be sure to do that. @Curlack hammered this out fast, very excited with the progress.

[Curlack]

Always a pleasure. Glad I'm able to contribute to such an awesome codebase. 😎

[Curlack]

Quick update: I've added the integration tests for Oracle and ran the tests. Couple of them passed, but the majority failed. But we expected as much...

Looking into the first failure: Delete_GivenPoco_ShouldDeletePoco, very first line i.e. the Insert(person), I had Oracle.ManagedDataAccess.Client.OracleException : ORA-01465: invalid hex number. This is most likely due to RAW(16) data type used for storing Guid.

As I'm debugging, I saw the following insert statement:

INSERT INTO "PEOPLE" ("ID","FULLNAME","AGE","HEIGHT","DOB") VALUES (:0,:1,:2,:3,:4)

I found that the default implementation of EscapeSqlIdentifier in the PetaPoco.Providers.OracleDatabaseProvider is confusing the issue. An identifier is either delimited (surrounded by double quotes), which makes it case sensitive, or ordinary which has limits (must start with character, can only contain letters, digits and underscore), but is case insensitive.

public override string EscapeSqlIdentifier(string sqlIdentifier) => $"\"{sqlIdentifier.ToUpperInvariant()}\"";

By default oracle objects will be created in uppercase unless surrounded by double quotes. The the user is pretty much in complete control of naming tables, columns, etc.

Shortcut would be to just return the identifier that was passed in, but I can also use Regex to determine whether quotes are required and include them if not already included.

It seems the intention was to automagically give the user the correct syntax, so obviously I'm leaning towards the latter. The question is what do you guys prefer @Ste1io, @pleb, @asherber ?

PS: Technically this me getting distracted, so just wanted to mention this, put a FIXME or TODO and continue.

[Ste1io]

Great timing, @Curlack. Was just getting on to check if you had started on that or wanted me to get rolling on that.

Shortcut would be to just return the identifier that was passed in, but I can also use Regex to determine whether quotes are required and include them if not already included.

For now, I'm leaning towards adding a TODO, and changing the EscapeSqlIdentifier for Oracle to essentially be a no-op, as you mentioned. We can sort out how best to handle that between now and finishing the remaining tests. Personally, I'm not that keen on using regex for this; even if compiled; that should be a fairly simple check?

On that same note, I don't believe there's any error checking going on for multiple escaping, or if that's even something that's an issue to begin with. Figured I'd mention it nonetheless, since Oracle's use of quotes it seems could become problematic if double-escaped, though I could be wrong. e.g., (using a legal but discouraged table name): SELECT * FROM ""Foo Table"".

Worth noting that there is no escaping in the stored procedures in Oracle's sql build script, currently; if there's any in the create statements, that could be causing a fair few test failures as well.

[Ste1io]

btw @Curlack, so you're aware and to prevent any unnecessary work... I'm currently wrapping up some general cleanup across the tests in a separate private branch, proofing my doc comments from earlier, and also addressing my inline todo's (those that don't involve breaking changes).

[Curlack]

So the "invalid hex number" issue was due to the hypens in the value.

1. Either OracleDatabaseProvider needs to handle Guid a little different for RAW(16) data type i.e. Id.ToString("N") instead of Id.ToString() OR column type should be VARCHAR2(36) i.e. we're not going to support the recommended UNIQUEIDENTIFIER data type. Telling the OracleDatabaseProvider it HasNativeGuidSupport doesn't work.
2. DATETIME is also finicky. OracleDatabaseProvider needs to use TO_TIMSTAMP or TO_TIMESTAMP_TZ functions and be sure to at least abbreviate the month in the format string to not confuse day for month in less than 13 cases lol.

The following sql successfully inserted the record.

INSERT
    INTO PEOPLE
        (ID,
        FULLNAME,
        AGE,
        HEIGHT,
        DOB)
    VALUES
        ('9b3e6f88d0eb4fa387fad7a33b09d2d9',
        'Peta',
        18,
        180,
        TO_TIMESTAMP_TZ('1945-DEC-01 05:09:04 -8:00', 'yyyy-mon-dd hh24:mi:ss tzh:tzm'))

I'll make the "noop" change and push the tests as-is. Then we divide and conquer, unless you also want me to change the db scripts based on the route we decide to take for item number 1 above?

[Ste1io]

Divide and conquer works for me. If you don't see any issues with how the db scripts are currently, no need change them until we need to. I'd err on the side of "best practice" when it gets to db scripts in general.

VARCHAR2(36) sounds like the right approach to me if we're dealing with the GUIDs as strings... 9b3e6f88d0eb4fa387fad7a33b09d2d9 appears to be the hexadecimal conversion of the string. What about working with GUIDs as 16-byte arrays? That's commonly used for GUID storage, and might be what oracle is wanting in this case? [EDIT: Just noticed you had (16) after RAW, so I guess you already went that route initially).

Re timestamps, sounds like DateTimeOffset might be appropriate here, beyond the script usage... Need to double check how the other db's are handling datetime fields again as a refresher.

dateTimeOffset.ToString("yyyy-MMM-dd HH:mm:ss zzz", CultureInfo.InvariantCulture);

[Curlack]

Ok cool. I changed the db scripts to use VARCHAR2(36), I agree with you, keep it simple. We won't have any issues with primary keys and referential integrity, but it will be case sensitive. Small price to pay considering it's all handled by the system.

Good news: over 60% of the tests pass. Bad news: the ones that are failing, are tough.

[Ste1io]

Good news: over 60% of the tests pass. Bad news: the ones that are failing, are tough.

Easy. New game plan:

[Ste1io]

I'm just stepping out for dinner @Curlack; will sync up locally when I get back and run the tests so I can see where things stand. If there's any particular collection or test you want me to focus on lmk. I'll be free for the rest of the evening into late night after eating so hopefully we can make a dent in things.

[Ste1io]

Getting a later start than I intended, but I'm here and synced up. 70 of 302 failing tests... Not bad for a first go at it tbh. A couple first impressions after a very brief cursory glance over the failed tests...

Two patterns are standing out to me: storedproc tests are all failing with only a couple of exceptions, and nearly all dynamic type tests are failing. Not all dynamic type tests are failing, so I doubt dynamic type is the cause... But one commonality with those is statically typed table names in the SQL strings. This is something I've been fixing in my tests cleanup branch for static pocos (still local at the moment), but for dynamic objects you don't have much of a choice. But regardless, they all use the standard pascal casing, and based on Oracle docs, reiterated by you earlier in this thread, a fair number of those tests are probably using uppercase table names.

I'm going to follow my gut and explicitly quote table names in the script and see if it has any effects on the test run, and go from there. Also going to write some tests for the provider class escaping methods since there are none currently that I recall, and that needs ruled out as a possible factor at play here.

[EDIT] Confirmed Oracle's table name casing convention when unescaped is the cause of a number of the failures. We will def need to override the base provider's EscapeTableName method.

[Curlack]

For the case sensitivity issue. I had the scripts like that (quoted identifiers) before, not realizing the issue with EscapeSqlIdentifier. In hindsight, only uppercasing would've been the issue then. In order to cater for all these flavors of database objects, it's gonna get messy very soon.

From my experience using Oracle, Ordinary Identifiers are preferred i.e. no quotes. This makes writing any script less prone to case mismatches.

To give you a picture of the prevurbial can of worms, see the difference between select Column from Table vs select * from Table vs select "Column" from "Table".

The other sad part of the story is that it's not ideal to tell someone they have to change their whole database and all the applications talking to it, if they wanna use PP :( lol.

On Sun, 22 Oct 2023, 5:28 am Stelio Kontos @.***> wrote:

Getting a later start than I intended, but I'm here and synced up. 70 of 302 failing tests... Not bad for a first go at it tbh. A couple first impressions after a very brief cursory glance over the failed tests...

Two patterns are standing out to me: storedproc tests are all failing with only a couple of exceptions, and nearly all dynamic type tests are failing. Not all dynamic type tests are failing, so I doubt dynamic type is the cause... But one commonality with those is statically typed table names in the SQL strings. This is something I've been fixing in my tests cleanup branch (still local at the moment). But regardless, they all use the standard pascal casing, and based on Oracle docs, reiterated by you earlier in this thread, a fair number of those tests are probably using uppercase table names.

I'm going to follow my gut and explicitly quote table names in the script and see if it has any effects on the test run, and go from there. Also going to write some tests for the provider class escaping methods since there are none currently that I recall, and that needs ruled out as a possible factor at play here.

— Reply to this email directly, view it on GitHub https://github.com/CollaboratingPlatypus/PetaPoco/issues/699#issuecomment-1773982829, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACI4HOPEBEC7GXRGRYOAH3YASHG5AVCNFSM6AAAAAA5NZDEFWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONZTHE4DEOBSHE . You are receiving this because you were mentioned.Message ID: @.***>

[Curlack]

For the dynamic objects, we have a couple of options:

1. Make the underlying dictionary, of the ExpandoObject, case insensitive.
2. Produce the correct casing on creation by convention.
3. Query the expected casing from the result based on db implementation flavor.

I've had huge success using Expando instead of ExpandoObject in one of my projects. See https://weblog.west-wind.com/posts/2012/feb/08/creating-a-dynamic-extensible-c-expando-object . Not sure if this can help us here though.

Gonna do some research on this later.

Wanna tackle the paging queries needing a table alias first. Thinking to use string templating algorithm utility method in either new class or ParameterHelper utility.

On Sun, 22 Oct 2023, 10:13 am Curlack @.***> wrote:

For the case sensitivity issue. I had the scripts like that (quoted identifiers) before, not realizing the issue with EscapeSqlIdentifier. In hindsight, only uppercasing would've been the issue then. In order to cater for all these flavors of database objects, it's gonna get messy very soon.

From my experience using Oracle, Ordinary Identifiers are preferred i.e. no quotes. This makes writing any script less prone to case mismatches.

To give you a picture of the prevurbial can of worms, see the difference between select Column from Table vs select * from Table vs select "Column" from "Table".

The other sad part of the story is that it's not ideal to tell someone they have to change their whole database and all the applications talking to it, if they wanna use PP :( lol.

On Sun, 22 Oct 2023, 5:28 am Stelio Kontos @.***> wrote:

Getting a later start than I intended, but I'm here and synced up. 70 of 302 failing tests... Not bad for a first go at it tbh. A couple first impressions after a very brief cursory glance over the failed tests...

Two patterns are standing out to me: storedproc tests are all failing with only a couple of exceptions, and nearly all dynamic type tests are failing. Not all dynamic type tests are failing, so I doubt dynamic type is the cause... But one commonality with those is statically typed table names in the SQL strings. This is something I've been fixing in my tests cleanup branch (still local at the moment). But regardless, they all use the standard pascal casing, and based on Oracle docs, reiterated by you earlier in this thread, a fair number of those tests are probably using uppercase table names.

I'm going to follow my gut and explicitly quote table names in the script and see if it has any effects on the test run, and go from there. Also going to write some tests for the provider class escaping methods since there are none currently that I recall, and that needs ruled out as a possible factor at play here.

— Reply to this email directly, view it on GitHub https://github.com/CollaboratingPlatypus/PetaPoco/issues/699#issuecomment-1773982829, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACI4HOPEBEC7GXRGRYOAH3YASHG5AVCNFSM6AAAAAA5NZDEFWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONZTHE4DEOBSHE . You are receiving this because you were mentioned.Message ID: @.***>

[Curlack]

Currently the OracleDatabaseProvider is reusing BuildPageQuery from SqlServerDatabaseProvider this is also causing the issue mentioned in #691. I believe it should have its own implementation but got stuck between a rock and a hard place. This SO anwser mentions three (actually only two if you take his performance comment into consideration) methods for paging in Oracle.

If you're happy with us only supporting Oracle 12c and above, the implementation is easy:

SELECT [Distinct ]FullName, Age 
FROM People
[ORDER BY FullName]
OFFSET 5 ROWS FETCH NEXT 14 ROWS ONLY

It just works, doesn't care if you omit Order by or include a Distinct.

If we need to support from 11g, I'm not sure how we'll know which version is used in order to cater for both.

[Ste1io]

I've had huge success using Expando instead of ExpandoObject in one of my projects. See https://weblog.west-wind.com/posts/2012/feb/08/creating-a-dynamic-extensible-c-expando-object

Ironically, I've used the exact same concept in multiple projects myself also since reading Rick's article ~2018. 😆 I don't see that as being a viable solution in this case. either, though.

In hindsight, only uppercasing would've been the issue then. In order to cater for all these flavors of database objects, it's gonna get messy very soon.

Agreed...realized this fairly quickly and didn't go down that route.

If we need to support from 11g, I'm not sure how we'll know which version is used in order to cater for both.

11g has been obsolete for years, with 12c at EOL since 2022 at this point. Even 12c support seems like an unnecessary burden if it brings with it any additional complexity to the library. Any thoughts here @pleb and @asherber?

1. Make the underlying dictionary, of the ExpandoObject, case insensitive.
2. Produce the correct casing on creation by convention.
3. Query the expected casing from the result based on db implementation flavor.

Ignoring case seems it could introduce some edge cases since it would no longer follow the same casing rules a strongly typed classs's properties follow. It seems mapping the poco's property names (which would match the dynamic object keys) to the db column names by convention, possibly even introducing a config option for column data (and atttribute) might be worth considering. The escaping methods would need to lookup the cached column mapping to determine if the string should be escaped, or not. Otherwise we're still stuck here, with one - but not both - being valid:

DB.Insert(_note);
var tblNote = DB.Provider.EscapeTableName(pd.TableInfo.TableName);
var entity = DB.Fetch<dynamic>($"SELECT * FROM {tblNote}").First();

entity.Text += " was updated"; // must be escaped PascalCase for class Note
DB.Update("Note", "Id", (object)entity); // must be UPPERCASE for default convention w/out quotes

Definitely needs some more thought on my end also. Will look at it some more this afternoon/evening once I'm home for the day.

[Curlack]

Thinking to use string templating algorithm utility method in either new class or ParameterHelper utility.

Scratch that, I'm just gonna override the QueryTests methods.

[Ste1io]

Scratch that, I'm just gonna override the QueryTests methods.

For the tests using a db-specific syntax, that's usually best. Some tests switch on the provider type inline if it's a minor change that doesn't necessitate overriding the method completely; either is fine.

[Curlack]

Quick question: Should output parameters also be listed in the LastCommand string? I was able to include the RefCursor for the stored procs, but got "NullReferenceException" since output parameter doesn't have a value. So was thinking:

public string FormatCommand(IDbCommand cmd)
{
    //Exclude output parameters from the arguments list
    return FormatCommand(cmd.CommandText, (from IDataParameter parameter in cmd.Parameters where parameter.Direction.HasFlag(ParameterDirection.Input) select parameter.Value).ToArray());
}

[Curlack]

Quick question: Should output parameters also be listed in the LastCommand string? I was able to include the RefCursor for the stored procs, but got "NullReferenceException" since output parameter doesn't have a value. So was thinking:

public string FormatCommand(IDbCommand cmd)
{
    //Exclude output parameters from the arguments list
    return FormatCommand(cmd.CommandText, (from IDataParameter parameter in cmd.Parameters where parameter.Direction.HasFlag(ParameterDirection.Input) select parameter.Value).ToArray());
}

It was the method below it causing the issue. It's when we call args[i].GetType().Name on a null. I'll just append "Unknown Type" to prevent it from falling over.

[Curlack]

How can I prevent the RefCursor parameter from being parameterized, i.e. "p_out_cursor" becomes ":p_out_cursor" via AddParameter() method?

I've tried reflection inside OracleDatabaseProvider during PreExecute, but that felt like a hack, so I scrapped that idea.

Now I'm trying something in Database, looks better, but not sure what the logic should be. I can't check for output parameter 'cause some out parameters might need the param prefix e.g. SqlServer I think. I can check the DbType (RefCursor is 121) or I can check the name. Here's what I have so far:

// Support passed in parameters
if (value is IDbDataParameter idbParam)
{
    if (cmd.CommandType == CommandType.Text)
        idbParam.ParameterName = cmd.Parameters.Count.EnsureParamPrefix(_paramPrefix);
    else if (idbParam.ParameterName?.StartsWith(_paramPrefix) != true &&
        //Don't touch output cursor parameters. This is specific to Oracle stored procedures
        !string.Equals(idbParam.ParameterName, "p_out_cursor", StringComparison.OrdinalIgnoreCase))
    {
        idbParam.ParameterName = idbParam.ParameterName.EnsureParamPrefix(_paramPrefix);
    }

    cmd.Parameters.Add(idbParam);
}

Am I on the right track or is there a better way?

[Curlack]

How can I prevent the RefCursor parameter from being parameterized, i.e. "p_out_cursor" becomes ":p_out_cursor" via AddParameter() method?

I've tried reflection inside OracleDatabaseProvider during PreExecute, but that felt like a hack, so I scrapped that idea.

Now I'm trying something in Database, looks better, but not sure what the logic should be. I can't check for output parameter 'cause some out parameters might need the param prefix e.g. SqlServer I think. I can check the DbType (RefCursor is 121) or I can check the name. Here's what I have so far:

// Support passed in parameters
if (value is IDbDataParameter idbParam)
{
    if (cmd.CommandType == CommandType.Text)
        idbParam.ParameterName = cmd.Parameters.Count.EnsureParamPrefix(_paramPrefix);
    else if (idbParam.ParameterName?.StartsWith(_paramPrefix) != true &&
        //Don't touch output cursor parameters. This is specific to Oracle stored procedures
        !string.Equals(idbParam.ParameterName, "p_out_cursor", StringComparison.OrdinalIgnoreCase))
    {
        idbParam.ParameterName = idbParam.ParameterName.EnsureParamPrefix(_paramPrefix);
    }

    cmd.Parameters.Add(idbParam);
}

Am I on the right track or is there a better way?

Looks like the overall rule should be to not touch output parameters. Please correct me if I'm wrong here.

[Curlack]

Looks like the overall rule should be to not touch output parameters. Please correct me if I'm wrong here.

Ok scratch all of the above. Seems like it's Oracle that's full of it. It's correct that Oracle parameters are prefixed by a colon EXCEPT for stored procedures! I've been struggling with this one the whole day now....A day in the life of a developer hahaha

[Ste1io]

Just getting back to pc so catching up. Oracle package has an OracleType enum for ref params iirc. I was thinking add that as an overload.

[Curlack]

Just getting back to pc so catching up. Oracle package has an OracleType enum for ref params iirc. I was thinking add that as an overload.

I've added the OracleDbType.RefCursor command type, if that's what you mean. Not sure what you mean by "add that as overload" though.

[Curlack]

FYI: Some goodies coming your way. See #707, hope it doesn't clash with some of the work you've been doing. Just glad I found the issue with the stored procedures. Anyway I'm gonna call it a night.

PS: I believe the only failing tests left are due to mismatching names :-)

[Ste1io]

Sounds great @Curlack. I'll look over things today. My reference to overloading was so that ExecuteReader could be handed in the name of the RefCursor, along the lines of what I mentioned in this earlier comment. You may have already handled this more elegantly in your PR, though.

[Ste1io]

@Curlack I may have figured out how to handle the escaping bit.. And it's actually fairly elegant if I may say so myself. Hopefully I'll have something later tonight for you to look at.

[Curlack]

In the beginning it sounded like a bad idea to ignore case, based on the reasons you mentioned here, but if Oracle is in case-insensitive mode, the code will just be modeled to the database and this will only be used for dynamic pocos. So I'm thinking we only need to cater for some one more dynamic type that allows you to specify whether to ignoreCase on creation and we should be done. This can even be the default dynamic type, but that would be a breaking change, just saying.

Consider the following OracleExpandoObject class:

public sealed class OracleExpandoObject : DynamicObject, IDictionary<string, object>
{
    private readonly IDictionary<string, object> _dictionary;
    public OracleExpandoObject() : this(false) { }
    public OracleExpandoObject(bool ignoreCase)
    {
        _dictionary = ignoreCase ?
            new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase) :
            new Dictionary<string, object>();
    }

    #region IDictionary<string, object> Implementation
    public object this[string key] { get => _dictionary[key]; set => _dictionary[key] = value; }
    public ICollection<string> Keys => _dictionary.Keys;
    public ICollection<object> Values => _dictionary.Values;
    public int Count => _dictionary.Count;
    public bool IsReadOnly => _dictionary.IsReadOnly;
    public void Add(string key, object value) => _dictionary.Add(key, value);
    public void Add(KeyValuePair<string, object> item) => _dictionary.Add(item);
    public void Clear() => _dictionary.Clear();
    public bool Contains(KeyValuePair<string, object> item) => _dictionary.Contains(item);
    public bool ContainsKey(string key) => _dictionary.ContainsKey(key);
    public void CopyTo(KeyValuePair<string, object>[] array, int arrayIndex) => _dictionary.CopyTo(array, arrayIndex);
    public IEnumerator<KeyValuePair<string, object>> GetEnumerator() => _dictionary.GetEnumerator();
    public bool Remove(string key) => _dictionary.Remove(key);
    public bool Remove(KeyValuePair<string, object> item) => _dictionary.Remove(item);
    public bool TryGetValue(string key, [MaybeNullWhen(false)] out object value) => _dictionary.TryGetValue(key, out value);
    IEnumerator IEnumerable.GetEnumerator() => ((IEnumerable)_dictionary).GetEnumerator();
    #endregion

    public override bool TryGetMember(GetMemberBinder binder, out object result) =>
        _dictionary.TryGetValue(binder.Name, out result);
    public override bool TrySetMember(SetMemberBinder binder, object value)
    {
        _dictionary[binder.Name] = value;
        return true;
    }
}

It's not as badass as ExpandoObject, but support the ways we're using it e.g.

IDictionary<string, object> a = new OracleExpandoObject(true);

/*
a.Add("Test", "One");
a.Add("TEST", "Two"); //<- This will still error as per usual <DuplicateKey>
*/

a["Test"] = "One";
a["TEST"] = "Two";
Console.WriteLine(a["TesT"]);

dynamic b = a;
b.Test = "Three";
b.TEST = "Four";
Console.WriteLine(b.TesT);

/* Output:
 * Two
 * Four
 */

Off course it's not polished yet and we'll have to do load tests to see how it behaves under pressure. It shouldn't perform worse than ExpandoObject and should be thread safe (I'm seeing LockObject in MS code).

As for the Core, we're gonna need a provider flag to IgnoreCase for Oracle. We could maybe support some custom value in the connectionstring. This flag would then be passed in the constructor when creating an instance. There's also couple of places (4 it seems) where we check if the type is ExpandoObject, that must also take OracleExpandoObject into consideration.

So this is the extend of my research. Let me know what you guys think. Piece out.

[Curlack]

I just realized that whether the columns are case sensitive or not, it's not PP's problem. We're already covering all the basis and can handle both scenarios. It's up to the user to query the db poco, whether all caps or specific case, dynamic or strongly typed, correctly. All we need to do is fix how the tests are handling the db results and maybe add a case sensitive table for Oracle specific test and call it a day. Even escaping the names must remain noop to leave the user in full control (off course he can still override it if he wants to).

[Curlack]

@Curlack I may have figured out how to handle the escaping bit.. And it's actually fairly elegant if I may say so myself. Hopefully I'll have something later tonight for you to look at.

I'm anxious to see what you came up with. Were you able to make progress with this? Because as you may have seen, I'm in two minds atm. 🤪

[Curlack]

If we let PP handle all of this, the only issue is a poco without explicit columns.

[Ste1io]

@Curlack I may have figured out how to handle the escaping bit.. And it's actually fairly elegant if I may say so myself. Hopefully I'll have something later tonight for you to look at.

I'm anxious to see what you came up with. Were you able to make progress with this? Because as you may have seen, I'm in two minds atm. 🤪

Have had little time this week thanks to a heavier-than-usual load from my usual work, @Curlack. I'll be free again this weekend. Sorry for the delay on my end. :(

[Curlack]

I'm seeing how far I can take the ExpandoPoco idea mentioned earlier. Looks like I would need an IgnoreCase property in both the Provider (for EscapeSqlIdentifier) and Mapper (for GetFactory). Is there a way to sync these objects? For example OracleDatabaseProvider will override IgnoreCase (from IProvider), so when Database realizes there is no mapper, it defaults to OracleConventionMapper and set its IgnoreCase property to be the same as OracleDatabaseProvider's. In this case the mapper would have liked IgnoreCase to be a constructor parameter, but has to remain an object with a parameterless constructor due to type constraints.

I managed to get 13 of the 15 failing tests to pass, but not yet happy with the changes, hence no PR yet.

[Ste1io]

Been a hellacious week with work; appreciate the patience. Will catch up with things here today.