Need help on simplifying this code (and adding it via Migrat

Rails
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.6
00^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.