emulating incremental fetch without implementing canFetchMore() and fetchMore()
-
wrote on 19 Aug 2021, 14:14 last edited by user4592357
i use QSql for fetching data from SQLite DB and showing them in table view, and selecting around ~19 million records from DB segfaults on
qsqlCachedResultPrivate::nextIndex()
. i cannot set forward-only mode to query though.so i'm approaching the problem from another angle, and want to implement incremental loading.
but i'm using a spreadsheet library implemented by someone, and the lib doesn't implement the functions needed to Qt view/model incremental loading (canFetchMore()
andfetchMore()
), this the model'sdata()
method:QVariant DataModel::data(const QModelIndex& index, int role ) const { int nRow = index.row(); int nCol = index.column(); if (nRow < 0 || nRow >= m_pData->getRowCount() || nCol < 0 || nCol >= m_pData->getColumnCount()) return QVariant(); if (role == Qt::DisplayRole) { QString sVal; m_pData->getData( nRow, nCol, sVal ); return sVal; } ....
so i tried to emulate the functionality.
this is the class that returns the data (the m_pData from above).
i return SQL record from this class for given row (getRecord()
method), and column is determined somewhere else (not relevant here):class DataSource { mutable std::vector<std::unique_ptr<QSqlRecord>> m_vecRowRecords; mutable int m_nRowCount = 1'000'000; static const int s_resultChunkSize = 100; // 1000'000; mutable int m_lastLoadedPage = 0; mutable int m_realRowCount=0; QString m_sQueryStr; mutable bool m_bRealCountKnown = false; public: DataSource() { sQuery = ... m_sQueryStr = sQuery; sQuery += QString(" LIMIT %1 OFFSET %2").arg(s_resultChunkSize).arg(m_lastLoadedPage); m_pQuery->prepare(sQuery); m_realRowCount = s_resultChunkSize; if (m_pQuery->exec()) m_vecRowRecords.resize(s_resultChunkSize); } int DataSource::getRowCount() const { return m_nRowCount; } QSqlRecord *pydbDataSource::getRecord(int nRow) const { if (nRow < 0) return nullptr; if (nRow < m_realRowCount) { auto &pRecord = m_vecRowRecords[nRow]; if (pRecord == nullptr) { m_pQuery->seek(nRow); pRecord = std::make_unique<QSqlRecord>(m_pQuery->record()); } return pRecord.get(); } if (m_bRealCountKnown) { if (nRow == m_realRowCount) return m_vecRowRecords.back().get(); return nullptr; } QString sQuery = m_sQueryStr + QString(" LIMIT %1 OFFSET %2").arg(s_resultChunkSize).arg(++m_lastLoadedPage * s_resultChunkSize); m_pQuery->prepare(sQuery); if (m_pQuery->exec()) { int newlyFetched = 0; while (m_pQuery->next()) { ++newlyFetched; m_vecRowRecords.emplace_back(std::make_unique<QSqlRecord>(m_pQuery->record())); } m_realRowCount += newlyFetched; if (!newlyFetched || newlyFetched < s_resultChunkSize) { m_nRowCount = m_realRowCount; m_bRealCountKnown = true; } } return m_vecRowRecords[nRow].get(); }
for most part this works, i'm able to scroll up to the end of the results.
but at the end of the table it crashes. i debugged and found out that even though i setm_nRowCount = m_realRowCount;
as soon as there are no more rows to fetch, this function is called for rows past the end ofm_nRowCount
, e.g.m_nRowCount + 1
in which case i return nullptr.i'm not sure this is the correct solution to my problem, but this is what i came up with.
please advice how can i solve this crash? or if you have a better solution, please share it.
thanks -
i use QSql for fetching data from SQLite DB and showing them in table view, and selecting around ~19 million records from DB segfaults on
qsqlCachedResultPrivate::nextIndex()
. i cannot set forward-only mode to query though.so i'm approaching the problem from another angle, and want to implement incremental loading.
but i'm using a spreadsheet library implemented by someone, and the lib doesn't implement the functions needed to Qt view/model incremental loading (canFetchMore()
andfetchMore()
), this the model'sdata()
method:QVariant DataModel::data(const QModelIndex& index, int role ) const { int nRow = index.row(); int nCol = index.column(); if (nRow < 0 || nRow >= m_pData->getRowCount() || nCol < 0 || nCol >= m_pData->getColumnCount()) return QVariant(); if (role == Qt::DisplayRole) { QString sVal; m_pData->getData( nRow, nCol, sVal ); return sVal; } ....
so i tried to emulate the functionality.
this is the class that returns the data (the m_pData from above).
i return SQL record from this class for given row (getRecord()
method), and column is determined somewhere else (not relevant here):class DataSource { mutable std::vector<std::unique_ptr<QSqlRecord>> m_vecRowRecords; mutable int m_nRowCount = 1'000'000; static const int s_resultChunkSize = 100; // 1000'000; mutable int m_lastLoadedPage = 0; mutable int m_realRowCount=0; QString m_sQueryStr; mutable bool m_bRealCountKnown = false; public: DataSource() { sQuery = ... m_sQueryStr = sQuery; sQuery += QString(" LIMIT %1 OFFSET %2").arg(s_resultChunkSize).arg(m_lastLoadedPage); m_pQuery->prepare(sQuery); m_realRowCount = s_resultChunkSize; if (m_pQuery->exec()) m_vecRowRecords.resize(s_resultChunkSize); } int DataSource::getRowCount() const { return m_nRowCount; } QSqlRecord *pydbDataSource::getRecord(int nRow) const { if (nRow < 0) return nullptr; if (nRow < m_realRowCount) { auto &pRecord = m_vecRowRecords[nRow]; if (pRecord == nullptr) { m_pQuery->seek(nRow); pRecord = std::make_unique<QSqlRecord>(m_pQuery->record()); } return pRecord.get(); } if (m_bRealCountKnown) { if (nRow == m_realRowCount) return m_vecRowRecords.back().get(); return nullptr; } QString sQuery = m_sQueryStr + QString(" LIMIT %1 OFFSET %2").arg(s_resultChunkSize).arg(++m_lastLoadedPage * s_resultChunkSize); m_pQuery->prepare(sQuery); if (m_pQuery->exec()) { int newlyFetched = 0; while (m_pQuery->next()) { ++newlyFetched; m_vecRowRecords.emplace_back(std::make_unique<QSqlRecord>(m_pQuery->record())); } m_realRowCount += newlyFetched; if (!newlyFetched || newlyFetched < s_resultChunkSize) { m_nRowCount = m_realRowCount; m_bRealCountKnown = true; } } return m_vecRowRecords[nRow].get(); }
for most part this works, i'm able to scroll up to the end of the results.
but at the end of the table it crashes. i debugged and found out that even though i setm_nRowCount = m_realRowCount;
as soon as there are no more rows to fetch, this function is called for rows past the end ofm_nRowCount
, e.g.m_nRowCount + 1
in which case i return nullptr.i'm not sure this is the correct solution to my problem, but this is what i came up with.
please advice how can i solve this crash? or if you have a better solution, please share it.
thankswrote on 19 Aug 2021, 14:33 last edited by JonB@user4592357 said in emulating incremental fetch without implementing canFetchMore() and fetchMore():
i use QSql for fetching data from SQLite DB and showing them in table view, and selecting around ~19 million records from DB segfaults on qsqlCachedResultPrivate::nextIndex(). i cannot set forward-only mode to query though.
I will keep my comment brief, because doubtless it's not what you want to hear, and maybe somebody will want to comment on your actual code issue.
If you want to select 19 million records, show them, keep them in memory and so on, there's a potential issue. And why can you not set forward-only here? Not that I think it would necessarily help. You going to update them all in memory while reading them?
-
wrote on 19 Aug 2021, 14:40 last edited by user4592357
yes i will potentially need to update the in-memory structures at some point.
okay, i know there's a problem, but i'm not sure which solution to take on. what do you suggest?
this was my code before these changes:DataSource() { // ... if (m_pQuery->exec()) { m_nRowCount = util::resultCount(m_pQuery); m_vecRowRecords.resize(m_nRowCount); } } QSqlRecord *getRecord(int nRow) const { if (!(nRow >= 0 && nRow < m_nRowCount)) return nullptr; auto &pRecord = m_vecRowRecords[nRow]; if (pRecord == nullptr) { m_pQuery->seek(nRow); pRecord = std::make_unique<QSqlRecord>(m_pQuery->record()); } return pRecord.get(); }
-
yes i will potentially need to update the in-memory structures at some point.
okay, i know there's a problem, but i'm not sure which solution to take on. what do you suggest?
this was my code before these changes:DataSource() { // ... if (m_pQuery->exec()) { m_nRowCount = util::resultCount(m_pQuery); m_vecRowRecords.resize(m_nRowCount); } } QSqlRecord *getRecord(int nRow) const { if (!(nRow >= 0 && nRow < m_nRowCount)) return nullptr; auto &pRecord = m_vecRowRecords[nRow]; if (pRecord == nullptr) { m_pQuery->seek(nRow); pRecord = std::make_unique<QSqlRecord>(m_pQuery->record()); } return pRecord.get(); }
wrote on 19 Aug 2021, 14:46 last edited by@user4592357
Again, I'll keep short, because I don't have the answer you want. If a Qt expert who knows the internal sees this they may be able to help you more. My thought is that to implement paging/fetching correctly to keepQTableView
s happy you needfetchMore()
&canFetchMore()
implementations (unfortunately for you). I'm actually slightly surprised you say "for most part this works" without! Maybe you are close, but I don't know. -
wrote on 19 Aug 2021, 16:06 last edited by user4592357This post is deleted!
-
Hi,
How does that library you use work ?
1/6