在 SQLite 中创建冻结表视图的方法？
我有一个 SQLite 表，其中可能有数十万个条目，这些条目会以不规则的时间间隔在后台添加（有时会从中删除）。 UI 需要在 wxWidgets wxListCtrl 项中以用户选择的任意排序顺序显示此表。
我计划使用 wxLC_VIRTUAL 列表控件，并根据需要使用 LIMIT 和 OFFSET 在表中查询一小组项目，但我预见麻烦。当后台进程对当前查看的项目“上方”的项目进行更改时，我看不到任何方法来了解当前查看的项目的偏移量将如何变化。
有一些 SQLite 技巧来处理这个问题吗？也许有一种方法可以识别特定记录在特定排序顺序中的偏移量，而无需迭代 SELECT 语句返回的所有记录？
或者，是否有某种方法可以在特定时间创建数据库的不变视图，而无需进行耗时的复制？
如果一切都失败了，我可以存储更改的项目并稍后添加它们，但我希望我不必这样做。
I've got an SQLite table with potentially hundreds of thousands of entries, which is being added to (and occasionally removed from) in the background at irregular intervals. The UI needs to display this table in an arbitrary user-selected sorted order, within a wxWidgets wxListCtrl item.
I'm planning to use a wxLC_VIRTUAL list control, and query the table for small groups of items as needed using LIMIT and OFFSET, but I foresee trouble. When the background process makes changes to items that are "above" the currently-viewed ones, I can't see any way to know how the offsets of the currently-viewed items will change.
Is there some SQLite trick to handle this? Maybe a way to identify what offset a particular record is at in a specific sorted order, without iterating through all of the records returned by a SELECT statement?
Alternatively, is there some way to create an unchanging view of the database at a particular time, without a time-consuming duplication of it?
If all else fails, I can store the changed items and add them later, but I'm hoping I won't have to.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
通过创建一个查询来查找项目的索引，通过计算“小于”（按用户定义的顺序）我正在查找的项目的数量来解决这个问题。由于用户定义的排序，编写起来有点复杂，但它可以工作，并且即使在巨大的表上运行也快得惊人。
Solved it by creating a query to find the index of an item, by counting the number of items that are "less than" (in the user-defined order) the one I'm looking for. A little complex to write, because of the user-defined ordering, but it works, and runs surprisingly fast even on a huge table.