Gradients: a Halftone Gradient

So much to do: so little time! I have 3 other fun subjects to blog about... all of them in varying states of completion. But this is the most finished, so it's the first to write up.

Halftone Gradients

I don't know why, but I've always wanted a gradient that looked like halftoning.

But for some reason whenever I thought of tackling it: I always envisioned myself writing a PaintContext and coming up with some ugly code that involved lots of varying radii, and antialiasing things by hand, and doing clever things with AffineTransforms and dot products that I'm not really well-versed in. And maybe that is the better/more efficient way to approach this subject... but there's a much easier way.

My last blog entry used transformed tiles to achieve a multiple-color gradient. If you look at a halftone gradient as a tiling pattern, then suddenly it becomes a piece of cake. I just need to create a tall, skinny tile that transitions from one color to another.

If I were a better blog author, I might try inserting a diagram here. I'd demonstrate exactly what the tile looks like, and maybe discuss the concept of halftoning a little bit more.

However I'm not a better author. I'm much too lazy. Plus (and I'm not *entirely* bs-ing here): I really do believe a hands-on interactive model will explain this project more thoroughly than diagrams ever will. So... here's a demo applet showing off the HalftoneGradient class:



The jar (source included) is available here.

Your Basic Options

Although I started with circles, it's even easier to make a tiled pattern using diamonds or isosceles triangles. The size of each shape scales in proportion to the width of the tile, and where in the gradient that shape is drawn.

I'm unsure, though, if the circles are done correctly. I didn't find an exact algorithm for describing how halftoning should work -- but I admit I didn't look too closely. If a gradient is expressed in terms of t=[0,1]: does the gradient at t = .5 really look correct? Is that a half-way point between the two colors? For the circles I made the radius related to t^.8 to try to improve the balance. There is surely a more scientific way to approach this subject, but I didn't have the interest or patience. Does anyone have any experience with this subject? Basically the proportion of color1 to color2 should increase linearly with t.

Oh, and as an afterthought I added the "offset" property, and the "animate" checkbox. How could I resist? (Have you seen my art? I love animating things.)

Making a Tiled Pattern Not Tile

This paint delegates all the hard work to a transformed TexturePaint, so it should be tiling like mad when the "Cycle" checkbox is unselected, right? This was probably the hardest part of the project: if a point lies outside of the strip created between P1 and P2: how do I make it a solid color?

Originally I went to Sun's website and downloaded the source for the TexturePaintContext. I was determined to roll up my sleeves and make an adaptation that solved this. It didn't take me long to realize I was waaaaaaay over my head, though, as I looked through their source code. Aside from subtle problems (like referencing non-public classes to manipulate rasters), they somehow eliminated floats/doubles. Everything was int-based, on a scale of [0, Integer.MAX_VALUE]. While probably more efficient: this hurt my head. If you locked me in a dark prison cell with just bagels and a laptop: maybe in a few days I could figure this out. But I wanted to figure this out in a few hours, so I moved on to plan B:

Instead I just rewrote the raster the TexturePaintContext returns. Is it the most efficient response? No. Does it work? Yes. The class is called CoveredContext. (It's non-public, although it's included in the jar mentioned above.) In its original draft the hard work looked like this:

public Raster getRaster(int x, int y, int w, int h) {
     WritableRaster raster = (WritableRaster)context.getRaster(x, y, w, h);
     ...
     double[] matrix = new double[6];
     transform.getMatrix(matrix);
     for(int y2 = 0; y2 < h; y2++) {
         raster.getDataElements(0, y2, w, 1, data);
    
         for(int x2 = 0; x2 < data.length; x2++) {
             double x3 = x2+x;
             double y3 = y2+y;

             // apply the transform manually:
             y3 = matrix[1]*x3+matrix[3]*y3+matrix[5];

             if(y3 < 1) {
                 data[x2] = color1;
             } else if(y3 >= distance-1) {
                 data[x2] = color2;
             }
         }
    
         raster.setDataElements(0, y2, w, 1, data);
      }
      return raster;
}

The final draft, though, is much more mathematically clever. (It calculates the two x-points in a row of pixels that mark the beginning and end of the gradient, and flood fills everything beyond those points.) But it's basically the same approach in spirit. It involves many more special cases, though.

Conclusion

So there you have it. Very painless. The code, including comments and javadoc, probably comes to less than 500 lines of code. Eventually I plan to integrate this into some other projects I'm working on, but that will have to wait.

I haven't tried to break this down and scrutinize performance yet. This class first needs to demonstrate that it, in fact, useful...

Gradients: a Boring Discussion

This really is a boring entry. In most of my articles I tried to include some sexy snippets of code, but this article is just an exploration of gradients and paints. There are no exciting conclusions. But I learned a lot, and in case you find yourself researching the same topic: here's what I found.

The catalyst that started all this was when I started studying some old code at work. A few years ago we devised a series of complicated mechanisms to help optimize rendering gradients. (Most of them were somehow based on Vincent Hardy's Java 2D API Graphics book.) Did our tricks work? And if they did: do they still?

In this entry when I say "gradient" I'm referring to what is now known as a MultipleGradientPaint. This is now standard in Java 1.6, but in earlier versions the only official Paint that came standard with Java is the GradientPaint (a 2-color gradient).

One problem here is that there are, to my knowledge, only a couple of gradients to work with. There are the linear and radial gradients in Hardy's book. Then later on Batik came along with linear and radial gradients designed for SVG. Funny thing is: Vincent Hardy helped write these, too. (Along with Nicholas Talian, Jim Graham, and Jerry Evans.) Which makes sense, but for the sake of this article I'd like to have some really distinct classes to compare.

A Simple Java 1.4-friendly Linear Gradient

So my first priority was to make a really simple alternative gradient to test against.

A few years ago Romain Guy pointed out you could improve performance with horizontal and vertical gradients if you used a TexturePaint instead of an actual gradient. And last year I combined TexturePaints with AffineTransforms in this article. So if you consider a linear gradient as basically a repeating tiled image... and you can transform that tile... then voila! You can make a GradientTexturePaint.

This paint creates a 1-pixel tall tiled image, and then finds the appropriate AffineTransform to make that tile stretch the two control points you provide. Here is a demo. Use the slider at the top to control the colors, and the two white circles adjust the endpoints of the gradient:

(You can download this demo, source included, here.)

You can right-click the preview area to change the rendering hints. Using Sun's pipeline there is a visible difference when the interpolation hints are set to BILINEAR or BICUBIC. For convenience I also made the ANTIALIASING key affect the interpolation, too.

Under Quartz, however, you have to set the INTERPOLATION, and define the COLOR_RENDERING. It doesn't matter what it is (SPEED or QUALITY), but it has to be defined. But the code I added to intercept the ANTIALIAS hint doesn't work... ugh. It's a wreck. Quartz is forever a mystery.

Meanwhile, here is a screenshot contrasting the difference RenderingHints make:

The biggest downside to this gradient is that it has to tile. (Unlike the GradientPaint class, which shows only one color beyond the two control points.) Also, as we see later: it's performance isn't great.

But the advantages are:

• It's Java 1.4 compatible. (Java 1.3, probably?)


• It requires no other classes. It's very lightweight to add to your project.

So. Problem #1 solved: I have at least one more gradient to compare.

Comparing Gradients

Let's focus on linear gradients. Here are the classes we can test against:

1. com.sun.glf.goodies.GradientPaintExt from Hardy's book.


2. com.bric.awt.GradientTexturePaint mentioned above.


3. org.apache.batik.ext.awt.LinearGradientPaint from Batik.


4. java.awt.LinearGradientPaint in Java 1.6+.


5. DelegatePaint: this is a small shell wrapped around the java.awt.LinearGradientPaint. I wanted to see if the LinearGradientPaint was receiving special optimized treatment under-the-hood in the graphics pipeline.

I put together several tests -- each test measures the median time and memory allocation required for an operation. The tests include:

• Creating PaintContexts.


• Calling Graphics2D.fillRect().


• Calling Graphics2D.fill(Ellipse2D).


• Performing these operations through a small AffineTransform.


• Also compare what it costs to recycle a gradient vs constructing it from scratch.

The test I used is here (source included). Here are the results after running it in a variety of environments.

There's a lot of data. Oiyee. What does it mean?

Creating Rasters vs Blitting

For starters: look at the first two lines. This involves getting a PaintContext and calling PaintContext.getRaster(). Notice that these are considerably faster than the next couple of lines that involve calling Graphics2D.fillRect() for the same number of pixels. This brought me to my first sad realization about Paint objects: the bottleneck in performance is deep in the graphics pipeline. This makes sense, but it is frustrating because it's harder to modify that behavior. Werner has taken on the task of creating a new Graphics2D pipeline, but it's a big undertaking.

Where does the rest of the time go? In the Sun pipeline (that is, when Quartz is not active), the time may look like this:

The vast majority of the time is spent blitting. This seems especially unfortunate for simple calls to fillRect() (because the "hard" work is already done, right?). But it is what it is.

Using Small AffineTransforms

What about those lines that usually take 1-3 milliseconds? The tests with "reduced" in the name? These paint both rectangles and ellipses through a transform that scales the x and y coordinates by 0.2.

The catch here is when Quartz is active: it doesn't do too well. This is where the "optimized" keyword comes in. Based on this research -- and some of the old code we had lying around work -- I added another method to the OptimizedGraphics2D class. Basically when using Quartz, and painting with anything other than a java.awt.Color, we get the raster from a PaintContext and then paint that as a BufferedImage. It looks something like this:

PaintContext context = currentPaint.createContext(ColorModel.getRGBdefault(), 
    rect, rect, getTransform(), oldHints);
BufferedImage scratchImage = getScratchImage(rect.width, rect.height);
WritableRaster paintRaster = (WritableRaster)context.getRaster(rect.x, rect.y, rect.width, rect.height);
BufferedImage newImage = new BufferedImage(context.getColorModel(), paintRaster, false, null);
Graphics2D g = scratchImage.createGraphics();
g.setComposite(AlphaComposite.Clear);
g.fillRect(0, 0, rect.width, rect.height);
g.translate(-rect.x, -rect.y);
g.transform( myTransform );
g.setColor(Color.black);
g.setComposite(AlphaComposite.SrcOver);
g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, 
    RenderingHints.VALUE_ANTIALIAS_ON);
g.fill(s);
g.setTransform(identityTransform);
g.setComposite(AlphaComposite.SrcIn);
g.drawImage(newImage, 0, 0, null);
g.dispose();
drawImage(scratchImage, 
    rect.x, rect.y, rect.x+rect.width, rect.y+rect.height, 
    0, 0, rect.width, rect.height, 
    null, null);
newImage.flush();

So we're painting the mask in black, and then using a SrcIn composite to render the fill of our paint.

I'd recommend avoiding Quartz if you can. This is one of a handful of surprises we've found over the years, and Apple has been steadily pushing the Sun pipeline for some time now.

Note that in these tests (in the GradientTest app), there is a crucial line involved in testing:

OptimizedGraphics2D.testingOptimizations = true;

This means the code I mentioned above is always being called. But in regular usage that method will only be called when Quartz is active. (The OptimizedGraphics2D is very careful about when it employs its special tricks.)

What About the Delegates?

Good news! The delegates are worthless. In Java 1.6 I wrapped the awt.LinearGradientPaint in a custom Paint object (and also wrapped their PaintContext, too). I know java.awt.Colors and java.awt.BasicStroke used to get special optimized treatment in Quartz (and maybe in Sun's pipeline?)... so I wanted to see if that was the case here. It isn't. This is great: it means what we see is what we get. And it means we can ignore those columns of data.

What About Sharing?

Here "sharing" refers to using a static gradient, instead of constructing a new gradient object in each test.

In the "Java 1.5" and "Java 1.6" tabs, there is practically no difference in the time it takes between sharing and non-sharing tests. This is also welcome news. It means the Paint objects aren't doing any clever caching of their own, and it makes developers' lives easier.

However. When Quartz is active, there's a speed improvement gained by sharing the gradient object. And the memory allocation? Wow. 0 bytes in some cases? Something special is going on here. And I have no idea what it is. Moving on. (Seriously, how am I supposed to cover all this material?)

The Gradient Classes

One of the most obvious comparisons is to look at the columns side-by-side. What stands out?

The first thing that pops out at me: the GradientTexturePaint loses. It's not horrible, but it's always behind the other classes in terms of speed.

Surprisingly in Java 1.6 the Batik class slightly outperform the awt class. I assumed the awt class was, well, based-on the Batik model. The code should be the same, right? Apparently not. Calling fillRect() is 25% faster when you use Batik.

I find the memory allocation results are looking pretty shady here. The GradientTexturePaint takes 0 bytes to run some of these tests? I wouldn't be surprised if the tests/measured are somehow flawed. This doesn't make sense.

Managing Memory

Usually when I measure memory allocation, I add lines that call garbage collection before a sensitive block of code. But it turns out this can dramatically skew some results in this case: because of how some gradients are cached. (WeakReferences can get involved! Calling garbage collection unnecessarily destroys the cached raster they refer to.)

The more I look at this, the more is looks like the Java2D architecture for Paints was poorly thought out. A Paint has one real method of interest: createContext(). (Already I'm scratching my head: why not make the method called getContext()? The distinction is that create() requires making a new object, and get() might simply return an existing object. This could have huge implications if you paint thousands of lines at varying transforms. But this is trivial compared to the next part...)

A PaintContext has a magic method called getRaster(). It returns a Raster object. It looks simple enough, until you consider how it is used: you might be painting millions of shapes! Is each call to this method creating a new Raster? Rasters are huge; this can really affect how fast memory is burned through. Instead the method should accept a destination WritableRaster as an argument. This avoids the cost of creating new objects constantly. Also it clearly defines the role of the method. With the existing signature: I don't know what happens to a raster object when I hand it to the caller. Am I free to reuse it? Am I responsible for disposing it? The graphics pipeline may want to hang on to it until a certain number of rasters queue up, and then process it. (This would be especially useful if opaque shapes were rendering on top of each other: it may be the case that the first shape is completely obscured by latter shapes, so its raster is irrelevant. I'm not saying this is done: just that it's possible.)

However "good" paints do not create rasters constantly. Batik paints, for example, have a static method: getCachedRaster(ColorModel cm,int w,int h). As long as you're asking for a width and height greater than 32 pixels: one static raster will be continuously recycled. They've been doing this for years and the world doesn't fall apart, so apparently it's a safe thing to do. So if you're writing your own PaintContext: recycle your rasters. Look at Batik's example. (They also dispose/release rasters in a clever way.) Their example is important because it's efficient, and because it obviously works with the Sun pipeline.

Conclusions

I don't really have any conclusions; I just felt like this article should end with a "Conclusions" section. :)

The only useful things to come out of this are:

• I added a new trick to the OptimizedGraphics2D class. However as I phase out Quartz more and more, it doesn't seem that important.


• I made a light multiple-color gradient for Java 1.4. Since I strive to maintain backwards compatibility in most of my projects: this may quickly end up in my custom UI components.

If you aren't worried about backwards compatibility: Java 1.6's gradient paints look perfectly sound. The Batik classes outperformed them in some cases, but overall I think they're a perfectly good choice if you're shopping around.

I apologize for the haphazard way material was presented here, but there really is so much ground to cover. I could spent hours (and pages) studying the subtleties of the numbers I collected; it's hard to figure out exactly what is important and how to present it.

If you'd like to see me add more tests, or have insight into the results already mentioned here: please be in touch. If you have specific questions that might be hard to answer through comments please email me directly.

AlphaComposites: Which Does What?

It seems like every 4 months or so I come across a particular problem that I want to solve using AlphaComposites. The problem is I'm a very visual person; the equations listed in the javadocs don't help me visualize what each composite actually looks like.

Here is an applet that demonstrates most of the possible variables at play:

This applet (source included) is available here.

Also there are several AlphaComposite types where it makes a huge difference whether you're rendering images vs shapes. In this applet if "Use Images" is selected then the shapes you see are first rendered in a BufferedImage, and then those images are rendered together.

That's all. This is a clean, short article -- as much for my benefit as anyone else's.

Buttons: New UI's

This article features some new ButtonUIs I've been working on.

Coming from a strong background in Macs, the first UIs I wanted to emulate are the ones found here. But I abstracted some common patterns and made a generic model that can easily be adapted to other looks, too.

Here's a demo applet showing off what I have so far. The UI's are displayed in a JInternalFrame so you can resize the window to see how they scale. (They're originally presented at their preferred size.)

This app (source included) is available here.

Why Bother?

Why bother to make these UI's when Apple provided some convenient client properties to get the same buttons in Java? I have several possible reasons:

These are cross-platform. You can use them anywhere.

These are pure Java. This means you can override methods of interest. Also if you find a bug you can go exploring...

Resizing. It turns out -- last I checked -- that Apple's buttons are vector-based. (Try rendering them through an AffineTransform!) However: as JComponents they don't always resize well. (Especially the segmented variety.) If you have an icon that's just a couple of pixels larger than allowed: then those couple of pixels may just dangle off the edge.

Vertical segments. Although horizontal segments are much more common/important: why not offer vertical segments?

Java 1.4 compatible. (Probably even Java 1.3 compatible -- I haven't checked?) At work we strive to maintain backward compatibility for several years, so this is an advantage for us.

As a disclaimer I should add: it is not my intention to make pixel-perfect replicas of any UI's. Instead my goal is to be "passable". There may be some improvements, and there be some regressions. If you have questions or comments, please let me know. I may be able to defend certain design decisions, or you may convince me to change something. :) But speaking of pixel-precision: I want to thank Werner for giving me some great advice for certain rendering problems I was having!

Architecture

After some discussion with Thasso, I decided to simplify the original architecture. Before there was too much emphasis on how to let subclasses define their own shape, and not enough flexibility with the fill.

In this version the FilledButtonUI is the parent of all my other ButtonUIs. This object is responsible for the shape of the button area; the subclasses are only interested in the fill and in rendering.

All the UI's can automatically receive Mac-like focus-rings either inside or outside the shape of the button (or both). In the demo, most of the rings are usually outside except in the 3x3 block of buttons: here the focus needs to be painted internally, because external focus wouldn't show in the the centermost cell. If you want to render a customized type of focus, you can override the getFocusPainting() method to return PAINT_NO_FOCUS. (This means that the FilledButtonUI isn't automatically painting focus for you, and you need to implement your own painting code somewhere else.)

Client Properties

In the demo above all the buttons share the same instance of one ButtonUI. The UI looks at client properties in the button to distinguish the shape. To set up a button on the bottom-left corner of a grid of buttons, you can call:

myButton.putClientProperty( HORIZONTAL_POSITION, LEFT );
myButton.putClientProperty( VERTICAL_POSITION, BOTTOM );

(It is assumed if one of these properties is missing that the intended value is ONLY.)

Also these UI's support an arbitrary shape. To create a circular button you can call:

myButton.putClientProperty(SHAPE, new Ellipse2D.Float(0,0,100,100));

(The shape will be scaled until the icon and text of a button fit inside it.)

In the comments below Andre requested something similar to this feature. Personally I only really see myself using this feature for circles, but it is up to your discretion to decide if you think other shapes are a good fit for your UI.

Thasso pointed out the current implementation requires that buttons be mutually exclusive in their layout: if two buttons are supposed to be nestled together in, say, a diamond-like pattern, then the FilledButtonUI is probably not a good choice. While that sounds like a fun design challenge to address (as far as programming is concerned), this project is already very large in scope and I don't want to take on more than I can handle.

ButtonClusters

You probably don't want to comb through your UI and set up client properties for each toolbar to get the correct segment positions. And even if you did: supposed you have a toolbar with buttons A, B, C and D, but then in some situations you made D invisible. At this point button C needs to become the right-most button in the set, instead of being a middle button. Updating this constantly seems like a real chore.

The ButtonCluster object automates this for you. It works under a couple of assumptions, though. It does not directly affect the layout of your buttons: it assumes when you pass it an array of buttons that they will always be adjacent in your UI, and that they will be presented in that order. A ButtonCluster has no way of knowing if that is or is not the case; it assumes that part of your UI is unchanging.

When you create a cluster you can ask it to be standardized or not. (This is largely in response to Michael's comments below.) If this boolean is true, then the FilledButtonUI will arrange each button in a cluster to have approximately the same width and height. This will result in larger GUI components, but with a more balanced look.

Also be sure to check out the static install() methods in the javadoc: that may be the only method you need to call from your code to incorporate this project into your app.

Embellishments

Extras. Eye candy. I added a few to the demo applet just to demonstrate how easy they are.

The blinking focus is built into the FilledButtonUI, and probably required about 20 lines of code. This is a feature a user once requested for accessibility reasons: users who can't easily use a mouse largely rely on keyboard focus to navigate software, but sometimes the focus is so subtle in a crowded interface that it's hard to spot. The blinking really helps the human eye to find the area of interest.

The other two extras (shimmer and zoom) are UIEffects. This is a very simple but fun little class that the FilledButtonUI supports when rendering a button. In a way this model is my attempt to compensate for not having multiple inheritance: I can make easy little effects that I can apply to any and all FilledButtonUI subclasses. (And remember Java2D's most basic drawing tools can go a long way if you're creative.)

Keyboard Focus

Speaking of keyboard focus, that reminds me (can you tell that writing this article is very much a stream-of-consciousness process?)... I'm also very pleased with the FocusArrowListener. This listens for arrow keys, and shifts the focus accordingly when a key is pressed. It's a very simple concept, but I think it should be applied to most GUI elements that do not already have defined behaviors for the arrow keys. JToolBars already do something very similar: pressing the the left and right arrow keys is generally equivalent to pressing tab and shift-tab. (Except when you get to one extreme of a JToolBar the focus will wrap around to the other side.)

Again: thanks goes to Werner for some helpful tips on this element.

Conclusion

The incentive for this project was to get some consistent aesthetic UI's to use in our desktop applications. With that goal in mind I put together what I hope is a relatively adaptable model for all sorts of other button UI's to work from.

I've spent a lot of time on this in the last few weeks and I need to move on for now. If you have improvements/fixes you want to make: the code is in the jar (and available on an SVN server), so you're welcome to do whatever is necessary. And if you email me afterwards I might be able to incorporate the changes into the official release.

As of this writing the features I would most like to add are HTML-support (in text) and a FadingUIEffect. Neither of these should require overhauling anything major: but they are outside my current needs.

Feel free in the comments to talk about your favorite ButtonUI's from other sources/L&F's, and what makes them your favorite.

Approaches to "UPSERT"

This week in the Database Programmer we look at something
called an "UPSERT", the strange trick where an insert
command may magically convert itself into an update if
a row already exists with the provided key. This trick
is very useful in a variety of cases. This week we will
see its basic use, and next week we will see how the same
idea can be used to materialize summary tables efficiently.

An UPSERT or ON DUPLICATE KEY...

The idea behind an UPSERT is simple. The client issues
an INSERT command. If a row already exists with the
given primary key, then instead of throwing a key
violation error, it takes the non-key values and updates
the row.

This is one of those strange (and very unusual) cases
where MySQL actually supports something you will not
find in all of the other more mature databases. So if you
are using MySQL, you do not need to do anything special
to make an UPSERT. You just add the term "ON DUPLICATE
KEY UPDATE" to the INSERT statement:

insert into table (a,c,b) values (1,2,3)
    on duplicate key update
     b = 2,
     c = 3

The MySQL command gives you the flexibility to specify
different operation on UPDATE versus INSERT, but with
that flexibility comes the requirement that the UPDATE
clause completely restates the operation.

With the MySQL command there are also various considerations
for AUTO_INCREMENT columns and multiple unique keys.
You can read more at the MySQL page for the
"http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html"
>INSERT ... ON DUPLICATE KEY UPDATE feature.

A Note About MS SQL Server 2008

MS SQL Server introduced something like UPSERT in
SQL Server 2008. It uses the MERGE command, which is
a bit hairy, check it out in this
"http://www.databasejournal.com/features/mssql/article.php/3739131/UPSERT-Functionality-in-SQL-Server-2008.htm"
>nice tutorial.

Coding a Simpler UPSERT

Let us say that we want a simpler UPSERT, where you do not
have to mess with SQL Server's MERGE or rewrite the entire
command as in MySQL. This can be done with triggers.

To illustrate, consider a shopping cart with a natural key
of ORDER_ID and SKU. I want simple application code that
does not have to figure out if it needs to do an INSERT or
UPDATE, and can always happily do INSERTs, knowing they will
be converted to updates if the line is already there.
In other words, I want simple application code that just keeps
issuing commands like this:

INSERT INTO ORDERLINES
       (order_id,sku,qty)
VALUES 
       (1234,'ABC',5)

We can accomplish this by a trigger. The trigger must occur
before the action, and it must redirect the action to an
UPDATE if necessary. Let us look at examples for MySQL,
Postgres, and SQL Server.

A MySQL Trigger

Alas, MySQL giveth, and MySQL taketh away. You cannot code
your own UPSERT in MySQL because of an extremely severe
limitation in MySQL trigger rules. A MySQL trigger may not
affect a row in a table different from the row originally
affected by the command that fired the trigger. A MySQL
trigger attempting to create a new row may not affect
a different row.

Note: I may be wrong about this. This limitation has bitten
me on several features that I would like to provide for MySQL.
I am actually hoping this limitation will not
apply for UPSERTs because the new row does not yet exist, but
I have not had a chance yet to try.

A Postgres Trigger

The Postgres trigger example is pretty simple, hopefully the
logic is self-explanatory. As with all code samples, I did
this off the top of my head, you may need to fix a syntax
error or two.

CREATE OR REPLACE FUNCTION orderlines_insert_before_F()
RETURNS TRIGGER
 AS $BODY$
DECLARE
    result INTEGER; 
BEGIN
    SET SEARCH_PATH TO PUBLIC;
    
    -- Find out if there is a row
    result = (select count(*) from orderlines
                where order_id = new.order_id
                  and sku      = new.sku
               )

    -- On the update branch, perform the update
    -- and then return NULL to prevent the 
    -- original insert from occurring
    IF result = 1 THEN
        UPDATE orderlines 
           SET qty = new.qty
         WHERE order_id = new.order_id
           AND sku      = new.sku;
           
        RETURN null;
    END IF;
    
    -- The default branch is to return "NEW" which
    -- causes the original INSERT to go forward
    RETURN new;

END; $BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;

-- That extremely annoying second command you always
-- need for Postgres triggers.
CREATE TRIGGER orderlines_insert_before_T
   before insert
   ON ORDERLINES
   FOR EACH ROW
   EXECUTE PROCEDURE orderlines_insert_before_F();

A SQL Server Trigger

SQL Server BEFORE INSERT triggers are significantly different
from Postgres triggers. First of all, they operate at the
statement level, so that you have a set of new rows instead
of just one. Secondly, the trigger must itself contain an
explicit INSERT command, or the INSERT never happens. All of this
means our SQL Server example is quite a bit more verbose.

The basic logic of the SQL Server example is the same as the
Postgres, with two additional complications. First, we must use
a CURSOR to loop through the incoming rows. Second, we must
explicitly code the INSERT operation for the case where it
occurs. But if you can see past the cruft we get for all of that,
the SQL Server exmple is doing the same thing:

CREATE TRIGGER upsource_insert_before
ON orderlines
INSTEAD OF insert
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @new_order_id int;
    DECLARE @new_sku      varchar(15);
    DECLARE @new_qty      int;
    DECLARE @result       int;

    DECLARE trig_ins_orderlines CURSOR FOR 
            SELECT * FROM inserted;
    OPEN trig_ins_orderlines;

    FETCH NEXT FROM trig_ins_orderlines
     INTO @new_order_id
         ,@new_sku
         ,@new_qty;

    WHILE @@Fetch_status = 0 
    BEGIN
        -- Find out if there is a row now
        SET @result = (SELECT count(*) from orderlines
                        WHERE order_id = @new_order_id
                          AND sku      = @new_sku
                      )
    
        IF @result = 1 
        BEGIN
            -- Since there is already a row, do an
            -- update
            UPDATE orderlines
               SET qty = @new_qty
             WHERE order_id = @new_order_id
               AND sku      = @new_sku;
        END
        ELSE
        BEGIN
            -- When there is no row, we insert it
            INSERT INTO orderlines 
                  (order_id,sku,qty)
            VALUES
                  (@new_order_id,@new_sku,@new_qty)
            UPDATE orderlines

        -- Pull the next row
        FETCH NEXT FROM trig_ins_orderlines
         INTO @new_order_id
             ,@new_sku
             ,@new_qty;

    END  -- Cursor iteration

    CLOSE trig_ins_orderlines;
    DEALLOCATE trig_ins_orderlines;

END

A Vague Uneasy Feeling

While the examples above are definitely cool and nifty,
they ought to leave a certain nagging doubt in many
programmers' minds. This doubt comes from the fact that
an insert is not necessarily an insert anymore,
which can lead to confusion. Just imagine the new programmer
who has joined the team an is banging his head on his desk
because he cannot figure out why his INSERTS are not
working!

We can add a refinement to the process by making the
function optional. Here is how we do it.

First, add a column to the ORDERLINES table called
_UPSERT that is a char(1). Then modify the trigger so that
the UPSERT behavior only occurs if the this column holds
'Y'. It is also extremely import to always set this value
back to 'N' or NULL in the trigger, otherwise it will appear
as 'Y' on subsequent INSERTS and it won't work properly.

So our new modified explicit upsert requires a SQL statement
like this:

INSERT INTO ORDERLINES
       (_upsert,order_id,sku,qty)
VALUES
       ('Y',1234,'ABC',5)

Our trigger code needs only a very slight modification.
Here is the Postgres example, the SQL Server example should
be very easy to update as well:

   ...trigger declration and definition above
   IF new._upsert = 'Y'
      result = (SELECT.....);
      _upsert = 'N';
   ELSE
      result = 0;
   END IF;
   
   ...rest of trigger is the same

Conclusion

The UPSERT feature gives us simplified code and fewer
round trips to the server. Without the UPSERT there are
times when the application may have to query the server to
find out if a row exists, and then issue either an UPDATE
or an INSERT. With the UPSERT, one round trip is eliminated,
and the check occurs much more efficiently inside of the
server itself.

The downside to UPSERTs is that they can be confusing if
some type of explicit control is not put onto them such as
the _UPSERT column.

Next week we will see a concept similar to UPSERT used
to efficiently create summary tables.

[Publi-Info] D�couvrez et prenez part au projet Moblin

Comme le savent ce qui suivent ce blog depuis un moment, il n'est pas dans mes habitudes de sponsoriser mon contenu et de vous parler de machines � caf�, de plantes vertes ou de gastronomie sur ce blog dans le but de me mettre un peu d'argent en poche.

J'ai choisi de vous parler de Moblin parce que j'ai moi-m�me r�cement install� Ubuntu sur mon ordinateur, et fait en m�me temps mes premiers pas dans le monde de libre et de Linux, qui m'enthousiasme par la richesse de sa communaut� et par les perspectives nouvelles qu'il ouvre sur l'informatique, enfin en tout cas pour moi qui m'�tait jusque l� cantonn� � Windows.

Moblin est un OS open source destin� aux netbook, t�l�phones portables et MIDs.

Bas� sur GNU/Linux, le projet a �t� lanc� en 2007 par Intel dans le but de devenir la plateforme Linux pour mobile la plus aboutie.

Je pourrais m'�tendre un peu plus sur les objectifs et les caract�ristiques de projet, mais je ne r�siste pas � l'envie de vous d�voiler � quoi ressemble un ordinateur tournant sous Moblin :

On peut remarquer que l'interface prend la forme d'onglets regroupants les principaux logiciels et fonctions de syst�me, ce qui n'est pas sans rappeller l'interface de Xandros sur les eeePC.

Voila cette m�me interface en fonctionnement :

Sympa non ?

Personellement je trouve cette interface tr�s ergonomique, dans le sens ou on peut acc�der rapidement aux fonctions essentielles pour l'utilisateur lambda, telles que le navigateur, le lecteur audio ...

Alors bien s�r cela parait tr�s limit� pour l'utilisateur averti qui ne peut se contenter des fonctions de base de son odinateur, mais cette interface convient on ne peut mieux � des personnes qui ont besoin d'un OS simple et rapide pour effectuer des t�ches basiques dans un environnement graphique chaleureux.

De plus, l'optimisation de Moblin pour les appareils mobiles rend sa consommation en �nergie moindre, vous assurant une automie cons�quente.

Moblin est aujourd'hui en plein d�veloppement par une communaut� grandissante, que vous pouvez rejoindre en vous rendant sur Moblin Zone ou Moblin.org.

Vous pourrez ainsi, si vous �tes d�veloppeur, participer � l'�laboration de Moblin. Et si vos comp�tences en code se limitent comme les miennes � pas grand-chose, vous pouvez t�l�charger Moblin, le tester sur votre propre machine et participer � son d�veloppement en reportant vos impression et en aidant les d�veloppeurs � faire de Moblin "la plateforme Linux pour mobile la plus aboutie".

Article sponsoris�

The Relational Model

If you look at any system that was born on and for the
internet, like Ruby on Rails, or the PHP language, you find
an immense wealth of resources on the internet itself, in
endless product web sites, blogs, and forums. But when
you look for the same comprehensive information on products
or ideas that matured before the web you find it is not there.
Relational databases stand out as a product family that matured
before the internet, and so their representation in cyberspace
is very different from the newer stuff.

The Math Stuff

You may have heard relational theorists argue that the
strength of relational databases comes from their solid
mathematical foundations. Perhaps you have wondered,
what does that mean? And why is that good?

To understand this, we have to begin with
>Edsger W. Dijkstra, a pioneer in the area of computer
science with many accomplishments to his name. Dijkstra
believed that the best way to develop a system or program
was to begin with a mathematical description of the system,
and then refine that system into a working program. When
the program completely implemented the math, you were
finished.

There is a really huge advantage to this approach. If you
start out with a mathematical theory of some sort, which
presumably has well known behaviors, then the working program
will have all of those behaviors and, put simply, everybody
will know what to expect of it.

This approach also reduces time wasted on creative efforts
to work out how the program should behave. All those
decisions collapse intot he simple drive to make the program
mimic the math.

A Particular Bit of Math

It so happens that there is a particular body of math
known as Relational Theory, which it seemd to
>E. F. Codd would be a very nice fit for storing
business information. In his landmark 1970 paper
>A Relational Model of Data for Large Shared Data Banks
(pdf) he sets out to show how these mathematical
things called "relations" have behaviors that would be
ideal for storing business models.

If we take the Dijkstra philosophy seriously, which is to
build systems based on well-known mathematical theories,
and we take Codd's claim that "Relations" match well to
business record-keeping needs, the obvious conclusion is
that we should build some kind of "Relational" datastore,
and so we get the Relational Database systems of today.

So there in a nutshell is why relational theorists are
so certain of the virtues of the relational model, it's
behaviors are well-known, and if you can build something
that matches them, you will have a very predictable
system.

They are Still Talking About It

If you want to know more about the actual mathematics,
check out the "http://groups.google.com/group/comp.databases.theory/topics"
>comp.databases.theory Usenet group, or check out
Wikipedia's articles on "http://en.wikipedia.org/wiki/Relational_algebra"
>Relational Algebra and "http://en.wikipedia.org/wiki/Relational_calculus"
>Relational Calculus.

A Practical Downside

The downside to all of this comes whenever the mathematical
model describes behaviors that are contrary to human goals
or simply irrelevant to them. Examples are not hard to
find.

When the web exploded in popularity, many programmers found
that their greatest data storage needs centered on documents
like web pages rather than collections of atomic values
like a customer's discount code or credit terms. They found
that relational databases were just not that good at storing
documents, which only stands to reason because they were never
intended to. In theory the model could be stretched,
(if the programmer stretched as well), but the programmers
could feel in their bones that the fit was not right, and they
began searching for something new.

Another example is that of calculated values. If you have
shopping cart, you probably have some field "TOTAL" somewhere
that stores the final amount due for the customer. It so
happens that such a thing violates relational theory, and there
are some very bright theorists who will refuse all requests
for assistance in getting that value to work, because you
have violated their theory. This is probably the most shameful
behavior that relational theorists exhibit - a complete
refusal to consider extending the model to better reflect
real world needs.

The Irony: There are No Relational Databases

The irony of it all is that when programmers set out to build
relational systems, they ran into quite a few practical
downsides and a sort of consensus was reached to break the
model and create the SQL-based databases we have today.
In a truly relational system a table would have quite
a few more rules on it than we have in our SQL/TABLE based
systems of today. But these rules must have seemed
impractical or too difficult to implement, and they were
scratched.

There is at least one product out there that claims to
be truly relational, that is "http://en.wikipedia.org/wiki/Dataphor">Dataphor.

The Weird Optional Part

Probably the grandest irony in the so-called relational
database management systems is that any programmer can
completely break the relational model by making bad
table designs. If your tables are not normalized, you
lose much of the benefits of the relational model,
and you are completely free to make lots of
non-normalized and de-normalized tables.

Conclusion

I have to admit I have always found the strength of
relational databases to be their simplicy and power,
and not so much their foundations (even if shaky) in
mathematical theory. A modern database is very good
at storing data in tabular form, and if you know how
to design the tables, you've got a great foundation for
a solid application. Going further, I've always found
relational theorists to be unhelpful in the extreme in
the edge cases where overall application needs are not
fully met by the underlying mathematical model. The
good news is that the products themselves have all of
the power we need, so I left the relational theorists
to their debates years ago.