1 //
2 // RecordSet.h
3 //
4 // Library: Data
5 // Package: DataCore
6 // Module: RecordSet
7 //
8 // Definition of the RecordSet class.
9 //
10 // Copyright (c) 2006, Applied Informatics Software Engineering GmbH.
11 // and Contributors.
12 //
13 // SPDX-License-Identifier: BSL-1.0
14 //
15
16
17 #ifndef Data_RecordSet_INCLUDED
18 #define Data_RecordSet_INCLUDED
19
20
21 #include "Poco/Data/Data.h"
22 #include "Poco/Data/Session.h"
23 #include "Poco/Data/Extraction.h"
24 #include "Poco/Data/BulkExtraction.h"
25 #include "Poco/Data/Statement.h"
26 #include "Poco/Data/RowIterator.h"
27 #include "Poco/Data/RowFilter.h"
28 #include "Poco/Data/LOB.h"
29 #include "Poco/String.h"
30 #include "Poco/Dynamic/Var.h"
31 #include "Poco/Exception.h"
32 #include "Poco/AutoPtr.h"
33 #include <ostream>
34 #include <limits>
35
36
37 namespace Poco {
38 namespace Data {
39
40
41 class RowFilter;
42
43
44 class Data_API RecordSet: private Statement
45 /// RecordSet provides access to data returned from a query.
46 /// Data access indices (row and column) are 0-based, as usual in C++.
47 ///
48 /// Recordset provides navigation methods to iterate through the
49 /// recordset, retrieval methods to extract data, and methods
50 /// to get metadata (type, etc.) about columns.
51 ///
52 /// To work with a RecordSet, first create a Statement, execute it, and
53 /// create the RecordSet from the Statement, as follows:
54 ///
55 /// Statement select(session);
56 /// select << "SELECT * FROM Person";
57 /// select.execute();
58 /// RecordSet rs(select);
59 ///
60 /// The shorter way to do the above is following:
61 ///
62 /// RecordSet rs(session, "SELECT * FROM Person"[, new SimpleRowFormatter]);
63 ///
64 /// The third (optional) argument passed to the Recordset constructor is a RowFormatter
65 /// implementation. The formatter is used in conjunction with << operator for recordset
66 /// data formating.
67 ///
68 /// The number of rows in the RecordSet can be limited by specifying
69 /// a limit for the Statement.
70 {
71 public:
72 using RowMap = std::map<std::size_t, Row*>;
73 using ConstIterator = const RowIterator;
74 using Iterator = RowIterator;
75
76 using Statement::isNull;
77 using Statement::subTotalRowCount;
78
79 static const std::size_t UNKNOWN_TOTAL_ROW_COUNT;
80
81 explicit RecordSet(const Statement& rStatement,
82 RowFormatter::Ptr pRowFormatter = 0);
83 /// Creates the RecordSet.
84
85 RecordSet(Session& rSession,
86 const std::string& query,
87 RowFormatter::Ptr pRowFormatter = 0);
88 /// Creates the RecordSet.
89
90 RecordSet(Session& rSession,
91 const std::string& query,
92 const RowFormatter& rowFormatter);
93 /// Creates the RecordSet.
94
95 template <class RF>
RecordSet(Session & rSession,const std::string & query,const RF & rowFormatter)96 RecordSet(Session& rSession, const std::string& query, const RF& rowFormatter):
97 Statement((rSession << query, Keywords::now)),
98 _currentRow(0),
99 _pBegin(new RowIterator(this, 0 == rowsExtracted())),
100 _pEnd(new RowIterator(this, true)),
101 _totalRowCount(UNKNOWN_TOTAL_ROW_COUNT)
102 /// Creates the RecordSet.
103 {
104 setRowFormatter(Keywords::format(rowFormatter));
105 }
106
107 RecordSet(const RecordSet& other);
108 /// Copy-creates the recordset.
109
110 RecordSet(RecordSet&& other) noexcept;
111 /// Move-creates the recordset.
112
113 ~RecordSet();
114 /// Destroys the RecordSet.
115
116 void setRowFormatter(RowFormatter::Ptr pRowFormatter);
117 /// Assigns the row formatter to the statement and all recordset rows.
118
119 RecordSet& operator = (const Statement& stmt);
120 /// Assignment operator.
121
122 RecordSet& operator = (const RecordSet& other);
123 /// Assignment operator.
124
125 RecordSet& operator = (RecordSet&& other) noexcept;
126 /// Move assignment.
127
128 std::size_t rowCount() const;
129 /// Returns the number of rows in the RecordSet.
130 /// The number of rows reported is dependent on filtering.
131 /// Due to the need for filter conditions checking,
132 /// this function may suffer significant performance penalty
133 /// for large recordsets, so it should be used judiciously.
134 /// Use totalRowCount() to obtain the total number of rows.
135
136 std::size_t extractedRowCount() const;
137 /// Returns the number of rows extracted during the last statement
138 /// execution.
139 /// The number of rows reported is independent of filtering.
140
141 std::size_t totalRowCount() const;
142 //@ deprecated
143 /// Replaced with subTotalRowCount() and getTotalRowCount().
144
145 std::size_t getTotalRowCount() const;
146 /// Returns the total number of rows in the RecordSet.
147 /// The number of rows reported is independent of filtering.
148 /// If the total row count has not been set externally
149 /// (either explicitly or implicitly through SQL), the value
150 /// returned shall only be accurate if the statement limit
151 /// is less or equal to the total row count.
152
153 void setTotalRowCount(std::size_t totalRowCount);
154 /// Explicitly sets the total row count.
155
156 void setTotalRowCount(const std::string& sql);
157 /// Implicitly sets the total row count.
158 /// The supplied sql must return exactly one column
159 /// and one row. The returned value must be an unsigned
160 /// integer. The value is set as the total number of rows.
161
162 std::size_t columnCount() const;
163 /// Returns the number of columns in the recordset.
164
165 template <class C>
column(const std::string & name)166 const Column<C>& column(const std::string& name) const
167 /// Returns the reference to the first Column with the specified name.
168 {
169 if (isBulkExtraction())
170 {
171 using E = InternalBulkExtraction<C>;
172 return columnImpl<C,E>(name);
173 }
174 else
175 {
176 using E = InternalExtraction<C>;
177 return columnImpl<C,E>(name);
178 }
179 }
180
181 template <class C>
column(std::size_t pos)182 const Column<C>& column(std::size_t pos) const
183 /// Returns the reference to column at specified position.
184 {
185 if (isBulkExtraction())
186 {
187 using E = InternalBulkExtraction<C>;
188 return columnImpl<C,E>(pos);
189 }
190 else
191 {
192 using E = InternalExtraction<C>;
193 return columnImpl<C,E>(pos);
194 }
195 }
196
197 Row& row(std::size_t pos);
198 /// Returns reference to row at position pos.
199 /// Rows are lazy-created and cached.
200
201 template <class T>
202 const T& value(std::size_t col, std::size_t row, bool useFilter = true) const
203 /// Returns the reference to data value at [col, row] location.
204 {
205 if (useFilter && isFiltered() && !isAllowed(row))
206 throw InvalidAccessException("Row not allowed");
207
208 switch (storage())
209 {
210 case STORAGE_VECTOR:
211 {
212 using C = typename std::vector<T>;
213 return column<C>(col).value(row);
214 }
215 case STORAGE_LIST:
216 {
217 using C = typename std::list<T>;
218 return column<C>(col).value(row);
219 }
220 case STORAGE_DEQUE:
221 case STORAGE_UNKNOWN:
222 {
223 using C = typename std::deque<T>;
224 return column<C>(col).value(row);
225 }
226 default:
227 throw IllegalStateException("Invalid storage setting.");
228 }
229 }
230
231 template <class T>
232 const T& value(const std::string& name, std::size_t row, bool useFilter = true) const
233 /// Returns the reference to data value at named column, row location.
234 {
235 if (useFilter && isFiltered() && !isAllowed(row))
236 throw InvalidAccessException("Row not allowed");
237
238 switch (storage())
239 {
240 case STORAGE_VECTOR:
241 {
242 using C = typename std::vector<T>;
243 return column<C>(name).value(row);
244 }
245 case STORAGE_LIST:
246 {
247 using C = typename std::list<T>;
248 return column<C>(name).value(row);
249 }
250 case STORAGE_DEQUE:
251 case STORAGE_UNKNOWN:
252 {
253 using C = typename std::deque<T>;
254 return column<C>(name).value(row);
255 }
256 default:
257 throw IllegalStateException("Invalid storage setting.");
258 }
259 }
260
261 Poco::Dynamic::Var value(std::size_t col, std::size_t row, bool checkFiltering = true) const;
262 /// Returns the data value at column, row location.
263
264 Poco::Dynamic::Var value(const std::string& name, std::size_t row, bool checkFiltering = true) const;
265 /// Returns the data value at named column, row location.
266
267 template <typename T>
268 Poco::Dynamic::Var nvl(const std::string& name, const T& deflt = T()) const
269 /// Returns the value in the named column of the current row
270 /// if the value is not NULL, or deflt otherwise.
271 {
272 if (isNull(name))
273 return Poco::Dynamic::Var(deflt);
274 else
275 return value(name, _currentRow);
276 }
277
278 template <typename T>
279 Poco::Dynamic::Var nvl(std::size_t index, const T& deflt = T()) const
280 /// Returns the value in the given column of the current row
281 /// if the value is not NULL, or deflt otherwise.
282 {
283 if (isNull(index, _currentRow))
284 return Poco::Dynamic::Var(deflt);
285 else
286 return value(index, _currentRow);
287 }
288
289 ConstIterator& begin() const;
290 /// Returns the const row iterator.
291
292 ConstIterator& end() const;
293 /// Returns the const row iterator.
294
295 Iterator begin();
296 /// Returns the row iterator.
297
298 Iterator end();
299 /// Returns the row iterator.
300
301 bool moveFirst();
302 /// Moves the row cursor to the first row.
303 ///
304 /// Returns true if there is at least one row in the RecordSet,
305 /// false otherwise.
306
307 bool moveNext();
308 /// Moves the row cursor to the next row.
309 ///
310 /// Returns true if the row is available, or false
311 /// if the end of the record set has been reached and
312 /// no more rows are available.
313
314 bool movePrevious();
315 /// Moves the row cursor to the previous row.
316 ///
317 /// Returns true if the row is available, or false
318 /// if there are no more rows available.
319
320 bool moveLast();
321 /// Moves the row cursor to the last row.
322 ///
323 /// Returns true if there is at least one row in the RecordSet,
324 /// false otherwise.
325
326 using Statement::reset;
327 /// Don't hide base class method.
328
329 void reset(const Statement& stmt);
330 /// Resets the RecordSet and assigns a new statement.
331 /// Should be called after the given statement has been reset,
332 /// assigned a new SQL statement, and executed.
333 ///
334 /// Does not remove the associated RowFilter or RowFormatter.
335
336 Poco::Dynamic::Var value(const std::string& name);
337 /// Returns the value in the named column of the current row.
338
339 Poco::Dynamic::Var value(std::size_t index);
340 /// Returns the value in the given column of the current row.
341
342 Poco::Dynamic::Var operator [] (const std::string& name);
343 /// Returns the value in the named column of the current row.
344
345 Poco::Dynamic::Var operator [] (std::size_t index);
346 /// Returns the value in the named column of the current row.
347
348 MetaColumn::ColumnDataType columnType(std::size_t pos) const;
349 /// Returns the type for the column at specified position.
350
351 MetaColumn::ColumnDataType columnType(const std::string& name) const;
352 /// Returns the type for the column with specified name.
353
354 const std::string& columnName(std::size_t pos) const;
355 /// Returns column name for the column at specified position.
356
357 std::size_t columnLength(std::size_t pos) const;
358 /// Returns column maximum length for the column at specified position.
359
360 std::size_t columnLength(const std::string& name) const;
361 /// Returns column maximum length for the column with specified name.
362
363 std::size_t columnPrecision(std::size_t pos) const;
364 /// Returns column precision for the column at specified position.
365 /// Valid for floating point fields only (zero for other data types).
366
367 std::size_t columnPrecision(const std::string& name) const;
368 /// Returns column precision for the column with specified name.
369 /// Valid for floating point fields only (zero for other data types).
370
371 bool isNull(const std::string& name) const;
372 /// Returns true if column value of the current row is null.
373
374 std::ostream& copyNames(std::ostream& os) const;
375 /// Copies the column names to the target output stream.
376 /// Copied string is formatted by the current RowFormatter.
377
378 void formatNames() const;
379 /// Formats names using the current RowFormatter.
380
381 std::ostream& copyValues(std::ostream& os,
382 std::size_t offset = 0,
383 std::size_t length = RowIterator::POSITION_END) const;
384 /// Copies the data values to the supplied output stream.
385 /// The data set to be copied is starting at the specified offset
386 /// from the recordset beginning. The number of rows to be copied
387 /// is specified by length argument.
388 /// An invalid combination of offset/length arguments shall
389 /// cause RangeException to be thrown.
390 /// Copied string is formatted by the current RowFormatter.
391
392 void formatValues(std::size_t offset, std::size_t length) const;
393 /// Formats values using the current RowFormatter.
394 /// The data set to be formatted is starting at the specified offset
395 /// from the recordset beginning. The number of rows to be copied
396 /// is specified by length argument.
397 /// An invalid combination of offset/length arguments shall
398 /// cause RangeException to be thrown.
399
400 std::ostream& copy(std::ostream& os,
401 std::size_t offset = 0,
402 std::size_t length = RowIterator::POSITION_END) const;
403 /// Copies the column names and values to the target output stream.
404 /// Copied strings are formatted by the current RowFormatter.
405
406 bool isFiltered() const;
407 /// Returns true if recordset is filtered.
408
409 private:
410 RecordSet();
411
412 template<class C, class E>
columnPosition(const std::string & name)413 std::size_t columnPosition(const std::string& name) const
414 /// Returns the position of the column with specified name.
415 {
416 using T = typename C::value_type;
417 using ExtractionVecPtr = const E*;
418
419 bool typeFound = false;
420
421 const AbstractExtractionVec& rExtractions = extractions();
422 AbstractExtractionVec::const_iterator it = rExtractions.begin();
423 AbstractExtractionVec::const_iterator end = rExtractions.end();
424
425 for (; it != end; ++it)
426 {
427 ExtractionVecPtr pExtraction = dynamic_cast<ExtractionVecPtr>(it->get());
428
429 if (pExtraction)
430 {
431 typeFound = true;
432 const Column<C>& col = pExtraction->column();
433 if (0 == Poco::icompare(name, col.name()))
434 return col.position();
435 }
436 }
437
438 if (typeFound)
439 throw NotFoundException(Poco::format("Column name: %s", name));
440 else
441 throw NotFoundException(Poco::format("Column type: %s, name: %s", std::string(typeid(T).name()), name));
442 }
443
444 template <class C, class E>
columnImpl(const std::string & name)445 const Column<C>& columnImpl(const std::string& name) const
446 /// Returns the reference to the first Column with the specified name.
447 {
448 return columnImpl<C,E>(columnPosition<C,E>(name));
449 }
450
451 template <class C, class E>
columnImpl(std::size_t pos)452 const Column<C>& columnImpl(std::size_t pos) const
453 /// Returns the reference to column at specified position.
454 {
455 using T = typename C::value_type;
456 using ExtractionVecPtr = const E*;
457
458 const AbstractExtractionVec& rExtractions = extractions();
459
460 std::size_t s = rExtractions.size();
461 if (0 == s || pos >= s)
462 throw RangeException(Poco::format("Invalid column index: %z", pos));
463
464 ExtractionVecPtr pExtraction = dynamic_cast<ExtractionVecPtr>(rExtractions[pos].get());
465
466 if (pExtraction)
467 {
468 return pExtraction->column();
469 }
470 else
471 {
472 throw Poco::BadCastException(Poco::format("Type cast failed!\nColumn: %z\nTarget type:\t%s",
473 pos,
474 std::string(typeid(T).name())));
475 }
476 }
477
478 bool isAllowed(std::size_t row) const;
479 /// Returns true if the specified row is allowed by the
480 /// currently active filter.
481
482 void filter(const Poco::AutoPtr<RowFilter>& pFilter);
483 /// Sets the filter for the RecordSet.
484
485 const Poco::AutoPtr<RowFilter>& getFilter() const;
486 /// Returns the filter associated with the RecordSet.
487
488 std::size_t _currentRow;
489 RowIterator* _pBegin;
490 RowIterator* _pEnd;
491 RowMap _rowMap;
492 Poco::AutoPtr<RowFilter> _pFilter;
493 std::size_t _totalRowCount;
494
495 friend class RowIterator;
496 friend class RowFilter;
497 };
498
499
500 ///
501 /// inlines
502 ///
503
504
505 inline Data_API std::ostream& operator << (std::ostream &os, const RecordSet& rs)
506 {
507 return rs.copy(os);
508 }
509
510
getTotalRowCount()511 inline std::size_t RecordSet::getTotalRowCount() const
512 {
513 if (UNKNOWN_TOTAL_ROW_COUNT == _totalRowCount)
514 return subTotalRowCount();
515 else
516 return _totalRowCount;
517 }
518
519
totalRowCount()520 inline std::size_t RecordSet::totalRowCount() const
521 {
522 return getTotalRowCount();
523 }
524
525
setTotalRowCount(std::size_t totalRowCount)526 inline void RecordSet::setTotalRowCount(std::size_t totalRowCount)
527 {
528 _totalRowCount = totalRowCount;
529 }
530
531
extractedRowCount()532 inline std::size_t RecordSet::extractedRowCount() const
533 {
534 return rowsExtracted();
535 }
536
537
columnCount()538 inline std::size_t RecordSet::columnCount() const
539 {
540 return static_cast<std::size_t>(extractions().size());
541 }
542
543
544 inline RecordSet& RecordSet::operator = (const Statement& stmt)
545 {
546 reset(stmt);
547 return *this;
548 }
549
550
551 inline RecordSet& RecordSet::operator = (const RecordSet& other)
552 {
553 reset(other);
554 return *this;
555 }
556
557
value(const std::string & name)558 inline Poco::Dynamic::Var RecordSet::value(const std::string& name)
559 {
560 return value(name, _currentRow);
561 }
562
563
value(std::size_t index)564 inline Poco::Dynamic::Var RecordSet::value(std::size_t index)
565 {
566 return value(index, _currentRow);
567 }
568
569
570 inline Poco::Dynamic::Var RecordSet::operator [] (const std::string& name)
571 {
572 return value(name, _currentRow);
573 }
574
575
576 inline Poco::Dynamic::Var RecordSet::operator [] (std::size_t index)
577 {
578 return value(index, _currentRow);
579 }
580
581
columnType(std::size_t pos)582 inline MetaColumn::ColumnDataType RecordSet::columnType(std::size_t pos)const
583 {
584 return metaColumn(static_cast<UInt32>(pos)).type();
585 }
586
587
columnType(const std::string & name)588 inline MetaColumn::ColumnDataType RecordSet::columnType(const std::string& name)const
589 {
590 return metaColumn(name).type();
591 }
592
593
columnName(std::size_t pos)594 inline const std::string& RecordSet::columnName(std::size_t pos) const
595 {
596 return metaColumn(static_cast<UInt32>(pos)).name();
597 }
598
599
columnLength(std::size_t pos)600 inline std::size_t RecordSet::columnLength(std::size_t pos) const
601 {
602 return metaColumn(static_cast<UInt32>(pos)).length();
603 }
604
605
columnLength(const std::string & name)606 inline std::size_t RecordSet::columnLength(const std::string& name)const
607 {
608 return metaColumn(name).length();
609 }
610
611
columnPrecision(std::size_t pos)612 inline std::size_t RecordSet::columnPrecision(std::size_t pos) const
613 {
614 return metaColumn(static_cast<UInt32>(pos)).precision();
615 }
616
617
columnPrecision(const std::string & name)618 inline std::size_t RecordSet::columnPrecision(const std::string& name)const
619 {
620 return metaColumn(name).precision();
621 }
622
623
isNull(const std::string & name)624 inline bool RecordSet::isNull(const std::string& name) const
625 {
626 return isNull(metaColumn(name).position(), _currentRow);
627 }
628
629
begin()630 inline RecordSet::ConstIterator& RecordSet::begin() const
631 {
632 return *_pBegin;
633 }
634
635
end()636 inline RecordSet::ConstIterator& RecordSet::end() const
637 {
638 return *_pEnd;
639 }
640
641
begin()642 inline RecordSet::Iterator RecordSet::begin()
643 {
644 return *_pBegin;
645 }
646
647
end()648 inline RecordSet::Iterator RecordSet::end()
649 {
650 return *_pEnd;
651 }
652
653
getFilter()654 inline const Poco::AutoPtr<RowFilter>& RecordSet::getFilter() const
655 {
656 return _pFilter;
657 }
658
659
formatNames()660 inline void RecordSet::formatNames() const
661 {
662 (*_pBegin)->formatNames();
663 }
664
665
666 } } // namespace Poco::Data
667
668
669 #endif // Data_RecordSet_INCLUDED
670