Wish: SUB-INSERT SQL statements

Sub-selects are nice:

INSERT INTO person
 (name, phone, city_id)
VALUES
 ( 
   'john',
   '555-1234',
   (SELECT id FROM city WHERE name = 'Los Angeles')
 )

It saves a DB roundtrip to find the city.id for ‘Los Angeles’.

But sometimes you encounter a new city and first have to assign an entry for it in the city table. Except that the row may already exist, so first you have to select the city, if it does not exist, create the city and finally store the person. MySQL has a nice pattern for storing rows that might already exist in the DB, allowing us to combine the select-if-not-found-store steps into one:

INSERT INTO city
  (name)
VALUES
  ('Palo Alto')
ON DUPLICATE KEY
  UPDATE id = LAST_INSERT_ID(id);

MySQL will return the correct city.id for the new city entry regardless of whether the row had to be inserted or it already existed.

Now, it would be sweet if INSERTs could be used the same way SUB-SELECTs are, allowing us to combine all three steps into a single statement:

INSERT INTO person
 (name, phone, city_id)
VALUES
  ( 
   'john',
   '555-1234',
   (
     INSERT INTO city
       (name)
     VALUES
      ('Palo Alto')
     ON DUPLICATE KEY
      UPDATE id = LAST_INSERT_ID(id);
   )
 )

This pattern happens all the time when storing complex records that span multiple tables. It would be very convenient if databases would support it natively.

Advertisements

Wish: Retina Display Image Magnification Service

I am writing this on a Retina MacBook Pro. Text and anything rendered from vectors looks absolutely, I’ll-have-a-hard-time-going-back-to-non-retina, amazing. But poorly scaled bitmap graphics look terrible. The contrast between the ultra crisp text and the pixelated, fuzzy scaled graphics makes the problem even more glaring and annoying.

For Polyvore’s Mobile HTML site, we had to deal with this problem. We switch to serving 2x dpi images when the client device uses a high dpi display. We could easily do this because our backend image rendering pipeline is able to render generate images in different resolutions.

But what about all our other image assets? And what about all the other services out there that can’t serve high dpi versions of their images?

A useful service would be some kind of Image Scaling proxy that sits in from of your image servers and dynamically decides what kind of image resolution it should serve.

If you have high resolution image sources, serving lower resolution images is not a problem. There are lots of algorithms that can down-scale images and produce great looking output.

The problem is when you have to magnify images from standard resolution sources. For pixel art type image, there are algorithms like hqnx.

NN interpolation


hqnx scaling

For photos and images of natural things, there are good magnification algorithms like fractal based scaling.

Fractal Resizing

But there is another class of hard-to-algorithmically-magnify images. Things like icons with gradients, a mixture of pixel art and natural images. For these, it would be interesting to offer an automated best-effort option but also create a marketplace where graphic designer can redo your image assets in high resolution. The proxy server would use the best option available to it.

imgix seems to be headed in this direction. Services like CloudFlare, Strange Loop and InStart could also offer this type of service.

Credit Rolls for Software

Software should have credit rolls the way films do.

At the end of every film, there are the credit rolls that acknowledge the work of everyone who was involved in the making of the film:

Star Wars Episode IV Credit Roll

Why not the same for software? In the case of films, all sorts of people get credit for their contributions but in most software and software services, we don’t know the contributors and usually we can’t even find out. I use tons of online services but I have no idea who the true contributors really are. At most, if the service is very popular, I might know the name of the founders (Larry/Sergey, Jerry/David, Adam D’Angelo, …).

This opacity is a huge shame because people who write software should rightfully be recognized for their work.

It is even technically possible to produce a credit roll for the software that we use. Here is a version control log (svn -blame) of who the latest contributor for each line of code for a Polyvore js function:

 
  8358      kwyee // Monitor calls f every interval ms.  
  8358      kwyee // It fires a change event when f() changes.
  8482      kwyee // f() is passed to the trigger event handler.
  2073      pasha function Monitor(f, interval) {
  2623      pasha     var curr = f();
  2073      pasha     if (!interval) { interval = 100; }
  8656      kwyee     this.check = function() {
  2073      pasha         var now = f();
  2073      pasha         if (now != curr) {
  2073      pasha             curr = now;
  2073      pasha             Event.trigger(this, 'change', curr);
  2073      pasha         }
  8656      kwyee         return now;
  8656      kwyee     };
 17189      pasha     this.timer = new Interval(interval, this.check, this);
  2073      pasha }

It is possible to take this information and instrument the code such that each execution keeps track of all the people who contributed and produce an exhaustive credit roll of sorts. There could be a dialog that shows all the people whose code was executed to make this app / page / REST call etc… possible. I actually remember that old System 7 era Apple software used to have credits in the ‘About X…’ dialogs.