Database

Reading RSS in Perl like a DB

My website currently uses RSS feeds as data sources, and I’m trying to replace some custom parsing code with standard CPAN modules. The DBD::AnyData module seems almost perfect, with the exception that I just spent a few hours banging my head on it trying to get it to read RSS properly. Despite trying many different combinations, it kept reading the channel title instead of the item title.

Read More

“Grant all” in Postgres

The security model in postgres is very granular, and is locked down by default. this is great for production, but is kind of a pain as a developer working locally. Coming from MySQL, it’s natural to try “GRANT ALL ON * TO some_user;” for development. Unfortunately, that just doesn’t work. From a fair bit of googling, the general advice is “just write a script to do that”, which is very unhelpful (You’re trying to write a DB app, not shell scripts).

Read More

Website Aggregation without a DB

Laziness is the prime virtue of a good programmer (along with being a polyglot, knowing the ins and outs of their editor, and knowing how to google APIs), but it is often forgotten when working on a personal project. My website has been no exception - I’ve actually spent far too much time writing my own session/users/form/template systems over the years.

Read More

Zero-Padding a value in T-SQL

I keep running into limitations in T-SQL as used in MS SQL Server. Apparently part of MySQL’s popularity is that the have added useful extensions to the language that actually solve common problems. (Okay, that was a bit too sarcastic. Still though)

Read More

SQL to Alter Every Third Row

This was an interesting problem posed to me by a friend. How does one go about altering every third row in a table? As an entirely contrived example, assume the table has exactly 2 columns, name and value, of types varchar(255) and int respectively. Assume that the rows are named like so, probably due to automatic entry or the involvement of a MSCE. Our table might look like this:

Read More

Selecting Overlapping Ranges in SQL

It comes up occasionally that you are storing some events in a DB with some duration, and you want to find all of those events that overlap a given date range. (At work, I needed to find out who was on vacation during a given period; it comes up more often than you’d think). It turns out that it’s a fairly simple SQL query to accomplish this. First, assume your DB table looks like:

Read More