Reuse entities btw DBs that use different @GeneratedValue strategies

[Jakub Kaláb]

Hi,

until recently our client used Oracle DB as a DBMS for their Enterprise Architect instances and our app was consuming the data using entities like this one:

```

@Entity
@DbName("ea")
@Table(name = "T_OBJECT")
data class EaObject(
    @Id
    @Column(name = "OBJECT_ID", nullable = false)
    @GeneratedValue(

        strategy = GenerationType.SEQUENCE,

        generator = "t_object_sequence_generator",

    )
    @SequenceGenerator(
        name = "t_object_sequence_generator",
        sequenceName = "OBJECT_ID_SEQ",
        allocationSize = 1,
    )
    var id: Long = 0,

    ...

}

```

As you can see, the entity uses sequences to populate the ids. But now the client migrated part of their data into a new MS SQL server instance. The MS SQL Enterprise Architect schema uses identities instead of sequences and as documented in the last section here https://ebean.io/docs/mapping/jpa/id#generated-value these strategies are actually the default ones for ebean on those platforms, so the first thing we tried to do was just to update the strategy param of the `@GeneratedValue` annotation to `GenerationType.AUTO`. This does indeed choose the sequence strategy in Oracle and identity strategy in MS SQL, the problem is the `@SequenceGenerator` annotation is ignored if the identity strategy is selected, which is good, but the `generator` parameter of the `@GeneratedValue` annotation is not (whereas it is indeed ignored if the `GenerationType.IDENTITY` is used), which results in an "java.lang.IllegalStateException: No custom IdGenerator registered with name t_object_sequence_generator" error on the MS SQL backend, or any other backend for which AUTO chooses the identity strategy.

My question then is, whether this is a bug, or if it's this is intentional and in that case, whether there's something I can do to tell ebean to ignore the `generator` parameter in case of AUTO (on platforms where this leads to identity strategy) as it does when IDENTITY is specified explicitly.

[Rob Bygrave]

> This does indeed choose the sequence strategy in Oracle and identity strategy in MS SQL

What version of Oracle? What version of SQL Server? What version of Ebean?

It's late so I'm off to bed and will pick this up tomorrow but ... in the "old days" with Oracle it indeed was preferable to use Sequences (and we need to talk about how sequences were designed to be used - "latch" cost - not shared - not increment by 1 ) and with MS SQL Server Identity was the preference.  "These days" it's actually the opposite of that and generally Identity is preferred by all databases except for MS SQL Server were sequences are preferred BECAUSE MS SQL Server doesn't NOT support use of GetGeneratedKeys with JDBC batch (and at this point I'd suggest they never will).

So as I see it the actual preference (if I had the choice) would be the opposite of what you are describing assuming recent versions of Oracle and MS SQL Server.

Anyway, there is more to say here but that will need to be tomorrow now.

Cheers, Rob.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/1324d972-4f47-4a53-ba57-a8d19446f6adn%40googlegroups.com.

[Jakub Kaláb]

Well, I took this information from the "Database platform" section at the end of this documentation page https://ebean.io/docs/mapping/jpa/id. We tested this on MS SQL where the AUTO strategy does pick IDENTITY (with the aforementioned problem). I didn't actually test that it picks SEQUENCE on Oracle, I just trusted the documentation. Nonetheless, our need to use sequences in Oracle and identities in MS SQL for this particular set of tables doesn't actually come from our preference, this decision is out of our control - it's simply what Enterprise Architect uses on these platforms, so even if this may not be the best choice of strategies nowadays, it's something we have to live with and it's actually fortunate for us that this is what ebean chooses there. As for the problem with GetGeneratedKeys, we actually did hit it and had to replace some of the `saveAll` calls by loops of `save` calls for now. It was actually a problem that I wanted to ask you about as well, once we overcome this more immediate one.

Returning to the original problem though, this basically boils down to the following two questions for us:

1) is the discrepancy btw the explicit `strategy = IDENTITY` vs identity being automatically chosen by `strategy = AUTO` a bug, or something you want to keep?

2) if it's not a bug, is our only option to duplicate the entities with different id annotations (and probably moving the rest to a common @MappedSuperclass), or is there a better way to support this use-case?

Also, since you mentioned that the preferred strategies actually change over time, is there some setting that would instruct ebean as to which strategy should be selected for AUTO on which db backend, so that we don't blindly rely on ebean's defaults?

Thank you, Jakub

[Rob Bygrave]

Lets make sure we are on the same page first.

What version of Oracle database?

What version of SQL Server database?

What version of Ebean?

Which Ebean Oracle platform is being used here?  (OraclePlatform, Oracle12Platform, Oracle11Platform)

Which Ebean Sql Server platform is being used here? (SqlServer17Platform, SqlServer16Platform)

Cheers, Rob.

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/1a1ccd7a-0776-4471-b8de-73c38552ed48n%40googlegroups.com.

[Rob Bygrave]

> Also, since you mentioned that the preferred strategies actually change over time, is there some setting that would instruct ebean as to which strategy should be selected for AUTO on which db backend, so that we don't blindly rely on ebean's defaults?

Yes. In the DatabasePlatform its the dbIdentity.setIdType(...)

For example in OraclePlatform we have:

https://github.com/ebean-orm/ebean/blob/master/platforms/oracle/src/main/java/io/ebean/platform/oracle/OraclePlatform.java#L34-L36

dbIdentity.setIdType(IdType.IDENTITY);  //  <!-- This means it prefers IDENTITY
dbIdentity.setSupportsSequence(true);    // <!-- ... but it also supports sequences if they are mapped and chosen to be used
dbIdentity.setSupportsIdentity(true);

For Oracle11Platform

https://github.com/ebean-orm/ebean/blob/master/platforms/oracle/src/main/java/io/ebean/platform/oracle/Oracle11Platform.java#L17

dbIdentity.setIdType(IdType.SEQUENCE);   //  <!-- This means it prefers SEQUENCE

> The MS SQL Enterprise Architect schema uses identities instead of sequences

We should identify which exact Ebean DatabasePlatforms are being used for Oracle and SQL Server to get on the same page.

Note that if we do: dbIdentity.setSupportsSequence(false); ... then it won't try to use database sequences which like one way to get what you want for the SQL Server case.

[Rob Bygrave]

> from the "Database platform" section at the end of this documentation page https://ebean.io/docs/mapping/jpa/id.

Unfortunately that documentation is badly out of date.

[Jakub Kaláb]

Hi,

thank you for your responses and sorry for the delay on my side.

> Lets make sure we are on the same page first.

>

> What version of Oracle database?

We're using Oracle 19c Standard Edition 2, release 19.0

> What version of SQL Server database?

SQL server 2019 (RTM-CU18)

> What version of Ebean?

ebean version 13.10.2

> Which Ebean Oracle platform is being used here?  (OraclePlatform, Oracle12Platform, Oracle11Platform)

in case of Oracle, we're not setting the platform explicitly, so I presume it's OraclePlatform

> Which Ebean Sql Server platform is being used here? (SqlServer17Platform, SqlServer16Platform)

in this case we're setting `databasePlatformName: sqlserver17`

Concerning the `setIdType` and `setSupports(Sequence|Identity)` calls, it's nice I can look in the code to see, what's actually being used in these cases, but this is not something I can set from the outside, when creating the data source/database configs, or is it?

In our case, what we need basically, is a way to tell ebean (when initializing the configs) "if I set (in the entity's `@GeneratedValue` annotation)  `strategy = GenerationType.AUTO` and the platform is <a> then use sequences and take the `generator` param and the `@SequenceGenerator` annotation into account, but if the platform is <b>, then use identity and ignore both the `@SequenceGenerator` annotations and the `generator` param of the `@GeneratedValue` annotation". As I said earlier, these two things are correctly ignored if I set `GenerationType.IDENTITY` explicitly, but if I set `GenerationType.AUTO` (and the identity strategy is used implicitly for that platform), the `@SequenceGenerator` is correctly ignored, but the `generator` param of `@GeneratedValue` is not, which I think is a surprising behavior dicrepancy and probably our main problem.

[Jakub Kaláb]

Actually, thinking about this some more, we don't even need ebean to do the conditional, I can do that in the config, since I'm creating it programatically anyway. So basically we only need to tell it which strategy to use for `GenerationType.AUTO` and if it's identity, to ignore the `generator` param.

[Rob Bygrave]

Just to say I haven't quite had time to reproduce this and look at the details yet.

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/99c7660c-d5d9-4c2f-a4ea-44b72f530da3n%40googlegroups.com.

[Jakub Kaláb]

Ok, is there something I can do to help? Should I create a minimal repo to reproduce?

[Rob Bygrave]

Did you create a minimal failing test case example project?