DDevelopment & Design

Subtelny feature konstrukcji CONNECT BY

Jeśli ktoś używa konstrukcji CONNECT BY i chce uniknąć subtelnych błędów to powinienen zapoznać się z opisaną poniżej własnością. Jeśli w kwerendzie hierarchicznej (czyli używającej konstrukcji CONNECT BY … START WITH …) używa się równocześnie złączeń, czy to ANSI czy Oracle’owych to wynik jest nieintuicyjny. Właśnie wykryłem istotny błąd w moim kodzie z tym związany. Kwerenda hierarchiczna to takie coś, co pozwala uzyskać w jednym zapytaniu np. listę wszystkich przełożonych danego pracownika, ale nie tylko tych bezpośrednich, ale również przełożonych tych przełożonych, itd. Chodzi o uzyskanie informacji z drzewa. Dobry opis działania kwerend hierarchicznych znajduje się tutaj:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/queries4a.htm#2053937 Są tam 2 ważne punkty:
* If the|WHERE| predicate contains a join, Oracle applies the joinpredicates /before/ doing the |CONNECT| |BY| processing.
* If the |WHERE| clause does not contain a join, Oracle applies allpredicates other than the|CONNECT| |BY| predicates _/after/_doing the |CONNECT| |BY|processing without affecting the other rows of the hierarchy. Jeśli klauzura where zawiera i złączenie i nie-złączenie (warunek nie będący złączeniem) to złączenie dokonywane jest przed przetwarzaniem connect by a nie-złączenie po. Jako przykład opiszę w uproszczeniu wykryty błąd: Kwerenda miała zwracać dla danego klienta listę jego przełożonych z jego firmy (w uproszczeniu)

SELECT c.* FROM customer_all c
CONNECT BY PRIOR c.customer_id_high = c.customer_id
START WITH c.customer_id = 123456

Potem zaszła potrzeba, aby pobierać też adresy billingowe klientów. Każdy klient może mieć wiele adresów, ale tylko jeden billingowy czyli z flagą CCBILL=’X’. Kwerenda została zmieniona na

SELECT level, c.*, cc.*
  FROM customer_all c,
       ccontact_all cc
 WHERE c.customer_id = cc.customer_id
   AND cc.ccbill = 'X'
CONNECT BY PRIOR c.customer_id_high = c.customer_id
START WITH c.customer_id = 123456

I tu był błąd. Warunek c.customer_id = cc.customer_id nastąpił przed wykonaniem connect by a cc.ccbill = ‘X’ po, co spowodowało że błąd wystąpił, gdy klient 123456 miał dwa adresy a jego przełożony i przełożony przełożonego mieli po jednym. Liczenie zapytania wyglądało następująco: Etap 1: w wyniku zapytania występują 2 rekordy dla klienta 123456, jeden dla adresu billingowego, drugi dla innego adresu Etap 2: dla obu rekordów powstałych w poprzednim etapie wykonywany jest warunek connect by. Do wyników 2 razy zostaje wrzucony przełożony 123456 Etap 3: dla obu rekordów powstałych w poprzednim etapie wykonywany jest warunek connect by. Do wyników 2 razy zostaje wrzucony przełożony przełożonego 123456 Etap 4: Usunięty został rekord z adresem niebillingowym dla klienta 123456, ale rekordy jego przełożonych dodane z powodu tego rekordu nie zostały usunięte ponieważ

*ich* adres był billingowy (nie mieli żadnego nie-billingowego). Skutek był taki, że kwerenda zwróciła 5 rekordów, a powinna była zwrócić 3: rekordy przełożonego i przełożonego przełożonego zostały zdublikowane z powodu drugiego adresu klienta 123456. Rekord z jego drugim adresem został usunięty, ale gałąź, która z niego powstała – nie. Rozwiązaniem było dodanie warunku na poprzedni rekord w CONNECT BY, koniecznie z PRIOR: PRIOR cc.ccbill = ‘X’

SELECT level, c.*, cc.*
  FROM customer_all c, ccontact_all cc
 WHERE c.customer_id = cc.customer_id
   AND cc.ccbill = 'X'
CONNECT BY PRIOR c.customer_id_high = c.customer_id
       AND PRIOR cc.ccbill = 'X'
 START WITH c.customer_id = 123456

Bezpiecznym rozwiązaniem byłoby też robienie joina z ccontract_all dopiero po obliczeniu wyniku kwerendy hierarchicznej.

SELECT *
  FROM (SELECT c. *
          FROM customer_all c,
        CONNECT BY PRIOR c.customer_id_high = c.customer_id
         START WITH c.customer_id = 123456) c,
       ccontract_all cc
 WHERE c.customer_id = cc.customer_id
   AND cc.ccbill = 'X'

Jeszcze taką ciekawą rzecz zauważyłem: gdy w zapytaniu posiadającym i złączenia i kwerendę hierarchiczną zrobi się złączenie poprzez konstrukcję JOIN … ON a nie warunek w WHERE to czas wykonania wzrasta kilkaset tysięcy razy! Być może są przypadki, gdy jest odwrotnie, więc warto to zawsze sprawdzić.

— Previous article

RTMP - Real Time Messaging Protocol

Next article —

Migracja strony kodowej

You May Also Like
DDevelopment & Design

33rd Degree day 2 review

Second day of 33rd had no keynotes, and thus was even more intense. A good conference is a conference, where every hour you have a hard dilemma, because there are just too many interesting presentations to see. 33rd was definitely such a conference, and the seconds day really shined.

There were two workshops going on through the day, one about JEE6 and another about parallel programming in Java. I was considering both, but decided to go for presentations instead. Being on the Spring side of the force, I know just as much JEE as I need, and with fantastic GPars (which has Fork/Join, actors, STM , and much more), I won't need to go back to Java concurrency for a while.

GEB - Very Groovy browser automation

Luke Daley works for Gradleware, and apart from being cheerful Australian, he's a commiter to Grails, Spock and a guy behind Geb, a  browser automation lib using WebDriver, similar to Selenium a bit (though without IDE and other features).

I have to admit, there was a time where I really hated Selenium. It just felt so wrong to be writing tests that way, slow, unproductive and against the beauty of TDD. For years I've been treating frontend as a completely different animal. Uncle Bob once said at a Ruby conference: "I'll tell you what my solution to frontend tests is: I just don't". But then, you can only go so far with complex GUIs without tests, and once I've started working with Wicket and its test framework, my perspective changed. If Wicked has one thing done right, it's the frontend testing framework. Sure tests are slow, on par with integration tests, but it is way better than anything where the browser has to start up front, and I could finally do TDD with it.

Working with Grails lately, I was more than eager to learn a proper way to do these kind of tests with Groovy.

GEB looks great. You build your own API for every page you have, using CSS selectors, very similar to jQuery, and then write your tests using your own DSL. Sounds a bit complicated, but assuming you are not doing simple HTML pages, this is probably the way to go fast. I'd have to verify that on a project though, since with frontend, too many things look good on paper and than fall out in code.

The presentation was great, Luke managed to answer all the questions and get people interested. On a side note, WebDriver may become a W3C standard soon, which would really easy browser manipulation for us. Apart from thing I expected Geb to have, there are some nice surprises like working with remote browsers (e.g. IE on remote machine), dumping HTML at the end of the test and even making screenshots (assuming you are not working with headless browser).

Micro services - Java, the Unix Way

James Lewis works for ThoughtWorks and gave a presentation, for which alone it was worth to go to Krakow. No, seriously, that was a gem I really didn't see coming. Let me explain what it was about and then why it was such a mind-opener.
ThoughtWorks had a client, a big investment bank, lots of cash, lots of requirements. They spent five weeks getting the analysis done on the highest possible level, without getting into details yet (JEDI: just enough design initially). The numbers were clear: it was enormous, it will take them forever to finish, and what's worse, requirements were contradictory. The system had to have all three guarantees of the CAP theorem, a thing which is PROVED to be impossible.
So how do you deal with such a request? Being ThoughtWorks you probably never say "we can't", and having an investment bank for a client, you already smell the mountains of freshly printed money. This isn't something you don't want to try, it's just scary and challenging as much as it gets.
And then, looking at the requirements and drawing initial architecture, they've reflected, that there is a way to see the light in this darkness, and not to end up with one, monstrous application, which would be hard to finish and impossible to maintain. They have analyzed flows of data, and came up with an idea.
What if we create several applications, each so small, that you can literally "fit it in your head", each communicating with a simple web protocol (Atom), each doing one thing and one thing only, each with it's own simple embedded web server, each working on it's own port, and finding out other services through some location mechanism. What if we don't treat the web as an external environment for our application, but instead build the system as if it was inside the web, with the advantages of all the web solutions, like proxies, caches, just adding a small queue before each service, to be able to turn it off and on, without loosing anything. And we could even use a different technology, with different pair of CAP guarantees, for each of those services/applications.
Now let me tell you why it's so important for me.
If you read this blog, you may have noticed the subtitle "fighting chaos in the Dark Age of Technology". It's there, because for my whole IT life I've been pursuing one goal: to be able to build things, that would be easy to maintain. Programming is a pure pleasure, and as long as you stay near the "hello world" kind of complexity, you have nothing but fun. If we ever feel burned out, demotivated or puzzled, it's when our systems grow so much, that we can no longer understand what's going on. We lose control. And from that point, it's usually just a way downward, towards complete chaos and pain.
All the architecture, all the ideas, practices and patterns, are there for just this reason - to move the border of complexity further, to make the size of "possible to fit in your head" larger. To postpone going into chaos. To bring order and understanding into our systems.
And that really works. With TDD, DDD, CQRS I can build things which are larger in terms of features, and simpler in terms of complexity. After discovering and understanding the methods (XP, Scrum/Kanbad) my next mental shift came with Domain Driven Design. I've learned the building block, the ideas and the main concept of Bounded Contexts. And that you can and should use a different architecture/tools for each of them, simplifying the code with the usage patterns of that specific context in your ming.
That has changed a lot in my life. No longer I have to choose one database, one language and one architecture for the whole application. I can divide and conquer, choose what I want to sacrifice and what advantages I want here, in this specific place of my app, not worrying about other places where it won't fit.
But there is one problem in here: the limit of technologies I'm using, to keep the system simple, and not require omnipotence to be able to maintain, to fix bugs or implement Change Requests.
And here is the accidental solution, ThoughtWorks' micro services bring: if you system is build of the web, of small services that do one thing only, and communicate through simple protocol (like Atom), there is little code to understand, and in case of bugs or Change Requests, you can just tear down one of the services. and build it anew.
James called that "Small enough to throw them away. Rewrite over maintain". Now, isn't that a brilliant idea? Say you have a system like that, build over seven years ago, and you've got a big bag of new requests from your client. Instead of re-learning old technologies, or paying extra effort to try to bring them up-to-date (which is often simply impossible), you decide which services you are going to rewrite using the best tools of your times, and you do it, never having to dig into the original code, except for specification tests.
Too good to be true? Well, there are caveats. First, you need DevOps in your teams, to get the benefits of the web inside your system, and to build in the we as opposite to against it. Second, integration can be tricky. Third, there is not enough of experience with this architecture, to make it safe. Unless... unless you realize, that UNIX was build this way, with small tools and pipes.
That, perhaps. is the best recommendation possible.

Concurrency without Pain in Pure Java

Throughout the whole conference, Grzegorz Duda had a publicly accessible wall, with sticky notes and two sides: what's bad and what's good. One of the note on the "bad" side was saying: "Sławek Sobótka and Paweł Lipiński at the same time? WTF?". 
I had the same thought. I wanted to see both. I was luckier though, since I'm pretty sure I'll yet be able too see their presentations this year, as 33rd is the first conference in a long run of conferences planned for 2012. Not being able to decide which one to see, I've decided to go for Venkat Subramaniam and his talk about concurrency. Unless we are lucky at 4Developers, we probably won't see Venkat again this year.
Unfortunately for me, the talk ("show" seems like a more proper word), was very basic, and while very entertaining, not deep enough for me. Venkat used Closure STM to show how bad concurrency is in pure Java, and how easy it is with STM. What can I say, it's been repeated so often, it's kind of obvious by now.
Venkat didn't have enough time to show the Actor model in Java. That's sad, as the further his talk, the more interesting it was. Perhaps there should be a few 90min sessions next year?

Smarter Testing with Spock

After the lunch, I had a chance to go for Sławek Sobótka again, but this time I've decided to listen to one of the commiters of Spock, the best thing in testing world since Mockito. 
Not really convinced? Gradle is using Spock (not surprisingly), Spring is starting to use Spock. I've had some experience with Spock, and it was fabulous. We even had a Spock workshop at TouK, lately. I wanted to see what Luke Daley can teach me in an hour. 
That was a time well spent. Apart from things I knew already, Luke explained how to share state between tests (@Shared), how to verify exceptions (thrown()), keep old values of variables (old()), how to parametrize description with @Unroll and #parameterName, how to set up data from db or whatever with <<, and a bit more advanced trick with mocking mechanism. Stubbing with closures was especially interesting.

What's new in Groovy 2.0?

Guillaume Laforge is the project lead of Groovy and his presentation was the opposite to what we could see earlier about next versions of Java. Most visible changes were already done in 1.8, with all the AST transformations, and Guillaume spent some time re-introducing them, but then he moved to 2.0, and here apart from multicatch in "throw", the major thing is static compilation and type checking.
We are in the days, were the performance difference between Java and Groovy falls to a mere 20%.  That's really little compared to where it all started from (orders of magnitude). That's cool. Also, after reading some posts and successful stories about Groovy++ use, I'd really like to try static compilation with this language
Someone from the audience asked a good question. Why not use Groovy++ as the base for static compilation instead. It turned out that Groovy++ author was also there. The main reason Guillaume gave, were small differences in how they want to handle internal things. If static compilation works fine with 2.0, Groovy++ may soon die, I guess.

Scala for the Intrigued


For the last talk this day, I've chosen a bit of Scala, by Venkat Subramaniam. That was unfortunately a completely basic introduction, and after spending 15 minutes listening about differences between var and val, I've left to get prepared to the BOF session, which I had with Maciek Próchniak.

BOF: Beautiful failures


I'm not in the position to review my own talk, and conclude whether it's failure was beautiful or not, but there is one things I've learned from it.
Never, under none circumstances, never drink five coffees the day you give a talk. To keep my mind active without being overwhelmed by all the interesting knowledge, I drank those five coffees, and to my surprise, when the talk started, the adrenaline shot brought me over the level, where you loose your breath, your pulse, and you start to loose control over your own voice. Not a really nice experience. I've had the effects of caffeine intoxication for the next two days. Lesson learned, I'm staying away from black beans for some time.
If you want the slides, you can find them here.
And that was the end of the day. We went to the party, to the afterparty, we got drunk, we got the soft-reset of our caches, and there came another day of the conference.

You can find my review from the last day in here.
DDevelopment & Design

Me on Hadoop on Parleys

Finally I've managed to import my WarJUG presentation to parleys.com. See for yourself :) If you've got problems with opening the parleys' version try the ones uploaded to youtube. Here is part 1: And here is part 2: Finally I've managed to import my WarJUG presentation to parleys.com. See for yourself :) If you've got problems with opening the parleys' version try the ones uploaded to youtube. Here is part 1: And here is part 2: