Monday 11 February 2013

SQL Joins - Quick examples






SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.


SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

Tables in a database are often related to each other with keys.


A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.



Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.
INNER JOIN:Return rows when there is at least one match in both tables
OUTER LEFT JOIN:Return all rows from the left table, even if there are no matches in the right table
OUTER RIGHT JOIN:Return all rows from the right table, even if there are no matches in the left table
FULL JOIN:Return rows when there is a match in one of the tables


Tables used for example:

CREATE TABLE cities (

    name character varying(20),
    location character varying(10)
);


CREATE TABLE weather (
    city character varying(20),
    temp_lo integer,
    temp_hi integer
);

Inner join

The INNER JOIN keyword returns rows when there is at least one match in both tables. If there are rows in "Cities" that do not have matches in "Weather", those rows will NOT be listed and vice versa.

IN the below example the "Delhi" and "Chennai" are the ones that are in both Cities and weather

SELECT * FROM cities INNER JOIN weather ON (cities.name=weather.city);




Left outer join

This query is called a left outer join because the table mentioned on the left of the join operator will have each of its rows in the output at least once, whereas the table on the right will only have those rows output that match some row of the left table. When outputting a left-table row for which there is no right-table match, empty (null) values are substituted for the right-table columns.

In the below example, "Calcutta "/"Delhi"/"Chennai" from cities are the selected rows, eventhough weather does not have "Calcutta"

SELECT * FROM cities LEFT OUTER JOIN weather ON (cities.name=weather.city);



Right outer join

This query is called a right outer join because the table mentioned on the right of the join operator will have each of its rows in the output at least once, whereas the table on the left will only have those rows output that match some row of the right table. When outputting a right-table row for which there is no left-table match, empty (null) values are substituted for the left-table columns.

In the Below Example, "Delhi"/"Chennai"/"Bangalore" from weather are the selected rows eventhough cites does not contain "Bangalore".


SELECT * FROM cities RIGHT OUTER JOIN weather ON (cities.name=weather.city);


Full Join

The FULL JOIN keyword return rows when there is a match in one of the tables.

IN the Below Example "Delhi"/"Chennai"/"Calcutta"/"Bangalore" all rows from cities and weather are listed.

SELECT * FROM cities FULL JOIN weather ON (cities.name=weather.city);




Self join

We can also join a table against itself. This is called a self join. 

As an example, suppose we wish to find all the weather records that are in the temperature range of other weather records. So we need to compare the temp_lo and temp_hi columns of each weather row to the temp_lo and temp_hi columns of all other weather rows. We can do this with the following query:
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, 
W2.city, W2.temp_lo AS low, W2.temp_hi AS high 
FROM weather W1, weather W2WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;





References

Thursday 7 February 2013

Setup remote server profiling with Jprofiler7 (Windows to Linux)

What is Profiling?

Analyze the performance of a Java program/JVM, by monitoring and collecting runtime data on object allocations, garbage collection cycles, CPU and memory usage, object references, method time stamps, threads and object interactions.


What is JProfiler?


Wiki says

"JProfiler is a commercially licensed Java profiling tool developed by ej-technologies GmbH, targeted at Java EE and Java SE applications."


At all times this is my first choice when it comes to profiling.


How to setup Remote profiling with Jprofiler  from Windows to Linux?

Summary 

  1. Download the Jprofiler EXE for windows and  tar.gz for linux
  2. Copy the tar file to linux and untar
  3. Remove linux firewall and selinux restrictions (if applicable)
  4. Run the JProfiler agent in linux
  5. Install JProfiler on windows and set the trial/commerical licence
  6. From JProfiler GUI, connect to the agent running on linux(Step 4)
  7. Start profiling

Lets walk through each step and see it working!


1. Download the Jprofiler EXE for windows and tar.gz for linux from here


2. Copy the tar file to linux and untar

  • Copy the file to a folder in linux

  • untar it using  "tar -xvf <tar filename>"
    This will untar the contents to a "jprofiler7" folder.


3. Remove linux firewall and selinux restrictions (if applicable)

Linux firewall and selinux might be turned on. Just disable those using the below commands. We can be enabled later after profiling
  • turnoff firewall :  service iptables stop
  • turnoff selinux :  setenforce 0


4. Run the JProfiler agent in linux

  • We should be linking the "server to be profiled" with the "untar'd jprofiler agent"
  • To do this we should add -agent VM parameter while running the server.
  • The same port specified here should be used when connecting from the GUI.
-agentpath:<installed folder> /jprofiler7/bin/linux-x64/libjprofilerti.so=port=<port number>

example :  -agentpath:/root/jprofiler7/bin/linux-x64/libjprofilerti.so=port=11002


Sample command to startup a jetty server

java -Djetty.home=/jetty -agentpath:/root/jprofiler7/bin/linux-x64/libjprofilerti.so=port=11002 -jar /jetty/start.jar

Now the server will startup with jprofiler agent, listening to Jprofiler GUI connections at port 11002.

5. Install JProfiler on windows and set the trial/commerical licence

Using the Executable we downloaded in step 1, install Jprofiler and set the appropriate licence and then start the Jprofiler

6. From JProfiler GUI, connect to the agent running on linux machine(Step 4)

  • Follow the steps as per the below diagram, the order are specified within the yellow circle.
  • Set the Linux Host IP or name and the same port specified with -agent in step 4. Example 11002.

You will pass through these screenshots as below:





7. Start Profiling

If you see the below screen, then it means you are connected.



References:



Tuesday 5 February 2013

Different ways to Skip Maven Tests

 2 Different ways to skip tests with Maven.



  • mvn –o clean install  -DskipTests  - Use this to just avoid “RUNNING” the tests
  • mvn –o clean install –Dmaven.test.skip=true – Use this to avoid “BOTH COMPILE  & RUNNING” the tests.  (skip completely when tests have compilation errors)
 
  These 2 may appear to be the same, but they perform differently.