A closer study uncovered the pretty obvious reason. It also turned out to be the key to zippier Neo4j performance.

Perhaps I should explain my seeming obsession with Neo4j. Once upon a time I was part of the development of an “object oriented” database system, doing much of its design. Quotes around “object oriented” because it was really a graph database in today’s terminology. It was written in C, having strong schema support. C plus schema support made data access extremely fast, a matter of a handful CPU cycles. EasyDB, the current name, has been further developed and is still available from Basesoft. It is a NoSQL database, although the term didn’t exist at the time it was engineered.

The key to Neo4j performance is memory. I reran the test cases with the Java memory options -Xms128m -Xmx1g, allowing the JVM to expand to 1 GB of heap space rather than the default 64 MB.

With these memory settings scanning 115.735 files and writing file information to the database became 35 times (!) faster with Neo4j than Apache Derby or PostgreSQL. Neo4j quickly claimed all of the 1 GB heap space, actively using up nearly 800 MB, almost 8 kB per node. Despite this remarkable performance it still spent a large fraction of the time doing MarkSweep garbage collection.

In comparison, Apache Derby had a modest memory footprint: under 100 MB and only Scavenge garbage collection.

MarkSweep garbage collection is very time-consuming. It seems Derby has logic to avoid incurring it. Neo4j has a tendency to trigger MarkSweep whenever it comes even remotely near the memory limit. It may escape the fatal OutOfMemory, but performance suffers badly.

As for reading the database, more memory didn’t change the relative performance figures a lot. Derby and PostgreSQL stand up well against Neo4j, using a fraction of the memory. A few observations:

• Neo’s getAllNodes uses a lot of memory, 600 MB in this test, and seems prone to triggering MarkSweep.
• The Groovy overhead became visible as a side effect of monitoring the test programs. Only Derby spent a sizable amount of time (20%) in Groovy-specific code. For Neo4j, garbage collection dwarfs the Groovy overhead.

Let it be perfectly clear that the reading test case for Derby and PostgreSQL involves only one or two queries. I think it is relevant to compare row-to-row table scan in the relational databases to node-to-node traversal in Neo4j.

In my test cases the data model could be adapted to the problem. The relational databases only had to use one or two queries. There might be situations when this is not possible. In such case the hierarchy must be traversed with one query per node, with a devastating performance hit for the relational databases. Only a specialized database like Neo4j keeps its performance without a flinch.

Software: Neo4j 1.1, Apache Derby 10.6.1.0, PostgreSQL 8.4.4, Groovy 1.7.4, Java 1.6.0_21. Platform: 64-bit openSUSE 11.3 on a humble Dell box with a Pentium Dual-Core CPU E5300 @ 2.60GHz CPU and 2 GB of memory.

Previous posts:

• Network versus Relational
• Network versus Relational: Part II
• Network versus Relational: Part III

A U-city is a city where you want to live, by definition. South Korea has seen an astounding rate of urbanization. A large part of the country’s population lives in Seoul, a megacity. No wonder building attractive and sustainable cities is a hot topic in Korea. Large-scale projects are underway to take principles to practice.

The Korean U-city concept includes a strong technology component. Information technology is regarded as a utility that is woven into the infrastructure from the beginning, just like water and electricity. This is not without reason. Korea has been very successful in building an IT industry, winning a big chunk of the world market. It is one of the pillars underlying Korea’s current high standard of living.

President Lee, who took office in 2008, quickly proclaimed “Low Carbon, Green Growth” as the national vision. The vision has been picked up by the Korean Ubiquitous City Association. The most prominent U-city keywords are green (referring to environmental considerations) and smart (referring primarily to information technology).

In Europe the “U”/”ubiquitous” terminology is a barrier to understanding concepts like U-city. This terminology is unique to Korea and Japan. See the U as in Ubiquitous blog category for more information.

The Korean U-city concept envisions tight monitoring of public resources and the public space. In my opinion Europeans are more apprehensive of such surveillance than Koreans.

Resources: The U-City World Community is a new Korean web site promoting the U-City World Forum.

If you are wrestling with this problem, and if you are on a Linux/Unix platform, download the filestat package from this website and get going. This post introduces the package briefly.

My interest was spawned by an excellent post: How to deal with filesystem softlinks/symbolic links in Java. It contains complete code for a native Java method to determine if a file is a symbolic link.

It was a long time since I did any JNI, so I felt inspired to dig into the more general problem of returning all of the C struct stat data to the Java level. The resulting FileStat Java class lets you access every bit of it.

If you wonder about the name, ’stat(2)’ is the system call used in Linux/Unix to get information about a file. There is also a similarly named command ’stat(1)’ that you may enter on the command line. It produces text output for human consumption.

On this level it is important to know about hard and soft (or symbolic) links. In a Linux/Unix file system any file is identified by its inode number. A file name is a hard link to an inode. Any number of names may be linked to the same inode. For instance, every directory contains entries named “.” and “..”. These are not shell conventions, but real file names, i.e. hard links. You may say that a Linux/Unix file system is a bag of inodes. Its structure is only determined by storage considerations. The directory tree structure we think is so essential is introduced by the file names linked to the inodes.

A symbolic link is a pointer to another file or directory. More exactly, it is a file containing the name of another file. It has a low level flag to indicate that this is a symbolic link. The flag may be accessed from Java through the filestat package.

Directories work in a similar way. A directory is just a file with a low level flag that indicates that its contents should be interpreted as a directory.

Another low level flag may indicate that a file is a socket. This kind of socket is a Unix domain socket, a device for interprocess communication. It is similar to network sockets, but works only for local processes. A Unix domain socket appears as a file in the file system, so it is natural that it can only be accessed locally. Unix domain sockets are heavily used by the KDE and Gnome desktop environments, for example.

In Linux/Unix devices appear as files, usually under /dev. This is a powerful metaphor. Want some high quality random numbers? It’s as easy as typing cat /dev/random. This example is contrived, but it shows the power of the concept. No special programming is necessary. Another example is /dev/null, the bottomless pit (or empty input). It may be used from the command line as well as programmatically.

The filestat package lets you check all those low level flags from Java. It also lets you access two file timestamps in addition to the last modification time present in java.io.File. The timestamps contain the time of last access (atime) and the time of the last status change (ctime). A status change is when you change the owner or the protection of a file, for instance. All timestamps are available with nanosecond precision, for what it’s worth.

The filestat package also contains a class named TestFileStat. You may use it from the command line. Give it one or more file paths. For each path it first prints the result of invoking FileStat methods. Then it runs the stat(1) command and prints its output for comparison.

The only caveat is that the filestat package comes to you as source code. It contains native methods, so you must compile the package on your own box. You need make and gcc (or some other C compiler).

When I say “the model” I mean the conceptual model that is the foundation of every database application. The model is usually a judicious simplification of the real world. Take an order entry system, for example. The model may stipulate that there are Products, Customers and Orders and relationships between them. This is an extremely narrow view of a business, but it may be ok for a model. The model bridges the gap between data and information. It gives meaning to the bytes stored in a database. Programmers continuously use the model when they code an application. The model is the crown jewel of every database application.

A modern relational database systems offers several standardized mechanisms to support a conceptual model. You may tell it to check value ranges and check relationships. You may create indexes to check that some value is unique or to speed up selected types of queries. In their limited way, these mechanisms help protect the database from storing garbage.

So why would anyone be against having their model supported by the database system? No one, if it wasn’t for one very disturbing fact about conceptual models: They evolve. All models in practical use change. If the model changes, and here is the crux of the matter, so must the schema.

High-end relational database systems support many types of schema changes. Adding or dropping a column is seldom a problem. Adding an index is straightforward. In theory. In practice, if a table has half a billion rows, creating an index may take a full day. It’s doable, but it takes so much time that it may interfere with normal operation.

Schema changes in large databases can be a serious pain. Schema-less databases don’t have a schema, so is the pain gone? The answer is no, the integrity of the conceptual model still has to be protected. The burden is shifted from the database system to applications. More code must be written because the database system no longer stops a buggy application from filling the database with nonsense. Application development time increases.

In my opinion “schema-less” is not a feature but a pragmatic trade-off to achieve performance in certain very large databases at the cost of longer development time.

In the specific case of Neo4j the almost total schema-less-ness seems counterproductive in my opinion. Every node in a database may be different. An application must check every node it retrieves to find out if it has the expected attributes, leading to performance loss. In practice every application will invent its own way of managing node types. The database system would do this more efficiently than applications. Versions of node types could be used to retain flexibility.

In conclusion, a schema-less database brings you freedom and flexibility in the same sense that driving without a safety belt does. In some special applications you may be forced to sacrifice schema support due to overriding performance concerns. Mainstream applications should use all schema support they can get.

I’m sure this won’t happen anytime soon. On the other hand, I don’t believe NoSQL databases will go away either. My prediction is that new NoSQL databases will keep popping up. Each one will fill a specific need for a time, and then fade away. No two NoSQL databases will ever be compatible. Standardization is fundamentally contrary to the reason why NoSQL databases exist.

Hang on and I will show you why.

Cutting Time to Market
Let’s start here: The only thing that matters in mainstream application development is time to market. Even code quality is sacrificed to have an application hit the market before the competition. This means developer productivity is a top priority. Write as few lines of code as possible to get the job done.

The main way to improve developer productivity is to raise the level of abstraction. Figuratively speaking, you don’t want to build the house from nails and a heap of boards. What you want is prefab modules. In computing it means you no longer code in C. You use Java or some other contemporary language that reduces the code volume by a significant factor.

Abstractions
Progress in the art of programming is all about creating abstractions. They are the prefab modules of programming. Object orientation, message passing, automatic memory management, dependency injection: all such concepts offer abstractions with one goal, to help developers get more bang per line of code. Abstractions certainly don’t help the computer. When the code hits the CPU almost all traces of classes, interfaces and methods are gone. There is only a stream of primitive byte-shuffling instructions. Abstractions are strictly for human consumption.

The principle of the highest possible level of abstraction also applies to data, but may be less obvious. People have generally left C, but you may still run into projects that claim that they “just need something simple” for managing data and that the file system will do.

The Relational Data Model
The currently highest level of abstraction in data management is the relational data model. This is true despite the fact that the theory was proposed in the 70’s. Its solid mathematical foundation makes a lot of difference. Its most notable abstraction is the possibility of a non-procedural query language that decouples applications from their data store.

Note: SQL is not inherent in the relational model. There have been other relational query languages, but SQL ended up as an ANSI standard.

The high level of abstraction was a problem at the time the relational model was proposed. As an illustration, Ingres was a pioneering relational research effort. It typically ran under Unix on PDP-11. It had to be partitioned into several interconnected processes because the PDP-11 (a 16-bit architecture) did not support processes larger than 64 KB (that’s right, kilobytes). This was a long time ago, well before the IBM PC era.

The feasibility of relational databases was questioned for decades. One of the most prominent contenders back then was the so-called CODASYL databases. The network data model was specified by CODASYL by the end of the 60’s. I mention it because modern-day databases like Neo4j have a quite similar data model. It’s one of the oldest ideas in database technology.

You may have heard about the so-called impedance mismatch. It means that the data structures of a programming language, Java for example, cannot be immediately translated to relational table rows. Most people blame the relational data model, but I also find fault with programming languages. The data structures of current programming languages are largely low level abstractions based on one-way pointers (lists) and one-way associations (maps). They have stayed essentially the same for decades so there should be room for new levels of abstraction.

Quantum Jumps in Computing
Note that doubt was cast on Java for performance reasons during its inceptive years. It was believed to be mainly a tool for building applets (client side).

Today a real-time telecom system may well be implemented in Java (server side) and backed by a relational database. A real time billing system, for instance, may handle millions of subscribers and generate gigabytes of billing records every day. It took a while, but today there is no doubt that the relational model and Java are feasible for many demanding purposes.

My point is this: A quantum jump in computing begins with conjuring up a level of abstraction that is not yet feasible. Initially people will complain that it’s a terrible waste of CPU cycles and that performance sucks. Given time there will be enough CPU cycles and memory and gradually everyone will use the new abstractions to improve their productivity.

Stretching the Limits
So much for mainstream applications. At any given time there are people who stretch the limits. This was true about electronics CAD systems in the 80’s – the relational databases of the day could not handle them. It is true now. For instance, there are extremely visible web applications with millions of users spread around the globe. Relational databases just won’t cut it.

The challenges are different at different points in time, but usually a combination of two principles are used to solve those hard cases,

• Give up finding a general solution and solve only the relevant special cases
• Take the hit of accepting lower levels of abstraction

For instance, if you are not in banking or telecom billing you may find that your application can do without airtight transactions. Eventual consistency may be good enough. Perhaps some components have to be coded in C after all. Perhaps you invent a way of partitioning the problem over a thousand computers in parallel. The important thing is to get the job done. The flip side of leaving the beaten track is a sharp increase in the volume of code you must produce. The companies behind the million-user web applications have the resources to do it.

NoSQL Databases
Most of the NoSQL databases we see are designed to gain some desirable characteristic (performance, scalability etc.) at the expense of not being general-purpose or operating at a lower level of abstraction. You may find that your application needs one of them to satisfy extreme requirements. Then go for it and accept that you will pay a price in terms of more developer hours. A few years down the road the trade-off will be different. General-purpose databases will manage more complex stuff which means that the previous generation of NoSQL databases will be less needed. New ones will be necessary for those who stretch the new limits.

NoSQL databases may be extremely capable. They are temporary fixes nonetheless. They get their edge by not trying to solve all the problems that contemporary relational databases do and/or by operating at a lower level of abstraction. Each one strikes its own trade-offs. You should find out what they are.

Maybe I’m unfair. Maybe some NoSQL databases build on new theory and not just on pragmatic trade-offs.

Maybe one day we will see a data model on a level of abstraction higher than the relational one. For instance, I have waited a long time for “table” to appear as a valid column data type in a new strain of relational databases. It sounds conceptually simple but is very disruptive. The query language as well as fundamental principles of storage organization are challenged.

Summing Up
In summary, when selecting a database system keep your head cool and don’t make decisions based on hype. Rational thinking still works. That and hands-on testing will help you steer away from costly database mistakes.

How about running the same tests with trusty old PostgreSQL? Neo4j and Apache Derby were both embedded in the test application. There was no database server, although Derby may be run in client-server mode.

PostgreSQL is a fully featured RDBMS firmly founded on the classical client-server architecture. It’s been around for ages. The server is coded in C.

The PostgreSQL logo is a stylized head of an elephant. If you think it’s a symbol of heavy feet you are in for a surprise.

I installed PostgreSQL 8.3.9 on the box where the previous tests were run.
Tuning PostgreSQL is an art in itself. It’s a big system with many parameters. To avoid getting stuck in configuration I decided to run PostgreSQL with default configuration, no tweaking. Server and client ran in the same box. The test programs needed only a minor touch-up.

I was in a hurry, so I ended up having data on the OS disk. Neo4j and Derby had the luxury of storing data on a dedicated database disk.

The effect of caching in the PostgreSQL server is very visible. Repeating a test improves the test case performance. Measurements were made after stopping and starting the server. In a real application, of course, you want the benefits of server-side caching.

Create database: PostgreSQL performance was about the same as Apache Derby in the small data sets, but noticeably faster in the long-running ones. Here is the table from the original post complemented with PostgreSQL but without the database size columns.

Files	Time (s)
	Neo4j	Derby	PostgreSQL
7 109	4.8	13.4	12.1
53 378	16.4	51.0	53.7
520 691	–	479.8	408.9

Neo4j shines in this test, except that it had a problem with large transactions.

Retrieval: PostgreSQL wiped the floor with Neo4j and Apache Derby. The original test on a scan containing 524.000 files took less than 0.8 s. with 1.2 million records in the file table. The modified test case, testing for inclusion in a subtree, took 0.3 s, still with 1.2 million files in the database. This is an order of magnitude faster than the Java databases. I double-checked that Postgres hadn’t created indexes behind my back, but no.

C is still faster than Java. PostgreSQL left the Java databases in the dust in the retrieval tests.

The retrieval test was a surprise because relational Derby seemed to perform better than Neo4j.

Anders Nawroth, Neo Technology, correctly commented that the retrieval test case searches for any file in a hierarchy, i.e. the search is independent of the hierarchy. The relational test program took advantage of this fact while Neo4j traversed the the graph.

In the interest of fairness this post digs somewhat deeper into the retrieval test case. The results are intriguing.

To force the retrieval test to take the hierarchy into account, let’s restate it this way: Find the oldest file bigger than 5 MiB in a given subtree of a given scan.

For Neo4j the revamped test case makes no big difference. It’s still a matter of graph traversal.

However, the idea of traversing a hierarchy with a relational database makes me cringe. A general solution implies one query per parent node to find the children. Performance is awful. In real life I would find a way to avoid it. (Note that I already did because every file node has a pointer to the scan it belongs to.) In the revamped test case I prefer to check the file path to find out if it belongs to the given subtree. I must give up my NoSQL ambitions and use a query like this,

SELECT * FROM file WHERE scan = ? AND size > 5000000 AND path LIKE '/usr/local/%' ORDER BY mtime

The result is ordered, so the answer is the first row of the result set. Before running this test I added another file system scan to the database, rooted in /usr, increasing the number of records of the file table to 1.15 million.

The revamped test case applied to the /usr/local subtree of the /usr scan took Derby 3.7 s. It may be compared directly to Neo4j running the original test case on a scan rooted in /usr/local (3.6 s). Derby still stands up to the competition.

Now let’s turn the test case around a different way. In the original test we allowed Derby to do a plain table scan. A similar approach may be used with Neo4j. It has a complementary access method (getAllNodes) for iterating over all nodes regardless of their position in the graph.

There is an important difference between Neo’s getAllNodes and a relational table scan. Neo4j is a schema-less database (except for relationship types). The getAllNodes method iterates over the entire database. In the original test program the type of a node is implied by its position in the hierarchy. With getAllNodes there is no way to determine the position of a node returned by the iterator. The solution is to add a node type attribute to every node. We also have to tag every file/directory node with the scan it belongs to, just like the relational schema.

(Note in passing: There is no such thing as a schema-free database. The question is only who does the job, the database system or you. But that’s the subject of another post.)

For every node in the iteration we must now check the node type and the scan. The search time rose to somewhere around 9 s, worse than graph traversal. The database contained 430.000 nodes at this point. Just iterating over all nodes without accessing any attributes or relationships took 3.0 s.

Derby, on the other hand, solved the original problem in 2.5 s having 1.150.000 files in the database. Finding the oldest file over 5 MiB among 521.000 files took 4.7 s (still with 1.15 million files in the database). Both cases involved at least one comparison for every table row.

Conclusion: I really cannot convince myself that Neo4j graph traversal is faster than Derby table scans. Database creation turned out to be 3 times faster than Derby, but most applications have many more reads than writes, making basic retrieval very important.

Disclaimer: This judgement is based on a toy example and does not necessarily apply to your application. Please make your own tests before doing anything expensive. Please also tell me if you find evidence contrary to this test.

In this test I managed to avoid hierarchy navigation with the relational database. Of course there are applications where this just isn’t possible and a non-relational database is the only way to get acceptable performance. I’ll look into this general issue in an upcoming post.

The test programs have been presented in previous posts. This is a link to the Neo4j test case. Here is a link to the Derby test case. The task is to create a database representing a file system hierarchy and then to find the oldest file with a size greater than 5 MiB from the database. A deep hierarchical data structure was chosen for the test because that’s where network databases are expected to shine. Relational databases, on the other hand, are challenged by deep hierarchies.

Any database evaluation is flame war fuel. A few disclaimers might ward off some of it.

The simple test case used here is like peeping through a keyhole. What you see is real but very limited. There is a lot more to evaluate. Fundamentals not tested here include managing concurrency and making efficient use of multi-core CPUs.

We measure performance. But seriously, if performance really matters one should not use Groovy.

The testing environment is summarized at the end of this post.

First task: Create a database. Here are the measurements. The Files column contains the number of files scanned. This is also approximately the number of database records created. The execution time includes opening and closing the database, a significant portion of the total execution time, especially for Neo4j.

Files	Time (s)		DB size (MB)
	Neo4j	Derby	Neo4j	Derby
7 109	4.8	13.4	2	1
53 378	16.4	51.0	15	13
520 691	–	479.8	–	121

The table shows that Neo4j was around 3 times faster than Derby in the two first cases. In the biggest test case Neo4j blew up with an out of memory error. One may argue that creating half a million records in a single transaction is unreasonable, but Derby did it without complaining. Neo4j also uses slightly more disk space for its databases.

Second task: Find the oldest file with a size greater than 5 MiB. Neither database uses any index, linear scan is expected. The test was only run on a database containing 53 378 records because of the Neo4j memory problems with the bigger database. Timing in this case only includes the search. Opening and closing the database is not measured. The timing is an average of a few runs.

Neo4j: 3.6 seconds
Derby: 2.2 seconds

In this task Derby outran Neo4j, a very surprising result. One possible explanation is that in the Derby test program the innermost loop never loops. It is converted behind the scenes into a single SQL query. The corresponding loop in the Neo4j test program is executed literally. Even so I find it a bit surprising that network traversal in Neo4j appears slower than a table scan in Derby.

In summary Neo4j was much quicker writing a database, but Derby unexpectedly won the retrieval test. If I can find some time I will dig deeper into this and follow up with a later post.

Testing environment: OpenSUSE 11.1 64-bit Linux on a vanilla HP xw4400 workstation with an Intel Core2 CPU 6400 at 2.13 GHZ and 4 GB of memory. More software versions: Groovy 1.7.2, Java 1.6.0. Default Java memory settings.

However, to Koreans and the Japanese ubiquitous has a different meaning. The double semantics of this word is little known. Since I couldn’t find any previous work on this subject I recently wrote an article about it, now published in the proceedings of the ICISA 2010 conference.

ICISA 2010 (The International Conference on Information Science and Applications) was held in April 2010 in Seoul, Korea. The Icelandic ash cloud did its best to shut out all European delegates. I may have been then only one slipping through.

The title of my contribution is: Linguistic Aspects of Ubiquitous Computing: On “ubiquitous” in Japanese and Korean Information Technology. So what is a linguistic paper doing in a conference on information science? It may seem like a long shot. In my opinion, if you are Korean or Japanese, the paper contains things you need to know to successfully promote IT concepts and products abroad. If you are not Korean or Japanese the paper contains a background you need to understand Korean and Japanese IT.

The key to resolving the language issue is to realize that ubiquitous has become a loanword in the Korean and Japanese languages. Loanwords are written in Korean and Japanese scripts (Hangul and Katakana, respectively). In this form their existence is somewhat decoupled from their English origin. The original phrase ubiquitous computing has been subject to lexical truncation and semantic shift. See the paper for details.

A draft version of the paper is available in the download area. The copyright of the conference proceedings is held by IEEE. The published article is available from IEEE Xplore for a fee (USD 30).

Now it's time for the relational combatant to enter the scene: Apache Derby.
We will write another Groovy program to store file system data, this time using a Derby relational database. To make the task more interesting we will try to earn a "nosql" medal by not using SQL.

Derby is a relatively new player in the database arena. I chose it mainly because I haven't used it before. The solid documentation makes a good initial impression by presenting a quite feature complete SQL/JDBC database.

The database was defined like this (nosql has to wait a minute, this is nothing but SQL):

We will use this schema to store file system scans in the scan table. Every file or directory in a file system scan will add an entry to the file table. Every file and directory except the top directory is linked to its parent directory through the parent column.

Scans and files are uniquely identified by an integer key. A specific file may occur more than once, but in different scans, so the file path alone is not unique.

The schema uses a common trick for managing hierarchies in a relational database: cross-level links. In this case every file will have a direct link (the scan column) to the scan it is part of.

Here is a Groovy program that scans the file system and writes the database.

The program expects a directory path on the command line. It will traverse the file system beginning with that directory and store file and directory data. Like the Neo4j equivalent it does everything in a single transaction.

You will find the main logic in the recursive collectDir method. It runs through all entries of a directory. If an entry is a directory it invokes itself. Otherwise it just adds a record to the file table.

The "nosql"-ness comes from using Groovy DataSet. DataSets have no way of picking up values assigned to autoincremented columns, so there is a tiny bit of SQL after all. We also had to avoid autoincrement and manage record ids explicitly. This detail takes away some of the Groovy elegance.

After making a few scans it is time to retrieve data. The obvious choice is to use ij that comes with Apache Derby. It is an interactive, command line tool. The beauty of JDBC is that there are many other JDBC-compatible tool we could use, some with a graphical user interface.

Listing scans and asking for the oldest file with a size > 5000000 looks like this (with a scan id provided in the second statement):

However, this is supposed to be a head-to-head showdown, so we also provide Groovy code to do the same thing.

The code takes some explanation. The listScans method is obvious enough, but how about findFile?

The findAll call in the findFile method looks like a procedural statement where we iterate over the tuples of the file table. Maybe you don't believe this, but the two closures (in findAll and sort) are never executed! They are compiled into a SQL statement which is run by firstRow. This is stunning nosql.

Well, to arrive at the elegant code we have to go through some SQL hoops after all. (Maybe someone out there can show me a better solution.) The only values allowed in the findAll conditions are literals. So we define a view for the query to operate on. This is done in the createView method. It is possible to use exception handling to check if a view exists, but there will be an ugly message on the console even if you catch the exception. In addition, strings containing ${...} (GString) are lazily evaluated and intelligently replaced by SQL parameters by DB.execute. So it is necessary to convert the GString to an ordinary String before submitting it as SQL because the view name cannot be a parameter.

In summary we have created almost nosql counterparts to the programs exercising Neo4j in the previous post. Running the last program yields,

/usr/local/thunderbird/thunderbird-bin size 12403548 modified 2006-12-07 09:05:44.0

With a sigh of relief it we note that this is the same file found by the Neo4j retrieval program. The modification time format differs only by including fractional seconds. The reason is that we use the Derby Timestamp datatype without formatting.

The two combatants are finally ready for the final fierce, no-mercy showdown.