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
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 rake, ruby | no comments
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
perl = '/usr/bin/perl'
awstats = '/usr/local/www/awstats'
statscmd = "#{perl} #{awstats}/tools/awstats_buildstaticpages.pl"
statsdir = '/home/pergesu/www/stats'
configs = Dir["#{awstats}/cgi-bin/awstats.*.{com,net,org}.conf"]
siteregexp = /awstats\.(\S+)\.conf$/
configs.each do |conf|
res = siteregexp.match(conf)
site = res[1]
Dir.mkdir("#{statsdir}/#{site}") unless File.exists?("#{statsdir}/#{site}") and File.directory?("#{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 awstats, ruby | 1 comment
Posted by Pat
Mon, 05 Dec 2005 08:58:00 GMT
In a previous article, we installed the ruby-postgres adapter using the ports system. I’ve spoken with a few people, and they’ve suggested using the postgres gem instead.
Unfortunately you can’t just do a “gem install postgres” to install it, you have to go through a few extra steps in order to get it to build cleanly on FreeBSD. Fortunately, I’ve done it and have all the steps here :)
You’ll get a bunch of errors and it’ll say it can’t create the Makefile. Don’t worry about that. We have to manually configure and install the gem.
For whatever reason, the Postgres installation doesn’t put the libpq-fe.h file in its include dir, so we need to symlink it. Then we’ll configure and install the gem.
# ln -s /usr/local/include/libpq-fe.h /usr/local/include/postgresql/server/
# cd /usr/local/lib/ruby/gems/1.8/gems/postgres-0.7.1/
# ruby extconf.rb --with-pgsql-include-dir=/usr/local/include/postgresql/server --with-pgsql-lib-dir=/usr/local/lib/postgresql
# make install
Should be all good to go. You’ll want to remove the ruby18-postgres package if you have it installed.
Posted in FreeBSD, PostgreSQL | Tags freebsd, postgresql, ruby | no comments
Posted by Pat
Thu, 17 Nov 2005 08:48:00 GMT
If you’re not using script/console, you really need to be. For those who don’t know what it is, it’s basically irb with the Rails environment loaded in. In fact, that might be precisely what it is, though I’m not sure.
Why is it so cool? Well you can access your entire Rails application from the command-line, which is cool if you’re a geek. A very nice use is to access your model and make database updates through there, rather than messing with your database directly. I remember a while ago I spent about an hour debugging some problem because I had used the psql client to delete some records…had I use script/console, my application logic would have taken care of it for me.
What I did that showed me how cool it is
I upgraded Typo to the current version. I found that they’ve got a sweet tag, <typo:code>, which you wrap around a code block and it formats it nicely. Adds scroll bars so that it doesn’t break your layout, puts a nice blue background, and you can probably specify more in CSS if you’re good with that.
In my previous posts, I was using the Textile @ to specify code formatting, and sometimes using <code> blocks. They didn’t look nearly as good as Typo’s cool <typo:code> tag, so I wanted to convert all my posts to use them. I barfed at the thought of having to go through all my posts and manually edit them…so I wondered if I could use script/console to load up all the posts and make the changes I wanted. I looked at a few and figured out all the cases that I had code blocks in, and wrote a little function to do all the substitutions. Then I loaded up script/console and made it happen.
def fix_code(article)
article.body = article.body.gsub("<br/>\r\n@", "\r\n<typo:code>")
article.body = article.body.gsub("@\r\n", "</ typo:code>")
article.body = article.body.gsub("</ typo:code><typo:code>", "\r\n")
article.body = article.body.gsub("<code><pre>", "<typo:code>")
article.body = article.body.gsub("</pre></code>", "</ typo:code>")
article.body = article.body.gsub("<pre><code>", "<typo:code>")
article.body = article.body.gsub("</code></pre>", "</ typo:code>")
article.save
end
articles = Content.find_all_by_type("Article")
articles.each { |a| fix_code(a) }
Unfortunately the </typo:code> in the code I posted above messes things up, so I made it ”< /typo:code>”. Anyway, there’s obviously not supposed to be a space.
Using script/console saved me a lot of time, and turned a boring task into a pretty fun challenge/learning experience. I suggest you start using it.
Posted in Rails | Tags console, rails, ruby | 2 comments
