Using Jabbr chat with MySql database

I wanted to integrate a .net chat application for my Mauritius Chat project and came across Jabbr which has most if not all of the features I wanted. However it works with SQL Server Express by default but I wanted to use MySql instead. The reason for that is that I’ve got a dedicated where I’m running MySql because it’s free compared to SQL Server 2008/2012 and I didn’t want another service for SQL Server Express to run just for one application – I have to use the limited RAM carefully. So this post is all about how I finally managed to do that.

First thing first – you have to download the source code from Jabbr repository on Github, There’s a Download Zip button on the left hand side.

Next you’ll need to download the MySql .Net Connector 6.9.3 and install it. If you have previous versions of the MySql .Net connector, like I did, you’ll have to uninstall that first.

These are the changes you’ll need in your web.config file:

Connection String


<connectionStrings>
<add name="Jabbr" providerName="MySql.Data.MySqlClient" connectionString="Database=Jabbr;Port=3203;Data Source=localhost;User Id=YourUserName;Password=YourPassword;"/>
</connectionStrings>

Entity Framework


<entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>

You will also need to reference the MySql.Data.Entity.EF6.dll in your project. You will find that where the MySql .Net Connector has been installed and you will have to choose the assembly according to the .net version you’re using, in my case .net 4.5.

Problems I encountered

When you recompile the project, you’d think it will work but for me it didn’t because it kept complaining about this error:

Schema specified is not valid. Errors:
(15,12) : error 2019: Member Mapping specified is not valid. The type ‘Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]’ of member ‘When’ in type ‘JabbR.Models.Attachment’ is not compatible with ‘MySql.timestamp[Nullable=False,DefaultValue=,Precision=0]’ of member ‘When’ in type ‘CodeFirstDatabaseSchema.Attachment’.
(40,12) : error 2019: Member Mapping specified is not valid. The type ‘Edm.DateTimeOffset[Nullable=True,DefaultValue=,Precision=]’ of member ‘RequestPasswordResetValidThrough’ in type ‘JabbR.Models.ChatUser’ is not compatible with ‘MySql.timestamp[Nullable=True,DefaultValue=,Precision=0]’ of member ‘RequestPasswordResetValidThrough’ in type ‘CodeFirstDatabaseSchema.ChatUser’.
(66,12) : error 2019: Member Mapping specified is not valid. The type ‘Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]’ of member ‘When’ in type ‘JabbR.Models.ChatMessage’ is not compatible with ‘MySql.timestamp[Nullable=False,DefaultValue=,Precision=0]’ of member ‘When’ in type ‘CodeFirstDatabaseSchema.ChatMessage’.
(95,12) : error 2019: Member Mapping specified is not valid. The type ‘Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]’ of member ‘LastActivity’ in type ‘JabbR.Models.ChatClient’ is not compatible with ‘MySql.timestamp[Nullable=False,DefaultValue=,Precision=0]’ of member ‘LastActivity’ in type ‘CodeFirstDatabaseSchema.ChatClient’.
(96,12) : error 2019: Member Mapping specified is not valid. The type ‘Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]’ of member ‘LastClientActivity’ in type ‘JabbR.Models.ChatClient’ is not compatible with ‘MySql.timestamp[Nullable=False,DefaultValue=,Precision=0]’ of member ‘LastClientActivity’ in type ‘CodeFirstDatabaseSchema.ChatClient’.

Basically in the Jabbr code, some of the properties (especially for DateTime) are marked as DateTimeOffset which is stores the date/time as an offset of UTC, rather than the server date/time. However the DateTimeOffset type was being mapped to datetime in MySql and Entity Framework realised it was not a proper mapping. I tried to check for solutions online but couldn’t find any and since this was not a big deal for me (storing the date/time as UTC), I ended up replacing the properties marked as DateTimeOffset as DateTime. When you do this, you also have to modify some parts of the code where it uses DateTimeOffset.UtcNow with just DateTime.Now. I’ve used replace/find and there are less than 5 occurrences if I remember correctly.

Okay at this point, the application compiles but when browsing the site, the application tells me that there’s not database/matching tables yet so I would need to use the Package Manager Console and run the command Update-Database. When I did that, it failed at one specific migration. So I tried to see what was happening by issuing a Update-Database -verbose command and I noticed MySql was complaining about a variable was not declared. That specific migration to was to rename a column from Proivder to Provider, something like that. So I thought I could just exclude that particular migration and do the rename manually but the migration still failed at another point.

I therefore decided to delete the whole Migrations folder so that the schema is created from what’s in the code without having to run it any migrations (this is only good if you’re just setting up the website and you don’t have any data yet). I had to comment out the Migration calls in Startup.cs and run the following command in Package Manager Console: Update-Database

I also issued Enable-Migrations so that if I make any changes, then these are tracked.

In the end, I was able to run Jabbr chat with a MySql database. Now I need to create a custom membership provider so that logins details are checked against the Users table which resides in another project, so I might need to create an API for that.

Setting Git on external hard drive or USB flash in Windows

At home, I usually work on personal projects and I wanted an easy way to back up my data and put my code under source control. I decided to go with Git for this and use my 250Gb external hard drive for the remote repository (you can use a USB flash drive as well if you want).

So the first thing to do is download Git Extensions and install the software. I installed mine on the C drive in Windows 7 using the default settings.

The next thing was to let Git know where my local repo for my project is. So I browsed to the folder where my project was located (the directory which contains all the C# source files from Visual Studio) and right click. This gives you the option for “Git Init here” which when chosen creates .git directory marking this folder as your local working directory. Open your Git Extensions program and do a commit to get your codes under source control (locally).

Once that is done, you’ll need to create a remote repository on your external hard drive or USB stick. Just create a folder there eg H:\Git\MyProject and when you right click in the folder, you will be able to click on the “Git Bash” option which will open a command line tool. Type the following command in there:

git init –bare

This will create a bare repository in the current directory.

Go back to Git Extensions and do a push now. It will ask you where to push to and you just need to enter the location of the remote repo you’ve just created and voila, all done.

Notes

Although I was able to set up the local repo using Git Extensions, I was not able to do the same for the remote repo, hence I’ve used the command line in Git Bash.

The local repo contains all your source code (it’s a working copy after all) but the remote one has only git files. My local repo was 8Mb while the remote one was only 0.5Mb so I was worried it was not a proper backup of my data. However that was not the case – I tried to get another working copy from the remote repo just to make sure that if my computer crashed, I would still be able to get all my source codes from the external drive and that worked perfectly. Actually it’s better because the size of the remote repo backup was way less than the working copy.