syedaslam.com

Retrieving random row from a table

2011-04-05T00:00:00+05:30

To retrive a random row from a table you have do just this:

 Model.find :first, :offset => ( Model.count * rand ).to_i

This is fast and requires no custom SQL queries! All this does is count number of rows in a table and select one row at some offset while still having the table ordered by primary key.

It uses offset (limit), not ID number, so it chooses n-th found row, not a row with ID equal to n. So if you have IDs like 1, 2, 4, 8and get 3 at random, this method will take 3rd row, which has ID = 4.

Recover MySQL root Password

2011-03-31T00:00:00+05:30

You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.

 # sudo /etc/init.d/mysql stop

Step # 2: Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password.

 # sudo mysqld_safe --skip-grant-tables &

Step # 3: Connect to mysql server as the root user.

 # mysql -u root

Step # 4: Setup new mysql root account password i.e. reset mysql password.

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Exit and restart the MySQL server.

# sudo /etc/init.d/mysql stop

# sudo /etc/init.d/mysql start

# mysql -u root -p

You have just changed the password for the root user of mysql database.

Highlighting current link within a navigation list in rails

2011-03-28T00:00:00+05:30

It's very common to want to highlight the current link within a navigation list for the current action being performed. If you are using Ajax, then this is easy, handle the onclick event to highlight the current link.

<%= link_to 'Home', 'javascript:void(0);', 
  :onclick => "this.addClassName('youarehere');this.siblings().each(function(s){
  s.removeClassName('youarehere');});", :class => '', :id => 'home' %> 

<%= link_to 'News', 'javascript:void(0);', 
  :onclick => "this.addClassName('youarehere');this.siblings().each(function(s){
  s.removeClassName('youarehere');});", :class => '', :id => 'news' %>

Make sure you have a CSS class called youarehere. The following CSS is just an example.

.youarehere {
    font-size: 11pt;
    text-decoration: underline;
}

If you are not using Ajax, you need to know what the current action and controller is. But how to know what the currect action is (without doing some ugly URL parsing)?

The first thing to do is add the following code to your application_helper.rb file

def section_link(name, options)
  action      = options[:action] || 'index'
  controller  = options[:controller] 
  
    if action == @current_action && controller == @current_controller
      link_to(name, options, :class => 'youarehere')
    else
        link_to(name, options)
    end
end

The above method always expects an :controller key to be passed in with the options hash. If you don't pass the optional :action key, it will assume 'index' as the action being performed.

We need to know what the current_controller and current_action is for the above method to work. Finally put this in your application_controller.rb file, as it makes the current executing action and controller name available to the helper.

before_filter :instantiate_controller_and_action_names

def instantiate_controller_and_action_names
    @current_action         = action_name
    @current_controller = controller_name
end

In your view you have to use:

<%= section_link( "Home", :controller => 'welcome', :action => 'index' ) %> |

<%= section_link( "News", :controller => 'news', :ref => 'home' ) %>

Centering a Fixed-Sized Element

2011-03-22T00:00:00+05:30

Here is one way to center a fixed-width/fixed-height div at the center of its container. This could be adapted to centering text, images, etc. within their containers. Essentially, we do a bit of arithmetic to get the fixed-sized element centered using absolute positioning and margins. Note that the parent container must have a position: relative property for this to work.

div { position: absolute; top: 50%; left: 50%; width: 400px; height: 300px; margin-top: -150px; /* 1/2 of your element height*/ margin-left: -200px; /* 1/2 of your element width */ }

Re-initializing table sequence in postgresql

2011-03-15T00:00:00+05:30

Re-initializing a table sequence is quite normal and required in some cases. I had to do that recently. If the table has data and needs to be preserved, then temporarily you have to move the data, delete the data in original table and re-initialize the sequence and copy the original data again.

Use copy column names to reinitialize key sequence.

In current versions of PostgreSQL you can specify column names with the COPY command to copy in or out only specific columns. This can come in handy if you want to recalculate your generated keys.

Given table:

    CREATE TABLE table_name (
        ID SERIAL PRIMARY KEY,
        name TEXT,
        role TEXT
    );

You can copy out only the name and the role like this:

      \COPY table_name (name, role) to 'something.dat'

And then you will want to delete your original data.

      DELETE FROM table_name;

Then you can reset your sequence. Perhaps you found out later that you wanted to start the id field at 100 instead of 1. To restart your sequence and copy the data back in use:

      ALTER SEQUENCE table_id_seq RESTART 100;
      \COPY people_131 (name, role) from 'something.dat'

This technique can be used to copy in new data if the COPY formatted data does not have the generated key info in it. In that case, you would NOT alter the sequence or delete data from your original table.

You can achieve the same effect of the original example by selecting the data into a temporary table instead of copying out. After you copy the data into the temporary table, delete the original data, reset the sequence and copy it back in.

      CREATE TEMPORARY TABLE people_temp AS SELECT name, role FROM people_131;
      ALTER SEQUENCE people_131_id_seq RESTART 1000;
      DELETE FROM people_131;
      INSERT INTO people_131 (name, role) select name, role from people_temp;

Things to notice:

* The sequence name (if you need it) is table_column_seq.
* The format of the data to copy in must match the columns speficied.

Loading data from multiple files in Ruby using Hash

2011-02-19T00:00:00+05:30

Lately, I have been working applications which require data from an external source to be loaded into the local database. One such application is Sydrea (in the making!), which requires the Drug information to be loaded. Drugs@FDA is freely downloadable compressed zip file which contains about 9 CSV files having Drug related data.

The usual path to data loading is through a rails runner script. I was tired of writing, testing different scripts to load data. I had to come up with one general script which, with minimal customizations, could load data into whatever table.

I created the required models and tables. Database schema for the tables to hold drug related information is available at the Drugs@FDA website. I started with defining a Hash mapping the file containing data, to the Object.

files = {
    'AppDoc.txt'              => 'AppDoc',
    'AppDocType_Lookup.txt'   => 'AppDocTypeLookup',
    'application.txt'         => 'Application',
    'ChemTypeLookup.txt'      => 'ChemicalTypeLookup',
    'DocType_lookup.txt'      => 'DocTypeLookup',
    'product.txt'             => 'Product',
    'Product_tecode.txt'      => 'ProductTECode',
    'RegActionDate.txt'       => 'RegActionDate',
    'ReviewClass_Lookup.txt'  => 'ReviewClassLookup'
}

What I need to do now is iterate over this hash, read file whose name is given by the Key and create new objects of the type Value. To the AR's new method you can pass a hash with key names matching the associated table column names. The column names are given by the file headers. Now, I need a method which would take two arrays and return me a hash. Like given

arr1 = ['col_name1', 'col_name2'] 
arr2 = ['val1', 'val2']

would return

{ 
    'col_name1' => 'val1',
    'col_name2' => 'val2'
}

I came up with a method that does exactly that and I added it to the Array class:

class Array
    def self.to_hash(headers, values) 
        hsh = Hash.new
        headers.each_with_index do |h, i|
            hsh[h.underscore] = values[i]
        end
    
        hsh
    end
end

Then, we're all set to put everything together and load data:

require 'rubygems'
require 'fastercsv'

files.each do |key, value|
    file = "#{RAILS_ROOT}/db/drugsatfda/" + key
    recs = 0
    
    puts "Working with #{value.pluralize}.."
    FasterCSV.foreach(file, :headers => true) do |row|
        begin
            obj = value.constantize.new(Array.to_hash(row.headers, row.fields))
            obj.save
        
            recs += 1
        rescue => e
            puts "Rows processed: " + recs.to_s
            puts e
        end
    end
    puts "Loaded #{recs} #{value.pluralize}"
end

Technicals: Rails 2.3.8, PostgreSQL, FasterCSV

Adding close link to flash messages

2011-01-24T00:00:00+05:30

The flash provides a way to pass temporary objects between actions. Anything you place in the flash will be exposed to the very next action and then cleared out. This is a great way of doing notices and alerts, such as a create action that sets flash[:notice] = "Successfully created" before redirecting to a display action that can then expose the flash to its template. Actually, that exposure is automatically done. But not closing or removing and that flash message will be there till the view is refreshed or a new action is served.

In one of my recent applications I figured a way to add close link to make flash message disappear. Of course, you can employ JavaScript to automatically hide the flash div's after certain time. But, I choose to give an explicit close button / link. In my application template:

<% flash.each do |name, msg| %>
    <%= content_tag :div, :id => "flash_#{name}" do %>
        <%= msg %>
        <%= content_tag :span, :style => "float: right;" do %>
            <%= link_to_function image_tag('icons/cross.png'), 
            :onclick => "document.getElementById('flash_#{name}').style.display='none'" %>
        <% end %>
    <% end %>
<% end %>

A little styling:

#flash_notice, #flash_error, #flash_alert {
    padding: 5px 8px;
    margin: 10px 0;
}

#flash_notice {
    background-color: #FAF0E6;
    border: solid 1px #5D871B;
    color: #5D871B;
}

#flash_error, #flash_alert {
    background-color: #FAF0E6;
    border: solid 1px #5D871B;
    color: #5D871B;
}

You need to have a image 'cross.png' in your 'images/icons'. And the outcome of is:

Connecting to two or more databases from a Rails app

2011-01-18T00:00:00+05:30

The current project I am working on is a re-write of an old application, which essentially was build as an prototype. At that time it was put off due to reasons unknown and now the client has come back for the same application with a new set of requirements and changes to the prototype.

Its a spatial application where everything is centered around 'location' and location information (pincodes etc.,). In the prototype application Location information is stored is stored in 'pinlocations' table. It takes a lot of effort to fill this table with lat, lng and other inforation and I didn't want to repeat it and the schema didn't change as much for this table. So, I decided to populate my new tables from the data in this table.

There are plugins (connection_ninja, secondbase, octopus) available which provide ways to connect to two (or more) databases from a rails application. But, my case was simple (one table) which I could do neatly and easily without a gem overkill.

I had to define temporary models in the application to work with the old database and employ the ' establish_connection' method of the ActiveRecord::Base. This method accepts a hash as input through which you provide the connection information.

class Pinlocation < ActiveRecord::Base
    establish_connection(
        :adapter  => "postgresql",
        :host       => "localhost",
        :username => "*****",
        :password => "*****",
        :database => "old_database"
    )
end

You can check whether the connection is established in the rails console:

 $> location = Pinlocation.first

This loads the first record from the "old_database" connection.

Then a script to read data from the old database and write to new database.

require 'rubygems'

out_file = "db/data/scripts/output.txt"
# I like the output (messages, errors ets.,) to be written to a file 
instead of the console.

open(out_file, 'w') do |f|
    f.puts "Total no. of record to be imported: #{Pinlocation.count}"

    ...

    Pinlocation.all.each do |location_old| 
        begin
            location_new = PinLocation.new(
                :pincode    => location_old.pincode,
                :name       => location_old.name,
                :lat            => location_old.lat,
                :lng            => location_old.lng
            )

            # Then I map the district, state to this location
            ...

            location_new.save

        rescue ActiveRecord::RecordInvalid => invalid
            puts invalid.record.errors
            f.puts invalid.record.errors
        end
    end
end

And I am set.

syedaslam.com version 3!

2011-01-15T00:00:00+05:30

So, what is this brand new blog gonna be about?

Well, first of all, I'd like to introduce myself. My name is Syed Aslam. I'm from Bangalore, India. I've been working in the web industry for quite some time now, so this blog will probably be about new (and old) web technologies, search engine secrets, web design tips and tricks (and many many more, hopefully).

This blog will be my notepad for my crazy experiments in the software development world. This will serve not only to know where I currently stand or where I'm headed, but also to know where I've been.

Oh, and I'd like to say thanks to the Jekyll for making blogging geeky and fun and Heroku for this wonderful (and free!) platform. It looks great and it's handy-coded, so I'll be sure to customize it in a while.

So, subscribe to my feed and stay tuned!