Simpler DebugKit Sql query out

[markstory]

From @cmann1 on June 11, 2015 13:2

Perhaps an option to output simpler queries could be helpful when debugging, currently if you open Sql Log tab your queries will be extremely verbose:

SELECT Languages.id AS `Languages__id`, Languages.name AS `Languages__name`, Languages.code AS `Languages__code`, Languages.iso_code AS `Languages__iso_code`, Languages.is_default AS `Languages__is_default`, Languages.deleted AS `Languages__deleted` FROM languages Languages WHERE deleted = 0 ORDER BY Languages.id desc LIMIT 20 OFFSET 0

All of the Table.col AS Table__col can be difficult to look through and, in my experience at least, most of the time is not needed.
Maybe the debug kit could format the query, stripping out unnecessary information such as the default AS statements.
The above statement would be a lot easier to read and still provide the same information if the SELECT part was just: Languages.id, Languages.name, ...

If you need to see the original full query maybe it could be shown when you hover over it?

Copied from original issue: cakephp/cakephp#6784

[markstory]

I don't really know if that is a good idea. SQL is non-trivial to parse and manipulate, and logging different SQL than was actually executed seems like it could cause as many problems as it solves.

[markstory]

From @ndm2 on June 11, 2015 20:28

I'd rather like to optionally see the queries in a highlighted, maybe even formatted fashion, or an easier way too hook into the output generation.

For now I've created a plugin with a custom panel extending SqlLogPanel, which grabs the DebugLog logger, and uses a copy of the sql_log_panel.ctp element, where the query output is being formatted using https://github.com/jdorn/sql-formatter.

While this works, it would be nice if something similar would be supported out of the box, or easier to achieve, ie without having to "reimplement" too much code, namely the element in this case.

[markstory]

Formatting the queries sounds like a good option.

[markstory]

I gave using jdorn/sql-formatter a spin. The end result looks like:

The syntax highlighting uses garish colours which don't look good with the rest of DebugKit.

[ndm2]

The colors can easily be customized, static properties aren't that nice tough.

Just for example purposes, especially the non-formatted variant would need something with better contrast.

SqlFormatter::$quote_attributes = 'style="color: #259B24;"';
SqlFormatter::$backtick_quote_attributes = 'style="color: #009688;"';
SqlFormatter::$number_attributes = 'style="color: #D76B6B;"';
SqlFormatter::$word_attributes = 'style="color: #8A7E57;"';
SqlFormatter::$pre_attributes = 'style="color: #594C20; background-color: white;"';

[lorenzo]

That looks great to me

[AD7six]

I like this too. I'm not sure yet but the whitespace changes are possibly more useful (to me) than the formatting changes.

Maybe the debug kit could format the query, stripping out unnecessary information such as the default AS statements.

Just in case: I definitely do not want anything more significant than whitepsace and formatting to be applied.

[markstory]

@ndm2 Cool, I didn't see the color options in the docs.

So do we want coloured output or just formatting?

[lorenzo]

I'm more interested in colors than formatting :P