thr3ads.net - Rails - Need help on simplifying this code (and adding it via Migrations) [Sep 2006]

If this information is useful, please help other people find it:
Share via:

Robert Gabaree

2006-Sep-13 23:38 UTC

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

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




--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Chris Hall

2006-Sep-14 00:01 UTC

head link

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

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

On 9/13/06, Robert Gabaree <lists-n9Q/XG8udXg@public.gmane.org>
wrote:>
> 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
>
>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

johnson_d-j9pdmedNgrk@public.gmane.org

2006-Sep-14 02:19 UTC

head link

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

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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Rails - Sep 2006 - Need help on simplifying this code (and adding it via Migrations)

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

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

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