I see how you can use rake and ActiveRecord outside of rails, like in the migrations zip files shown last night. In either case, you are using ruby migrations to create and change the database and in some cases enter the actual data. If I had a CSV file of data that I wanted to put into a table I created with the needed schema, would I write a migration to import the CSV? Or do people create the needed table to hold the data they have, then use whatever tools the underlying DB ( pgsql, sqlite, etc... ) has to import data?
Yes, you could import data during a data migration from a .csv or a YAML file. In AWDR, see the section on data migrations, where they specifically discuss importing from YAML. For CSV, you would need to use the CSV class (much improved for 1.9 -- http://www.ruby-doc.org/ruby-1.9/classes/CSV.html).
Do note, though, that it is somewhat awkward to load data through migrations. As I noted in lectures, Rails 3.0 will be moving to this "seed" idea for loading data, which will be outside the migration process (I think I provided a link in the slides).
You will have to decide that depending on what you're doing. If it's data for a table you just created, you're also dropping the table in the reverse direction, so there's no need to remove the data.
At other times, it will be appropriate to delete the data.
You'll just have to think it through in terms of the state of your application and your data.
In this case, you are starting with something you know about a Stock (its id), and you want associated users.
"Out of the box," you can do this (note: for this to work you will need to remove the database, tweak the models, and run all of the migrations as I describe below):
I haven't talked about include and conditions much yet. What the "find" above is saying is: Get me all of the users, join to the holdings and stocks, and then limit the rows to the ones where the stock symbol is IBM.
But let's think about the problem a different way -- going from the Stock we know, and finding all of its users.
To understand this, you will need to look at the code. Now bear with me. After you've run the migrations, there is a crucial bit you have to understand.
An important note about the migrations3 project. Before running the migrations, you will need to go into the Stock and User models and change the associations so that the migrations will run. As you will recall from lecture, one thing I pointed out is that as you add migrations, you have to change the associations in your models. In this case, the models are in their final state. To get the migrations to run, you want to:
rm db/development.sqlite3
Then make sure the models are right to get the migrations to run: Make sure that the associations are set for the state before the holdings table is created. For example, in stock.rb, it should look like this:
# Use this after we have the holdings table
# has_many :holdings
# has_many :users, :through => :holdings
# Use this before we have the holdings table
belongs_to :user
A similar change needs to be done in user.rb
Now you can do
rake db:migrate
ruby demo1.rb
(Running demo1.rb populates the database.)
Finally, go BACK IN to the stock.rb and user.rb files, and switch the associations to the way they are supposed to be AFTER the migrations have run.
Now, the crucial bit:
In lecture I said that you have to do stuff in your migrations and in your models.
In this case, you want to start with a Stock, and find all of the associated Users.
But: I never defined such an association! I only defined an association to find all Holdings for a Stock (look at the source for stock.rb). The foreign keys are there -- there is the latent possibility of creating such an association between Stock and User -- but until I actually declare the association, there is no way for ActiveRecord to follow the keys from Stock to User. Why? Because I might not want this. Associations take some work for the framework, and, generally, you want to model the associations you're really going to use. In my "mental model" of Users, Holdings, and Stocks, I knew I wanted to be able to find all Stocks for a particular User, but I wasn't that interested in the reverse association.
So let's add it. What we need is an association "users" on Stock so that we can move from a Stock to its Users VIA the holdings table. This means adding, in stock.rb,
has_many :users, :through => :holdings
With that all done, here's my run of demo1.rb and, afterwards, an irb session that shows me finding all of the Users associated with the 'IBM' stock:
jgn:migrations3 jgn$ ruby demo1.rb
User Load (0.3ms) SELECT * FROM "users" WHERE ("users"."first_name" = 'John') LIMIT 1
Holding Load (0.4ms) SELECT * FROM "holdings" WHERE ("holdings".user_id = 1)
Holding Destroy (1.1ms) DELETE FROM "holdings" WHERE "id" = 4
Holding Destroy (0.2ms) DELETE FROM "holdings" WHERE "id" = 5
Holding Destroy (0.1ms) DELETE FROM "holdings" WHERE "id" = 6
Stock Load (0.5ms) SELECT * FROM "stocks" WHERE ("stocks"."company" = 'IBM') LIMIT 1
Stock Load (0.3ms) SELECT * FROM "stocks" WHERE ("stocks"."company" = 'Dell Computers') LIMIT 1
Stock Load (0.4ms) SELECT * FROM "stocks" WHERE ("stocks"."company" = 'Sun Microsystems') LIMIT 1
Holding Create (0.4ms) INSERT INTO "holdings" ("user_id", "stock_id", "quantity") VALUES(1, 1, 100)
Holding Create (0.3ms) INSERT INTO "holdings" ("user_id", "stock_id", "quantity") VALUES(1, 3, 100)
Holding Create (0.3ms) INSERT INTO "holdings" ("user_id", "stock_id", "quantity") VALUES(1, 2, 500)
Symbol: IBM - 100
Symbol: DELL - 100
Symbol: JAVA - 500
jgn:migrations3 jgn$ irb
irb(main):001:0> require 'setup'
=> true
irb(main):002:0> ibm = Stock.find_by_symbol('IBM')
Stock Load (0.2ms) SELECT * FROM "stocks" WHERE ("stocks"."symbol" = 'IBM') LIMIT 1
=> #
irb(main):003:0> ibm.users
User Load (0.5ms) SELECT "users".* FROM "users" INNER JOIN "holdings" ON "users".id = "holdings".user_id WHERE (("holdings".stock_id = 1))
=> [#]
irb(main):004:0>
Ah, yes, so now I see that the "references" method is an id-creating-alternative to a method like "string" or "integer" which acts on the table creation object which was passed to the block. "references" is really a column-creating method.
John, I'd be happy to re-ask the question, but I'm asynchronous--living in Spain, 6 hours ahead.
ActiveRecord is a really big API, and there are a number of things I won't be discussing: Those are: Single table inheritance, acts_as_list, acts_as_tree, extending associations, and a few other topics (single table inheritance [STI] is one that students are frequently interested in, but my experience with STI, and the experience of other developers, has been fairly negative).
I *do* intend to talk about polymorphic, but not tonight.
The idea is this:
-- You have, say, a comments table.
-- But you want to have comments associated with different other tables (for example, comments on blog posts; comments on blog pages; etc.). So you allow the FK in comments to point to different tables. In other words, the other tables are "commentable."
I see how you can use rake and ActiveRecord outside of rails, like in the migrations zip files shown last night. In either case, you are using ruby migrations to create and change the database and in some cases enter the actual data. If I had a CSV file of data that I wanted to put into a table I created with the needed schema, would I write a migration to import the CSV? Or do people create the needed table to hold the data they have, then use whatever tools the underlying DB ( pgsql, sqlite, etc... ) has to import data?
thanks
-peter
@peter
Yes, you could import data during a data migration from a .csv or a YAML file. In AWDR, see the section on data migrations, where they specifically discuss importing from YAML. For CSV, you would need to use the CSV class (much improved for 1.9 -- http://www.ruby-doc.org/ruby-1.9/classes/CSV.html).
@john
Got it. So it is standard to load your data with a migration then. I can see that it is all about using the ORM to interact with the RDB. Cool.
Thanks, have a good weekend, go Sox!
-peter
@peter
Do note, though, that it is somewhat awkward to load data through migrations. As I noted in lectures, Rails 3.0 will be moving to this "seed" idea for loading data, which will be outside the migration process (I think I provided a link in the slides).
What would be appropriate to put in the down() method of a migration whose up() method adds data in bulk from a CSV or YAML file?
@Ron Newman
You will have to decide that depending on what you're doing. If it's data for a table you just created, you're also dropping the table in the reverse direction, so there's no need to remove the data.
At other times, it will be appropriate to delete the data.
You'll just have to think it through in terms of the state of your application and your data.
It might be challenging to remove just the added data, as opposed to all of the data in the table.
@Ron Newman
Sometimes you just need to raise IrreversibleMigration.
At the end of the lecture you mentioned the find_by_xxx methods. Can they also be used with holdings? So in your example, something like:
User.find_by_stock(stock_id)
@Gabriel
Not quite.
In this case, you are starting with something you know about a Stock (its id), and you want associated users.
"Out of the box," you can do this (note: for this to work you will need to remove the database, tweak the models, and run all of the migrations as I describe below):
I haven't talked about include and conditions much yet. What the "find" above is saying is: Get me all of the users, join to the holdings and stocks, and then limit the rows to the ones where the stock symbol is IBM.
But let's think about the problem a different way -- going from the Stock we know, and finding all of its users.
To understand this, you will need to look at the code. Now bear with me. After you've run the migrations, there is a crucial bit you have to understand.
An important note about the migrations3 project. Before running the migrations, you will need to go into the Stock and User models and change the associations so that the migrations will run. As you will recall from lecture, one thing I pointed out is that as you add migrations, you have to change the associations in your models. In this case, the models are in their final state. To get the migrations to run, you want to:
Then make sure the models are right to get the migrations to run: Make sure that the associations are set for the state before the holdings table is created. For example, in stock.rb, it should look like this:
A similar change needs to be done in user.rb
Now you can do
(Running demo1.rb populates the database.)
Finally, go BACK IN to the stock.rb and user.rb files, and switch the associations to the way they are supposed to be AFTER the migrations have run.
Now, the crucial bit:
In lecture I said that you have to do stuff in your migrations and in your models.
In this case, you want to start with a Stock, and find all of the associated Users.
But: I never defined such an association! I only defined an association to find all Holdings for a Stock (look at the source for stock.rb). The foreign keys are there -- there is the latent possibility of creating such an association between Stock and User -- but until I actually declare the association, there is no way for ActiveRecord to follow the keys from Stock to User. Why? Because I might not want this. Associations take some work for the framework, and, generally, you want to model the associations you're really going to use. In my "mental model" of Users, Holdings, and Stocks, I knew I wanted to be able to find all Stocks for a particular User, but I wasn't that interested in the reverse association.
So let's add it. What we need is an association "users" on Stock so that we can move from a Stock to its Users VIA the holdings table. This means adding, in stock.rb,
With that all done, here's my run of demo1.rb and, afterwards, an irb session that shows me finding all of the Users associated with the 'IBM' stock:
jgn:migrations3 jgn$ ruby demo1.rb User Load (0.3ms) SELECT * FROM "users" WHERE ("users"."first_name" = 'John') LIMIT 1 Holding Load (0.4ms) SELECT * FROM "holdings" WHERE ("holdings".user_id = 1) Holding Destroy (1.1ms) DELETE FROM "holdings" WHERE "id" = 4 Holding Destroy (0.2ms) DELETE FROM "holdings" WHERE "id" = 5 Holding Destroy (0.1ms) DELETE FROM "holdings" WHERE "id" = 6 Stock Load (0.5ms) SELECT * FROM "stocks" WHERE ("stocks"."company" = 'IBM') LIMIT 1 Stock Load (0.3ms) SELECT * FROM "stocks" WHERE ("stocks"."company" = 'Dell Computers') LIMIT 1 Stock Load (0.4ms) SELECT * FROM "stocks" WHERE ("stocks"."company" = 'Sun Microsystems') LIMIT 1 Holding Create (0.4ms) INSERT INTO "holdings" ("user_id", "stock_id", "quantity") VALUES(1, 1, 100) Holding Create (0.3ms) INSERT INTO "holdings" ("user_id", "stock_id", "quantity") VALUES(1, 3, 100) Holding Create (0.3ms) INSERT INTO "holdings" ("user_id", "stock_id", "quantity") VALUES(1, 2, 500) Symbol: IBM - 100 Symbol: DELL - 100 Symbol: JAVA - 500 jgn:migrations3 jgn$ irb irb(main):001:0> require 'setup' => true irb(main):002:0> ibm = Stock.find_by_symbol('IBM') Stock Load (0.2ms) SELECT * FROM "stocks" WHERE ("stocks"."symbol" = 'IBM') LIMIT 1 => #
irb(main):003:0> ibm.users
User Load (0.5ms) SELECT "users".* FROM "users" INNER JOIN "holdings" ON "users".id = "holdings".user_id WHERE (("holdings".stock_id = 1))
=> [#]
irb(main):004:0>
There you have it.
Sorry if I missed this elsewhere, but in the class samples, the antidote (i.e., the down() method) for adding a column reference was:
remove_column :stocks, :user_id
where the up() method to create the reference was:
s.references :user
Does this down() method just remove the reference or does it remove the whole column, as implied by the method name, "remove_column?"
Thanks,
Mike
@Mike Bond
Good question.
One thing I mentioned in lecture is that ActiveRecord does NOT create foreign key constraints.
This means that when, in a migration, you say:
s.references :user
It ONLY means that a column will be created called user_id, which is an int, and which "auto increments" in the database.
It does NOT add a database level constraint.
So . . . it removes the column. There is no "reference" in the database, just a column called user_id, which can SERVE to join tables.
Mike, you might want to ask this question again when I'm lecturing.
just to clarify, the "user_id" column does not auto increment in the Stocks table. The 'id' column auto increments in the Users table.
Ah, yes, so now I see that the "references" method is an id-creating-alternative to a method like "string" or "integer" which acts on the table creation object which was passed to the block. "references" is really a column-creating method.
John, I'd be happy to re-ask the question, but I'm asynchronous--living in Spain, 6 hours ahead.
@Ron Newman
Right -- sorry. I should not even answer questions during the day!
@Mike Bond
You'll have to use "gem install time_travel" (joke)
In the documentation, I find this statement:
TableDefinition#references will add an appropriately-named _id column, plus a corresponding _type column if the :polymorphic option is supplied.
I don't recall hearing anything about the ':polymorphic option' -- does that come later in the course?
@Ron Newman
ActiveRecord is a really big API, and there are a number of things I won't be discussing: Those are: Single table inheritance, acts_as_list, acts_as_tree, extending associations, and a few other topics (single table inheritance [STI] is one that students are frequently interested in, but my experience with STI, and the experience of other developers, has been fairly negative).
I *do* intend to talk about polymorphic, but not tonight.
The idea is this:
-- You have, say, a comments table.
-- But you want to have comments associated with different other tables (for example, comments on blog posts; comments on blog pages; etc.). So you allow the FK in comments to point to different tables. In other words, the other tables are "commentable."
This is a nice summary:
http://guides.rubyonrails.org/association_basics.html#polymorphic-associations