ActiveRecord

From WhyNotWiki
Jump to: navigation, search

Contents

ActiveRecord / Database schemas and migrations

ActiveRecord / Database schemas and migrations edit


This is for information regarding the creation of, limitations of, etc. database schemas, ConnectionAdapters, tables, column types, etc. Most of this is done through the use of migrations, although some of these topics affect ActiveRecord in general, since your database schema can affect all aspects of ActiveRecord.

Unsorted

http://www.perham.net/mike/log/?p=462 Mutterings » Blog Archive » Rails Migrations

http://wiki.rubyonrails.org/rails/pages/Foreign+Key+Schema+Dumper+Plugin Foreign Key Schema Dumper Plugin in Ruby on Rails

Data / one-time / non-sequential migrations

...

Data migrations

The tangled web: data migrations (http://blog.brightredglow.com/2006/9/6/the-tangled-web-data-migrations). Retrieved on 2007-05-11 11:18.


Recently we had an application that needed a bunch of preloaded data. Unfortunately, it wasn’t a one-shot loading of data once the structure was mostly solidified. I needed to try out stuff and wanted the super useful migrations to be my tool. Previously, this had been done with some ad hoc rake tasks, which turned out to be, frankly, a mess. The single biggest challenge working with the data was the fact that the state of the data would be changing independent of the migrations. There was no way to ensure the state between one migration and the next. I decided that the one thing I could assert before and after a migration was that certain records with certain id’s did not exist (pre-) and then did exist (post-migration) (reverse that for migrating down). It turned out this would be good enough for what I needed. ... This capture method adds callbacks for after_create and after_destroy to keep track of the id’s. This is written out to a file in a data subdirectory of the db/migrate directory. These files can be ignored by Subversion so each developer can run the migrations on their own databases without clashing. ... This has been useful for adding and removing data while trying out different table structures. Obviously, it’s a rather targeted solution. I’ll be packaging it as a plugin and posting it to PLANET ARGON’s community site soon.


Problem: Old migrations can stop working when you make changes to the code (models)

(Includes subproblem: A migration may use a feature of a model that you later want to delete)

Should old migrations be updated to work with the new code? [1] says yes.

Migrating in two dimensions (http://scottstuff.net/blog/articles/2005/10/31/migrating-in-two-dimensions). Retrieved on 2007-05-11 11:18.


The big problem is that we’ve been using migrations wrong the whole time, and we just realized it. There are probably a dozen bugs in Typo’s bug tracker that boil down to “I fell behind the trunk and now rake migrate throws exceptions and I can’t upgrade anymore.” The problem is that migrations are designed to run against an earlier version of your database, but they use the current version of your code. The first time that this caused problems was with the migration from Typo 2.0 to 2.5–we’d added two new fields to articles. Migration number 7 added the permalink field and a before_save hook to make sure that all saved articles have permalinks. Then migration number 9 added GUIDs and a second before_save hook to fill the guid field. Both migrations did Articles.find(:all).each { |a| a.save } to update each Article and populate the new fields. This worked great for developers who frequently upgraded. A few days after the GUID migration went in, though, we started getting weird bug reports–users who tried to do both upgrades at the same time found that migration number 7 was dying. What was happening was that migration number 7 added the new permalink field to articles, but when it went to run the save loop both before_save hooks ran, and Typo tried to add a GUID to each article. However, the guid field didn’t exist yet, so the migration threw a bunch of exceptions and died. This caused a bunch of grumbling on the Typo IRC channel. We threw around a bunch of possible fixes. Our favorite was separating migrations into two parts–a schema change part and a data change part. First we’d run all of the schema changes, and then update all of the data. As a work-around, we added a hack that checked the current schema version and disabled specific before_save filters for older versions. We managed to keep this little bandaid working until a couple weeks ago, when a huge set of new migrations went it; they renamed the articles table and merged several other tables into the new contents table using STI. And, again, we found that older migrations broke when users tried to upgrade from Typo 2.5.6 to the current dev tree. Unlink the permalink/guid case, this time there was no simple workaround. We couldn’t just add a couple if statements in a filter and make it all go away. The fundamental problem is that we were using the wrong mental model for migrations. I saw migrations as a one-dimensional thing–a list of steps for migrating old data into the new format. In this view, the migration for going from schema version 6 to schema version 7 is constant–once it’s been written, the only reason to change it is if a bug turns up in the logic for that migration. Otherwise, the migration code should remain unchanged over time. And that’s the problem–migrations aren’t one-dimensional. They are (and need to be) two dimensional–the schema version is one dimension and the code version is the other. Individual migrations exist to migrate from a specific old schema version to the current version, using the current code. Each migration should change over time to adapt to the changes in the code. So, the right fix for the permalink migration that caused so many problems wasn’t to add a bunch of logic to before_save. Instead, we should have deleted the entire save loop from the migration, and trusted the GUID migration to update both fields. If that wasn’t good enough, then we should have added a new migration at the end to do permalink cleanup after the GUIDs were added. Once I came to grips with this, the migration changes needed to allow 2.5.x users to upgrade to the current trunk were pretty simple, and took about 5 minutes to write and test. Or was I the only person in the Rails universe who thought about migrations this way?


Database connection adapters / RDBMSs

MySQL

How ActiveRecord column types map to MySQL database types

ruby mysql
 :string, :limit => 1 varchar(1)
 :string varchar(255)
 :text text
 :text, :limit => 1 tinytext
 :integer int(11)
 :integer, :limit => 1 int(1)
 :integer, :limit => 10 int(10)
 :float float
 :decimal decimal(10,0)
 :decimal, :precision => 30 decimal(30,0)
 :decimal, :precision => 30, :scale => 10 decimal(30,10)
 :decimal, :scale => 10 decimal(10,0)
 :decimal, :precision => 4, :scale => 10 mysql::error: #42000for float(m,d), double(m,d) or decimal(m,d), m must be >= d (column 'column name')
 :datetime datetime
 :timestamp datetime
 :time time
 :date date
 :binary blob
 :binary, :limit => 8 tinyblob
 :boolean tinyint(1)


SQLite

SQLite is great because it spares you the work of having to create a new database (and configure users and permissions) every time you make a new app. It also lets you distribute your app and people can pretty much use it right "out of the box" (assuming they have SQLite installed on their system).

http://wiki.rubyonrails.org/rails/pages/HowtoUseSQLite HowtoUseSQLite in Ruby on Rails

This is the config/database.yml setup that I like to use when I'm just getting started on a project and I don't want to bother setting up a MySQL database:

development:
  adapter: sqlite3
  database: db/development.sqlite

test:
  adapter: sqlite3
  database: db/test.sqlite

production:
  adapter: sqlite3
  database: db/production.sqlite

On the other hand, it could easily be argued that if you're going to end up migrating to MySQL eventually, then you may as well just get it over with at the beginning.

In any case, though, I will usually end up migrating to MySQL/PostgreSQL eventually!

Here are some reasons to switch from SQLite to MySQL:

  • If you need to share the same database between multiple applications.
  • Compatibility/sameness with production: If you plan on deploying anytime soon, then it's best to develop using the same database that you'll be deploying to, in case there are any behavioral differences or feature differences between the databases. (ActiveRecord tries to make everything behave the same on all RDBMS's, but it can't make everything the same...)

So... How do you migrate your data over to a MySQL database, assuming you've accumulated some useful data in your SQLite database that you don't want to lose?

Migrating a SQLite database to MySQL edit

sqlite3 db/development.sqlite '.dump'
sqlite3 db/development.sqlite '.dump' | mysql -u tyler -p... tyler

You'll probably need to tweak the dump first before importing into MySQL, so it would be better to dump to a file first, like this:

sqlite3 db/development.sqlite '.dump' > dump.sql
vim dump.sql  # Make any necessary changes
mysql -u tyler -p... tyler < dump.sql

Problem: If your MySQL database doesn't support transactions

The dump that it generates will be wrapped in a transaction, so if your target database doesn't support transactions, you'll have to remove those lines:

BEGIN TRANSACTION;
COMMIT;

Problem: MySQL doesn't like column names to be in double quotes

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"schema_info" VALUES(0)' at line 1

It would prefer the column names to be wrapped in backticks (`).

So how do we reliably convert the dump file to that? (Without inadvertently changing a " to a ` that shouldn't be changed.)

Well, I wish there were a better way, but I ended up using the venerable [sed (category)] to do this magic. (It's just a regular expression search and replace.)

To fix the inserts:

cat dump.sql | sed 's#^INSERT INTO "\(.*\)"#INSERT INTO `\1`#' > dump2.sql

To fix the "create table"s: Who am I kidding? I can't do that in sed.

http://svn.tylerrick.com/public/ruby/bin/sqlite_dump_to_mysql.rb

cat dump2.sql | ./sqlite_dump_to_mysql.rb > dump3.sql


Problem: BLOB/TEXT column 'name' used in key specification without a key length

That happened for this line:

CREATE INDEX `index_nested_set_menu_items_on_name` ON menu_items (`name`);
CREATE TABLE menu_items (..., `name` text DEFAULT NULL, ...);

Solution was easy enough: Just changed that line to:

CREATE TABLE menu_items (..., `name` varchar(255) DEFAULT NULL, ...);

Finish

mysql -u tyler -p... tyler < dump3.sql

Problem: sqlite doesn't have auto_increment??

So you may have to manually add auto_increment to each of your primary key / id columns:

mysql> alter table table_name modify id int(11) not null auto_increment;

if you don't want to see errors like this:

Mysql::Error: Duplicate entry '0' for key 1: INSERT INTO foo_table (`url`, `user_id`) VALUES('http://example.com', 0)


References

 


What if I already have a database schema?? Can I still use migrations??

That's okay. Yep, you can. Just not to create your initial tables/columns...

http://greenprogrammer.blogspot.com/2006/04/further-adventures-along-our-trail.html

Use rake db:schema:dump one time when you want to start using migrations from a pre-existing SQL DDL generated schema. At this point you will be at version 0. Generate your next model or generate a new migration which will be version 1. If you back out via rake migrate VERSION=0 you will be at the last point before you started using migrations.

Here's how I do it:

  • rake db:schema:dump
  • script/generate all your models (using --skip-migration). (If you forget to use --skip-migrations, just delete any migrations that it generates (if you don't it will throw an error when you migrate, saying the table already exists).)
  • rake migrate. It may look like nothing happened, but this will actually create a new table in your database called schema_info:
# select * from schema_info;
 version
---------
       0
(1 row)
  • You can use script/console and interact with your models now.

You can also take the schema that db:schema:dump generates and throw it into your migration_000.rb.

Can I use a primary key other than 'id'?

Yep. Let's say you want the primary key of your users table to be user_id...

  • In your model, set_primary_key :user_id
  • In your fixture, make sure you specify user_id: 1 rather than id: 1 (which is what Rails will generate for you)

How do I add support for DB-specific data types in ActiveRecord?

http://ghouston.blogspot.com/2006/07/let-activerecord-support-enterprise.html Greg Houston: Let ActiveRecord support Enterprise Databases.


valid column types

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/TableDefinition.html :

 :primary_key, :string, :text, :integer, :float, :decimal, :datetime, :timestamp, :time, :date, :binary, :boolean

:decimal

PragDave (2006-07-14). Decimal Support in Rails (http://pragdave.pragprog.com/pragdave/2006/07/decimal_support.html). Retrieved on 2007-05-11 11:18.


A couple of blog posts ago, I commented on the dangers of converting database decimal columns into Ruby floats. And, five months early, Santa delivers. In the Rails trunk, numeric and decimal database columns with a scale factor are now converted into Ruby BigDecimal objects. If the scale factor is zero, they instead become integers. Migrations now support decimal columns too, with the addition of two new attributes, precision and scale.

   add_column :orders, :price,
              :decimal, :precision => 8, :scale => 2

valid methods

http://api.rubyonrails.org/classes/ActiveRecord/Migration.html

  • create_table(name, options)
  • drop_table(name)
  • rename_table(old_name, new_name)
  • add_column(table_name, column_name, type, options) A default value can be specified by passing an options hash like { :default => 11 }.
  • rename_column(table_name, column_name, new_column_name)
  • change_column(table_name, column_name, type, options)
  • remove_column(table_name, column_name)
  • add_index(table_name, column_names, index_type, index_name)
  • remove_index(table_name, index_name)

how to wrap it in a transaction/commit

  def self.up
    execute "begin;"
      add_column 'table', 'column', :string
      ...
    execute "commit"
  end

Better, if it works...

  def self.up
    ActiveRecord::Migration.transaction {
      create_table :crawls do |t|
        t.column :created_at, :datetime, :null => false
      end
      add_column :snapshots, :crawl_id, :integer
      foreign_key :snapshots, :crawl_id, :crawls
    }
  end

Migrations/Postgresql: how to see the result of a query

Before I switched to using Rails migrations, I did migrations using plain Postgresql SQL commands. This had the advantage that it would tell you how many rows you updated when you did an update/delete.

How to get this information with Rails migrations?

The execute command returns a PGresult object (http://ruby.scripting.ca/postgres/rdoc/classes/PGresult.html), which you can then query to get Postgresql's output.

  def self.up
    puts execute("update orders set ...").cmdstatus
  end

UPDATE 244

Don't forget to add indexes!

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#M000612

add_index(:suppliers, :name)
add_index(:accounts, [:branch_id, :party_id], :unique => true)


ActiveRecord  edit   (Category  edit)


How do I do migrations outside of Rails?

http://blogs.pragprog.com/cgi-bin/pragdave.cgi/Tech/Ruby/MigrationsOutsideRails.rdoc

    ActiveRecord::Schema.define do
      create_table children, :force => true do |t|
        t.column :parent_id, :integer
        t.column :name,      :string
        t.column :position,  :integer
      end
    end

add_column with :null => false didn't work

add_column :my_table, :first_opened_time, :datetime, { :default => 'now', :null => false }

== AddFirstOpenedTime: migrating ========================================
-- execute("begin")
   -> 0.0003s
-- add_column(:my_table, :first_opened_time, :datetime, {:default=>"now", :null=>false})
rake aborted!
PGError: ERROR:  column "first_opened_time" contains null values
: ALTER TABLE my_table ALTER first_opened_time SET NOT NULL

Complaint: You have to use :allow_nil => true when you do validates_length_of :maximum

Otherwise you'll get "value is too long" validation errors when you try to set it to nil.

I can see why it might be intuitive for :minimum validations, but not for :maximum validations!

Can I use a composite (multi-column) primary key?

Sure. Just use http://rubyforge.org/projects/compositekeys .

http://www.pjhyett.com/posts/208-composite-primary-keys-are-good Composite Primary Keys are Good

class AddViewsTable < ActiveRecord::Migration
  def self.up
    create_table :views, :primary_key => [:topic_id, :user_id] do |t|
      t.column :topic_id, :integer
      t.column :user_id,  :integer
      t.column :post_id,  :integer
    end
  end
end

Caveat: ActiveRecord doesn't respect PostgreSQL's default values

# I had a table that had a not-null constraint on its date_time field. So when I did a plain a = MyModel.new; a.save it said 
ActiveRecord::StatementInvalid: PGError: ERROR:  null value in column "date_time" violates not-null constraint

# To solve this, I created a before_save method in my model that would initialize the column to Time.now() if it was nil. (The column was set up in PostgreSQL to have a default value of Now(), but ActiveRecord doesn't look at / respect that default. It tries to set it to nil, which translates to Null in SQL, which overrides PostgreSQL's default value.)

This works:

  def before_save
    self.date_time = Time.now() if date_time.nil?
  end

So does this:

  def before_save
    write_attribute(:date_time, Time.now()) if date_time.nil?
  end


Question: How do I ask an ActiveRecord ConnectionAdapter what its connection information (host, database name, etc.) is?

Why would you want to do that?

Well, suppose you want to put a sanity check in some important script, just to make absolutely sure that you are running it on the correct host or the correct database or something...

How to do it:

/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/postgresql_adapter.rb

      3 module ActiveRecord
      4   class Base
      5     # Establishes a connection to the database that's used by all Active Record objects
      6     def self.postgresql_connection(config) # :nodoc:
...
      9       config = config.symbolize_keys
     10       host     = config[:host]
     11       port     = config[:port] || 5432
     12       username = config[:username].to_s
     13       password = config[:password].to_s
...
     16 
     17       if config.has_key?(:database)
     18         database = config[:database]
     19       else
     20         raise ArgumentError, "No database specified. Missing argument: database."
     21       end
     22 
     23       pga = ConnectionAdapters::PostgreSQLAdapter.new(
     24         PGconn.connect(host, port, "", "", database, username, password), logger, config
     25       )
...
     32     end
     33   end

     50     class PostgreSQLAdapter < AbstractAdapter
     51       def adapter_name
     52         'PostgreSQL'
     53       end
     54 
     55       def initialize(connection, logger, config = {})
     56         super(connection, logger)
     57         @config = config
     58         @async = config[:allow_concurrency]
     59         configure_connection
     60       end

So it's stored in @config. I don't see any accessors for getting that out, so I guess we'll just have to cheat and use instance_variable_get(:@config):

p SomeModel.connection.instance_variable_get(:@config)
# {:username=>"...", :allow_concurrency=>false, :database=>"...", :password=>"...", :adapter=>"postgresql", :host=>"..."}
 


ActiveRecord

This is for information regarding using/creating ActiveRecord models -- what methods are available, how to do searching, etc.

Security

Decide which attributes should be "secure"; Deny mass-assignment to secure attributes

Securing your Rails application (http://manuals.rubyonrails.com/read/chapter/47). Retrieved on 2007-05-11 11:18.


The easiest way to create a user object from the form data in the controller is:

User.create(@params['user'])

But what happens if someone decides to save the registration form to his disk and play around with adding a few fields?

<form method="post" action="http://website.domain/user/register">
  <input type="text" name="user[name]" />
  <input type="text" name="user[password]" />
  <input type="text" name="user[role]" value="Admin" />
  <input type="text" name="user[approved]" value="1" />
</form>

He can create an account, make himself admin and approve his own account with one click.

...

Using attr_protected, we can secure the User models like this:

class User < ActiveRecord::Base
  attr_protected :approved, :role
end

This will ensure that on doing User.create(@params['user']) both @params['user']['approved'] and @params['user']['role'] will be ignored. You’ll have to manually set them like this:

user = User.new(@params['user'])
user.approved = sanitize_properly(@params['user']['approved'])
user. role    = sanitize_properly(@params['user']['role'])

...

You can also specify the protection in reverse. You simply allow access instead of deny it, so only the attributes named in attr_accessible are available for mass-assignment.

Using attr_accessible, we can secure the User models like this:

class User < ActiveRecord::Base
  attr_accessible :name, :password
end

[...] When you add new attrbutes to the User model, they’ll be protected by default.



Model.table_exists?

Is sometimes solution to [Symptoms (category)]: ActiveRecord::StatementInvalid error

At least when using SQLite3, I once received this unhelpful error message:

500 Internal Server Error

ActiveRecord::StatementInvalid
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/sqlite_adapter.rb:360:in `table_structure'
/usr/lib/ruby/gems/1.8/gems/activesupport-1.4.2/lib/active_support/core_ext/object/misc.rb:23:in `returning'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/sqlite_adapter.rb:359:in `table_structure'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/sqlite_adapter.rb:210:in `columns'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/base.rb:763:in `columns'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/base.rb:2060:in `attributes_from_column_definition_without_lock'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/locking/optimistic.rb:45:in `attributes_from_column_definition'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/base.rb:1502:in `initialize_without_callbacks'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/callbacks.rb:225:in `initialize'
./script/../config/../vendor/plugins/app_profiler/lib/app_profiler.rb:12:in `new'
./script/../config/../vendor/plugins/app_profiler/lib/app_profiler.rb:12:in `new_request'
/usr/lib/ruby/gems/1.8/gems/qualitysmith_extensions-0.0.18/lib/qualitysmith_extensions/module/guard_method.rb:44:in `disable_benchmarking'
./script/../config/../vendor/plugins/app_profiler/lib/app_profiler.rb:11:in `new_request'
./script/../config/../vendor/plugins/app_profiler/lib/app_profiler.rb:91:in `process'

Traced to:

    class SQLite3Adapter < SQLiteAdapter # :nodoc:
      def table_structure(table_name)
        returning structure = @connection.table_info(table_name) do
          raise ActiveRecord::StatementInvalid if structure.empty?
        end
      end
    end

Would have been more helpful if they had given me a more specific error, like:

"Table app_profiler_requests does not exist".

Workaround: Do the check yourself in your code/plugin:

raise "AppProfilerRequest table doesn't exist. Please run rake db:migrate:plugins PLUGIN=app_profiler" unless AppProfilerRequest.table_exists?

[Caveats (category)] (mostly ActiveRecord) [actually Ruby-general]: Always include 'self.' when calling my_attribute= from within a model!

Otherwise, it will think you are setting a local variable named my_attribute.

Example:

# Don't do this:
  def before_save
    date_time = Time.now() if date_time.nil?
  end

# Do this:
  def before_save
    self.date_time = Time.now() if date_time.nil?
  end

ActiveRecord: What's the difference between write_attribute and update_attribute?

write_attribute update_attribute
protected public
doesn't save after writing the attribute saves after writing the attribute
skips the setter method and directly modifies the internal attributes hash?? invokes the setter method (my_attribute=)

http://api.rubyonrails.org/classes/ActiveRecord/Base.html:

You can alternatively use self[:attribute]=(value) and self[:attribute] instead of write_attribute(:attribute, vaule) and read_attribute(:attribute) as a shorter form.

ActiveRecord: When to use self.my_attr, my_attr on its own, @my_attr, or read_attribute(:my_attr)??

I think the answer is, for all DB-backed attributes that are created automatically for you by the ActiveRecord framework:

  • use the plain old my_attr whenever possible (this is an accessor method that is automatically created)
  • use self.my_attr if just saying my_attr would conflict with a local variable defined in the current method. This ensures that the custom accessor is invoked if one is defined.
  • use read_attribute(:my_attr) if my_attr would conflict with a method name (that is, a custom setter).

Be aware that read_attribute(:my_attr) only uses the default accessor, which is why you should only use it if this is the behavior you want.

How do I safely include user input in my queries?

sanitize_sql

sanitize_sql(["name='%s', "foo'bar"]  =>  "name='foo''bar'"

How do I do straight-SQL select queries?

res = Order.connection.select_all("select id, quantity*unit_price as total from line_items")
p res

This produces something like

[{"total"=>"29.95", "id"=>"91"},
{"total"=>"59.90", "id"=>"92"},
{"total"=>"44.95", "id"=>"93"}]

You don't even have to use a class that makes sense; you're perfectly free to call Books.connection.select_all("select * from cars") or something.

When would you want to use raw-SQL queries like this? Well, you should only rarely need to, if ever...but at least you can! Perhaps you need to get the result of some database-specific function or select from a (database-specific) view...

with_scope

with_scope doesn’t work with find_by_sql. (http://woss.name/2006/05/07/notes-from-a-rails-course/)

with_scope can be combined with a bit of metaprogramming to automatically limit the scope of actions very easily. See "Nested with_scope" [2]. (http://woss.name/2006/05/07/notes-from-a-rails-course/)

Caveat: When I tried it, it seemed that with_scope didn't limit the scope of associations. So this would example would not do what it's author was trying to do:

def User
  def do_something_with_cool_posts_only
    Post.with_scope({:find => {:conditions => ['cool = true']} }) do
      my_cool_posts = self.posts   # nope, this actually gets *all* of my posts, not just the cool ones
      do_something_with my_cool_posts
    end
  end
end

Update (7/11/2007): This won't work. You must use Post.find(:all, :conditions=>["user_id =?", self.id]) instead of self.posts.

This is because you are limiting the scope of the Post model, and instead querying the User model (for self.posts) This won't work.

-Chinmay

Eager loading

"Appointment.find(:first, :include => [:contacts => [:addresses => :cities]]) is going to eagerly load the first appointment along with its contacts, their addresses and the addresses’ cities!" (http://woss.name/2006/05/07/notes-from-a-rails-course/)


http://weblog.rubyonrails.org/2006/03/28/rails-1-1-rjs-active-record-respond_to-integration-tests-and-500-other-things/ : has stuff on eager loading

ActiveRecord booleans

From Agile Web Development with Rails (2nd ed).20060613.pdf page 291:

# DON'T DO THIS
user = Users.find_by_name("Dave")
if user.superuser
  grant_privileges
end

# INSTEAD, DO THIS
user = Users.find_by_name("Dave")
if user.superuser?
  grant_privileges
end

foo.blank? tests to see if it’s empty [empty as in ""? or what?] or nil in Rails. 0 is not blank, though! (http://woss.name/2006/05/07/notes-from-a-rails-course/)


ActiveRecord: relationships: belongs_to or has_* ?

belongs_to :other_table if the table has a foreign key to other_table

ActiveRecord: might be useful: ActiveRecord::Base.blank?

Returns true if its receiver is nil or an empty string.

ActiveRecord: callbacks

http://rubyonrails.org/api/classes/ActiveRecord/Callbacks.html

/usr/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/callbacks.rb

    # Is called when the object was instantiated by one of the finders, like Base.find.
    #def after_find() end

    # Is called after the object has been instantiated by a call to Base.new.
    #def after_initialize() end

after_initialize() seems to be called even when I call reload(). Is that supposed to happen? Possibly. I guess the "the object [is] instantiated by a call to Base.new" part happens even when the programmer calls model.find or model.reload rather than ModelClass.new ! Just something to be aware of.

The main reason I wanted it was so that I could have code that was only called when a record is first created, not when an existing record is re-loaded into the model. One can detect that condition like so:

  def after_initialize
    if self.new_record?
      # Do stuff that should only happen when an object is first instantiated, *before* it is saved...
    else
      # The object was instantiated from an existing record.
    end
    # The object was instantiated either from a new record or an existing record.
  end

To put it another way:

  • The after_find callback is triggered only when a find occurs
  • The after_initialize callback is triggered both when a find occurs and when a new/initialize is called

Different ways to update attributes

  • Model.new(initial_attribute_values): Updates/initializes but doesn't save. Calls validations??
  • Model.create(initial_attribute_values): Updates/initializes and saves. Calls validations.
  • model.attributes = hash_attributes: Updates in memory but doesn't saves. [Skips validations.??]
  • model.update_attribute(name, value): Updates and saves. Skips validations.
  • model.update_attributes(attributes_to_change): Updates and saves. Calls validations. If the object is invalid, the saving will fail and false will be returned.
  • model.write_attribute(name, value): private. Call it from your custom setter methods to prevent infinite loops.
  • model[:name] = value. Updates in memory, setting the attribute directly; doesn't save until you call model.save. (self[:attribute]=(value) and self[:attribute] are just a shorter form of write_attribute(:attribute, vaule) and read_attribute(:attribute).)
  • model.name = value. Updates in memory, using the setter method; doesn't save until you call model.save.

Caveat! update_attribute (singular) apparently saves the record even if it was previously unsaved, skipping validations

I spent a couple hours trying to debug some strange behavior where one of my columns was getting nil values even though I had this line:

validates_presence_of :survey_response

I couldn't figure out how it was possible for those to get in there since the validation was supposed to make that impossible. I even added a validate function:

  def validate
    if survey_response.nil?
      errors.add :survey_response, 'is missing'
    end
    #$out += "in validate: {{{ #{survey_response.inspect} }}}<br>"
  end

For some reason, though, it didn't seem to get called in the cases where it was nil. (It got called just fine when it wasn't nil.)

The reason for all this is the behavior of update_attribute... which, according to the docs:

Updates a single attribute and saves the record. This is especially useful for boolean flags on existing records. Note: This method is overwritten by the Validation module that’ll make sure that updates made with this method doesn’t get subjected to validation checks. Hence, attributes can be updated even if the full object isn’t valid.

I even read that before adding the update_attribute call in my code. "Fine," I thought, "it will skip validations when updating an existing record." But it didn't even occur to me that it actually cause the record to be saved if it was still an unsaved record, without paying any regard to my validations.

My code looked something like this, not that it matters much:

      for question_id, value in params[:survey_question_responses]
        question = SurveyQuestion.find(question_id)
        question_response = @survey_response.question_responses.select {|r|
          r.question == question
        }
        if question_response.size > 0
          question_response = question_response.first
        else
          question_response = SurveyQuestionResponse.new(:question => question)
        end
        question_response.update_attributes({:selected_option_ids, value[:selected_option_ids]})
      end
      @survey_response.question_responses << question_response

Solution: Use update_attributes (plural) instead, which doesn't have this strange behavior. Any call to be update_attribute can easily be converted into a call to update_attributes.

If you clone a model and save it, will it create a new one or keep the same id and save over the existing one?

Cloning a model is mostly (as far as I can see) a technique useful for testing...

Would something like this work, for example?


    # Set up the model once
    shop1 = Shop.new
    shop1.is_active = true
    shop1.save
    
    shop2 = shop1.clone
    shop2.save
    
    shop3 = shop1.clone
    shop3.save

    assert_equal 3, Shop.find_active_shops

Why or why not?

Miscellaneous

To find associations from the console, do Model.reflections.keys. (http://woss.name/2006/05/07/notes-from-a-rails-course/)

Caveat: "For has_many :books, :conditions => 'published = 1', we’ve discovered that if you do model_instance.books.create ..., it will not automatically set published = 1 on the new instance. So while it seems like you’re adding a books to the has_many collection of published books, you’re not. It should be possible to emulate that in some way with with_scope." (http://woss.name/2006/05/07/notes-from-a-rails-course/)





ActiveRecord / Single table inheritance

ActiveRecord / Single table inheritance edit



Introduction

It is sometimes desirable to be able to subclass your ActiveRecord objects. For example, if you want to have an Employee and a Customer, both of which share some common fields. You can do this without any problem:

class Person < ActiveRecord::Base
class Employee < Person
class Customer < Person

But once you save your object to the database, it forgets what type it is.

Solution: Single Table Inheritance. To use it, simply add a TEXT type column to your table (people in our case). Rails handles the rest.

If there are any columns that are only used by one subclass and not another, then you can just leave the values of those columns blank for the records/models that don't use them... (Somewhat wasteful and non-normalized, perhaps, but it works.)

Employee.create(:name => "Bob the underpaid", :some_column => "12345")
Customer.create(:name => "Fred the impulse spender", :another_column => "4567")

Person.find(:all).each { |person|
  puts person.class
}

Outputs:

Employee
Customer

Can you have records of the base class type?

Yes!

irb -> m = MenuDuJour::MenuItem.create

irb -> m.reload
    => #<MenuDuJour::MenuItem:0xb7513120 @attributes={"name"=>nil, "type"=>nil, "id"=>"5", "display_name"=>nil,} >

Can I change the type of a record after it's been created?

Yes!

irb -> m.type
(irb):36: warning: Object#type is deprecated; use Object#class
    => MenuDuJour::MenuItem

irb -> m.class
    => MenuDuJour::MenuItem

irb -> m.type = 'Label'
    => "Label"

irb -> m.save!
    => true

irb -> m.reload
    => #<MenuDuJour::MenuItem:0xb74fc164 @attributes={"name"=>nil, "type"=>"Label", "id"=>"5", "display_name"=>nil,} >

irb -> m.attributes['type']
    => "Label"


irb -> m.type = 'MenuItem' # Base class
    => "MenuItem"
irb -> m.save!; m.reload
    => #<MenuDuJour::MenuItem:0xb74fc164 @attributes={"name"=>nil, "type"=>"MenuItem", "id"=>"5", "display_name"=>nil,} >
irb -> m.attributes['type']
    => "MenuItem"

irb -> m.type = nil # Base class
    => nil
irb -> m.save!; m.reload
    => #<MenuDuJour::MenuItem:0xb74fc164 @attributes={"name"=>nil, "type"=>nil, "id"=>"5", "display_name"=>nil,} >
irb -> m.attributes['type']
    => nil


Reusing a shared column in two different subclasses but using a different column name

Gregory Seidman. http://lists.rubyonrails.org/pipermail/rails/2006-March/027914.html. Retrieved on 2007-05-11 11:18.


class Super < ActiveRecord::Base
  
  def self.alias_field(old_field, new_field)
    define_method(new_field) { self.send(old_field) }
    define_method("#{new_field}=") { |arg| self.send("#{old_field}=", arg) }
    define_method("#{new_field}?") { self.send("#{old_field}?") }
  end

end

class Sub < Super
  alias_field :text1, :address1
  alias_field :text2, :address2
  alias_field :text3, :zipcode
end

Now I can do all of the following:

fail "Where's the address?" unless foo.address1?
foo.address1 = '1600 Pennsylvania Ave.'
addr = foo.address1

...and it will use text1?, text1=, and text1, respectively.

The "type" column

ryanb on http://railsforum.com/viewtopic.php?pid=22131#p22131


"type" is protected from mass assignment so you have to set it directly.

ryanb on http://railsforum.com/viewtopic.php?pid=22135#p22135

[Modified quotation (category)] I think you have to set it like this:

user[:type] = params[:type] 

rather than like this:

user.type = params[:type] 

because "type" is also a reserved method name in Ruby so you can't really set it directly.

 


Associations / joins

Big caveat!!: has_and_belongs_to_many relationship caveat: make sure your join table has no id column!

Otherwise you will get DB errors saying you're duplicating a unique key (namely, 'id').

To cause it to omit the id column when you create the join table:

create_table :users_groups, :id => false do |t|
...
end

(What do I mean by "join" table? This is the table used in a has_and_belongs_to_many association. :users has_and_belongs_to_many :groups would expect (by default) a join table to exist named "groups_users".)

Is there any way to have two applications share the same database?

The immediate problem I see is that each application wants to have its own schema_info table.

I guess maybe if you only did your migrations in one application... (maybe call it "shared_migrations" and have it only contain the migrations...)

[Caveat (category)][Setting has_many association (category)]: Can't use the association [proxy] to alter the objects themselves

That's hard to explain in one sentence. Let me try again.

Group has_many :users

new_users = [user1, user2]
group.users = new_users
This works as expected if you're adding *new* things to the association. So the records for user1 and user2 will both be saved (when you save group?).

But it doesn't work if those records (the records with those IDs) are *already associated* and you're actually trying to change columns of those records themselves.

group = Group.find(...) # assume that group.users already includes user1.id
user1 = User.find(...)
user1.name = 'something different'
new_users = [user1]
group.users = new_users
group.save
# This won't change *anything*!!! Even though you've modified user1, *that change will be lost*. This is because the association [proxy] can *only* be used to change the association (associated records) itself.

In other words, group.users is equivalent to group.user_ids and can only be used to change *which* objects are associated...not any columns *of* those associated records!

Lance and I spent a couple hours tracking down why this behaves the way it does... And we found that it is because of how ActiveRecord::Associations::AssociationCollection#replace works...

      # Replace this collection with +other_array+
      # This will perform a diff and delete/add only records that have changed.
      def replace(other_array)
        other_array.each { |val| raise_on_type_mismatch(val) }

        load_target
        other   = other_array.size < 100 ? other_array : other_array.to_set
        current = @target.size < 100 ? @target : @target.to_set

        @owner.transaction do
          delete(@target.select { |v| !other.include?(v) })
          concat(other_array.select { |v| !current.include?(v) })
        end
      end


It's using the wrong foreign key in the join clause for your join table? Try :association_foreign_key !

          #  profiles LEFT OUTER JOIN profiles_hobbies ON profiles_hobbies.profile_id = profiles.id
          #           LEFT OUTER JOIN option_lists ON option_lists.id = profiles_hobbies.option_list_id
          #Mysql::Error: Unknown column 'profiles_hobbies.option_list_id' in 'on clause'
          #  Should be doing:
          #  Profile.find(:all, :limit => 10, :joins =>
          #         'join profiles_hobbies on profiles.id = profiles_hobbies.profile_id join option_lists on profiles_hobbies.hobby_id = option_lists.id')
          # Solved with:
          # has_and_belongs_to_many :hobbies, :join_table => :profiles_hobbies, :association_foreign_key => 'hobby_id'


ActiveRecord: Multiple joins on same table breaks find :include (ambiguous column)

          #   Profile.find(:all, :include=>[:hobbies, :majors])
          #LEFT OUTER JOIN profiles_hobbies ON profiles_hobbies.profile_id = profiles.id
          #LEFT OUTER JOIN option_lists ON option_lists.id = profiles_hobbies.hobby_id AND option_lists.`type` = 'Hobby' AND approved = 1
          #LEFT OUTER JOIN profiles_majors ON profiles_majors.profile_id = profiles.id
          #LEFT OUTER JOIN option_lists majors_profiles ON majors_profiles.id = profiles_majors.major_id AND majors_profiles.`type` = 'Major' AND approved =
          # Mysql::Error: Column 'approved' in on clause is ambiguous:

http://dev.rubyonrails.org/ticket/3574

Supposedly fixed it, but didn't seem to:

http://dev.rubyonrails.org/changeset/3776

Develop test case?

How do I create a user interface for editing a HABTM association with checkboxes?

http://wiki.rubyonrails.org/rails/pages/CheckboxHABTM

[How stuff works / Dissections] How do association accessor methods work, how do attribute accessor methods work, and what’s the difference (in ActiveRecord)?

Accessor Missing (http://errtheblog.com/post/20) (2006-08-22). Retrieved on 2007-02-15 22:35.

The question arose from a failed attempt to override an association accessor method. Something like this did not work:

class Story < ActiveRecord::Base
  belongs_to :author

  def author
    auth = super
    auth.name
  end
end

Obviously I want stories.author to return the name of a particular story’s author rather than the associated Author object. Remember that the super method calls the current method on the class parent. I’m trying to call the author method, then, on ActiveRecord::Base (hoping it will return the associated author object like normal). Rails, unfortunately, is having none of it:

>> story = Story.find(:first)
>> story.author
NoMethodError: super: no superclass method `author'

The truth’s that authors is not really a method_missing trick. It’s a real method defined on my Story class. Where does it get defined? When belongs_to is called. Like much of Rails’ sugar, belongs_to is a class method of ActiveRecord::Base. When you call it in a subclass, stuff happens.

The adventurous can peek into active_record/associations.rb around line 646 to see how belongs_to is defined. Here’s the cliffs: when belongs_to is called, ActiveRecord defines reader and writer methods on the calling class. Now it’s starting to make sense: author is added to my Story class when belongs_to is called. It’s not a method_missing trick and doesn’t exist on ActiveRecord::Base.

Accessor Missing (http://errtheblog.com/post/20) (2006-08-22). Retrieved on 2007-02-15 22:35.

That feels great. But I still have a question: what about, say, story.title? Does ActiveRecord define all of my attribute accessor methods in the same way it defines my association accessor methods? All told, attribute accessor methods are indeed method_missing magic. Get pumped: super will work when overriding them. class Story < ActiveRecord::Base belongs_to :author def title "This story's title is: " + super end end So close, so close…

>> story = Story.find(:first)
=> #<Story:0x279f9e0 ...>

>> story.title
=> "This story's title is: Accessor Missing"

["Accessor Missing" is the actual title of his article; this is not an error message!]








Libraries

Composite Primary Keys

Homepage: http://compositekeys.rubyforge.org/
Documentation: Forum


Project/Development: http://rubyforge.org/projects/compositekeys



License: MIT


Authors: Nic Williams


Composite Primary Keys (http://compositekeys.rubyforge.org/). Retrieved on 2007-05-11 11:18.


A model with composite primary keys would look like…

class Membership < ActiveRecord::Base
  # set_primary_keys *keys - turns on composite key functionality
  set_primary_keys :user_id, :group_id
  belongs_to :user
  belongs_to :group
  has_many :statuses, :class => 'MembershipStatus', :foreign_key => [:user_id, :group_id]
end

A model associated with a composite key model would be defined like…

class MembershipStatus < ActiveRecord::Base
  belongs_to :membership, :foreign_key => [:user_id, :group_id]
end

...

Membership.find(1,1)  # composite ids returns single instance
=> <Membership:0x39218b0 @attributes={"user_id"=>"1", "group_id"=>"1"}>



DrySQL

Name: DrySQL
Dynamic, Reflective, Invisible ORM for Ruby


Homepage: http://drysql.rubyforge.org/
Documentation: By example


Project/Development: http://rubyforge.org/projects/drysql
As listed in other directories: http://rubyfurnace.com/plugins/drysql
Depends on: ActiveRecord



Authors: Bryan Evans


http://www.infoq.com/articles/DrySQL - An article

http://rubyforge.org/projects/drysql

Define your DB constraints, validations, and associations in only one place: the DB.

http://drysql.rubyforge.org/getStarted.htm

DrySQL is a Ruby plug-in that extends ActiveRecord and applies the DRY principle to Object-Relational Mapping.

http://drysql.rubyforge.org/by_example.htm

DrySQL can generate the following validations based on the DB's information schema:

  • validates_numericality_of
  • validates_length_of
  • validates_inclusion_of (boolean columns only)
  • validates nullability_of
DrySQL can generate the following associations based on the DB's information schema:
  • belongs_to
  • has_many
  • has_one
  • has_many :through

Ways in which ActiveRecord sucks

Let me count the ways...

http://mysqldump.azundris.com/archives/72-Rubyisms.html Rubyisms - MySQL-dump

So, question: Most of these problems are fixable, right? Who's working on fixing them? When will they be fixed?

Some of these problems probably won't be fixed by the core team if they have different opinions about the way things should be. But that doesn't stop others from fixing them...




ActiveRecord  edit   (Category  edit)


Object-Relational Mapping  edit   (Category  edit) Category:Object-Relational Mapping

Category Object-Relational Mapping not found
Facts about ActiveRecordRDF feed
Depends on ActiveRecord  +
Licensed under MIT  +
Ads
Personal tools