Read my latest article: 8 things I look for in a Ruby on Rails app (posted Thu, 06 Jul 2017 16:59:00 GMT)

Using Named Placeholders in Ruby

Posted by Fri, 21 Oct 2005 04:53:00 GMT

2 comments Latest by Sokolov Yura Sun, 27 Aug 2006 07:34:26 GMT

Insert Hip Quote Here:

“In ancient times, hundreds of years before the dawn of history, an ancient race of people… the Druids. No one knows who they were or what they were doing… “ – Nigel Tufnel, Spinal Tap

Story Time…

Earlier, I was giving a customer of ours, Jared from CommunityWalk.com a quick tutorial on some of the features script/console… which lead to helping him with a SQL query. When I provided him with some working code he was curious about what I had done in the SQL query string that I was passing to find_by_sql.

WARNING

If you have ANY SQL queries that resembles the following, PLEASE READ THE REST OF THIS. :-)

values = params[:search]
RockLegend.find( :all, :conditions => "first_name = '#{values['first_name']}'" )

If you are doing that… then you are opening yourself up to some security problems. Let’s take a few minutes and discuss how you can make this more secure and still keep your code readable. (the best of both worlds!)

The ? Placeholder

Many of you are probably familiar with this approach…
RockLegend.find( :all, :conditions => ['first_name = ?', 'Nigel'] )

RockLegend.find( :all, :conditions => ['first_name = ? AND last_name = ?', 'Nigel', 'Tufnel'] )

You can pass it a hash as well.. and as long as you put everything in the same order as the ?s are placed… then all is well.

My only real problem with this approach is that it requires you to keep things in a specific sequential order… and who wants to keep track of that? So, I would like to recommend that you use named placeholders. Aside from that, it looks magical and I don’t like magical-looking code. I like easy to read code. :-)

Named Placeholders

If you already use these… you know how useful they can be in your SQL queries. If you haven’t seen them before… it’s because the Rails docs don’t really mention it and is something that comes from the underlying database library in Ruby. So what is so great about these?

Let’s first replace the above code with named parameters…
RockLegend.find( :all, :conditions => ['first_name = :first_name', { :first_name => 'Nigel' } ] )

We are passing a hash with a matching key to the conditions option. Pretty neat, right? In this case with just one placeholder we just increased the amount of code to do the same thing. So, it might always be the best solution… but it is easier to read.

Let’s try another with multiple keys in our hash… infact, we’ll build the hash prior to calling find.

values = { :first_name => 'Nigel', :last_name => 'Tufnel'}
RockLegend.find( :all, :conditions => ['first_name = :first_name AND last_name = :last_name', values ] )

It will happily match the hash keys to the named placeholders in the conditions string. Again, nothing terribly exciting…but it is easier to read.

Who cares about order? Not named placeholders!

Okay, let’s mix things up a bit…

values = { :last_name => 'Tufnel', :first_name => 'Nigel' }
RockLegend.find( :all, :conditions => ['first_name = :first_name AND last_name = :last_name', values ] )

The hash keys were not added in the same order… but it still works!

Okay, now for one last quick example (it’s late and I am tired…).

I have a search mechanism on a site that allows you to search for a string of text across multiple fields. So, I have one string… but several fields to compare against.

Here is a string that I will pass to the find method.
conditions = "role = :role AND (first_name ~* :str OR last_name ~* :str OR nick_name ~* :str)"

Note: this string is using PostgreSQL regular expressions... (~*).

Here is a hash that with that matches the keys, :str and :role

values = { :str => '^(Nigel|Tufnel)$', :role => 'Guitar' }

If you look above, you’ll see that the conditions string contains four named placeholders… but the hash only has two keys. With the ? placeholder, we would have to pass the same vaule three times… which isn’t any fun to read or maintain. ;-/

So, with our new friends, named parameters, we can call find (or any find-like method) using this technique for placeholders.

RockLegend.find( :all, :conditions => [ conditions, values ] )

...and hopefully this is useful to you. :-)

Have fun!

A PostgreSQL tip

Posted by Wed, 05 Oct 2005 05:07:00 GMT

I promised that I would send a link out or something… so here goes.

Tonight at the Portland Ruby Brigade meeting, the question about having Active Record READ from one database and WRITE to another database was brought up. The discussion was going in the route of, “How can we make AR do this?”

I chimed in with my usual PostgreSQL bias. “pgpool.”

Let something else handle this. Infact, with PgPool, you don’t have to change any code in your Rails application to make this work. :-)

So, take a look at: pgpool

Enjoy!

Portland Ruby Brigade meets CD Baby

Posted by Wed, 05 Oct 2005 04:25:00 GMT

This is what happened to the Portland Ruby Brigade today:
  • Got double booked on meeting night with the PHP group
  • Got asked to leave after getting threats of the lights being turned off
  • Stayed almost til closing time at a brew… we heard, “Last Call!” and still continued talking…

Tonight I finally got to meet Ben, Caleb, Justin Watkins, Marcus Estes, Lennon Day-Reynolds, Jeremy Kempter (bitsweat), Aaron Johnson, and Peat Bakke-each for the first time. I also got to see Scott Laird (of Typo fame) and John Labovitz again. Scott and I met at FOSCON and John once found me when he was trying to hack people at a coffee shop and saw that I was on the wireless network. Okay, he wasn’t hacking… but found me at Urban Grind. :)

We had about ~25 people show up. I don’t think that there was a head count made. We sat around in a big circle and fired questions at both Derek Sivers and Jeremy Kemper. Topics ranged from how Derek found his way to PostgreSQL to why he thinks that he is an amazing web designer (smirk) to why Ruby and Rails was picked as the platform to move to from a PHP/MySQL system. Jeremy shared some insights into some problems with Rails that need to be worked on (boolean-stuff for example), tips on debugging, and many other things that I don’t recall off the top of my head.

It was a very relaxed and open conversation with some cool local developers who are working on getting their Rails/PostgreSQL system finished.

After we got kicked out of FreeGeek most of migrated to the Lucky Lab Pub… and then we realized that Derek and Jeremy didn’t show up. So, we found our ways to a big table and broke up into smaller conversations. I got stuck at the table with Ben, Scott, Caleb, and John. Actually, it was quite enjoyable. Scott talked about his work with Typo and Ben and I gave him some of our thoughts on things.

This is a horrible summary of the whole evening. It’s what I remember… and it’s late and I look forward to getting to hang out with this gang again in the future. I think that it was great to get to finally meet a few of the people that I have worked on some smaller projects with. If everything works out well, expect there to be some more projects coming out of the PDX.rb in the coming months. :-)

Thanks again to Derek Sivers and Jeremy Kemper for taking time out of their busy schedule to meet & greet with the Portland Ruby people. :-)

The bitter-sweet taste of agnostic database schemas

Posted by Tue, 27 Sep 2005 12:41:00 GMT

If you know me at all by now… you might know that I am a huge fan of PostgreSQL. Coming to the Rails camp was a bit of an eye-opener./ Working with PostgreSQL used to save me quite a bit of work and let me sleep at night. I have dealt with the problems that many developers face while working with MySQL. Like many of us in the Open Source world, I was exposed to MySQL more often than PostgreSQL. For several years, I convinced myself that MySQL was awesome because it was fast… but speed isn’t always the most important factor in the technology world. Otherwise, we’d all be coding in C. What PostgreSQL provided to me as a developer was better relational integrity. Foo cannot exist unless Bar does, or vice versa. ON DELETE CASCADE was a godsend and saved me quite a bit of work when building an application. Triggers… Procedural Languages… oh so beautiful.

PostgreSQL was (and is) the Open Source database server that both the Enterprise DBA and Developer could agree on.

Enter Ruby on Rails

At first, I just ignored my instincts and jumped in head first. Pluralization? Primary keys named id? Blasphemy! Yet, I moved forward. After a few days of consistently reminding myself of these new-fangled conventions (I even printed out a little cheat sheet and taped it to my monitor)... I was hooked. Rails worked with PostgreSQL. Let me say that again.

RAILS WORKS WITH POSTGRESQL!

Don’t freak out. Yes, the person who started the Rails project is a MySQL fan... but Rails is database agnostic. It’s true.

I wanted to write an entry to clear up a few myths about Rails.

  • Table names are not forced
  • Primary Key field names are not forced
  • Foriegn Key field names are not forced
  • Legacy databases can work with Rails
  • Active Record (like much of Rails) can be customized for your particular application’s needs

I was, and still am, concerned about trusting Active Record with handling my relational data… because in some cases it wasn’t doing what it should. But, that is being worked on… and because Rails is Open Source, the issue is transparent for us developers to see and help fix.

In the past, you might have found me advocating the addition of business logic in your database server, whenever possible. It is the gate keeper. The key master. It is not meant to be flexible with your data. If business rules are to change, you change them there so that if you have one or more INPUT streams into your database, the data will not get into your server unless your business rules are met. PostgreSQL even provides you with a nice ERROR message, which some other servers do not. If you can control everything through one INPUT stream (Application Database), then putting these constraints solely in your database abstraction layer is a-ok. However, if you are interfacing with your server through a variety of avenues (Integration Database), you probably already know that your system is going to be a pain to maintain in the long run. So, what are you to do?

Handling Legacy systems is going to be a headache, regardless of what you do… but you can’t always rebuild the whole thing and migrate your data. Add that to your Someday-Maybe list.

When you’re adding new pieces to your application, start by using good tools, frameworks, and practices. For example, try your best to follow the Application Database path to pragmatic enlightenment. Begin working on a new layer for your legacy database. Add on web services that use this layer. Refactor your existing applications. Can they use these new services? ReThink everything. That is… if you have the time and resources to do so.

If you are questioning whether or not to put your business logic in your database abstraction layer, stop. Just do it™. But, do it with caution. Test it. Test it well. I’ll admit that I still add some constraints into my database schemas-I am not ready to give up that extra layer of data security. But, that’s just me. :)

PostgreSQL works with Rails!

...and I promise to show you how to do some fun legacy stuff with Rails in my book. :-)

Hurricane shelter uses Rails and PostgreSQL, thanks open source

Posted by Sat, 24 Sep 2005 11:45:00 GMT

I came across this weblog entry on a website that is offering shelter for victims of Hurricane Katrina.

The author writes:
How do you measure the ROI on a project like this? How do I get PostgreSQL or Ruby-on-Rails credit for reuniting a twelve year-old girl with her father who had been missing for three weeks or any of the other amazing stories we heard everyday at Windsor?

It’s great to hear that Open Source is used for situations like this. The money that would have been spent on commercial applications and support would be better spent on the people that they are trying to help.

I tried to figure out exactly which page might be using Rails (as they are using Gallery, WordPress, etc for a few other pieces of their system). I found this, which looks like it might be based in Rails.

Disclaimer I couldn’t find too much information about this, just their blog and a few links from it. If you find more information, please comment and I’ll update this entry. :-)

UPDATE

I got an email from Rudi Cilibrasi from Katrina Help. They are using Ruby on Rails for their people search.

CDBaby Q&A at next Portland Ruby Brigade meeting

Posted by Fri, 23 Sep 2005 19:06:00 GMT

I just posted on the Portland Ruby Brigade blog that Derek Sivers and Jeremy Kemper (bitsweat) will be coming to the next PDX.rb user group meeting. Derek and Jeremy will field questions from all of us in regards to their complete rewrite of CD Baby from PHP+MySQL to Ruby+Rails+PostgreSQL. (my favorite open-source projects!)

This will take place at FreeGeek in Portland, Oregon on October 4th, 2005!

For more information, see the Portland Ruby Brigade website

Older posts: 1 ... 3 4 5 6