Support cache when creating a sequence

[bruceliu2008]

Hi,
May I know are there any plan for ef core to support cache size for sequence, something like this:
modelBuilder.HasSequence("OrderNumbers", schema: "shared")
.StartsAt(1000)
.IncrementsBy(5)
.CacheSize(10);

Thanks,
Bruce'

[ilmax]

If nobody is working on this, I would like to give it a try

[divega]

Sounds good @ilmax. Just keep in mind that at this stage we may choose not to take the PR in 2.1.

[ilmax]

Ok, no problem! I'm working on it now

[ilmax]

I made some progress on this, some unit tests are still missing but the implementation should mostly be done. I have a some questions though and would like to get some feedback because adding cache size support ended up in a breaking change, I've also left a couple of TODOs around.
What's the best way to proceed with this? Should I open a WIP PR or wait till 2.1 is out of the door?

[smitpatel]

@ilmax - Feel free to open PR. We can always merge it after 2.1 or merge into feature branch if 2.1 is not fully done.

[koenmetsu]

@ilmax if things are working out, could you try fitting in "NO CACHE" as well?

[Compufreak345]

@ajcvickers As you removed the help wanted tag - can you tell something about the current state of this issue? Thanks!

[ajcvickers]

@Compufreak345 There were some inconsistencies in how help wanted was being used. This issue is marked as good first issue, indicating the implementation should not be too difficult for a community member to attempt.

Beyond that, this issue is in the Backlog milestone, which means we still plan to implement it in some future release, but not for the next release.

[bikbov]

@ajcvickers @smitpatel Hi. I'd be happy to implement this feature.

sequence documentation for for sql server https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver16

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ]
[ ; ]

Defaults to CACHE.
If the cache option is enabled without specifying a cache size, the Database Engine will select a size.

Cache statement will be added when translation to sql in the end.
CacheSize(long? cacheSize = null) - signature

There are two variants

1. cache enabled, but cache size is not specifying
   modelBuilder.HasSequence("TSequence", schema: "shared")
   .CacheSize();
   will be translated to "CREATE SEQUENCE TSequence CACHE"

2. cache enabled, cache size is specifying
   modelBuilder.HasSequence("TSequence", schema: "shared")
   .CacheSize(5);
   will be translated to "CREATE SEQUENCE TSequence CACHE 5"
   cacheSize = 1 corresponds to NO CACHE

[bikbov]

Perhaps another function name would be appropriate

[roji]

cacheSize = 1 corresponds to NO CACHE

In SQL Server, it's possible to use both CACHE 1 and NO CACHE - I think we should allow the user to express both via the Fluent API (even if we're not aware of a functional difference).

Also, our Fluent API methods generally start with a verb (e.g. UseThis, HasThat). So I think we should have:

.UseCache(5) // CACHE 5
.UseCache() // CACHE (default)
.UseNoCache() // NO CACHE

Finally, note that SQL Server, PostgreSQL and Oracle support this option, so we should add support in Relational (and not just in the SQL Server provider).

[bikbov]

Great! Thanks a lot for feedback

[ajcvickers]

Reverting this from 9. We will revisit in the future and agree on how it should be implemented.

[fsbflavio]

Is it possible to include an option for "ORDER" and "NOORDER"?. Oracle has this option too:

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ORDER | { NOORDER }]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ]
[ ; ]

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-SEQUENCE.html