Our Blog

Defining variables for rpmbuild

By Jon Jensen
August 20, 2009

RPM spec files offer a way to define and test build variables with a directive like this:

%define <variable> <value>

Sometimes it’s useful to override such variables temporarily for a single build, without modifying the spec file, which would make the changed variable appear in the output source RPM. For some reason, how to do this has been hard for me to find in the docs and hard for me to remember, despite its simplicity.

Here’s how. For example, to override the standard _prefix variable with value /usr/local:

rpmbuild -ba SPECS/$package.spec --define '_prefix /usr/local'

Text sequences

By Greg Sabino Mullane
August 20, 2009

Somebody recently asked on the Postgres mailing list about “Generating random unique alphanumeric IDs”. While there were some interesting solutions given, from a simple Pl/pgsql function to using mathematical transformations, I’d like to lay out a simple and powerful solution using Pl/PerlU

First, to paraphrase the original request, the poster needed a table to have a text column be its primary key, and to have a five-character alphanumeric string used as that key. Let’s knock out a quick function using Pl/PerlU that solves the generation part of the question:

DROP FUNCTION IF EXISTS nextvalalpha(TEXT);
CREATE FUNCTION nextvalalpha(TEXT)
RETURNS TEXT
LANGUAGE plperlu
AS $_$
  use strict;
  my $numchars = 5;
  my @chars = split // => qw/abcdefghijkmnpqrstwxyzABCDEFGHJKLMNPQRSTWXYZ23456789/;
  my $value = join '' => @chars[map{rand @chars}(1..$numchars)];
  return $value;
$_$;

Pretty simple: it simply pulls a number of random characters from a string (with some commonly confused letters and number removed) and returns a string:

greg=# SELECT nextvalalpha('foo');
 nextvalalpha
--------------
 MChNf
(1 row)

greg=# SELECT nextvalalpha('foo'); …

Two quick tips: egrep & SQL dumps, Vim and deleting things that don’t match

By Selena Deckelmann
August 20, 2009

Sometimes, I just don’t want to restore a full SQL dump. The restore might take too long, and maybe I just want a small subset of the records anyway.

I was in exactly this situation the other day—​faced with a 10+ hour restore process, it was way faster to grep out the records and then push them into the production databases, than to restore five different versions.

So! egrep and vim to the rescue!

In my case, the SQL dump was full of COPY commands, and I had a username that was used as a partial-key on all the tables I was interested in. So:

egrep “((^COPY)|username)” PostgresDump.sql > username.out

I get a pretty nice result from this. But, there are some records I’m not so interested in that got mixed in, so I opened the output file in vim and turned line numbers on (:set numbers).

The first thing that I do is insert the ‘.’ needed to tell Postgres that we’re at the end of a COPY statement.

:2,$s/^COPY/\.^V^MCOPY/

The ‘^V^M’ is a control sequence that results in a ‘^M’ (a newline character, essentially). And the ‘2’ starts the substitution command on the second line rather than the first COPY statement (which, in my case, was on the first line).

Next, I want …

lessons = colors.find_all {|c| c.kind_of? Blue}

By Ethan Rowe
August 19, 2009

As noted in this nice article at Slate.com, the much-loved “Kind of Blue” celebrated its 50th anniversary recently. In the article, Fred Kaplan asks, and attempts to answer, “what makes this album so great?”

As somebody who has made a point of introducing his daughter (at infancy) to Miles Davis, who succumbed to the allure of jazz thanks in no small part to this very album, I would be remiss in my duties as a Caring Human not to blog about it. And yet this is a corporate blog for a consulting company that does lots of stuff with free software (as demonstrated so effectively here). What to do?

Fortunately, there’s something we in the software world can learn from this album and the lessons Mr. Kaplan derives from it. Let’s look at one key paragraph:

So Kind of Blue sounded different from the jazz that came before it. But what made it so great? The answer here is simple: the musicians. Throughout his career, certainly through the 1950s and ’60s, Miles Davis was an instinctively brilliant recruiter; a large percentage of his sidemen went on to be great leaders, and these sidemen—especially Evans, Coltrane, and Adderley—were among his greatest. They came to the date, were handed …

Debugging prepared statements

By Greg Sabino Mullane
August 17, 2009

I was recently tasked with the all-too-familiar task for DBAs of “why is this script running so slow?”. After figuring out exactly which script and where it was running from, I narrowed down the large number of SQL commands it was issuing to one particularly slow one, that looked something like this in the pg_stat_activity view:

current_query 
-------------
SELECT DISTINCT id
FROM containers
WHERE code LIKE $1

Although the query ran too quick to really measure a finite time just by watching pg_stat_activity, it did show up quite often. So it was likely slow and being called many times in a loop somewhere. The use of ‘LIKE’ always throws a yellow flag, so those factors encouraged me look closer into the query.

While the table in question did have an index on the ‘code’ column, it was not being used. This is because LIKE (on non-C locale databases) cannot work against normal indexes—​it needs a simpler character by character index. In Postgres, you can achieve this by using some of the built in operator classes when creating an index. More details can be found at the documentation on operator classes. What I ended up doing was using text_pattern_ops:

SET maintenance_work_mem = …

Site Search on Rails

By Steph Skardal
August 14, 2009

I was recently tasked with implementing site search using a commercially available site search application for one of our clients (Gear.com). The basic implementation requires that a SOAP request be made and the XML data returned be parsed for display. The SOAP request contains basic search information, and additional information such as product pagination and sort by parameters. During the implementation in a Rails application, I applied a few unique solutions worthy of a blog article. :)

The first requirement I tackled was to design the web application in a way that produced search engine friendly canonical URLs. I used Rails routing to implement a basic search:

map.connect ':id', :controller => 'basic', :action => 'search'

Any simple search path would be sent to the basic search query that performed the SOAP request followed by XML data parsing. For example, https://www.gear.com/s/climb is a search for “climb” and https://www.gear.com/s/bike for “bike”.

After the initial search, a user can refine the search by brand, merchant, category or price, or choose to sort the items, select a different page, or modify the number of items per page. I chose to …

Google Voice first impressions

By Jon Jensen
August 13, 2009

I’ve been using Google Voice on and off for about a week now, and wanted to share my first impressions.

Google Voice is still available only by invite, but I only had to wait about 3 days to get access. Signup was quick and easy, and doesn’t cost any money. The hardest part was selecting a phone number – you can choose one from most anywhere in the United States, but as far as I know you can’t change it later, at least not easily. I finally got the Montana phone number I’ve always wanted!

Simultaneous multiple call forwarding: The feature I was most aware of beforehand is having your Google Voice number forward calls to as many other numbers as you want. This is actually better than I expected, because it calls the other numbers simultaneously, not in series, so there’s very little delay to the caller compared with directly calling. Whichever phone you answer first and tell the robot lady “1”, is the one that takes the call.

Android integration (not VoIP): I was most excited to use Google Voice as a VoIP client on my Android phone, but that is a feature I apparently imagined all by myself. The Android Google Voice client still uses cell phone minutes and goes through the cell …

Perl+Postgres: changes in DBD::Pg 2.15.1

By Greg Sabino Mullane
August 12, 2009

DBD::Pg, the Perl interface to Postgres, recently released version 2.15.1. The last two weeks has seen a quick flurry of releases: 2.14.0, 2.14.1, 2.15.0, and 2.15.1. Per the usual versioning convention, the numbers on the far right (in this case the “dot one” releases) were simply bug fixes, while 2.14.0 and 2.15.0 introduced API and/or major internal changes. Some of these changes are explained below.

From the Changes file for 2.15.0:

CHANGE:
 - Allow execute_array and bind_param_array to take oddly numbered items, 
   such that DBI will make missing entries undef/null (CPAN bug #39829) [GSM]

The Perl Database Interface (DBI) has a neat feature to allow you to execute many sets of items at one time, known as execute_array. The basic format is to pass in an list of arrays, in which each array contains the placeholders needed to execute the query. For example:

## Create a simple test table with two columns
$dbh->do('DROP TABLE IF EXISTS people');
$dbh->do('CREATE TABLE people (id int, fname text)');

## Pass in all ids as a single array
my @numbers = (1,2,3);

## Pass in all names as a single array
my @names = ("Garrett", "Viktoria", …