Oracle and case sensitive

[Tom Marien]

Hey,

There is a very interesting pull request see https://github.com/schambers/fluentmigrator/pull/331

This would make oracle case sensitive, anyone has a oppinion to share about this one. It seems like a very decent fix, but could probably break existing use cases

Regards

Tom

[Shane Walters]

I think it broke everything for me.  Sorry that I'm just now seeing it, but I'm only now trying to upgrade FM to the latest. 

The latest totally ignores my 'VERSIONINFO' table and builds a completely new, empty 'VersionInfo'.  What a fail.

 Is there any way to turn off the quotes?

-Shane

[Shane Walters]

After some digging, I think we can either:

1. Add a new Oracle Factory the retains the old behavior of no quotes. All we have to do is create a new OracleQuoterNoQuotes that overrides all the QuoteTableName, etc and use that in the Generator instead of the default one.  What to call the provider?  OracleUpperCaseNames? OracleNoQuotedIdentifiers?

2. Add some kind of switch that we can look at when creating the Generator instance in the factory.  Since this switch would be specific to Oracle, I doubt it's the way to go.  Are any other databases needing this ability?

Any opinions?  Any existing Oracle project that upgrades to this quoting version is in for a nasty surprise.

-Shane

[Tom Marien]

Shane,

Hi, i prefer a runner switch instead of another provider, but thanks for all the feedback :)

Op vrijdag 22 maart 2013 00:25:57 UTC+1 schreef Shane Walters het volgende:

[Shane Walters]

Julius, I tried that.  It fixes the VersionInfo table issue, but existing migrations will not work because of case differences in column and table names.

Tom, does FM have any notion of custom properties that we can pass in and then access from the generators?  Something like /p:OracleCaseSensitive=false;AnotherProperty=whatever

Thanks,

-Shane

On Friday, March 22, 2013 8:40:03 AM UTC-5, Julius Ræstad wrote:

Maybe I'm wrong, but can you not provide the VersionInfo table name by setting custum meta data (see https://github.com/schambers/fluentmigrator/wiki/Create-custom-metadata-for-the-VersionInfo-table) and set your version table to VERSIONTABLE?

Then when you migrate it will look at the correct table?

-Julius

[Julius Ræstad]

Yes, you are right. 

You would also have to update all your migrations to be uppercase. 

This just illustrates why we created the patch, we expected our table and column names to be the way we wrote them in the migration. 

You probably wrote the your migration with mixed case?

[Shane Walters]

Yes, we used mixed case in our migrations because we are targeting SqlServer and Oracle.  When working with Oracle, I just took the uppercase identifiers as another Oracle peculiarity.  I didn't even know you could quote the identifiers to preserve case. Live and learn :)

I think your patch is the best way and should be the default, but it does break backwards compatibility.

As soon as I know the best way to pass a switch to the factory, I'll submit a patch that allows for the old behavior.

-Shane

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to fluentmigrator-goog...@googlegroups.com.
To post to this group, send email to fluentmigrato...@googlegroups.com.
Visit this group at http://groups.google.com/group/fluentmigrator-google-group?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Shane Walters]

Julias,

Once you start using quoted tablenames, do you have to use them everywhere after that?  Do you have to put quotes around table and column names in Select statements?

Select "ColumnA" from "TableA"?  From my testing, you must do so.

If so, I take back my statement about quoted being the default.  I don't know many developers that would like being required to quote all table and column names. What a nightmare to do just so your table names appear in a nice case.

With unquoted identifiers, I can code

Select ColumnA from TableA or

Select COLUMNA from TABLEA or whatever.  case doesn't matter.

Any Oracle devs on here with an opinion?  My vote is we role back this commit.

-Shane

[Julius Ræstad]

You might be right about that. 

We do not write our sql statements directly, we use NHibernate for that.

Maybe there should be a way to change Quoter or an option as you described earlier?

-Julius

[Shane Walters]

Julius,

We also use NHibernate.  Are you specifying the quotes in Nhibernate mappings? If so, why don't you also use the quotes in FluenMigrator?

Create.Table("\"QuotedTable\"")

                .WithColumn("\"QuotedColumn\"");

From NHibernate docs:

5.3. SQL quoted identifiers

You may force NHibernate to quote an identifier in the generated SQL by enclosing the table or column name in backticks in the mapping document. NHibernate will use the correct quotation style for the SQL Dialect(usually double quotes, but brackets for SQL Server and backticks for MySQL).

<class name="LineItem" table="`Line Item`">
    <id name="Id" column="`Item Id`"/><generator class="assigned"/></id>
    <property name="ItemNumber" column="`Item #`"/>
    ...
</class>

[Julius Ræstad]

Shane,

We found that both NHibernate and FluentMigrator dealt with quoting differently depending on database.

If I remember correctly, NHibernate quoted SQL Server and SQLCE, but not Oracle and Postgres (I even think they quoted columns and not tables, or the other way around). Now since everything behaved differently we had to take some action. FluentMigrator had quoting for many (all?) databases already, so we thought it be natural that Oracle got quoting in place.

We use FluentNHibernate to map our models. FluentNHibernate has a INamedStrategy you can implement where you can add your own quotes. So we did that for Postgres and Oracle. Now we are able to write mapping and migrations for all the databases (allmost, still have Oracle sequencing) and support Oracle, Postgres, SQL Server and SQL CE.

Quoting in the migrations would depend on the type of database, so we wanted to avoid that.

I'm not sure that we tried the NHibernate SQL quoted identifiers (I will look into that). And this seems like a good idea for FluentMigrator also? 

If identifiers are enclosed with ' the quoter adds quotes otherwise not.

-Julius

[Julius Ræstad]

Just tested NHibernate SQL quoted identifiers by adding ' quotes for all our databases using INamingStrategy, and it does not work for SQL server and SQL CE. 

-Julius

[Shane Walters]

Julius,

I changed the default OracleQuoter to ignore quotes for identifiers and I renamed the original to QuotedIdentifiersOracleQuoter.  I changed all the unit tests to use both quoters.  I have not yet changed the integration tests.

To implement this change, we'll need to add a switch to specify that you want to use the QuotedIdentifiersOracleQuoter. I'm open to ideas on how to implement that.  Probably the easiest way would be a new property on RunnerContext.

You can see the commits here:

https://github.com/swalters/fluentmigrator/commit/75b6c82ec5f0d59d28891acd86d2d4760f1630a1

https://github.com/swalters/fluentmigrator/commit/d5e976610cd52ca340d70003ef11d6d583efd952

Shane Walters

Singletree Technologies | ( O: 601.344.1634 | Ë  C: 601.498.3555  | Ê F: 908.934.9220 

On Tue, Mar 26, 2013 at 3:46 AM, Julius Ræstad <julius....@gmail.com> wrote:

Just tested NHibernate SQL quoted identifiers by adding ' quotes for all our databases using INamingStrategy, and it does not work for SQL server and SQL CE. 

-Julius

--

[Julius Ræstad]

Shane,

I think the default should be with quotes since that is the way the other databases operates.

I do not have any strong opinions on the switch implementation. Should this be implemented on the other databases which are also case sensitive?

Maybe someone else have a suggestion on that?

-Julius

[Shane Walters]

I can work either way as long as it's an option, but I've seen many Oracle business systems over my career and I've never seen one that uses quoted identifiers.  In addition, NHibernate doesn't use the quotes by default so that tells me the standard is probably no quotes.

-Shane 

-Julius

--

[Julius Ræstad]

Shane,

You got some strong arguments. I think you might be right using unquoted as default. This also prevents others from experiencing what you are experiencing.

I might not remember all the details correctly (concering NHibernate defaults etc), but I remember we tried a lot of things and we saw a lot of strange behaviors. 

I think most of it boiled down to trying to support both Postres and Oracle, which where implemented differently in FluentMigrator (I think also in NHibernate), and behaves different internally concerning case sensitivity.

-Julius 

[Daniel Lee]

Asking around it seems that most Oracle developers uppercase everything and pretty much no one quotes. Like Shane says.

Generally, Oracle pull requests are really hard to review as I (personally) know nothing about Oracle and don't use it. We could really use some help with those if there are any volunteers. This issue is typical. I had no idea that the standard was to uppercase all variable names.

I run the integration tests for all the databases except Oracle when I review a pull request. Do you run the Oracle tests, Tom? So together with Jet, the Oracle has the worst test coverage and could really use a bit of TLC.

//Daniel

2013/3/26 Julius Ræstad <julius....@gmail.com>

-Julius 

--

[Tom Marien]

Hi

When pulling this pull, i ran all tests on oracle, but there are not much of them.

I think fluentmigrator should grow to a common base framework and provider nugets, like we allready discussed. But to do that we need owners for all the providers, if we do not find them we will cannot support them. I am even thinking of separating the releases and codebases from eachother

Regards

Tom

Op dinsdag 26 maart 2013 19:25:12 UTC+1 schreef Daniel Lee het volgende:

Asking around it seems that most Oracle developers uppercase everything and pretty much no one quotes. Like Shane says.

Generally, Oracle pull requests are really hard to review as I (personally) know nothing about Oracle and don't use it. We could really use some help with those if there are any volunteers. This issue is typical. I had no idea that the standard was to uppercase all variable names.

I run the integration tests for all the databases except Oracle when I review a pull request. Do you run the Oracle tests, Tom? So together with Jet, the Oracle has the worst test coverage and could really use a bit of TLC.

//Daniel

2013/3/26 Julius Ræstad <julius....@gmail.com>

Shane,

You got some strong arguments. I think you might be right using unquoted as default. This also prevents others from experiencing what you are experiencing.

I might not remember all the details correctly (concering NHibernate defaults etc), but I remember we tried a lot of things and we saw a lot of strange behaviors. 

I think most of it boiled down to trying to support both Postres and Oracle, which where implemented differently in FluentMigrator (I think also in NHibernate), and behaves different internally concerning case sensitivity.

-Julius 

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to fluentmigrator-google-group+unsub...@googlegroups.com.

[Shane Walters]

I hesitate to say I could fulfill the role of an Oracle expert.  My skills with Oracle are wide, not deep.  Since we use NHibernate to support SqlServer and Oracle from the same code base, we avoid stored procedures and any database specific code.

However, I would be glad to help setup an Oracle instance for integration tests.  I just need to know where we will do it and have the proper credentials.

How should we proceed with our current issue?  Should I add another runner context property for QuotedIdentifiers?

Thanks,

-Shane

To unsubscribe from this group and stop receiving emails from it, send an email to fluentmigrator-goog...@googlegroups.com.

[Tom Marien]

Shane,

I think a switch would be best, maybe a generic approach like a switch with name providerswitches="QuotedIdentifiers=true;somethingelse=1" could keep the runnercontext unpolluted, what do you think ? But i my honest oppinion if all oracle users agree we could revert the pull

Regards

Tom

Op woensdag 27 maart 2013 16:44:21 UTC+1 schreef Shane Walters het volgende:

I hesitate to say I could fulfill the role of an Oracle expert.  My skills with Oracle are wide, not deep.  Since we use NHibernate to support SqlServer and Oracle from the same code base, we avoid stored procedures and any database specific code.

However, I would be glad to help setup an Oracle instance for integration tests.  I just need to know where we will do it and have the proper credentials.

How should we proceed with our current issue?  Should I add another runner context property for QuotedIdentifiers?

Thanks,

-Shane

On Wed, Mar 27, 2013 at 5:27 AM, Tom Marien <tomm...@gmail.com> wrote:

Hi

When pulling this pull, i ran all tests on oracle, but there are not much of them.

I think fluentmigrator should grow to a common base framework and provider nugets, like we allready discussed. But to do that we need owners for all the providers, if we do not find them we will cannot support them. I am even thinking of separating the releases and codebases from eachother

Regards

Tom

Op dinsdag 26 maart 2013 19:25:12 UTC+1 schreef Daniel Lee het volgende:

Asking around it seems that most Oracle developers uppercase everything and pretty much no one quotes. Like Shane says.

Generally, Oracle pull requests are really hard to review as I (personally) know nothing about Oracle and don't use it. We could really use some help with those if there are any volunteers. This issue is typical. I had no idea that the standard was to uppercase all variable names.

I run the integration tests for all the databases except Oracle when I review a pull request. Do you run the Oracle tests, Tom? So together with Jet, the Oracle has the worst test coverage and could really use a bit of TLC.

//Daniel

2013/3/26 Julius Ræstad <julius....@gmail.com>

Shane,

You got some strong arguments. I think you might be right using unquoted as default. This also prevents others from experiencing what you are experiencing.

I might not remember all the details correctly (concering NHibernate defaults etc), but I remember we tried a lot of things and we saw a lot of strange behaviors. 

I think most of it boiled down to trying to support both Postres and Oracle, which where implemented differently in FluentMigrator (I think also in NHibernate), and behaves different internally concerning case sensitivity.

-Julius 

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to fluentmigrator-google-group+unsubs...@googlegroups.com.

To post to this group, send email to fluentmigrato...@googlegroups.com.

Visit this group at http://groups.google.com/group/fluentmigrator-google-group?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Julius Ræstad]

Hi,

We need the case sensitivity, so I vote for an option. 

Besides I do not think this will be the last time options like these will be needed. 

-Julius

[Shane Walters]

Most of the work has been done already to allow the case sensitive.   

If the group chooses to allow it as an option, all that needs to be done is coding that part. Julius, would your team be willing to make those changes?  You can take my latest changes (minus the last commit which was a 4.0 project change) from https://github.com/swalters/fluentmigrator/commits/master and add in the switch.  All you need to do is swap in the appropriate Oracle Quoter.

So, by default, it will run without quoted identifiers.  With the switch, it will quote the identifiers.

Thanks,

-Shane

--

You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to fluentmigrator-goog...@googlegroups.com.

[Julius Ræstad]

I think we should be able to do that.

Julius

[Julius Ræstad]

We are quite busy this week, but I think we are able to do this next week.

-Julius

[Julius Ræstad]

I have made the option part (take a look here https://github.com/JuliusOnGitHub/fluentmigrator/compare/master...OptionalOracleQuoter), but I am not sure if I have implemented it in the way you want.

Please take a look and provide some feedback.

If it looks ok, I will create a pull request.

-Julius