Unit testing Relational Queries (SQL or HQL or JQL or LINQ...)

Hi!
Most applications I have built have something to do with a database (I remember that while I was on college I used to think that was not exiting stuff, I used to dream about doing neural networks stuff, logic programming in Prolog, etc) but then I met WebObjects and its Object Relational Mapper (Enterprise Objects Framework) and I got really excited about object oriented data programming... but I always had a problem... to test my object oriented queries I had to "manually" translate them into SQL and test them against the database, and only after they give me what I thought were correct result I would write them using EOF Qualifiers...
Then I met Hibernate's HQL and I realized it is much more powerful than EOF Qualifiers, but I still had to translate it to SQL to test it, I know I can get the SQL that is generated from the HQL from the debug console, and paste it in my favorite SQL editor... but even then if I found a mistake, a lot of times it was easier to tweak it in SQL and the manually translate it HQL.
Currently, there are some extensions for Eclipse (Hibernate Tools) that make this more "direct" but, what if I don't like (or don't want, or can't) use Eclipse... it would be great if someone could, for example, make a plugin for SquirrelSQL, but until then... what options do I have?

Then I learned about unit testing... and the answer came to my mind immediately: I just had to write a unit test for each of my queries. That worked fine... in the beginning... until I started having queries that returned thousands (or millions) of objects, and it wasn't such a good idea to output them to the debug console... and I had another problem... how should I write the "asserts" of query?... and how can I do it so that it doesn't make my test so slow that it becomes unusable? (I can, of course, check the results just by viewing them, but my brain is not that good to say if those 10,000 row really match with the idea I had when I wrote that HQL)

So, I started to look "what do I do" to check if an SQL query is correct, lets say for example, that I write this:

select count(*) from Address,Employee where Address.Id= Employee.AddressId and Employee.Id = 3

(Translated to English: How many address the Employee with Id = 3 has?)

Now... how do I test that? well I could add an assert after getting the result in java (or c#) like this:

assert(count>0)

But the, what happens if someone deletes the row with Id = 3 from the table Employee? That means my test will fail... or what what if someone deletes all the addresses from employee? and what if I want to test that if there are no addresses for an employee, the answer should be zero...

That is a lot of work just to test if that simple query is right... and I think that work could be done automatically:

Take a look at the SQL, it could be decomposed into:

select count(*) from (select * from Address,Employee where Address.Id= Employee.AddressId and Employee.Id = 3) as EmployeeAddresses

And then we could say, lets automatically check for the case when the resulting set is empty, and for the case when the result is not empty, to check for a case when the result is empty, we need and Employee with Addresses, so we generate:

Select * from Employee where exists(select * from Address where Address.EmployeeId = Employee.Id)

And take the Id first employee we get... and that should give us a non empty set if used in the original sql sentence that we are trying to test... after that, we automatically generate:

Select * from Employee where not exists(select * from Address where Address.EmployeeId = Employee.Id)

And take the Id first employee we get... and that should give us an empty set if used in the original sql sentence that we are trying to test...

I call this queries "inverses" of the original one, it like when one is testing a multiplication, to see if 2 x 3 = 6, just do: 6/3 = 2 and 6/2 = 3, if 2, and 3 match the operands of the multiplication, you multiplication is right. The same thing goes for SQL, one just has to find the way to "invert" it, if I could automate this inversion, the automatically generated queries would help me by telling me things that might not be immediately obvious to me when I look at the original query, and that would help me check if my original query is right.... it would me some kind of "invariants" that would help me to better understand my querying... or maybe I could even write the invariants first, and then create a query and see if it matches my invariants...

Mmmm.... maybe using a select there is another way to "invert" a query to test if it is right, using the actual inverse operation of selecting... that is "inserting", could I derive from:

select count(*) from Address,Employee where Address.Id= Employee.AddressId and Employee.Id = 3

Something like (In pseudocode):

Insert Employee;

Store Employee.Id

Run select count(*) from Address,Employee where Address.Id= Employee.AddressId and Employee.Id = EmployeeId

Assert("The answer should be zero")

Insert Address related to Employee

Run select count(*) from Address,Employee where Address.Id= Employee.AddressId and Employee.Id = EmployeeId

Assert("The answer should be one")

This has the advantage that I don't need a database with data already on it, but it has the disadvantage that takes lot of time to write an unit test like this in java, because to insert an employee, it might be necessary to:

• Avoid breaking validation rules no related to this particular test, for example, an Employee must be related to a Department, but if the Department table is empty, then I should create a Department or I will not be able to insert an employee.
  
• Avoid conflicts with validation rules directly related to this particular test, for example, what if I have an Hibernate interceptor that won't let me insert an address without 1 or more Addresses

The main problem here, I believe, is that f I insert a row leaving a not null column empty most databases won't wait until I try to commit the transaction to say "integrity violation" and rollback my changes... therefore it is impossible to write partial data just for the test that I have in front of me, but... could I automatically generate consistent inserts using as a source just the integrity rules at the database level... and the select that I want to test?
I think it can be done... the question is..
What is the algorithm to generate the inserts needed to satisfy an SQL select statement?

The perfect infrastructure (framework?) for data systems

The perfect  infrastructure (framework?)  for data systems:

• Has an object relational query language (something like JQL or LINQ) 
• Has a database with versioning (like Subversion) so you can always consult the database as it was in a particular moment in time transparently
• Supports transactions... and distributed transactions. (like Spring)
• Has a framework to exchange graphs of objects with a remote client, objects can be manipulated freely on the client, filtered and queried without hitting the database without need, and are transparently loaded in to de client without having the n+1 problem. (like an hybrid between Hibernate, Apple's EOF & Carrierwave)
• Supports "client only transactions" and nested client only transactions (like the Editing Context in WebObject's JavaClient applications) so that it is possible to make rollbacks without hitting the database, and it is even possible to make partial rollbacks... and have savepoint functionality, without going all the way to the database (unless you want to do so)
• Client objects, server objects and database elements are kept in perfect sync automatically, but it is possible to add logic to a particular tier of the system without too much hassle.
• Has a validation framework, that make it really easy to write efficient validation code following DRY, and that validates data on the client, on the application server and on the database.
• Validation code, combined with the versioning capabilities of the infrastructure allows to save information partially, as easily as writing part of a paper, validating only as you completely the information, with multiple integrity levels
• It is possible to disconnect the client from the server, and it will be able to save your changes until the connection is established again
• The applications built with this perfect infrastructure auto update automatically.
• With a very simple configuration tweak, it is possible to download the application "sliced" in pages, or as a complete bundle. This capability is so well integrated that the final user can choose the installation method, and the programmer doesn't even care about this feature.
• The developer only needs to specify the requirements semi-formally in a language (like Amalgam) and he will receive a running application, that adapts dynamically to specification (unless he chooses to "freeze" a particular feature of the application, in which case, the default procedural code for that feature is automatically generated, and the developer can customize as he wishes ... or decide to un-freeze it.
• Can be coded in any language compatible with a virtual machine that runs anywhere, or can be compiled to an specific platform.
• Allows for easy report design... by the developer, or the user.
• It is opensource (or sharedsource), so that in the extremely unlikely case of needing another feature, or finding a bug, it can be easily fixed by the developer
• It is freely (as in beer) downloadable from the Internet. (or has a reasonable price)
• It is fully documented, with lots of examples, going from very simple examples for beginners, to really complex real world applications with best practices for experts
• Includes the source code with unit-test with 100% coverage of the code
• Supports design by contract coding (from the database up to the client side).

You know what is the funny (or sad) part of all this? I have met frameworks that do 1, 2 or even 3 or more of this features... but none that does them all... will I ever see such a thing? is even possible to build it?

RIAs: Faulting &Uniquing (or Merging?) (Granite, Ajax)

Today I realized that lazy loading support for Granite Data Services is in its infacy... is more like "Partial Loading" (it will load everything not initialized, and not initialized stuff will remain "unloaded" forever).

I am thinking this leads to a pattern like this:

1. I need to work with persons, so I fetch a list of them from a remote service.
2. I choose to work with the person with id "3";
3. present the contacts of person "3". (here is the tricky stuff, all the contacts that I load have a reference to person "3", what do I do about that? do I re fetch it, creating a different object and breaking uniquing, or do I look for a way to prevent that "same but different object" in my application? )

I guess that we will need something like Faulting & Uniquing , and a Client Side EditingContext (or Client Side EntityManger)... to control data in the client side... (our own idea of LDS DataStore ?)

But... until granite has that... what could we do as a first step? it would be nice if we could "merge" a recently obtained object with one a fetched before... something like ADO.NET DataSet... (I can not believe I am writing that I miss the DataSet)

I have been thinking... a fully "AJAX" traditional JavaScript based application would have the same problems if it had a complex enough UI... but I haven't heard of anything like it, it seems that most AJAX application developers build applications so simple that they don't even care about having to write and re-write client side data manipulation code... (or... maybe those applications don't even enough client side behavior to need it?).

I guess that until Granite has his own "Data Management" the way to handle data will be... to imitate the practices of traditional AJAX applications?

(Mmmm, now with Google Gears... will we see how JavaScript based frameworks for automatic handling of DTOs and ORM start to appear everywhere? Parhaps this will revive the interest in something like SDO?)

SOA: Transactional Boundaries, The Paper / Computer Document Impedance Mismatch

Here I am... I again, facing the exact same problem... this is becoming repetitive... I have to build an OLTP system... what is an OLTP system?

Online Transaction Processing (or OLTP) is a class of programs that facilitate and manage transaction-oriented applications, typically for data entry and retrieval transaction processing.

And right there, in the wikipedia article about OLTP, it is possible to read about the Paper Computer Impedance Mismatch (I here I was feeling like I discovered something) "The term Online Transaction Processing is somewhat ambiguous: some understand "transaction" as a reference to computer or database transactions, while others (such as the Transaction Processing Performance Council) define it in terms of business or commercial transactions."

Well, IMO, that is just a part (a very important part) of the P/C impedance mismatch... when we talk about a transaction oriented computer system... what are we talking about? about computer SQL X/Open DTP transactions? or about business transactions...? Some people believe it is the same thing... Some other people have never realized the difference between paper and computer documents... and ask... why was I able to do X in paper but now it is invalid in the computer? To explain it.... lets go back to the origins...before computer documents.... lets say you have to register a fine against someone (someone committed a mistake against the law, and now they have to pay some money as punishment). So, you start writing down the document "a fine of 8 gold coins for... " suddenly, you feel and urge to go to the latrine.... you stand up and run.... you have left the paper document incomplete... it doesn't say what was the mistake that originated it... it doesn't say who has to pay the 8 gold coins, it doesn't say by which authority is that person obliged to pay... but there is nothing to worry about... is already in ink over the paper... it doesn't matter if you have a real bad digestion problem and you can't continue for 2 months... when you return to the paper sheet, it will still say "a fine of 8 gold coins for...". that is persistence... exactly the same persistence used if you were saving the fine in modern AJAX based system... but if you were using that modern system, when you returned after 2 months, you would find that "your session has timed out" and you have lost the amount you written in the "amount" field (or maybe someone else had to use the computer, and they closed your account, or had to use the plug... and unplugged your computer...). So, with paper, you have to "actively" want to revert persistence (by destroying the piece of paper) but, with electronic persistence, loosing information is a lot easier, just close the current window without hitting save... and it is lost... and it can get lost even "after" hitting save... because... now... we have created a new enemy for persistence... we have "data integrity"... we have more problems than when we only had to fight for paper to write stuff down...

Why do I see integrity as an enemy of persistence? it is pretty simple, really... with the new computer based OLTP systems, it is impossible to save "data without integrity" (corrupted data?), so, unless you know all the facts precisely, you can't write something down... (it doesn't matter that you know the fine is about selling a controlled substance, that it was issued by the DEA, and signed by "John Smith", if you don't know who has to pay it (Jane Doe for example) every time you hit the "save" button, the system is going to answer, in a very nice and polite way (if well designed) "I can not save that fine, because the "First Name" and "Last Name" of the person that has to pay for it are mandatory fields". Well, you say... just let me register the information I have "now", and after I get the missing data, I will return and add it, but the system wont hear your begging... and it will not save your data until it has "integrity"... but before you had to use this new software system you were able to write down this information in a piece of paper... it didn't matter if you had the full name of the person or you didn't, those people on the IT department I going to hear about you,  and they will have to change that stubborn attitude...

Well, it turns out, sometimes integrity is needed... what can you do with a fine without a name? suppose you have to manage another 100,000 similar documents... and that you don't know the name of the person that has to pay for half of them... and on some other thousands you don't know how much you have to pay... or why... and on others you don't even know any of this stuff (you just know someone has to pay something because of some unknown law, and that by adding all the fines in February, you will earn 50,000 dollars, because federal government told you that). Now you are in trouble, you have to start defining how much is the minimal information to describe a fine... you have to draw a line between "useful information with integrity" and "vague corrupted stuff" or you will start loosing track of what is happening with every document (what makes a document a document, how much can you alter it before it becomes another document).

Suddenly you realize you can ask the computer to classify the document, and create two lists (that is one of the things computer do really well with structured information), so you ask the computer to create a list with "complete integral documents" and another with "incomplete documents", and you say "problem solved, I have 100,000 documents, and of those, 45,000 have integrity (full documents), all others are work in progress" but, after few days... you realize now you only have 44,985 full documents... someone has been erasing the data in the documents because he got a bribe... and, unless you have a backup from previous week, you cant know which documents were corrupted... so, now it turns out that an already "integral" document, can go back and become "corrupted" real easy... and you cant even know that in those cases where the information was incomplete from the beginning, the source of the problem was that the original information was incomplete... or just that the person that has to write it on to the system is doing in an incomplete form... intentionally...

WebBrowser + Embedded WebServer + Embedded DataBase = Google Gears

Hi!

Today I found out about a new Google project, Google Gears... a new browser plugin... that adds an SQL database and a local, only for that browser on that machine "Web Server" (oh, and an external WorkerPool for threaded asynchronous proceseses)...

So... now the that WebBrowser has an SQL database... a Worker Pool ... and a WebServer... it can run disconnected applications... you can save you emails locally... or your blog entries... or your RSS (I believe that is what google reader does)... WebApplications are now... Desktop applications... (or RIAs as they are called now).

So... now... what is the real advantage of  a RIAG (a RIA with "Google Gears") vs a Desktop App? Well, lets look at its features.. the RIAG... is slower (interpreted)... needs a plugin like Flash to do  real graphical stuff... it can't access anywhere on disk  (we could say it has its own SQL based filesystem)... therefore it is still not better for graphically intensive applications (I don't see a Photoshop or 3dStudio killer in the near future) ...  but could be a nice idea for desktop like stuff (for example a disconnected mail reader, or perhaps even a disconnected wiki). But wait... we already have disconnected mail readers...  (well, but they are not multiplatform.... mmmm... wait, Thunderbird IS multiplatform... and of course we have Java to create those multiplaform mail readers if we need to do so)... okay, but we can create a multiplatform Office like system (yes, a revolutionary idea... wait... what about OpenOffice?) and of course building an Office in a technology like JavaScript will make it really fast in standard hardware (like the very successful Java Office built by Corel a few years ago... wait... never heard of it? mmm, maybe it wasn't that successful... I wonder if that was because Java was really slow on hardware back then... )

Of course... none of that is going to stop Google Gears... people are just hypnotized with building stuff in the "web way" (even if can be done easier on the Desktop)... the way I see it.. with all this stuff, as the "thin client" of the WebBrowser becomes a "rich client" it is also gaining weight, becoming fat, becoming a fat client... so... by this logic... adding a plugin to all available browsers... it is better than a Java applet... but I can't find a logical reason for that... the new RIAs are just applications that use the browsers as the platform.... the  difference with windows applications? that there are many different browsers following the HTML/JavaScript standard, and only 1 windows (of course every browser follows the standard on its own particular way)... the difference with Java? (there isn't, but RIAs are slower... and sliced in pages... that seem to be faster to download... but in fact they consume even more bandwidth than classic fat clients with their proprietary binary protocols ), perhaps the key here is the "openness" of HTML & XML and JSON as protocols for communication (but that can also be done in Java, or in .NET & Mono)

So...  I just don't get it... what it is so great about adding a database plugin to the browser? by following this path all that we are doing is reinventing the wheel (everything that can already be done outside the browser is being re-built inside it... until RIAs become as Fat as Fat-Clients... and then someone else invents the new Thin-Client... and the story repeats again).

I guess the software industry is really, really iterative... we need to go back to an re-try stuff from the previous iteration... to realize it wasn't such a bad idea... enhance that idea... and from there, realize that the idea from 2 iterations ago, was the solution for the drawbacks of our current problems...

Project OpenJFX

Java counterattacks? The other day I posted that Silverlight and Flash might be going to kill Java... well Java is figting back:

JavaFX is a new family of Sun products based on Java technology and targeted at the high impact, rich content market.

JavaFX Script is a highly productive scripting language that enables content developers to create rich media and content for deployment on Java environments. JavaFX Script is a declarative, statically typed programming language. It has first-class functions, declarative syntax, list-comprehensions, and incremental dependency-based evaluation. It can make direct calls to Java APIs that are on the platform. Since JavaFX Script is statically typed, it has the same code structuring, reuse, and encapsulation features (such as packages, classes, inheritance, and separate compilation and deployment units) that make it possible to create and maintain very large programs using Java technology. See the FAQ for more information.

I am very impressed with the demos in the site, and the way less verbose way to describe interfaces (when compared with traditional Java Swing code, and I am thinking it could even be a threath for XAML & XML, some people on the net believe that XML is the poor man's parser, and that it is being overutilized to create stuff that should be implemented as an specific language... well, JavaFX is not XML... is this the start of a new trend?), I was also very exited to see how easy is to add animation to Java 2D application with this new API (everything that can be done with Flash will be possible... and maybe even more...). Now... the question are:

• Will Sun release a "UI Designers Pack" for Netbeans that will be pretty much something like Microsoft Expressions for Java?
• Could OpenJFX be adopted by projects like OpenLaszlo?
• Is using JavaScript like languages the new trend?
• Will JSON stuff become the new poor man's parser?

Eclipse... Is NOT an IDE

Okay... have been trying to use Eclipse 3.2 like an IDE all week... that failed miserably...

• VisualStudio.NET is an IDE
• Borland Developer Studio is an IDE
• NetBeans is an IDE
• IntelliJ is an IDE
• FlexBuilder (an Eclipse plugin) is an IDE
• JBuilder (an Eclipse plugin) is an IDE

But... Eclipse... Eclipse... is a PE... "Plug Environment", NOT an IDE: Integrated Development Environment.

After you add JDT to it can be considered an IDE... if you only build Console tools (command line applications) but, if you want to build anything more complex than that... then  JDT  is a very limited IDE.

Yes, you can add lots of plugins to Eclipse... and make it become JBuilder... (like Borland did or as Macromedia did with FlexBuilder), but the thing is, that it is JBuilder (the plugin) the thing that IS the IDE, Eclipse is just the PLATFORM for the IDE..., saying that Eclipse is an IDE it is like saying that Windows is word processor... or graphic design application... or why not, Windows is a IDE! (Of course, that is crazy... well saying that Eclipse is an IDE is crazy... comparing it to any real IDE is crazy...) Eclipse is a PLATFORM, and you can build an IDE on top of that, but, the quality (and INTEGRATION) of the free available plugins in Eclipse Callipso,  in my opinion is not enough to call it an IDE

Netbeans is a great IDE, the best OpenSource IDE for Java for Swing or Web or J2EE applications, Eclipse is NOT and IDE. Period.

(I guess this is my first rant in a blog)