Is your feature request related to a problem? Please describe.
I love the power of exposing the data from logfiles to a SQL interface with the SQLite integration. However, once the query has been executed the result remains static. I have to re-execute the query to see updated results whenever new log lines have appeared, and I can't treat the results as a zoomable dataset.
E.g. I currently regurarly run a report query that summarises the number of ban actions executed by fail2ban, per jail per time period. This outputs rows with the start of the time period and a numeric column per jail:
but this report doesn't auto-update as new log lines come in nor can I zoom in or out.
Describe the solution you'd like
I'd like to see support for one or more of the following features:
- A keyboard shortcut / command to rerun the query for the currently displayed dataset in the DB view.
- Toggle auto-requery support; this re-executes the query when the underlying log data has been updated. Alternatively, support for displaying a SQL table or view could be added (just a straight
select * from [table-or-view]) where inserts, deletes and updates trigger a view update. Re-casting an existing query as a view is easy enough, after all.
- A way to hook into the lnav histogram functionality (zooming in and out, optionally with support for the synchronizing the top time when switching views). This could take the form of additional SQL variables and column name conventions, where the query is re-executed whenever the zoom level changes. The variable value should be suitable for use in the
timeslice() function). Alternatively, this could be done by requiring that a query outputs data with a per-second aggregation (e.g. timeslice(log_time_msecs, '1s') AS log_time_msecs), which lnav then aggregates down into the displayed zoom level, without re-running the potentially slow original query.
Describe alternatives you've considered
I've considered using triggers on the target log table to react to inserts for new rows, however, this is a lot more complex and I haven't really figured out how to get lnav to update the db view for a query using triggers.
Additional context
If query performance is an issue developers could still use use triggers to maintain the dynamic data query as a separate table, like a poor-man's materialized view.
Is your feature request related to a problem? Please describe.
I love the power of exposing the data from logfiles to a SQL interface with the SQLite integration. However, once the query has been executed the result remains static. I have to re-execute the query to see updated results whenever new log lines have appeared, and I can't treat the results as a zoomable dataset.
E.g. I currently regurarly run a report query that summarises the number of ban actions executed by fail2ban, per jail per time period. This outputs rows with the start of the time period and a numeric column per jail:
but this report doesn't auto-update as new log lines come in nor can I zoom in or out.
Describe the solution you'd like
I'd like to see support for one or more of the following features:
select * from [table-or-view]) where inserts, deletes and updates trigger a view update. Re-casting an existing query as a view is easy enough, after all.timeslice()function). Alternatively, this could be done by requiring that a query outputs data with a per-second aggregation (e.g.timeslice(log_time_msecs, '1s') AS log_time_msecs), which lnav then aggregates down into the displayed zoom level, without re-running the potentially slow original query.Describe alternatives you've considered
I've considered using triggers on the target log table to react to inserts for new rows, however, this is a lot more complex and I haven't really figured out how to get lnav to update the db view for a query using triggers.
Additional context
If query performance is an issue developers could still use use triggers to maintain the dynamic data query as a separate table, like a poor-man's materialized view.