Sql results missing when script is invoked from keybind

[ConorMurphy21]

trafficstars

In a script called test.lnav

;SELECT * FROM (VALUES (1,2), (3,4), (5,6))
:toggle-view db

If the script is invoked via cmd line by typing |test then the db view will be populated with 1..6 and the view will toggle to db view.

However if this script is invoked by pressing a key that is bound to running the script:

"x6f": {
    "__keypress": "o"
    "command": "|test"
 }

The db view will not be populated.

[tstack]

Yes, this is somewhat intentional since I didn't want SQL operations that were used in implementing hotkeys to clobber the contents of the DB view. But, yes, there needs to be a way direct where query output goes.

[ConorMurphy21]

Ah yeah that makes sense. For some reason I was thinking that the way it worked was that if the last line of a script was a query that returned multiple rows then it would populate the db, otherwise it would not (so all of the intermediate queries wouldn't clobber the db view). But yeah I can see that getting iffy.

Were you thinking something like :display-table or :write-table-to Or were you considering exposing a dbline vtable that can be inserted into and also queried from?

[tstack]

Were you thinking something like :display-table or :write-table-to

Yes, probably a command like this to specify where results are directed to. I'll think about it a bit.

[ConorMurphy21]

Not sure if this helps your decision on this but I thought I'd add that scripts that depend on the currently selected line in the db view are fairly restricted to using :pipe-line-to and parsing that. So if a dbline was feasible it might open up a lot of avenues for custom scripts.

I can understand it still might be a bit of a pain from scripting perspective because presumably the table schema of dbline would change every query with the fields being whatever was selected.

And maybe :pipe-line-to is good enough given maybe a very niche use-case.

[tstack]

Not sure if this helps your decision on this but I thought I'd add that scripts that depend on the currently selected line in the db view are fairly restricted to using :pipe-line-to and parsing that.

Can you go into a little more detail here? You mentioned opening a file from a list as one use case, do you have some others in mind?

So if a dbline was feasible it might open up a lot of avenues for custom scripts.

I've thought about dumping the query results into a SQLite table instead of storing them internally. But, I just got done redoing the guts of the DB view to use a purpose-built data structure that is compact/performant and serves the needs of the view pretty directly. So, I don't want to ditch that work.

One alternative you could use would be to create a separate table that holds the extra information that you need to do the operations you want. Then you don't have to parse the display contents. You can get the selection from lnav_views table and use that value to query the secondary table.

---

Another alternative I would suggest investigating is to have the scripts output markdown. That gives you a lot more control of the display and you can use links to open stuff and execute commands. The tutorial is built around markdown and SQLite triggers. The user can then just click a link instead of having to remember a hotkey.

As an example, in v0.12.4, I added this report-access-log script that generates a markdown file that has links that can be used to run queries. The links refer to properties in the YAML front matter. The front matter has the operations that to execute.

So, I could imagine implementing the script that opens source files by generating some markdown with either links to the files themselves or links that run :sh commands to open the files.

If you want to share some of your use cases, I could help prototyping scripts to do what you want (and make sure lnav actually works correctly).

[ConorMurphy21]

I think the main script I was thinking about was the listing files, that can be opened one. I assumed that there may be other use cases for this but it definitely could just be this 1 niche application. I can think of other similar scripts I might want but those might be better suited for the md link way. ( I think I like the files in db view more because I can have different columns for package and line-number ect ).

One alternative you could use would be to create a separate table that holds the extra information that you need to do the operations you want. Then you don't have to parse the display contents. You can get the selection from lnav_views table and use that value to query the secondary table.

This is what I've ended up going with so far, but the downside of this approach is that if someone wanted to query the result table to, for example, only show paths in a couple specific packages, selection would no longer give an accurate key to the index of the result table. I think this is a minor issue though and I'm willing to accept the inaccuracy in a pretty niche use case.

As an example, in v0.12.4, I added this report-access-log script that generates a markdown file that has links that can be used to run queries. The links refer to properties in the YAML front matter. The front matter has the operations that to execute.

Ty for this I'll keep this in mind when considering scripts that require user-input in the future!