Andrew Cooke | Contents | Latest | RSS | Previous | Next

C[omp]ute

Welcome to my blog, which was once a mailing list of the same name and is still generated by mail. Please reply via the "comment" links.

Always interested in offers/projects/new ideas. Eclectic experience in fields like: numerical computing; Python web; Java enterprise; functional languages; GPGPU; SQL databases; etc. Based in Santiago, Chile; telecommute worldwide. CV; email.

Personal Projects

Choochoo Training Diary

Last 100 entries

[Programming] React Leaflet; AliExpress Independent Sellers; Applebaum - Twilight of Democracy; [Politics] Back + US Elections; [Programming,Exercise] Simple Timer Script; [News] 2019: The year revolt went global; [Politics] The world's most-surveilled cities; [Bike] Hope Freehub; [Restaurant] Mama Chau's (Chinese, Providencia); [Politics] Brexit Podcast; [Diary] Pneumonia; [Politics] Britain's Reichstag Fire moment; install cairo; [Programming] GCC Sanitizer Flags; [GPU, Programming] Per-Thread Program Counters; My Bike Accident - Looking Back One Year; [Python] Geographic heights are incredibly easy!; [Cooking] Cookie Recipe; Efficient, Simple, Directed Maximisation of Noisy Function; And for argparse; Bash Completion in Python; [Computing] Configuring Github Jekyll Locally; [Maths, Link] The Napkin Project; You can Masquerade in Firewalld; [Bike] Servicing Budget (Spring) Forks; [Crypto] CIA Internet Comms Failure; [Python] Cute Rate Limiting API; [Causality] Judea Pearl Lecture; [Security, Computing] Chinese Hardware Hack Of Supermicro Boards; SQLAlchemy Joined Table Inheritance and Delete Cascade; [Translation] The Club; [Computing] Super Potato Bruh; [Computing] Extending Jupyter; Further HRM Details; [Computing, Bike] Activities in ch2; [Books, Link] Modern Japanese Lit; What ended up there; [Link, Book] Logic Book; Update - Garmin Express / Connect; Garmin Forerunner 35 v 230; [Link, Politics, Internet] Government Trolls; [Link, Politics] Why identity politics benefits the right more than the left; SSH Forwarding; A Specification For Repeating Events; A Fight for the Soul of Science; [Science, Book, Link] Lost In Math; OpenSuse Leap 15 Network Fixes; Update; [Book] Galileo's Middle Finger; [Bike] Chinese Carbon Rims; [Bike] Servicing Shimano XT Front Hub HB-M8010; [Bike] Aliexpress Cycling Tops; [Computing] Change to ssh handling of multiple identities?; [Bike] Endura Hummvee Lite II; [Computing] Marble Based Logic; [Link, Politics] Sanity Check For Nuclear Launch; [Link, Science] Entropy and Life; [Link, Bike] Cheap Cycling Jerseys; [Link, Music] Music To Steal 2017; [Link, Future] Simulated Brain Drives Robot; [Link, Computing] Learned Index Structures; Solo Air Equalization; Update: Higher Pressures; Psychology; [Bike] Exercise And Fuel; Continental Race King 2.2; Removing Lowers; Mnesiacs; [Maths, Link] Dividing By Zero; [Book, Review] Ray Monk - Ludwig Wittgenstein: The Duty Of Genius; [Link, Bike, Computing] Evolving Lacing Patterns; [Jam] Strawberry and Orange Jam; [Chile, Privacy] Biometric Check During Mail Delivery; [Link, Chile, Spanish] Article on the Chilean Drought; [Bike] Extended Gear Ratios, Shimano XT M8000 (24/36 Chainring); [Link, Politics, USA] The Future Of American Democracy; Mass Hysteria; [Review, Books, Links] Kazuo Ishiguro - Never Let Me Go; [Link, Books] David Mitchell's Favourite Japanese Fiction; [Link, Bike] Rear Suspension Geometry; [Link, Cycling, Art] Strava Artwork; [Link, Computing] Useful gcc flags; [Link] Voynich Manuscript Decoded; [Bike] Notes on Servicing Suspension Forks; [Links, Computing] Snap, Flatpack, Appimage; [Link, Computing] Oracle is leaving Java (to die); [Link, Politics] Cubans + Ultrasonics; [Book, Link] Laurent Binet; VirtualBox; [Book, Link] No One's Ways; [Link] The Biggest Problem For Cyclists Is Bad Driving; [Computing] Doxygen, Sphinx, Breathe; [Admin] Brokw Recent Permalinks; [Bike, Chile] Buying Bearings in Santiago; [Computing, Opensuse] Upgrading to 42.3; [Link, Physics] First Support for a Physics Theory of Life; [Link, Bike] Peruvian Frame Maker; [Link] Awesome Game Theory Tit-For-Tat Thing; [Food, Review] La Fabbrica - Good Italian Food In Santiago; [Link, Programming] MySQL UTF8 Broken; [Link, Books] Latin American Authors

© 2006-2017 Andrew Cooke (site) / post authors (content).

Using Scala with Empire DB

From: andrew cooke <andrew@...>

Date: Wed, 7 Oct 2009 23:28:18 -0400

Bringing together two different language paradigms doesn't seem to be
easy.  For example, merging OO and functional programming seems to
have taken quite some time.  I am not sure where the problem lies -
perhaps it takes time to develop appropriately balanced languages, or
perhaps people just need time to adjust.

Another example is how best to access relational databases from OO
languages.  The most popular approach seems to have been to emphasise
the objects as much as possible, and then slowly introduce SQL-like
(more declarative) features.  That is how I would (roughly)
characterise the approach taken by, say, Hibernate.

But just as functional ideas are starting to spread into the OO
mainstream, so there are few signs that a more declarative, SQL-like
approach may be useful for some problems.  Perhaps the best example is
LINQ which I have not had the chance to use.

Two other libraries that take a vaguely similar approach (although,
been libraries, they are not sa deeply integrated as I think LINQ is)
are SQLAlchemy for Python and Empire DB for Java.  Both these place
the primary emphasis on constructing SQL queries; building objects
from the results comes second.

Personally, as someone who actually likes using SQL, I find this
approach very interesting.

Recently I started a new, small project, to learn Scala: I decided to
write a playlist generator that would take my MP3 collection and use
metadata to construct graphs of connected tracks.  Given the above I
also decided to try using Eclipse DB.

What follows are some examples from my initial code.  I am still
working on constructing the database, given a set of MP3 files, and I
am sure my code will evolve significantly before I finish.  But what I
have is already interesting and I thought it would be useful to share
it.

Empire DB is at http://incubator.apache.org/empire-db/

The changeset for the code that I have as I write this is at
http://code.google.com/p/uykfd/source/detail?r=a834c704643dc48642bb71e74aa7c4c967ea1da9


I thought I would focus on two areas of the code.  First, the database
definition.  This is used (1) to define the database (Empire DB
creates SQL that builds the schema) and (2) to define instances of
Java classes that will support the construction of type safe queries.

The database definition is here -
http://code.google.com/p/uykfd/source/browse/src/main/scala/org/acooke/uykfd/db/Schema.scala?spec=svna834c704643dc48642bb71e74aa7c4c967ea1da9&r=a834c704643dc48642bb71e74aa7c4c967ea1da9#21
- and I will also cut+paste some examples below.

Here, for example, is a class that describes a table with two columns
- an auto-increment key and some varchar text:

  class IdValue(name: String)
      extends DBTable(name, this) {
    val ID = addColumn("ID", DataType.AUTOINC,
                       0, true, name + "_SEQ")
    val VALUE = addColumn("VALUE", DataType.TEXT,
                          MAX_VARCHAR, true)
    setPrimaryKey(ID)
  }

This can be then instantiated -

  object CANONICALS extends IdValue("CANONICALS")

- to create a table, called CANONICALS, that has two columns (ID and
VALUE), with the types as above.  Later Scala code can refer to those
columns as CANONICALS.ID and CANONICALS.VALUE

Note that Scala's object/class approach makes this code particularly
compact and transparent (at least if you know a little Scala).  With
both Empire DB and SQLAlchemy I have been impressed with how easy it
is to define a schema, including basic foreign key constraints, in a
engine-neutral manner (I am using HSQL, but the same code would work
with, say, Oracle or MySQL).


The second area I wanted to show was the code I use to generate
entries in the database.  This code is *not* as transparent as the
schema.  That is largely because I am writing quite generic code that
is reused for a variety of tables.  I hope I can improve this work.

However, some of the details are still worth showing.  For example,
look at the routines used to extract data from the database -
http://code.google.com/p/uykfd/source/browse/src/main/scala/org/acooke/uykfd/db/Schema.scala?spec=svna834c704643dc48642bb71e74aa7c4c967ea1da9&r=a834c704643dc48642bb71e74aa7c4c967ea1da9#102

These are for all tables that follow the ID/VALUE layout I described
above and the first method, fromValue, constructs a SQL query to fine
the value:

    val cmd = Schema.createCommand
    cmd.select(row.table.getColumns)
    cmd.where(row.table.VALUE.is(value))

Notice how there are no strings used for table names in that snippet.
Everything is defined in terms of the schema described above.

OK, that will have to do for now - I need to get some sleep.

Andrew

Outer Join and Sub-Select Example for Empire DB and Scala

From: andrew cooke <andrew@...>

Date: Thu, 8 Oct 2009 15:04:09 -0400

Here's a nice example where I am clearing out orphaned values (I don't
want to cascade on deletion for various reasons).

  def clean(cnxn: Connection) {
    val subCmd = Schema.createCommand
    subCmd.select(Schema.ARTIST_TAGS.ID)
    subCmd.join(Schema.TRACKS.ARTIST_TAG,
                       Schema.ARTIST_TAGS.ID, DBJoinType.RIGHT)
    subCmd.where(Schema.TRACKS.ID.is(null))
    val orphanArtists = new DBQuery(subCmd)
    val cmd = Schema.createCommand
    cmd.where(Schema.ARTIST_TAGS.ID.in(orphanArtists))
    Schema.executeSQL(cmd.getDelete(Schema.ARTIST_TAGS), cnxn)
  }

The right join constructs a table with all the artists, then I select
those fr which there is no corresponding track (null ID).  That is
used as the sub-select for deletion.

Andrew

And If You Still Don't Get It

From: andrew cooke <andrew@...>

Date: Thu, 8 Oct 2009 15:16:48 -0400

Because it's code, it's easy to reuse:

  def clean(cnxn: Connection) {
    def cleanOrphans(tag: DBColumnExpr, table: Schema.Id) {
      val subCmd = Schema.createCommand
      subCmd.select(table.ID)
      subCmd.join(tag, table.ID, DBJoinType.RIGHT)
      subCmd.where(Schema.TRACKS.ID.is(null))
      val orphans = new DBQuery(subCmd)
      val cmd = Schema.createCommand
      cmd.where(table.ID.in(orphans))
      Schema.executeSQL(cmd.getDelete(table), cnxn)
    }
    cleanOrphans(Schema.TRACKS.ARTIST_TAG, Schema.ARTIST_TAGS)
    cleanOrphans(Schema.TRACKS.ALBUM_TAG, Schema.ALBUM_TAGS)
    cleanOrphans(Schema.TRACKS.SONG_TITLE_TAG, Schema.SONG_TITLE_TAGS)
  }

Andrew

Comment on this post