I have a SQLite database that contains a huge set of log messages.
I want to display this in a list view (using wxWidgets).
The user can reorder the list (by pressing the column header), apply a filter on the result set and navigate through it as a usual list, using the scroll bar. The user can also select one or multiple entries in the list and delete them.
I have a virtual list model: the list view asks the model for the content of a particular row. The model issues a select-query with the current filter-conditions and order and returns the corresponding row from the result.
To make it faster I keep page-caches of results: when a row is requested i fetch a whole page (~100 rows) using LIMIT and OFFSET and return the particular row from the page. I store a number of pages, and next time a row is requested I first look if it is available in one of the cached pages. This technique has proven to be fast and responsive even with lots of entries (50k+).
The problem
My problem is how to handle updates/inserts/deletes. I have one trigger for each so the model is notified whenever an insert/update/delete happens. The trigger also tell the model the ID (primary key) of the affected entry.
My first version simply made a complete reset of the model after each trigger. This was not very fast, but fast enough. The problem was that if the user had made a selection of one or a couple of rows, the selection was lost.
The base class of the model (wxDataViewVirtualListModel) contains methods that should be called when a change happens:
- RowInserted (row)
- RowDeleted (row)
- RowChanged (row)
If I used them the selection problem would be solved, however there are problems:
- How do I know if the changed row is within the currently filtered set?
- How do I know which row in the list view was affected?
The first problem could be solved by creating a method that check if the entry belongs to the set. It must behave exactly like the SQL-conditions, but it is doable.
The second problem I have simply no idea about how to solve.
I've used a bogus (0 or last row) row-number to force the view to be updated, but the problem is if the row was inserted/deleted before the selection, the selection points to wrong rows afterwards, and so on.
How would you do? Keep an advanced data structure with all entries in memory?
This question is related to another question: http://stackoverflow.com/questions/821506/display-large-result-set