Topic: Executing an external .sql file

I need to execute an external .sql file. I'm guessing

Well what I really need to do is input a large amount of data into a mySQL data base via rails.

This would work like a stored procedure in MSSQL. Maybe I just need to know how to execute my own SQL from a controller?? I'm just not sure. I have outlined below what I am trying to do.

Sorry I am very new to Rails.

thanks for you help



#Create a new week
INSERT INTO weeks(id, user_id, name)

#find the identity of the new week and set it to week_id.
#loop this seven times for the days of the week.

INSERT INTO days(id, title, week_id)

#find the identity of the day and set the day_id
#Create hours for each day
#Loop 24 time per day.

INSERT INTO hours(id, title, day_id)

#end hour loop
#end day loop

Re: Executing an external .sql file

Is the file on the database server? If so, try this through the command line:

mysql -u root -p < /path/to/file.sql

Of course, change the user if necessary. You may want to specify the database too:

mysql -u root -p my_database < /path/to/file.sql

Railscasts - Free Ruby on Rails Screencasts

Re: Executing an external .sql file

Thanks... but I think I was asking this the wrong way.

I need the user, in this case the administrator to be able to add a week to the database.

I have three tables:

weeks
days (related to weeks through week_id)
hours (related to days through day_id)


I need to do the following either via ActiveRecord or Raw SQL when a user clicks a button. What is the best way to this?



#Create a new week
INSERT INTO weeks(id, user_id, name)

#find the identity of the new week and set it to week_id.
#loop this seven times for the days of the week.

INSERT INTO days(id, title, week_id)

#find the identity of the day and set the day_id
#Create hours for each day
#Loop 24 time per day.

INSERT INTO hours(id, title, day_id)

#end hour loop
#end day loop

Last edited by kanden (2007-01-15 16:14:02)

Re: Executing an external .sql file

If you have Week, Day and Hour models you can use the after_create callback to do this:

# in Week model
def after_create
  %w[Sunday Monday Tuesday Wednesday Thursday Friday Saturday].each do |title|
    days.create(:title => title)
  end
end

# in Day model
def after_create
  24.times do |hour|
    hours.create(:title => hour)
  end
end


However, this won't have the greatest performance, so if you need to do this frequently there's probably a better way (executing the command I gave before may work better if you generate the file).

That said, are you certain this data should be stored in the database? Because it can be generated makes me think storing in a database is a little redundant. You could use date/datetime columns in other models to relate it to a time, then generate the weeks/days/hours on the fly whenever you need to. This feels more flexible and should releave a lot of strain on the database.

Railscasts - Free Ruby on Rails Screencasts

Re: Executing an external .sql file

Thanks. This works.

The reason I set it up this way is beacuse there is certain information that goes with each time slot. for example there is a user information for each week as well as each day and hour. These relate to a users table.

Is there a better way to do this?

Thanks

Re: Executing an external .sql file

I would need to know more about your current schema, interface and requirements before I can offer any detailed suggestions. I'm assuming the user information has either a week_id, day_id, or hour_id column and relates to these times in some way?

What I'm thinking is you can replace the day_id column with a simple date type column. You can replace the week_id with a start_day and end_day date columns. You can then replace the hour reference with start/end datetime columns. Although you may not need it, this allows you to have date/time ranges outside of week/day/hour increments.

This is not without its disadvantages. Being able to call a specific week model and grab all the user information is a benefit from your current implementation. But, I think you can create your own class which mimics this functionality without relying heavily on the data in the database since the Time and Date classes built into Ruby already know about weeks/days/hours.

Railscasts - Free Ruby on Rails Screencasts

Re: Executing an external .sql file

Thanks for your help. We appreciate all you do for the rails community.