2014-11-12

Logical order of SQL Query execution

First of all we should know that logical and physical order of query execution are different but result must be the same.

Every RDBMS can optimize the order in which parts of query are executed for performance reason. However that rearrangement must not influence the final result. The query must return the same records as if it is executed in this logical order:

  1.     FROM
  2.     WHERE
  3.     GROUP BY
  4.     HAVING
  5.     SELECT
  6.     DISTINCT
  7.     ORDER BY
  8.     TOP X / LIMIT X/ select * from ( subquery ) ROWNUM <= X

The programmers should always be aware of this sequence in order not to fall into a trap.

A common mistake is to try using column alias in GROUP BY, WHERE and HAVING clause but actually it is possible to use it only after SELECT clause is executed (ORDER BY and TOP/LIMIT/that long stupid oracle boilerplate).

Another mistake is to try sorting by column  that is not listed in SELECT clause.

The less common but hard to debug problem could be made if OUTER JOIN is used and join condition is moved from ON into WHERE and vice versa. While it is OK with INNER JOIN these two selects will return different results:
select *
from t1
     left join t2 on (t1.id = t2.t1_id and t2.color = 'blue')

select *
from t1
     left join t2 on (t1.id = t2.t1_id)
where t2.color = 'blue'

Which of those two will return less records?

2014-07-08

SQL Fiddle

If you need fast try and error check against some of the most popular relational database management system SQL Fiddle is the wright address. It is also free as in beer :)

It is an online application in which you can select to work some of supported RDBMS.
There are 2 text area fields: first is used to set your schema and data, the second is used to write some query that will run against your schema and data.

Using SQL Fiddle you can share your script similar to Gist for source code sharing.

It is also useful as online SQL code beautifier and formatter.

Here is an example of SQL Code for Oracle DB: http://sqlfiddle.com/#!4/1897b/1

2014-06-11

XMLUnit


XMLUnit for Java provides a set of supporting classes (e.g. Diff,  DetailedDiff, Transform, SimpleXpathEngine, Validator,NodeTest) that allow assertions to be made about:
  • The differences between two pieces of XML
  • The outcome of transforming a piece of XML using XSLT
  • The evaluation of an XPath expression on a piece of XML
  • The validity of a piece of XML
  • Individual nodes in a piece of XML that are exposed by DOM Traversal
XMLUnit for Java can also treat HTML content (even badly-formed HTML) as valid XML to allow these assertions to be made about the content of web pages too.

 

Experience


I've used XMLUnit in 2 situations: assert that 2 xml files are semantically the same regardless of structural differences (eg. different formatting, different namespace prefixes, usage of opening and closing tag vs. empty tag...) and finding real differences between two xml files regardless of prior mentioned differences in structure.

The equality of two xml files can be compared in two ways. We can check if two documents are identical. Two documents are considered to be "identical" if they contain the same elements and attributes in the same order. But we can check if two documents are similar, where those two documents must contain the same elements and attributes regardless of order. In most cases you will checking similarity and that is what is checked when XMLAssert.assertXMLEqual methdo is called.

Howto


There are only few classes that needs to be known. Not so complicated but not so easy when compared to ordinary Assert implementations (JUnit, TestNG or FEST).
XMLUnit class is used as a static configuration class. If there is need for some general configuration change (eg. to ignore whitespaces, to normalize text nodes, to ignore xml comments...) this is a place to do it before calling asserts. Also it is possible to change parsers and engines used by XMLUnit.

DifferenceEngine is comparing Nodes (just Nodes of type: ELEMENT_NODE, CDATA_SECTION_NODE, COMMENT_NODE, DOCUMENT_TYPE_NODE, PROCESSING_INSTRUCTION_NODE and TEXT_NODE) and when difference is found the responsibility gets delegated to DifferenceListener interface which implementation is the final judge if difference is significant. DifferenceEngine is not used directly but in some not so simple use cases difference listener should be set.
There are several implementations of DifferenceListener, most useful are Diff and DetailedDiff.

Diff is used for simple comparison where only the state of comparison is needed (identical, similar or different) and the comparison is halted as soon as the status is known with certainty.

If you need o collect all the differences and have all details DetaildedDiff should be used. It is just a decorator for other DifferenceListeners.

MatchTracker interface gets notified when a match between test and actual node is found. It is useful to implement listener that logs matches so that you get insight of comparison process. This interface has no implementation.

ElementQualifier Interface used by the DifferenceEngine to determine which elements can be compared within a NodeList of child nodes. It controls which two nodes from test and actual XML document should be compared.

There are few implementations of this interface: ElementNameQualifier which is default, compares element nodes whose tag name is same. ElementNameAndAttributeQualifier uses tag name and attribute, ElementNameAndTextQualifier uses tag name and text content, RecursiveElementNameAndTextQualifier compares all Element and Text nodes in two pieces of XML. Allows elements of complex, deeply nested types that are returned in different orders but have the same content to be recognized as comparable. 

Every single compare is done using ElementNameQualifier. I had a special case where I needed recursive ElementQualifier but which compares single nodes not just by tag name but also by attributes. So I have tweaked RecursiveElementNameAndTextQualifier to be just a recursive decorator around existing ElementQualifier implementation.

Example


I've crated example at https://github.com/aurelije/xmlunit I had an use case where I needed to refactor one complex wsdl file. I wanted to sort all operations, types and messages, so I can easily compare that sorted Old API wsdl file with New API wsdl file and see what is changed and what is added. So I needed some proof that the original and sorted wsdl are semantically the same. And then I could also use this test to list all differences between Old API and New API wsdl.

References


2013-11-17

How to download multiple files using cURL

A few months ago I've bought Kobo Aura HD eBook reader. That is my best investment this year :) I am using every chance to read books, but also I am reading magazines that do have ePub format (PragProg, Libre!, FullCircle, BSDMag and Linux Magazine.

But I've had a problem to download all issues of some magazine. If download URL for particular issue can be guessed than cURL will do the job perfectly. As example here is a command to download all 78 issues of FullCircle:

curl http://dl.fullcirclemagazine.org/issue[1-78]_en.epub --create-dirs -o "/home/zlatan/Downloads/fullcircle/fullcirclemagazine#1.epub"

Particular url is http://dl.fullcirclemagazine.org/issueXX_en.epub where XX is issue number. The option o controls output, and because there are 78 files filename will be constructed from fullcircleXX.epub name where XX is corresponding issue number taken from URL

cUrl is free software that exists as package for almost all platforms. So Linux user can use their package manager to install cUrl (if packege is not installed already) and MS Windows users can downloaded it from: http://curl.haxx.se/download.html

Simillary  to download all epub versions of PCLinuxOS Magazine copy and paste the following line into terminal:

curl http://pclosmag.com/download-epub.php?f=[2012-2013][01-12]epub.epub --create-dirs -o "/home/zkadragic/Downloads/pclinuxos/pclinuxos#1-#2.epub"

2013-02-10

How to install Lotus Notes 8.3 on Kubuntu 12.04

Installing Lotus Notes became easier than in previous versions and fortunately there is no need to edit .deb files anymore.
Follow these steps to install Lotus Notes (Kubuntu 12.04.1 32b):

  1. untar the installation: tar xvf lotus_notes853_linuxUb_en.tar
  2. run installer: sudo dpkg -i ibm-lotus-notess-8.5.3.i586.deb
  3. dpkg will throw missing dependencies, you should search and install all that dependencies using apt-get, aptitude or Muon package manager
  4. run: sudo dpkg -i ibm-lotus-notess-8.5.3.i586.deb again
  5. install other lotus notes parts: sametime, activities and so on using the same approach
  6. similarly untar and install fixpack
  7. install the wrapper:
mkdir -p /tmp/notesfix
cd /tmp/notesfix
 
wget –no-check-certificate https://github.com/sgh/lotus-notes_gtk2.23.3/raw/d3492c997bc7a92b895fa1369d4743a8e20b56b5/Makefile
 
wget –no-check-certificate https://github.com/sgh/lotus-notes_gtk2.23.3/raw/d3492c997bc7a92b895fa1369d4743a8e20b56b5/libnotesgtkfix.c
 
wget –no-check-certificate https://github.com/sgh/lotus-notes_gtk2.23.3/raw/d3492c997bc7a92b895fa1369d4743a8e20b56b5/notes-wrapper
 
make
chmod +x notes-wrapper
 
sudo cp notes-wrapper libnotesgtkfix.so /opt/ibm/lotus/notes/
The final step is to edit menu. Right click to Kickoff App Launcher -> Edit Application, find the Lotus Notes entry in Office menu, and put this line into command field:

sudo -u $USER env  GTK2_RC_FILES="/usr/share/themes/Raleigh/gtk-2.0/gtkrc" /opt/ibm/lotus/notes/framework/../notes-wrapper
Now you can "enjoy" your Lotus Notes :)

2012-06-23

Pro Spring 3

Right now I am reading Pro Spring 3 written by Clarence Ho and Rob Harrop (a SpringSource co-founder). Very up to date book about Spring covering version 3.1. This book doesn't lack information, almost every part of Spring is covered including some useful addons like Spring Data JPA, Spring Batch, Spring Integration and Roo, The book is not just about Spring it is about how to be productive with Spring. Author explains how to use SpringTool Suite (STS), how to decide which approach to follow, examples are very profound. And through the book author is developing a real application! I wish that authors have paid attention on Spring security (not covered at all) and Beans Validation chapter is too thin with only 2 field validation annotations mentioned (@NotNull and @Size). Still this is the best book that will make you a good and capable Spring programmer even if you don't have any precognition about Spring.

2012-06-21

YU18n

Raspad SFRJ doneo je mnogo problema lokalizatorima za naše pordučje. Autori Java jezika se nisu baš najbolje snašli u tome pa i u javi 6-te verzije postoje problemi. Rešenje za te probleme kao i kratak istorijat problema pogledajte na sledećem linku: http://klaus.e175.net/java-yu

Hvala Draženu na ovom linku :)