(Draft -Incomplete )How to design tree or hierarchical strucutre in data base ?

This article subsumes -
What are current ways to design tree structure ?
Comparision of all of them ?
Flat table approach and advatages ?














dumb approach














Clever approach

How to get second highest record from table?

Generally a daft question asked by a nincompoop to other dunce who think that this is a lallapalooza of ones's knowledge of data base queries ?(none of wasn't me :) )
Don't throw conniptions at him because it is very difficult , I think you should realize that how easy it is and it is a bugaboo to just scare away you .

guess which question ;) ?

How to get second highest record from a table in MY SQL ?
or generic how to get Nth highest record in MY SQL ?

Consider following table















How to get second highest record from a table in MY SQL ?

SELECT name , salary FROM employee WHERE salary < (SELECT max( salary) FROM employee ) order by salary DESC LIMIT 1 OR using generic query N =2 , Offset =1

SELECT name, salary FROM employee order by salary DESC LIMIT 1,1

To get Generic Nth Highest record

SELECT name, salary FROM employee order by salary DESC LIMIT (N-1),1

LIMIT X, Y --> suggest that X is row from which onward to start and Y is number of rows or offset

example of limit clause

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

What to look in code review ?

Coding Guide Lines

For some Team leads code review is like just one afternoon free from work .What these guys look in code is indentation , naming convention and all crap which is not going to reduce bugs in QA or in acceptance testing .Actually , such activities are overhead from companies . Under the name of code review it is a real time pass with vacuous comments.
For static code review we must rely on automated checking using like find bug , check style PMD etc .Many people don't know to calculate dependency , complexity of code , how to calculate cyclomatic complexities of code .Just review of indentation , formatting is useless and overhead.
In design review, which must happen at after requirement gathering stage . These people must be well versed with design patterns .At least they must know gang of four patterns .Some people I have seen do excellent procedural coding in java which is very upsetting and frustrating .

Buzz words of OOP coding like loose coupling and adhesive coding should be found in such reviews .Most important thing is neglected is thread safe coding .Eventhough These bugs are very hard to catch in code review , we should at least try to catch them early stages of project because in next staged cost of fixing is very high and futher at customer end is the highest cost and loss of trust .
The most important thing is that reviewer does not consider amount of data handled by method .If large amount of data to be processed then inefficiency of loop is going to magnify and it will cause more response time.
Proficient reviewer must catch such errors before causing serious damage to customer.
While coding consider the situation that next person who will be coding , is a desperado , reckless outlaw with three counts of felony and further more he knows your address. ;) .Or you may imagine that next coder is demon and his doppelganger is going to haunt you if you make you code inflexiable and not readbale , inefficient .

What is cost to the company?
It is really time consuming and extractives with no real time out put .So do code review for hot spots like critical module, new comer who don't know code. Instead of making long list of does and don't , real implementation of recommendations is important .Code review should be done in minimum time with maximum awareness of nasty design related bug and not just grammar check of code .shouldn't it ?

In general
1. One function should be maximum of 25 lines. Use proper indentation.
2. Follow naming conventions for classes, methods and variables.
3. Refractor the complex code in simple pieces whenever possible.
4. Put informative comment to important methods and update the same when logic is changed.
5. Minimize code complexity by reducing nested of conditional statements
(if else , while , for) statements inside one another.
6. Reduce dependency of one class on another .Too many import statements suggest more dependency and less flexibility.
7. Remove old commented code which clutters uncommented code.
8. Do not waste time to optimize, generalize the code which will be used only once.
9. Concentrate on optimization, efficiency of module on which many modules are dependent.
10. Avoid copy paste of code .Write proper reusable function for it.
11. Consider reuse of repeated and critical code.

In specific to Java
1. Coding style related
Pease refer to
http://www.oracle.com/technetwork/java/codeconv-138413.html

2. Standard practice related

1. Use private variables and give set method only if necessary.
2. Avoid try {} catch (Exception ex) .Be specific about thrown exception.
3. Try to avoid null. Return empty object in case of collection.
4. Always override hash code when you override equals
5. Select proper collection for time efficient methods.
6. Do not make public class level variables
7. Do not synchronize methods unnecessarily.
8. Always override toString() methods for data models.
9. Use comparable interface than comparator.
10. Consider thread safety when multiple threads are accessing the object.

For more please refer to

http://www.javapractices.com/home/HomeAction.do
http://www.havelund.com/Publications/JavaCodingStandard.pdf
http://www-01.ibm.com/software/webservers/appserv/ws_bestpractices.pdf


Code review
A. Static/Automated Code review
Rely on automated code review for coding style and naming convention and not for logical or design related bugs .
1. Use of PMD
Link - http://pmd.sourceforge.net/


2. Use of check style
Link - http://checkstyle.sourceforge.net/


3. Use of find bug
Link - http://findbugs.sourceforge.net/


4. Use of tools like Jdepend, TC tool to determine complexity, dependency Index of class.
- I Will be adding information on Jdepend , TCtoolkit ,SourceMonitor ,GraphViz ,DependencyFinder
Depend-o-meter etc.


B. Logical/Design related Code review

• Correct Design patterns
Link -

• http://java.sun.com/blueprints/corej2eepatterns/Patterns/index.html
  • Modular Programming
  • Flexibility for future requirement
  • Object oriented coding (Some people (from C++ )write exellent procedural code in java : ( )
  • Consider amount of data to be handled
  • Inefficient loops
  • Avoid copy paste of code at many places.
  
  
  References
  http://en.wikipedia.org/wiki/Code_conventions
  http://pdfcast.org/pdf/effective-java-programming-language-guide
  http://www.sourceformat.com/coding-standard-java.htm

-- NILESH SALPE

(NOTE : This blog is draft - no grammar check )

Detect Duplicate entries in table by composite key

table TABLE_NAME has key1 , key2 , key3 columns .


select key1,key2, key3 from TABLE_NAME group by key1, key2 , key3 having count(*)>1

Copy column from one table to other table

------------------------
EMPLOYEE TABLE
------------------------
salCodeid name
------------------------



---------------------------------------------
SALARY TABLE
----------------------------------------------
idsalCode salary basic tax bonus
---------------------------------------------



Column exists in target table then

INSERT INTO EMPLOYEE (salCodeid) SELECT idsalCode FROM SALARY

If column does not exixts

ALTER EMPLOYEE (salCodeid) SELECT idsalCode FROM SALARY

Hello world in JSF 2.0

XHTML is clean format html which follows strict rules for styles of tags .
Use of expression language in EL eases to keep code neat and clean .
JSF libraries support used of EL with xhtml .

In JSF 2.0 , it’s recommended to create JSF page in XHTML file format.
I am giving example of a web.xml with JSF and rich faces (Jboss components )

web.xml

 <?xml version="1.0" encoding="UTF-8"?> 
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
     xmlns="http://java.sun.com/xml/ns/javaee"  
     xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"  
     xsi:schemaLocation="http://java.sun.com/xml/ns/javaee  
     http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"  
     id="WebApp_ID" version="2.5"> 
 <display-name>JavaServerFaces</display-name> 
 <!-- Change to "Production" when you are ready to deploy --> 
 <context-param> 
  <param-name>javax.faces.PROJECT_STAGE</param-name> 
  <param-value>Development</param-value> 
 </context-param> 
 <!-- Welcome page --> 
 <welcome-file-list> 
  <welcome-file>faces/hello.xhtml</welcome-file> 
 </welcome-file-list> 
 <!-- JSF mapping --> 
 <servlet> 
  <servlet-name>Faces Servlet</servlet-name> 
  <servlet-class>javax.faces.webapp.FacesServlet</servlet-class> 
  <load-on-startup>1</load-on-startup> 
 </servlet> 
 <!-- Map these files with JSF --> 
 <servlet-mapping> 
  <servlet-name>Faces Servlet</servlet-name> 
  <url-pattern>/faces/*</url-pattern> 
 </servlet-mapping> 
 <servlet-mapping> 
  <servlet-name>Faces Servlet</servlet-name> 
  <url-pattern>*.jsf</url-pattern> 
 </servlet-mapping> 
 <servlet-mapping> 
  <servlet-name>Faces Servlet</servlet-name> 
  <url-pattern>*.faces</url-pattern> 
 </servlet-mapping> 
 <servlet-mapping> 
  <servlet-name>Faces Servlet</servlet-name> 
  <url-pattern>*.xhtml</url-pattern> 
 </servlet-mapping> 
</web-app> 

Navigation of pages need to be configured in a xml called faces-config.xml.
In JSF 2.0 you can manage bean in code using annotation
Still to control page flow this xml should be used .

example

 <?xml version='1.0' encoding='UTF-8'?>
<faces-config xmlns="http://java.sun.com/xml/ns/javaee" version="1.2" xsi="http://www.w3.org/2001/XMLSchema-instance" schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-facesconfig_1_2.xsd" xi="http://www.w3.org/2001/XInclude">
<application>
<view-handler>com.sun.facelets.FaceletViewHandler</view-handler>
</application>
---
managed beans
--
navigation rules
---
</faces-config>

Rename table name in SQL

RENAME TABLE TABLEA TO TABLEB

SQL table change order of column if we have already populated table

Example if table TAB has c1 - int(11) , c2 - varchar(40) , c3 - varchar (40)

/ -------- Table name - TAB ---------- ---/
--------------------------------------------------------
C1 INT (11) l C2 VARCHAR (11) l C3 VARCHAR (11)
--------------------------------------------------------

But now we want order C1, C2, C3 as C1,C3,C2 ... ofcourse you may go to views without changing physical data table . But if we want change in physical data table then ??

sample querry -

ALTER TABLE TAB MODIFY COLUMN C2 varchar(60) AFTER C3


ALTER TABLE TABLE_NAME MODIFY COLUMN COLUMN_NAME_TO_BE SHIFTED DATA_TYPE_OF_THE_COLUMN AFTER COLUMN_NAME

** Replace red colored fields with real values of your table
AFTER Querry


/ -------- Table name - TAB ---------- ---/
--------------------------------------------------------
C1 INT (11) l C3 VARCHAR (11) l C2 VARCHAR (11)
--------------------------------------------------------

Difference between SAX and DOM parser ? Which parser to Use ?

SAX (Simple API for XML)
DOM (Document Object Model)

XML is type of data base also it is data accessing objects for communication between different products written in different languages.

why we need sax parser in first place while we have traditional DOM parser at hand ?

DOM

DOM parser reads XML file and create tree based data models (objects corresponding to each tag ) in memory and thus loads whole tree at the time of reading the XML file .As whole file is loaded in memory we can get objects like all children's of node , parent of node etc .Means it can traverse from root to inner most element also from innermost element to root or any element .It can insert / update any element due to this structure .DOM parser is also used by browsers to show XSLT parsing as HTML. This is easy way to manipulate the XML files .Now is we have very large XML file and if we try to parse it with DOM from java then "JVM out of memory " exception will be thrown as it hogs all heap space .Then intuitively we can say that to avoid the problem we should load nodes which are only needed at a time in the memory ... which leads to SAX parser creation .

SAX parser

It uses event bases navigation to get particular element of XML at a time .It uses certain event based handlers to look required node .They are like start element , end of element , error etc.Because of this nature it can parse XML of gigabytes in size as it does not create DOM structure of object models in memory .Because of this it also fast and need relatively less memory .APIs of SAX parser are quite complex than intuitive DOM parser .It uses streamed reading of XML file than entire file in one time .

Java SAX parser have problem in multi threaded environment so the out put may be corrupt then two threads are accessing the document at same time .So it is needed to create different instance of SAXParser for each new thread.

It has problem when it needed whole element tree in DTD validation .

Choice -

For reading the large XML document SAX parser is best choice and it is faster than DOM in term of speed . If you want to manipulate the tree elements of large XML then it is very difficult
with this parser because you have to write down your own custom implementation .

DOM parser is made upon W3-DOM specifications and it is very easy in traversing and manipulating /inserting new elemets in XML .


Speed of DOM , SAX , XSLT for XML parsing

Cutting edge table rows reorder control

Problem with firefox is that it does not support DOM method
table is element .
table.moveRow(fromIndex, toIndex);

There is work arround for this

JS mthods after clicking on " Move Up method "
You can infer method for "Move Down " from above example.

function moveUpRow(tableId) {
var fromindex = null;

var trs = document.getElementById(tableId)
.getElementsByTagName('tbody')[0].getElementsByTagName('tr');


for ( var i = 0; i < classname ="=" fromindex =" i;"> 0){

var table = document.getElementById(tableId);

moveRow(table,(parseInt(fromindex) - 1),parseInt(fromindex));
moveUp(fromindex);
}

}

function moveRow(table, from, to)
{
var tbody = table.tBodies[0]; // Use tbody
var trFrom = tbody.rows[from]; // Make sure row stays referenced
tbody.removeChild(trFrom); // Remove the row before inserting it (dupliate id's etc.)
var trTo = tbody.rows[to];
tbody.insertBefore(trFrom, trTo);
}

Above code is working and tested for IE8, firewfox3.5 , safari , opera

Jcaptcha implementaion in J2EE web application .Step by step

How to create security certificate (SSL ) in apache tomcat server ?

IE 8 mixed content issue .

CAPTCHA - Jcaptcha a bad option ?

Jcaptcha problems

Warning - In UNIX environment JCAPTCHA need X11 server running .But In my case inspite of this server graphic image generation was not possible so I opted for reCAPTCHA.

• Main Troubles
  

  1. JVM Memory issues as graphic image is created for every new user
  2. Need X11 server running in UNIX which could be security issue in server environment.
  3. Till date No audio CAPTCHA available . It uses JPEG encoding classes which may become copyright of Oracle in future

• Advantages
  

  1. No dependency on third party
  2. Validation of CAPTCHA is done on our server .

• Recaptcha Buzz points

• It's Free! Yep, reCAPTCHA is free.
  It's Accessible. reCAPTCHA has an audio test that allows blind people to freely navigate your site.
  It's Secure. Most other CAPTCHA implementations can be easily broken.
  It's Popular. Over 100,000 sites use reCAPTCHA, including household names like Face book, Ticketmaster, and Craigslist .
  It's Easy. reCAPTCHA is a Web service. As such, adopting it is as simple as adding a few lines of code on your site. Also available in for J2EE environment. Also available for HTTPS secure connection.
  No problems with UNIX like memory , X11 server as graphic is not generated at our server
  Customization is available to some extend .
  
  

• Main trouble !

1. Dependency on reCAPTCHA server .
2. If their server is down login , registration functionality will be down .
3. For CAPTCHA versification third party server call is made which depends on security setting (firewall settings etc.) of deployed environment .

• Step by Step Implementation (in Java Server Faces )

• I . IN XHTML

 <script type="text/javascript"
src="http://api.recaptcha.net/challenge?k=your_public_key">
</script>
<noscript>
<iframe src="http://api.recaptcha.net/noscript?k=your_public_key"
height="300" width="500" frameborder="0"></iframe><br>
</noscript>

Step by Step Implementation

• II . In backingBean.java

 Boolean checkData (){
// Other code
FacesContext facesContext = FacesContext.getCurrentInstance();
HttpServletRequest request = (HttpServletRequest) facesContext
.getExternalContext().getRequest();
String challenge = request.getParameter("recaptcha_challenge_field");
String response = request.getParameter("recaptcha_response_field");
String remoteAddr = request.getRemoteAddr();
RECAPTCHASingleton RECAPTCHASingleton = RECAPTCHASingleton
.getInstance();
boolean isResponseCorrect = RECAPTCHASingleton.validate(challenge,
response, remoteAddr);
if (!isResponseCorrect)
{
flag = false;
}
//Other code
}

Step by Step Implementation

• III . New singleton Class

 import net.tanesha.recaptcha.ReCaptchaImpl;
import net.tanesha.recaptcha.ReCaptchaResponse;
public class RECAPTCHASingleton
{
// Logic for SINGLETON DESIGN PATTERN
public synchronized boolean validate(String challenge, String response,String remoteAddr)
{
ReCaptchaImpl reCaptcha = new ReCaptchaImpl();
reCaptcha.setPrivateKey();
ReCaptchaResponse reCaptchaResponse = reCaptcha.checkAnswer(remoteAddr,
challenge, response);
return reCaptchaResponse.isValid();
}
} 

• Dependency of External Jars

1. recaptcha4j-0.0.7.jar
2. Recaptcha server availability .

• References
  
  
  1. http://recaptcha.net/
  2. http://code.google.com/p/recaptcha/
  3. http://wiki.recaptcha.net/index.php/How_to_change_reCAPTCHA_colors
  4. http://wiki.recaptcha.net/index.php/Overviewhttp://wiki.recaptcha.net/index.php/Overview http://wiki.recaptcha.net/index.php/Overview
  5. http://wiki.recaptcha.net/index.php/Firewalls_and_reCAPTCHA

---------- NILESH SALPE