LibreOffice Module sc (master)  1
doubleref.cxx
Go to the documentation of this file.
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 <doubleref.hxx>
21 #include <global.hxx>
22 #include <document.hxx>
23 #include <queryparam.hxx>
24 #include <queryentry.hxx>
25 #include <globstr.hrc>
26 #include <scresid.hxx>
27 #include <scmatrix.hxx>
28 
29 #include <svl/sharedstringpool.hxx>
30 #include <osl/diagnose.h>
31 #include <unotools/charclass.hxx>
33 
34 #include <memory>
35 #include <utility>
36 #include <vector>
37 
38 using ::std::unique_ptr;
39 using ::std::vector;
40 
41 namespace {
42 
43 void lcl_uppercase(OUString& rStr)
44 {
45  rStr = ScGlobal::getCharClass().uppercase(rStr.trim());
46 }
47 
48 bool lcl_createStarQuery(
49  const ScDocument* pDoc,
50  svl::SharedStringPool& rPool, ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
51 {
52  // A valid StarQuery must be at least 4 columns wide. To be precise it
53  // should be exactly 4 columns ...
54  // Additionally, if this wasn't checked, a formula pointing to a valid 1-3
55  // column Excel style query range immediately left to itself would result
56  // in a circular reference when the field name or operator or value (first
57  // to third query range column) is obtained (#i58354#). Furthermore, if the
58  // range wasn't sufficiently specified data changes wouldn't flag formula
59  // cells for recalculation.
60 
61  if (pQueryRef->getColSize() < 4)
62  return false;
63 
64  bool bValid;
65  OUString aCellStr;
66  SCSIZE nIndex = 0;
67  SCROW nRow = 0;
68  SCROW nRows = pDBRef->getRowSize();
69  SCSIZE nNewEntries = static_cast<SCSIZE>(nRows);
70  pParam->Resize(nNewEntries);
71 
72  do
73  {
74  ScQueryEntry& rEntry = pParam->GetEntry(nIndex);
75 
76  bValid = false;
77 
78  if (nIndex > 0)
79  {
80  // For all entries after the first one, check the and/or connector in the first column.
81  aCellStr = pQueryRef->getString(0, nRow);
82  lcl_uppercase(aCellStr);
83  if ( aCellStr == ScResId(STR_TABLE_AND) )
84  {
85  rEntry.eConnect = SC_AND;
86  bValid = true;
87  }
88  else if ( aCellStr == ScResId(STR_TABLE_OR) )
89  {
90  rEntry.eConnect = SC_OR;
91  bValid = true;
92  }
93  }
94 
95  if ((nIndex < 1) || bValid)
96  {
97  // field name in the 2nd column.
98  aCellStr = pQueryRef->getString(1, nRow);
99  SCCOL nField = pDBRef->findFieldColumn(aCellStr); // TODO: must be case insensitive comparison.
100  if (pDoc->ValidCol(nField))
101  {
102  rEntry.nField = nField;
103  bValid = true;
104  }
105  else
106  bValid = false;
107  }
108 
109  if (bValid)
110  {
111  // equality, non-equality operator in the 3rd column.
112  aCellStr = pQueryRef->getString(2, nRow);
113  lcl_uppercase(aCellStr);
114  const sal_Unicode* p = aCellStr.getStr();
115  if (p[0] == '<')
116  {
117  if (p[1] == '>')
118  rEntry.eOp = SC_NOT_EQUAL;
119  else if (p[1] == '=')
120  rEntry.eOp = SC_LESS_EQUAL;
121  else
122  rEntry.eOp = SC_LESS;
123  }
124  else if (p[0] == '>')
125  {
126  if (p[1] == '=')
127  rEntry.eOp = SC_GREATER_EQUAL;
128  else
129  rEntry.eOp = SC_GREATER;
130  }
131  else if (p[0] == '=')
132  rEntry.eOp = SC_EQUAL;
133 
134  }
135 
136  if (bValid)
137  {
138  // Finally, the right-hand-side value in the 4th column.
139  rEntry.GetQueryItem().maString =
140  rPool.intern(pQueryRef->getString(3, nRow));
141  rEntry.bDoQuery = true;
142  }
143  nIndex++;
144  nRow++;
145  }
146  while (bValid && (nRow < nRows) /* && (nIndex < MAXQUERY) */ );
147  return bValid;
148 }
149 
150 bool lcl_createExcelQuery(
151  const ScDocument* pDoc,
152  svl::SharedStringPool& rPool, ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
153 {
154  bool bValid = true;
155  SCCOL nCols = pQueryRef->getColSize();
156  SCROW nRows = pQueryRef->getRowSize();
157  vector<SCCOL> aFields(nCols);
158  SCCOL nCol = 0;
159  while (bValid && (nCol < nCols))
160  {
161  OUString aQueryStr = pQueryRef->getString(nCol, 0);
162  SCCOL nField = pDBRef->findFieldColumn(aQueryStr);
163  if (pDoc->ValidCol(nField))
164  aFields[nCol] = nField;
165  else
166  bValid = false;
167  ++nCol;
168  }
169 
170  if (bValid)
171  {
172  // Count the number of visible cells (excluding the header row). Each
173  // visible cell corresponds with a single query.
174  SCSIZE nVisible = pQueryRef->getVisibleDataCellCount();
175  if ( nVisible > SCSIZE_MAX / sizeof(void*) )
176  {
177  OSL_FAIL("too many filter criteria");
178  nVisible = 0;
179  }
180 
181  SCSIZE nNewEntries = nVisible;
182  pParam->Resize( nNewEntries );
183 
184  SCSIZE nIndex = 0;
185  SCROW nRow = 1;
186  OUString aCellStr;
187  while (nRow < nRows)
188  {
189  nCol = 0;
190  while (nCol < nCols)
191  {
192  aCellStr = pQueryRef->getString(nCol, nRow);
193  aCellStr = ScGlobal::getCharClass().uppercase( aCellStr );
194  if (!aCellStr.isEmpty())
195  {
196  if (nIndex < nNewEntries)
197  {
198  pParam->GetEntry(nIndex).nField = aFields[nCol];
199  pParam->FillInExcelSyntax(rPool, aCellStr, nIndex, nullptr);
200  nIndex++;
201  if (nIndex < nNewEntries)
202  pParam->GetEntry(nIndex).eConnect = SC_AND;
203  }
204  else
205  bValid = false;
206  }
207  nCol++;
208  }
209  nRow++;
210  if (nIndex < nNewEntries)
211  pParam->GetEntry(nIndex).eConnect = SC_OR;
212  }
213  }
214  return bValid;
215 }
216 
217 bool lcl_fillQueryEntries(
218  const ScDocument* pDoc,
219  svl::SharedStringPool& rPool, ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
220 {
221  SCSIZE nCount = pParam->GetEntryCount();
222  for (SCSIZE i = 0; i < nCount; ++i)
223  pParam->GetEntry(i).Clear();
224 
225  // Standard QueryTabelle
226  bool bValid = lcl_createStarQuery(pDoc, rPool, pParam, pDBRef, pQueryRef);
227  // Excel QueryTabelle
228  if (!bValid)
229  bValid = lcl_createExcelQuery(pDoc, rPool, pParam, pDBRef, pQueryRef);
230 
231  nCount = pParam->GetEntryCount();
232  if (bValid)
233  {
234  // bQueryByString must be set
235  for (SCSIZE i = 0; i < nCount; ++i)
237  }
238  else
239  {
240  // nothing
241  for (SCSIZE i = 0; i < nCount; ++i)
242  pParam->GetEntry(i).Clear();
243  }
244  return bValid;
245 }
246 
247 }
248 
250  mpDoc(pDoc)
251 {
252 }
253 
255 {
256 }
257 
259 {
260  if (!pDBRef)
261  return false;
262 
263  return lcl_fillQueryEntries(getDoc(), getDoc()->GetSharedStringPool(), pParam, pDBRef, this);
264 }
265 
267 {
268  pParam->bHasHeader = true;
269  pParam->bByRow = true;
270  pParam->bInplace = true;
271  pParam->bCaseSens = false;
273  pParam->bDuplicate = true;
274 }
275 
277  ScDBRangeBase(pDoc), maRange(rRange)
278 {
279 }
280 
282 {
283 }
284 
286 {
287  return maRange.aEnd.Col() - maRange.aStart.Col() + 1;
288 }
289 
291 {
292  return maRange.aEnd.Row() - maRange.aStart.Row() + 1;
293 }
294 
296 {
297  SCCOL nCols = getColSize();
298  SCROW nRows = getRowSize();
299  if (nRows <= 1)
300  return 0;
301 
302  return (nRows-1)*nCols;
303 }
304 
305 OUString ScDBInternalRange::getString(SCCOL nCol, SCROW nRow) const
306 {
307  const ScAddress& s = maRange.aStart;
308  // #i109200# this is used in formula calculation, use GetInputString, not GetString
309  // (consistent with ScDBInternalRange::getCellString)
310  // GetStringForFormula is not used here, to allow querying for date values.
311  return getDoc()->GetInputString(s.Col() + nCol, s.Row() + nRow, maRange.aStart.Tab());
312 }
313 
315 {
316  return getRange().aStart.Col();
317 }
318 
320 {
321  const ScRange& rRange = getRange();
322  const ScAddress& s = rRange.aStart;
323 
324  SCCOL nDBCol1 = s.Col();
325 
326  // Don't handle out-of-bound condition here. We'll do that later.
327  return nIndex + nDBCol1 - 1;
328 }
329 
330 SCCOL ScDBInternalRange::findFieldColumn(const OUString& rStr, FormulaError* pErr) const
331 {
332  const ScAddress& s = maRange.aStart;
333  const ScAddress& e = maRange.aEnd;
334  OUString aUpper = rStr;
335  lcl_uppercase(aUpper);
336 
337  SCCOL nDBCol1 = s.Col();
338  SCROW nDBRow1 = s.Row();
339  SCTAB nDBTab1 = s.Tab();
340  SCCOL nDBCol2 = e.Col();
341 
342  bool bFound = false;
343 
344  OUString aCellStr;
345  ScAddress aLook( nDBCol1, nDBRow1, nDBTab1 );
346  while (!bFound && (aLook.Col() <= nDBCol2))
347  {
348  FormulaError nErr = getDoc()->GetStringForFormula( aLook, aCellStr );
349  if (pErr)
350  *pErr = nErr;
351  lcl_uppercase(aCellStr);
352  bFound = ScGlobal::GetTransliteration().isEqual(aCellStr, aUpper);
353  if (!bFound)
354  aLook.IncCol();
355  }
356  SCCOL nField = aLook.Col();
357 
358  return bFound ? nField : -1;
359 }
360 
361 std::unique_ptr<ScDBQueryParamBase> ScDBInternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
362 {
363  unique_ptr<ScDBQueryParamInternal> pParam(new ScDBQueryParamInternal);
364 
365  // Set the database range first.
366  const ScAddress& s = maRange.aStart;
367  const ScAddress& e = maRange.aEnd;
368  pParam->nCol1 = s.Col();
369  pParam->nRow1 = s.Row();
370  pParam->nCol2 = e.Col();
371  pParam->nRow2 = e.Row();
372  pParam->nTab = s.Tab();
373 
374  fillQueryOptions(pParam.get());
375 
376  // Now construct the query entries from the query range.
377  if (!pQueryRef->fillQueryEntries(pParam.get(), this))
378  return nullptr;
379 
380  return std::unique_ptr<ScDBQueryParamBase>(std::move(pParam));
381 }
382 
383 bool ScDBInternalRange::isRangeEqual(const ScRange& rRange) const
384 {
385  return maRange == rRange;
386 }
387 
389  ScDBRangeBase(pDoc), mpMatrix(pMat)
390 {
391  SCSIZE nC, nR;
392  mpMatrix->GetDimensions(nC, nR);
393  mnCols = static_cast<SCCOL>(nC);
394  mnRows = static_cast<SCROW>(nR);
395 }
396 
398 {
399 }
400 
402 {
403  return mnCols;
404 }
405 
407 {
408  return mnRows;
409 }
410 
412 {
413  SCCOL nCols = getColSize();
414  SCROW nRows = getRowSize();
415  if (nRows <= 1)
416  return 0;
417 
418  return (nRows-1)*nCols;
419 }
420 
421 OUString ScDBExternalRange::getString(SCCOL nCol, SCROW nRow) const
422 {
423  if (nCol >= mnCols || nRow >= mnRows)
424  return OUString();
425 
426  return mpMatrix->GetString(nCol, nRow).getString();
427 }
428 
430 {
431  return 0;
432 }
433 
435 {
436  return nIndex - 1;
437 }
438 
439 SCCOL ScDBExternalRange::findFieldColumn(const OUString& rStr, FormulaError* pErr) const
440 {
441  if (pErr)
442  *pErr = FormulaError::NONE;
443 
444  OUString aUpper = rStr;
445  lcl_uppercase(aUpper);
446  for (SCCOL i = 0; i < mnCols; ++i)
447  {
448  OUString aUpperVal = mpMatrix->GetString(i, 0).getString();
449  lcl_uppercase(aUpperVal);
450  if (aUpper == aUpperVal)
451  return i;
452  }
453  return -1;
454 }
455 
456 std::unique_ptr<ScDBQueryParamBase> ScDBExternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
457 {
458  unique_ptr<ScDBQueryParamMatrix> pParam(new ScDBQueryParamMatrix);
459  pParam->mpMatrix = mpMatrix;
460  fillQueryOptions(pParam.get());
461 
462  // Now construct the query entries from the query range.
463  if (!pQueryRef->fillQueryEntries(pParam.get(), this))
464  return nullptr;
465 
466  return std::unique_ptr<ScDBQueryParamBase>(std::move(pParam));
467 }
468 
469 bool ScDBExternalRange::isRangeEqual(const ScRange& /*rRange*/) const
470 {
471  return false;
472 }
473 
474 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
ScDBInternalRange(ScDocument *pDoc, const ScRange &rRange)
Definition: doubleref.cxx:276
virtual SCCOL getColSize() const override
Definition: doubleref.cxx:285
sal_Int32 nIndex
const ScRange & getRange() const
Definition: doubleref.hxx:97
ScAddress aStart
Definition: address.hxx:497
SharedString intern(const OUString &rStr)
static void fillQueryOptions(ScQueryParamBase *pParam)
Populate query options that are always the same for all database queries.
Definition: doubleref.cxx:266
SC_DLLPUBLIC OUString GetInputString(SCCOL nCol, SCROW nRow, SCTAB nTab, bool bForceSystemLocale=false) const
Definition: document.cxx:3584
OUString ScResId(TranslateId aId)
Definition: scdll.cxx:90
SCROW Row() const
Definition: address.hxx:274
virtual SCCOL getColSize() const =0
const Item & GetQueryItem() const
Definition: queryentry.hxx:85
virtual std::unique_ptr< ScDBQueryParamBase > createQueryParam(const ScDBRangeBase *pQueryRef) const override
Definition: doubleref.cxx:456
virtual SCSIZE getVisibleDataCellCount() const override
Definition: doubleref.cxx:295
SCCOLROW nField
Definition: queryentry.hxx:61
ScAddress aEnd
Definition: address.hxx:498
ScDBRangeBase()=delete
sal_uInt16 sal_Unicode
const ScMatrixRef mpMatrix
Definition: doubleref.hxx:173
virtual SCCOL findFieldColumn(SCCOL nIndex) const override
Get a 0-based column index that corresponds with the passed field index.
Definition: doubleref.cxx:319
size_t SCSIZE
size_t typedef to be able to find places where code was changed from USHORT to size_t and is used to ...
Definition: address.hxx:44
virtual SCSIZE getVisibleDataCellCount() const =0
int nCount
SC_DLLPUBLIC SCSIZE GetEntryCount() const
Definition: queryparam.cxx:110
SC_DLLPUBLIC const ScQueryEntry & GetEntry(SCSIZE n) const
Definition: queryparam.cxx:115
::boost::intrusive_ptr< ScMatrix > ScMatrixRef
Definition: types.hxx:25
SCTAB Tab() const
Definition: address.hxx:283
void FillInExcelSyntax(svl::SharedStringPool &rPool, const OUString &aCellStr, SCSIZE nIndex, SvNumberFormatter *pFormatter)
Definition: queryparam.cxx:203
virtual OUString getString(SCCOL nCol, SCROW nRow) const override
Get a string value of a specified cell position.
Definition: doubleref.cxx:305
virtual bool isRangeEqual(const ScRange &rRange) const override
Definition: doubleref.cxx:469
int i
virtual SCROW getRowSize() const override
Definition: doubleref.cxx:290
void IncCol(SCCOL nDelta=1)
Definition: address.hxx:316
sal_Int16 SCCOL
Definition: types.hxx:21
bool ValidCol(SCCOL nCol) const
Definition: document.hxx:900
static SC_DLLPUBLIC const CharClass & getCharClass()
Definition: global.cxx:1024
Base class for abstracting range data backends for database functions.
Definition: doubleref.hxx:37
const SCSIZE SCSIZE_MAX
Definition: address.hxx:59
virtual ~ScDBRangeBase()=0
Definition: doubleref.cxx:254
OUString uppercase(const OUString &rStr, sal_Int32 nPos, sal_Int32 nCount) const
void Resize(size_t nNew)
Definition: queryparam.cxx:195
virtual SCSIZE getVisibleDataCellCount() const override
Definition: doubleref.cxx:411
virtual bool isRangeEqual(const ScRange &rRange) const override
Definition: doubleref.cxx:383
virtual ~ScDBExternalRange() override
Definition: doubleref.cxx:397
virtual std::unique_ptr< ScDBQueryParamBase > createQueryParam(const ScDBRangeBase *pQueryRef) const override
Definition: doubleref.cxx:361
ScDocument * getDoc() const
Definition: doubleref.hxx:79
svl::SharedString maString
Definition: queryentry.hxx:49
FormulaError
SCCOL Col() const
Definition: address.hxx:279
sal_Int32 SCROW
Definition: types.hxx:17
virtual SCCOL getFirstFieldColumn() const override
Definition: doubleref.cxx:429
virtual SCCOL findFieldColumn(SCCOL nIndex) const override
Get a 0-based column index that corresponds with the passed field index.
Definition: doubleref.cxx:434
virtual SCCOL getFirstFieldColumn() const override
Definition: doubleref.cxx:314
virtual SCCOL findFieldColumn(SCCOL nIndex) const =0
Get a 0-based column index that corresponds with the passed field index.
virtual SCROW getRowSize() const =0
bool fillQueryEntries(ScQueryParamBase *pParam, const ScDBRangeBase *pDBRef) const
Definition: doubleref.cxx:258
void * p
virtual SCCOL getColSize() const override
Definition: doubleref.cxx:401
ScQueryConnect eConnect
Definition: queryentry.hxx:63
virtual ~ScDBInternalRange() override
Definition: doubleref.cxx:281
ScDBExternalRange(ScDocument *pDoc, const ScMatrixRef &pMat)
Definition: doubleref.cxx:388
virtual OUString getString(SCCOL nCol, SCROW nRow) const =0
Get a string value of a specified cell position.
static SC_DLLPUBLIC::utl::TransliterationWrapper & GetTransliteration()
Definition: global.cxx:986
FormulaError GetStringForFormula(const ScAddress &rPos, OUString &rString)
Definition: document.cxx:3592
ScQueryOp eOp
Definition: queryentry.hxx:62
Each instance of this struct represents a single filtering criteria.
Definition: queryentry.hxx:33
utl::SearchParam::SearchType eSearchType
Definition: queryparam.hxx:43
B2DRange maRange
sal_Int16 SCTAB
Definition: types.hxx:22
virtual SCROW getRowSize() const override
Definition: doubleref.cxx:406
virtual OUString getString(SCCOL nCol, SCROW nRow) const override
Get a string value of a specified cell position.
Definition: doubleref.cxx:421