Sunday, March 15, 2009

Implementing REGEXP in Sqlite3

I am implementing a rails application that required searching using a regular expression. I am using MySQL for the production database, but SQLite3 for the test database. Now SQLite3 supports the REGEXP SQL operator, however it is left to the user to implement the function. I found a post by Rolando Abarca where he attempted to implement regexp in Ruby. I adapted that and also created a rails initializer (config/initializers/sqlite3_regexp.rb):

require 'active_record/connection_adapters/sqlite3_adapter'

class ActiveRecord::ConnectionAdapters::SQLite3Adapter
def initialize(db, logger)
db.create_function('regexp', 2, :numeric) do |func, pattern, expression|
regexp =, Regexp::IGNORECASE)
if expression.to_s.match(regexp)
func.result = 1
func.result = 0

This allowed me to implement a named_scope to search for a set of comma (or semicolon) separated names:

class Subject < ActiveRecord::Base
named_scope :with_names, lambda {|names|
names = names.strip.split(/ *[,;] */).join('|')
if !names.blank?
{:conditions => ["name regexp ?", names]}

Which could then be called like:

subjects = Subject.with_names("math, english")

See this post on my site Veit Consulting.

Sunday, May 25, 2008

Scaffold Resource Matrix

The scaffold resource matrix generator creates a model, a controller,
and a set of templates that are ready to use as the starting point for
your REST-like, resource-oriented application. This basically means
that it follows a set of conventions to exploit the full set of HTTP
verbs (GET/POST/PUT/DELETE) and is prepared for multi-client access
(like one view for HTML, one for an XML API, one for ATOM,
etc). Everything comes with sample unit, functional and matrix tests
as well.

The resource may optionally be authenticated,
in which case all controller actions require a user to be logged
in. Additional tests are added to the functional matrix test to test
for this.

The generator takes the name of the model as its first argument. This
model name is then pluralized to get the controller name. So
"scaffold_resource_matrix book" will generate a Book model and a
BooksController and will be intended for URLs like /books and

As additional parameters, the generator will take attribute pairs
described by name and type. These attributes will be used to
prepopulate the migration to create the table for the model and to
give you a set of templates for the view. For example,

scaffold_resource_matrix book title:string created_on:date body:text published:boolean
will give you a model with those four attributes,
forms to create and edit those models from, and an index that'll list
them all.

You don't have to think up all attributes up front, but it's a good
idea of adding just the baseline of what's needed to start really
working with the resource.

Once the generator has run, you'll need to add a declaration to your
config/routes.rb file to hook up the rules that'll point URLs to this
new resource. If you create a resource like "scaffold_resource_matrix
book", you'll need to add "map.resources :books" (notice the plural
form) in the routes file. Then your new resource is accessible from

Note: This generator is an enhancement of the scaffold_resource
generator that comes with Rails.


./script/generate scaffold_resource_matrix book # no attributes, view will be anemic
./script/generate scaffold_resource_matrix book title:string body:text published:boolean
./script/generate scaffold_resource_matrix book title:string body:text published:boolean --authenticated


./script/plugin install git://


gem install ZenTest

./script/plugin install restful_authentication
./script/generate authenticated user sessions

Additional Information

See Dr. Nic's post on Functional Testing using a

See Ryan Davis' talk at RejectConf2007 on functional matrix test

See wiki entry on restful_authentication

See this post on my site Veit Consulting.

Monday, April 21, 2008

Converting Subversion Rails Project to Github

I signed up for a Github account. Github is a hosted git repository. Git is a distributed version control system focused on speed, effectivity and real-world usability on large projects. There are a couple of things I want to do with this. I want to use it as a repository for my rails apps. I also want to use it to host Rails plugins that I plan to release. Ruby on Rails has moved their repo to Github.

Before using Github, I installed Git on my PowerBook. I used the installer from Git for OS X.

I signed up for the Micro plan at $7 per month since I needed private repos. When signing up you need to provide an SSH public key. I use Linux and OS X. My public key is located in ~/.ssh/ If you don't have a public key use the following command to create one:

ssh-keygen -trsa

Enter the contents of the key file in the "SSH Public Key" box. Since I use more than one computer, I added my other SSH keys. I did this from the account page in GitHub. I also went to the profile page and added information about myself.

I created a new repository by clicking on Create a Repository.

I then imported my subversion repo using the instructions from Jon Maddox.

Set up my user translations file:

echo 'sveit = Stephen Veit ' > ~/Desktop/users.txt

mkdir ~/tess_tmp
cd ~/tess_tmp
git-svn init $SVN/tess/trunk/ --no-metadata
git config svn.authorsfile ~/Desktop/users.txt
git-svn fetch
cd ..
git clone tess_tmp tess
cd tess

Edit .git/config. Change the [remote "origin"] section to

[remote "origin"]
url =
fetch = +refs/heads/*:refs/remotes/origin/*
push = refs/heads/master:refs/heads/master

Then push the repo to github:

git push origin

To check out the project into a new directory:

git clone tess

More information on git can be found from the home page:

"See the tutorial to get started, then see Everyday Git for a useful minimum set of commands."

See this post on my site Veit Consulting.