Context / Purpose
So...!
I'm a data analyst in an AWS Redshift SQL environment.
For context, the environments that analysts out in the field tend to use, in order of popularity are:
1 TSQL/ SQL Server
2 Oracle
3 PostgreSQL
4 Spreadsheet / Google Sheet
5 Scrap pieces of paper
6 Crystal Reports
7 Lotus Notes
.
.
.
40 Using an Abacus
41 Cave Drawings
42 Redshift AWS
So suffice to say finding Redshift SPECIFIC technology is pretty hard to find on the interwebs.
When I search for Redshift on the VS Code Extension Store there is nothing!!!!
Well that's a slight lie, there is one, but that's also a lie, its just tagged as Redshift because SEO....
Now this hasn't been an overly big problem for me.
Redshift is very similar to PostgreSQL and Oracle and so I've been getting by using Oracle based extensions.
However the Oracle Extension I've been using this whole time has been getting bigger and buggier over time.
The extension also does like 60 different things and I only use it for 2.
So seeming all I need is SQL syntax highlighting and a snippet library I thought I'd give it a shot at making my own syntax highlighter extension and I can ditch bloaty-mcbloat face.
(And I would just use the internal global snippets file)
Here are the lessons I learned along the way.
Lesson One: No one built something I could steal.
So I totally thought I could git-bash my way to success here.
I 100% thought I could take the Oracle Extension and rip out the bits I needed and would be done in time for dinner.
I was very wrong.
There are 2 ways to do syntax highlighting.
Method One requires you to declare what you are highlighting in a JSON package using REGEX expressions. This is what the VSCode documentation tells you.
Method Two simply requires you to download a TextMate Language pack and then nothing else. That’s it, you are done. No dev work what so ever.
Every single extension I looked at uses the TextMate Language pack method. Could I find a language pack for Redshift? Nope. Could I alter an existing one? Not that I could figure out.
So this was mental breakdown number one, everyone on git-hub had taken the easy way out because the languages they supported where mainstream enough to have an easy to grab language pack.
I felt like I was trying to find a case for my Motorola phone when all I can find are Samsung and Apple cases.
So I had to take option one and Dev something myself.
Lesson 2: It wasn’t as free form as I thought it was.
So syntax highlighting isn’t just a highlight everything that's not freetext. You could do that but I could also call all my variables VAR, it gets the job done but it's hardly efficient.
Good syntax highlighting makes functions different colors from variables. Comments different from strings.
Having different highlighted zones allows your eyes to snap to the bits of the code you are looking for.
I knew for SQL I wanted to highlight the following
Commands like 'select' and 'where'
Data types like 'varchar'
Single and double quoted Strings
Single and block comments
Functions like 'row_number' and 'upper'
Special words like distkey
Special characters like ‘<>’
Numbers
Active words like 'and', 'or', 'else'
By the time I had written out every object I wanted to highlight out of the AWS spec it was a very very long list, about 300 things.
Now I just assumed I would group these how I want and then apply the rules.
Again I was wrong...
Because Visual Studio Code can be themed so many ways there are already pre-set groups for you to match to. This makes the themers job easy as they just say what color a certain group will be and then it doesn’t matter which one of the 87547868545874 coding languages you are using, provided it’s matched to the predefined group it should change with the theme.
This meant I had to take my planned groups and smush them into the groups available to me.
This wasn’t so much as a disaster as I thought it would be, I ended up using ten groups which ends up close to what I originally wanted to do, its just not how I expected.
Lesson 3: It wasn’t as hard as I thought it would be.
I'm not a dev, I understand basic HTML / CSS and SQL and I've honed those skills into the floor. I'm like one of those wood people who can make fucking awesome letter boxes but don't ask me to make a chair or a fence.
I don't understand things like Javascript, or typescript or how these sort of things work. Setting out to make a VS code extension I watched YouTube videos and pulled gits and then cried for a solid 5 minutes every time. Do you know how hard it is to read ReadMe.md's with eyes full of tears???
But I sort of just kept bumbling my way forward and found that it was easily very doable at my skill level.
- For example you can use Yeoman, a NPM package to build a working shell of a VSCode extension which works and debugs right away.
- Then I matched what I wanted to highlight to the predefined groups outlined here
- Then used the provided JSON template
- Along with these Regex guidelines
- And this handy regex editor to check that I was doing it right.
Next minute I was compiling the extension and its now running on my work machine.
Git Hub Repo
ronsoak
/
vsc_redshift_extension
A Visual Studio Code Extension to support working in a Redshift (AWS) SQL Environment
Syntax Highlighter for Redshift
Simply put there is no Syntax Highlighting for Redshift AWS SQL in Visual Studio code So I made my own It aint flashy, it never will be.
Features
Syntax Highlighting for:
- SQL Commands like Select, Abort, Begin...
- SQL Keywords like Date_trunc, Distkey, lower...
- Single line comments
- Double line comments
- Single quote strings
- Double quote strings
- SQL Data types like BIGINT, BOOL, VARCHAR
- SQL Constants such as AND, BETWEEN, ILIKE
- Support for number highlighting
- Special Characters like =, <> , =!
Technical Information
Syntax highlighting is done by corresponding to textmate elements.
Keywords
Patterns matched to keyword.control:
ABORT, ALTER, ANALYZE, BEGIN, CALL, CANCEL, CLOSE, COMMENT, COMMIT, COPY CREATE, DEALLOCATE, DECLARE, DELETE, DROP, END, EXECUTE, EXPLAIN, FETCH, GRANT, INSERT, LOCK, PREPARE, RESET, REVOKE, ROLLBACK, SELECT, SET, SHOW, START, TRUNCATE, UNLOAD, UPDATE, VACUUM
Patterns matched to keyword.other:
ABS, ACOS, ADD_MONTHS, APPROXIMATE, PERCENTILE_DISC, ASIN, ATAN, ATAN2, AVG BIT_AND, BIT_OR, BIT-WISE,…
Who am I?
Ronsoak 🏳️🌈🇳🇿@ronsoak
Who am I? 🌈. Senior Data Analyst for Xero. Blogger dev.to/ronsoak - medium.com/@ronsoak . I draw sometimes instagram.com/ronsoak_art/ and I write Sci-Fi that no-one has seen yet. My tweets do not represent my company. 💬 Be Good and Be Good At it !00:30 AM - 02 Jun 2019
Top comments (13)
This post is great, really enjoyed how you shared your process! I’m sure a how-to VS Code Syntax Highlighter article written by you would be helpful to many devs. Thanks for sharing.
Hello
since I think you don't wanna fool anybody :-) I think there is a little mistake or typo
I mean
these two points at the very end of your post
are both linking the same identical link
Can you kindly fix that?
Thank you
Good Spotting! have rectified that mistake!
Really nice post, but you know what its missing? (also your repo is missing this) a fancy result screenshot. You don't have to go all r/*nixporn and make it a fancy screenshot of vs-code with a dope theme and added shadows on a transparent background (so that on the white github readme it really pops) or anything like that, just a screenshot of the fancy syntax coloring you've ended up with. (though if you wanted to do that, seems I accidentally gave you a blueprint)
Thanks for awesome post.
Add Installation guide and some screenshots
Hello! I'm back on this cool article
What about MySQL/SQL queries syntax color highlighting?
Did you implemented this?
Or do you have any valid (And working as expected) VSCode extension?
Thank you
as you can see, the unique supposed to be conceived for this purpose ... doesn't work
see this screenshot :-(((
ibb.co/Y2KdGYL
Thanks for writing this up and the extension. I don't see it in the Marketplace/Extension list in vs code though. Are you going to release it?
Nah the process to register it on the market place is a bit and I 100% only intended on making this for my team lol. Inside the github repo is the compiled v1 so you can download that and manually install it to your local VSCODE.
Great post! Awesome result after going down so many different paths
Well done, and thanks for sharing your journey.
Nice work, I often thought about doing something like this but it always ended up in the too hard basket!
I'm surprised anyone still knows it exists
Hello, this was an enjoyable read. As a techie, I was able to quickly get to where I needed to be after reading this. Thanks for going through the pain for us.