What is the purpose/concept of using temp tables versus going against the actual tables? Is it to avoid some table locks?
Generally the temp tables are there when a query requires large datasets (i.e. a list of paths, which may be large). By creating a temporary table, it is possible to execute a single query, instead of looping over the inputs and aggregating the results.
It is not used to avoid locks, as the database is only accessed from one thread at a time (not all SQLite libraries are compiled with multithreading support).
If you are looking at a specific query, maybe I can comment on that.
Thanks. That is a helpful explanation. I appreciate your looking at this use of a temp table.
The reason for this temporary table is that we need to filter the paths based on some regular expressions. The list of paths can be quite large (like millions of paths), but SQLite does not directly support the regular expressions, so we need to evaluate each path and figure out if the filters include or exclude the path.
Rather than integrating this code multiple places, we generate a temporary table with only the paths matching the user’s filter. After this initial filter process, we have a list in SQLite that exactly matches the filters, and we can use it in joins to filter the results.
You can see that in the else clause we do not load all paths into memory, but just produce a reduced path list with SQLite’s filter methods.
Thanks. That makes sense now.