jakebathman / mysql-syntax Goto Github PK

When the mssql extension is enabled in Visual Studio Code, the language service provided by this extension is completely unavailable due to conflicting language IDs (both register "sql" but the mssql extension gets there first). I suggest following the lead of the doublefint.pgsql extension and us the ID "mysql" instead to work around this problem.

Tested with:

• Visual Studio Code 1.23.1
• mssql extension (ms-mssql.mssql) 1.3.1
• MySQL Syntax (jakebathman.mysql-syntax) 1.3.1

It is actually being recognized when typed in the "insert into" operation, but when the word is stand alone, it is not recognized:

The extension cannot detect and highlight MySQL while using mysql2.js with Node as an example below:
db.execute('SELECT * FROM products');

Having a space character in a column alias breaks the rest of the highlighting:

Example:

create table pers
	(	id				int unsigned		not null	primary key 	auto_increment
	,	inactive		tinyint unsigned	default 	0
	,	first_name		varchar(100)
	,	last_name		varchar(100)
	,	created		datetime			not null	default			current_timestamp
	);	

Example:

create table country
	(	id				smallint unsigned	not null	primary key		auto_increment
	,	name			varchar(100)		not null	
	,	iso_2			varchar(2)			not null
	,	iso_3			varchar(3)			not null
	,	unique key		ux_ctry_iso_2		(iso_2)
	,	unique key		ux_ctry_iso_3		(iso_3)
	,	unique key		ux_ctry_name		(name)
	);

https://twitter.com/peterettery/status/962595631842955264

For the longest time, I wasn't able to get proper mysql syntax highlighting and error checking. Just for fun, I uninstalled the mssql plugin and this plugin started working. I think it would be helpful for your users and potential users to point out that the mssql plugin must not be installed for this plugin to work. Thanks.

stand alone JOIN not highlitghting, also bad string syntax highlighting after AS statement.

However, the in the "on delete cascade" part of the foreign key clause, the "cascade" is correctly highlighted.

create table cust_pers
	(	cust_id			int unsigned            not null
	,	pers_id			int unsigned		not null
	,	primary key		(cust_id, pers_id)
	,	foreign key		(cust_id)			references	cust(id)		on delete cascade	on update cascade
	,	foreign key		(pers_id)			references 	pers(id)		on delete cascade	on update cascade
	);	

There is a text color glitch when intalled.

code example :
https://drive.google.com/file/d/1TKIV5qd0MecmDLlNy1xe5YpJ-yvNWXZR/view?usp=sharing

The comment syntax # is not highlighted in green, the comment color, as is the other alternate MySQL comment syntax forms of -- and /*...*/.

is there a reason why the keyword "JOIN" alone isnt highlighted ? i have to type INNER JOIN instead, but me (and probably other ppl) at not used to do so !

Problems highlighting single quote comment when the comment start with a space and a special word (eg. "as").

The MySQL functions TIMESTAMPDIFF and TIMESTAMPADD have only partial highlights

It seems like the TIMESTAMP type is superseding the function names.

Other functions which contain type names within them (such as char_length, current_timestamp, date_add, and date_sub) seem to be fine.

tested on Debian Rodete using VS Code 1.24.1 and MySQL Syntax v1.3.1

full VS Code info:

Version 1.24.1
Commit 24f62626b222e9a8313213fb64b10d741a326288
Date 2018-06-13T17:47:35.732Z
Shell 1.7.12
Renderer 58.0.3029.110
Node 7.9.0
Architecture x64

Only some escaped single and double quotes are being caught correctly.

"foo \"bar\" baz"
"foo \'bar\' baz"
'foo \"bar\" baz'
'foo \'bar\' baz'

If I activate mysql-syntax extension, the following code will consider the comment below and the echos as strings:

<?php
$sql = 'SELECT users.username as u1, users2.username as u2'
  .' users.username as u3, users2.username as u4';

// echo "sql:$sql\n"; // DEBUG INFO
echo "hello world";
echo "hello world";

However If I remove the as u2, this problem stops from occurring.
The code below has no problems:

<?php
$sql = 'SELECT users.username as u1, users2.username'
  .' users.username as u3, users2.username as u4';

// echo "sql:$sql\n"; // DEBUG INFO
echo "hello world";
echo "hello world";

mysql-syntax disabled:

mysql-syntax enabled:

mysql-syntax enabled but no as u2:

Example:

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

When using backticks to escape an AS clause containing spaces, the syntax hilighting is not handled properly. For example, with no spaces hilighting is as expected:

SELECT
    protocol_no AS `ProtocolNo`,
    principal_investigator AS `PrincipalInvestigator`,
    funding
FROM study;

But when the escaped string contains a space it breaks the syntax hilighting:

SELECT
    protocol_no AS `Protocol No`,
    principal_investigator AS `Principal Investigator`,
    funding
FROM study;

Here is an example PHP snippet...

<?php

$thing = 'SELECT as ';
$anotherThing = '';

I tried to boil it down to the minimum needed to reproduce the problem. It happens if in single or double quote (originally I noticed it in a double quoted SQL query that was much more complex, but boiled it down to the above parts). Take away the space at the end, or change SELECT to something else, it doesn't happen.

With the extension enabled, the above makes the syntax highlighting break for all the rest of the lines, so for instance $anotherThing is highlighted as if it is still part of the string.

I would imagine other combinations would also create this, it seems that for some reason it does not figure out that the end ' happened.

Here is something closer to the actual line that originally caused the problem:

$query = "SELECT thing as 'anotherthing'";
// ...

Just to point out something that might actually be found in the wild instead of the bare boiled down parts... Note that if ' around anotherthing is removed it works ok. Either way I think underlying problem is that it doesn't properly figure out that the string has ended...

The following line is showing to have syntax errors even though this is correct syntax for mysql:

CREATE DATABASE IF NOT EXISTS mydatabase;

Code:

UPDATE

smev_fk.smev_fk_charge as c
JOIN
smev_request.smev_req_fk_charge as r
on c.id = r.REQ_charge_id

It would be great if you could wrap things in backticks by selecting them and pressing `, just as you can do with single and double quotes.
I believe that would fit nicely in the scope of this extension.

I have this extension installed in the latest version of vs code. However when I open a .sql file, I'm greeted with MSSQL syntax highlighting:

If I click on MSSQL in the toolbar, no other options are available:

If I click on SQL in the toolbar, nothing helpful appears:

Hello
I have created a query and this is the sad result
I think much improvement is needed in the syntax highlighting
Especially for tables and columns names colors
But also as you can see, regex behaves weirdly, since it changes colors and also changes the color of the closing ")"
This query is valid and works perfectly

The keyword "restrict" is used in foreign key clauses, e.g.:

create table event
	(	id				bigint unsigned		not null	primary key 	auto_increment
	,	event_type_id	smallint 			not null
	,	foreign key		event_type_id		references event_type(id)	on delete restrict	on update restrict
	);

I wanted to see if this would change anything from the built-in syntax highlighting but none of the documentation / sites has an example of this code in action.