How do DatabaseOptions works in 1.3.10?

Topics: Customizing Orchard, Installing Orchard, Troubleshooting, Writing modules
Jan 2, 2012 at 11:19 AM

I am trying to apply MySql on Orchard.Web.1.3.10 and i have tried do exact the same things that the old module in the gallery has but there is many changes in that doing its not works todo exact the same.

Right now i getting this when i setup with mysql database: "The value 'MySql' is not valid for DatabaseOptions." 

Can anyone explain how DatabaseOptions works and where i can add MySql there?

Jan 2, 2012 at 3:03 PM

Why do you want to use MySQL?

I think you'll find too many complexities and implementation details to easily swap out to a completely different database engine. I know NHibernate gives you an abstraction but ... well, some modules even run hand-written SQL code ...

Jan 2, 2012 at 4:03 PM

Then i dont use those modules, and i think its bad code if its runs hand-written sql codes..

Jan 2, 2012 at 4:45 PM

You can often rely on HQL instead of pure SQL. I have never tried the MySql module myself, would be nice to see if it still works on 1.3.10, and even more on 1.4.

Jan 2, 2012 at 5:33 PM

What is HQL?

I have tried the old MySQL module but its not works on version 1.3.10 

So any ideas how i can do?

Jan 2, 2012 at 7:41 PM

Let me google that for you ;)

Jan 2, 2012 at 7:55 PM

On the MySQL module, did you try to contact the author?

Jan 2, 2012 at 8:29 PM

HQL is nothing for me that whant to have mysql as database but for the people that has hand writen sql in modules its should be good.

I have sent a contact message to the author of the MySQL module but didnt get any answer, has sent a new one and see.

I really dont understand why Orchard is not created so it is easy to use what every database you whant, when its has sql compact and sql server... maybe its easy to implemente both of them but not mysql?

Jan 2, 2012 at 8:36 PM

That's an easy one to answer: we rely on the community to develop features. The more database engines we support from the start, the more we have to maintain. That's expensive, and the community will typically fix what it cares the most about. If the author of that module is not responding, then your best bet is to try to fix it yourself.

Jan 2, 2012 at 8:39 PM

Thats why i asking how DatabaseOptions works, somewhere i have missed add mysql as DatabaseOptions...

Jan 2, 2012 at 9:15 PM

I don't know, sorry.

Jan 3, 2012 at 2:52 PM

RickardP, do some more searching. I came across some pages a few months ago where people got Orchard to work with MySQL, and also there was a blog post someone made about getting Orchard to work on Mono with Postgresql, with instructions on the code changes he/she made for the db part. It seemed like a pretty small amount of code. It shouldn't be too difficult to modify that to work with MySQL. 

I also agree that people shouldn't use custom SQL in modules if it won't work with multiple databases. 

Something that would be awesome would be to have a server for Orchard where tests are run automatically (nightly build or Continuous Integration), and then tests automatically run against all the different databases. I know that people have tried and maybe gotten some success in running Orchard on MySQL and pgSQL. If we just had those users upload their code, a build & test system like that would help ensure that future versions continue to work with those other DB's, also would potentially help people test their modules. 

I think as an open source project it's important for Orchard to work with the most popular open source DB's. This would allow scenarios where Orchard is a big CMS in the .NET world and people building bigger infrastructures around Orchard (e.g., large sites, or hosting services, PaaS, multi-tenant hosting) would have a wider set of options (and possibly cheaper, because MS databases get expensive once you surpass the requirements of the free or cheaper licenses like SQL Server Web Edition). It also allows Mono community to run Orchard. 

Jan 3, 2012 at 4:08 PM

TheMonarch: Thanks for your answer, i didnt have any idea on search for other database setups, i find this documentation therre is a 2 patch files one for mono and one to get postgresql to work.

But i have done exactly the things that the patch for postgresql says but for MySQL instead, before i read that patch.

The patch is for a old version of Orchard, like i said its something that has changed in later versions that this dont works like it has done before.

Jan 3, 2012 at 4:48 PM

I haven't messed with the alternate providers so I can't give specific advice. If the module you tried and the method in the link you just found don't work you'll have to get it working yourself. It doesn't look like it would be very difficult. Hopefully you'll contribute your code back to the orchard project once it's working. 

Take a look at Orchard.Framework\Data\Providers\, specificaly the SqlServerDataServicesProvider.cs and SqlCeDataServicesProvider.cs classes to see how you can implement your own. There is actually very little SQL server code that I have come across so far, and the small amount of database specific stuff in this folder implies that it would not be too hard to add your own provider for MySQL. Once I have more time I plan to come back and explore this area but that won't be until 2-3 months from now at the earliest. 

Why not start out by adding a MySqlDataServicesProvider.cs, and in it define a class by the same name that derives from AbstractDataServicesProvider abstract base class? Model it after the code in SqlServerDataServicesProvider.cs. To give yourself more help, look at the code for the pgSQL patch, and the MySQL gallery module you mentioned. Also might want to do a case insensitive search through the Orchard source for references to 'SQLServer' and "SqlCE". 

The code as it is right now should be refactored because it doesn't provide an easy way to plug new database providers (for example line 111 in SetupController.cs hardcodes the db options you get: "DatabaseProvider = model.DatabaseOptions ? "SqlCe" : "SqlServer"). 

Jan 3, 2012 at 5:58 PM

Thank you for your tip`s TheMonarch!

Greatful you trying to help!

I going to start from the begining and try pgSQL patchen but for MySQL again, i got trow the mysql module before and do what they have changed but what i finded and its looks the same as pgSQL but now i have tested to match to really know what exact i changed.

Goint to version control everything so i can release some code later.

Jan 3, 2012 at 7:23 PM

Some update, after taked a clean "Orchard.Source.1.3.10" and try to implement like postgresql patch on the url above i get the same result.

I put all settings on the setup form and then go forward, its loading some time, but then i getting:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) 

I have tried to search after SqlServer and SQL Server and everything i can think about but i can not find what i missed.

I really whanted to get it to work but i maybe has to wait for someone more advanced Orchard developer to take a look.

Jan 3, 2012 at 7:53 PM

Are you able to make a plain ASP.NET webforms or MVC (whichever you are more comfortable with) page that connects to you MySQL database to verify that it works? This would ensure that your connection string and db provider for MySQL are working. If you know those work then you know something needs to be fixed in the Orchard db provider code you worked on. 

Jan 4, 2012 at 7:45 AM

Fuck, fuck, fuck, sometimes i stare me blind on stuff.

TheMonarch: Thanks för point out to try other application, i was so in the that it most be the code that are the problem.

Its was it also but after check the connectionstring again i find out that i most taked wrong connectionstring, its was not a mysql connectionstring i tried... and when i did that i got some forward and got that the port keyword not was valid.

Thats becouse of the SqlDatabaseConnectionString that validate the connection string, when i removed the validation (for now, maybe can find a better way) i get forward in the code, more then it come with my old connection string also, so i find out that some how the mysql database provider not was find and instead it taked sqlserver that are set to default if database provider not find.

The solution was to move the database provider to the right folder, i tried to have it in the Orchard.Setup so i could release only a new Orchard.Setup but looks like something didnt whanted to get it to work there, going to try some more now when i know its works but i whanted to say that i am on it.

I going to try get a patch for it and i thinking about if i should implement even postgresql when i now know some more about it.

Jan 4, 2012 at 8:52 AM

For people intrest in this you finding a fork of me on the url below that has a working implementation of MySQL, has also created a pull request on it.

When i taked a new look on the PostgreSQL implementation they have changed how boolean is adding, seems like in PostgreSQL boolean is a true or false and in sql server and mysql it is 0 or 1.

I dont know how to fix it for both so i skiped that implementation, but for people needed PostgreSQL i think take a look att my changes and add postrgresql should not be so big deal, but the boolean stuff maybe more difficult.