Tag: mssql


On competence #2

Strange things do happen. They just do. But not all of them are strange bad, some are strange good. For example, I just wept like a baby. It was the end of season 4 of Dr. House – well.. in this respect I’m not quite sure whether it’s a good thing. To be alone with strange feelings on a Saturday evening.

On the other hand, admittedly, that’s not what I wanted to write about.

Yesterday began just like the the day before yesterday ended. I saw somebody trying to install Microsoft’s sequelserver on a Windows machine. Mind the fact that current versions of both programs were chosen. Nevertheless it seemed quite impossible just to install it. Crazy thing. One might think that programs of the same vendor should cooperate more easily – at least if both are bought for insane prices and labeled to be compliant to one another. Lucky for me, that’s nothing I have to care about – I already won my fight against MSSQL; all I needed were five servers and three versions of Windows. Probably somebody should mention to Microsoft that there are other operating systems available; equipped with sequelservers off the shelf. Some even are free and performing just as well if not better(I mean.. there’s no LIMIT/OFFSET implemenation in MSSQL; how good can it be?)

Anyways, to once again feel corrupted by such a pitiful sight is nothing special – as I have just pointed out. Thereafter I asked my dear and sincerely noble friend Carl Duevel to help me make the world a better place. And guess what – my wish instantly came true on Friday, 13th. What about all that blabber concerning bad luck and stuff. In my opinion people should get punished if reporting sick on this day; it is clearly economically abstruse to let all these menial dumbheads carry on doing nothing but guarding their bed sheets. But not Carl. He wrote an excellent article on Javas RegEx implementation. That alone has made my day, but there’s more – he’s mocking the use of inferior technology(aka Flash*choke*) only a couple of hours later. A great start for a newly brightened future is before us. Behold, world, for Captain Code will show you how it is done! I can’t even tell how much I’m looking forward to read frequently of his ulterior insights. Those have have been part of the few sorrows I hold on behalf of leaving Stuttgart; I had to physically leave him behind. On the other hand, maybe, just maybe, that might have been one of the glitches needed for him to finally turn public.

The most interesting thing could probably be, that while I was writing this post, I experienced like the tenth power failure in this wonderful building. One of my housemates came home, started her oversized electrical heater – and boom. Well, I understand that she needs it. Our main heating system doesn’t work properly and in her room it doesn’t work at all. Unlucky for us was that she tangled with the main fuses that thus went byebye, too.

1 comment » | personal

p_pagination 
Sortable tables and pagination on any database for RoR

Lately I have been working on several projects that condemn me to use Microsofts SQLServer, which has led me to believe that there is something missing in the RoR core to make it really a sound inclusion for business applications. Whilst there are very good implementations for pagination and sortable tables at hand, these won’t work if your database doesn’t have an auto_increment id or you want to use customized SQL code instead of working with the ActiveRecord model; and believe me – there are lots of databases out there that can’t be changed, because other applications need them in order to function. There are also good uses for custom SQL some times when using Oracle or MSSQL, because they feature their very own toolset to optimize queries in a fashion where Ruby just can’t adapt whilst implementing a generic interface for all potential backends.

So I started working on my very own plugin that would help reduce writing redundant code over and over again. I have hosted a demo version right here which should look like this:

As you can see the interface really is straight forward; click the title to sort the column – and click a number or an arrow to move from page to page.

The nice thing is that this implementation should work with about any database format; and any SQL or ActiveRecord statement. Let me show you how much code you’d have to write to implement the table from above:

In the controller you’d fetch the data with something like:

session[:data] = StUser.find(:all, :limit => 600)

or even something as ugly as

session[:data] = StUser.find_by_sql("select top 600 * from st_users")

Now to the interesting part – in the view you will only have to write one single line; p_pagination is generic – meaning it automatically adjusts to the number of columns and the input data types.

<%= p_paginate(session[:data], {:max => 10,
:page => session[:page].to_i},
"login", "MAIL", "FIRSTNAME", "LASTNAME", "id") %>

Just give it the database resultset(session[:data]), the number of rows per page and the attributes that are to be read from the resultset.

Now you have seen the might and the first drawback of p_pagination. All data is currently saved in the session, which means that the whole resultset is held available – many of you will now think this is very inefficient and draws performance and memory and I would be inclined to support your thought; only if databases like MSSQL supported the allmighty LIMIT and OFFSET commands like MySQL does. Since it doesn’t, I have deliberately chosen this path, because the needed workarounds in MSSQL like select top 10 * from users where login not in (select top 5 login from users) will result in a massive table search – and if your legacy database works on four text columns that alltogether emulate a primary key in userspace.. then you will really blast your performance away.
Anyway – storing the resultset in the session is not that bad, either, because RoR supports storing session information back in the database as explained in http://wiki.rubyonrails.org/rails/pages/HowtoChangeSessionStore. However even in the database the space for each session is limited, so this current implementation won’t perform on massive data with thousands of rows – but on smaller data collections it does the job pretty well as you can see.

There’s one more drawback; my code doesn’t seem to work on all browsers properly – I designed it using Firefox3 and IE6 where it just works fine. But I have seen that Safari displays the spinner to the right of the pagination bar while Firefox2 will scramble the bar and render it useless; Opera I haven’t tried yet.

So this is it – I’d like to call this a preview alpha release even though it already went productive on some small systems where it does its job as expected.

In a following post I will explain how to install p_pagination as a plugin.

Comment » | articles