MySQL ` (backtick) highlighting

[jord1e]

Hello,
I am trying to highlighting everything between backticks exactly like in MySQL Workbench:

Code

This is my style and example sql

# -*- coding: utf-8 -*-

from pygments.style import Style
from pygments.token import Keyword, Name, Comment, String, Error, \
    Number, Operator, Punctuation, Generic, Whitespace, Text, Literal, Punctuation


class WorkbenchStyle(Style):
    styles = {
        Whitespace: '#a89028',

        Text: '#000000',
        Punctuation: '#000000',

        Comment.Single: '#0987cb',
        Comment.Special: '#0987cb',
        Comment.Multiline: '#0987cb',
        Comment.Preproc: '#0987cb',

        Number.Hex: '#cc6c00',
        Number.Bin: '#cc6c00',
        Number.Float: '#cc6c00',
        Number.Integer: '#cc6c00',

        Literal.Date: '#cc6c00',

        String.Single: '#dd7a00',
        String.Double: '#dd7a00',
        String.Escape: '#dd7a00',

        Name: '#993a3e', # <<<<<<<<<<<<<
        Name.Variable: '#000000',
        Name.Constant: 'bold #007FBF',
        Name.Function: '#7d7d63',

        Operator: '#000000',

        Keyword: 'bold #007FBF',
        Keyword.Type: 'bold #007FBF',
    }

SELECT MIN(date) FROM medw
WHERE afd = (SELECT anr FROM afd WHERE name = 'Verkoop\%');
/**
abc
\%
*/
-- abc
# lollll lol
create user bob@localhost identified by 'Secure1pass!';
SELECT * FROM abc GROUP BY a;
use bobdb;
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4; ?
EXECUTE stmt1 USING @a, @b;
SELECT * FROM abc WHERE x = ' %s a' OR a = 0xA111 or c = 0b0011
AND x IS NULL OR c = true
AND f = '1983-09-05 13:28:00' OR `xbc`.`a` >= 567;
'x'
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1
PROCEDURE
MAX_EXECUTION_TIME(3)
CREATE TABLE IF NOT EXISTS tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    start_date DATE,
    due_date DATE,
    status TINYINT NOT NULL,
    priority TINYINT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)  ENGINE=INNODB;

I compile everything using

pygmentize -f html -l mysql -O style=workbench -O noclasses=True -o abc.html test.sql

My attempts at solving the issue

The problem is that Name: '#993a3e' makes everything red:

I Have tried solving it with noinherit, but alas

Backtick identification seems to be happening here, here or here. A solution would be appreciated

[Anteru]

@kurtmckee has been recently doing a lot of fantastic work on the MySQL lexer -- maybe he can help :)

[kurtmckee]

Yes, I can assist here. Root cause is that backtick-quoted and unquoted names share the same token type: Name. The only difference between them is that unquoted names have restrictions (like no spaces, cannot be a reserved keyword, etc), while quoted names have no restrictions. They are lexically identical, however. Matthäus, I don't recall that there is an existing Name.Quoted token type or anything similar. Is there precedent for creating a one-off token type? If the new token type isn't recognized by a formatter do they trace up the token hierarchy to find something they *can* format? For example, if I create a Name.Awesome.Quoted token, would a formatter search for that, then up the token hierarchy for Name.Awesome and then up to Name? If this is automatic perhaps a custom sub-token could be created without breaking other formatters and styles, and perhaps the user can add formatter and style support for the custom token type. However, this delves into parts of Pygments I haven't touched yet so I'll need an expert opinion on the matter.

[jord1e]

Since it's very MySQL specific we could use one of the predefined tokens (e.g. Name.Attribute) as to not break backwards compatibility.

autumn and trac styles for example already have an entry for this. We should probably check for keywords between backticks though, as to not highlight them.

The biggest problem then is choosing between the different predefined tokens (Name.Entity, Name.Attribute, Name.Namsepace etc.), maybe @Anteru knows the best one?

The formatter does seem to inherit from parents (hence the noinherit style attribute exists), see here.

Edit: String.Backtick exists, but that seems to be for strings and not names, thoughts?

[kurtmckee]

@Anteru, I have a serious question for you below. If you can help answer this it will allow me to both improve the MySQL lexer as well as to resolve @jord1e's need for a unique token type for quoted schema object names.

@jord1e, the MySQL lexer does its best to correctly tokenize all of the input. Quoted and unquoted schema object names have no semantic difference that I'm aware of in MySQL so in both cases they are tokenized as "Name". If the token type is changed for quoted schema object names it will introduce a new semantic distinction between quoted and unquoted names. If that's the case, the semantic distinction still needs to be meaningful. Name.Attribute would be unique, but it wouldn't be meaningful. Neither would String.Backtick.

I actually wanted to use a custom type for quoted schema object names. I seriously considered creating the Name.Quoted token that I mentioned above. It would have also allowed me to introduce a Name.Quoted.Escape sub-token so that I could even tokenize escape sequences in quoted schema object names. But I didn't, and it was because I anticipated it would have unforeseen consequences.

I had thought that if I introduced custom token types then I would have to add new CSS class names in token.py. I would have to modify all of the existing color schemes to at least map the new custom token types to the existing Name token color. Then I would have to confirm that none of the formatters that I don't use or understand, like LaTeX, didn't break because I overlooked something.

My big question for @Anteru is: What actually has to happen if I introduce a custom token type? I anticipated this apocalyptic scenario where I would have to touch 20 to 30 files, but now that I'm checking some of the formatters it appears that they do exactly what I was hoping: if the formatter doesn't recognize the token type then it follows the token hierarchy until it finds a token type that it recognizes (like in html.py or latex.py). This is encouraging but I would really like some guidance here.

I think that my original goal to uniquely tokenize quoted schema object names, as well as the fate of this ticket, primarily hinges on creating custom types.

[birkenfeld]

@kurtmckee don't forget that practicality beats purity. The token type names assigned by Pygments don't necessarily have to match the semantic meaning assigned by the language - they're not used by a parser. Usually it's a good idea since similar things will have a similar color for different lexers, but for the use case here it's much easier to use some other token type that already has useful assigned attributes in the various styles.

I don't think it makes sense to introduce new styling definitions in all style classes for a token type that exclusively appears in the MySQL lexer.

[Anteru]

I haven't checked yet if the class falls back to the next item in the hierarchy, but it would make sense. @birkenfeld Do you know? Does Name.Foo try Name.Foo, and fall back to Name?

[birkenfeld]

That's the idea, yes.

[Anteru]

In which case @kurtmckee I think your question is answered. If we spot a formatter down the line which doesn't behave like this it'll be considered a bug and fixed. If you can get away with some other pre-existing token that's probably the easiest solution though.

[kurtmckee]

Great! I'll explore a custom token for this situation, test before-and-after output for the HTML formatter and the LaTeX formatter, and make sure that the unit tests are in place to validate the behavior. If all goes well then I'll reply back to this with instructions for taking advantage of the new functionality once it's merged. I may be able to work on this over the weekend.

…

On September 24, 2020 8:24:43 PM UTC, "Matthäus G. Chajdas" ***@***.***> wrote: In which case @kurtmckee I think your question is answered. If we spot a formatter down the line which doesn't behave like this it'll be considered a bug and fixed. If you can get away with some other pre-existing token that's probably the easiest solution though. -- You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub: #1551 (comment)

[kurtmckee]

@jord1e, I've created a pull request to fix this. I have also fixed two bugs in the lexer that I overlooked previously, involving escape characters in quoted schema object names as well as unquoted schema object names that start with leading digits.

I successfully tested adding the new token names in the "friendly" scheme. You will need to add "Name.Quoted" and whatever color definition you want. If you want to highlight escape sequences in quoted schema object names, add "Name.Quoted.Escape" with a custom color definition.

Please note that you won't be able to get 100% highlighting parity with MySQL Workbench. For example, Workbench incorrectly highlights schema object names with leading digits, and I've fixed this problem in Pygments with the same PR: