[PATCH] Change in core[libreoffice-4-0]: fix handling of subqueries in query design

Hi,

I have submitted a patch for review:

    https://gerrit.libreoffice.org/1827

To pull it, you can do:

    git pull ssh://gerrit.libreoffice.org:29418/core refs/changes/27/1827/1

fix handling of subqueries in query design

 - don't remove parentheses around subqueries
   (without the parentheses, it is not valid SQL)
   * when saving a Field (name value) typed by the user interactively
   * when parsing SQL and constructing the initial Query Design view

 - automatically add the necessary parentheses
   when a SELECT statement is entered as column name

Also:

In code saving a Field (name value) typed by the user interactively,
factorise some common code

Assorted minor fixes (typos in comments, etc)

Change-Id: I3843258323c903cba23238b0730ec4eb5875f792
---
M dbaccess/source/ui/querydesign/QueryDesignView.cxx
M dbaccess/source/ui/querydesign/SelectionBrowseBox.cxx
M dbaccess/source/ui/querydesign/SelectionBrowseBox.hxx
3 files changed, 50 insertions(+), 35 deletions(-)



diff --git a/dbaccess/source/ui/querydesign/QueryDesignView.cxx 
b/dbaccess/source/ui/querydesign/QueryDesignView.cxx
index 3c386db..8264152 100644
--- a/dbaccess/source/ui/querydesign/QueryDesignView.cxx
+++ b/dbaccess/source/ui/querydesign/QueryDesignView.cxx
@@ -2198,7 +2198,8 @@
                     pColumnRef = pColumnRef->getChild(0);
                     OTableFieldDescRef aInfo = new OTableFieldDesc();
 
-                    if (    pColumnRef->count() == 3 &&
+                    if (    pColumnRef->getKnownRuleID() != OSQLParseNode::subquery &&
+                            pColumnRef->count() == 3 &&
                             SQL_ISPUNCTUATION(pColumnRef->getChild(0),"(") &&
                             SQL_ISPUNCTUATION(pColumnRef->getChild(2),")")
                         )
diff --git a/dbaccess/source/ui/querydesign/SelectionBrowseBox.cxx 
b/dbaccess/source/ui/querydesign/SelectionBrowseBox.cxx
index 5544549..e85cb29 100644
--- a/dbaccess/source/ui/querydesign/SelectionBrowseBox.cxx
+++ b/dbaccess/source/ui/querydesign/SelectionBrowseBox.cxx
@@ -649,7 +649,7 @@
     return bError;
 }
 // -----------------------------------------------------------------------------
-sal_Bool OSelectionBrowseBox::saveField(const String& _sFieldName,OTableFieldDescRef& 
_pEntry,sal_Bool& _bListAction)
+sal_Bool OSelectionBrowseBox::saveField(String& _sFieldName ,OTableFieldDescRef& _pEntry, 
sal_Bool& _bListAction)
 {
     sal_Bool bError = sal_False;
 
@@ -680,50 +680,63 @@
     // we have to look which entries  we should quote
 
     const ::rtl::OUString sFieldAlias = _pEntry->GetFieldAlias();
-    size_t nPass = 4;
     ::connectivity::OSQLParser& rParser( rController.getParser() );
-    OSQLParseNode* pParseNode = NULL;
-    // 4 passes in trying to interprete the field name
-    // - don't quote the field name, parse internationally
-    // - don't quote the field name, parse en-US
-    // - quote the field name, parse internationally
-    // - quote the field name, parse en-US
-    do
     {
-        bool bQuote = ( nPass <= 2 );
-        bool bInternational = ( nPass % 2 ) == 0;
+        // automatically add parentheses around subqueries
+        OSQLParseNode *pParseNode = NULL;
+        OUString devnull;
+        pParseNode = rParser.parseTree( devnull, _sFieldName, true );
+        if (pParseNode == NULL)
+            pParseNode = rParser.parseTree( devnull, _sFieldName, false );
+        if (pParseNode != NULL && SQL_ISRULE(pParseNode, select_statement))
+            _sFieldName = ::rtl::OUString("(") + _sFieldName + ")";
+    }
 
-        ::rtl::OUString sSql;
-        if ( bQuote )
-            sSql += ::dbtools::quoteName( xMetaData->getIdentifierQuoteString(), _sFieldName );
-        else
-            sSql += _sFieldName;
+    OSQLParseNode* pParseNode = NULL;
+    {
+        // 4 passes in trying to interprete the field name
+        // - don't quote the field name, parse internationally
+        // - don't quote the field name, parse en-US
+        // - quote the field name, parse internationally
+        // - quote the field name, parse en-US
+        size_t nPass = 4;
+        ::rtl::OUString sQuotedFullFieldName(::dbtools::quoteName( 
xMetaData->getIdentifierQuoteString(), _sFieldName ));
+        ::rtl::OUString sFullFieldName(_sFieldName);
 
         if  ( _pEntry->isAggreateFunction() )
         {
             OSL_ENSURE(!_pEntry->GetFunction().isEmpty(),"Functionname darf hier nicht leer sein! 
;-(");
-            ::rtl::OUStringBuffer aTmpStr2( _pEntry->GetFunction());
-            aTmpStr2.appendAscii("(");
-            aTmpStr2.append(sSql);
-            aTmpStr2.appendAscii(")");
-            sSql = aTmpStr2.makeStringAndClear();
+            sQuotedFullFieldName = _pEntry->GetFunction() + "(" + sQuotedFullFieldName + ")";
+            sFullFieldName = _pEntry->GetFunction() + "(" + sFullFieldName + ")";
         }
 
-        sSql = ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM("SELECT ")) + sSql;
-        if ( !sFieldAlias.isEmpty() )
-        { // always quote the alias name there canbe no function in it
-            sSql += ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(" "));
-            sSql += ::dbtools::quoteName( xMetaData->getIdentifierQuoteString(), sFieldAlias );
-        }
-        sSql += ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(" FROM x"));
+        do
+        {
+            bool bQuote = ( nPass <= 2 );
+            bool bInternational = ( nPass % 2 ) == 0;
 
-        pParseNode = rParser.parseTree( sErrorMsg, sSql, bInternational );
+            ::rtl::OUString sSql;
+            if ( bQuote )
+                sSql += sQuotedFullFieldName;
+            else
+                sSql += sFullFieldName;
+
+            sSql = ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM("SELECT ")) + sSql;
+            if ( !sFieldAlias.isEmpty() )
+            { // always quote the alias name: there cannot be a function in it
+                sSql += ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(" "));
+                sSql += ::dbtools::quoteName( xMetaData->getIdentifierQuoteString(), sFieldAlias );
+            }
+            sSql += ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(" FROM x"));
+
+            pParseNode = rParser.parseTree( sErrorMsg, sSql, bInternational );
+        }
+        while ( ( pParseNode == NULL ) && ( --nPass > 0 ) );
     }
-    while ( ( pParseNode == NULL ) && ( --nPass > 0 ) );
 
     if ( pParseNode == NULL )
     {
-        // something different which we have to check (may be a select statement)
+        // something different which we have to check
         String sErrorMessage( ModuleRes( STR_QRY_COLUMN_NOT_FOUND ) );
         sErrorMessage.SearchAndReplaceAscii("$name$",_sFieldName);
         OSQLWarningBox( this, sErrorMessage ).Execute();
@@ -737,8 +750,8 @@
     {
         _pEntry->SetField(_sFieldName);
         clearEntryFunctionField(_sFieldName,_pEntry,_bListAction,_pEntry->GetColumnId());
-    } // travel through the select column parse node
-    else
+    }
+    else // travel through the select column parse node
     {
         ::comphelper::UStringMixEqual bCase(xMetaData->supportsMixedCaseQuotedIdentifiers());
 
@@ -777,6 +790,7 @@
 
             ::connectivity::OSQLParseNode* pColumnRef = pChild->getChild(0);
             if (
+                    pColumnRef->getKnownRuleID() != OSQLParseNode::subquery &&
                     pColumnRef->count() == 3 &&
                     SQL_ISPUNCTUATION(pColumnRef->getChild(0),"(") &&
                     SQL_ISPUNCTUATION(pColumnRef->getChild(2),")")
diff --git a/dbaccess/source/ui/querydesign/SelectionBrowseBox.hxx 
b/dbaccess/source/ui/querydesign/SelectionBrowseBox.hxx
index b3b2c98..55bf270 100644
--- a/dbaccess/source/ui/querydesign/SelectionBrowseBox.hxx
+++ b/dbaccess/source/ui/querydesign/SelectionBrowseBox.hxx
@@ -246,7 +246,7 @@
             @return
                 <TRUE/> if an error occurred otherwise <FALSE/>
         */
-        sal_Bool        saveField(const String& _sFieldName,OTableFieldDescRef& _pEntry,sal_Bool& 
_bListAction);
+        sal_Bool        saveField(String& _sFieldName, OTableFieldDescRef& _pEntry, sal_Bool& 
_bListAction);
 
         /** sets the table window at the _pEntry
             @param  _pEntry

-- 
To view, visit https://gerrit.libreoffice.org/1827
To unsubscribe, visit https://gerrit.libreoffice.org/settings

Gerrit-MessageType: newchange
Gerrit-Change-Id: I3843258323c903cba23238b0730ec4eb5875f792
Gerrit-PatchSet: 1
Gerrit-Project: core
Gerrit-Branch: libreoffice-4-0
Gerrit-Owner: Lionel Elie Mamane <lionel@mamane.lu>