Need help on simplifying this code (and adding it via Migrations)

rubyonrails-talk
1

Hi guys,

I have a file that contains nutritional information for various foods (around 7,000 in total). Its format looks like this (for example):

~04133~^~SALAD DRSNG,FRENCH,HOME RECIPE~^24.20^631^0.10^70.20^1.80^3.40^0.0^^6^0.20^0^3^24^658^0.00^^^1.6^0.6^0.010^0.020^0.130^0.000^0.000^0^0^0^0^0.00^514^26^0^8.00^^^^^^^12.600^20.700^33.700^0^14.00^~1 tablespoon~^220.00^~1 cup~^0

I'm trying to add these values (I've seperated them into arrays) via migrations and so need to create a column for each value and add the value to the column. I tried something like this, which seems to work:

def self.up      create_table :foods do |t|        t.column :ndb_no, :string        t.column :shrt_desc, :string        t.column :water, :string        t.column :energ_kcal, :string        t.column :protein, :string       .. snip .. (50 t.column lines in total)     end

     IO.foreach("lib/usda_db_files/abbr_version") do |line|        new_line = line.strip.gsub(/~/,'').split('^')        execute("INSERT INTO foods (ndb_no, shrt_desc, water, energ_kcal, protein) VALUES ('#{new_line[0]}','#{new_line[1]}','#{new_line[2]}','#{new_line[3]}','#{new_line[4]}')")      end end

But, I have 50 t.column lines in total. I just showed you a small sniplet. If I was to do this method, I would have to re-type all 50 values into the ( ) fields of the execute statement, and then continue to add #{new_line[new_number]} after I do one. This can get confusing, as there's 50 values to do, and would take an aweful long time to write out not to mention it looks messy.

Is there a way to simplify this process and do it quicker, or am I out of luck?

Thanks, Rob

2

you should just be able to do

execute("INSERT INTO `foods` (`" + Food.column_names.join("`,`") + "`) VALUES ('" + new_line.join("','" + "')")

however, i am not sure on a couple of things...

1) if column_names returns the column names in the correct order 2) if this way is database agnostic

but it's worth a shot

Chris

3

Data model change:

Separate the food from the food attributes and turn the table that is giving you headaches 90 degrees.

You have three manageable tables instead of one unwieldy table, and migrations are a breeze because new attributes are just data (not table changes).

There is a small performance hit, exacerbated by rails insistence on building and preparing the SQL every time you run the query, but for small systems on decent hardware the database hit shouldn't be too bad.   You only have 7,000 rows, so you are looking at IO hit of 7 I/O's per select on a generational database, or 4 I/O per hit on a locking RDBMS with a good optimizer and support for index only scans.

This entire schema will most likely end up in the buffer pool, so you should rarely hit the actual physical store on selects.

create table foods (    id integer not null primary key,    name varchar (128) not null,    recipe memo (8192) )

create table attributes (    id integer not null primary key,    name varchar (128) not null,    type varchar(20) )

create table attributes_foods (    attribute_id integer not null references attributes(id),    food_id integer not null references foods(id),    nvalue numeric(10,4),    cvalue varchar(50),    primary key (attribute_id, food_id) }

I hope this helps.