Blog do projektu Open Source JavaHotel

piątek, 28 września 2012

db2odbc_fdw, db2 and postgresql

Introduction
Postgresql FDW and ODBC allows to get access to all databases having ODBC interface. But I'm not happy with this odbc_fdw implementation and decided to create my own.
Implementation
The source code is available here. Just download, read README file and make install. At the beginning I planned to create DB2 specific fdw using CLI, (Call Level Interface) but because DB2 CLI is almost identical with ODBC I decided to implement purely ODBC interface.
Comparison with odbc_fdw
  • Much smaller (600 lines of source code against 1200)
  • Simpler : just define DSN name, ODBC credentials mapping and query to run
  • Full signalling of connection and query errors.
  • Proper handling of NULL value
Problems
  • Wrapper reconnects with ODBC database every time foreign table is scanned. The performance could suffer that way. Maybe worth considering is connecting only for the first time and next time table is scanned just reuse connection opened before. But it requires passing state between wrapper invocation and could be the source of several problem.
  • Tuple is created by PostgreSQL API method BuildTupleFromCStrings. So ODBC firstly decodes all columns to string and later PostgreSQL engine encodes these string to appropriate format. Maybe performance would improve if direct data type were used without string intermediary format. 
  • For some reason DB2 decimal are converted to string format using coma (,) as decimal character. But posgresql expected dot (.) as decimal character so rather awkward solution (for number data types replace all , with .) is used. Some more elegant solution is needed.
  • PlanForeignScan is implemented in a simple way - just put some constant values. odbc_fdw solution run (select * from .) query to retrieve number of rows in the query. But using this solution means performance degradation - instead of one query two queries are executed. So in search of more advanced solution without performance degradation.
  • What about 'big' columns like: LOB, BLOB etc.
Future
The solution was tested on Fedora 17 (64 bit) against DB2 10.0 database. Next step is to test db2odbc_fdw wrapper against other databases and also test it on Windows platform. But to accomplish it I'm planning to prepare some regression test and run this test again every database. The main purpose is to check if all standard data types are converted correctly.


czwartek, 13 września 2012

CellTable and line wrapping

Introduction
In the previous (before GWT 2.4) it was difficult to add "no wrap" attribute to the CellTable.







































It was easy while creating user interface in HTML/CSS - just add "no-wrap" attribute to
tag or "white-space: nowrap" style. But it was difficult in GWT because there was no direct API for modifying (cell) attribute.
The only solution I found was to make copy and paste of DefaultCellTableBuilder and and modify it a little bit.


/**
 * IMPORTANT: copy and paste of DefaultCellTableBuilder. The only difference is
 * to add "nowrap" to td tag. Because all attributes in class are private I have
 * to copy also constructor with all attributes (cannot extends
 * DefaultCellTableBuilder)
 * 
 */

class MyCellTableBuilder<T> extends AbstractCellTableBuilder<T> {

    private final String evenRowStyle;
    private final String oddRowStyle;
    private final String selectedRowStyle;
    private final String cellStyle;
    private final String evenCellStyle;
    private final String oddCellStyle;
    private final String firstColumnStyle;
    private final String lastColumnStyle;
    private final String selectedCellStyle;

    // enhancement
    private boolean addNoWrap = false;
    
    /**
     * @return the addNoWrap
     */
    boolean isAddNoWrap() {
        return addNoWrap;
    }

    /**
     * @param addNoWrap the addNoWrap to set
     */
    void setAddNoWrap(boolean addNoWrap) {
        this.addNoWrap = addNoWrap;
    }
    // ===========


    MyCellTableBuilder(AbstractCellTable<T> cellTable) {
        super(cellTable);

        // Cache styles for faster access.
        Style style = cellTable.getResources().style();
        evenRowStyle = style.evenRow();
        oddRowStyle = style.oddRow();
        selectedRowStyle = " " + style.selectedRow();
        cellStyle = style.cell();
        evenCellStyle = " " + style.evenRowCell();
        oddCellStyle = " " + style.oddRowCell();
        firstColumnStyle = " " + style.firstColumn();
        lastColumnStyle = " " + style.lastColumn();
        selectedCellStyle = " " + style.selectedRowCell();
    }

    @Override
    public void buildRowImpl(T rowValue, int absRowIndex) {

        // Calculate the row styles.
        SelectionModel<? super T> selectionModel = cellTable
                .getSelectionModel();
        boolean isSelected = (selectionModel == null || rowValue == null) ? false
                : selectionModel.isSelected(rowValue);
        boolean isEven = absRowIndex % 2 == 0;
        StringBuilder trClasses = new StringBuilder(isEven ? evenRowStyle
                : oddRowStyle);
        if (isSelected) {
            trClasses.append(selectedRowStyle);
        }

        // Add custom row styles.
        RowStyles<T> rowStyles = cellTable.getRowStyles();
        if (rowStyles != null) {
            String extraRowStyles = rowStyles.getStyleNames(rowValue,
                    absRowIndex);
            if (extraRowStyles != null) {
                trClasses.append(" ").append(extraRowStyles);
            }
        }

        // Build the row.
        TableRowBuilder tr = startRow();
        tr.className(trClasses.toString());

        // Build the columns.
        int columnCount = cellTable.getColumnCount();
        for (int curColumn = 0; curColumn < columnCount; curColumn++) {
            Column<T, ?> column = cellTable.getColumn(curColumn);
            // Create the cell styles.
            StringBuilder tdClasses = new StringBuilder(cellStyle);
            tdClasses.append(isEven ? evenCellStyle : oddCellStyle);
            if (curColumn == 0) {
                tdClasses.append(firstColumnStyle);
            }
            if (isSelected) {
                tdClasses.append(selectedCellStyle);
            }
            // The first and last column could be the same column.
            if (curColumn == columnCount - 1) {
                tdClasses.append(lastColumnStyle);
            }

            // Add class names specific to the cell.
            Context context = new Context(absRowIndex, curColumn,
                    cellTable.getValueKey(rowValue));
            String cellStyles = column.getCellStyleNames(context, rowValue);
            if (cellStyles != null) {
                tdClasses.append(" " + cellStyles);
            }

            // Build the cell.
            HorizontalAlignmentConstant hAlign = column
                    .getHorizontalAlignment();
            VerticalAlignmentConstant vAlign = column.getVerticalAlignment();
            TableCellBuilder td = tr.startTD();
            td.className(tdClasses.toString());
            if (hAlign != null) {
                td.align(hAlign.getTextAlignString());
            }
            if (vAlign != null) {
                td.vAlign(vAlign.getVerticalAlignString());
            }
            // wrap enhancement
            if (addNoWrap) {
              td.attribute("nowrap", "true");
            }

            // Add the inner div.
            DivBuilder div = td.startDiv();
            div.style().outlineStyle(OutlineStyle.NONE).endStyle();

            // Render the cell into the div.
            renderCell(div, context, column, rowValue);

            // End the cell.
            div.endDiv();
            td.endTD();
        }

        // End the row.
        tr.endTR();
    }
}

The only important difference is
 // wrap enhancement
            if (addNoWrap) {
              td.attribute("nowrap", "true");
            }
in the middle. But because of code duplication I felt unhappy with that rather lame solution.
How it can be resolved now
Starting from GWT 2.4 version things have looked better because additional method setCellStyleName was added to the  Column interface which resolved the issue. So the solution now is:
Add class name to the css file.
.no_wrap_cell_style {
  white-space: nowrap;
}
And main code now is (source file PresentationTable)
    @Override
    public void setNoWrap(boolean noWrap) {
        for (int i = 0; i < table.getColumnCount(); i++) {
            Column co = table.getColumn(i);
            co.setCellStyleNames(noWrap ? "" : IConsts.nowrapStyle);
        }
        table.redraw();
    }
Demo version is available here. "FindTest"->"Ustawienia"->"Zawijaj linie"

piątek, 7 września 2012

DB2, HADR and J2EE

Introduction
HADR works perfectly with stand alone Java application using Driver Manager interface. But what about Web applications where common practice is a connection by means of DataSource ?
Sample application
 To check it I created a simple GWT application (running on Tomcat container) connecting to HADR enabled DB2 and doing several simple actions like before. Full source code is available here. I reused the whole interface and implementation of IJDBCAction without changing any single line.
The application itself looks like (I did not pay too much attention to GUI design)


















It is nothing more but Web (GWT) interface to IJDBCAction.
The only difference is of course the method of obtaining JDBC Connection.

  private class GetConnection implements IGetConnection {

                @Override
                public Connection getCon() throws SQLException, ClassNotFoundException {
                        InitialContext ic;
                        DataSource ds;
                        try {
                                ic = new InitialContext();
                                ds = (DataSource) ic.lookup("java:comp/env/jdbc/SAMPLE");
                                Connection c = ds.getConnection();
                                return c;
                        } catch (NamingException e) {
                                throw new ClassNotFoundException(e.getLocalizedMessage());
                        }
                }

        }

The most important thing is defining the data source (server.xml for Tomcat)
    
<Resource auth="Container" driverClassName="com.ibm.db2.jcc.DB2Driver" name="jdbc/SAMPLE" password="db2had1" 
type="javax.sql.DataSource" 
      url="jdbc:db2://think:50001/SAMPLE:clientRerouteAlternateServerName=think;clientRerouteAlternatePortNumber=50002;" 
username="db2had1" >
The crucial point is adding additional connection parameters describing alternative DB2 server participating in HADR configuration. Beside that there is nothing specific in this application.
Additional remarks
  • I reused IJDBCAction implementation from JavaHadr project by means of Eclipse "source link". The only disadvantage that I was unable to use PersonRecord bean as a transient object in GWTHadr application. The nice feature of GWT is the possibility to share code between server and client part. It is possible to achieve that but it requires to decompose JavaHadr application into two parts.
  • Adding additional parameters to URL is not the only method to create DataSource connection HADR enabled. The second method is to create an instance of DB2ClientRerouteServerList and pass it to DataSource as a JNDI entry. It is described here in more detail.