How about implementation for MySQL as database provider?

Topics: Core, Customizing Orchard, Writing modules
Jul 13, 2012 at 1:07 PM

I am one of the developers that not whant to get from MySQL becouse it is the database i have learn from the begining and dont see any reason to change to another database.

So i have done for some versions of Orchard CMS now a fork and implemented a MySQL database provider.

I know that some people using my code becouse i got some e-mail and i think more should use MySQL if they got it as a alternative in the default installation.

Why has Orchard CMS not implemented MySQL and other databases thats alrady is implemented from third-part developers?

My fork for 1.4.2 is on http://orchard.codeplex.com/SourceControl/network/forks/RickardP/OrchardMySQL142 and i going to update to 1.5 as soon as it is stable.

After done the implementation for some versions now i think the best practice to implement more database providers should be let the install module to read search for Database provider modules or something so everyone more easy can implement a database provider they whant.

Then after first time implemented MySQL i find some people whanted PostgreSQL and i taked a look at it but there they have some changes in datatypes so i skiped it, but maye something to think about if the implementation should be done a good way anytime...

Coordinator
Jul 13, 2012 at 1:23 PM

Simple: if you support a bit of code, you must maintain it. We have had other priorities, given that the typical Orchard developer doesn't touch the DB directly, and that SQL CE and Express are both free. It's great that the option exists as a third party fork, and we're grateful for the work you've put into that. Ideally, it wouldn't require a fork and we'd have hooks for it at setup. We might want to re-evaluate what it would take for this to be simpler to set-up than a fork, although this has to be available at setup-time, which complicates things. What do you think?

Jul 13, 2012 at 1:25 PM

I understand the priorities and i am open for discussion how i can do the implementation without need to fork every version...

Coordinator
Jul 13, 2012 at 1:35 PM

If you want, you could raise that question at the next steering committee meeting. We meet every Wednesday at noon PDT. Here is the Linq meeting info: https://join.microsoft.com/meet/sebros/TWFQ3LYH

Jul 13, 2012 at 1:44 PM
bertrandleroy wrote:

If you want, you could raise that question at the next steering committee meeting. We meet every Wednesday at noon PDT. Here is the Linq meeting info: https://join.microsoft.com/meet/sebros/TWFQ3LYH

ok, if i have time i trying to join next meeting...

Developer
Jul 14, 2012 at 2:47 AM

Yeah, that would be great.

Btw - I think it would also be good to discuss the extendability/replaceability of the DB access layer as a whole.

I'm not saying that core should have a built-in support for all DB's in the world - this won't happen. But just the ability to easily replace the whole storage layer in a module (and eg. choosing the right one via recipe). So to be able to not only plug in other RDBMS's, but a storage of any kind (like RDBMS, document DB, graph DB, custom backend services and so on, even XML-based storage;)

But that would mean we have to add a new layer of abstraction, unbound from NHibernate, which in turn may hurt performance. There was some work done on something similar (support for Raven DB in the raven-db branch), but discontinued - it's worth taking a look what's in there.

Jul 14, 2012 at 7:15 AM
pszmyd wrote:

Yeah, that would be great.

Btw - I think it would also be good to discuss the extendability/replaceability of the DB access layer as a whole.

I'm not saying that core should have a built-in support for all DB's in the world - this won't happen. But just the ability to easily replace the whole storage layer in a module (and eg. choosing the right one via recipe). So to be able to not only plug in other RDBMS's, but a storage of any kind (like RDBMS, document DB, graph DB, custom backend services and so on, even XML-based storage;)

But that would mean we have to add a new layer of abstraction, unbound from NHibernate, which in turn may hurt performance. There was some work done on something similar (support for Raven DB in the raven-db branch), but discontinued - it's worth taking a look what's in there.

Yeah, i think that the whole project most be built on NHibernate like it is today, but selecting the dabase provider for NHibernate should not be a very big deal to fix.

Then i really dont think Orchard CMS should have all database providers as default but if it should be easy to build a module that handling a database provider it should be a good start.

The first time i run Orchard CMS with MySQL it was a module that did the work, but then after some update, dont remember what version that module stop to work. It was then i taked a look what they did and have from then done a fork for the latest stabile version with a implementation.

But a module i didnt get it to work with becouse some namespaces didnt work, in Orchard CMS 1.5 i going to try one new approach on the implementation, i have some ideas.

But i dont think i starting with the implementation before 1.5 being stable so no big changes being done.. 

Coordinator
Jul 14, 2012 at 1:11 PM
Edited Jul 14, 2012 at 1:13 PM

@Piotr: nHibernate *is* a database abstraction layer. Do a search on Ayende's blog for ORM abstraction and you'll see how evil it is. He has good points. No, Orchard should support all databases that nHib supports in principle, but abstracting the ORM itself seems absurd, useless and dangerous.

The work that was done in the Raven branch, and more recently by Sébastien using YesSQL is a replacement of data access, not an abstraction, as far as I know. The YesSQL work even still relies on nHib and as such can work with any DB nHib supports. I feel much more comfortable with that.

Developer
Jul 14, 2012 at 2:32 PM

Isn't it the case that in the end everything goes through the IRepository<> implementation that's DB-related? (Well I don't know much about Projector's IHqlQuery, there should be a big piece of magic, but I've see that all the service classes use IRepository in the end.) If yes, the data-access layer can be replaced by just creating a new implementation, and it can use any technique for data storage access, NHibernate or not.

Coordinator
Jul 14, 2012 at 5:43 PM

The Content Manager is a data abstraction layer for Content Items, and Repositories are another one for content outside Content Items. If you want to change the data access layer, then you need to implement another CM, then remove the repositories if your storage doesn't work in terms of Records.

Side note:
1- Orchard needs transactions, because it relies on it. Without transactions Orchard doesn't work anymore. Example is when a part is bad but previous ones were updated, it rolls back the transaction to undo changes from previous parts.
2- Orchard needs to work on RDMS. For hosting purposes. What would be the hosting story if users needed to setup a document db like Mongo or RavenDb. It's more complex than just "Deploy". And on which hosters ? 
3- You don't want to have to pay for the db. SqlCe is free, MySQL can be provided for free by the hoster.  

Mongo is not transactional. RavenDb is not free. so let's stick with RDBMS. Then what, MicroORM, NH, EF ? Today Orchard works with NH, why would we replace it with something else ? You won't gain any perf by switching to another ORM. And now that I got NH3 to work in Orchard, there are even less reasons.

So why would you remove NHibernate ? 

Find another solution which is transactional, works on RDMS, is free, adds better performance, they we will implement it. Yes we have one, but Bertrand already told you, can't keep his mouth (keyboard) closed ;)

@RickarP: If you need to add new RDBMS please fix the module on the gallery. It's the correct implementation. Actually this code should be in core, it was made by a previous Orchard developer at Microsoft, but didn't get into it because of priorities.

Developer
Jul 16, 2012 at 11:57 PM

Bertrand, Sebastien, Zoltan - first of all thanks for the input - these are fair points I cannot disagree with.

My point was actually not about ORMs at all - rather about scenarios that require non-RDBMS or other storage not supported by NHib. 

Example. A while ago I had a requirement to utilize graph database (namely - Neo4J) as a storage. It's performance can be orders of magnitude better when doing complex queries over huge datasets in comparison to RDBMSes. Finally took a hybrid approach with Orchard DB + Neo4J storage for all stuff besides the core ones. Maintaining such hybrid was a pain, so I thought if maybe it would be good to have the ability to replace the whole Orchard DAL to utilize storage other than RDBMS.

Like I see it know, after reading your arguments and Zoltan's suggestion, is that in the above case it'd be sufficient to replace CM (and IRepository<> implementations, ITransactionManager etc. if needed). No additional abstraction needed. I was definitely wrong, being so focused on the idea of low-level abstractions, and didn't see that quite obvious solution...

And btw - maybe I'll finally try Sebastien's YesSQL;)

Jul 17, 2012 at 6:30 AM
sebastienros wrote:

@RickarP: If you need to add new RDBMS please fix the module on the gallery. It's the correct implementation. Actually this code should be in core, it was made by a previous Orchard developer at Microsoft, but didn't get into it because of priorities.

I did like the module did from the begining but then in some version the namespaces could not be read from the Install module but i let it another try on Orchard 1.5 again when it is ready for it.

Jul 17, 2012 at 1:56 PM
Edited Jul 17, 2012 at 1:59 PM

I have started working on a Setup module for MySQL and i think the problem i got when i get from todo a module is fixed but its has come some new things.

I getting "Setup failed: Keyword not supported: port." (i have translated the message from swedish to english so maybe not exact that english words.

I have debug the code and step by step in the setup module and find it is when Orchard checks if database is created or not it failes and running a SQL Server connection instead of MySQL.

The row where it failes is in file Orchard.Web\Modules\Orchard.Setup\Services\SetupService.cs on line 119:

 

schemaBuilder.ExecuteSql("SELECT * FROM " + tablePrefix + "Settings_ShellDescriptorRecord");

 

When it executes its runs the SQL server instead of MySQL and this was not a problem in the 1.4.2 code when i maked my implementation last time.

Some ideas?

If someone whant to test my implementation so far take a look at http://orchard.codeplex.com/SourceControl/network/forks/RickardP/orchardmysqlsetupmodule/

Edit: The user going to need to put MySQL.Data.dll by it own in the bin folder.

Jul 24, 2012 at 8:10 AM

I have after sebastienros mini profiler fix got the NH3 branch to work with my implementation of MySQL.

I have put a pull request for review and i am open for discussion about the implementation.

Pull request url: http://orchard.codeplex.com/SourceControl/network/forks/RickardP/orchardmysql/contribution/3140