Support PL/SQL (Oracle) syntax

[daagar]

Would be cool to see this work with the PL/SQL syntax highlighter in addition to regular SQL.

[DavidLGoldberg]

Ah, true. I haven't worked with that in a while.

Have you used PL/SQL in TextMate/Sublime/Atom at all? How do they syntax highlight?

Atom might have another "language" (grammar).

Ah, so an apm search yields:
$ apm search pl
Search Results For 'pl' (7)
├── language-oracle Oracle PL/SQL language support in Atom (585 downloads, 4 stars)
├── autocomplete-oracle Code completions for Oracle PL/SQL (92 downloads, 0 stars)
├── language-pgsql PL/PgSQL language support in Atom (1413 downloads, 11 stars)

I'm not even sure which is the most official / real with those few downloads / stars.

Goes to show, it's going to be hard to get adoption for this package, as most people really aren't doing SQL in the editor... Although with Atom, there's some good opportunity to build query runners etc. so hopefully eventually there will be more momentum!

[daagar]

No problem, was just pointing it out since as you said, it is a lesser known set of plugins. I was specifically referring to the Oracle PL/SQL language support, but the Postgres PL/PgSQL plugin would probably be just as worthwhile to support. I don't believe the 'autocomplete-oracle' plugin would be of impact, as that is purely code-completion and not syntax highlighting.

Neither of the two is more official, they are targeting different database languages.

The hope, of course, was it was something that could just be 'turned on'. I'm not terribly surprised it isn't quite that simple :)

[DavidLGoldberg]

Well, I originally thought PL/Pg meant oracle and PostgreSQL, oops, didn't realize it was the official name of Postgre's

Not much info on the readme :
https://atom.io/packages/language-pgsql

Talks about how similar it is:
http://www.postgresql.org/docs/7.4/static/plpgsql-porting.html
...but yeah the grammar probably does not currently handle both.

[moberemk]

I know this is an old issue but is there any chance that this plugin could be set up to work with the Postgres language plugin anytime soon? As discussed above the syntax differences aren't too severe so it should just work out of the box right?

[DavidLGoldberg]

Thanks so much for the interest!

I haven't worked on Postgres in years (I know it's pretty much the hotness).

BTW This package is mostly for DML (at the moment)... I have an issue to add more coloring for DDL, although it's less useful since the major use case was of course to decipher a giant web of joins...

Curious: What happens if you take one of those queries and cmd+shift+l -> sql (to set your grammar to language-sql) ......Isn't it mostly ANSII SQL?

Or are you referring to all of the DDL aspects?

Also, even if it works, of course, it'd be nice to have it automatically work.

Believe it or not. It might be a one word change (adding language-pgsql to an array) IF stuff works... If it drastically breaks stuff, I wouldn't want to make this the default code case.

Can you do a little investigating with the above grammar change?

Also, I can add in option to add language-pgsql to the array, for opt in (in case stuff breaks).

I'm looking at Qolor tonight anyway (almost done with #10 ), so I'll try setting the grammar in my tests as well and just seeing what happens!!

[DavidLGoldberg]

Depending on whether or not language-pgsql is a complete rewrite of language-sql or a fork...

If it's a fork, we might get lucky, if not, there's some base code that could probably be repurposed, but there is a LOT of code to handle really weird nuances of language-sql (too bad there is no REAL nice sql parser in npm.. because if there was, I wouldn't mind slowing down qolor running it really out of band with a nice debounce etc...)

[moberemk]

They're very similar for the most part; the only real issues are in the way that Atom in plain-SQL mode fails to recognize some more Postgres-specific features such as CTEs and some keywords. Based on how straightforward the Postgres language definition is it might actually just work out of the box?

[DavidLGoldberg]

I've created a pull request (link right above), I tried the basic first steps last night. As I suspected the grammars are different enough that it doesn't just work "for free". That said, I was able to get table highlighting, without aliases, so it isn't too far off. That alone is sometimes useful, but I'd rather not send a conflicting message to users who are using both contexts (one with working aliases)

Do you do any atom package dev by any chance?

As I don't use pgsql much, I don't think I will work too hard on it, but I might.

Also, I'll keep the branch / PR open for anyone who wants a head start.

From what I can tell so far, the https://atom.io/packages/language-pgsql is very good but with only about 1700 downloads at the moment, I don't think supporting it is super crucial.

Keeping this issue open to try and recreate the above steps with PL/SQL grammars, as it might just work better "for free" (the grammar might be a fork closer to language-sql).

[moberemk]

Hmm, interesting; I wonder if it might be easier to just add the Postgres language keywords to the core SQL language itself in that case?

[DavidLGoldberg]

Yes, but that might be beyond the scope of the language-sql repo.

Maybe that's all the language-pgsql should have been. I'm actually still no expert in the grammars. They're real nasty, but a lot of smart people have looked at them throughout the editors. Same idea / grammars are inherited from TextMate -> SublimeText -> Atom. I haven't begun to try to look at the language-oracle.

What i'm trying to understand is, and haven't played with yet is, what syntax do you need from language-pgsql or even the oracle equivalent?

If it were so important I think a lot more than 1700 people or 3300 (oracle) would have downloaded it.

What happens if you open your postgresql files in language-sql? Don't they work fine as they are mostly ANSII SQL right? Is it the DDL that's different? How much better is it really? Do you spend a lot of your day doing DDL or DML like me (most of us?)

If it's one thing this regex pipeline/chain of responsibility esque grammar pattern has going for it is if they're done right they're very forgiving.

Maybe you haven't tried in a while? Maybe try going to Packages -> language-pgsql and disabling it? I'm just curious what your findings are. I don't have good sample pgsql SQL to sample from at the moment.

[moberemk]

They're very similar yes, but there's some syntax features in more complex queries that fail such as CTEs, most Postgres, and a handful of newer JSON operators. It does work for the more common parts of SQL syntax without issue though, but it fails at some of the more elaborate queries I have to run.

[DavidLGoldberg]

OK. Makes sense. We do a lot of MS SqlServer / transact code at work..

We do CTE's and a lot of crazy stuff too but MOSTLY, language-sql works, or at least mostly...

Because I haven't attacked CTE's for Qolor yet I haven't inspected how language-sql handles them with the sql server code we run. I know they've been in files I've used and qolor succesfully covers everything around it... So I imagine it's not handling them that bad. Also, another user requested I support CTEs, and is using language-sql / qolor just fine.... I wish the author of language-pgsql showed a side by side comparison of that package's output to the same query in language-sql