PostgreSQL Love: deleting from views

I know that MySQL gets most of the attention in the relational-database field, as an easy-to-install, general-purpose relational database. But I've used PostgreSQL for years, and sometimes I have a moment like I did today that reminds me why.

In a quizzing application I run, I have an 'attempts' table with a lot of columns, and many of them are very wide. So, I created a view of the 'attempts' table that contains the key columns, and most of the time this view is what I use when I'm looking something up.

The problem is, you cannot delete records from a view (and rightly so). So, I often end up with a scenario like this:

# select * from attempts_brief where quiz=1479;
id    | quiz | person | score | out_of
-------+------+--------+-------+----
14981 | 1479 | admin  |    20 |     20
14982 | 1479 | admin  |       |  
(2 rows)

I want to delete those two dummy rows, so I alter the query, changing SELECT * to DELETE, as I would on any other cautious deletion attempt. But I get this:

# delete from attempts_brief where quiz=1479;
ERROR:  cannot delete from a view
HINT:  You need an unconditional ON DELETE DO INSTEAD rule.

Of course, the deletion fails, and I'm reminded that I'm not querying a table directly: silly programmer. But that hint, what a beaut! Using PostgreSQL's Rules system, I can tell the database what I really mean to do:

# create rule attempts_brief_del as on delete to attempts_brief
# do instead delete from attempts where id=OLD.id;
CREATE RULE

Now that the rule is in place, I can delete from the view, and the database will do the Right Thing:

# delete from attempts_brief where quiz=1479;
DELETE 2

Perfect!

It's not a huge thing, I know. But it just reminds me how much time the Postgres designers spent, not only creating a system that is fast, consistent and powerful, but that is helpful, flexible and user-friendly to boot. I love ya, Postgres.

Roman Numerals in Haskell

Here's a toy Haskell program, for converting integers to Roman numerals. It's inspired directly by Doug Coleman's Factor version; I'm using the same algorithm he describes.

(Haskell's an interesting language for writing little things like this. I haven't used a statically-typed language in a long time; it's kind of like taking a hike in someone else's boots. The monad thing hasn't been as hard to wrap my head around as I initially thought it might be; I particularly like the way that exception handling can be generalized using monads.)

-- inspired by
-- http://code-factor.blogspot.com/2007/06/roman-numeral-conversion-in-factor.html

module RomanNumerals (toRoman) where

-- constants

romanValues = [1000, 900, 500, 400, 100, 90, 50, 40, 10, 9, 5, 4, 1]
romanDigits = words "m cm d cd c xc l xl x ix v iv i"
theRomans   = zip romanValues romanDigits


-- internals

-- To do the actual conversion to Roman numerals, we left-fold over
-- theRomans, starting with a seed value of (accumulator,
-- remainder). The initial remainder is the value we want to
-- convert. Using theRomans as a resource, romFold breaks down the
-- remainder by modular arithmetic, adding Roman digits to the
-- accumulator as it goes. Finally we take the accumulator, and we're
-- done.

toRoman' :: Int -> String
romFold  :: ([a], Int) -> (Int, [a]) -> ([a], Int)

toRoman' n = fst $ foldl romFold ("", n) theRomans

romFold (acc, n) (romValue, romDigit) =
let (result, remain) = divMod n romValue
    newDigits = concat $ replicate result romDigit
in (acc ++ newDigits, remain)


romanRangeCheck :: (Monad m) => Int -> m Int
romanRangeCheck n
| 0 < n && n < 4000 = return n
| otherwise = fail "out of bounds [0 < n < 4000]"


-- The public, range-checking version.

toRoman :: Monad m => Int -> m String
toRoman n = romanRangeCheck n >>= (return . toRoman')

The kindness of dictators, and an octet-full of eggs

I had a real treat today; I received a t-shirt from Felix Winkelmann, the benevolent dictator of the Chicken Scheme project. My prize was for submitting the 256^th “egg” (extension module) to the language, a simple library for accessing Oracle databases. (Of course, it took 255 contributions from others to make mine shirt-worthy: the giants, and their shoulders, did the real work.)

I've been reflecting upon Scheme recently, not only because of the shirt (which, by the way, pleases me more than an article of clothing ought to), but also because I've been using Scheme in earnest these past few months, building a small-but-serious set of Web projects at work. Before working with Scheme, I would have chosen Python for these projects, without a second thought. A dynamic language is the only sensible tool for the bob-and-weave, roll-with-the-punches development style that gets projects like these off of the napkin and into production in short time.

Scheme fills the same dynamic niche, but makes different tradeoffs than Python, and its cousin Ruby, do. It really does feel more like a language-construction kit than a language in itself. You can use Scheme without defining any new syntax, but I think that syntactic freedom is where one of the sweet-spots is. I've done my share of metaprogramming in Python, trying to push the syntax-envelope in order to write concise and representative solutions. In Scheme, there really isn't an envelope to push: you can write anything you want, any way you want it, and sure enough, there's a way to treat it as valid Scheme code. Problems have a way of growing more complicated over time; the lack of a syntax barrier carries the promise (the siren song?) of solutions that can evolve in step with their problems.

Scheme communities are much smaller than those of mainstream languages, so you end up writing a lot of stuff that you take for granted in other languages. That's a strength, of sorts: the devil you wrote is sometimes easier to understand, and often much simpler, than the general library that someone else did. (On the other hand, a programmer who uses his own libraries may have a fool for a client.) The fragmentation of communities doesn't help in the library department; although Scheme-the-language is standardized, there are several dialects, each not fully compatible with the others. (My Oracle library, for example, is a problem that is already (partially) solved in PLT Scheme, but in a PLT-specific manner.) Fragmentation aside, those communities are smart as hell, and if you ask nicely they can help a great deal.

Anyway, back to those Web projects of mine. I can't say that they any shorter than they would have been in Python, and probably not much more readable. At least half of what I've written has been reusable library code, which is good; but I still write Scheme like a greenhorn, and I can see many areas I'd like to fix. So, it's comparable in complexity to the Python I would have written — though in different ways — but I'm optimistic that it will mature more naturally. I also like the performance; Web apps don't always need a lot of computational power, but it's hard to ignore just how well these apps perform: the hotspots just aren't that hot.

In short, I earned a shirt while conspiring with giants, in a wild-west, open-source frontier (or backwoods?), using a shockingly-powerful language that my grandpa would have recognized. Man, I love this stuff.

---

Rethinking OpenURL

Dan Chudnov says a lot of great things in Rethinking OpenURL, but this caught my eye:

"This, my friends, is dynamic service linking, in a nutshell. (And, yes, I do mean 'in a nutshell' as in 'ooh, ooh, help, I'm trapped in a nutshell, get me out of here.')"

And now, I'm off to read the rest before the weekend starts. :-)

Hello planet code4lib!

Hello to the readers of planet code4lib! I just found out my blog is being syndicated [t]here. In future, I'll try to keep the noise down, and finish my snacks before entering the blog...

group-by: like itertools.group in Scheme

Just a little post. I wanted to share a Scheme procedure that worked something like Python's itertools.groupby function. Emphasis on "something like": for one thing, it works on lists only, rather than any "iterable" type, which is part of the magic and appeal of itertools.

(use srfi-1 srfi-26)

(define (group-by keyproc lst #!optional (is-equal equal?))
  ;; examples:
  ;; (group-by identity '(1 2 3 3 4 4 4)) --> ((1) (2) (3 3) (4 4 4))                                         
  ;; (group-by car '((a 1) (a 2) (b 1))) --> '(((a 1) (a 2)) ((b 1)))                                                   
  (let loop ((lst lst) (acc '()))
   (if (null? lst)
       (reverse acc)
       (let ((key (keyproc (car lst))))
         (receive (grouped rest)
             (span (lambda (item) (is-equal key (keyproc item))) lst)
           (loop rest (cons grouped acc)))))))

Also unlike the Python version, there's no default "key" function (Python uses an identity function as the default). To get the Pythonic behaviour, use identity (or values if your Scheme doesn't have an identity procedure). The optional is-equal? function lets you specify a different key-comparison function; by default, the comparison function is Scheme's equal? which is pretty liberal: strings are compared by value, for example, whereas Scheme's eq? compares by identity only. You might want to use a custom comparison function anyway --- for example, to group by strings, but case-insensitively:

(group-by first '(("fred" 25) ("Fred" 33) ("mary" 22)))
--> ((("fred" 25)) (("Fred" 33)) (("mary" 22)))
(group-by first '(("fred" 25) ("Fred" 33) ("mary" 22)) string-ci=?)
--> ((("fred" 25) ("Fred" 33)) (("mary" 22)))

That's all.

hart 0.1 (or, Man lays egg, news at 11)

Happy day — I just submitted Hart, my very first egg (module) for Chicken Scheme. I feel like a proud papa! It's still an early release, and has lots of room to grow, but I've enjoyed writing it, and using it. Now, don't all download it at once, there's plenty for everyone...

...minus Chicken, plus Io, ...

A delightful rewrite in Io of my rewrite in Scheme of this Haskell RSS aggregator. For me, the take-away is the sheer beauty of the functional style of programming. By the way, I'd love to see a rewrite in Factor, if anyone's listening... :-)

Haskell + RSS Aggregator + Chicken − Haskell...

This little example of an RSS aggregator written in Haskell got my curiosity piqued: what would a work-alike in Scheme look like? My motivation was not to write a shorter version, or even a better one really. I did want to kick the tires of "Hart", my soon-to-be-released HTML-generation module for Chicken Scheme... the more real-world use, the better.

Here's the code (download source):

;; srss.scm -- simple RSS aggregator in Chicken Scheme

(use http-client              ; to fetch the feeds
     ssax                     ; XML parsing
     sxml-tools               ; XPath-like functions (sxpath)
     hart)                    ; html generation (mine; not public yet)

(define (url->dom url)
  (SSAX:XML->SXML (nth-value 2 (http:send-request url)) '()))

(define-macro (>> obj . rest)
  ;; just a little sugar for getting text from XML nodes.
  ;; e.g. (>> my-htmldoc html head title) --> "doc title"
  ;;      (>> my-htmldoc // h1) --> "first main heading"
  `(let ((lst ((sxpath '(,@rest *text*)) ,obj)))
     (if (null? lst) "" (first lst))))

(define get-items (sxpath '(* * item)))

(define (aggregate urls)
  (hart (html
         (head (title "RSS Aggregate")
               (link (@ (rel "stylesheet")
                        (href "srss.css") (type "text/css"))))
         (body (h1 "RSS Aggregate")
               (for: (dom (map url->dom urls))
                 (div (@ (class "channel"))
                      (h2 (raw: (>> dom * * title)))
                      (ul (for: (item (get-items dom))
                            (li (@ (class "item"))
                                (a (@ (href (>> item link)))
                                   (h3 (raw: (>> item title))))
                                (p (raw: (>> item description))))))))))))

;; main

(match (command-line-arguments)
  ((out-file . urls) (with-output-to-file out-file 
                       (lambda () (aggregate urls))))
  (_ (print "usage: srss outfile [url]*")))

Since I haven't released Hart quite yet, I guess you'll have to trust me that it works. ;-) The Hart-part above is the stuff that looks like (ul (for: (item items) (li (text: item)))) — my ultra-clever new technology turns expressions like that into efficient HTML/XML-generating code. Without Hart, a similar approach in Scheme would be to build up a DOM-like SXML structure, and serialize it to XML; the SXML-based code would look pretty similar.

I sort of like how the program looks a bit like XSLT cross-bred with a more-traditional "scripting" language. Sort of. I won't push the analogy too far; I'm sure that to many it looks more like oatmeal with fingernail clippings mixed in. I do think it's a touch more readable (though not by much) than the Haskell program it mimics.

Like the Haskell program, it could benefit from more documentation, especially for readers unfamiliar with Scheme. (If anyone asks, I'll publish a step-by-step breakdown of the program to cover all the Schemely goodness therein.)

Anyway… I came, I saw, I transliterated a short Haskell program. QED. If you're in a meme-perpetuating mood, maybe you'd like to code the same thing up in the language of your choice, and share it?