Functional pattern for converting database results to structures

[Richard Jones <rich@annexia.org>]

[-- Attachment #1: Type: text/plain, Size: 3146 bytes --]

[This isn't really a question about OCaml, or ML, but functional
programming in general.]

I often come across the same problem.  I have to read a table of data
(often the result of a SQL query, or a CSV file), and create a
hierarchical structure from it.

For example, my current problem is a table of data which looks like
this:

Campaign   Adgroup   Keyword   Advert   Campaign_status  Adgroup_status
-----------------------------------------------------------------------
Summer04   Domain    KW1       AD1      Finished         Finished
Summer04   Domain    KW2       AD1      Finished         Finished
Summer04   Domain    KW3       AD1      Finished         Finished
Summer04   Domain    KW4       AD1      Finished         Finished
Summer04   Domain    KW5       AD2      Finished         Finished
Summer04   Domain    KW6       AD2      Finished         Finished
Summer04   Offer1    KW7       AD3      Finished         Finished
Summer04   Offer2    KW8       AD3      Finished         Finished
Summer04   Offer3    KW9       AD3      Finished         Finished
Summer04   Offer4    KW10      AD3      Finished         Finished
Winter04   Domain    KW1       AD1      Active           Active
Winter04   Domain    KW2       AD1      Active           Active
Winter04   Domain    KW3       AD1      Active           Active
Winter04   Domain    KW4       AD1      Active           Active
Winter04   Domain    KW5       AD2      Active           Active
Winter04   Domain    KW6       AD2      Active           Active
Winter04   Offer5    KW7       AD3      Active           Active
Winter04   Offer5    KW8       AD3      Active           Active
Winter04   Offer5    KW9       AD3      Active           Active
Winter04   Offer6    KW10      AD4      Active           Active

and I'd like to come out with a structure like this:

type campaign = { campaign_name : string; campaign_status : string;
                  adgroups : adgroup list }
and adgroup = { adgroup_name : string; adgroup_status : string;
                keywords : keyword list;
                adverts : advert list }
and keyword = { keyword : string }
and advert = { advert : string }

I've previously been using a "group_by" function kindly written by
Isaac Trotts (see thread here:
http://groups.yahoo.com/group/ocaml_beginners/message/1556 ).

val group_by : ('a * 'b) list -> ('a * 'b list) list
group_by [1, "foo"; 2, "bar"; 2, "baz"; 2, "biz"; 3, "boo"; 4, "fizz"];;
- : (int * string list) list =
[(1, ["foo"]); (2, ["bar"; "baz"; "biz"]); (3, ["boo"]); (4, ["fizz"])]

But it's still complicated to use, particularly when we have several
levels of nesting, and, as in this case, the levels aren't even
cleanly nested (adgroups have both keywords and adverts).

Help!  What's a good pattern for solving this problem?

Rich.

-- 
Richard Jones.  http://www.annexia.org/  http://www.j-london.com/
>>>   http://www.team-notepad.com/ - collaboration tools for teams   <<<
Merjis Ltd. http://www.merjis.com/ - improving website return on investment
Advanced wiki and collab tool - http://sandbox.merjis.com/

[-- Attachment #2: Digital signature --]
[-- Type: application/pgp-signature, Size: 189 bytes --]