Questions on SQL Server adapter

Just a brief post to note that there is a specific discussion group on SQL Server adapter in rails here.

Rails against a legacy sql server database

The main problem with legacy database is that you can't do what you want. And there is 99% of chances that your legacy database does not follow the rails conventions.

Here are a few tricks to simplify your life :

1. First of all, SQL Server views are updatable so use them !

ex. create view
as
select mylegacyid as id,
originalcolumn1,
originalcolumn2,
originalcolumn3,
originalcolumn4,
originalcolumn...,
record_creation_date as created_at,
record_modification_date as updated_at

This way your legacy system continues to work correctly and you have a Rails layer built on top using specific views.

Then a trick, as rails builds its indentation on the last record in the database, you must have at least one record in each table otherwise rails won't be able to insert a new record with this method. I've found that this method works well with tables with few fields (25 fields) and saves time.

The other method is to modify the conventions and specify everything (key, table name etc..) manually. If you can't avoid it, here are a few tips :

HowToUseLegacySchemas

Legacy databases

Masking the Database

Rails and Legacy databases

My advice here is to start generating your empty scaffold with the rails convention for your model : ruby script/generate scaffold Car

Then you need to edit the model and indicate the primary key and the table name:

class Car < ActiveRecord::Base
set_table_name "TABLE_VintageCars"
set_primary_key "MyPrimaryKey_Identifier"
end

Then finally update your views. Be carefull with the column names as you know that rails is case sensitive (not like most of your SQL Server or ODBC setup).

This method functions as well and although the creation of new views seems to conform more to the rails philosophy, it might be usefull not to add a ton of new object in your db and to live with existing tables.

Rails 2 and SQL Server 2008 on Windows made simple

I always wondered if rails was really working well with SQL Server. A lot of posts on the web concern every database on earth excepted SQL Server. Most posts concerning SQL Server and Rails are written for previous version of rails and deal with specific issues. And sorry, but the official documentation is the jungle ;-)

So I thought it was time for a simple tutorial on how to use SQL Server and rails 2.0 under MS Windows. The goal of this tutorial is to create a basic CRUD application from scratch. The targeted audience is 0 or little knowledge of rails. I'm using SQL Server 2008 CTP6 but this works as well with other versions of SQL Server (2005 or 2000).

After this simple tutorial, I'll try to go more into details in subsequent posts.

First of all, we have to create our application environment :

1. Open a ruby console, go to your rails_apps directory and type

rails sqlserverapp


This will create your application file (you should see a lot of files preceded by the create statement).

Then you have to install the SQL Server adapter which was removed from the standard rails 2.0 distribution.

2. In the ruby console, type :

gem install activerecord-sqlserver-adapter --source=http://gems.rubyonrails.org


If you have an error message, try to turn off temporarily the windows firewall (the installation won't take more than a few minutes).

Then you have to create a database on your SQL Server

3. Open your SQL Server client tool and create a database called sqlserverapp

4. Add a user with SQL Server authentication called user with secret as password to the database you just created.

5. Create an ODBC link name sqlserverapp to connect to this database.

Then you have to edit your database configuration file to tell rails to use SQL Server and your new database.

6. In the rails console, go to the configuration directory and edit the database.yml file.

cd configuration
edit database.yml

Change the section under development with the following :


development:
adapter: sqlserver
mode: odbc
dsn: sqlserverapp
username: user
password: secret
host: mycomputer

Note : replace mycomputer with the name of your instance of SQL Server (in most cases, locahost should do the job).

Repeat the process for the test and production database if needed.

Then the basic configuration is done. We can start writing the application.

7. In the rails console, to generate the scaffold type :


ruby script/generate scaffold Client name:string address:string email:string remark:text

And ... that's almost finished ! This instruction has done all the work, i.e. created the views, prepared your model, your controller, your test files etc...

You just have to run your db migrations and start your server now.

8. In the rails console, type:

rake db:migrate

This will create your model into SQL Server. If you look into your SQL Server database you should have a table named clients with the columns stated in your scaffold plus two columns for timestamps and one for the primary key which are added automatically.

The last thing you have to do now is to start the web server and test your application.

9. In the rails console, type:

ruby script/server

10. Open your web browser and type:

http://localhost:3000/clients

You should now see a form where you can edit your first client. You can also edit/update/delete any client straight away out of the box.

Yes Ruby on rails is magic ! Remember that the whole application took you 2 lines and 10 minutes to write (spending most of the time reading and watching rails work for you).

So now I can say it ! YES, rails does work simply and efficiently with SQL Server 2008 and active records. It's even very simple to use. I no longer will be a frustrated rails and SQL Server user !