New rails 2.2.Sql Server adapter

There is a new SQL-Server adapter for active-record. The new features are the following :

- Enabled support for DDL transactions.
- Micro second support. Time#usec is automatically converted to SQL Server's 3.33 millisecond limitation.
- Datetime data types before type casting are represented correctly. For example: 1998-01-01 23:59:59.997
- Implementation for #disable_referential_integrity used by ActiveRecord's Fixtures class.
- Pessimistic locking suppot. See the #add_lock! method for details.
- Enabled #case_sensitive_equality_operator used by unique validations.
- Unicode character support for nchar, nvarchar and ntext data types.
- View support for table names, identity inserts, and column defaults.

to install it :

gem install rails-sqlserver-2000-2005-adapter -s http://gems.github.com

More info on article on railsinside.com or get the code on git

Update : After some tests I can confirm it does not function with SQL Server 2008, so stick to the normal adapter mentionned in my previous post if you are running SQL Server 2008 if you want to avoid the nice message saying "Currently only 2000 and 2005 are supported". Somehow frustrating if you have 2008 !

New Update : It seems that there is a workaround ! You can play with the constant SUPPORTED_VERSIONS by adding 2008 to it. The reason why it is not officially supported is that the author would like to rewrite the code to take advantage of the new features of SQL Server 2008. I can only thank him for this idea, let's hope the wait won't be too long !

By the way, this adapter is now also tested and improved for rails 2.3.2

Using will_paginate for pagination

Since Rails 2.0 the pagination has been removed from the core rails. This allows more flexibility in the choice of the pagination method you want to use.

One secure choice is will_paginate. It is available as a gem

To install the will_paginate gem follow the procedure :

1. rake gems:install
2. gem sources -a http://gems.github.com
3. gem install mislav-will_paginate

The usage of this gem in a project is simple :

1. In your config\environment.rb file add

Rails::initializer.run do |config|
config.gem 'mislav-will_paginate', :version => '~> 2.2.3', :lib => 'will_paginate', :source => 'http://gems.github.com'
end

2. rake gems:install

3. In your controller, replace

@yourmodels = Yourmodel.find

with

@yourmodels = Yourmodel.paginate :page => params[:page], :order => 'created_at DESC'

4. In your index view, add

<%= will_paginate @yourmodels %>

Note : a trick, if it tells you it does not find the method in the controller, configure the gem directly in config\development, test or production file.

Switching to Thin server

By default, rails uses Webrick which is not particularly efficient. Although a lot of rails sites are using Mongrel in a cluster mode, I've chosen to use Thin.

The installation is really easy :

Yourapplicationdir$\ gem install thin

Then choose the i386 version when asked (at least with such a name, you're sure it's working on Windows).

Usage: thin start instead of the usual ruby script/server start

More information can be found here or on this site.

Ext JS GUI Designer

There is a GUI designer for Ext-js that can be found here

More about it can be found in this ext js forum.

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.

Ext JS 2.0 and rails

I've always wanted a nice Javascript library that I could use as a framework for everything I build. Prototype and Script.a.cul.ous are nice but not sufficient to give the look and feel of a desktop app. So I've decided to implement Ext JS for my solution and to share this experience. There are basically 2 solutions if you want to integrate ext js in your application, the usage of a plugin called ext-scaffold (very easy to use) or the hard-way (manual coding).

Note : The current Rails 2.1 changes the way that rails renders json format. Therefore the plugin ext-scaffold will cease to work out of the box with rails 2.1. This plugin is still functionning perfectly with rails 2.0.2 and remains the fastest way to start playing with ext js and rails.

The plugin gives a good starting point and is functional for simple CRUD application. if you are looking for a quick way to provide a user interface to your tables without exposing the database, it's perfect.

From there, the need to do more complex things arise and then, you guessed it, it's time for the manual coding.

SQL Server 2008 and active records

Some important things to know when working with Active records and SQL Server :

1. The ID field has Integer as default data type

2. The ID field is automatically created with an Identity specification with the following values :
- Identity Increment 1
- Identity Seed 1

3. The created_at field does not get a default value (eg. Getdate() would have been nice !). Thus if you don't rely exclusively on the active record to populate this field, it is recommended to add this default value.

4. The updated_at field does get the same value as created_at first. It is then updated while created_at remains unchanged.

Online SQL Designer

I found a free online tool for database design. It allows you to directly model a database with relationships and then script with appropriate syntax to different databases like SQLite, Mysql, Oracle, MS SQLServer, PostgresSQL etc...

I find this very usefull to switch from one database environment to the other in rails.

You can also export and import your designs as xml or even apply them directly to your database.

Online SQLDesigner
Project

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 !