Thursday, November 10, 2011

ANT- 'upToDate' to rescue build time overhead

I kind of performing a refactor on ANT Build.,

Goals in mind -

* Do not run ANT targets unnecessarly.
* Make build to get pass ant-clean test. (If there are no code changes and when you run ANT build nothing should happen.)

Observed -

* ANT was running unnecessarly for TARGETS that doesn't have code changes.
   Say the build contains
   3 targets -
    
          1. CodeGen - Do's, Sql's, Ejb's, Annotations
          2. Compile - Javac
          3. Package - Jar
     
      1. Target CodeGen It contains child targets GenDo's , GenSql's, GenEjb's, GenAnnotation's - ANT doesn't know when to run and not.
      2. Compile - Javac determines code changes and run on those files alone, thus reduces the build time.
      3. Package - Jar, Ant runs Jar target only If compiled set of files are modified, thus skips the unnecessary runs.
     
      A build with out refactor will try to run all the Code gen targets irrespect of Code changes, thus increases build time.
     
      So we need a way to tell ANT Skip Targets If there are no Code Changes made.
     
      ANT provides a mechanism called UP-TO-DATE
     
      It Sets a property if a target file or set of target files is more up-to-date than a source file or set of source files.
      A single source file is specified using the srcfile attribute.
      A set of source files is specified using the nested <srcfiles> elements. These are FileSets, whereas multiple target files are specified using a nested <mapper> element.

      By default, the value of the property is set to true if the timestamp of the target file(s) is more recent than the timestamp of the corresponding source file(s). You can set the value to something other than the default by specifying the value attribute.

      If a <srcfiles> element is used, without also specifying a <mapper> element, the default behavior is to use a merge mapper, with the to attribute set to the value of the targetfile attribute.

      Normally, this task is used to set properties that are useful to avoid target execution depending on the relative age of the specified files.
     
      For Example -
     
      If you need to do an xml build only If any changes made to DTD files, you can do this by., checking the timestamp of the final output.
     
          This can be written as:

          <uptodate property="xmlBuild.notRequired">
            <srcfiles dir= "${src}/xml" includes="**/*.dtd"/>
            <mapper type="merge" to="${deploy}\xmlClasses.jar"/>
          </uptodate>
     
        sets the property xmlBuild.notRequired to true if the ${deploy}/xmlClasses.jar file is more up-to-date than any of the DTD files in the ${src}/xml directory.
     
        The xmlBuild.notRequired property can then be used in a <target> tag's unless attribute to conditionally run that target. For example, running the following target:

        <target name="xmlBuild" depends="chkXmlBuild" unless="xmlBuild.notRequired">
          ...
        </target>

        will first run the chkXmlBuild target, which contains the <uptodate> task that determines whether xmlBuild.notRequired gets set. The property named in the unless attribute is then checked for being set/not set. If it did get set (ie., the jar file is up-to-date), then the xmlBuild target won't be run.
       
        Courtesy - http://www.jajakarta.org/ant/ant-1.6.1/docs/en/manual/CoreTasks/uptodate.html
       
        Thus you can Skip unnecessary Targets, and reduces build time a lot.
     
     

Monday, July 11, 2011

Html Select width problem in IE : workaround using JQuery

Setting the fixed width of a <select> element in Internet Explorer will cause all of the select options that are wider than the select's set width to be cropped.

If you set a static width on the <select> element, any <option> elements that are wider get cut of in IE. There is no good CSS solution for this.

So here are some workarounds to rsolve the problem.

Keep the static width on the select element by default, but when moused over in IE, change the width to "auto". And then put it back to static width when moused out.

onmouseover="autoWidth(this)"
onblur="resetWidth(this)"
So when a user clicks on the select the width will automatically expand, and user moves out of the select box, the width will be reset to original.

But the problem with this approach is the original size of the select box is not maintained and the width of the box expands, thus causes the page look and feel collapse.

It has been tackled with JavaScript a number of ways. but nothing suits to the problem.

So tried in a jQuery way of solving the Problem.

$(function() {
 
  if(browser.isIE){  
    $("#select")
        .mouseover(function(){
            $(this)
                .data("origWidth", $(this).css("width"))
                .css("width", "auto");
        })
        .mouseout(function(){
            $(this).css("width", $(this).data("origWidth"));
        }); 
    }
});

   1. When mouse pointer goes over select element, keep track of the original width, then set it to "auto".

   2. When mouse pointer leaves, set width back to original width.

But again the problem of page collapse and not consistent...oops!

There is a jQuery plugin proposes a work around.

http://www.jainaewen.com/files/javascript/jquery/ie-select-width/

Download this Plugin and add it to your class path, and call the function.. thats it..

<script type="text/javascript" src="/js/jquery/plugins/jquery.ie-select-width.min.js">&#xa0;</script>
  <script type="text/javascript" language="JavaScript">
    //Workaround for IE html select issue(Width of the dropdown is not expanding w.r.t to the content width.)
    var $j = jQuery.noConflict();
    $j(document).ready(function() {
        if (browser.isIE) {
         $j('select[name*=xxxxx]').ieSelectWidth({});
        }
    });
  </script>

Applying this plugin makes the select element in Internet Explorer appear to work as it would work in Firefox, Opera etc...

However I noticed this works exactly as FF on IE versions 8 & 9, but in 6 & 7 the width of the dropdown also expands to the max width of the options but solves the Problem.

Struts Custom tag to support JSTL functions

The Struts Taglib component provides a set of JSP custom tag libraries that help developers create interactive form-based applications. There are tags to help with everything from displaying error messages to dealing with nested ActionForm beans.

Struts Taglib is composed of four distinct tag libraries: Bean, HTML, Logic, and Nested.
Bean  :   The bean tags are useful in defining new beans (in any scope) from a variety of possible sources, as well as a tag to render a particular bean (or bean property) to the output response.
HTML :    The HTML tags are used to create input forms, as well as other tags generally useful in the creation of HTML-based user interfaces. The output is HTML 4.01 compliant or XHTML 1.0 when in XHTML mode.
Logic   :  The Logic tags that are useful in managing conditional generation of output text, looping over object collections for repetitive generation of output text, and application flow management
Nested  :   The Nested tags extend the base Struts tags to allow them to relate to each other in a nested nature. The fundamental logic of the original tags doesn't change, except in that all references to beans and bean properties will be managed in a nested context.

A 'custom' tag is something which a browser will not understand, because it is not a pre-defined HTML tag. you need to write/tell the browser so that it understands what it is supposed to do.
A custom tag, is thus, a tag, you write in your Java Server Page. When the jsp compiler encounters that tag, it knows what to do with it. It generates the proper HTML after doing whatever it is supposed to do.

Custom Tag

   1. Write the tag handler class.
   2. Create the tag library descriptor (TLD).
   3. Make the TLD file and handler classes accessible.
   4. Reference the tag library.
   5. Use the tag in a JSP page.      

        * Tag Handlers implement one of three interfaces:

1.  Tag
    Implement the javax.servlet.jsp.tagext.Tag interface if you are creating a custom tag that does not need access to its interface. The API also provides a convenience class TagSupport that implements the Tag interface and provides default empty methods for the methods defined in the interface.

2.  BodyTag
    Implement the javax.servlet.jsp.tagext.BodyTag interface if your custom tag needs to use a body. The API also provides a convenience class BodyTagSupport that implements the BodyTag interface and provides default empty methods for the methods defined in the interface. Because BodyTag extends Tag it is a super set of the interface methods.

3.  IterationTag
    Implement the javax.servlet.jsp.tagext.IterationTag interface to extend Tag by defining an additional method doAfterBody() that controls the reevaluation of the body.
Sample Java program implements Custom Tag.
package com.xyz.CustomTag;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.jsp.JspException;
import javax.servlet.jsp.JspWriter;
import javax.servlet.jsp.PageContext;
import javax.servlet.jsp.tagext.BodyContent;
import javax.servlet.jsp.tagext.BodyTag;
import javax.servlet.jsp.tagext.Tag;

public class DateTag implements BodyTag {
    private PageContext pc = null;
    private Tag parent = null;
    private BodyContent bd = null;
    private boolean showInUpperCase;

    public boolean isShowInUpperCase() {
        return showInUpperCase;
    }

    public void setShowInUpperCase(boolean showInUpperCase) {
        this.showInUpperCase = showInUpperCase;
    }

    @Override
    public void doInitBody() throws JspException {

    }

    @Override
    public void setBodyContent(BodyContent arg0) {
        bd = arg0;

    }

    @Override
    public int doAfterBody() {
        try {
            String bodyString = bd.getString();
            JspWriter out = bd.getEnclosingWriter();
            SimpleDateFormat simDate = new SimpleDateFormat("dd-MM-yyyy");
            out.print((bodyString + simDate.format(new Date())).toUpperCase());
           
            bd.clear(); // empty buffer for next evaluation
        } catch (IOException e) {
            System.out.println("Error in BodyContentTag.doAfterBody()" + e.getMessage());
           
        } // end of catch

        return SKIP_BODY;
    }

    @Override
    public int doEndTag() throws JspException {
        return EVAL_PAGE;
    }

    @Override
    public int doStartTag() throws JspException {
        return EVAL_BODY_AGAIN;
    }

    @Override
    public Tag getParent() {
        return parent;
    }

    @Override
    public void release() {
        pc = null;
        parent = null;

    }

    @Override
    public void setPageContext(PageContext arg0) {
        pc = arg0;
    }

    @Override
    public void setParent(Tag arg0) {
        parent = arg0;
    }

}
         
     * Write a tag library descriptor.

      that will contain the mappings between our custom tag and the Java class (or classes) that will handle it. This library is defined within an XML document called a tag library descriptor (TLD). We'll call the TLD for our DateTag example DateTagLib.tld. Note that ".tld" is the standard extension for such files.
            <?xml version="1.0" encoding="ISO-8859-1" ?>
            <taglib>
                <tlibversion>1.0</tlibversion>
                <info>A simple tag library</info>

            <tag>
                <name>displayDate</name>
                <tagclass>myTags.DateTag</tagclass>
                <bodycontent>empty</bodycontent>
               <info>Display Date</info>
            </tag>
          </taglib>
      Although not mandatory, the TLD file is usually placed under WEB-INF/tlds/.

     * Reference the Library    

    * Declare the TLD in the web.xml file. web.xml is the standard descriptor file for any web application. The TLD is declared like:
      <webapp>
         ....
         <taglib>
            <taglib-uri>DateTag</taglib-uri>
            <taglib-location>/WEB-INF/tlds/DateTagLib.tld</taglib-location>
         </taglib>
      </webapp>
Using a Custom Tag in your page

Now that the custom tag is built and deployed, it can be used in a JSP by:
    * Declaring the TLD as a directive:
      <@ taglib uri="DateTag" prefix="test" %>
    * Using a tag "displayDate", which is a part of the TLD, in the JSP page:

      <test:displayDate attr1="value1" attr2="value2" />
As soon as the runtime encounters the "test" tag, it knows a tag in that library is going to be used. In the above example, "displayDate" in the library is used. When the runtime parses "<test:displayDate", the method of "displayDate" is triggered.

JSTL Functions 
JSTL functions are supported from version 1.1.

If you are in version 1.0 here is the steps to upgrade.

1. Reference the correct servlet specification in your deployment descriptor:

      <?xml version="1.0"?>
      <web-app version="2.4"
       xmlns="http://java.sun.com/xml/ns/j2ee"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
   2. Reference the correct JSTL uri in your JSP:
      change

      <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c'%>
      to
      <%@ taglib uri='http://java.sun.com/jsp/jstl/core' prefix='c'%>

And ofcourse upgrade the JSTL jar in your class path.

The JSTL 1.1. library provides inbuilt function support .. refer http://download.oracle.com/docs/cd/E17802_01/products/products/jsp/jstl/1.1/docs/tlddocs/fn/tld-summary.html

<%@ taglib uri="http://java.sun.com/jsp/jstl/core"
     prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions"
    prefix="fn" %>
<html>
<body>
<c:if test="${fn:length(param.username) > 0}" >
  <%@include file="response.jsp" %>
</c:if>
</body>
</html>

Use a Custom Tag with function support from Apache commons-lang.
Defining a Tag function in a web app

The TLD File

<!-- WEB-INF/tld/commons-lang.tld -->
<?xml version="1.0" encoding="UTF-8" ?>
<taglib xmlns="http://java.sun.com/xml/ns/javaee"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-jsptaglibrary_2_1.xsd"
        version="2.1">
    <description>Tags used escape characters</description>
    <display-name>escape tags</display-name>
    <tlib-version>1.0</tlib-version>
    <short-name>escape</short-name>
    <uri>http://commons.apache.org/lang/StringEscapeUtils</uri>
    <function>
        <description>Escapes the characters in a String using HTML entities.</description>
        <name>html</name>
        <function-class>org.apache.commons.lang.StringEscapeUtils</function-class>
        <function-signature>java.lang.String escapeHtml(java.lang.String)</function-signature>
    </function>
    <function>
        <description>Escapes the characters in a String using JavaScript String rules.</description>
        <name>javaScript</name>
        <function-class>org.apache.commons.lang.StringEscapeUtils</function-class>
        <function-signature>java.lang.String escapeJavaScript(java.lang.String)</function-signature>
    </function>
    <function>
        <description>Escapes the characters in a String using XML entities.</description>
        <name>xml</name>
        <function-class>org.apache.commons.lang.StringEscapeUtils</function-class>
        <function-signature>java.lang.String escapeXml(java.lang.String)</function-signature>
    </function>
</taglib>

The JSP

Somewhere near the top of the file.

<%@ taglib prefix="lang" uri="/WEB-INF/tld/commons-lang.tld"%>

...

// example of escaping for javascript

img.description = '${escape:javaScript(xxxxx)}';

// example of escaping an html attribute

title="${escape:html(pic.description)} ${escape:html(xxxxxx)}"
... />

Friday, July 1, 2011

Struts html:form with focus element creates JS error on IE

The built in struts <html:form ...  focus="[elementname]"> functionality automatically generates this X-browser
javascript.

you can see this on your browser at the end of form element.

<script type="text/javascript" language="JavaScript">
 <!--
 var focusControl = document.forms["component"].elements["contents"];
 if (focusControl.type != "hidden") {
 focusControl.focus();
 }
 // -->
</script>

With this, Internet Explorer (version 6, 7 and 8) generates the following error:

"Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus."

The problem seems to be that for some very odd reason the javascript is being called before the element is available.

So, to comeout of this, I had removed the struts generated focus field(form tag) and added a own script., below the form with a try/catch.

Something like this..

 </html:form>
-
+<script type="text/javascript" language="JavaScript">
+<!--
+ var focusControl = document.forms["component"].elements['<c:out value="${focus}"/>'];
+ try{
+ if (focusControl.type != "hidden")
+  focusControl.focus();
+ }catch(er){
+  //Fix for IE focus problem.
+ }
+//-->
+</script>

Now the error goes off...

Tuesday, June 14, 2011

Slowly Changing dimension (type - 2) on Pentaho Kettle

Recently a'm in a need to use SCD Type -2 on the datamart design, and kettle spoon as the tool...

SCD management methodologies referred to as Type 0 through 6. Type 6 SCDs are also sometimes called Hybrid SCDs.

The Type 2 method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. With Type 2, we have unlimited history preservation as a new record is inserted each time a change is made.
Courtesy : http://en.wikipedia.org/wiki/Slowly_changing_dimension

For example, take an employee table, if the employee moves to other company, the table could look like this, with incremented version numbers to indicate the sequence of changes:

emp_key | name  | emp_code |           company           | version
---------+-------+----------+-----------------------------+---------
       1 | Ashik | emp01    | WhiteHouseBusinessSolutions |       0
       2 | Ashik | emp01    | NetEd Learning Solutions    |       1
       3 | Ashik | emp01    | MindTree Ltd                |       2
       4 | Ashik | emp01    | CollabNet Inc               |       3

In Pentaho, Lets see how.,
I created two tables Employee and the Employee_detail.,

select * from employee;
 emp_key | emp_id
---------+--------
       1 | emp01

select* from employee_detail;
 emp_detail_key | name | code | company | version | date_from | date_to
----------------+------+------+---------+---------+-----------+---------
(0 rows)

Pentaho Will read the emp_id from the eployee table by a TableInput step, and I add the remaining fields as constants for the sake of this sample., and add Dimension Lookup/Update step from kettle to perform SCD -Type2




I just read the emp_id from employee using TableInput step.




and am trying to change the employee company name for every run.,


the Dimension Lookup/Update step used to do the SCD here, this componenet requires the date range fields and the version field on the SCD table. and I had specified emp_id as the lookup field.

and it changes the version accordingly., i.e whenever a change in the company pentaho creates a new version and the older ones remain as versioned.



and I had given the "type of update" as update for name and insert for company., thus name will get update if there any change in same and the company get versioned upon change.


So now the setup is ready to perform SCD., lets run the transformation.

After the first run...

select* from employee_detail;
 emp_detail_key | name  | code  |           company           | version | date_from  |  date_to
----------------+-------+-------+-----------------------------+---------+------------+------------
              0 |       |       |                             |       1 |            |
              1 | Ashik | emp01 | WhiteHouseBusinessSolutions |       1 | 2011-06-17 | 2199-12-31

Again am changing the company name..


now lets see the table..,

select* from employee_detail;
 emp_detail_key | name  | code  |           company           | version | date_from  |  date_to
----------------+-------+-------+-----------------------------+---------+------------+------------
              0 |       |       |                             |       1 |            |
              2 | Ashik | emp01 | NetEd Learning Solutions    |       2 | 2011-06-17 | 2199-12-31
              1 | Ashik | emp01 | WhiteHouseBusinessSolutions |       1 | 2011-06-17 | 2011-06-17

You can see the version update.. on change of company name.. after several runs..

select* from employee_detail;
 emp_detail_key | name  | code  |           company           | version | date_from  |  date_to
----------------+-------+-------+-----------------------------+---------+------------+------------
              0 |       |       |                             |       1 |            |
              1 | Ashik | emp01 | WhiteHouseBusinessSolutions |       1 | 2011-06-17 | 2011-06-17
              3 | Ashik | emp01 | MindTree Ltd                |       3 | 2011-06-17 | 2199-12-31
              2 | Ashik | emp01 | NetEd Learning Solutions    |       2 | 2011-06-17 | 2011-06-17

select* from employee_detail;
 emp_detail_key | name  | code  |           company           | version | date_from  |  date_to
----------------+-------+-------+-----------------------------+---------+------------+------------
              0 |       |       |                             |       1 |            |
              1 | Ashik | emp01 | WhiteHouseBusinessSolutions |       1 | 2011-06-17 | 2011-06-17
              2 | Ashik | emp01 | NetEd Learning Solutions    |       2 | 2011-06-17 | 2011-06-17
              4 | Ashik | emp01 | CollabNet Inc               |       4 | 2011-06-17 | 2199-12-31
              3 | Ashik | emp01 | MindTree Ltd                |       3 | 2011-06-17 | 2011-06-17

Thats it.., to get the latest version or the current record.,

select * from employee_detail where name = 'Ashik' and now() between date_from and date_to;
 emp_detail_key | name  | code  |    company    | version | date_from  |  date_to
----------------+-------+-------+---------------+---------+------------+------------
              4 | Ashik | emp01 | CollabNet Inc |       4 | 2011-06-17 | 2199-12-31



Wednesday, May 18, 2011

AutoIncrement sequences in Oracle/Postgres/MySql

Came across a situation, where I need to perform Auto insert on a Table's column in a sequential key order, and that too be compatible with Oracle, Postgres and MySql.

And here is how the comparision goes....,

Ofcourse we can use TableMax + 1, but the overhead is the performance...So decided to go with Pseudo columns..

Pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.
Thus suggests the use of Sequences., that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:

    * CURRVAL: Returns the current value of a sequence
    * NEXTVAL: Increments the sequence and returns the next value

A sequence can be accessed by many users concurrently with no waiting or locking.

Create Sequence:

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

Sequences are independant of any table and can be used to keep a value unique across a number of tables.

MySQL doesn't currently support sequences. There is no sequence number generator (CREATE SEQUENCE) in MySQL. However it does have an auto increment value which can be applied to a primary key of a table. This is done during the table creation.

mysql> create table seq_test (id int primary key auto_increment, name  varchar(21));

As the name suggests the value is assigned automatically, this is in contrast to Oracle where we have to call the nextval function of the sequence to return the value when we need it.

In Postgres you can use type SERIAL during table creation which is similar to AUTO_INCREMENT in MySQL.

pgsql=> CREATE TABLE seq_test (id    SERIAL PRIMARY KEY);

 Is just shorthand notation for:

pgsql=> CREATE SEQUENCE XXX START 1;
pgsql=> CREATE TABLE seq_test (id integer PRIMARY KEY DEFAULT nextval('XXX'));

Well the shorthand notation has a minor gotcha: you cannot drop the sequence that has been created automatically. You need to drop the column itself.
Thats the difference between a SERIAL PRIMARY KEY definition and the "verbose" mode.

Verbosely you can have even more control over the sequence. With SERIAL the default is something like

CREATE SEQUENCE XXX
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

By hand you can define e.g.

CREATE SEQUENCE XXX
    START n
    INCREMENT BY n
    MAXVALUE n
    MINVALUE n
    CACHE 1;

Insert in to Table using seq nextVal.

pgsql=> insert into seq_test(id, name)values(nextval('s1'), test1);
INSERT 0 1
pgsql=> select * from seq_test;
 id          name
------------------
  1          test1
(1 row)

We can also insert value of our choosing.

pgsql=> insert into seq_test(id, name)values('100', 'test2');
INSERT 0 1
pgsql=> select * from seq_test;
 id             name
-------------------------
   1            test1
 100            test2
(2 rows)

Thus, the sequence will start from its own higher point, not related to any of the table values.

pgsql=> insert into seq_test(id, name)values(nextval('s1'), 'test3');
INSERT 0 1
pgsql=> select * from seq_test;
 id             name
---------------------------
   1           test1
 100           test2
   2           test3
(3 rows)

However the same is different in MySQL If you use AUTO_INCREMENT.

mysql> insert into seq_test (name) values ('test1');

mysql> select * from seq_test;
+----+------+
| id | name |
+----+------+
|  1 | test1 |
+----+------+
1 row in set

However you can override the value of your choice.

mysql> insert into seq_test (id,name) values (100,'test2');
Query OK, 1 row affected

mysql> select * from seq_test;
+-----+------+
| id  | name |
+-----+------+
|   1 | test1 |
| 100 | test2 |
+-----+------+
2 rows in set

The sequence will then start from this new higher point.

mysql> insert into seq_test (name) values ('test3');
Query OK, 1 row affected

mysql> select * from seq_test;
+-----+-------+
| id  | name  |
+-----+-------+
|   1 | test1  |
| 100 | test2  |
| 101 | test3 |
+-----+-------+
3 rows in set

Also you can reset the sequence using an alter table command.

mysql> alter table seq_test auto_increment = 100;
Query OK, 1 row affected

This method could also be used to assign a higher number to start the sequence rather than starting with 1 by calling the alter table straight after the table creation.

So in summary auto_increment offers a great way of assigning a unique value automatically to a table. However what it doesn't allow when compared with an Oracle Sequence is different increment values, ability to use across a number of tables.
Oracle doesn't provide either AUTO_INCREMENT like MySQL or SERIAL like Postgres.,
If you look an equivalent to AUTO_INCREMENT in MySQL.,

-- Won't work:
Sql>CREATE TABLE   (seq_id NUMBER(1) DEFAULT xxx_seq.NEXTVAL);
ORA-00984: column not allowed here

Then how to create an autoincrement field in a table with a sequence ...

-- Will work:

Sql> create table seq_test(id number , name varchar2(21));
Statement processed.

First create a sequence
Sql> create sequence xxx ;
Statement processed.

Then create the trigger.
create trigger yyy before insert on seq_test
for each row
when (new.id is null)
begin
 select xxx.nextval into :new.id from dual;
end;
/
Also, the below works as like Postgres...

Sql>INSERT INTO seq_test(id, name)VALUES(xxx_seq.nextval, 'test1');

However, setting the default vlaue while Table creation to sequence.nextVal in Postgres will work unlike in Oracle.

pgsql=>create sequence xxx;

pgsql=>create table seq_test(id INT DEFAULT nextval('s1') NOT NULL, name varchar(21));

pgsql=> insert into seq_test(name)values('test4');

pgsql=> select * from seq_test;
id | name
----+-------
  3 | test4
(1 row)


Decided to use sequences while Insert.........


Tuesday, May 17, 2011

Execute PL/SQL on Oracle from Python Script

I had a chance to write a Python script that connects to Oracle and do some SQL..
I thought of bloging the basic steps., that I followed..

Install Oracle client (below packages), If the box doesn't have Oracle Instance., to run the script.

The local box requires the below packages to be installed., to run the script.

oracle-instantclient11.2-basic-11.xxxxx.xx.zip
cx_Oracle-5.xxx.tar.gz

Once the installation is done, write the script to connect to Oracle and do process.

#The Script uses the cx_Oracle to connect to the Oracle instance.. It can be of a local or remote instance. 

def grantSelectOnOracle():
    try:
        import cx_Oracle
        #Checking for the presence of database
        con = cx_Oracle.connect(u"%s/%s@%s:%s/%s" % ('DATABASE_USERNAME',
                                                     'DATABASE_PASSWORD',
                                                     'DATABASE_HOST',
                                                     'DATABASE_PORT',
                                                     'DATABASE_NAME'))


#Once the connection is established, the script tries to run a PL/SQL block, here #it grant select permission on the User passed.

        try:
            #Granting select privilege to read only user on all reports tables
            cur = con.cursor()
            grantCommand = """
begin
for i in (select table_name from user_tables)
loop
execute immediate 'grant select on '|| i.table_name||' to %s';
end loop;
end;""" % ( 'DATABASE_READ_ONLY_USER')
            cur.execute(u"%s" % grantCommand)
            cur.close()
            logger.info("Done.")
        except:
            logger.warn("Some Problem Occured in Granting Read Only Permission to User. Please set permissions Manually")
            pass
        con.close()
    except Exception, e:
        logger.error("Connection problem occured with Oracle")
        pass

Here it is., now enjoy with the Python script to execute PlSql's remotely on Oracle......!





Setup remote debugging in Eclipse

Debugging is always an interesting way to understand better how a system works internally.

And will happen with most of opensource projects.

So how can we easily debug ? The answer is : use remote debugging.

To be able to attach your Eclipse debugger to a running Java process you need to start that process with the following Java options…It can be any of your startup files for the application.. may be run.sh..etc

export JAVA_OPTS=

"-Xdebug -Xrunjdwp:transport=dt_socket,address=9000,server=y,suspend=n"

Once you have done this restart the server.

From Eclipse go to the Debug Configuration.






create a new Remote Java Application configuration for the process you want to connect to and set the port number to xxxx(eg - 9000), the same as that of the options.





Enter the hostname for the machine running the Java process. Thats it…you are on debug mode...

Java Program to run Pentaho kettle ETL Job

I got to use Kettle spoon as the ETL tool, and created Jobs and transformations.

and used the Pentaho provided API's to run the Transformations/Jobs from java.

An ETL Job file can be created and get stored in a disk.. the API requires the file path to get passed.


Provided the system has the required Pentaho libraries and the class path.,

The Jars can be downloaded from the Pentaho Site.

First Initialize the Kettle Environment from Java.,

/** static block that initializes the kettle environment */
    static {
        try {
            KettleEnvironment.init(false);
        } catch (KettleException e) {
            smLogger.error("Pentaho.Job.Run.Error.Initialization :" + e.getMessage());
        }
    }


And now pass the Kettle Job File Path to the API.

     /**
     * Executes the Job
     * @param jobPath jobFile
     * @throws ETLRunException
     */
    public void executeJob(String jobPath) throws ETLRunException {

# Initialize the Job Meta.

       try {
            JobMeta jobMeta = new JobMeta(jobPath, null, null);

            // Create the Job Instance
            Job job = new Job(null, jobMeta);
            job.getJobMeta().setInternalKettleVariables(job);

            job.setLogLevel("BASIC");

            job.setName(Thread.currentThread().getName());

            // Start the Job, as it is a Thread itself by Kettle.
            job.start();
            job.waitUntilFinished();

            if (job.getResult() != null && job.getResult().getNrErrors() != 0) {
                smLogger.error("Pentaho.Job.Run.Error.FinishedWithErrors");
                throw new ETLRunException("Pentaho.Job.Run.Error.FinishedWithErrors");
            }

            // Now the job task is finished, mark it as finished.
            job.setFinished(true);

            // Cleanup the parameters used by the job. Post that invoke GC.
            jobMeta.eraseParameters();
            job.eraseParameters();

        } catch (Exception e) {
            smLogger.error("Pentaho.Job.Run.Error.FinishedWithErrors :" + e.getMessage());
             throw new ETLRunException(e);
        }

    }
     

Log memory utilization before and after a Java process run




Sometimes running a Java process requires ..
to calculate the amount of memory it needs/takes to run.

And sometimes logging the same is useful when the job grows in future.

Java API provides a mechanism to log the memory..
In such a way.., We can calculate memory available, used memory, total memory.. etc by using the API provided methods..

Method to get the Total memory available...

Runtime.getRuntime().totalMemory()

Similarly, we can get the available memory by...

Runtime.getRuntime().freeMemory()

and by doing some calculation, we get the memory used by a Process...

Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()

Combining all these data, conclude a method that will log the Total memory allocated and the memory used by the Process.


Now time to log the memory usage before and after a job/process run.

# Write a LogUtil class which has the below method.

Log memory utilization before and after a job run

/**
     * returns the total memory and used memory.
     *
     * @return total and used memory
     */
    public static String getMemoryUsageLog() {

        return MEMORY_ALLOCATED + "[" + String.valueOf(Runtime.getRuntime().totalMemory()) + "] " + MEMORY_USED + "[" +
                       String.valueOf(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()) + "]";
    }


# Call the method from the Invoker class.

# Write a pre-execute method to call the Memory log util.

  /**
     * pre-execute method, initializes the pre-execute data
     *
     * @throws XXXRunException
     */
    protected void preExecute() throws XXXRunException {
        //memory usage before job run
        mLogger.debug("Memory Usage before Job Run : " + LogUtil.getMemoryUsageLog());       
    }

# Run the Process/Job

# Write a pre-execute method to call the Memory log util.

    /**
     * post-execute method, initializes the post-execute data
     *
     * @throws XXXRunException
     */
    protected void postExecute() throws XXXRunException {
        //memory usage after job run
        mLogger.debug("Memory Usage after Job Run : " + LogUtil.getMemoryUsageLog());       
    }

Now look for the memory logs in your loggers....! 

Connect Oracle from Shell and execute SQL.

A shell script that connects to Oracle and execute SQL..
The script will run on Oracle installed box, and do authentication against the username, password..

and verifies the Oracle_sid passed is valid. Once the authentication passes it executes the sql on Oracle.

The skeleton of the Script looks similar to the below...

Begin your main method.

main() {main() {

Make sure the User executing the script is Oracle user.

#1. Make sure only user oracle can run the script
if [ "$(whoami)" != "oracle" ]; then
   echo "This script must be run as oracle user" 1>&2
   exit 1
fi

#2. Read the Oracle System User Name

oracle_system_user=$PARAM(system)

#3. Read the Oracle System User Password

oracle_system_password=$PARAM(password)

#4. Prompt for ORACLE_SID
database_name=$PARAM(test_db)

#5. Check the given credentials are valid and that the SID exists.
checkAuth # Have this function created outside of main method.

#6. If, the Authentication suceeds, Export the Oracle SID
export ORACLE_SID=database_name

#7. Execute the Query you need to.
echo "Processing..............please do not exit.........."
executeProcess # Have this metod outside of main method.
echo "Execution Success........!"

# Close the main method.

}

main


# function to check if the given SID is valid.
checkAuth() {
    #Connect to oracle
    sqlplus -s -l $oracle_system_user/$oracle_system_password@$database_name << STD_IN
    exit;
STD_IN
    errorCode=$?   
        if [ ${errorCode} -ne 0 ];then
            echo "Unable to connect the DB with the supplied credentials."
            exit 1
        fi   
}


# function to execute process, DATABASE_USERNAME.
executeProcess() {
    #Connect to Oracle
    sqlplus -s -l $oracle_system_user/$oracle_system_password@$database_name << STD_IN   
    select sysdate from dual;   
    exit;
STD_IN
}


For reading the Params we can prompt the User to enter the valuse dynamically., by writing a readParam function.,
instead of hardcoding the values for username, password and sid values.. In that way the script can be uses as dynamic.

Similarly, the executeProcess method can be used to execute Plsql blocks.

The command that goes between "<< STD_IN" and "STD_IN" will be directly interpretted by Oracle, so we can have
only the valid sql commands inside... and can give any user defined name for "STD_IN"..it can be "XXXYYZZ".

that is only to say the interpreter the commands inside will get directly executed....!

Thursday, January 27, 2011

Browser - Server Communication(Http Caching)

Hi, Recently I had faced a very common web app problem...and there are few interesting things to share with..

User Profile page - where User can upload an Image and can change If he wants.. and to upload the Image we made an Action class to respond.. and the thing is the Url is static.. thus causes the Problem..
Even though the User is Changing the Picture the browser displays the Old picture(Edit -> View)..Post to Get ..the url to fetch the Image is static the actual request to Fetch the Picture is not Fired..the browser simply fetches the Image from the Cache..Unless If we manually refresh the page..

Two things to note down here.. Either
a) we can tell the browser not to use the Cache or
    * 1 Option One: Use a Cache-Control Header with 's-maxage'
    * 2 Option Two: Use a Query String
    * 3 Option Three: Use a "Zero" Expires Header
    * 4 Option Four: Use a Cache-Control Header with 'private'
    * 5 Option Five: Use a Cache-Control Header with 'no-cache'
    * 6 Option Six: Use a Cache-Control Header with 'no-store'
b) use the cache for may be for some period of time..
* 1 Use a Cache-Control Header with 'maxage' with time period specified.
Problem with option a) is the performance hit..Every time a new request made eventhough the picture is not actually changed by the User.

Problem with option b) If user changes the Picture the Browser uses the Cached version. Oops...

Is there a way to tell the Browser, If the Picture modified at the Server side use the updated one If not use the one in Cache..Oh this is been the perfect solution, If it can be achieved.. !
 So we need to communicate with the browser.. the only way via through the HTTP response..
The Browser will normally Interpret the response and initiate a new request..

The Http Response Header which suits very close is "max-age=0"

max-age=0 implies that the content is considered stale (and must be re-fetched) immediately

It can be used by either by the Browser(via Request) or by the Server (via Response)

When "max-age=0" added in Response Header by the Server It simply tells caches/Browsers  the response is stale from the get-go and so they SHOULD revalidate the response before using a cached copy. ( I tried this option with Mozilla/IE8 but both are not performing any Validation and it causes a new request Every time..It's simply behaves like not using the Cache.)

When "max-age=0" added in Request Header by the Browser , then each cache along the way will revalidate its cache entry (eg. with the "If-Not-Modified" header) all the way to the origin server. If the reply is then 304 (Not Modified), the cached entity can be used.Else If it's 200(Modified), Use the Updated one.

When sent by the Browser: Request Header with "max-age=0"


Response From Server with Status Code :



So...the Browser uses "max-age=0" in the Request Header the Server responds with proper status code(304/200) and the Cache entries are used accordingly.

If Server uses "max-age=0" in response Header  "response.setHeader("Cache-control","max-age=0");" the Browser always makes a new request to the Server...may be can avoid the actual download cost by using server side logic, but not the overhead an additional Http Call to the Server!!!