3 ms·
You could also go entirely into the matrix and write custom functions + wrappers for SQLite that are able to parse & pass-through shell commands. For instance:
by bob1029 6y ago
You could also go entirely into the matrix and write custom functions + wrappers for SQLite that are able to parse & pass-through shell commands. For instance:
./customsqlite "SELECT 1, 3, 5 FROM MyLogParser(Shell('tail -n 50 mylog.txt'))" | grep 'something really important'
This stuff is dangerously powerful. User/Application-defined functions in SQLite do not have to be free of side-effects. You could also do something like:
tail -n 50 mylog.txt | customsqlite "SELECT Shell('ping ' || ip.IpAddress) FROM ParseAsMyIpData(STDIN()) ip"
Where the following are custom UDFs:
- MyLogParser
- Shell
- ParseAsMyIpData
- STDIN
See: https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html
- deleted 6y ago[deleted]
- kbenson 6y agoThere are projects like fsql[1] that support this and make it easier in many respects too. SQL is a powerful syntax for tabular data. This is also one of the selling points of Microsoft's PowerShell (which I think you can also install on Linux?). 1: https://metacpan.org/pod/distribution/App-fsql/script/fsql https://metacpan.org/pod/distribution/App-fsql/script/fsql
- patrickdevivo 6y agoThis sounds similar to a proof-of-concept project I've been putting together for simila ruse cases: https://github.com/augmentable-dev/flite https://github.com/augmentable-dev/flite. Basically UDFs + virtual tables (table-valued functions) that are smaller helpers for using SQLite to slice and dice shell output. Still totally WIP, but I'm curious to play it out and see where it goes
- hnlmorg 6y agoThat's the same as what I've built except I've integrated it into the shell whereas you've integrated it into sqlite3. Take your README examples: -- flite SELECT * FROM lines("/path/to/some/file.ndjson") # murex open /path/to/some/file.ndjson | select * . -- flite SELECT readfile("/path/to/file.json") # murex open /path/to/file.json | select * . -- flite SELECT yaml_to_json("hello: world") # murex tout yaml "hello: world" | format json . -- flite SELECT json_to_yaml('{"hello":"world"}') # murex tout json {"hello":"world"} | format yaml What's happening here is the pipes are typed (rather than dumb byte streams) so subsequent commands, if they support _murex_ pipes as opposed to dumb POSIX pipes, are able to take advantage of a multitude of different marshallers without each command needing to worry the underlying file format of the content. `open`, as you might expect, opens the file and pushes the content to STDOUT. It's a little bit like `cat` except it pushes data type information down the pipe. It also differs a little from cat in that it can render different output depending on whether STDOUT is a TTY or a pipe. eg JSON can be formatted for human readability if it's a TTY or minimised for bandwidth if it's a pipe. The shell gets a little bit more clever here though in that images are rendered to the terminal if STDOUT is a TTY but pushed as a byte stream if it's a pipe. https://murex.rocks/docs/commands/open.html https://murex.rocks/docs/commands/open.html `tout` is `typed output`. So you're saying "echo this output and set the data type to ..." https://murex.rocks/docs/commands/tout.html https://murex.rocks/docs/commands/tout.html `select` is the sqlite3 connector https://murex.rocks/docs/optional/select.html https://murex.rocks/docs/optional/select.html `format`, as you might have guessed, is for changing the data type and data format (you can change the data type without the format by using `cast`). https://murex.rocks/docs/commands/format.html https://murex.rocks/docs/commands/format.html --- Looking through your code, it's pretty cool what you've written too. Also loving the clean readable code. Good work there.
- hnlmorg 6y agoThat's basically the same as what I'm showing off here. Except that the log parsing happens prior to the data being imported into sqlite3 but aside from that all the field names in the sqlite3 table are the field names in the log files. Because the log files are parsed prior to importing them, it also means the SQL you write in is cleaner and more readable. Not taking anything away from what you've exampled there though. That does look really cool too.