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.

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

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:

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.

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!