Blog do projektu Open Source JavaHotel

niedziela, 1 kwietnia 2012

Search and filter enhancement

General

I added simple enhancement to search and filter interface.
It is not easy to design a good search and filter mechanism. On one hand it should be powerful enough to allow the user to find what he/she needs, on the other it cannot be complicated because it would be rejected. So how to combine together effectiveness and usability. I don't mean that I'm in a possession of the magic formula but to my thinking the most convenient way is the "from - to" or "equal to" paradigm. Something like: "find all persons of age from 20 to 30" or "find all persons living in .. eg. find all persons whose place of residence is equal to ..".
Another approach to make the filter/searching mechanism as simple as possible is avoiding any additional improvement or modifiers (check or radio boxes) something like "in case of something cannot be found try something else" because it makes all stuff very complicated and finally it is not obvious what is looked for and what is found.
So I ended up with simple "from value" - "to value" schema without any buetyfiers.




















The formula is a simple as follows:
  • If neither "from" nor "to" value is entered then this field (attribute) is ignored during searching.
  • If only "from" value is enter ("to" remains empty) then mechanism picks up rows equal to the "to" value.
  • If both "from" and "to" values are entered then all rows between (inclusive) them are selected.
  • If only "to" value is entered ("from" remains empty) then all rows less or equal to the "to" value are selected.
  • All "from" - "to"  lines are participating in the query in "and" manner. For instance: if filter for "Symbol" attribute is used (meaning that either "from" or "to" value is not empty) and the same for "Nazwa" attribute then all rows passing both tests are selected.

To my experience that simple and intuitive mechanism is well appreciated by most users and enough in  most cases. Of course it is possible to enrich this schema by adding a lot of checboxes like ("and", "or", "equal", "less then") but it makes all stuff more complicated and does not bring many more value.

But, unfortunately, I was unable to keep to it any longer. Very often user needs to find something like "find all rows equal or greater than value" and this mechanism does not allow it. It is possible to put into "to" field very big value but it is artificial.
So I ended up with adding additional check box(GUI layout needs some improvements) :


The meaning of this additional check box is as follows.
If "from" value is entered and "to" value is empty then
  • If checkbox is checked then select all rows equal to the "from" value (default).
  • If check box is unchecked than find all rows equal or greater then "from" value.
 I'm not very happy with that because I had to break a rule (no single checkbox at all) but I was unable to find any other solution.

Demo version
Demo version is available here. The source code is available here. The data is persisted by means of Google App Engine for Java (JPA). The code for persistence layer and entity bean is also available.

niedziela, 25 marca 2012

Simple calendar

I extracted simple "calendar" component from JavaHotel application. It is nothing more than several controls allowing user to navigate through period of time. It does not make a lot of sense to run it as a stand-alone but can be used as a part of larger application.

Demo version is available (click on ScrollControll).
http://javahoteltest.appspot.com/

The source code for this demo :

  private class DrawClass implements IDrawPartSeason {

        @Override
        public void setW(IGWidget w) {
            hP.add(w.getGWidget());
            hP.add(vP);
            vP.setBorderWidth(1);
            vP.setSpacing(15);
        }

        @Override
        public void refresh(IDrawPartSeasonContext sData) {
            int no = sData.getLastD() - sData.getFirstD() + 1;
            if (vP.getWidgetCount() != no) {
                vP.clear();
                for (int i = 0; i < no; i++) {
                    vP.add(new Label(""));
                }
            }
            for (int i = 0; i < no; i++) {
                Date d = sData.getD(sData.getFirstD() + i);
                String s = DateFormatUtil.toS(d);
                Label l = (Label) vP.getWidget(i);
                l.setText(s);
            }

        }

    }
Callback function should implement two methods. The first (launched only once at the beginning) delivers widget containing the controls which should be placed somewhere in the display.
The second responds to the user movement through the period of time. In this example simply refresh the cells.
The source code : interfaces and implementation.

piątek, 10 lutego 2012

So wise so young

http://shakespeare.mit.edu/richardiii/full.html

GLOUCESTER
[Aside] So wise so young, they say, do never live long.

niedziela, 5 lutego 2012

My next Eclipse plugin, DB2 and PL/SQL

Introduction
I created my next Eclipse plugin. Full source code is available here. This time a little bit more complicated one. It allows extracting and working with PL/SQL packages (specification and body) and deploy them again. It is not a mistake - PL/SQL (SQL Oracle dialect) packages can be created and run against DB2 by virtue of Oracle Compatible Mode in DB2.
Of course - it is rather a lightweight version.
My purpose was also to practice some additional features of Eclipse plugin I have not used so far: project nature, new project wizard,  project properties, and integration with Data Tools Platform component.
Project nature
Eclipse project nature  does not mean anything special. It allows to distinguish one project from another and to define some actions or properties specific to some project. For instance : "Extract PL/SQL objects" actions (look below) makes sense for a project with nature "Database Object" only but does not make any sense for any other project.
Defining project nature is very simple. Just add an entry in plugin.xml file.

<extension
         id="databaseobject.category.projectNatureId"
         point="org.eclipse.core.resources.natures">
      <runtime>
         <run
               class="databaseobject.nature.ProjectNature">
         </run>
      </runtime>
</extension>

and supporting class (with empty content in this case).
New project wizard
Having project nature defined I created New Wizard Project action. It creates Database Objects project and applied project nature to it.

 <extension
         id="databaseobject.category.wizards"
         name="Custom Wizard"
         point="org.eclipse.ui.newWizards">
         <category
            id="databaseobject.category.wizards"
            name="DatabaseObject">
      </category>
        <wizard
            category="databaseobject.category.wizards"
            class="databaseobject.wizards.NewProjectWizard"
            id="databseobject.wizard.new.custom"
            name="Database Objects project">
      </wizard>
      </extension>

Wizard class - available here.






















Project properties
Next step is to add some specific properties to the project (identified by project nature).

<extension
      point="org.eclipse.ui.propertyPages">
   <page
         class="databaseobject.properties.DatabaseObjectPropertyPage"
         id="databaseobject.properties.DatabaseObjectPropertyPage"
         name="Database Objects properties">
  <filter name="nature"
     value="databaseobject.category.projectNatureId"/>
   </page>
</extension>
By means of project nature this property will be visible only for this type of project and absent in all other projects.
Supporting class is  defined here. This property is very simple and allows assign DB2 database connection (look above) to the project.
















Integration with Eclipse Data Tools Platform
Every Java developer knows very well how to set up JDBC connection to any database (jar files, driver, URL etc). But Eclipse plugin developer does not need reinvent this wheel again, it is done already. The only effort is to make usage of goodies provided by Data Tools Platform.






















Unfortunately - integration with Data Tools Platform is not easy task and requires tedious browsing through doc and sample.  But after being connected we can run SQL statements by using standard JDBC methods and tools.
Several useful pieces of code can be found here.
Extract existing PL/SQL packages from DB2 database
First step to work with PL/SQL packages is to extract them from DB2 database into our Database Object project.
Enumerating and retrieving PL/SQL packages is performed by several DB2 specific SQL statements.
Source code

     /** Statement enumerating all PL/SQL packages. */
        private static final String SELECTPACKAGESTATEMEN = "SELECT MODULESCHEMA, MODULENAME FROM SYSCAT.MODULES WHERE MODULETYPE = 'P' ORDER BY MODULESCHEMA"; //$NON-NLS-1$
        /** Statement retrieving package specification. */
        private static final String SELECTPACKAGESPEC = "SELECT TEXT FROM DBA_SOURCE WHERE SCHEMA = ? AND TYPE = 'PACKAGE' AND NAME = ?"; //$NON-NLS-1$
        /** Statement retrieving package body. */
        private static final String SELECTPACKAGEBODY = "SELECT TEXT FROM DBA_SOURCE WHERE SCHEMA = ? AND TYPE = 'PACKAGE BODY' AND NAME = ?"; //$NON-NLS-1$
To display and select which packages are to be extracted I reused Feature Tree created in my previous Eclipse project.



















After double clicking on any PL/SQL package name the content of this package is displayed.


















Project with some PL/SQL packages extracted
After extracting required packages our project contains package specification and body as a plain text file and by using Eclipse text editor it is possible to modify and develop them.
























Deploying PL/SQL packages into DB2 database
Of course - also opposite operation is possible. After modifying package content we can put it again into DB2.




















Deploying package into DB2 is done by standard JDBC Statement.execute method. Source code :


// get jdbc connection
                        Connection jdbc = PluginUtil.getConnection(iP);
                        // read and prepare script as string
                        String fileName = PluginUtil.getFileName(sel);
                        FileReader r = new FileReader(new File(fileName));
                        String sql = "";
                        int ch;
                        while ((ch = r.read()) != -1) {
                                sql += (char) ch;
                        }

                        // run sql statement (deploy PL/SQL package here)
                        Statement stmt = jdbc.createStatement();
                        stmt.execute(sql);
Nothing specific is necessary to accomplish it.
Conclusion, future.
Of course, this plugin is rather a skeleton and is unlikely to be used in any production environment. But can be used as a starting point for future development.

  • Add "New package" action. Creates empty package specification and body
  • Deploy action also available from editor
  • Deploy group of packages at the same time
  • "Run" action. Allows defining and executing SQL statement which calls some package method for testing purpose.
  • Better integration with Data Tools Platform, use SQL editor available there. Unfortunately Data Tools Platform package is not enabled for PL/SQL packages in DB2 and it is not possible to deploy them.
  • Make usage of "SQL Results" view provided with Data Tools Platform for keeping tracks of all database activities (extracting and deploying).
IBM Data Studio
One can ask if it makes any sense to develop this skeleton if IBM Data Sudio is available for free and contains all that functionality and much more. IBM Data Studio is a very good choice but I found some problems there.
  • It is not possible to deploy package specification only (without package body).
  • Package content is persisted as XML document and there is no easy method to extract it into plain text file (only by copy and paste).
  • Because of that also Eclipse "Compare" text does not work as expected (compares XML files).
But on the other hand IBM Data Studio is still evolving tool (version 3.1 was released on November 2011) and may be the next version will contain a solution to the problems described above.

poniedziałek, 26 grudnia 2011

XML or not XML

Problem
For some time I have been working on adding invoice making to javahotel application. I had to resolve a problem how to keep invoice data in the database. One invoice contains a lot of data of different data types and it is possible to design a table with a set of columns reflecting invoice data.
But I found the following problems:
  1. There are a lot of different invoice types and every invoice type contains a set of data common to all invoices and some data specific to the invoice type given. So I have to design a different relational table for every invoice type or one common table containing the superset of all possible data. The second approach means that probably a lot of columns would be empty for  a particular invoice.
  2. Invoice contains also a list of invoice details (different  for every invoice). So I have to design the second table with invoices details ("details" could be different for every invoice type) and connect them by one-to-many relation.
  3. I cannot predict at this moment  all possible invoice types. Probably in the future it will be necessary to add new data to the invoice or redesign existing. But modifying relational data in a production environment is a complicated task and should be avoided as much as possible.
Solution idea
The solution is to keep invoice data as XML string in relational table. But XML format is convenient at server-side and less convenient at client-side. Although GWT provides tools for handling XML types (link) it requires some additional effort and not reads well. Designing a POJO class for keeping invoice data and deserialize XML into POJO object is having the same disadvantages as described above.
But I found that very flexible and useful entity for keeping data is simple Map class. Instead of :
 POJO container;
 Object val =  container.getInvoiceDate();
just use:
Map container;
Object val = container("InvoiceData");
Map does not require redesigning if invoice structure changes and could be the same for any invoice type.
So the solution is to keep invoice data as XML string in the database and transform it to the Map class before transporting to client side and opposite.
Solution
So I created a general solution for transforming XML to Map (and opposite) at server side and use Map as a transient object. Client is receiving and using Map and is sending Map back to the server. General sequence of actions is as follows.
  1. Client request
  2. Server retrieves XML string from database and transforms to Map
  3. Map is sent back to client
  4. Client sends Map to server
  5. Server transforms Map to XML and XML string is stored in the database.
Common data structure
This package is common for client and server.

It is an interface (Map) for keeping data. Should be extended for concrete implementation.  It is a subset of Map interface. Keys are XPath selector for the node containing data.

package com.gwtmodel.table.mapxml;

import java.util.Set;

/**
 * @author hotel
 *
 */
public interface IDataContainer {
   
    public Set getKeys();

    public Object get(Object key);

    public Object put(String key, Object val);

}
It is an abstract template class for the whole data (invoice). It contains main body (as IDataContainer) and list of invoice details (as list of IDataContainer). This class should extended for concrete implementation.
package com.gwtmodel.table.mapxml;

import java.io.Serializable;
import java.util.List;

/**
 * @author hotel Template class for keeping IDataContainer data.
 */
@SuppressWarnings("serial")
abstract public class DataMapList implements
        Serializable {

    /** Main body of data. */
    private T dFields;
    /** List of lines in shape of IDataContainer. */
    private List dLines;

    public DataMapList(T dFields, List dLines) {
        this.dFields = dFields;
        this.dLines = dLines;
    }

    /**
     * @return the dFields
     */
    public T getdFields() {
        return dFields;
    }

    /**
     * @return the dLines
     */
    public List getdLines() {
        return dLines;
    }

    /**
     * Adds next line to the dLines and returns IDataContainer just added
     * 
     * @return IDataContainer added
     */
    abstract public T addToLines();
}

It is limited to one list of data but can be easily extended.
Solution for server-side
The package is available here.
The solution requires to define pattern XML. An example is available here. Pattern is an XML file with empty content (only tag structure is defined) and type attribute which describes the content of the tag. Interpreting types and transforming between Map object and XML texts is done by IXMLTypeFactory interface (look below).
So creating XML string from IDataContainer map is done by means of filling the pattern XML with text content. Map keys are XPath pointers for XML document.
And opposite - deserialize XML string into IDataContainer is done by scanning XML file, taking 'type' info from pattern XML and filling the IDataContainer with key values.
For concrete implementation it is necessary to customize IXMLTypeFactory interface.

package com.gwtmodel.mapxml;

/**
 * @author hotel Factory interface providing user specific data
 */
public interface IXMLTypeFactory {

    /** Common types. */
    String DATE = "date";
    String DECIMAL = "decimal";
    String INT = "int";
    String INTEGER = "integer";
    String LONG = "long";

    /** Name of type attribute. */
    String TYPE = "type";

    /** Returns name of 'lines' element. */
    String getLinesTag();

    /** Returns name of single line in 'lines' section. */
    String getLineTag();

    /**
     * Creates object from string (element text). Object will be inserted into
     * IDataContainer map.
     * 
     * @param xType
     *            Type string (or null). Values is taken from 'type' attribute
     * @param s
     *            String value
     * @return Object
     */
    Object contruct(String xType, String s);

    /**
     * Opposite to construct. Transforms object to string
     * 
     * @param xType
     *            Type string (or null).
     * @param o
     *            Object taken from IDataContainer map
     * @return String to be inserted into XML string
     */
    String toS(String xType, Object o);
}

There is also available default implementation of this interface. It can be used "as is" or extended if customization is necessary.
package com.gwtmodel.mapxml;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.gwtmodel.table.common.CUtil;
import com.javahotel.common.command.PaymentMethod;

/**
 * @author hotel Default implementation of IXMLType Factory. Can be used 'as is'
 *         or extended
 */
public class SimpleXMLTypeFactory implements IXMLTypeFactory {

    protected final SimpleDateFormat fo = new SimpleDateFormat("yyyy-MM-dd");

    public final static String PAYMENT = "pay";

    protected boolean isInt(String xType) {
        return xType.equals(INT) || xType.equals(INTEGER);
    }

    @Override
    public Object contruct(String xType, String s) {
        if (CUtil.EmptyS(xType)) {
            return s;
        }
        if (xType.equals(PAYMENT)) {
            return PaymentMethod.valueOf(s);
        }
        if (xType.equals(DATE)) {
            try {
                return fo.parse(s);
            } catch (ParseException e) {
                e.printStackTrace();
                return null;
            }
        }
        if (xType.equals(DECIMAL)) {
            return new BigDecimal(s);
        }
        if (isInt(xType)) {
            return new Integer(s);
        }
        if (xType.equals(LONG)) {
            return new Long(s);
        }
        return s;
    }

    @Override
    public String toS(String xType, Object o) {
        if (CUtil.EmptyS(xType)) {
            return (String) o;
        }
        if (xType.equals(DATE)) {
            Date d = (Date) o;
            return fo.format(d);
        }
        if (xType.equals(PAYMENT)) {
            PaymentMethod pa = (PaymentMethod) o;
            return pa.toString();
        }
        if (xType.equals(DECIMAL)) {
            BigDecimal b = (BigDecimal) o;
            return b.toString();
        }
        if (xType.equals(LONG)) {
            Long l = (Long) o;
            return l.toString();
        }
        if (isInt(xType)) {
            Integer i = (Integer) o;
            return i.toString();
        }
        return (String) o;
    }

    @Override
    public String getLinesTag() {
        return "//Lines";
    }

    @Override
    public String getLineTag() {
        return "Line";
    }

}
Additional classes description

Example implementation

Implementation of IDataContainer.

Implementation of DataMapList.

Entity bean for keeping invoice data: link. Invoice data is stored as:
@Basic(optional = false)
private Text invoiceXML;
Transient object used at client side: link. All keys (XPath selector) are defined as constants

Source code for Map->XML transformation.


      sou.getInvoiceD().getdFields()
                .put(InvoiceP.INVOICENUMBER, sou.getName());
        String xml = HotelCreateXML.constructXMLFile(iC, IMess.INVOICEPATTERN,
                sou.getInvoiceD());
        boolean ok = false;
        if (xml != null && HotelVerifyXML.verify(iC, xml, IMess.INVOICEXSD)) {
            ok = true;
        }
        if (!ok) {
            iC.getLog().getL().info(xml);
            String mess = iC.logEvent(IMessId.INPROPERINVOICEXML,
                    dest.getName(), IMess.INVOICEXSD);
            throw new HotelException(mess);
        }
        dest.setInvoiceXML(xml);

Code for opposite XML->Map transformation.
        HotelChangeXMLToMap.constructMapFromXML(iC, dest.getInvoiceD(),
                sou.getInvoiceXML());
        String i = (String) dest.getInvoiceD().getdFields()
                .get(InvoiceP.INVOICENUMBER);

Additional information
There is also additional disadvantage for XML - searching and query running. Because it is not stored as a regular relational columns standard SQL statement cannot be used. So searching in XML requires additional effort - for instance in Google App Engine there is no standard method for scanning through XML. In order to find something one has to scan through table, retrieve XML string and by means of Java programming search through XML.
But there is good news for DB2 users. DB2  (available also for DB2-Express free edition) provides built-in engine (pureXML) for querying XML data without extracting them to the application.
For instance:
Finding all invoices greater then 10000$ can be as easy as running a query:

select * from invoice

where xmlexists('$c/Invoice/Total/Total > "10000"'

passing invoice.invoiceXML as "c")

wtorek, 6 grudnia 2011

Oracle, awk, tablespace

Problem
Assume we have the Oracle database schema export and want to deploy this schema to another database.
CREATE TABLE "SCOTT"."BONUS" 
( "ENAME" VARCHAR2(10), 
"JOB" VARCHAR2(9), 
"SAL" NUMBER, 
"COMM" NUMBER 
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
TABLESPACE "T_SPACE1" 
Probably after deploying this table definition we receive the error message ORA-00959 (Table space T_SPACE1 does not exist).  Of course - no problem to create T_SPACE1 table space manually but what to do if we have thousands of tables with tens or even hundreds different table spaces ?
Solution
I found the following awk script useful. It simply extracts all table spaces names from table schema export and creates a script for creating all table spaces used. Datafile name is the same as table space name.
BEGIN {  }

/TABLESPACE/ {
   for (i=1; i < NF; i++) {
     if ($i == "TABLESPACE") {
       TSPACENAME=toupper($(i+1))
       gsub(",.*","",TSPACENAME);
       namespaces[TSPACENAME] = 1;
     }   
   }
}
END { 
  for (i in namespaces) {
    TSPACENAME=i;
    TFILENAME=tolower(TSPACENAME); gsub("\"","",TFILENAME); 
    TFILENAME = TFILENAME ".dbf";

    print "CREATE TABLESPACE " TSPACENAME
    print "DATAFILE '/home/oracle/app/oracle/oradata/test/" TFILENAME "'"
    print "size 1m autoextend on next 1m maxsize 2048m EXTENT MANAGEMENT LOCAL;"
    print
  }
}
You can catch the standard output and modify it before deployment - for instance remove creating TEMP and USERS table space. An example of usage:
awk -f namespace.awk < MY_USERS_TABLES.SQL >create_tablespace.sql
Additional remarks

  1. This script assumes that TABLESPACE clause and the following table space name is in the same line. It does not hold true every time. It is possible to extend this script but it requires more coding.
  2. The predicat i<NF (not i<=NF) is on purpose. Just skips if TABLESPACE clause is the last in the line. Probably it makes sense to add a warning message on that.
  3. The gsub(",.*","",TSPACENAME); regards the case when table space name is followed by , (coma). For instance  TABLESPACE "T_SPACE", PARTITION .. Unfortunately awk does not allow to specify more than one field deliminator, so coma is included as a part of table space name and should be removed after that.

poniedziałek, 28 listopada 2011

Logging and Eclipse plugin

I added logging to my first Eclipse plugin. It seems to have been much simpler than I previously thought. Just simple class like that is enough:

package com.db2.sb.scriptlauncher;

import org.eclipse.core.runtime.ILog;
import org.eclipse.core.runtime.Status;

import scriptlaucherdb2.Activator;

public class DB2LogUtil {

        private static ILog getLog() {
                ILog log = Activator.getDefault().getLog();
                return log;
        }

        private static void log(String msg, int id, Exception e) {
                getLog().log(new Status(Status.INFO, Activator.PLUGIN_ID, id, msg, null));
        }

        static void log(String msg) {
                log(msg, Status.OK, null);
        }

        static void log(String msg, Exception e) {
                log(msg, Status.ERROR, e);
        }

        static void logError(String msg) {
                log(msg, Status.ERROR, null);
        }

        static void launchererrorLog(Exception e) {
                log(Messages.DB2LauncherError, e);
        }

}