Tom Clarkson

As part of trying to make my code work cross platform, I want to be able to use my existing LINQ to SQL code with other databases, ideally by changing only a connection string.

DbLinq is an open source implementation of LINQ to SQL that can connect to other databases. Being an open source product with a zero version number, it naturally required a little hacking to integrate it with Visual Studio.

DbLinq includes a command line tool, DbMetal, that will generate dbml files from a database or generate C# code from a dbml file. This is much the same as SqlMetal, the tool that comes with Visual Studio – the only real difference in the generated code is that the base class supports using multiple implementations of IDbConnection, so replacing the code generator used should enable cross platform databases without requiring too many code changes.

The dbml generated by Visual Studio doesn’t quite match the dbml expected by dbmetal, but this is mostly a matter of properties needing default values and easily fixed. I downloaded the source code and added some code to DbMetal\Generator\Implementation\Processor.cs in the load dbml section of ReadSchema.

                // add missing attributes so dbmetal will work with dbml from the visual studio designer
                if (string.IsNullOrEmpty(dbSchema.Provider)) dbSchema.Provider = "MySql";
                foreach (var t in dbSchema.Table)
                {
                    if (string.IsNullOrEmpty(t.Name)) t.Name = t.Type.Name;
                    if (string.IsNullOrEmpty(t.Name)) t.Name = t.Member;
                    foreach (var c in t.Type.Columns) {
                        if (string.IsNullOrEmpty(c.Member)) c.Member = c.Name;
                    }
                }

Rebuild DBMetal and the command line tool should be able to generate a suitable C# class from the dbml generated in Visual Studio.

The next step is to get it working in the IDE. Add LINQ to SQL classes to your project in the usual way. You can use server explorer to create the classes from an existing database, but be aware that this will add some server specific stuff (like “dbo.” on every table name) that may need to be removed later.

You will need to disable the default code generation. To do this, open the properties for the dbml file. Clear the field “Custom Tool”, which will be set to “MSLinqToSQLGenerator” by default. That will remove the designer.cs file.

The best way to integrate DbMetal would probably be to set up a custom tool so you can just change the value in the properties, but there is a much simpler method that is almost as good – adding an MSBuild task. Unload and edit the project. As the last child of the Project element add:

<target name="BeforeBuild">  
  <exec command="C:\Code\dblinq2007-read-only\build.dbg\dbmetal -code:DataClasses1.designer.cs DataClasses1.dbml">  
  </exec>  
</target>

Set the path to dbmetal and the file names appropriately. If you have multiple databases, just add additional Exec elements.

Reload the project and build it. This will create the designer.cs file, but will not add it to the project. You will need to turn on show all files and include it before everything will build without errors.

Using the generated classes is much the same as for standard LINQ to SQL. Since there is no default connection string, you will need to create data contexts using

var dc = new DataClasses1DataContext(connectionString);

To determine the correct connection type, DbLinq needs a couple of extra parameters on the connection string. Below are the connection strings I got working with the same code – additional provider valuse can be found in the DbMetal app.config.

    <add name="SQLTestDB" connectionstring="Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=True;DbLinqProvider=SqlServer"></add>  
      
        <add name="MySqlTestDB" connectionstring="Database=test;Data Source=servername;User Id=username;Password=password;DbLinqProvider=MySql;DbLinqConnectionType=MySql.Data.MySqlClient.MySqlConnection, MySql.Data"></add>  

Over the last few days I have been setting up a new build/web server. I’m replacing the Windows 2003 dedicated server I have been using for the past few years. I was initially going to go with another windows server – it was noticing the new Windows based Rackspace cloud servers that got me started looking at options – but since I’m running quite a few open source components and I don’t need to worry about setup fees, I decided to try a linux server.

1. The starting point

My starting point for this install is a new Rackspace cloud server running Ubuntu 9.10. I have installed MySQL, Apache, Rails and PHP, using the instructions on http://cloudservers.rackspacecloud.com/. I moved the apache default site from /var/www to /var/www/default in order to handle virtual hosts better.

For editing config files I am using nano:

nano [filename]

2. Subversion

The first component to install is a subversion server and something to manage it with. I used Jose Prado’s instructions (Roll your own Subversion server) to set up svn and Warehouse with a couple of changes to suit use with Ubuntu and Apache.

Installing:

aptitude install subversion

svnadmin create /var/svn/repositories/test1

To get automatic startup:

Edit /etc/init.d/subversion:

#!/bin/sh

#

# start/stop subversion daemon.

test -f /usr/bin/svnserve || exit 0

OPTIONS="-d -r /var/svn/repositories"

case $1 in

start)

echo -n "Starting subversion daemon:"

echo -n " svnserve"

start-stop-daemon --start --quiet --oknodo --user root --exec /usr/bin/svnserve$

echo "."

;;

stop)

echo -n "Stopping subversion daemon:"

echo -n " svnserve"

start-stop-daemon --stop --quiet --oknodo --exec /usr/bin/svnserve

echo "."

;;

reload)

;;

force-reload)

$0 restart

;;

restart)

$0 stop

$0 start

;;

*)

echo "Usage: /etc/init.d/subversion {start|stop|reload|restart}"

exit 1

;;

esac

exit 0

Now allow the file to run as a script and set it up to be called on system startup/shutdown:

chmod +x /etc/init.d/subversion

update-rc.d subversion defaults


To start svnserve manually, use

/etc/init.d/subversion start

3. Mono

My first attempt at installing Mono didn’t go well. The official package is Mono 2.4, and includes a bug in xbuild that makes it incompatible with TeamCity.

To get mono 2.6 you will need to download the source and build it. Fortunately that’s not quite as scary as it sounds thanks to a script from Patrick McEvoy (Install Mono 2.6.x parallel environment on Ubuntu 9.10).

I did need to make a couple of modifications to the script – the version below isn’t quite the same as the one on Patrick’s site. In particular, I had to stop it from excluding mod_mono with the output “Skipping mono-tools”.

[ code removed - a better version is available from https://github.com/nathanb/iws-snippets/blob/master/mono-install-scripts/ubuntu/install_mono-2.8.1.sh ]

Once this finishes running (it takes a while) you need to tell the system to use the new version. Since it is installed parallel rather than replacing the default version, the system doesn’t know where to find it.

export PATH=/opt/mono-2.6/bin:$PATH

export PKG_CONFIG_PATH=/opt/mono-2.6/lib/pkgconfig

Setting PATH will get you the right version when you run any of the mono commands from the shell. pkg-config is used by TeamCity to detect the version and location of Mono to use. You can check that it worked using

pkg-config --modversion mono

If it returns “2.4″ or can’t find the package, something went wrong.

The next step is to test that ASP.NET is working. Create a simple ASP.NET MVC app, build it, and copy it to /var/www/MVCTest1.

First test it with XSP, which is a standalone server for development use:

cd /var/www/MVCTest1
xsp


Go to http://servername:8080/ and you should see your app working. At this point the default mono package would give an error finding System.Web.MVC.

If that worked, it’s time to set up Apache with mod_mono.

Edit /etc/apache2/sites-available/mvctest1:

<VirtualHost *:80>

ServerName  mvctest1.orangeguava.com

ServerAlias www.mvctest1.orangeguava.com

DocumentRoot /var/www/MVCTest1

DirectoryIndex index.html index.aspx

MonoDocumentRootDir "/var/www/MVCTest1"

MonoServerPath mvctest1 "/opt/mono-2.6/bin/mod-mono-server2"

MonoApplications mvctest1 "/:/var/www/MVCTest1"

<Directory /var/www/MVCTest1>

MonoSetServerAlias mvctest1

SetHandler mono

AddHandler mod_mono .aspx .ascx .asax .ashx .config .cs .asmx

allow from all

</Directory>

</VirtualHost>

Enable mono and the new site:

a2enmod mod_mono

/etc/init.d/apache2 restart


You should now have an ASP.NET MVC application running through Apache on port 80.

4. TeamCity

The last component to install is TeamCity.

TeamCity includes a Tomcat web server, but not Java, so you need to install java first:

aptitude install default-jre

export PATH=$PATH:$JAVA_HOME/bin


Download TeamCity and extract it to /var/TeamCity.

Enable TeamCity to connect to Apache by editing /var/TeamCity/conf/server.xml and uncommenting the line

<Connector port="8109" protocol="AJP/1.3" redirectPort="8543" />

<VirtualHost *:80></code>

ServerName  teamcity.orangeguava.com

<Proxy *>

AddDefaultCharset Off

Order deny,allow

Allow from all

</Proxy>

ProxyPass / ajp://localhost:8109/

ProxyPassReverse / ajp://localhost:8109/

</VirtualHost>

Enable mod_proxy_ajp and the new site:

a2enmod proxy_ajp

a2ensite teamcity


Since the apache site is a proxy and only works when the TeamCity Tomcat server is running, we need to set up startup scripts.
Edit /etc/init.d/teamcity:

#!/bin/sh

#

# /etc/init.d/teamcity -  startup script for teamcity</code>

export JAVA_HOME=/usr/lib/jvm/default-java

export PATH=/opt/mono-2.6/bin:$PATH:$JAVA_HOME/bin

export PKG_CONFIG_PATH=/opt/mono-2.6/lib/pkgconfig

case $1 in

start)

/var/TeamCity/bin/runAll.sh start

;;

stop)

/var/TeamCity/bin/runAll.sh stop

;;

esac

exit 0

Install the init script:

chmod +x /etc/init.d/teamcity

update-rc.d teamcity defaults

Start TeamCity manually with:

/etc/init.d/teamcity start

At this point, TeamCity should be available on both ports 8111 and 80, and configuration is much the same as it is on Windows.

If you decide to block port remote access to port 8111, you will need to update the server url setting in the server configuration setting of the TeamCity webapp.

To be compatible with the build agent, you need to set the build configuration to MSBuild/Mono xbuild/x64.

If you get the error

MSBUILD0005: Property name and value expected as /p:<prop name>=<prop value>

TeamCity is using the wrong version of Mono.

TeamCity works with its own embedded database, but recommends connecting to a real database.

Install the MySQL JDBC driver

aptitude install libmysql-java


Make the driver available to TeamCity

ln -s /usr/share/java/mysql-connector-java.jar /var/TeamCity/webapps/ROOT/WEB-INF/lib/mysql-connector-java.jar

Create a database and user in MySQL:

create database teamcity;

CREATE USER 'teamcity'@'localhost' IDENTIFIED BY 'password';

GRANT ALL ON teamcity.* TO teamcity@localhost;

Create a TeamCity database config file

cp ~/.BuildServer/config/database.mysql.properties.dist ~/.BuildServer/config/database.properties

Edit ~/.BuildServer/config/database.properties:


driverName=com.mysql.jdbc.Driver
connectionUrl=jdbc:mysql://localhost/teamcity
connectionProperties.user=teamcity
connectionProperties.password=password

Restart TeamCity and it should be using the MySQL database.