Rake Task to Load SQL

Posted by Pat Sat, 11 Feb 2006 23:58:00 GMT

Sometimes I have SQL files that I need to run. These can be from old projects, or something that another programmer working on the same project gives to me. It’s easy enough to run psql to load it up, but I’m lazy and would prefer not to have to type in things like the databsae name. More importantly, sometimes I’d like to do some processing on the DB immediately after loading the SQL. So I wrote a simple rake task to do it.

def load_sql_file(file)
  config = ActiveRecord::Base.configurations[RAILS_ENV]
  database = config['database']
  user = config['username']
  puts `psql -U #{user} -f #{file} #{database}`
end

desc 'Load an SQL file'
task :load_sql => [ENV['FILE'], :environment] do |t|
  file = ENV['FILE']
  file = RAILS_ROOT + "/" + file unless file[0].chr == '/'
  load_sql_file file
end

As you can see, the load_sql_file method simply invokes the CLI psql tool using your app’s settings.

The load_sql task takes an environment variable named FILE and runs load_sql_file using the filename specified. If a a relative pathname is given, it uses RAILS_ROOT as the base bath.

This lets you easily load up an SQL file: 
$ rake load_sql FILE=db/sql_file.sql
$ rake load_sql FILE=/usr/local/etc/sql_file.sql

I realize that’s not a huge deal, but I prefer running rake tasks to typing out psql with its arguments all the time, and this lets me set RAILS_ENV while running the task.

The final benefit is that you can create named tasks which load the SQL file and do some processing. Just as an example, let’s say I have an SQL file that inserts a bunch of user records, and for whatever reason I want to make sure that all the users have the admin field set to false. I could do something like this: 
desc 'Load up the users and set admin fields to false'
task :load_users => ["#{RAILS_ROOT}/db/users.sql", :environment] do |t|
  load_sql_file t.prerequisites.first
  User.update_all "admin=false"
end

Now running ‘rake load_users’ from the command line will load up the users.sql file and update every User object’s admin field to false.

So there you go, a task to easily load SQL files, as well as the ability to create named tasks for particular files. You can use that either just to have a named task, or to do some postprocessing.

Tags ,  | no comments

Any interest in FLPR hosting?

Posted by Pat Sat, 11 Feb 2006 13:46:00 GMT

Yogi asked if I knew of a FLPR host. I don’t, but I’ve kind of been considering offering it myself. I’d like to get guage the interest level, so if you’d be interested in hosting on FLPR, let me know.

Posted in  | Tags ,  | 3 comments

Setting up Subversion Access through Apache

Posted by Pat Thu, 09 Feb 2006 22:10:00 GMT

I’ve been using Subversion for my source code control for a few months now. For the server, I’ve just been using the built-in svnserve, which has worked perfectly fine for me. However lately I’ve been doing more projects for outside companies, and dealing with SVN access has been a pain. I have access control on my repository, but svnserve doesn’t allow any fine-grained access…any user has access to the entire repository. I also want to make some of my code publicly available over SVN. Naturally I don’t want everyone in the world to have access to all my business code.

One way to have more fine-grained access control over the repository is to use Apache and mod_dav_svn. Unfortunately we can’t use lighttpd for the task because it doesn’t support mod_dav_svn. I’m not too pleased about having to install Apache, but it’s the right tool for the job. Also, since it will only be a front end to my Subversion repository, I can keep it pretty small.

Installing and Configuring Apache 2

The Subversion documentation says you need to use Apache 2.0, not 1.3. Checking out /usr/ports/www, you’ll see there are three versions of Apache 2 – 2.0, 2.1, and 2.2. I figure it makes sense to just go with the latest one, so that’s what I’ll install. Should work fine though if you decide to go with 2.0 or 2.1 instead.

There are a couple make options that we need to set for the Apache install. You can do these on the command line, but it’s probably a good idea to throw them in /etc/make.conf so that when you upgrade Apache, those options are remembered. So put the following lines in /etc/make.conf: 
# Apache config
.if ${.CURDIR:M*/www/apache2*}
WITH_AUTH_MODULES=yes
WITH_DAV_MODULES=yes
WITH_SSL_MODULES=yes
WITH_BERKELEYDB=db42
.endif

# Subversion config
.if ${.CURDIR:M*/devel/subversion}
WITH_APACHE2=true
WITH_MOD_DAV_SVN=yes
.endif
Now go ahead and build Apache: 
# cd /usr/ports/www/apache22/ && make install clean
Now you’ll need to rebuild subversion: 
# cd /usr/ports/devel/subversion && make deinstall && make install clean
For Apache to be able to use the repository, it must have read/write access to it. Initially I set up my repository at /home/svn/repos, but since I’ll no longer be using the ‘svn’ user, I wanted to move it under /usr/local. So just copy the whole repository over and change the ownership. 
# cp -Rp /home/svn/repos /usr/local/repos
# chown -R www:www /usr/local/repos
Finally you need to configure Apache to expose your SVN repository. Go ahead and edit /usr/local/etc/apache22/httpd.conf. I commented out all the modules I didn’t need, so that I could keep Apache as small as possible. Here’s what my module list looks like, along with an example Subversion config: 
LoadModule authn_file_module libexec/apache22/mod_authn_file.so
LoadModule authz_host_module libexec/apache22/mod_authz_host.so
LoadModule authz_groupfile_module libexec/apache22/mod_authz_groupfile.so
LoadModule authz_user_module libexec/apache22/mod_authz_user.so
LoadModule authz_dbm_module libexec/apache22/mod_authz_dbm.so
LoadModule authz_owner_module libexec/apache22/mod_authz_owner.so
LoadModule authz_default_module libexec/apache22/mod_authz_default.so
LoadModule auth_basic_module libexec/apache22/mod_auth_basic.so
LoadModule auth_digest_module libexec/apache22/mod_auth_digest.so
LoadModule include_module libexec/apache22/mod_include.so
LoadModule log_config_module libexec/apache22/mod_log_config.so
LoadModule logio_module libexec/apache22/mod_logio.so
LoadModule env_module libexec/apache22/mod_env.so
LoadModule dav_module libexec/apache22/mod_dav.so
LoadModule dav_svn_module libexec/apache22/mod_dav_svn.so
LoadModule authz_svn_module libexec/apache22/mod_authz_svn.so
LoadModule dav_fs_module libexec/apache22/mod_dav_fs.so

<Location />
  DAV svn
  SVNPath /usr/local/repos
</Location>
Now put the following line in /etc/rc.conf: 
apache22_enable="YES"
And start up apache: 
# /usr/local/etc/rc.d/apache22.sh start

Congrats, you should now be able to go to http://ip/ in your web browser to view your repository. For more detailed config info, check out the Apache section of the SVN Book.

Switching your working copies to use the new URL

You have two options at this point – either just check out your projects again using the new URL, or use the svn switch command to switch each working copy to the new URL. Either one works fine, and the first is probably easier in general. Just ‘svn co http://12.34.56.78/project’. However in case you want to immediately switch it, run the following commands (you need to have svnserve running for this to work): 
$ cd working_copy
$ svn switch --relocate svn://12.34.56.78/project http://12.34.56.78/project

Once you’ve done all of this, you don’t need to run svnserve anymore, so you should kill it off. If you forgot to switch a working copy to the new URL, you’ll get an error as soon as you try to execute a command like svn update, so you’ll immediately know to just check out the project again.

Final thoughts

I spent about 15 minutes configuring Apache last night, have a nice little setup. Now my SVN repository is available to the public, and my private projects are all password protected. In addition, I can create users for each private project I’m working on, so the companies I’m developing for have easy SVN access to their own projects, without exposing other companies’ projects. Pretty sweet.

Posted in  | Tags ,  | 6 comments

Setting default boolean values in migrations

Posted by Pat Tue, 07 Feb 2006 14:47:00 GMT

I just ran into a strange problem with a migration. It looked like: 
 def self.up
    add_column :restaurants, :visa, :boolean, :default => false, :null => false
 end
Turns out that you can’t use :default => false when using add_column (it works fine in a create_table statement). You need to use :default = 0 instead, so the code looks like 
 def self.up
    add_column :restaurants, :visa, :boolean, :default => 0, :null => false
 end

I imagine this is because MySQL doesn’t provide actual boolean support. Not quite sure what the problem is, and I don’t have time to look at the code right now, but if you run into that problem, that’s how you solve it.

Posted in ,  | Tags , ,  | no comments

Using mod_alias to Share Assets

Posted by Pat Thu, 26 Jan 2006 07:19:00 GMT

In awstats – The Static Way, I showed you how to generate static stats pages using awstats. Shane quickly pointed out that the icons won’t work properly. I hadn’t noticed this, because awstats has very nice alt tags, but he’s absolutely right. There’s a much easier way to fix it though, and it involves using a nice (though apparently poorly documented) feature of our favorite webserver – mod_alias.

mod_alias is kind of like a simple mod_rewrite, but instead of rewriting the URL, it takes the incoming URL and maps it to a different location on the file system. This allows you to set up an single alias in your lighty config file, and use that path among all your sites. In the case of awstats, the HTML files all link to images in /awstatsicons/. If you followed my instructions though, that won’t exist. mod_alias to the rescue.

First just add mod_alias to the list of modules in lighttpd.conf. It’ll end up looking something (though perhaps not exactly) like this: 
server.modules              = (
                                "mod_rewrite",
                                "mod_redirect",
                                "mod_proxy",
                                "mod_access",
                                "mod_fastcgi",
                                "mod_alias", # Add this line
                                "mod_compress",
                                "mod_accesslog" )
The next thing to do is set up an alias. In our case, we want /awstatsicons/ to be accessible to every site we run, and the icons are stored in /usr/local/www/awstats/icons. So in your config file, before any virtual hosts are defined, add this line: 
alias.url = ( "/awstatsicons/" => "/usr/local/www/awstats/icons/" )

Now load up your stats page, and you’ll see the pretty icons!

Final Thoughts

As you can see, mod_alias is a pretty useful module for sharing static assets. If you have a bunch of images or CSS files that you want to share among a number of different web sites, you can simply use mod_alias to set up a common URL path. Pretty slick.

Posted in  | Tags , ,  | no comments

awstats - The Static Way

Posted by Pat Tue, 24 Jan 2006 23:27:00 GMT

awstats seemed pretty nice, but I didn’t really want to run it via CGI. I’m sure there’s nothing wrong with it…but one less server side process is one less point of attack, particularly if I didn’t write the process.

awstats provides a way of building static HTML pages instead of using a cgi script to view the stats. I wrote up a quick script that will generate stats for all my sites. Make it run whenever you want via cron, and you’re all set. Here’s the code:

awstats_build_stats.rb 
#!/usr/local/bin/ruby

# Define the location of awstats, and the dir to output the stats pages to
perl = '/usr/bin/perl'
awstats = '/usr/local/www/awstats'
statscmd = "#{perl} #{awstats}/tools/awstats_buildstaticpages.pl"
statsdir = '/home/pergesu/www/stats'

# Get all the config files
configs = Dir["#{awstats}/cgi-bin/awstats.*.{com,net,org}.conf"]
siteregexp = /awstats.(S+).conf$/

# Loop through all the configs
configs.each do |conf|
  res = siteregexp.match(conf)
  site = res[1]
  # Create the dir if it doesn't exist
  Dir.mkdir("#{statsdir}/#{site}") unless File.exists?("#{statsdir}/#{site}") and File.directory?("#{statsdir}/#{site}")

  # Now run the command to generate the stats
  `#{statscmd} -config=#{site} -update -awstatsprog=#{awstats}/cgi-bin/awstats.pl -dir=#{statsdir}/#{site}`
end

I throw it all in a single stats dir because I have a couple sites that all have different docroots…they’re all in the same main /home/www/pergesu folder, but some have rails project folders which throws the naming off. So I just spit the output to a central stats dir, then I manually symlinked a stats folder in each site’s docroot to its appropriate awstats folder. I also symlinked index.html to the generated main awstats page (awstats.site.com.html) so I can just go to http://www.mysite.com/stats/ and view the stats.

One limitation here is that it doesn’t let me navigate between months or years to view past stats. There may be an option in the awstats_buildstaticpages.pl script, I’m not sure. For now, this works fine, and I don’t have to run a CGI process to view my stats.

Tags ,  | 1 comment

Database-less Testing

Posted by Pat Tue, 24 Jan 2006 10:08:00 GMT

Rails is awesome for building database-driven applications, and has a very nice testing framework built right in. Unfortunately it’s a bit too built in. I’ve recently been writing a number of small applications that don’t use a database. I started off writing my tests as usual..then rake blew up on me. I honestly don’t remember the exact errors…I asked on the Rails list, and made a frustrated post after my first question didn’t lead to satisfactory results. No luck still.

Okay enough about that..I think I’ve found the answer. Basically you need to strip AR entirely out of your Rails project, and redefine one of the rake tasks.

First of all, in your app’s environment.rb file, make sure you don’t load AR:

config.frameworks -= [ :active_record ]

You should of course exclude any other frameworks you don’t want.

The next thing to do is make a stripped down version of the test_help.rb file. This isn’t the same as test_helper.rb which lives in the test/ dir, it’s a file named test_help.rb I found in the bowels of Railties, but without the AR and fixture loading stuff. This should go in the lib/ dir.

lib/test_help_without_ar.rb 
require 'application'

# Make double-sure the RAILS_ENV is set to test, 
# so fixtures are loaded to the right database
silence_warnings { RAILS_ENV = "test" }

require 'test/unit'
require 'action_controller/test_process'
require 'action_web_service/test_invoke'
require 'breakpoint'

Here’s the original code in railties, so you can see what I took out.

require 'application'

# Make double-sure the RAILS_ENV is set to test, 
# so fixtures are loaded to the right database
silence_warnings { RAILS_ENV = "test" }

require 'test/unit'
require 'active_record/fixtures'
require 'action_controller/test_process'
require 'action_web_service/test_invoke'
require 'breakpoint'

Test::Unit::TestCase.fixture_path = RAILS_ROOT + "/test/fixtures/"

def create_fixtures(*table_names)
  Fixtures.create_fixtures(RAILS_ROOT + "/test/fixtures", table_names)
end

Now go back and edit test/test_helper.rb to use this test_help file instead of the default rails one. It’s a simple matter of loading our custom test_help file:

test/test_helper.rb 
ENV["RAILS_ENV"] = "test"
require File.expand_path(File.dirname(__FILE__) + "/../config/environment")
require 'test_help_without_ar'

class Test::Unit::TestCase
  # Add whatever specific functionality you want
end

After some more digging through railties, I came across the prepare_test_database rake task. Redefine it to do nothing, and everything works fine. I found some code that Blair Zajac wrote to provide a method for redefining a rake task. I’m only showing the code here, but his full comments and credit appear in the attached file.

lib/tasks/testing.rake 
# The following code is by Blair Zajac and appeared in the Ruby on Rails mailing list.
# See archived message here:  http://article.gmane.org/gmane.comp.lang.ruby.rails/27426

module Rake
   class Task
     # Clear all existing actions for the given task and then set the action for the task to the given block.
     def self.redefine_task(args, &block)
       task_name, deps = resolve_args(args)
       TASKS.delete(task_name.to_s)
       define_task(args, &block)
     end
   end
end

# Clear all existing actions for the given task and then set the action for the task to the given block.
def redefine_task(args, &block)
   Rake::Task.redefine_task(args, &block)
end

# The following code is by (obvious) master hacker Pat Maddox
desc "Prepare the test database"
redefine_task :prepare_test_database do |t|
  # Don't do anything, there's no database to prepare
end

Final Thoughts

This gave me a real headache back when I first tried to do it, I couldn’t find anything to help me out. However now I can happily write all my tests and use rake to automatically run the tests. I’ve noticed that it seems to be a bit slow..I’m really not sure why, but if it’s working then I’m happy. Hopefully someone else will find this useful.

Posted in  | Tags ,  | 6 comments

Cool stats with awstats

Posted by Pat Tue, 24 Jan 2006 06:30:00 GMT

There was a post on the Rails list the other day about installing awstats on lighttpd. If you don’t know, awstats is a log analyzer tool that runs through your web server logs and generates cool stats. Since I want to see how much traffic I’m (not) getting, I figured I’d install it. Should be a breeze, because I’m using FreeBSD :)

cantona# cd /usr/ports/www/awstats/
cantona# make install clean

Now awstats is installed to /usr/local/www/awstats/. Follow the instructions on the link above to finish..took me about 5 minutes. I’d like to see if somehow I can make stats.mydomain.org automatically append the config for the incoming hostname. That sure would be sweet..might have to hack the script up a little bit.

Tip: In your lighttpd config file, you may have virtual hosts set up with something like:

$HTTP["host"] =~ "(www.)?mydomain.com"

I was stumped for a minute because it kept matching “stats.mydomain.com” when I didn’t think it should. Anyway, since it’s using Perl pattern matching, the key is to make sure that you check to see that the domain starts with that, which you do using ^:

$HTTP["host"] =~ "^(www.)?mydomain.com"

Pretty simple..regular expressions sure are fun.

Posted in ,  | Tags ,  | 2 comments

Tip - When you get a blank screen

Posted by Pat Thu, 19 Jan 2006 10:52:00 GMT

Occasionally when I’m working on an app, I’ll be going along just fine in dev mode. Then I put it up on my production server, and I get an absolute blank page on any request I make. Pisses me off like none other, because I look in the error logs and it’s nothing. I think I’ve figured it out though…just need to remove any existing session info from /tmp or the database.

I just wanted an excuse to post :) I’m working on some paid projects that allow me to write Rails code now, keeping pretty busy..but I need to raise my rates, because I finish way too quickly now.

Tags ,  | no comments

Testing with database constraints

Posted by Pat Mon, 12 Dec 2005 12:38:00 GMT

PostgreSQL calls itself “the world’s most advanced open source database.” I want to use some of the advanced features, dammit! The first, not-so-spectacular-but-still-nice feature that I want to use is foreign key constraints. If you don’t know what they are (read the links!), the gist of it is that you can specify that a field in one table refers to a field in another table. That’s the foreign key part. The constraint is that the record in the other table must exist. Something like:

Table Companies
id integer
name varchar(80)

Table Products
id integer
name varchar(80)
company_id integer  # This is a foreign key

company_id is a foreign key to the id field in the companies table. We haven’t specified it yet, and I’ll show how to do that later, but for now just understand that concept. Any value insert into products(company_id) must exist in companies(id). So for example, if you make products(company_id) = 2982475, and there’s no record in ‘companies’ with id = 2982475, postgresql will give you an error and no insert is made.

Similarly, if you try to delete a record in ‘companies’, and there are records in ‘products’ that reference it, postgresql will again give you an error. <anecdote style=”useless”>This would have been really nice the time I deleted a couple records I “didn’t need” anymore, and then my whole site stopped working. Turns out that there were still a few records somewhere else that referenced these. I should have used console to make the changes…but I didn’t know about it.</anecdote>

This is a good thing because it ensures you maintain referential integrity. Rails is nice and all, but a simple fact of business apps is that the data is far more valuable and has a longer life than the actual app you use to access it. You want to make sure that the database has no chance of having broken references. Bad data at best causes you lots of headaches, and at worst is absolutely worthless.

TDD with fkey constraints

I’m a huge fan of test-driven development. It’s just fun for some reason, and I quickly make lots of progress. Anyway, I wanted to be able to get foreign key constraints into my apps, so I needed to fit them into my process. Basically here’s what I came up with:

1. Write tests 2. Write code to make tests pass 3. Add foreign key constraints 4. Make sure tests still pass

I didn’t know the best way to add foreign key constraints, but then found out about migrations and thought I could give that a shot. I had a tough time getting started, so I made a post to the Rails list and then Robby Russell showed me how. Go read that article to learn how to use constraints in your migrations.

At first my development process went very smoothly. Then it blew up when I created two tables that referenced a single table. The problem is that when you run tests, rake doesn’t actually wipe the db clean like you might expect. Instead it just rolls it back to its initial state, which is to have the fixtures loaded. This means you end up with dirty tables, and then postgresql complains about you violating foreign key constraints. I haven’t explained it well, honestly, but the entire issue is covered here. There are a couple solutions listed there, and I’ll explain what I’ve managed to get working so far.

First solution – delete records in teardown()

The first fix is to include the following code in your test_helper.rb file inside the TestCase definition. 
def teardown
  self.class.fixture_table_names.reverse.each do |table_name|
    klass_name = Inflector.classify(table_name.to_s)
    if Object.const_defined?(klass_name)
      klass = Object.const_get(klass_name)
      klass.connection.delete("DELETE FROM #{table_name}", 'Fixture Delete')
    else
      flunk("Cannot find class for table '#{table_name}' to delete fixtures")
    end
  end
end

This overrides the teardown method so that when a test is done completes, all the fixtures will be deleted from the db. The only problem with this solution is that I couldn’t use transactional fixtures with it. Testing worked, it was just a bit slow.

Second solution – Make your models paranoid

I ended up using a different solution, which is to include the following code in test_helper.rb, but this time outside of any class definitions: 
# neuter fixture deletions and multiple fixture insertions to work with database constraints
class Fixture
  attr_reader :class_name
end 

class Fixtures
  @@inserted_fixture_list ||= {}
  alias :original_insert_fixtures :insert_fixtures

  def insert_fixtures
    return if @@inserted_fixture_list[values[0].class_name]
    @@inserted_fixture_list[values[0].class_name] = true
    unless ActiveRecord::Base.connection.select_one("select 1 from #{fixture_class_to_table_name(values[0].class_name)}")
      original_insert_fixtures
    end
  end

  def delete_existing_fixtures() end

  # compute a fixture's table name from its (known) class name
  def fixture_class_to_table_name(class_name)
    class_name.gsub(/([a-z])([A-Z])/) { |match| "#{$1}_#{$2}" }.downcase.pluralize
  end
end

I did run into a problem here because it turns out that this code doesn’t delete any fixtures, which becomes obvious once you see that delete_existing_fixtures is empty. If I commented out the redefinition, I got a bunch of warnings about a transaction not being in progress. At this point I realized that I wouldn’t really want to delete any objects that are referenced anyway…I might not want them accessible to the user under normal circumstances, but I definitely don’t want them deleted from the database. So I installed Rick Olson’s acts_as_paranoid module and added that to the models that I wanted to be paranoid in the db. Paranoid models don’t actually get deleted when you call destroy(), they just get a deleted_at field set to the timestamp. Pretty slick.

Go with either of these solutions and you should be able to add foreign key constraints and test away to your heart’s content.

Posted in ,  | Tags , , ,  | no comments

Older posts: 1 2 3