Thursday, 17 September 2015

sql

1. select
      select <column_names>
      from <<table_name>>
      where <condition>
      [and|or] <condition>
      group by <column_name>
      order by <column_name> [asc, desc]


2. select distinct
      select distinct <column_name>
      from <table_name>


3. where
      where <column_name> in ('value1', 'value2', ...)
      where <column_name> between 'value1' and'value2'
      where <column_name> like {pattern}


4. sum()    
      select sum(<column_name>)       //return a value
      from <table_name>
      where <condition>;

      select <column_name1>,<column_name2>, sum(aggregate_expression)
      from <table_name>
      where <condition>
      group by <column_name1>, <column_name2>;
  • If extra column in involved in select, then a group by is needed

5. group by
      select <column_name1>, <column_name2>, func(column_name3)
      from <table_name>
      group by <column_name1>, <column_name2>
  • group by is used when aggregate function is used in the select statement
  • group by really means "for each unique" or "for each unique combination of ..."
      select service, sum(fraud_vol), sum(fraud_val) from

      (select biz_svc_cd as service, volume as fraud_vol, value as fraud_val
      from x2p_fraud_daily
      where fraud_tran_date >= to_date('01/02/2016', 'dd/mm/yyyy')
      and fraud_tran_date <= to_date('29/02/2016', 'dd/mm/yyyy')
      and instr_tp = 'pmnt')

      group by service
      order by service asc


6. having
      select <column_name1>, sum(<column_name2>)
      from <table_name>
      group by <column_name1>
      having sum(column_name2) > 1500
  • having is used when aggregate function is used in the select statement

7. update
      update <table_name>
      set <column_name1>=value1, <column_name2>=value2,...
      where <column_name3>=value3;


8. delete
      delete from <table_name>
      where <column_name1>=value1;


9. insert into
      insert into <table_name>
      values (value1,value2,value3,...);

      insert into <table_name> (column_name1,column_name2,column_name3,...)
      values (value1,value2,value3,...);


10. count
      select count(*)
      from <table_name>;

      select count(distinct <column_name>)
      from <table_name>;

      select count(<column_name>)
      from <table_name>
      where <column_name1>=value1;


11. join
  • join is based on foreign key concept. 
  • based on a column match between table a and b, it combines columns of a and b on a condition
  • inner join only return rows that meet the condition
  • left join return all rows from table a
  • right join return all rows from table b
  • full join return all rows from table a and b
  • self join is to find relationship between rows in the same table (self join create cartesian product of table with itself)
      select yr, mon, fraud_reason, sum(val) from
           (select extract(year from fraud_tran_date) as yr, extract(month from fraud_tran_date) as mon, value as val, description as fraud_reason
           from x2p_fraud_daily
           left join lo_x2p_fraud_rsn
           on x2p_fraud_daily.fraud_rsn_id=lo_x2p_fraud_rsn.id
           where fraud_tran_date>= to_date('01/02/2016', 'dd/mm/yyyy')
           and fraud_tran_date<= to_date('29/02/2016', 'dd/mm/yyyy'))
      group by yr, mon, fraud_reason
      order by yr desc, mon asc, fraud_reason;


12. common command

      create table new_table as select * from old_table;
      insert into table_1 select * from table_2;
      select extract(year from fraud_tran_date) as yr, extract(month from fraud_tran_date) as mon
      where fraud_tran_date >= to_date('01/02/2016', 'dd/mm/yyyy')
      and sender in ('ctbaau2sxxx', 'ctbaau2sbca', 'ctbaau2sxb1', 'ctbaauc1bca', 'ctbaauc1boc')

13. bulk delete with 50000 in one batch to avoid performance issue

      begin
      loop
      delete /*+ parallel (table1, 4) */  from table1
           where event_date < to_date('[date]','mm/dd/yyyy') and rownum < 50000;
           exit when sql%rowcount < 49999;
           commit;
      end loop;
      commit;
      end;


14. select into             //backup a table by creating a new one

      select *
      into backup_table
      from table;


15. merge into

      merge into x2p_fraud_entry using
          (select
                  x2p_fraud_entry_staging.id,
                  x2p_fraud_entry_staging.version,
                  x2p_fraud_entry_staging.transaction_key,
            from x2p_fraud_entry_staging
            left outer join x2p_settlement_transaction
                on x2p_settlement_transaction.tx_id_key = x2p_fraud_entry_staging.transaction_key
          ) txsrc
          on (x2p_fraud_entry.id = txsrc.id)
          when matched then update set
                --x2p_fraud_entry.id = txsrc.id, -- no need to update the id
                x2p_fraud_entry.version = txsrc.version,
          when not matched then insert
          (
           id,
           version,
          )
          values
          (
           txsrc.id,
           txsrc.version,
          );


16. coalesce
  • returns the first non-NULL expression among its arguments
  • so if expression 1 is null value, expressions 2 can become its default value
      coalesce("expression 1", "expressions 2", ...)


17. to_timestamp
  • convert char string to timestamp
  • select to_timestamp('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
       from dual;
18. use with .. as to calculate percentage

      with mytable as
          (select .... from ...)
      select column_1/(select sum(column_1) from mytable)
      from mytable

19. indexing
  • content -> address

20. join query result with table

      select c1, c2 from t1
      left join (select c3, c4 from t2) t3
      on t1.c5 = t3.c6

21. query lookup with multiple column
      select
        d1.val as column1,
        d2.val as column2
      from table1 t
      join data d1 on ( d1.dataId = t.col1 )
      join data d2 on ( d2.dataId = t.col2 )


22. comment
      --


23. count(*) vs count(p.product_id)
  • count(*) counts the number of rows
  • count(p.product_id) counts the number of non-null product_id values

reference:
1. zentut sql tutorial

Cover Letter, Resume, Interview

1. Interview Question
  • Tell me about yourself.
  • What are you immediate objectives? What are your future aspirations?
  • What are your strengths and weaknesses?
  • What interests you in the position and/or our company?
  • What do you know about our company?
  • Can you give me an example of a time when you demonstrated the ability to be a team player?
  • Can you tell me an example of a specific time/event in your career when you displayed initiative? (Other areas may include time management, leadership, teamwork, problem solving, conflict resolution, staff motivation and the ability to work independently)

2. Ask the Interviewer
  • How has this vacancy come about?  
  • What will be the next step in the selection process?

Wednesday, 16 September 2015

cucumber bdd


1.  feature files
feature: <brief title>
    as a <role>
    i want a <feature>
    so that i can get <expected benefit>

    # additional background information
    <optional description>

    # acceptance criteria
    scenario: <brief title>
    <optional description>
        given <describe prerequisites>
        when <describe interaction or state change>
        then <describe intended results with "should">


2. scenario
  • each scenario consists a list of steps
  • steps start with keyword 'given', 'and', 'when', 'then', 'but'

3. step definition (generation)
  • each 'given' 'when' 'and' 'then' step can be auto generated but need to be implemented
  • cucumber search feature file and step definitions in classpath when run
  • also mark /src dir as source root so that step function can be found by ide (see link)
  • get rid of the 'throw new pendingException()' at the end of auto generated step function, otherwise junit/testNG will not run
  • how runner find where step definition is: glue = {"com/restassured/example"}
com.nab.ewcs.ubankac.se.FeatureRunner -t @mytest -g classpath:com/nab/ewcs/ubankac/se classpath:features -f html:cucumber-reports/cucumber-pretty --glue /Users/P781889/repo/tracker-SE-test/src/test/java/com/nab/ewcs/ubankac/se/ /Users/P781889/repo/tracker-SE-test/src/test/resources/features

4. background

feature: multiple site support
    as a multiple site owner
    i want to host blogs for different people
    in order to make gigantic piles of money

background:
    given a global administrator named "Greg"
    and a blog named "Greg's anti-tax rants"

scenario: Dr. Bill posts to his own blog
    given I am logged in as Dr. Bill
    when I try to post to "Expensive Therapy"
    then I should see "Your article was published."
  • background is run before each scenarios but after any Before Hooks

5. scenario outline - a list of scenarios

scenario outline: feeding a suckler cow
    given the cow weighs <weight> kg
    when we calculate the feeding requirements
    then the energy should be <energy> MJ
    and the protein should be <protein> kg

    examples:
    | weight | energy | protein |
    | 450      | 26500  | 215      |
    | 500      | 29500  | 245      |


6. 'before' and 'after' hooks

public class StepDefinitions {
     @before("@hook1")
     public void beforeScenario() {
     }

     @after("@hook1")
     public void afterScenario() {
     }
}
    • @before will be run before 1st step of each scenario
    • @before and @after can be used in same manner as in testng to setup and teardown
    • @before hook is useful to setup env while @after hook is useful to clean up env after each run

      7. capture groups

      given User enters Number 123 and String "abc"

      @given("^User enters Number (\\d+) and String \"([^\"]*)\"$") .      //match something in double quote
      public void user_enters_UserName_and_Password(int arg1, String arg2) throws Throwable {
          // write code here that turns the phrase above into concrete actions
          throw new PendingException();
       }

      given a blog post named "Random" with Markdown body  //doc string
       """
      some title, eh?
      ===============
      here is the first paragraph of my blog post.
      lorem ipsum dolor sit amet, consectetur adipiscing elit.
      """

      @given("^a blog post named \"([^\"]*)\" with markdown body$")
      public void a_blog_post_named_with_Markdown_body(String arg1) throws{ }

      given the following animals:   // data table
      | cow   |
      | horse |
      | sheep |

      @given("the following animals:")
      public void the_following_animals(List<String> animals) { }

      given the following users exist:   // data table
      | name  | email | twitter |
      | Aslak | aslak@cucumber.io | @aslak_hellesoy |
      | Julien | julien@cucumber.io | @jbpros |
      | Matt | matt@cucumber.io | @mattwynne |

      @given("^the following users exist:$")
      public void the_following_users_exist(DataTable arg1){ }
      • captured groups are captured 'arguments'
      • capture groups are strings (even when they match digits like \d+). for statically typed languages, cucumber will automatically transform those strings into the appropriate type
      
      
      8. regular expression

      ^I'm logged in$     //begin to end
      .                   //any char
      *                   //preceding token 0 or more times
      +                   //preceding token 1 or more times 
      d                   //any digit
      ?                   //previous token optional
      [^abc]              //anything but abc
      \d                  //match a digital char, same as [0-9]



      9. junit test runner
          @RunWith(Cucumber.class)
          @CucumberOptions(
              features = "Feature"
              ,glue={"stepDefinition"}
              )
          public class CucumberTest {
          }
      •  be careful when using maven, 'cucumber' lib version need to be compatible
          <dependency>
              <groupId>info.cukes</groupId>
              <artifactId>cucumber-java</artifactId>
              <version>1.2.4</version>
          </dependency>
      
          <dependency>
              <groupId>info.cukes</groupId>
              <artifactId>cucumber-junit</artifactId>
              <version>1.2.4</version>
          </dependency>
      
      
      
      10. testNG tester runner
          @CucumberOptions(
          features = "Feature"
          ,glue={"stepDefinition"}
          )
          public class NewTest extends AbstractTestNGCucumberTests{
          }
          <dependency>
              <groupId>info.cukes</groupId>
              <artifactId>cucumber-java</artifactId>
              <version>1.2.4</version>
          </dependency>
          <dependency>
              <groupId>info.cukes</groupId>
              <artifactId>cucumber-testng</artifactId>
              <version>1.2.4</version>
              <scope>test</scope>
          </dependency>  


      11. do not mix Junit and TestNG runner in same Maven project
      • junit runner will need 'cucumber-junit' dependency
      • testNG runner will need 'cucumber-testng' dependency
      • if both dependencies are included in same Maven project, one or both runner will not run properly

      12. writing feature file
      • wording should not be implementation specific
      • ask yourself, will this wording need to change if the implementation does?
      • user parameter to reduce duplicate step functions 
         When I am on "A" website
         Then I check page title is "title_A" 
         When I am on "B" website 
         Then I check page title is "title_B"

      13. @CucumberOptions(tags)
         @CucumberOptions(tags={"@one","@two"}
         Has @one AND @two tag
         @Cucumber.Options(tags = {"@one, @two"}) 
         Has @one OR @two tag 
      
      
      
      

      16. cucumber-jvm convension (works for maven)

      Example
      └───src
          ├───main
             └───java
          └───test
              ├───java
                 └───com
                     └───bensnape
                         └───example
                                 MyStepdefs.java
              
              └───resources
                  └───com
                      └───bensnape
                          └───example
                                  example.feature

      17. cucumber best practice
      • write "declarative" feature in domain specific language
      • insert a narrative
      • use background when necessary

      18. dependency injection in cucumber
      • a single INSTANCE of class is shared between many step definition class that use it
      • you will never need to create the INSTANCE using 'new'
            <dependency>
                  <groupId>info.cukes</groupId>
                  <artifactId>cucumber-picocontainer</artifactId>
                  <version>${cucumber.version}</version>
                  <scope>test</scope>
            </dependency>
            <dependency>
                  <groupId>org.picocontainer</groupId>
                  <artifactId>picocontainer</artifactId>
                  <version>${picocontainer.version}</version>
                  <scope>test</scope>
            </dependency>

            class AccountSteps {
                  KnowsTheDomain helper;  //defined in another class file, can be shared state among
                                                              //many step classes

                  public AccountSteps(KnowsTheDomain helper) {
                        this.helper = helper;
                  }
            }
      • picoContainer created a new instance of KnowsTheDomain for each scenario and injected that instance into every step definition class that needed it

      19. cucumber maven plugin

      20. cucumberjs datatable


      21. cucumberjs rerun failed test cases
      • on the first run add the parameters --format rerun:@rerun.txt
      • after a failed run, remove any arguments specifying the locations of feature files and add @rerun.txt
      ./node_modules/.bin/cucumber-js acceptance-tests/features/*.feature --tags @mytest -r acceptance-tests/steps --format json:acceptance-tests/output/cucumber.json --format rerun:@rerun.txt

      ./node_modules/.bin/cucumber-js @rerun.txt --tags @mytest -r acceptance-tests/steps --format json:acceptance-tests/output/cucumber.json --format rerun:@rerun.txt


      22. junit runner rerun failed test (see link)

      23. cucumberjs run in parallel
      • --parallel 4


      References:
      1. cucumber reference 
      2. toolsQA cucumber java test example
      3. cucumber with testNG 
      4. cucumber java implementation 
      5. cucumber selenium testNG maven example
      6. cucumber "background" keyword
      7. cucumber hooks
      8. cucumber hooks example
      9. cucumber tutorials
      10. cucumber step definition 
      11. regular expression syntax
      12. what does this regex mean?
      13. 15 expert tips for using cucumber 
      14. define step function's location for cucumber jvm 
      15. youtube: cucumber jvm setup 
      16. just enough regex for cucumber
      17. my stackflow question
      18. test your frontend code
      19. cucumber in java - 10 min tutorial
      20. cucumberjs datatable
      21. cucumberjs rerun failed tests
      22. how to rerun failed test cases in cucumber
      23. cucumberjs run feature file in parallel
      24. use maven cucumber reporting plugin
      25. intelliJ cannot find any declarations

      Monday, 14 September 2015

      selenium grid in the cloud

      1. what is selenium grid
      • when running test in local, use selenium webdriver to communicate with browser directly
      • when running test remotely (grid or cloud), use DesiredCapabilites + RemoteWebDriver
      • start selenium server as hub and node, single hub, multiple nodes, node register to hub
      • distribute test over different platform, different browsers in parallel
      • hub receive 'test' request with 'browser' and 'platform' information, and assign to node that has such capability

      2. install selenium grid
      • java -jar selenium-server-standalone-2.44.0.jar -role hub (install hub)
      • java -jar selenium-server-standalone-2.31.0.jar -role node -hub http://localhost:4444/grid/register -maxSession 15 -browser browserName="chrome",version=ANY,platform=WINDOWS,maxInstances=15 -Dwebdriver.chrome.driver=lib\chromedriver.exe (install node)

      3. maxInstance vs maxSession
      • maxInstance (5 firefox and 5 ie in remote machine)
            -browser browserName=firefox,version=12,maxInstances=5,platform=LINUX
            -browser browserName=InternetExplorer,version=9.0,maxInstances=5,platform=LINUX
      • maxSession = 8 (any given time, total 8 sessions, 5ff3ie, 4ff4ie etc)

      4. change test script and xml to add 'RemoteWebDriver', 'DesiredCapabilities', 'parallel', 'thread-count' etc

      5. suggestions
      • on node machine, at least 1GB memory for each browser instance
      • driver instance quit after each test
      • use the same version of 'selenium-server' on hub and node

      6. advantage to run selenium testing on the cloud
      • save time, tests run in parallel
      • covers all platforms, all browsers and versions
      • no need to scale-up

      7. run selenium on Sauce Labs
      • install Sauce Labs Jenkins plugin
      • configure Sauce Labs username/access key
      • configure a Jenkins job

      8. bridged network, NAT, host-only network in virtual machine
      • NAT: vm and host system in within the same NAT
      • bridged: create another node in external physical network and vm receive its ip address
      • host-only: create a new LAN within host system, only accessible within host
      Virtualbox Networking Modes


      reference
      1. selenium grid showing WebDriverException error
      2. how to speed up test execution using selenium grid
      3. setting up grid2 and working with it
      4. selenium jenkins - how to do it yourself and the Sauce Labs advantage
      5. configuring the jenkins Sauce OnDemand plugin
      6. selenium grid 2 
      7. host-only, NAT and bridged network

      Thursday, 10 September 2015

      mobile/cross browser testing

      1. mobile testing
      1.  hardware related
      2.  core functions:  sms, call, bluetooth, wifi, mobile network, battery

      2. mobile application testing
      1. change of orientation (activity is destroyed) 
        is screen redrawn correctly?
        does application remember its state correctly?
        app should not lose what user entered in the UI
        app should not 'forget' its place in the current transaction
         
      2. change of configuration
        availability of keyboard or system language will also destroy the application
         
      3. switch your app with another app (navigate to home screen, then return to your app) will cause possible memory leak

      4. interrupt by call/sms/push notification/multi-task
      5. network status change (2g/3g/4g/wifi, offline, flight mode)
      6. install/uninstall
      7. screensize
      8. internationalization

        3. monkey test
        1. clean bootup, do not set lock screen for the phone
           
        2. adb devices
          adb shell
          pm | grep your.package.name
          adb shell monkey -p your.package.name -v 500 > monkey_report.txt
           
        4. test tricks
        • special char
        • come back and edit
        • back on browser or refresh page
        • mobile and multiple browsers

        5. set cookie in console
        • document.cookie = "key=value";

        6. mobile automation

        Thursday, 3 September 2015

        selenium testing best practice

        1. how to select test cases to automate?
        • how does business make money?
        • how do users use your application?
        • what browsers are user using?
        • what things have broken in the past?

        2. type of automation framework
        • module based
        • data driven
        • keyword driven (table lookup)
        • bdd (cucumber, jbehave)

        3. page object model
        • single place for the service offered by the page
        • clean separation between test code and page specific code

        4. page factory
        • a class provided by selenium webdriver to support page object model
        • allow testers use @FindBy annotation
        • use the initElements method to initialize all the web elements located by @FindBy

        5. avoid thread.sleep, prefer wait

            (new WebDriverWait(driver, 30)).until(new ExpectedCondition() {
                        public Boolean apply(WebDriver d) {
                            return d.getTitle().toLowerCase().startsWith("java developer")             

            }

        6. hrm modules
        • users
        • payroll
        • personal information
        • leave and timesheet
        • recruitment
        • performance
        • training

        7. ecommerce modules
        • customer registration and account management
        • product catalog
        • orders and payment
        • coupons
        • store locator
        • invoice and receipt

        8. do not use try/catch in bdd code
        • so that test will fail-fast (any run-time exception will terminate the step/step definition)

        9. visual testing using blue-harvest

        10. common issues
        • handle UnexpectedAlertOpenError in protractor
        • headless mode

        11. open new page
        • click something to open
        • findInteractableWebElement on the new page (to know that new page is open)


        reference
        1. figuring out what to test
        2. explicit vs implicit waits
        3. page object model
        4. selenium Wiki
        5. mixing Implicit Wait and Explicit Wait 
        6. serenity tips and tricks
        7. explicit and implicit timeout in serenity
        8. implicit vs explicit wait
        9. visual regression testing using blue-harvest
        10. handle UnexpectedAlertOpenError in Protractor
        11. headless mode