Mastodon

Category: rails

Postgres and Rails – for MySQL people

A little while ago I decided to switch from MySQL to Postgres for my development database. Although I love how command line friendly MySQL is and think group_concat is the bomb, I’ve been burned my MySQL’s loosey-goosey nature and had it corrupt some data on me. I decided that it was time to switch to Postgres. The transition was a little aggravating at times but it doesn’t take long to get used to it. Installing Postgres is easy enough on Ubuntu:

mike@sleepycat:~$ sudo aptitude install postgresql postgresql-server-dev-8.4

If you need to start at new Rails project you can specify Postgres right from square one:

mike@sleepycat:~/test$ rails myapp -d postgresql

After installing the database I started to look for the gems that provide the driver. I found myself sorting through a bewildering number of gems. “Postgres”, “postgres-pr”, a few minutes search turned up several options each of which proved to be the wrong thing. The gem you need is actually called ruby-pg and is written by a group of people that includes the actual creator of the Ruby language, Yukihiro Matsumoto. Although it is called ruby-pg if you ever want to find it on the internet, you install it using the name pg:

sudo gem install pg

The next stumbling block for me was my assumption that the naming of the Rails adapter would be similar to MySQL. Since adapter: mysql is what I have been using in my project already, I assumed adapter: postgres would be fine. It’s not.

development:
adapter: postgresql
database: myapp_development
username: postgres
password: password
host: localhost

Coming from MySQL, it took a fair bit of reading to collect the Postgres equivalent of all the commands I use most often. Just to save you some looking here is my “Cole’s Notes” version:

Setting a password for the default user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres psql postgres
[sudo] password for mike:
psql (8.4.2)
Type “help” for help.
postgres=# \password postgres
Enter new password:
Enter it again:

Create a database as user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres createdb myapp_development

Drop a database as user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres dropdb myapp_development

Get to the command line of the database myapp_development as user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres psql myapp_development

Load the file ‘pgdump.sql’ into the myapp_development database as the user ‘postgres’:

mike@sleepycat:~/$ sudo -u postgres psql -f pgdump.sql myapp_development

Dump a Postgres database to a file:

mike@sleepycat:~/$ sudo -u postgres pg_dump myapp_development > ~/myapp_dev_dump.sql

Show tables:

postgres=# \dt

show a table:

postgres=# \d tablename

quit:

postgres=# \q

Thats what worked for me. In general I feel like using postgres is forcing me to write more portable code. Pretty much anything I write in Rails that runs in postgres will run on MySQL, but the reverse is really not true. Little things like using ‘1’ for true. MySQL is fine with that but for Postgres with a real boolean datatype, true is true. 1 is not. I stumbled arcoss that when I was using validates_acceptance_of. I feel like its also forcing me to write better SQL as well. If you’ve written a bunch of “find_by_sql” in an existing app you might not appreciate its pickyness though. However once the initial pain of the switch passes, Postgres is pretty great.

Dealing with a stale cache at the Rails console

Working on other peoples apps can be tough. In spite of all the “convention” that is supposed to come over configuration, people still do a hell of a lot of configuring. In the end it takes a while figure out what’s what in a new app. Different places to create the same effect, dozens of ways to write equivalent code. After scratching my head for a while over a page that had a bunch of links to various models that all resulted in 404’s I was getting a little frustrated. The production log was no help. After dumping the DB and copying it to my dev database I noticed the problem was gone when I ran the app on my local machine. WTF? Then it hit me: Its cached.

So the task was to clean a stale cache from the console. A little searching turned up these gleaming bits of awesomeness and I’m putting them up here so I never forget them. For a starter on caching you should definitely check out the Railsguide.

This will return the cache directory in case you need to “expire” it manually (defaults to the public directory):

ActionController::Base.page_cache_directory

Or clear a page from the cache with something like this:

ApplicationController.expire_page(:controller => “site”, :action => “index”)

or a fragment:

ActionController::Base.new.expire_fragment(:controller => “my”, :action => “index”)
ActionController::Base.new.expire_fragment(/\/productions.*/)
ActionController::Base.new.expire_fragment(‘sidebar’)

I’ll be remembering that one!

Rails 3 on Heroku

Heroku sent around an email a little while ago talking about their “experimental” Rails 3 support. After a night of tinkering with a Rails 3 project  I thought I might try it out. When I pushed my project I got this:

Total 131 (delta 27), reused 0 (delta 0)

—–> Heroku receiving push
—–> Gemfile detected, running Bundler
Unresolved dependencies detected; Installing…
Fetching source index from http://gemcutter.org/
Resolving dependencies
Installing abstract (1.0.0) from system gems
Installing actionmailer (3.0.0.beta) from rubygems repository at http://gemcutter.org/
Installing actionpack (3.0.0.beta) from rubygems repository at http://gemcutter.org/
Installing activemodel (3.0.0.beta) from rubygems repository at http://gemcutter.org/
Installing activerecord (3.0.0.beta) from rubygems repository at http://gemcutter.org/
Installing activeresource (3.0.0.beta) from rubygems repository at http://gemcutter.org/
Installing activesupport (3.0.0.beta) from rubygems repository at http://gemcutter.org/
Installing arel (0.2.1) from rubygems repository at http://gemcutter.org/
Installing builder (2.1.2) from system gems
Installing bundler (0.9.12) from rubygems repository at http://gemcutter.org/ /usr/local/lib/ruby/site_ruby/

1.8/rubygems/installer.rb:192:in `install’: bundler requires RubyGems version >= 1.3.6 (Gem::InstallError)
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/source.rb:42:in `install’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/installer.rb:30:in `run’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/installer.rb:18:in `each’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/installer.rb:18:in `run’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/installer.rb:6:in `install’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/cli.rb:60:in `install’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/vendor/thor/task.rb:33:in `send’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/vendor/thor/task.rb:33:in `run’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/vendor/thor/invocation.rb:109:in `invoke’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/vendor/thor/invocation.rb:116:in `call’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/vendor/thor/invocation.rb:116:in `invoke’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/vendor/thor.rb:137:in `start’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/vendor/thor/base.rb:378:in `start’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/lib/bundler/vendor/thor.rb:124:in `start’
from /usr/local/lib/ruby/gems/1.8/gems/bundler-0.9.9/bin/bundle:11
from /usr/local/bin/bundle:19:in `load’
from /usr/local/bin/bundle:19
FAILED: Have you updated to use a 0.9 Gemfile?

Wha? I had filled out my Gemfile, what could it be? It turns out that you need to specify a “stack” to use when you are running Rails 3. The stack is basically all the a specific configuration of all the supporting software (the OS, Ruby, whatever else). Whatever stack I had been given by default (“aspen”) was not the one that contained Rubygems 1.3.6. Check out their stacks page on the Heroku site for more info.

The fix? Even Though I find Heroku’s documentation is usually pretty bad, this time they had exactly what I needed:

mike@sleepycat:~/projects/myapp$ heroku stack:migrate bamboo-ree-1.8.7
—–> Preparing to migrate
aspen-mri-1.8.6 -> bamboo-ree-1.8.7

NOTE: You must specify ALL gems (including Rails) in manifest

Please read the migration guide:
http://docs.heroku.com/bamboo

—–> Migration prepared.
Run ‘git push heroku master’ to execute migration.
Next time I pushed it worked fine. Yay!

Rails 3 finder syntax

Reading about Rails 3 and all the changes that were made is definitely exciting. Big changes to the ActiveRecord API, revamping routing, a tonne of new stuff. At the same time it felt kind of depressing because at first glance it all looked SO different. Even good changes mean that you have to remember the old stuff to work on your old apps and the new stuff for the new apps. Its not as though there isn’t enough learning involved in this occupation already…

Now that I am starting to play with Rails 3 in some of my personal projects, I am happy to report that there is plenty that is exactly the same in Rails 3 and that even the changes are pretty easy to get used to.

So here you go, some things are the same in Rails 3:

Model.find id

Model.find id, id

Model.all

Model.first

Model.last

Model.find_by_…..

Just avoid Model.find :first and Model.find :all. Especially since this syntax will not just deprecated but removed in Rails 3.2. Pratik from the core team has all the details.

The real changes are really just in how you handle giving options and conditions to the find. In fact the new syntax is very jQuery-like, which you are probably using anyway. It becomes natural very quickly.

Things like:

Model.where “id > 1”

or

Model.order(“id desc”).limit 1

There is lots more info over at Rails guides. Not so bad really. Now to figure out what they have done with the Routing!

Rails integer handling

Well it certainly is the case that you learn something new every day. After digging into some confusion around how Rails chooses its data types I ended up turning to the API docs.  From the API docs:

:limit – Requests a maximum column length. This is number of characters for :string and :text columns and number of bytes for :binary and :integer columns.

:limit => 11 would give us 11 characters in a string column, but I was trying to apply it to an integer.  As they said in the docs, when you use :limit with an integer it means the number of bytes. So, for the sake of my own mental clarity here is what you should get when you use the :limit option:

:limit => 1 TINYINT 1 byte -128 to 127
:limit => 2 SMALLINT 2 bytes -32768 to +32767
:limit => 3 MEDIUMINT 3 bytes -8388608 to 8388607
:limit => 4 INT 4 bytes -2147483648 to +2147483647
:limit => 8 BIGINT 8 bytes -9223372036854775808 to 9223372036854775807

I say “should” above because Rails will take the value you give it and map that as best it can to whatever the actual database you are using can provide. So for MySQL it will give you a bigint for any :limit value between 5 and 8. One strange thing is that :limit => 11 maps to a 4 byte int(11) in MySQL. Not sure what thats about. For values that fall outside those, well, I guess I’ll figure that out another day.

Bundle mania

I ended up using Heroku’s bundles feature a fair bit today. For those who have not played with this yet, a bundle is essentially a zip/tar file containing all the code from your application along with a dump  of your database. From the help file:

bundles                      # list bundles for the app
bundles:capture [<bundle>]   # capture a bundle of the app’s code and data
bundles:download             # download most recent app bundle as a tarball
bundles:download <bundle>    # download the named bundle
bundles:animate <bundle>     # animate a bundle into a new app
bundles:destroy <bundle>     # destroy the named bundle

In general its pretty intuitive. The commands do what you expect; “bundles:capture” captures a bundle, while “bundles:download mybundle” downloads the bundle named mybundle. With my expectations set by using the other commands I tried out bundles:animate

mike@sleepycat:~/projects/myapp$ heroku bundles
2010-03-16        complete 03/17/2010 14:25
mike@sleepycat:~/projects/myapp$ heroku bundles:animate 2010-03-16
Animated myapp 2010-03-16 into http://myapp-2010-03-16.heroku.com/ | git@heroku.com:myapp-2010-03-16.git

Given the description “animate a bundle into a new app” I was expecting a fully functional copy of my application waiting for me at that new address, based on the code and the database dump contained in the bundle. Instead I get:

mike@sleepycat:~/projects/myapp$ heroku console –app myapp-2010-03-16
!   myapp-2010-03-16 has an empty code repository. Push and try again.

When I log in and look at my account, sure enough the repository is empty and even more surprising:

Data size

0 Bytes in 0 tables

So if it does not load any of the code from my bundle, and doesn’t use the database dump to create a database, how does this differ from creating a new app?

Rails 3 on Ubuntu Lucid.

I just got finished updating my Rails setup script and testing it on the Alpha 3 version of Lucid. Now that the packages seem to be settling down the the repositories for Lucid suddenly I can find the ones I need for Rails. The script now gets Rails 3 running on either Karmic or Lucid. If you need older versions of Rails just uncomment the lines for the version you need.

You can get  the latest version of the script here: http://github.com/sleepycat/wrong-side-of-the-tracks

Mysql’s explain and indexes in ActiveRecord.

After doing some work on optimising a query in Rails app I am working on I got to thinking about how useful MySQL’s explain statement is. It happens to be really useful for figuring out what, if any, indexes are being used by a given select statement. So I decided to see what I could do about adding that functionality to Rails myself. Projects like this really give you a great tour through a lot of Rails internals, so I figured it would be good for me whether I could figure it out or not. After a little hacking I came up with a patch to ActiveRecord::Base.connection that runs an explain statement before every select. I wrapped it in an unless statement to make sure it does not run if the mode is production.

Explain (0.000000)    | select_type: SIMPLE | key_len: 263 | table: taggings | id: 1 | possible_keys: index_taggings_on_tag_id,index_taggings_on_taggable_id_and_taggable_type | type: ref | Extra: Using where | rows: 1 | ref: const,const | key: index_taggings_on_taggable_id_and_taggable_type
Tag Load (0.000910)   SELECT `tags`.* FROM `tags` INNER JOIN taggings ON tags.id = taggings.tag_id WHERE ((`taggings`.taggable_id = 5) AND (`taggings`.taggable_type = ‘Member’))

The “possible keys” portion is where it lists the indexes for that apply to that query. If there are none there and this is a query that runs a fair bit you probably want to think about adding an index. For me this is a great way to make indexes, or a lack thereof more visible to me during development because they are really easy to forget. So in the spirit of “If you want to truly understand something, try to change it”, here is my first bit of tinkering with ActiveRecord:

unless RAILS_ENV == ‘production’
module ActiveRecord
module ConnectionAdapters
class MysqlAdapter < AbstractAdapter

def select_with_explain(sql, name = nil)

explanation = execute_with_disable_logging(‘EXPLAIN ‘ + sql)

e = explanation.all_hashes.first
exp = e.collect{|k,v| ” | #{k}: #{v} “}.join

log(exp, ‘Explain’)

select_without_explain(sql, name)
end

def execute_with_disable_logging(sql, name = nil) #:nodoc:
#Run a query without logging
@connection.query(sql)
rescue ActiveRecord::StatementInvalid => exception
if exception.message.split(“:”).first =~ /Packets out of order/
raise ActiveRecord::StatementInvalid, “‘Packets out of order’ error was received from the database. Please update your mysql bindings (gem install mysql) and read http://dev.mysql.com/doc/mysql/en/password-hashing.html for more information.  If you’re on Windows, use the Instant Rails installer to get the updated mysql bindings.”
else
raise
end
end

alias_method_chain :select, :explain

end
end
end
end

There it is, quick and dirty. If it ends up being something that I, or other people find really useful I will look at cleaning it up, adding some tests and maybe making a gem out of it. In the mean time you can just paste it into a file in your initializers directory. Happy indexing!

Caveats: I have got this working with Rails 2.1 with MySQL. Its only intended for helping out a little during development, and I have no idea what might happen if you run it elsewhere. If you have some suggested improvements I would love to hear them.

Wrong side of the tracks: Rails on Ubuntu

Ever noticed that all the Ruby on Rails people seem to be “Mac people”? DHH and Ryan Bates (Rails casts), the guy from Peepcode all spring to mind. My boss and every other person that I have met that works in Rails, all do it on a Mac.

Well it might not have the sex appeal of the Mac, but Ubuntu is an amazing development platform and runs Ruby on Rails perfectly. My only real gripe with it, is that its a pain keeping up with their release cycle. Six months goes by really fast, and every time I install the latest version of Ubuntu,  I have to set up Rails again (I’m not one for upgrades, clean installs are the way to go).

Last time I decided that enough was enough. I wrote a setup script that installs everything needed for Rails development in one go.

I’ve used it a few times since then and its already saved me hours of setup time so I thought I would make it available.  So here you go world, even though this might be the slightly ghetto way to develop in Rails, it just got a little easier.

You can find it on github: http://github.com/sleepycat/wrong-side-of-the-tracks

Setting up terminal windows for Rails development

Even though Netbeans make things like the Rails development log and the database connections pretty easy to get to, I still have a pretty strong preference for the command line. When I am coding I almost always have a few terminal windows open and stretched nice and big; generally one for mysql, one for the Rails development log and often a third for the Rails console in case I want to experiment with the code I am writing.

The down side to this set up is just that; the set up. Every day I type in the same stuff… mysql -u root blah blah blah.

So last night, after an ill advised post dinner coffee, I set about scripting my morning Rails ritual and on the way learned a bunch about the terminal.

Gnome terminal is the proper name for the program that resides under Applications > Accessories > Terminal. A quick look at the man pages give a pretty nice set of options to play with but I found they required a bit of trial and error to get right.

Cut and paste the ones you want into custom launchers.

Launching Mysql and getting a prompt on a specified database:

gnome-terminal  -e “mysql -u root -ppassword -D myapp_development”

Thats no typo! There can’t be any space between the -p option above and the actual password!

Launching a window with the Rails development log (with a specified Rail environment):

gnome-terminal  –working-directory=/home/mike/projects/myapp -e “script/server -e mikes”

That one is great for me because I am rapidly becoming sick of typing in stuff for custom Rails environments. Now I don’t need to!

Those are pretty fun, but the ultimate in convenience for me would be to have a single tabbed terminal window with everything. So why not combine the two into a single command?

gnome-terminal –geometry=174×46+50+50 –tab-with-profile=Default –title=”Mysql” -e “mysql -u root -ppassword -D myapp_development” –tab-with-profile=Default –working-directory=/home/mike/projects/myapp –title=”Server” -e “script/server -e mikes” –tab-with-profile=Default –working-directory=/home/mike/projects/myapp –title=”Console” -e “script/console mikes”

This is a doozie but it means that my whole morning Rails set up ritual has been reduced to a single click on a launcher. Sweet!