dba33j: #i114566# +getNormalizedStructuredFilter

3 files changed, 101 insertions, 35 deletions

diff --git a/wizards/com/sun/star/wizards/db/DatabaseObjectWizard.java b/wizards/com/sun/star/wizards/db/DatabaseObjectWizard.java
index 5c65d73794f0..e03323c6ebbc 100644
--- a/wizards/com/sun/star/wizards/db/DatabaseObjectWizard.java
+++ b/wizards/com/sun/star/wizards/db/DatabaseObjectWizard.java
@@ -57,7 +57,8 @@ public abstract class DatabaseObjectWizard extends WizardDialog
     {
         try
         {
-            m_docUI.loadComponent( i_type, i_name, i_forEditing );
+            if ( m_docUI != null )
+                m_docUI.loadComponent( i_type, i_name, i_forEditing );
         }
         catch ( IllegalArgumentException ex )
         {
diff --git a/wizards/com/sun/star/wizards/db/SQLQueryComposer.java b/wizards/com/sun/star/wizards/db/SQLQueryComposer.java
index 0a52f6a3a7a9..235da2d7c04a 100644
--- a/wizards/com/sun/star/wizards/db/SQLQueryComposer.java
+++ b/wizards/com/sun/star/wizards/db/SQLQueryComposer.java
@@ -47,6 +47,7 @@ import com.sun.star.uno.UnoRuntime;
 import com.sun.star.sdbc.SQLException;
 import com.sun.star.lang.XInitialization;
 import com.sun.star.awt.XWindow;
+import com.sun.star.sdb.SQLFilterOperator;
 
 import com.sun.star.wizards.common.*;
 
@@ -60,7 +61,7 @@ public class SQLQueryComposer
     // String m_sFromClause;
     public XSingleSelectQueryAnalyzer m_xQueryAnalyzer;
     Vector composedCommandNames = new Vector(1);
-    private XSingleSelectQueryComposer m_xQueryComposer;
+    private XSingleSelectQueryComposer m_queryComposer;
     XMultiServiceFactory xMSF;
     boolean bincludeGrouping = true;
 
@@ -72,7 +73,7 @@ public class SQLQueryComposer
             xMSF = (XMultiServiceFactory) UnoRuntime.queryInterface(XMultiServiceFactory.class, CurDBMetaData.DBConnection);
             final Object oQueryComposer = xMSF.createInstance("com.sun.star.sdb.SingleSelectQueryComposer");
             m_xQueryAnalyzer = (XSingleSelectQueryAnalyzer) UnoRuntime.queryInterface(XSingleSelectQueryAnalyzer.class, oQueryComposer);
-            m_xQueryComposer = (XSingleSelectQueryComposer) UnoRuntime.queryInterface(XSingleSelectQueryComposer.class, m_xQueryAnalyzer);
+            m_queryComposer = (XSingleSelectQueryComposer) UnoRuntime.queryInterface(XSingleSelectQueryComposer.class, m_xQueryAnalyzer);
             XSQLQueryComposerFactory xSQLComposerFactory;
             xSQLComposerFactory = (XSQLQueryComposerFactory) UnoRuntime.queryInterface(XSQLQueryComposerFactory.class, CurDBMetaData.DBConnection);
         // /* XSQLQueryComposer */ xSQLQueryComposer = xSQLComposerFactory.createQueryComposer();
@@ -160,7 +161,7 @@ public class SQLQueryComposer
         {
             for (int i = 0; i < CurDBMetaData.getFilterConditions().length; i++)
             {
-                m_xQueryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions());
+                m_queryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions());
             }
         }
         catch (Exception exception)
@@ -172,7 +173,7 @@ public class SQLQueryComposer
     public void prependSortingCriteria() throws SQLException
     {
         XIndexAccess xColumnIndexAccess = m_xQueryAnalyzer.getOrderColumns();
-        m_xQueryComposer.setOrder("");
+        m_queryComposer.setOrder("");
         for (int i = 0; i < CurDBMetaData.getSortFieldNames().length; i++)
         {
             appendSortingCriterion(i, false);
@@ -186,7 +187,7 @@ public class SQLQueryComposer
                 if (JavaTools.FieldInTable(CurDBMetaData.getSortFieldNames(), sName) == -1)
                 {
                     boolean bascend = AnyConverter.toBoolean(xColumnPropertySet.getPropertyValue("IsAscending"));
-                    m_xQueryComposer.appendOrderByColumn(xColumnPropertySet, bascend);
+                    m_queryComposer.appendOrderByColumn(xColumnPropertySet, bascend);
                 }
             }
             catch (Exception e)
@@ -203,13 +204,13 @@ public class SQLQueryComposer
 
         String sSort = CurDBMetaData.getSortFieldNames()[_SortIndex][1];
         boolean bascend = (sSort.equals("ASC"));
-        m_xQueryComposer.appendOrderByColumn(xColumn, bascend);
+        m_queryComposer.appendOrderByColumn(xColumn, bascend);
     }
 
     public void appendSortingcriteria(boolean _baddAliasFieldNames) throws SQLException
     {
         String sOrder = "";
-        m_xQueryComposer.setOrder("");
+        m_queryComposer.setOrder("");
         for (int i = 0; i < CurDBMetaData.getSortFieldNames().length; i++)
         {
             String sSortValue = CurDBMetaData.getSortFieldNames()[i][0];
@@ -223,7 +224,7 @@ public class SQLQueryComposer
                 }
                 sOrder += CurDBMetaData.AggregateFieldNames[iAggregate][1] + "(" + CurDBMetaData.AggregateFieldNames[iAggregate][0] + ")";
                 sOrder += " " + CurDBMetaData.getSortFieldNames()[i][1];
-                m_xQueryComposer.setOrder(sOrder);
+                m_queryComposer.setOrder(sOrder);
             }
             else
             {
@@ -232,7 +233,7 @@ public class SQLQueryComposer
             sOrder = m_xQueryAnalyzer.getOrder();
         }
         // just for debug!
-        sOrder = m_xQueryComposer.getOrder();
+        sOrder = m_queryComposer.getOrder();
         int dummy = 0;
     }
 
@@ -241,7 +242,7 @@ public class SQLQueryComposer
         for (int i = 0; i < CurDBMetaData.GroupFieldNames.length; i++)
         {
             XPropertySet xColumn = CurDBMetaData.getColumnObjectByFieldName(CurDBMetaData.GroupFieldNames[i], _baddAliasFieldNames);
-            m_xQueryComposer.appendGroupByColumn(xColumn);
+            m_queryComposer.appendGroupByColumn(xColumn);
         }
         String s = m_xQueryAnalyzer.getQuery();
     }
@@ -309,7 +310,7 @@ public class SQLQueryComposer
                     if (CurDBMetaData.getFilterConditions().length > 0)
                     {
                         CurDBMetaData.setFilterConditions(replaceConditionsByAlias(CurDBMetaData.getFilterConditions()));
-                        m_xQueryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions());
+                        m_queryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions());
                     }
                 }
             }
@@ -319,7 +320,7 @@ public class SQLQueryComposer
                 appendGroupByColumns(_baddAliasFieldNames);
                 if (CurDBMetaData.GroupByFilterConditions.length > 0)
                 {
-                    m_xQueryComposer.setStructuredHavingClause(CurDBMetaData.GroupByFilterConditions);
+                    m_queryComposer.setStructuredHavingClause(CurDBMetaData.GroupByFilterConditions);
                 }
             }
             appendSortingcriteria(_baddAliasFieldNames);
@@ -426,8 +427,63 @@ public class SQLQueryComposer
             typeexception.printStackTrace(System.out);
         }
     }
+
+    /**
+     * retrieves a normalized structured filter
+     *
+     * <p>XSingleSelectQueryComposer.getStructuredFilter has a strange habit of returning the predicate (equal, not equal, etc)
+     * effectively twice: Once as SQLFilterOperator, and once in the value. That is, if you have a term "column <> 3", then
+     * you'll get an SQLFilterOperator.NOT_EQUAL (which is fine), <strong>and</strong> the textual value of the condition
+     * will read "<> 3". The latter is strange enough, but even more strange is that this behavior is not even consistent:
+     * for SQLFilterOperator.EQUAL, the "=" sign is not include in the textual value.</p>
+     *
+     * <p>To abstract from this weirdness, use this function here, which strips the unwanted tokens from the textual value
+     * representation.</p>
+     */
+    public PropertyValue[][] getNormalizedStructuredFilter()
+    {
+        final PropertyValue[][] structuredFilter = m_queryComposer.getStructuredFilter();
+        for ( int i=0; i<structuredFilter.length; ++i )
+        {
+            for ( int j=0; j<structuredFilter[i].length; ++j )
+            {
+                if ( !( structuredFilter[i][j].Value instanceof String ) )
+                    continue;
+                final StringBuffer textualValue = new StringBuffer( (String)structuredFilter[i][j].Value );
+                switch ( structuredFilter[i][j].Handle )
+                {
+                case SQLFilterOperator.EQUAL:
+                    break;
+                case SQLFilterOperator.NOT_EQUAL:
+                case SQLFilterOperator.LESS_EQUAL:
+                case SQLFilterOperator.GREATER_EQUAL:
+                    textualValue.delete( 0, 2 );
+                    break;
+                case SQLFilterOperator.LESS:
+                case SQLFilterOperator.GREATER:
+                    textualValue.delete( 0, 1 );
+                    break;
+                case SQLFilterOperator.NOT_LIKE:
+                    textualValue.delete( 0, 8 );
+                    break;
+                case SQLFilterOperator.LIKE:
+                    textualValue.delete( 0, 4 );
+                    break;
+                case SQLFilterOperator.SQLNULL:
+                    textualValue.delete( 0, 7 );
+                    break;
+                case SQLFilterOperator.NOT_SQLNULL:
+                    textualValue.delete( 0, 11 );
+                    break;
+                }
+                structuredFilter[i][j].Value = textualValue.toString().trim();
+            }
+        }
+        return structuredFilter;
+    }
+
     public XSingleSelectQueryComposer getQueryComposer()
     {
-        return m_xQueryComposer;
+        return m_queryComposer;
     }
 }
diff --git a/wizards/com/sun/star/wizards/ui/FilterComponent.java b/wizards/com/sun/star/wizards/ui/FilterComponent.java
index 4720b40ca55f..50b855ab3693 100644
--- a/wizards/com/sun/star/wizards/ui/FilterComponent.java
+++ b/wizards/com/sun/star/wizards/ui/FilterComponent.java
@@ -27,7 +27,6 @@
 package com.sun.star.wizards.ui;
 
 // import java.util.Vector;
-import com.sun.star.awt.ItemEvent;
 import com.sun.star.awt.TextEvent;
 import com.sun.star.awt.VclWindowPeerAttribute;
 import com.sun.star.awt.XControl;
@@ -40,6 +39,7 @@ import com.sun.star.beans.XPropertySet;
 import com.sun.star.lang.EventObject;
 import com.sun.star.lang.IllegalArgumentException;
 import com.sun.star.lang.XMultiServiceFactory;
+import com.sun.star.sdb.SQLFilterOperator;
 import com.sun.star.sdbc.DataType;
 import com.sun.star.uno.Any;
 import com.sun.star.uno.AnyConverter;
@@ -53,6 +53,7 @@ import com.sun.star.wizards.common.Helper;
 import com.sun.star.wizards.common.JavaTools;
 import com.sun.star.wizards.db.FieldColumn;
 import com.sun.star.wizards.db.QueryMetaData;
+import com.sun.star.wizards.db.SQLQueryComposer;
 import java.util.logging.Level;
 import java.util.logging.Logger;
 
@@ -119,7 +120,6 @@ public class FilterComponent
 
     class ItemListenerImpl implements com.sun.star.awt.XItemListener
     {
-
         public void itemStateChanged(com.sun.star.awt.ItemEvent EventObject)
         {
             int iKey = CurUnoDialog.getControlKey(EventObject.Source, CurUnoDialog.ControlList);
@@ -237,6 +237,7 @@ public class FilterComponent
         int nFilterCount = getFilterCount();
         if (nFilterCount > 0)
         {
+            final SQLQueryComposer composer = oQueryMetaData.getSQLQueryComposer();
             try
             {
                 final String serviceName = "com.sun.star.beans.PropertyBag";
@@ -247,29 +248,29 @@ public class FilterComponent
                 column.addProperty("Value", (short)( PropertyAttribute.MAYBEVOID | PropertyAttribute.REMOVABLE ), null );
                 final XPropertySet columnSet = UnoRuntime.queryInterface(XPropertySet.class, column);
 
-                if ( oQueryMetaData.getSQLQueryComposer().getQuery().length() == 0)
+                if ( composer.getQuery().length() == 0)
                 {
-                    final String fromClause = oQueryMetaData.getSQLQueryComposer().getFromClause();
+                    final String fromClause = composer.getFromClause();
                     StringBuilder sql = new StringBuilder();
-                    sql.append(oQueryMetaData.getSQLQueryComposer().getSelectClause(true));
+                    sql.append(composer.getSelectClause(true));
                     sql.append(' ');
                     sql.append(fromClause);
-                    oQueryMetaData.getSQLQueryComposer().getQueryComposer().setElementaryQuery(sql.toString());
+                    composer.getQueryComposer().setElementaryQuery(sql.toString());
                 }
-                oQueryMetaData.getSQLQueryComposer().getQueryComposer().setStructuredFilter( new PropertyValue[][] {} );
+                composer.getQueryComposer().setStructuredFilter( new PropertyValue[][] {} );
                 for (int i = 0; i < RowCount; i++)
                 {
-                    ControlRow CurControlRow = oControlRows[i];
-                    if (CurControlRow.isEnabled())
+                    ControlRow currentControlRow = oControlRows[i];
+                    if (currentControlRow.isEnabled())
                     {
-                        if (CurControlRow.isConditionComplete())
+                        if (currentControlRow.isConditionComplete())
                         {
-                            String sFieldName = CurControlRow.getSelectedFieldName();
-                            int nOperator = (int) CurControlRow.getSelectedOperator();
+                            String sFieldName = currentControlRow.getSelectedFieldName();
+                            int nOperator = (int) currentControlRow.getSelectedOperator();
                             FieldColumn aFieldColumn = oQueryMetaData.getFieldColumnByDisplayName(sFieldName);
                             columnSet.setPropertyValue("Name", aFieldColumn.getFieldName());
                             columnSet.setPropertyValue("Type", aFieldColumn.getXColumnPropertySet().getPropertyValue("Type"));
-                            Object value = CurControlRow.getValue();
+                            Object value = currentControlRow.getValue();
                             switch(aFieldColumn.getFieldType())
                             {
                                 case DataType.TIMESTAMP:
@@ -278,14 +279,23 @@ public class FilterComponent
                                     break;
                             }
                             column.removeProperty( "Value" );
-                            column.addProperty("Value", (short)( PropertyAttribute.MAYBEVOID | PropertyAttribute.REMOVABLE ), value );
+                            final short operator = currentControlRow.getSelectedOperator();
+                            if  (   ( operator == SQLFilterOperator.SQLNULL )
+                                ||  ( operator == SQLFilterOperator.NOT_SQLNULL )
+                                ||  AnyConverter.isVoid( value )
+                                )
+                            {
+                                column.addProperty("Value", (short)( PropertyAttribute.MAYBEVOID | PropertyAttribute.REMOVABLE ), new String() );
+                                value = new Any( new Type( TypeClass.VOID ), null );
+                            }
+                            else
+                                column.addProperty("Value", (short)( PropertyAttribute.MAYBEVOID | PropertyAttribute.REMOVABLE ), value );
                             columnSet.setPropertyValue("Value", value);
-                            oQueryMetaData.getSQLQueryComposer().getQueryComposer().appendFilterByColumn(columnSet, getfilterstate() == this.SOI_MATCHALL,nOperator);
+                            composer.getQueryComposer().appendFilterByColumn(columnSet, getfilterstate() == this.SOI_MATCHALL,nOperator);
                         }
                     }
                 }
-                final String test = oQueryMetaData.getSQLQueryComposer().getQueryComposer().getQuery();
-                filterconditions = oQueryMetaData.getSQLQueryComposer().getQueryComposer().getStructuredFilter();
+                filterconditions = composer.getNormalizedStructuredFilter();
                 int[] iduplicate = JavaTools.getDuplicateFieldIndex(filterconditions);
                 if (iduplicate[0] != -1)
                 {
@@ -520,7 +530,6 @@ public class FilterComponent
     // -------------------------------------------------------------------------
     final class ControlRow
     {
-
         private final static int SOLSTFIELDNAME = 3;
         private final static int SOLSTOPERATOR = 4;
         private final static int SOTXTVALUE = 5;
@@ -936,9 +945,9 @@ public class FilterComponent
 
         protected String getDateTimeString(boolean bgetDate)
         {
-                double dblValue = ((Double) getValue()).doubleValue();
-                NumberFormatter oNumberFormatter = oQueryMetaData.getNumberFormatter();
-                return oNumberFormatter.convertNumberToString(iDateTimeFormat, dblValue);
+            double dblValue = ((Double) getValue()).doubleValue();
+            NumberFormatter oNumberFormatter = oQueryMetaData.getNumberFormatter();
+            return oNumberFormatter.convertNumberToString(iDateTimeFormat, dblValue);
         }
     }
 }