Technical Notes

My online notepad

  • Social

  • Support

    Donate towards my web hosting bill!

SQL: Find a Row That You Don’t Have

Posted by Anton Khitrenovich on July 28, 2016

LEFT OUTER JOINSeveral years ago I had to create Oracle DB structure for one of my side projects. One of the data types to be stored contained results of certain observation. Each object had some meta-information (like timestamp, name of the observer, location and so on) plus dynamic collection of various key-value pairs – zero or more per observation. I went with the classic “one to many” pattern – main table with observation metadata and additional table for key-value pairs, connected via observation ID. And this worked pretty well for some time…

+----+-----------+-----+	+--------+-----+-------+	
| ID | TIMESTAMP | ... |	| OBS_ID | KEY | VALUE |
+----+-----------+-----+	+--------+-----+-------+
|  1 |           |     |	|      1 | A   |   123 |
|  2 |           |     |	|      1 | B   |   456 |
|  . |           |     |	|      2 | A   |   ... |
|  . |           |     |	|      . |     |       |
+----+-----------+-----+	+--------+-----+-------+

Recently I worked on the next version of that tool. One of the requests that I got was less trivial than others – “we need to find all observations in given time period that do not have key-value records with some given key”. It took me time to recall about outer joins (I rarely work with databases directly nowadays), but the initial version of the query was ready almost instantly:

SELECT FROM observations obs 
   LEFT JOIN obs_values v ON 
   WHERE key is null

Unfortunately, this worked only for observations that did not have any key-value pairs at all – the rest of the records had real data rows on the right side of the joined table and did not match the NULL key condition. So, the final version was bit more complex and took much more time to produce. I had to “enrich” main table with additional column containing the key in question (see line 2 below) – and only then I was able to use proper left outer join on both record ID and the key, which resulted in “null” row parts for records with no key in the values table.

   (SELECT *,? as key from observations) obs 
   LEFT JOIN obs_values v ON and obs.key=v.key 
   WHERE v.key is null

It was fun to recall a bit of Oracle world, but I’m glad I don’t have to deal with that in my everyday work 🙂

Posted in HOWTO | Tagged: , | 5 Comments »

Eclipse: Configure HTTP Proxy Settings for All Run Configurations

Posted by Anton Khitrenovich on August 13, 2015

There are many cases nowadays when you have to go through HTTP proxy in order to access the internet. Eclipse provides an option to configure HTTP proxy settings – either by manual configuration or loading native system settings. (It does not support auto-configuration scripts, though, so if this is the way you configure your OS, you’ll have to provide proxy address to Eclipse manually.)

However. the settings configured via Eclipse preferences are not propagated to the Java programs that you develop. Obviously, you can provide proxy settings via VM arguments, but doing that for each and every run configuration may be painful. Fortunately, this can be solved with the help of “Default VM Arguments” setting under JRE/JDK definition. System properties configured there will be in effect for every Java program you are going to run or debug. Read the rest of this entry »

Posted in HOWTO | Tagged: , , | Leave a Comment »

Summary of Cache-Related HTTP Headers

Posted by Anton Khitrenovich on March 11, 2015

Long ago (long before the first post in this blog!) I’ve composed a list of cache-related HTTP headers, so I would not need to go through the trial-and-error process of guessing the right combination more than once. Recently I got another question about caching and it took me a lot of time to recall where I saw this list last time. So now I’m placing it here.

Please treat the explanations below as quick and incomplete summary. For full specification of “Pragma”, “Cache-Control” and “Expires” headers refer to HTTP/1.1 specification.

Caching in HTTP 1.1

Following directive does not prevent caching despite its name. It allows caching of the page, but specifies that the cache must ask the originating web server if the page is up-to-date before serving the cached version. So the cached page can still be served up if the originating web server says so. Applies to all caches.

Cache-Control: no-cache

Following directive tells the browser that the page has expired and must be treated as stale. Should be good news as long as the caches obey.

Expires: Thu, 01 Jan 1970 00:00:00 GMT

Following directive specifies that the page contains information intended for a single user only and must not be cached by a shared cache (e.g. a proxy server).

Cache-Control: private

Following directive specifies that a cache must not store any part of the response or the request that elicited it.

Cache-Control: no-store

Following directive tells the cache that the maximum acceptable staleness of a page is 0 seconds.

Cache-Control: max-stale=0

Caching in HTTP 1.0

Following directive is the only cache control directive for HTTP 1.0, so use it in addition to any HTTP 1.1 cache control headers you include.

Pragma: no-cache

Read the rest of this entry »

Posted in HOWTO, Miscellaneous | Tagged: , | Leave a Comment »

How to Use CDATA in Spring Configuration Entries

Posted by Anton Khitrenovich on February 15, 2015

Today I had to create a map in Spring XML configuration file, where both keys and values ought to be XML elements by their own. Obviously, using CDATA is the most readable way to achieve that – but it was not immediately clear how to use CDATA for entry’s key and value attributes.

Here is what I ended with:

<util:map id="patterns">


Posted in HOWTO | Tagged: , , | Leave a Comment »

How to Download Eclipse Update Site for Offline Use

Posted by Anton Khitrenovich on January 26, 2015

Unlike the early Eclipse days, now most of Eclipse plugins are distributed via Update Manager. Installation of new software is only a few clicks away – unless your development environment is not connected to the internet! Some vendors publish update site archives for offline use, but most of them are not. Eclipse tries to provide a solution for that with site mirroring, but is not as easy as it could be and forces you to install full-fledged Eclipse on a machine connected to the internet. So, what can you do about it?

Let’s take run-jetty-run – excellent plugin that allows you to run Jetty in Eclipse with a single click, including source attachment for debugging, – and prepare an offline update site for it. Read the rest of this entry »

Posted in HOWTO | Tagged: , , , | Leave a Comment »