Since I started this blog (or at least refocused my occasional ramblings on using Rails with IIS and SQL Server) I have been meaning to write an article all about adapting your application to work with Rails. Well, this is that article. However, it’s not what I intended it to be, for reasons I’ll explain at the end.
Our application is a Delphi desktop application running on a SQL Server back-end (with much of the business logic in stored procedures). It’s been about eight years in the making and has had over ten developers working on it at various stages of its development. There was never a clear development plan and is often the case, the code is pretty messed up in places. But it works. It is the market leader in its particular niche and, while rewriting often appears as the simplest thing to do, the eight years worth of quirks and bug-fixes would be impossible to reproduce without spending another eight years to get to where I am now.
So, when a web applications were needed, our original thought was to develop it, firstly in traditional ASP and then, later, in ASP.NET. With all the “wrinkles” that those environments bring. But I discovered Rails and thought that this could be the tool to help us bring the web applications to market quicker. But there were two problems: IIS and Rails’ configuration.
IIS has been the main focus of this blog. Firstly, the adventures in setting up FastCGI and, more recently, using RForward to simplify installation. Assuming RForward continues moving, er, RForward (and there’s no reason why not) then that should be one of the hurdles overcome.
The second was a bigger job. Our application has over a hundred tables accessed through about a thousand stored procedures. There are loose naming conventions, but they are not Rails’ conventions and they are not rigorously enforced. Primary key fields tend to be called fID and are of type uniqueidentifier. Unless they’re identity columns. Or use a composite key. The procedures tend to be called spEntityNew, spEntityUpdate and spEntityDelete. Unless they’re called spEntityAdd and spEntityRemove. And of course, nothing is unit tested so we are loathe to change anything in case the application breaks.
So, in order to get Rails to look at this database, I had to create some sort of Rails compatibility layer that did not interfere with the existing code. But that reused the existing code whereever possible.
Stage 1: Get Rails to read the data from the database.
This one was easy. Seeing as we always use mixed mode authentication in SQL Server (that is, rather than exclusively taking usernames from Windows and/or Active Directory, SQL Server can maintain its own list of users) and SQL Server uses the username as a scoping mechanism (in the same way as PostgreSQL’s schemas), I created a set of views for each table, following the Rails conventions.
create view rails.people as select fID as id, fFirstName as first_name, fLastName as last_name from dbo.tbPerson
Ordinarily, to read from this view you would need to use:
select top 100 * from rails.people
which means that your Rails application would need to apply a prefix to actually get at the data. But if your Rails application is told to log in as the user “rails” a simple:
select top 100 * from people
will automatically scope the reference to “people” with the username “rails”.
So now our Rails application can read from a set of views with minimal disruption. Each model thinks it is accessing a table that follows the Rails conventions and the Delphi application knows nothing of these new views, so they do not interfere with any existing functionality.
Stage 2: Get Rails to write to the database.
This one was slightly harder. I made sure that all the views were updateable. This means that I made sure that each view only ever selects from a single table. Therefore, if you do an insert or update into the view, SQL Server can figure out which field you are referring to in the real table. (I know you can use triggers to make non-updateable views updateable – but I chose not to as it does not really help in this situation).
If the view referred to a table with an identity column then inserts were easy. But if the table used a uniqueidentifier primary key then we need to supply one for SQL Server. I thought about giving each table a default value of newid() but this would break existing stuff and there is no equivalent of @@identity to retrieve the newly created value.
So, the following module was created:
module GUIDGenerator def before_create @internal_id = connection.select_value "select newid()" self[:id] = @internal_id logger.info "Created new id: #{@internal_id}" return true end
def after_create self[:id] = @internal_id logger.info "Retrieved: #{@internal_id}" return true endend
Any model that uses a uniqueidentifier key simply needs to use this module and the model automagically generates its own id. The before_create handler asks SQL Server to generate a new GUID and sets its own id field to this value. Then the after_create retrieves this value from a local variable (as ActiveRecord assumes it has to ask the database itself for the new id value, overwriting the one I had stored in self[:id]). This does result in a few extra round-trips to the server, but it does do the job.
Lastly, some objects had complex insert and update rules that were implemented in stored procedures. Hence the stored procedure module that adds new methods to your model allowing you to call a stored procedure. In my application I implemented these as specialist methods on the model objects and I had to remember not to call save – but more on this later.
Stage 3: Improving performance on database reads.
Seeing as Rails was actually abstracted from the database, I realised that I could improve performance on reads even further than Rails’ :include option. I created a whole set of views that were not updateable. This means I did all the necessary joins and filters within the view, meaning that SQL Server could not always tell which rows to update – but it did mean that I could get the exact joins that I wanted, with much more detailed control than a find … :include. Each of these, non-updateable, models was prefixed with the name info_ (for example I had a view called rails.people and another called rails.info_people).
Once I had done all this, I had a working application that referenced my live database, did not interfere with the existing code and produced new data that fitted the existing business rules. I was pretty chuffed, but still unhappy about the fact that I had to remember whether it was safe to call save on the model object, or if I needed to use my custom stored procedure routines to insert or update a model.
Stage 4: Better inserts and updates.
I came across the perfect scheme. Write a new module with a custom save method. It could examine the model, decide whether it was doing an insert or update and automagically call the correct stored procedure, passing through the correct parameters.
Of course, the existing stored procedures did not fit with any formal conventions, so I would have to create a new layer. Again, I could use the username trick to ensure that the scope of these new procedures did not interfere with the existing procedures. I wrote a small Delphi application that would, given a “Rails” view, would generate a skeleton insert, update or delete procedure using a fixed naming convention. rails.person_insert, rails.person_update and rails.person_delete, with a parameter for each field in the underlying view (and hence the parameter names also follow Rails’ conventions). I could then write the contents of the stored procedure and get it to call the existing procedure. The Rails model object could look for the relevant procedure, examine its own field values and pass them into the parameters of the same name and execute the procedure (thus calling the “old-style” procedure internally). Job’s a good ‘un. But at this point I stopped.
Why stop when I had travelled so far?
There were two reasons: firstly, we added timestamp columns to all our tables, in order to implement optimistic locking, and secondly, I wanted to unit test my Rails application.
The timestamp column was not really a problem. Basically, timestamp is SQL Server’s native version of Rails’ lock_version field and the server does not allow you to write to a timestamp column at all. Of course, Rails just sees it as another field and so tries to write back to it at every possible opportunity.
update people set first_name = self[:first_name], last_name = self[:last_name], timestamp = self[:timestamp] where id = self[:id]
I got around this by customising the SQL Server database adapter – I changed the columns method to ignore any column of type timestamp.
sql = "SELECT COLUMN_NAME as ColName, COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, COL_LENGTH('#{table_name}', COLUMN_NAME) as Length, COLUMNPROPERTY(OBJECT_ID('#{table_name}'), COLUMN_NAME, 'IsIdentity') as IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '#{table_name}' AND DATA_TYPE <> 'timestamp'"
But the fact that I had to break into the database adapter code, rather than localising the change within my application had annoyed me.
But not as much as the unit testing. First of all, there was the problem of foreign keys. In MySql it’s easy to switch off foreign key checks, so your fixtures can empty tables and reload the data to their hearts’ content. In MSSql, unsurprisingly, foreign keys are rigidly enforced, so when your fixture empties a table, any deleted detail rows are immediately flagged as an error (quite rightly). Likewise, when inserting a row with a foreign key reference, if the referenced row does not exist, MSSql (quite rightly) disallows the insert. So that leaves your fixtures completely screwed. Secondly, I simply could not get rake to work. It will recreate the test database as required. But the command line tool scptxfr, that rake uses to copy the database structure from the development database to test, simply would not work on my machine. By this point, I was so annoyed I could not be bothered to figure out why. Even if I could get it working, without fixtures the unit tests are pretty useless.
And then, the final straw. We were part way through the development of our latest version of the application. I thought, seeing as we have no unit tests, I better check the web application out. And it was totally broken. Somewhere along the line, some underlying table changes had broken everything that the Rails application needed to work. Without unit tests, the possibility of manually retesting every ancillary application (as the web work tended to be bespoke additions to the standard product) after every version upgrade was unacceptable.
A change of tack
So, this is where we are now. I have started on a new strategy for development that I feel is much safer and simpler. Each web application will have its own database. I will use migrations to define the tables. I will use MySql to develop and test the database. I will use MySql or MSSql for the production database. RForward will be used for deployment on IIS. Apache/FastCGI or LightHttp on *nix.
Each Rails application will have its own web-service built in. This will be SOAP-based. It does not need to be complex and is basically for my use only (so I’m not too bothered about the SOAP vs REST arguments). Plus Delphi 7 imports your SOAP structs into easy to use Delphi classes (as does .Net into .Net classes). This service is there so that a small Delphi synchroniser application can run at set intervals, copying data from one system to another. As this is such a small tool, unit testing ought to be easy. And guess what? The end result will be a set of small applications with a well defined interface between them and the main database. Hopefully, a cleaner architecture with less dependencies, easier testing and higher productivity.
