Topic: Building a Glossary from a Legacy DB

I have to rebuild a simple glossary for a client and am not sure of the cleanest way to do it.

Right now the glossary is built in Textpattern, a PHP CMS which we have outgrown. For the record, Textpattern has quite a few columns in its article table, but all I need are the Title, Body & Section fields to be mapped to :title, :body and :section_id

I know for sure I will need a sections table, model, controller and views. I am also positive that each section has_many :articles and that my articles table will need a section_id column.

The biggest problem here is that textpattern stores the actual name of the section with each article instead of its numerical ID. WIth such a huge inconsistency, I dont know how I can get around assigning the section ids of all 4,000 articles by hand (argh!)

For the sake of positive thinking, lets assume that this is stupidly easy to fix. Once I have imported this legacy data I am then faced with the task of how to structure these sections in Rails, with clean URLs that look like this:

www.mysiteglossary.com/a/apple

If I am using just one table and mvc for sections, how do I set up my routes.rb file so that certain sections get mapped to certain URLs?

Re: Building a Glossary from a Legacy DB

Sometimes Im glad when nobody answers me here.. it shows that I need to do my homeowrk more.

I found the answer to my routing question here:

http://routes.groovie.org/manual.html#setting-up-routes

Still hunting down resources on legacy data, but at least this is one less thing to worry about

Re: Building a Glossary from a Legacy DB

Some inspiration just struck me and I realize I am going about this the wrong way. Instead of having to manually indicate letters when filling out an entry, why not have Rails figure that out for me?

Essentially I just need to have all legacy entries marked with section "A-Z" and "0-1" imported to a glossary table. Then in my glossary model I can define this find method.

class Glossary < ActiveRecord::Base
  def self.first_letter(letter)
    find(:all, :conditions => ['title LIKE ?', "#{letter}%"], :order => 'title ASC')
  end
end
# Feel free to provide a cleaner alternative

Then in my controller...

class GlossaryController < ApplicationController
  def a
    @a = Glossary.first_letter(a) 
  end
  def b
    @b = Glossary.first_letter(b) 
  end
end

# etc etc


Where I get confused is how to pass a range of values as an argument. One of the glossary sections is just numbers 0-9, so should I pass a * into the original argument?

Also, from a maintenance standpoint, am I going in the right direction? You folks are always talking about stinky code, and something about having 26 actions in one controller doesn't smell too good. Perhaps I can abstract this even further, but I'm not sure how.

Last edited by pimpmaster (2007-03-28 09:48:40)

Re: Building a Glossary from a Legacy DB

You don't want to be creating 26 actions. They all do roughly the same thing which is a clue that it should be in one action. Instead you should create a "letter" method or something similar:

class GlossaryController < ApplicationController
  def letter
    @items = Glossary.first_letter(params[:id])
  end
end

Okay, using params[:id] for the letter is a bit of a hack. If you want to clean things up you can create a custom route.

Now as for the first_letter method. You can make this method much more powerful by doing this:

class Glossary < ActiveRecord::Base
  def self.first_letter(letter)
    find(:all, :conditions => ['LEFT(title, 1) IN (?)', letter], :order => 'title ASC')
  end
end

The LEFT sql method will return the first character. Using the "IN" command allows you to do something really cool: allow ranges. Which means you can create an action to handle the number range in the controller. For example:

# in glossary controller
def numbers
  @items = Glossary.first_letter(0..9)
end

Untested.

Hope that helps.

Railscasts - Free Ruby on Rails Screencasts

Re: Building a Glossary from a Legacy DB

Wow, I wasn't even close it seems. Occasions like this make me realize that I need to get my SQL skills up to par.

Will test this code out next week after the hangover from my drinking binge wears off wink

Cheers ryan!

Re: Building a Glossary from a Legacy DB

Ok so Im still a bit in the dark on how I can get my code to jive with that params hack of yours. I also dont see the point in defining custom routes if I can get those routes automatically by defining the 26 actions. Seems to be the same amount of code, though probably not the best practice.

What do you think? If i did use the params, I still dont understand the following:

1. How I can pass letters to that in my routes
2. How to link to letters in my views
3. How to index for each letter

Re: Building a Glossary from a Legacy DB

I think I have nailed it: 

First, my custom route:

map.connect '/:action/:id', :controller => 'glossaries'

My shiny new controller:

  def a
    letter('a')
  end

  def b
    letter('b')
  end

  ## etc   
           
private

def letter(arg)
  @entries = Glossary.first_letter(arg)
  render :action => "index"
end


Now at first I was concerned that this might not be so DRY, but then I remember what you were saying about real vs fake duplication. Since the behavior of each letter can easily be changed from one private action, I dont see the harm in having these 27 simple actions just to take care of my routes.

As always I am open to refactoring suggestions as I find them highly educational. But for now, this is working splendidly.

Re: Building a Glossary from a Legacy DB

I stand corrected. Everything works fine except the numbers section. For some reason, using your method calls up all the rows in that table. Weirdness

Edit: Even weirder, if I change my number range from (0..9) to (1..9) it works without a hitch. Does Ruby think that A-Z falls in between 0 and 1?

Anyways, none of my entries start with zero anyway so Im not bothered. I have a whole different problem now.. entries that begin with a period. I tried getting clever on my query:

def self.numbers
find(:all, :conditions => ['LEFT(title, 1) IN (?)', ['.'] + (1..9).to_a], :order => 'title ASC')
end

but this is still returning everything in my table. Totally stumped!

Last edited by pimpmaster (2007-04-01 14:03:47)

Re: Building a Glossary from a Legacy DB

pimpmaster wrote:

Now at first I was concerned that this might not be so DRY, but then I remember what you were saying about real vs fake duplication. Since the behavior of each letter can easily be changed from one private action, I dont see the harm in having these 27 simple actions just to take care of my routes.

Nah, this isn't good duplication. True, it could be worse since at least you are calling a private letter method. But the question is: why? There is no benefit to separating each letter out into a separate action. This should be done in the routes.

I don't understand how you want the routes to behave so it's hard to give a recommendation. What is the 2nd parameter, the ":id" in your case, used for? Here's how I would do it. Add these two routes to the top of your routes file:

map.connect 'glossary/numbers', :controller => 'glossary', :action => 'numbers'
map.connect 'glossary/:letter', :controller => 'glossary', :action => 'letter'

This way the URLs will look like this:

/glossary/a
/glossary/b
/glossary/numbers

Then it will get redirected to the letter/numbers action appropriately.

If it gets redirected to the letter action, it will pass the letter as params[:letter] so that's what you would use:

def letter
  @entries = Glossary.first_letter(params[:letter])
  render :action => "index"
end

As for the numbers, what SQL query is being sent? It should be in the development log.

Railscasts - Free Ruby on Rails Screencasts

Re: Building a Glossary from a Legacy DB

Here is the deal with numbers. As you can see, I tried to setup a custom query to account for entries starting with periods. At first I was returning all my entries but after tinkering a bit, now I am returning zero

#model
def self.numbers
  find(:all, :conditions => ['LEFT(title, 1) IN (?)', ['.'] + (0..9).to_a], :order => 'title ASC')
end

#controller
def numbers
   @entries = Glossary.numbers
   render :action => "index"
end

#routes
map.resources :glossaries
map.connect '/:letter', :controller => 'glossaries', :action => 'letter'
map.connect '/:letter/:id', :controller => 'glossaries', :action => 'show'
map.connect '/numbers', :controller => 'glossaries', :action => 'numbers'

# Query results from log
Parameters: {"action"=>"letter", "letter"=>"numbers", "controller"=>"glossaries"}
  Glossary Load (0.018016)   SELECT * FROM glossaries WHERE (LEFT(title, 1) IN ('numbers')) ORDER BY title ASC


As for my URL routes, I am trying to set it up very specifically since many articles are already linked in a certain way..

www.siteroot.com/a
www.siteroot.com/a/a-random-entry
www.siteroot.com/numbers
www.siteroot.com/numbers/10-Base-T


So far my /:letter route works perfectly.

The /number route seems to be pointing to my letter action for some weird reason and
my route for the show action is being stubborn and always defaults to /glossaries/a-random-entry

There are some fundamental concepts of routing that I am not grasping it seems.

Last edited by pimpmaster (2007-04-02 09:48:08)

Re: Building a Glossary from a Legacy DB

Your number route should be able the letter route in your routes.rb. As it will match them in order, and it will have already matched numbers to your first (:letter) route.

Re: Building a Glossary from a Legacy DB

One thing I forgot to mention is that I am using RESTful resources here and am starting to wonder if its really a good idea.

Im gonna try this the standard way and see what happens

Re: Building a Glossary from a Legacy DB

Yup, this was exactly my problem. Removing the REST from my routes and views has all my actions triggering where they should be. All is 90% perfect. Except now I am getting the same problem as before. My numbers method is returning all my entries for some reason

 Glossary Load (0.107206)   SELECT * FROM glossaries WHERE (LEFT(title, 1) IN ('.',0,1,2,3,4,5,6,7,8,9)) ORDER BY title ASC

The query looks fine to my untrained eyes. No idea what could be wrong.

Re: Building a Glossary from a Legacy DB

Interesting. Try accessing the database from the command line and running that query directly. Do you still get all results? What happens if you quote the numbers?

Railscasts - Free Ruby on Rails Screencasts

Re: Building a Glossary from a Legacy DB

Indeed it was the quotes. I had to modify my model's find method and its not as purty, but it works flawlessly now.

find(:all, :conditions => ['LEFT(title, 1) IN (?)', ['.','0','1','2','3','4','5','6','7','8','9']], :order => 'title ASC')

As usual, you are the man

Re: Building a Glossary from a Legacy DB

you can also do this:

['.'] + (0..9).map(:to_s)

Railscasts - Free Ruby on Rails Screencasts

Re: Building a Glossary from a Legacy DB

There is one minor pitfall of using params to set the find method. if people enter in some random url text that is not A-Z or the string 'numbers' I get a blank page with no query results. Gonna investigate how I can make this more airtight

Re: Building a Glossary from a Legacy DB

You can do a "requirement" option in your routes file:

map.connect '/:letter', :controller => 'glossaries', :action => 'letter', :requirements => { :letter => /[a-z]/ }

Railscasts - Free Ruby on Rails Screencasts

Re: Building a Glossary from a Legacy DB

Wow..Im so close I can taste it!

The only thing left is to gracefully handle these routing exceptions, which I am guessing is outside the realm of my routes.rb file. This looks like a job for my controller for sure. Since all my glossary actions render through my index action, that seems like the obvious place to catch exceptions...

def index
  rescue ActiveRecord::RecordNotFound => e
    flash[:warning] = "Quit fiddling with the URLs, freak."
    redirect_to '/glossary'
  respond_to do |format|
    format.html # index.rhtml
    format.xml  { render :xml => @glossaries.to_xml }
  end   
end

But this is really doing nothing for me. All I see is the standard error page

Routing Error

no route found to match "/asshead" with {:method=>:get}

Which raises another question. My logs show this as a 404, so I assume I will only see this message in development mode and production should reroute to my 404 page. Correct?

Last edited by pimpmaster (2007-04-03 08:34:17)

Re: Building a Glossary from a Legacy DB

Setting a requirement in the routes doesn't even call the action, it just moves onto the next route if it doesn't meet the requirements. If no route is found it raises that "no route" error. To me this makes more sense, but if you want to provide a different error than 404 then you can remove the requirements from the routes and do the check in the controller:

if params[:letter] =~ /^[a-z]$/i
  # good letter
else
  # bad letter
end

Untested.

pimpmaster wrote:

Which raises another question. My logs show this as a 404, so I assume I will only see this message in development mode and production should reroute to my 404 page. Correct?

Yep.

Railscasts - Free Ruby on Rails Screencasts