If I wanted to ‘extract’ a table from a local SQLite database and visualize it in a TableListBox, what would be a good and efficient way to go about it?
The only thing I could think of so far is to first build a representation of the SQL table in form of a ValueTree and then use that for the table. While that seems to work, I’m not sure if that’s a very good solution (it should at least handle a table of around 10.000 entries, so I guess every bit of efficiency counts).
int callback(void* NotUsed, int argc, char** argv, char** azColName)
{
juce::ValueTree row("ROW");
for (int i = 0; i < argc; i++)
{
juce::ValueTree column(azColName[i]);
column.setProperty("value", argv[i], nullptr);
row.appendChild(node, nullptr);
}
table.appendChild(row, nullptr);
return 0;
}
juce::ValueTree table ("TABLE");
juce::String sql = "SELECT * FROM 'MYTABLE';";
sqlite3_exec(db, sql.getCharPointer(), callback, 0, &zErrMsg)
Since you seem to have this ValueTree version working, I am assuming you understand how the TableListBoxModel::refreshComponentForCell works? Without out considering the implications of doing an SQL query, and parsing that data, this callback is designed so that you only need access to the data for the items currently shown TableListBox, thus you can load data on demand in this callback.
So, a naïve implementation would be to execute the SQL query in the callback, for only the data that needs to be displayed. Of course, if the query is a ‘lengthy’ (in terms of UI responsiveness) operation, this might be unusable, as it would cause the UI to stall each time the table needed to be painted.
Assuming you are familiar with using SQL and C++, you probably have had to deal with this previously. And others may have good advice on the topic. Off the top of my head, a classic caching system might help mitigate the issue, where you load the first page, the last page, the current page, and page before and after the current page. This means you get good performance for going to the start and end, page forward/page back, and you have the items for scrolling a few lines either direction. The trick is how this cache is filled in so that it reduces performance impacts.
And while I’ve not done this, if doing the sql queries is a bad idea on the message thread, it might require that you do them on another thread, and fill in the table as the data arrives. Obviously this means that you might not have the data when TableListBoxModel::refreshComponentForCell is called, and you would need to do something at that point to asynchronously request the data, and store something so that the data can be filled in when it arrives. I don’t think this would be too difficult. But, I haven’t done any SQL is quite a long time, and can’t comment how that fits in with this design.
I went exactly this way writing modified versions of ListBoxModel and ListBox to display data from a database server. However, the biggest problem here is to navigate through such database by keys or scrollbar having in mind that the number of records can dynamically change. It is not possible to handle with the default implementation of these two classes in JUCE, but works after some modifications. Because data transfer can take some time, for example when connecting to a network server, I display temporarily something like “waiting for data…” in all cells waiting. In the production code it is a little bit more complicated, you need to handle user actions like hectic scrolling etc. but more or less it works this way.
SQLite supports limit & offset on your query, I don’t think 10k rows is too large to process in the callback.
I load the first 25 rows, and when the user scrolls down and there only 15 rows left I load the next 25. The list box i think handles this fine out of the box
With regards to network vs a local db, you should be able to get a handle of the length to fetch the next records, then trigger this when the user hits a certain point at a reasonable scroll speed, otherwise you can stick a null row at the end of the list which says “waiting for results” instead of having it affect all rows of the list box
