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ć.

You May Also Like

Sonar Gerrit Plugin Release

I am happy to announce a first release of my Sonar Gerrit plugin. This plugin reports Sonar violations on your patchsets to your Gerrit server. Sonar analyses full project, but only files included in patchset are commented on Gerrit. Please forward to project page for installation instructions. This plugin is intended to use with Gerrit Trigger plugin for Jenkins CI server. Together they provide a great tool for automatic static code analysis.I am happy to announce a first release of my Sonar Gerrit plugin. This plugin reports Sonar violations on your patchsets to your Gerrit server. Sonar analyses full project, but only files included in patchset are commented on Gerrit. Please forward to project page for installation instructions. This plugin is intended to use with Gerrit Trigger plugin for Jenkins CI server. Together they provide a great tool for automatic static code analysis.

CasperJS for Java developers

Why CasperJS

Being a Java developer is kinda hard these days. Java may not be dead yet, but when keeping in sync with all the hipster JavaScript frameworks could make us feel a bit outside the playground. It’s even hard to list JavaScript frameworks with latest releases on one website.

In my current project, we are using AngularJS. It’a a nice abstraction of MV* pattern in frontend layer of any web application (we use Grails underneath). Here is a nice article with an 8-point Win List of Angular way of handling AJAX calls and updating the view. So it’s not only a funny new framework but a truly helper of keeping your code clean and neat.

But there is also another area when you can put helpful JS framework in place of plan-old-java one - functional tests. Especially when you are dealing with one page app with lots of asynchronous REST/JSON communication.

Selenium and Geb

In Java/JVM project the typical is to use Selenium with some wrapper like Geb. So you start your project, setup your CI-functional testing pipeline and… after 1 month of coding your tests stop working and being maintainable. The frameworks itselves are not bad, but the typical setup is so heavy and has so many points of failure that keeping it working in a real life project is really hard.

Here is my list of common myths about Selenium: * It allows you to record test scripts via handy GUI - maybe some static request/response sites. In modern web applications with asynchronous REST/JSON communication your tests must contain a lot of “waitFor” statements and you cannot automate where these should be included. * It allows you to test your web app against many browsers - don’t try to automate IE tests! You have to manually open your app in IE to see how it actually bahaves! * It integrates well with continuous integration servers like Jenkins - you have to setup Selenium Grid on server with X installed to run tests on Chrome or Firefox and a Windows server for IE. And the headless HtmlUnit driver lacks a lot of JS support.

So I decided to try something different and introduce a bit of JavaScript tooling in our project by using CasperJS.

Introduction

CasperJS is simple but powerful navigation scripting & testing utility for PhantomJS - scritable headless WebKit (which is an rendering engine used by Safari and Chrome). In short - CasperJS allows you to navigate and make assertions about web pages as they’d been rendered in Google Chrome. It is enough for me to automate the functional tests of my application.

If you want a gentle introduction to the world of CasperJS I suggest you to read: * Official website, especially installation guide and API * Introductionary article from CasperJS creator Nicolas Perriault * Highlevel testing with CasperJS by Kevin van Zonneveld * grails-angular-scaffolding plugin by Rob Fletcher with some working CasperJS tests

Full example

I run my test suite via following script:

casperjs test --direct --log-level=debug --testhost=localhost:8080 --includes=test/casper/includes/casper-angular.coffee,test/casper/includes/pages.coffee test/casper/specs/

casper-angular.coffe

casper.test.on "fail", (failure) ->
    casper.capture(screenshot)

testhost   = casper.cli.get "testhost"
screenshot = 'test-fail.png'

casper
    .log("Using testhost: #{testhost}", "info")
    .log("Using screenshot: #{screenshot}", "info")

casper.waitUntilVisible = (selector, message, callback) ->
    @waitFor ->
        @visible selector
    , callback, (timeout) ->
        @log("Selector [#{selector}] not visible, failing")
        withParentSelector selector, (parent) ->
            casper.log("Output of parent selector [#{parent}]")
            casper.debugHTML(parent)
        @echo message, "RED_BAR"
        @capture(screenshot)
        @test.fail(f("Wait timeout occured (%dms)", timeout))

withParentSelector = (selector, callback) ->
    if selector.lastIndexOf(" ") > 0
       parent = selector[0..selector.lastIndexOf(" ")-1]
       callback(parent)

Sample pages.coffee:

x = require('casper').selectXPath

class EditDocumentPage

    assertAt: ->
        casper.test.assertSelectorExists("div.customerAccountInfo", 'at EditDocumentPage')

    templatesTreeFirstCategory: 'ul.tree li label'
    templatesTreeFirstTemplate: 'ul.tree li a'
    closePreview: '.closePreview a'
    smallPreview: '.smallPreviewContent img'
    bigPreview: 'img.previewImage'
    confirmDelete: x("//div[@class='modal-footer']/a[1]")

casper.editDocument = new EditDocumentPage()

End a test script:

testhost = casper.cli.get "testhost" or 'localhost:8080'

casper.start "http://#{testhost}/app", ->
    @test.assertHttpStatus 302
    @test.assertUrlMatch /\/fakeLogin/, 'auto login'
    @test.assert @visible('input#Create'), 'mock login button'
    @click 'input#Create'

casper.then ->
    @test.assertUrlMatch /document#\/edit/, 'new document'
    @editDocument.assertAt()
    @waitUntilVisible @editDocument.templatesTreeFirstCategory, 'template categories not visible', ->
        @click @editDocument.templatesTreeFirstCategory
        @waitUntilVisible @editDocument.templatesTreeFirstTemplate, 'template not visible', ->
            @click @editDocument.templatesTreeFirstTemplate

casper.then ->
    @waitUntilVisible @editDocument.smallPreview, 'small preview not visible', ->
        # could be dblclick / whatever
        @mouseEvent('click', @editDocument.smallPreview)

casper.then ->
    @waitUntilVisible @editDocument.bigPreview, 'big preview should be visible', ->
        @test.assertEvalEquals ->
            $('.pageCounter').text()
        , '1/1', 'page counter should be visible'
        @click @editDocument.closePreview

casper.then ->
    @click 'button.cancel'
    @waitUntilVisible '.modal-footer', 'delete confirmation not visible', ->
        @click @editDocument.confirmDelete

casper.run ->
    @test.done()

Here is a list of CasperJS features/caveats used here:

  • Using CoffeeScript is a huge win for your test code to look neat
  • When using casper test command, beware of different (than above articles) logging setup. You can pass --direct --log-level=debug from commandline for best results. Logging is essential here since Phantom often exists without any error and you do want to know what just happened.
  • Extract your helper code into separate files and include them by using --includes switch.
  • When passing server URL as a commandline switch remember that in CoffeeScript variables are not visible between multiple source files (unless getting them via window object)
  • It’s good to override standard waitUntilVisible with capting a screenshot and making a proper log statement. In my version I also look for a parent selector and debugHTML the content of it - great for debugging what is actually rendered by the browser.
  • Selenium and Geb have a nice concept of Page Objects - an abstract models of pages rendered by your application. Using CoffeeScript you can write your own classes, bind selectors to properties and use then in your code script. Assigning the objects to casper instance will end up with quite nice syntax like @editDocument.assertAt().
  • There is some issue with CSS :first and :last selectors. I cannot get them working (but maybe I’m doing something wrong?). But in CasperJS you can also use XPath selectors which are fine for matching n-th child of some element (x("//div[@class='modal-footer']/a[1]")).
    Update: :first and :last are not CSS3 selectors, but JQuery ones. Here is a list of CSS3 selectors, all of these are supported by CasperJS. So you can use nth-child(1) is this case. Thanks Andy and Nicolas for the comments!

Working with CasperJS can lead you to a few hour stall, but after getting things working you have a new, cool tool in your box!

Phonegap / Cordova and cross domain ssl request problem on android.

In one app I have participated, there was a use case:
  • User fill up a form.
  • User submit the form.
  • System send data via https to server and show a response.
During development there wasn’t any problem, but when we were going to release production version then some unsuspected situation occurred. I prepare the production version accordingly with standard flow for Android environment:
  • ant release
  • align
  • signing
During conduct tests on that version, every time I try to submit the form, a connection error appear. In that situation, at the first you should check whitelist in cordova settings. Every URL you want to connect to, must be explicit type in:
res/xml/cordova.xml
If whitelist looks fine, the error is most likely caused by inner implementation of Android System. The Android WebView does not allow by default self-signed SSL certs. When app is debug-signed the SSL error is ignored, but if app is release-signed connection to untrusted services is blocked.



Workaround


You have to remember that secure connection to service with self-signed certificate is risky and unrecommended. But if you know what you are doing there is some workaround of the security problem. Behavior of method
CordovaWebViewClient.onReceivedSslError
must be changed.


Thus add new class extended CordovaWebViewClient and override ‘onReceivedSslError’. I strongly suggest to implement custom onReceiveSslError as secure as possible. I know that the problem occours when app try connect to example.domain.com and in spite of self signed certificate the domain is trusted, so only for that case the SslError is ignored.

public class MyWebViewClient extends CordovaWebViewClient {

   private static final String TAG = MyWebViewClient.class.getName();
   private static final String AVAILABLE_SLL_CN
= "example.domain.com";

   public MyWebViewClient(DroidGap ctx) {
       super(ctx);
   }

   @Override
   public void onReceivedSslError(WebView view,
SslErrorHandler handler,
android.net.http.SslError error) {

String errorSourceCName = error.getCertificate().
getIssuedTo().getCName();

       if( AVAILABLE_SLL_CN.equals(errorSourceCName) ) {
           Log.i(TAG, "Detect ssl connection error: " +
error.toString() +
„ so the error is ignored”);

           handler.proceed();
           return;
       }

       super.onReceivedSslError(view, handler, error);
   }
}
Next step is forcing yours app to  use custom implementation of WebViewClient.

public class Start extends DroidGap
{
   private static final String TAG = Start.class.getName();

   @Override
   public void onCreate(Bundle savedInstanceState)
   {
       super.onCreate(savedInstanceState);
       super.setIntegerProperty("splashscreen", R.drawable.splash);
       super.init();

       MyWebViewClient myWebViewClient = new MyWebViewClient(this);
       myWebViewClient.setWebView(this.appView);

       this.appView.setWebViewClient(myWebViewClient);
       
// yours code

   }
}
That is all ypu have to do if minSdk of yours app is greater or equals 8. In older version of Android there is no class
android.net.http.SslError
So in class MyCordovaWebViewClient class there are errors because compliator doesn’t see SslError class. Fortunately Android is(was) open source, so it is easy to find source of the class. There is no inpediments to ‘upgrade’ app and just add the file to project. I suggest to keep original packages. Thus after all operations the source tree looks like:

Class SslError placed in source tree. 
 Now the app created in release mode can connect via https to services with self-signed SSl certificates.