dbaccess/source/ui/dlg/directsql.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  *
   9  * This file incorporates work covered by the following license notice:
  10  *
  11  *   Licensed to the Apache Software Foundation (ASF) under one or more
  12  *   contributor license agreements. See the NOTICE file distributed
  13  *   with this work for additional information regarding copyright
  14  *   ownership. The ASF licenses this file to you under the Apache
  15  *   License, Version 2.0 (the "License"); you may not use this file
  16  *   except in compliance with the License. You may obtain a copy of
  17  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  18  */
  19
  20 #include <core_resource.hxx>
  21 #include <directsql.hxx>
  22 #include <sqledit.hxx>
  23 #include <strings.hxx>
  24 #include <strings.hrc>
  25 #include <comphelper/types.hxx>
  26 #include <osl/mutex.hxx>
  27 #include <rtl/ustrbuf.hxx>
  28 #include <rtl/ustring.hxx>
  29 #include <comphelper/diagnose_ex.hxx>
  30 #include <vcl/svapp.hxx>
  31 #include <vcl/weld.hxx>
  32 #include <com/sun/star/sdbc/SQLException.hpp>
  33 #include <com/sun/star/sdbc/XRow.hpp>
  34 #include <com/sun/star/beans/XPropertySet.hpp>
  35 #include <com/sun/star/sdbc/DataType.hpp>
  36 #include <com/sun/star/sdbc/XMultipleResults.hpp>
  37 #include <com/sun/star/sdbc/XResultSetMetaData.hpp>
  38 #include <com/sun/star/sdbc/XResultSetMetaDataSupplier.hpp>
  39
  40 // tdf#140298 - remember user settings within the current session
  41 // memp is filled in dtor and restored after initialization
  42 namespace
  43 {
  44     struct memParam {
  45         std::vector<OUString> SQLHistory;
  46         bool DirectSQL;
  47         bool ShowOutput;
  48     };
  49     memParam memp;
  50 }
  51
  52 namespace dbaui
  53 {
  54
  55     using namespace ::com::sun::star::uno;
  56     using namespace ::com::sun::star::sdbc;
  57     using namespace ::com::sun::star::lang;
  58
  59     constexpr sal_Int32 g_nHistoryLimit = 20;
  60
  61     // DirectSQLDialog
  62     DirectSQLDialog::DirectSQLDialog(weld::Window* _pParent, const Reference< XConnection >& _rxConn)
  63         : GenericDialogController(_pParent, u"dbaccess/ui/directsqldialog.ui"_ustr, u"DirectSQLDialog"_ustr)
  64         , m_xExecute(m_xBuilder->weld_button(u"execute"_ustr))
  65         , m_xSQLHistory(m_xBuilder->weld_combo_box(u"sqlhistory"_ustr))
  66         , m_xStatus(m_xBuilder->weld_text_view(u"status"_ustr))
  67         , m_xDirectSQL(m_xBuilder->weld_check_button(u"directsql"_ustr))
  68         , m_xShowOutput(m_xBuilder->weld_check_button(u"showoutput"_ustr))
  69         , m_xOutput(m_xBuilder->weld_text_view(u"output"_ustr))
  70         , m_xClose(m_xBuilder->weld_button(u"close"_ustr))
  71         , m_xSQL(new SQLEditView(m_xBuilder->weld_scrolled_window(u"scrolledwindow"_ustr, true)))
  72         , m_xSQLEd(new weld::CustomWeld(*m_xBuilder, u"sql"_ustr, *m_xSQL))
  73         , m_nStatusCount(1)
  74         , m_xConnection(_rxConn)
  75         , m_pClosingEvent(nullptr)
  76     {
  77         int nWidth = m_xStatus->get_approximate_digit_width() * 60;
  78         int nHeight = m_xStatus->get_height_rows(7);
  79
  80         m_xSQLEd->set_size_request(nWidth, nHeight);
  81         m_xStatus->set_size_request(-1, nHeight);
  82         m_xOutput->set_size_request(-1, nHeight);
  83
  84         m_xSQL->GrabFocus();
  85
  86         m_xExecute->connect_clicked(LINK(this, DirectSQLDialog, OnExecute));
  87         m_xClose->connect_clicked(LINK(this, DirectSQLDialog, OnCloseClick));
  88         m_xSQLHistory->connect_changed(LINK(this, DirectSQLDialog, OnListEntrySelected));
  89
  90         m_xDirectSQL->set_active(true);
  91         m_xShowOutput->set_active(true);
  92
  93         for (size_t i = 0; i < memp.SQLHistory.size(); i++)
  94         {
  95             implAddToStatementHistory(memp.SQLHistory[i], true);
  96             m_xDirectSQL->set_active(memp.DirectSQL);
  97             m_xShowOutput->set_active(memp.ShowOutput);
  98         }
  99
 100         // add a dispose listener to the connection
 101         Reference< XComponent > xConnComp(m_xConnection, UNO_QUERY);
 102         OSL_ENSURE(xConnComp.is(), "DirectSQLDialog::DirectSQLDialog: invalid connection!");
 103         if (xConnComp.is())
 104             startComponentListening(xConnComp);
 105
 106         m_xSQL->SetModifyHdl(LINK(this, DirectSQLDialog, OnStatementModified));
 107         OnStatementModified(nullptr);
 108     }
 109
 110     DirectSQLDialog::~DirectSQLDialog()
 111     {
 112         memp.DirectSQL = m_xDirectSQL->get_active();
 113         memp.ShowOutput = m_xShowOutput->get_active();
 114
 115         ::osl::MutexGuard aGuard(m_aMutex);
 116         if (m_pClosingEvent)
 117             Application::RemoveUserEvent(m_pClosingEvent);
 118         stopAllComponentListening();
 119     }
 120
 121     void DirectSQLDialog::_disposing( const EventObject& _rSource )
 122     {
 123         SolarMutexGuard aSolarGuard;
 124         ::osl::MutexGuard aGuard(m_aMutex);
 125
 126         assert(!m_pClosingEvent);
 127
 128         OSL_ENSURE(Reference< XConnection >(_rSource.Source, UNO_QUERY).get() == m_xConnection.get(),
 129             "DirectSQLDialog::_disposing: where does this come from?");
 130
 131         {
 132             OUString sMessage(DBA_RES(STR_DIRECTSQL_CONNECTIONLOST));
 133             std::unique_ptr<weld::MessageDialog> xError(Application::CreateMessageDialog(m_xDialog.get(),
 134                                                         VclMessageType::Warning, VclButtonsType::Ok,
 135                                                         sMessage));
 136             xError->run();
 137         }
 138
 139         m_pClosingEvent = Application::PostUserEvent(LINK(this, DirectSQLDialog, OnClose));
 140     }
 141
 142     sal_Int32 DirectSQLDialog::getHistorySize() const
 143     {
 144         #ifdef DBG_UTIL
 145         {
 146             const char* pError = impl_CheckInvariants();
 147             if (pError)
 148                 SAL_WARN("dbaccess.ui", "DirectSQLDialog::getHistorySize: " << pError);
 149         }
 150         #endif
 151         return m_aStatementHistory.size();
 152     }
 153
 154     void DirectSQLDialog::implEnsureHistoryLimit()
 155     {
 156         #ifdef DBG_UTIL
 157         {
 158             const char* pError = impl_CheckInvariants();
 159             if (pError)
 160                 SAL_WARN("dbaccess.ui", "DirectSQLDialog::implEnsureHistoryLimit: " << pError);
 161         }
 162         #endif
 163
 164         if (getHistorySize() <= g_nHistoryLimit)
 165             // nothing to do
 166             return;
 167
 168         sal_Int32 nRemoveEntries = getHistorySize() - g_nHistoryLimit;
 169         while (nRemoveEntries--)
 170         {
 171             m_aStatementHistory.pop_front();
 172             m_aNormalizedHistory.pop_front();
 173             m_xSQLHistory->remove(0);
 174         }
 175     }
 176
 177     void DirectSQLDialog::implAddToStatementHistory(const OUString& _rStatement, const bool bFromMemory)
 178     {
 179         #ifdef DBG_UTIL
 180         {
 181             const char* pError = impl_CheckInvariants();
 182             if (pError)
 183                 SAL_WARN("dbaccess.ui", "DirectSQLDialog::implAddToStatementHistor: " << pError);
 184         }
 185         #endif
 186
 187         // add the statement to the history
 188         m_aStatementHistory.push_back(_rStatement);
 189         if (!bFromMemory)
 190             memp.SQLHistory.push_back(_rStatement);
 191
 192         // normalize the statement, and remember the normalized form, too
 193         OUString sNormalized = _rStatement.replaceAll("\n", " ");
 194         m_aNormalizedHistory.push_back(sNormalized);
 195
 196         // add the normalized version to the list box
 197         m_xSQLHistory->append_text(sNormalized);
 198
 199         // ensure that we don't exceed the history limit
 200         implEnsureHistoryLimit();
 201     }
 202
 203 #ifdef DBG_UTIL
 204     const char* DirectSQLDialog::impl_CheckInvariants() const
 205     {
 206         if (m_aStatementHistory.size() != m_aNormalizedHistory.size())
 207             return "statement history is inconsistent!";
 208
 209         if (!m_xSQLHistory)
 210             return "invalid listbox!";
 211
 212         if (m_aStatementHistory.size() != static_cast<size_t>(m_xSQLHistory->get_count()))
 213             return "invalid listbox entry count!";
 214
 215         if (!m_xConnection.is())
 216             return "have no connection!";
 217
 218         return nullptr;
 219     }
 220 #endif
 221
 222     void DirectSQLDialog::implExecuteStatement(const OUString& _rStatement)
 223     {
 224         #ifdef DBG_UTIL
 225         {
 226             const char* pError = impl_CheckInvariants();
 227             if (pError)
 228                 SAL_WARN("dbaccess.ui", "DirectSQLDialog::implExecuteStatement: " << pError);
 229         }
 230         #endif
 231
 232         ::osl::MutexGuard aGuard(m_aMutex);
 233
 234         OUString sStatus;
 235
 236         // clear the output box
 237         m_xOutput->set_text(OUString());
 238         try
 239         {
 240             // create a statement
 241             Reference< XStatement > xStatement = m_xConnection->createStatement();
 242
 243             if (m_xDirectSQL->get_active())
 244             {
 245                 Reference< css::beans::XPropertySet > xStatementProps(xStatement, UNO_QUERY_THROW);
 246                 try
 247                 {
 248                     xStatementProps->setPropertyValue(PROPERTY_ESCAPE_PROCESSING, Any(false));
 249                 }
 250                 catch( const Exception& )
 251                 {
 252                     DBG_UNHANDLED_EXCEPTION("dbaccess");
 253                 }
 254             }
 255
 256             Reference<XDatabaseMetaData> xMeta = m_xConnection->getMetaData();
 257             css::uno::Reference< css::sdbc::XMultipleResults > xMR ( xStatement, UNO_QUERY );
 258
 259             if (xMeta.is() && xMeta->supportsMultipleResultSets() && xMR.is())
 260             {
 261                 bool hasRS = xStatement->execute(_rStatement);
 262                 if(hasRS)
 263                 {
 264                     css::uno::Reference< css::sdbc::XResultSet > xRS (xMR->getResultSet());
 265                     if (m_xShowOutput->get_active())
 266                         display(xRS);
 267                 }
 268                 else
 269                     addOutputText(
 270                         Concat2View(OUString::number(xMR->getUpdateCount()) + " rows updated\n"));
 271                 for (;;)
 272                 {
 273                     hasRS = xMR->getMoreResults();
 274                     if (!hasRS && xMR->getUpdateCount() == -1)
 275                         break;
 276                     if(hasRS)
 277                     {
 278                         css::uno::Reference< css::sdbc::XResultSet > xRS (xMR->getResultSet());
 279                         if (m_xShowOutput->get_active())
 280                             display(xRS);
 281                     }
 282                 }
 283             }
 284             else
 285             {
 286                 const OUString upperStatement = _rStatement.toAsciiUpperCase();
 287                 if (upperStatement.startsWith("UPDATE"))
 288                 {
 289                     sal_Int32 resultCount = xStatement->executeUpdate(_rStatement);
 290                     addOutputText(Concat2View(OUString::number(resultCount) + " rows updated\n"));
 291                 }
 292                 else if (upperStatement.startsWith("INSERT"))
 293                 {
 294                     sal_Int32 resultCount = xStatement->executeUpdate(_rStatement);
 295                     addOutputText(Concat2View(OUString::number(resultCount) + " rows inserted\n"));
 296                 }
 297                 else if (upperStatement.startsWith("DELETE"))
 298                 {
 299                     sal_Int32 resultCount = xStatement->executeUpdate(_rStatement);
 300                     addOutputText(Concat2View(OUString::number(resultCount) + " rows deleted\n"));
 301                 }
 302                 else if (upperStatement.startsWith("CREATE"))
 303                 {
 304                     xStatement->executeUpdate(_rStatement);
 305                     addOutputText(u"Command executed\n");
 306                 }
 307                 else if (upperStatement.startsWith("SELECT") || m_xShowOutput->get_active())
 308                 {
 309                     css::uno::Reference< css::sdbc::XResultSet > xRS = xStatement->executeQuery(_rStatement);
 310                     if (m_xShowOutput->get_active())
 311                         display(xRS);
 312                 }
 313                 else
 314                 {
 315                     sal_Int32 resultCount = xStatement->executeUpdate(_rStatement);
 316                     addOutputText(Concat2View(OUString::number(resultCount) + " rows updated\n"));
 317                 }
 318             }
 319             // successful
 320             sStatus = DBA_RES(STR_COMMAND_EXECUTED_SUCCESSFULLY);
 321
 322             // dispose the statement
 323             ::comphelper::disposeComponent(xStatement);
 324         }
 325         catch(const SQLException& e)
 326         {
 327             sStatus = e.Message;
 328         }
 329         catch( const Exception& )
 330         {
 331             DBG_UNHANDLED_EXCEPTION("dbaccess");
 332         }
 333
 334         // add the status text
 335         addStatusText(sStatus);
 336     }
 337
 338     void DirectSQLDialog::display(const css::uno::Reference< css::sdbc::XResultSet >& xRS)
 339     {
 340
 341         const Reference<XResultSetMetaData> xResultSetMetaData = Reference<XResultSetMetaDataSupplier>(xRS,UNO_QUERY_THROW)->getMetaData();
 342         const sal_Int32 nColumnsCount = xResultSetMetaData->getColumnCount();
 343         sal_Int32 nRowCount = 0;
 344         // get a handle for the rows
 345         css::uno::Reference< css::sdbc::XRow > xRow( xRS, css::uno::UNO_QUERY );
 346         // work through each of the rows
 347         while (xRS->next())
 348         {
 349             // initialise the output line for each row
 350             OUStringBuffer out;
 351             // work along the columns until that are none left
 352             try
 353             {
 354                 for (sal_Int32 i = 1; i <= nColumnsCount; ++i)
 355                 {
 356                     switch (xResultSetMetaData->getColumnType(i))
 357                     {
 358                         // tdf#153317, at least "Bit" type in Mysql/MariaDB gives: "\000" or "\001"
 359                         // so retrieve Sequence from getBytes, test if it has a length of 1 (so we avoid BLOB/CLOB or other complex types)
 360                         // and test if the value of first byte is one of those.
 361                         // In this case, there's a good chance it's a "Bit" field
 362                         case css::sdbc::DataType::BIT:
 363                         {
 364                             auto seq = xRow->getBytes(i);
 365                             if ((seq.getLength() == 1) && (seq[0] >= 0) && (seq[0] <= 1))
 366                             {
 367                                 out.append(OUString::number(static_cast<int>(seq[0])) + ",");
 368                             }
 369                             else
 370                             {
 371                                 out.append(xRow->getString(i) + ",");
 372                             }
 373                             break;
 374                         }
 375                         // for the rest, be dumb, treat everything as a string
 376                         default:
 377                             out.append(xRow->getString(i) + ",");
 378                     }
 379                 }
 380                 nRowCount++;
 381             }
 382             // trap for when we fall off the end of the row
 383             catch (const SQLException&)
 384             {
 385             }
 386             // report the output
 387             addOutputText(out);
 388         }
 389         addOutputText(DBA_RES_PLURAL(STR_COMMAND_NROWS, nRowCount).replaceAll("%1", OUString::number(nRowCount)));
 390     }
 391
 392     void DirectSQLDialog::addStatusText(std::u16string_view _rMessage)
 393     {
 394         OUString sAppendMessage = OUString::number(m_nStatusCount++) + ": " + _rMessage + "\n\n";
 395
 396         OUString sCompleteMessage = m_xStatus->get_text() + sAppendMessage;
 397         m_xStatus->set_text(sCompleteMessage);
 398
 399         m_xStatus->select_region(sCompleteMessage.getLength(), sCompleteMessage.getLength());
 400     }
 401
 402     void DirectSQLDialog::addOutputText(std::u16string_view _rMessage)
 403     {
 404         OUString sAppendMessage = OUString::Concat(_rMessage) + "\n";
 405
 406         OUString sCompleteMessage = m_xOutput->get_text() + sAppendMessage;
 407         m_xOutput->set_text(sCompleteMessage);
 408     }
 409
 410     void DirectSQLDialog::executeCurrent()
 411     {
 412         #ifdef DBG_UTIL
 413         {
 414             const char* pError = impl_CheckInvariants();
 415             if (pError)
 416                 SAL_WARN("dbaccess.ui", "DirectSQLDialog::executeCurrent: " << pError);
 417         }
 418         #endif
 419
 420         OUString sStatement = m_xSQL->GetText();
 421
 422         // execute
 423         implExecuteStatement(sStatement);
 424
 425         // add the statement to the history
 426         implAddToStatementHistory(sStatement);
 427
 428         m_xSQL->GrabFocus();
 429     }
 430
 431     void DirectSQLDialog::switchToHistory(sal_Int32 _nHistoryPos)
 432     {
 433         #ifdef DBG_UTIL
 434         {
 435             const char* pError = impl_CheckInvariants();
 436             if (pError)
 437                 SAL_WARN("dbaccess.ui", "DirectSQLDialog::switchToHistory: " << pError);
 438         }
 439         #endif
 440
 441         if ((_nHistoryPos >= 0) && (_nHistoryPos < getHistorySize()))
 442         {
 443             // set the text in the statement editor
 444             OUString sStatement = m_aStatementHistory[_nHistoryPos];
 445             m_xSQL->SetTextAndUpdate(sStatement);
 446             OnStatementModified(nullptr);
 447
 448             m_xSQL->GrabFocus();
 449         }
 450         else
 451             OSL_FAIL("DirectSQLDialog::switchToHistory: invalid position!");
 452     }
 453
 454     IMPL_LINK_NOARG( DirectSQLDialog, OnStatementModified, LinkParamNone*, void )
 455     {
 456         m_xExecute->set_sensitive(!m_xSQL->GetText().isEmpty());
 457     }
 458
 459     IMPL_LINK_NOARG( DirectSQLDialog, OnCloseClick, weld::Button&, void )
 460     {
 461         m_xDialog->response(RET_OK);
 462     }
 463
 464     IMPL_LINK_NOARG( DirectSQLDialog, OnClose, void*, void )
 465     {
 466         assert(m_pClosingEvent);
 467         Application::RemoveUserEvent(m_pClosingEvent);
 468         m_pClosingEvent = nullptr;
 469
 470         m_xDialog->response(RET_OK);
 471     }
 472
 473     IMPL_LINK_NOARG( DirectSQLDialog, OnExecute, weld::Button&, void )
 474     {
 475         executeCurrent();
 476     }
 477
 478     IMPL_LINK_NOARG( DirectSQLDialog, OnListEntrySelected, weld::ComboBox&, void )
 479     {
 480         const sal_Int32 nSelected = m_xSQLHistory->get_active();
 481         if (nSelected != -1)
 482             switchToHistory(nSelected);
 483     }
 484
 485 }   // namespace dbaui
 486
 487 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */