If you're reading this after April 2012 there's an increased likelihood that Heroku has made the new shared database available as default and this article probably will no longer apply.

In order to migrate your existing shared database to the new public beta version with user defined function support you'll want to backup your existing database - assuming that data is important to you.

We'll use pgbackups for backing up and restoring as this leaves the data on Heroku/Amazon. Note: You'll probably want to disable your application if its in a running state before backing up as any data committed after the backup but before the restore by your users would be effectively lost.

# shell
$ heroku addons:add pgbackups:basic
$ heroku pgbackups:capture

If this is your first backup you'll likely end up with a backup that can be identified as "b001" - backup identifiers beginning with a "b" signify that they are a manual backup.

Next we'll add the new heroku-shared-postgresql addon which automatically creates a database for us:

# shell
$ heroku addons:add heroku-shared-postgresql

In the output you'll see the identifier of the new shared database all in caps. Mine was HEROKU_SHARED_POSTGRESQL_COBALT; yours may be as well (or perhaps some other _METAL name).

Next restore the new database from your backup:

# shell
heroku pgbackups:restore HEROKU_SHARED_POSTGRESQL_COBALT b001

You'll be asked to confirm. Finally switch your application from the old database to the new database:

# shell
$ heroku pg:promote HEROKU_SHARED_POSTGRESQL_COBALT

Now you'll be able to run those migrations that create user defined functions and even connect to your database instance using PSQL!

# shell
$ heroku pg:psql HEROKU_SHARED_POSTGRESQL_COBALT

# shell
jruby -J-Xmx1024m -S trinidad

So how do you do this in a production environment? You could of course pass along the same command line options, or for better configuration management you can use the Trinidad Daemon extension.

# ./Gemfile
  ⋮
  gem 'trinidad'
  gem 'trinidad_daemon_extension'
  gem 'trinidad_logging_extension' # optional, hah! who're we kidding?
  ⋮

And then some configuration options:

# ./config/trinidad.yml
jruby_max_runtimes: 1 # also enables threadsafe by convention
libs_dir: lib # by default this is lib/jars - but if you're coming from Warbler you might be accustomed to /lib

extensions:
  logging: # logging always a good idea
    config: lib/resources/log4j.properties
    logging_system: log4j
  daemon:
    jvm_args: '-Xmx1024m'

Now just jruby -S trinidad for your production environment or your dev workstations and the configuration environment will be the same. And if you've setup everything correctly you can still be rails s which also means your Spork/Guard/RSpec world will continue to function as well.

I needed to essentially have a parameterized view (that's what we called it in SQL Server) and I was delighted when I found that Postgres supports it right out of the box - not to mention its superior temporal functions. The next challenge was Rails of course. I'm stepping outside of the typical ActiveRecord model pattern here and while I'm sure there are better ways to execute on this here's my first practical working version of integrating a parameterized view (ahem, table returning function) from Postgres into Rails 3.

(And for the curious: yes you can SELECT * FROM your_function(91) WHERE some_column > 42 just like a parameterized view.)

So first I have my migration in which I create my function:

$ rails g migration CreateStockHourlyPerformanceFunction

Then the actual function itself:

# ./db/migrations/TIMESTAMP_create_stock_hourly_performance_function.rb

class CreateStockHourlyPerformanceFunction < ActiveRecord::Migration
  def up
    execute <<-SPROC
CREATE OR REPLACE FUNCTION stock_hourly_performance(integer) RETURNS TABLE(time_block timestamp, average integer) AS $$
SELECT
  block AS time_block
  ,MAX(block_avg)::integer AS average
FROM
  (SELECT
    date_trunc('hour', created_at) AS created_at_trunc
    ,AVG(value) AS block_avg
  FROM stock_values
  WHERE stock_id = $1
  GROUP BY date_trunc('hour', created_at)) AS x,
  (SELECT
    ('today'::date+'1 hour'::interval*q)::timestamp AS block
  FROM generate_series(0, extract('hour' from localtimestamp)::integer) AS q) AS y
WHERE created_at_trunc > block - '1 hour'::interval AND created_at_trunc <= block
GROUP BY 1 ORDER BY 1
$$ LANGUAGE SQL;
SPROC
  end

  def down
    execute "DROP FUNCTION IF EXISTS stock_hourly_performance(integer)"
  end
end

Then don't forget to migrate!

$ rake db:migrate
$ RAILS_ENV=test rake db:migrate

And then the other interesting part - how do we integrate with the Rails environment? I chose not to use ActiveRecord because it doesn't make a lot of sense here. I have a feeling I'd get better results if I went with DataMapper but I'm not ready to configure another data access layer just yet. Also it turns out that hourly performance won't be the only time we need the Performance class - this class will gain some more static constructor methods over time as the project evolves.

 # ./models/performance.rb
class Performance
  attr_accessor :time_block, :average
  def initialize(time_block, avg)
    @time_block = time_block.is_a?(DateTime) ? time_block : DateTime.parse(time_block)
    @average = avg.to_i
  end
  class << self
    def hourly_for_stock(stock_id)
      result = ActiveRecord::Base.connection.execute("SELECT * FROM stock_hourly_performance(#{stock_id})")
      if result.count == 0
        stock = Stock.select(:par_value).find(stock_id)
        return [Performance.new(DateTime.now.at_beginning_of_day, stock.par_value)]
      end
      result.map { |r| Performance.new(r["time_block"], r["average"]) }
    end
  end
end

The table is pretty simplistic in that it has a stock_id foreign key, a value field (representing the current value at that point in time), and of course the created_at timestamp field.

Here's my first naive attempt using Postgres:

SELECT
	block
	,MAX(block_avg) AS max_avg
FROM
	(SELECT
		date_trunc('hour', created_at) AS created_at_trunc
		,AVG(value) AS block_avg
	FROM stock_values GROUP BY date_trunc('hour', created_at)) AS x,
	(SELECT
		('today'::date+'1 hour'::interval*q)::timestamp AS block
	FROM generate_series(0, extract('hour' from localtimestamp)::integer) AS q) AS y
WHERE created_at_trunc > block - '1 hour'::interval AND created_at_trunc <= block
GROUP BY 1 ORDER BY 1

I worked into this solution beginning with a mailing list post I found here.

It'd be pretty easy to create a login form with no model except then I'd have to handle exceptional situations (validation!) outside of the typical flow which makes skipping the model ultimately just cost me extra time in the form of more work. I'm also using Formtastic, specifically Bootstrap-Formtastic, and I really like consistency in my application.

So based on this article I implemented a database-less ActiveRecord model so I could utilize semantic_form_for and remain consistent with the rest of my application.

#./app/controllers/sessions_controller.rb
class SessionsController < AdAuthController
  before_filter :check_authentication, :except => [ :new, :create, :destroy ]
  ⋮
  class LoginUser < ActiveRecord::Base
    validates :username, :presence => true
    validates :password, :presence => true

    def self.columns
      @columns ||= []
    end
    def self.column(name, sql_type = nil, default = nil, null = true)
      columns << ActiveRecord::ConnectionAdapters::Column.new(name.to_s, default, sql_type.to_s, null)
    end

    column :username, :string
    column :password, :string
  end
end

And since I'm using an embedded class its important to remember to specify the correct symbol in the parameters:

#./app/controllers/sessions_controller.rb
  ⋮
  def create
    @login_user = LoginUser.new(params[:sessions_controller_login_user])
    ⋮
  end
  ⋮

Because we are backing to a HBase cluster I expect this isn't the last time I'll need to use this approach on our project.

After creating a new Rails project, in the Gemfile:

#./Gemfile
⋮
gem 'haml' # you probably have already done this
group :development do
  gem 'haml-rails' # you'll want this so all subsequent views are generated using Haml
  gem 'hpricot'
end
⋮

Then create a new lib/tasks/erb2haml.rake Rake task:

#./lib/tasks/erb2haml.rake
desc "Creates haml files for each of the erb files found under views (skips existing)"
task :erb2haml do
  from_path = File.join(File.dirname(__FILE__), '..', '..', 'app', 'views')
  Dir["#{from_path}/**/*.erb"].each do |file|
    puts file
    # for each .erb file in the path, convert it & output to a .haml file
    output_file = file.gsub(/\.erb$/, '.haml')
    `bundle exec html2haml -ex #{file} #{output_file}` unless File.exist?(output_file)
  end
end

Now don't forget that its not rake erb2html, its rake erb2haml!

Originally built this task for each project based on this post.

Fortunately the fix is incredibly easy [thanks to my co-worker] and probably well known to many OS X vim users relying on Terminal.app: In your Terminal preferences (⌘,) under Settings > Advanced tab set Declare terminal as: to "xterm-256color". Restart your terminal sessions and 256 color themes will now work.

I figured out what the problem was from the excellent Xoria256 color theme which helpfully reports when your terminal is not running in 256 color mode with a message during vim startup...

if &t_Co != 256 && ! has("gui_running")
  echomsg ""
  echomsg "err: please use GUI or a 256-color terminal (so that t_Co=256 could be set)"
  echomsg ""
  finish
endif

As this is for OS X Lion's Terminal.app you won't need SIMBL or any other hacks to get this working.

Because the contents of the local directory's .pryrc is evaluated before the :before_session hook from ~/.pryrc timing is a bit more delicate. I get around this by creating a custom function named _pry_before_session (but you could name it anything you want really) and have the ~/.pryrc's before_session hook execute it if it exists.

So my project's .pryrc:

#~/Projects/ActiveAvro/.pryrc
def _pry_before_session
  require 'active_avro'
  require 'spec_helper'
  ActiveAvroHelper.initialize
end

And my updated ~/.pryrc looks like this:

#~/.pryrc
require 'interactive_editor'

Pry.config.editor = "mate"
# add the current directories /lib and /spec directories to the path if they exist
before_session = Proc.new do |out, target, _pry_|
  dir = `pwd`.chomp
  %w(lib spec test).map{ |d| "#{dir}/#{d}" }.each { |p| $: << p unless !Dir.exists?(p) || $:.include?(p) }
  # if a local .pryrc defines a _pry_before_session function, execute it now
  send(:_pry_before_session) rescue nil
end
Pry.hooks[:before_session] = before_session

Ruby code you place in your ~/.pryrc is executed when your Pry session begins.

Here's the relevant lines from my ~/.pryrc that add the appropriate paths to $LOAD_PATH during start up.

#~/.pryrc
⋮
# add the current directories /lib and /spec directories to the path if they exist
before_session = Proc.new do |out, target, _pry_|
  dir = `pwd`.chomp
  %w(lib spec test).map{ |d| "#{dir}/#{d}" }.each { |p| $: << p unless !File.exist?(p) || $:.include?(p) }
end
Pry.hooks = { :before_session => before_session }

_arguments:448: _vim_files: function definition file not found

when attempting to tab complete a file name while invoking vim. Easy fix - at least for me: rm ~/.zcompdump. Found the answer here. How it got that way in the first place is another mystery.