Pretty print of sql queries

[doubleloop]

Did you think of adding sql queries support to pretty-print view? It would be awesome.

--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/33345069-pretty-print-of-sql-queries?utm_campaign=plugin&utm_content=tracker%2F449456&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F449456&utm_medium=issues&utm_source=github).

[tstack]

Can you give an example of the formatting you would like? It's a little difficult to do since we'd probably have to parse/understand SQL, whereas the current logic is based around matching curly braces, square brackets, and parentheses.

[doubleloop]

Example tools witch do that:

• https://github.com/andialbrecht/sqlparse (python)
• https://github.com/hoterran/sqlparser (c)
• https://github.com/jdorn/sql-formatter (php)

So for example:

{"component_name":"gohan.db.sql","log_level":"DEBUG","log_type":"log","msg":"Executing SQL query 'SELECT events.`version` as `events__version`, events.`id` as `events__id`, events.`path` as `events__path`, events.`timestamp` as `events__timestamp`, events.`type` as `events__type`, events.`marked_for_deletion` as `events__marked_for_deletion`, events.`body` as `events__body` FROM `events` ORDER BY events.`id` asc LIMIT 10000'","timestamp":"2016-04-24T16:54:20.646599368Z"}

the msg could be formatted like

2016-04-24T16:54:20.646 gohan.db.sql DEBUG Executing SQL query '
SELECT events.`version` AS `events__version`,
       events.`id` AS `events__id`,
       events.`path` AS `events__path`,
       events.`timestamp` AS `events__timestamp`,
       events.`type` AS `events__type`,
       events.`marked_for_deletion` AS `events__marked_for_deletion`,
       events.`body` AS `events__body`
FROM `events`
ORDER BY events.`id` ASC LIMIT 10000'

Some simple rules for select would be:

• newline before keywords SELLECT, FROM, ORDER BY, WHERE, LIMIT,
• , as line separator after SELLECT and before FROM,
• ; finishes a query

[tstack]

I think the problem for me is that SQL syntax is pretty complex, you can have nested queries and a fair amount of other things going on. So, I would be afraid that there would be a lot of false positives if I were to start doing indenting when 'FROM' was seen.

[doubleloop]

It is true that this may be not the best idea in general. Actually after some thought, maybe it is better to enable some way of customization/extensibility. For example one could add something like this to log format definition:

custom_pprints: [{
    "msg_pattern": "\\'SELLECT\\.*\\'$",
    "module": "gohan.db.sql",
    "pprint_cmd": "sqlformat -r -",
    "skip_builtin_pprint": "false"   
}]

In this example, as e result, after you go to pretty print mode, log messages are searched for regexp pattern (or whole log msg is taken if msg_pattern is not specified), and then passed to external tool/script, then the result is taken.

So for log entry:

{"component_name":"gohan.db.sql","log_level":"DEBUG","log_type":"log","msg":"Executing SQL query 'SELECT events.`version` as `events__version`, events.`id` as `events__id`, events.`path` as `events__path`, events.`timestamp` as `events__timestamp`, events.`type` as `events__type`, events.`marked_for_deletion` as `events__marked_for_deletion`, events.`body` as `events__body` FROM `events` ORDER BY events.`id` asc LIMIT 10000'","timestamp":"2016-04-24T16:54:20.646599368Z"}

the pretty print could retrieve result by executing external python tool in a way equal to:

echo 'SELECT events.`version` as `events__version`, events.`id` as `events__id`, events.`path` as `events__path`, events.`timestamp` as `events__timestamp`, events.`type` as `events__type`, events.`marked_for_deletion` as `events__marked_for_deletion`, events.`body` as `events__body` FROM `events` ORDER BY events.`id` asc LIMIT 10000' | sqlformat -r -

This way, one can define own formatters which is pretty cool. In the example, module could be used for narrowing where to apply ppring_cmd (all by default), and skip_builtin_pprint could be something that if enabled skips all internal lnav pprint (if disabled, default pprints for json/brackets may still be used on parts of message witch did not match the msg_pattern)

What do you think about the idea?

[ostrolucky]

This is possible in user space now (in quite limited fashion and with side effects) through regex replace like so

"context": {
  "kind": "json",
  "rewriter": ";SELECT REGEXP_REPLACE(:context, '\\b((FROM|WHERE|GROUP BY|ORDER BY|HAVING|INSERT INTO|VALUES|UPDATE|SET|JOIN|ON|AND|OR)\\b|\\))', \"\n\\1\")"
},

[ostrolucky]

Here's what I've settled on:

:pipe-line-to jq -n --raw-output '$ENV.log_raw_text|fromjson.context.sql' | sleek | source-highlight --src-lang=sql --out-format=esc

You'll need

1. Ensure your lnav version contains this commit https://github.com/tstack/lnav/commit/96e69508849aa8ea5cf47d8fd88e4d0a92dd294c
2. Install https://jqlang.github.io/jq/ (JSON parsing)
3. Install https://github.com/nrempel/sleek (this is for SQL formatting)
4. Install https://www.gnu.org/software/src-highlite/ (this is for colorizing the output)
5. Assign shortcut for executing this (optional): Execute in lnav :config /ui/keymap-defs/default/x27/command :pipe-line-to jq -n --raw-output '$ENV.log_raw_text|fromjson.context.sql' | sleek | source-highlight --src-lang=sql --out-format=esc (replace \x27 with your desired key)

Result