Hangfire: Port hangfire to Oracle

[andrecarlucci]

Hi @odinserj,

I'm already working on an Oracle provider for Hangfire.
I'm doing it completely independent of the SqlServer provider, but many classes and interfaces are exactly the same.
To make it right, I would like to create a new Project called Hangfire.Sql (or Hangfire.GenericDatabase) and put all common classes and interfaces there. Both Hangfire.SqlServer and Hangfire.Oracle (and other future Dapper supported databases) would reference this dll and override some methods.

Do you think this is something you would merge in the future (if you like the code)? Because if you have different plans, I will let my provider completely independent (and with duplicated code from SqlServer) so I can continue to get your updates.

Cheers!

[frankhommers]

I have done the same for PostgreSql (Npgsql). Mostly by replacing just the SQL, but some things are very different, like the WriteOnlyTransaction and the stuff for the Install.sql script.

[andrecarlucci]

@frankhommers, did you create another project to share the common classes or put everything in your new project?

[andrecarlucci]

Just saw your source code. Exactly the way I'm doing with Oracle.
I feel bad having to copy/paste so much code and of course any change to hangfire core will probably break our code :(

[frankhommers]

I copied the SqlServer provider and changed all the SQL. But looking back I'd rather would have made an IDbConnection Storage provider also ;-)

I was thinking to dynamically loading the SQL based on the ProviderName of the connection, instead of having multiple implementations.

[andrecarlucci]

That's the originating thought of this issue.
IDbConnection and overrides to change the sql to be executed.
That way you could still have "UseOracle()", "UsePostgre()", "UseSqlServer()", etc

[andrecarlucci]

What would be a good name for the Common project?

• Hangfire.Sql
• Hangfire.Databases
• Hangfire.Foo
  ...

[frankhommers]

I wouldn't use overrides with my idea. (Or very few at most). I would just have UseIDbConnection().

So I think Hangfire.IDbCon(nection) ;-)

[andrecarlucci]

How would you execute the correct sql? Injecting a container for all sqls or some class that resolves the sql at runtime? How would you do that resolution?
Overrides gives us more flexibility to make appropriate changes to the specific database, but it's more code. Maybe your idea is better, can you explain a bit?

[andrecarlucci]

Maybe because we are creating our data access layer on top of dapper, the common project could be Hangfire.Dapper.

[odinserj]

I don't think generic database project will be useful. Let's look at the main classes:

• SqlServerConnection – merge and scope_identity are common for this class and related to SQL Server.
• SqlServerWriteOnlyTransaction is full of merge commands also.
• SqlServerDistributedLock uses SQL Server's sp_getapplock.
• SqlServerMonitoringApi heavily uses SQL Server's row_number.
• SqlServerJobQueue uses output clause.

The Dapper library does not abstract anything, it provides the same things as ADO.NET, but simplifies de-materialization. I also want to ILMerge Dapper with the assembly to remove unneeded dependencies.

The only things that can be moved to the common project are DTO objects and IPersistentJobQueue interfaces. But I'm in doubt they worth adding a separate project.

[odinserj]

Guys, you are doing awesome things! @frankhommers, I'm still can not reach and overview your contribution (hope I'll do it within next two weeks), but thank you for it!

[andrecarlucci]

Hi @odinserj, thanks for joining the party :)

SqlServerConnection could be a class that extends DapperSqlConnection providing SqlServer specific sqls. OracleConnection would do the same and replace these scope_identity with insert returning Id. Oracle also has merge, but it's a little bit different and we could change on the child class as well.

SqlServerDistributedLock is 100% SqlServer, but since it's an interface, we can return a Fake implementation and put on the docs that this provider doesn't support that.

SqlServerMonitorApi is easy for Oracle, override the methods and use Oracle rowid the same way.

SqlServerJobQueue output can be replaced with insert/select.

I don't want to put things on Hangfire.Core, I want to create a project with some abstract classes, entities and interfaces called Hangfire.Dapper/Whatever and have a standard way to add new databases.

[andrecarlucci]

We also have to create a ISchemaBuilder interface and inject it in every provider so small differences in the schema can be treated separated (like "not supported by Oracle" very big names of some SqlServer indexes).

[odinserj]

Hm, it seems to me that I understood you. Common project will contain almost all the classes except query strings, right?

[frankhommers]

@andrecarlucci: My idea is indeed injecting an SQL Statement container, so it would have less code to maintain. I managed to work around SCOPE_IDENTITY and ROW_NUMBER in the PostgreSql version and I think it will be possible for others too.

I've written SqlServerDistributedLock for PostgreSql ( https://github.com/frankhommers/Hangfire.PostgreSql/blob/master/src/Hangfire.PostgreSql/PostgreSqlDistributedLock.cs ). The same solution would work for most databases (also SqlServer and Oracle).

@odinserj: Thanks!

@odinserj: That's the idea yes. But I think we need to tweak the common project until all the databases we want are supported, and still support overriding for the exceptions.

[andrecarlucci]

@frankhommers Got you. I'll make some tests here, but I think we will need more than just swapping sqls in some situations.
@odinserj Exactly. Sqls, schema builders (or use Migrations) and small tweaks.

What would be a good name for this common project? I'm using Hangfire.Dapper, but I'm not very happy about it.

[luisrudge]

name it just like microsoft.
Hangfire.BaseSqlProviderForDifferentDatabases

[andrecarlucci]

IDbConnection has no EnlistTransaction :(

[frankhommers]

@andrecarlucci I didn't use TransactionScope at all because it isn't "stable" yet in Npgsql. Instead I created transactions manually in the provider. Take a peek at my code how it's done.

[andrecarlucci]

@frankhommers Tks!

[odinserj]

Yet another idea for naming – Hangfire.SqlStorage

[andrecarlucci]

Hi @odinserj,
Good name. I already started with Hangfire.Sql, but I can rename when I finished everything.
The SqlServer part is all separated and now I'm fixing Oracle sqls.

Take a look at https://github.com/andrecarlucci/Hangfire/tree/Hangfire.Oracle and tell me what you think.

Cheers!

[andrecarlucci]

@frankhommers @odinserj Pull request ready. Please, comment :)

[bhugot]

When are you going to merge with Hangfire.Oracle so we can use nuget package.

@andrecarlucci there is some error in your implementation on query with SUM() because if there is no line the result is null not 0. I did a regex replacement to SUM(value) by NVL(sum(value),0)

[luisrudge]

:+1:

[andrecarlucci]

Hi @bhugot, I already fixed that locally, I plan to push it tomorrow. If you find more issues, please, let me know.

[bhugot]

@andrecarlucci

I have an error 'ORA-08177 - can't serialize access for this transaction' when doing

for (int i = 0; i < 100; i++)
{
BackgroundJob.Enqueue(() => Console.WriteLine("Test"));
}

Any chance you know how to change this?

[bhugot]

When are you merging so it will be easier to play with new versions?

[jods4]

I'm interested in testing/debugging this. Is there any way to grab compiled bits or should I build it myself from the sql-oracle branch?

[jods4]

@andrecarlucci @odinserj any status update regarding the oracle provider? The oracle branch seems stale and 7 months behind master. According to the discussion above there were some fixes to be merged... Is it abandoned?

I wanted to use this, but I would like to know what the official status is before committing to it.

[agabuza]

Hi, @odinserj @andrecarlucci. Thanks for great job. Any updates? maybe you need some help porting hangfire to Oracle?

[jods4]

@mason-mcglothlin @AGRocks
Last time I heard from @odinserj, he didn't have the time and resources to commit to an Oracle provider himself.

He would like to see it happen and will support anyone's effort to do so, but he won't do the implementation, testing and support.

[gandarez]

I'm on it

[agabuza]

@gandarez, Great. Any help needed?

[gandarez]

@AGRocks I meant following this topic :+1:

[yasin75]

Hello,

can anyone help to understand the current status of the Oracle port please?

Ive seen the following branch , but not sure how what delta with the main (v1.5):
https://github.com/HangfireIO/Hangfire/tree/sql-oracle

thanks,
Yasin

[yasin75]

I've tried the Oracle port of hangfire (v1.3), but unfortunately it was not working as expected. (at least when the 1,5 version runs against SQL Server)

So I am now directing to another solution: use Quartz.NET:
http://www.quartz-scheduler.net/

[wmkDev]

Tested source from: https://github.com/HangfireIO/Hangfire/tree/sql-oracle and got it working after change the query's as @bhugot described (NVL).

@bhugot when execute your sample:
for (int i = 0; i < 100; i++) { BackgroundJob.Enqueue(() => Console.WriteLine("Test")); }
jobs get randomly serialization error (3 errors of 100), maybe it was related to this: http://stackoverflow.com/questions/2326750/randomly-getting-ora-08177-with-only-one-active-session

@yasin75 , i made a new database for the test and dashboard show version: 1.0.0.0.

i'll keep testing this week, but looks like everything is working fine.

[KaraokeStu]

Are there any further updates on this, including any modifications that are needed for the SQL scripts?

I'm really keen to get a Hangfire.Oracle for version 1.6

[KaraokeStu]

@wmkDev Please can you detail what you did to get this working? Thanks!

[stefannikolei]

any progress or roadmap for the Oracle Version????

[worthy7]

What's the story with this, started in 2014 and its 2017 now. Give us what you've got

[jods4]

@Worthy7
At one point I got a clear message from @odinserj: he doesn't have the resources and the will to test, maintain and support an Oracle provider.

He's willing to encourage a community member to do it (support, free Enterprise licence) if someone wants to step up.

Bottom line: don't expect an official Oracle provider any time soon.

[worthy7]

What does free licence mean... Can I take it elsewhere after the project is done? :D They are expensive.. lol

Anyway we'll see. It might be nice to have it since oracles prices are mega, but MS isn't the best either. We are trying to survive off oracle SE.

I'll think about it.

[jods4]

@Worthy7
I meant that odinserj told me he would provide an Hangfire Enterprise licence to whomever would officially maintain an Oracle provider for Hangfire.

Note that this was a while ago, so if you are interested in creating and supporting an Oracle provider you should get in touch with him directly and discuss those matters again.

The TL;DR of my previous comment is
Q: _"What's the story with this?"_
A: there is no official Oracle provider and it's unlikely to happen any time soon. Such a thing would be a community-driven effort.

[odinserj]

@jods4, could you tell me where I was proposing Enterprise license? ☺️

[jods4]

@odinserj I am using our own Oracle provider for Hangfire in production... we talked about it (like 2 years ago) and you asked if I was willing to publish, maintain and support it. You said you would support such a community effort (incl. licence). That was nothing official, I guess! 😁

[odinserj]

Sure, but anyway, I can give access to Hangfire Pro feed and sources.

[worthy7]

Oh I thought we were talking about an Oracle Enterprise Edition licence hahaha

[odinserj]

Unlikely I can transfer Oracle licenses 😄

[victorperez2911]

@jods4 and @odinserj
Has this story ended?

[jods4]

@victorperez2911 I think so.
I don't have time and resources to maintain and support a public Oracle fork.
Apparently neither does @odinserj, although he would support such an effort. Seems like nobody stepped up.

[worthy7]

How about making the oracle part a paid addon or something - I could be interested in royalties lol. This shouldn't be that hard as long as the right abstractions have been done ...

[worthy7]

I just read through the posts again, for starters, @wmkDev can we not just rebase this one onto the latest master? What's the problem?

[worthy7]

@odinserj Just out of interest, why are you not using EF for this project? Something specific? There are EF oracle providers that could have helped.

[burningice2866]

@Worthy7 a quick guess is that EF is sloooow compared to the closer-to-bare-metal queries Dapper provides.

[worthy7]

It's fast enough trust me. We are talking nanoseconds of translating LINQ
to sql.

On Tue, 16 Jan 2018 at 10:26 Pauli Østerø [e-mail] wrote:

@Worthy7 https://github.com/worthy7 a quick guess is that EF is sloooow
compared to the closer-to-bare-metal queries Dapper provides.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/HangfireIO/Hangfire/issues/250#issuecomment-357827129,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AEirH6PexaFPMhIex0yj2sxF2PiJcz6Tks5tK_rYgaJpZM4C-cgv
.

[burningice2866]

EF is around 3-10 times slower than Dapper and raw ADO.NET so you would never use a ORM when speed and control is of the essence.

[jods4]

@Worthy7 this project has stringent requirements as far as concurrency control goes.
EF won't cut it for that kind of specific statements.

[worthy7]

@jods4 interesting, I didn't think this project was so complex... could you point me to some of the statements? I'm curious - thanks.

[burningice2866]

https://github.com/HangfireIO/Hangfire/issues/250#issuecomment-63846979 is a start

[worthy7]

@burningice2866 You're right, I totally skipped that post, sorry guys.
Ok, so we need alternatives for these things in Oracle, so each one of them needs abstracting.
These are all implementations for SQL server, if those methods are abstracted into a service, with interfaces, then that would be a start. I'll take another look at what people have put together when I get a bit more time. I feel like there should be another layer somewhere in this project - services or DAL....

[jods4]

@Worthy7 I can tell you that I used the Hangfire provider above as a starting point, but to get it to work reliably I've had to do lots of tricky stuff.
Like handling funny _ORA-08177: Can't serialize access._ errors and performing retries.