From: ball <chezball@...>
Date: 2009-03-12T09:12:06+09:00
Subject: help with refactor and db/ar advice

Hi all,
I have a number of delimited data files that I would like to import
into MySql. I am using ActiveRecord to create the schema and do the
import of the data. Code snippets follows for how I am doing one of
the many files. However, the code bloats when I do it for all of them.
Now, of course, I can't get around creating a schema for each table,
but it seems that the function that loads the code is a bit redundant.
So, my end goal in this post is just to learn a few things and create
some cleaner code, so I would love your comments.

My questions are:
1. Is there a way to take advantage of the schema so that I don't have
to re-write it in the load function?
2. Is there a way I can write a generic load function (taking
advantage of #1 if available, or not if not).
3. What is the "better" way (than ][1..-2]) to strip a string of a
starting/ending ~
4. Should I just use an AR generated primary key, rather than the data
sets ASCII primary key? (many of the tables in the datasets have
composite keys, in which I will not implement, so I am curious if I
just create all of the primary keys through AR insted).
5. Any other advice?

Thanks in advance!

===========> CURRENT CODE <=============

  class FoodDescription < ActiveRecord::Base
  end

  def create_food_descriptions
    ActiveRecord::Schema.define do
      create_table(:food_descriptions, :primary_key =>
'NDB_No', :options => 'DEFAULT CHARSET=utf8') do |table|
          table.column :NDB_No, :string, :limit => 5, :null => false
          table.column :FdGrp_Cd, :string, :limit => 4, :null => false
          table.column :Long_Desc, :string, :limit => 200, :null =>
false
          table.column :Shrt_Desc, :string, :limit => 60, :null =>
false
          table.column :CommName, :string, :limit => 100, :null =>
true
          table.column :ManufacName, :string, :limit => 65, :null =>
true
          table.column :Survey, :string, :limit => 1, :null => true
          table.column :Ref_desc, :string, :limit => 135, :null =>
true
          table.column :Refuse, :string, :limit => 2, :null => true
          table.column :SciName, :string, :limit => 65, :null => true
          table.column :N_Factor, :float, :null => true
          table.column :Pro_Factor, :float, :null => true
          table.column :Fat_Factor, :float, :null => true
          table.column :CHO_Factor, :float, :null => true
      end
      puts "  ... food_descriptions created"
    end
  end

  def load_food_descriptions( food_desc_file_name )
    # Loop through the file, assuming the following format:
    #   ~01001~^~0100~^~Butter, salted~^~BUTTER,WITH
SALT~^~~^~~^~Y~^~~^0^~~^6.38^4.27^8.79^3.87
    #    ndbnum, fdgrp, long_desc, shrt_desc, common_name,
manufacturer_name, survey,
    #    refuse_desc, percent_refuse, science_name, n_fac, pro_fac,
fat_fac, CHO_fac

    food_desc_file = File.open(food_desc_file_name)
    food_desc_file.each do |line|
      # caret (^) is the delimeter
      # squiglies are the string delimeters: ~string~
      #  hence below, we split with 1..-2 to remove them if a string
      record_split = line.split('^')

      FoodDescription.create(
        :NDB_No => record_split[0][1..-2],
        :FdGrp_Cd => record_split[1][1..-2],
        :Long_Desc => record_split[2][1..-2],
        :Shrt_Desc => record_split[3][1..-2],
        :CommName => record_split[4][1..-2],
        :ManufacName => record_split[5][1..-2],
        :Survey => record_split[6][1..-2],
        :Ref_desc => record_split[7][1..-2],
        :Refuse => record_split[8][1..-2],
        :SciName => record_split[9][1..-2],
        :N_Factor => record_split[10],
        :Pro_Factor => record_split[11],
        :Fat_Factor => record_split[12],
        :CHO_Factor => record_split[13]
        );
    end
  end