# Help with refactor and db/ar advice

**URL:** <https://www.ruby-forum.com/t/help-with-refactor-and-db-ar-advice/162619>\
**Category:** Ruby\
**Created:** [March 12, 2009, 1:15am UTC](https://www.ruby-forum.com/t/help-with-refactor-and-db-ar-advice/162619 "2009-03-12T01:15:57Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ball](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/858c86/32.png) [@ball](https://www.ruby-forum.com/u/ball)\
**Post date:** [March 12, 2009, 1:15am UTC](https://www.ruby-forum.com/t/help-with-refactor-and-db-ar-advice/162619/1 "2009-03-12T01:15:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ball](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/858c86/32.png) [@ball](https://www.ruby-forum.com/u/ball)\
**Post date:** [March 12, 2009, 9:52am UTC](https://www.ruby-forum.com/t/help-with-refactor-and-db-ar-advice/162619/2 "2009-03-12T09:52:46Z")

</div>

ball wrote:

> 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?

puts FoodDescription.columns.inspect  
puts FoodDescription.map { |c| c.name }.inspect

> 1. Is there a way I can write a generic load function (taking  
> advantage of #1 if available, or not if not).

As long as the columns in the text source appear in the same order as  
the columns in the database definition you should be fine. I think this  
ordering is guaranteed by the database, otherwise you couldn’t  
meaningfully do “INSERT INTO foo VALUES (x,y,z)” (without naming the  
columns) or “SELECT \* FROM foo”

> 1. What is the “better” way (than ][1…-2]) to strip a string of a  
> starting/ending ~

Do it all in one go. A couple of options:

records = line.split(/^/).map { |rec| rec[1…-2] }  
records = line.scan(/~([^~^]\*)~/)

but they won’t work unless _all_ your fields are ~ delimited. So this  
might work better for you:

records = line.split(/^/).map { |rec| rec.sub(/~(.\*)~) { $1 } }

> 1. 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).

ActiveRecord has some hard-coded assumptions about primary keys. If you  
override its assumption that the key is called “id” then it won’t  
generate keys automatically for new records. If you have non-integer  
primary key then it may or may not work. It certainly doesn’t handle  
composite primary keys; I saw a plugin which claimed to do this some  
time ago, but I don’t know if it’s been kept up to date.

So my advice with AR is: if you are working with an existing  
database/schema that you don’t control then try using non-standard  
primary keys, but if you control the schema yourself, follow AR’s  
assumption of an integer primary key.