Array Studio Reading List - a list of useful books

Nested :include ActiveRecord option June 23, 2007

Posted by Slobodan Kovacevic in : Programming, Ruby & Rails , add a comment

While working on a site I had User, Order, OrderItems and Product models and they were related like this:

Order belongs_to User
Order has_many OrderItems
OrderItem belongs_to Product

So, when displaying an order I found myself doing accessing product through order_item, i.e. @order.order_items.product.name.

Although this worked problem was that even though I have told Rails using :include to eager load order_items it didn’t load products, so whenever I accessed a product it executed a separate SQL query. In other words, to display order with 10 products it required 11 SQL queries - hardly optimized.

Rails API documentation doesn’t say anything about eager loading “second” level, but after some searching I found that you can do that you can do nested :includes, like this:

This will eager load User and OrderItems, but it will also eager load Products under each OrderItem. This decreased my number of queries from 11 for 10 product order to only one (big one).

You can read more (for example, it appears that you can nest multiple :includes) about this undocumented feature in ‘The beauty of a nested ‘:include’ option’.

MySQL insert a new row or update old one February 16, 2007

Posted by Slobodan Kovacevic in : Programming, Resources and Links , add a comment

Today I stumbled upon a newish MySQL feature that can often be very useful. Since MySQL 4.1 there’s a non-standard feature (i.e. it’s an extension of SQL standard and won’t work on other databases) that lets you insert a new row, but if it happens that a row with same primary/unique key already exists it will just update that row.

Insert query syntax looks like this:

INSERT INTO table (primarykeycol,col1,col2) VALUES (1,2,3) ON DUPLICATE KEY UPDATE col1=0, col2=col2+1

If there is already a row with primarykeycol set to 1 this query is equal to:

UPDATE table SET col1=0, col2=col2+1 WHERE primarykeycol = 1

Ordinarily to achieve the same result you would have to issue an UPDATE query, then check if there were affected rows and if not issue an INSERT query. This way, you can do everything in one step - first try insert and then update if insert fails.

One situation for which this type of syntax is perfect is when you work with daily counters. For example, you might have a table with PostID, Date and Count columns. Each day you’d have to check if you already created an entry for that day and if so increase the count column - and this can be easily substituted with one INSERT … ON DUPLICATE KEY UPDATE query.

Unfortunately there are some caveats. One being that when you have multiple unique indexes it will act as if you had an OR condition in WHERE clause of UPDATE query. This means that multiple rows should be update, but INSERT … ON DUPLICATE KEY UPDATE will update only one row.

For more information you should read article in MySQL manual: INSERT … ON DUPLICATE KEY UPDATE Syntax

Niceforms 1.0 - final version January 31, 2007

Posted by Slobodan Kovacevic in : Programming, Resources and Links , 2 comments

Over a year ago I wrote about an excellent script called Niceforms and little fix I wrote (see NiceForms with even nicer select). Now a final version has been released - Niceforms 1.0 which solves a lot of problems from previous version.

I still haven’t had chance to look at the new improvements but it seems that Niceforms now supports keyboard only navigation, scalable buttons, increased browser support, etc.

Unfortunately it still has some of the problems I tried to fix more than a year ago. Granted they tried to fix it, but it still has problems. For example, when you click on a select element if you move over some options and then move out it will immediately close the drop down. On the other hand if you open select element and don’t move over to any options (i.e. click on down arrow), but you move the mouse away from it - the select will remain open, which can again lead to strange effect where you can have a lot of open drop downs.

Perhaps that can be fixed in future versions - or at least I will try to make a patch for it.

If you want to get Niceforms 1.0 you can from author’s site.

Rails Simplified: Migrations December 25, 2006

Posted by Slobodan Kovacevic in : Programming, Ruby & Rails , add a comment

When you start learning Ruby and Ruby on Rails you are faced with very powerful set of tools and sometimes it can be overwhelming. You are often faced with long cryptic explanation of some feature and all you want is a quick overview of what it does and how you could use it.

Rails Simplified will be a series of posts is giving you a brief description of various Ruby / Rails related features, as well as pointer to other articles that give you a more in-depth view of those features. Rails Simplified - Migrations will be the first one in this series…

Rails Migrations

What are migrations?

Migrations are an easy way to manage your database schema in a way that you can easily synchronize it between different servers (for example between development and production server). Simply put: you can write code that can upgrade or downgrade your database schema - think of it as database versioning.

If you did any programming you probably know how hard it can be to keep track of database changes - migrations make your life a lot easier.

How do you use migrations?

• Create migration using: ./script/generate migration ShortDescriptionChange
• Edit file that was created in db/migrate dir and describe changes that need to be made
• Run rake migrate which brings your database up to date

How do you describe schema changes?

Once you create migration defined class will have two methods self.up (used when upgrading schema) and self.down (downgrade schema). If you want to create a table you’d write code like this:

  def self.up
    create_table :lists do |t|
      t.column :name, :string
      t.column :description, :string
      t.column :created_on, :datetime
    end
  end

  def self.down
    drop_table :lists
  end

First method defines table that needs to be created (notice that it’s database independent) and second provides a way to undo changes made by this migration.

These are just a simple example and you can do a lot more using migrations - change existing tables, delete tables, insert default values to tables and if necessary you can even execute plain SQL code.

Where can I get more information about Rails migrations?

• Rails API Class ActiveRecord::Migration - it describes everything you can do with Rails migrations.
• Screencast about Rails Migrations
• The Joy of Migrations - more detailed post on migrations.
• Rails Migration Cheat Sheet
• Foreign key and rails migration - an excellent helper method that fixes an oversight in Rails migrations (you cannot define foreign keys natively).

The CAPTCHA Alternatives December 9, 2006

Posted by Slobodan Kovacevic in : Programming, Web , 22 comments

Most people know what CAPTCHA is and if they don’t then I am sure that they have seen one. Furthermore, I am sure that everyone has been molested by bad CAPTCHA. Once I tried to register at a forum and it took me 8 times to get the CAPTCHA right - 8 times! I had to register at that forum, but if I didn’t after couple of tries I would just presume that the CAPTCHA isn’t working and I would have closed the browser.

Of course CAPTCHA is very useful and it can help you reduce spam, false/automatic registrations, etc. But at the same time is a big accessibility and usability problem - which means that your site or web app can lose precious visitors/users. In fact inaccessibility is such a big problem that there’s W3C document outlining CAPTCHA problems and possible solutions.

All this means that there are plenty of people searching for an alternative way to tell computers and humans apart. Here are some of the proposed alternatives and fixes…
(more…)

Amazon on Rails - search Amazon using Rails and Ruby/Amazon August 31, 2006

Posted by Slobodan Kovacevic in : Programming, Ruby & Rails , 4 comments

Currently I am working on my first big Rails application and I needed an simple and quick way to get matching movies and music based on user entered keywords. The best place to get such information is Amazon as they have almost anything in their store.

So I decided to write a small Rails application that takes input from user and displays products from Amazon matching that criteria - in essence Amazon search. In the end it will look something like this - Amazon on Rails
(more…)

MySQL: Get total number of rows when using LIMIT August 11, 2006

Posted by Slobodan Kovacevic in : Programming , 14 comments

Every now and then you need to limit the number of rows MySQL returns, i.e. use the LIMIT clause. Result set pagination is by far the most often usage of LIMIT clause, since you usually want to select only rows you’ll be displaying on certain page.

The problem is that for pagination you also need total number of rows in a result set, so you know how many pages you’ll have. This usually means that you need to execute query two times. First query is for counting total number of rows without LIMIT. Second query is exactly the same as the first, just without LIMIT and it will actually retrieve required data. You would need two queries like these:

SELECT COUNT(*) FROM users WHERE name LIKE 'a%';

SELECT name, email FROM users WHERE name LIKE 'a%' LIMIT 10;

Now, this is not such a big problem when you have small result sets and/or simple queries. But if you have a complex query that joins several tables and takes a while to execute - well, you probably wouldn’t want to execute it twice and waste server resources.

Luckily since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWS option in your query which will tell MySQL to count total number of rows disregarding LIMIT clause. You still need to execute a second query in order to retrieve row count, but it’s a simple query and not as complex as your query which retrieved the data.

Usage is pretty simple. In you main query you need to add SQL_CALC_FOUND_ROWS option just after SELECT and in second query you need to use FOUND_ROWS() function to get total number of rows. Queries would look like this:

SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;

SELECT FOUND_ROWS();

The only limitation is that you must call second query immediately after the first one because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.

Although this solution also requires two queries it’s much more faster, as you execute the main query only once.

You can read more about SQL_CALC_FOUND_ROWS and FOUND_ROWS() in MySQL docs.

Disable ctrl + n and other ctrl + key combinations in JavaScript June 17, 2006

Posted by Slobodan Kovacevic in : Programming , comments closed

Few days ago Vijay asked if there’s a way to disable ctrl + n combination (open the new window shortcut. So I set out to create a small Java Script that disables any ctrl + key combination (e.g. ctrl + v, ctrl + c, ctrl + a, etc.).

Script is a bit more complicated than Disable form submit on enter keypress and it should work in both Fire Fox and Internet Explorer.

And you just add this to the field where you’d like to disable keys:

Although the key combinations are disabled only on one field the same script can be easily modified to disable ctrl + key combinations on whole page.

Also if you are looking for something more sophisticated and with more features you might want to take a look at a visitor comment that contains DisableKeys.js script that gives you more control and you can disable almost any key and/or key combination, including ctrl + key and alt + key.

Ruby installation on Windows June 10, 2006

Posted by Slobodan Kovacevic in : Programming, Ruby & Rails , 3 comments

Recently I’ve started playing around with Ruby and Ruby On Rails on our hosted server. Since I liked it a lot I have finally decided to try to install it on our local Windows server. While I was doing it I found that most of the online tutorials are not all completely accurate, i.e. they need a bit of tweaking… So I started logging every step I did.
(more…)

NiceForms with even nicer select September 11, 2005

Posted by Slobodan Kovacevic in : Programming , 31 comments

UPDATE 31-Jan-2007: There’s a new version of Niceforms. For more info see this post.

Few months ago I stumbled upon a great piece of JavaScript called NiceForms by Lucian Slatineanu. The idea of script is to replace all form elements with visually much nicer and more controllable elements. Great thing about it is that it replaces all form elements automatically, meaning that you don’t need to make any changes to your form. As a consequence if JavaScript is turned off it just degrade to ordinary form.

NiceForms still has some problems - for example with accessibility such as tab element switching, with changing text size manually in FireFox, etc. (for more info visit Lucian’s site).

One particular problem it has is with select elements:

- You can only open select by clicking on right arrow
- It doesn’t close automatically (i.e. when you click somewhere else), you either need to select an option or click right arrow again. This can create some strange effects, for example you can have more then one drop down option list open at the time.
- When first loaded it has some default text instead of currently selected option, which can lead to problems if you have pre-selected an option in HTML.

So, I had some time and decided to try to fix this. Hopefully these fixes will be included in following versions.

If you don’t want to read all the details you can just see modified NiceForms in action or download a ZIP containing the whole modified NiceForms example.

If you want to read everything, then here is what I did…
(more…)

Copyright © 2006 Array Studio