Temporary Table Nicety 31 Jan 2007
A taste of temporary table support for ActiveRecord and MySQL.

Creating Temporary Tables

Temporary tables can be created by using two forms. Temporary tables will generate the following SQL:

CREATE TEMPORARY TABLE table_name LIKE base_table

The First Form

A block form which manages your temporary table (ie: it creates the table at the start of the block and ensures it is removed by the end of the block, similar to how IO objects work in ruby) .

The Second Form

The second form allows you to assign your temporary table to a variable. You are responsible for destroying it. Or you could just leave it around until your ruby process is done and your database will kill it when the connection closes.

Here’s an example using the block form:

# create our model
class Account < ActiveRecord::Base

# perform the update
Account.create_temporary_table do |t|
   t.new.is_a? ActiveRecord::Base # => true
   t.new.is_a? ActiveRecord::TemporaryTable # => true

    # You can treat 't' like any model class, and create new records, 
    # add validations, etc.
    record = t.new :name=>"New Account" 
    record.create :name => "Another new account"

    # TempAccount is created to reference this model class as well
     TempAccount == t # => true

    # the "end" block here will drop our temporary table and remove
    # our TempAccount constant

Checking It Out

You can pull it down from trunk right now. It will be included in the next release (which will be 0.4.0):

  • svn checkout svn://rubyforge.org/var/svn/arext/trunk/ar-extensions

Like Tables

Like tables are similar to temporary tables. In fact they are created in the exact same way except that they are permanent. They are normal tables until you destroy them. A like table simply generates the following sql:

CREATE TABLE table_name LIKE base_table

You can create this the same way way you create temporary tables, just pass in the :permanent option as a parameter:

class Account < ActiveRecord::Base

# t is your model class, also aliased as TempAccount.
temp_account_model_class = Account.create_temporary_table :permanent=>true

TempAccount == temp_account_model_class # => true  

# drop your table

blog comments powered by Disqus