Extend SQL dialect?

Hi,

In our projects we use templates for SQL queries, which are basically using the same syntax as the Oracle dialect in the SQL plugin, with an additional syntax for parameters.

For example:

select u.*
from user u
where u.login=#MY_USER.LOGIN#


Parameters are surrounded with "#", so here we retrieve the field named "login" from the named parameter "my_user". The Oracle dialect does not like such syntax and highlights the first underscore with the following message:         '(' or '[' expected, got '_'

Since I don't have access to the source code, I can only make assumptions, so here they are. I think it wouldn't be necessary to modify the parser, but only the lexer so that it recognizes my delimited parameters as SQL_IDENT/SQL_IDENT_DELIMITED, right?
I don't feel like hacking the generated lexer, and I don't have the original .flex file to modify it :(

What would be the best way to achieve this?

Thanks
Bastien

7 comments
Comment actions Permalink

Any SQL dialect uses generated + handwritten lexers approach.
So you can hack OracleLexer or even SqlLexer class instead. ;-)

I've added "." as allowed character so your case will be covered.
Expect the fix in the upcoming 12.0.2 EAP later this week.

0
Comment actions Permalink

Thanks Gregory. I extended OracleLexer and added a method similar to lookCustomParameter(). I must say it was a bit challenging without any doc/source code, but I managed to get it working.
Now I hope that since the code is not part of the OpenAPI, it won't change very often, otherwise I'll have to modify my plugin...

0
Comment actions Permalink

Bastien, would you mind sharing your code?  I am looking to do something similar for Anorm which uses named parameters delinated with {}.

0
Comment actions Permalink

Here you go: I have overriden the default OracleLexer to do custom lookaheads for parameters within $, % or #. The content of lookKasperParameter is based on the original code in OracleLexer (haven't tested it in IntelliJ 13 though).

 
package org.intellij.plugins.kasper.parser.sql;

import com.intellij.lexer.Lexer;
import com.intellij.sql.dialects.oracle.OracleLexer;
import com.intellij.sql.psi.SqlTokens;

public class KOracleLexer extends OracleLexer {

    @Override
    protected void lookAhead(Lexer baseLexer) {
        if (!lookKasperParameter(baseLexer)) {
            super.lookAhead(baseLexer);
        }
    }

    protected boolean lookKasperParameter(Lexer baseLexer) {
        CharSequence text = baseLexer.getBufferSequence();
        int tokenStart = baseLexer.getTokenStart();
        int len = baseLexer.getBufferEnd();
        if ((len > tokenStart) && (
                (text.charAt(tokenStart) == '#') || (text.charAt(tokenStart) == '$') || (text.charAt(tokenStart) == '%') || (text.charAt(tokenStart) == '@'))) {
            int stringStart = tokenStart + 1;
            String endDelimiter = String.valueOf(text.subSequence(tokenStart, tokenStart + 1));
            return processCustomQuotation(baseLexer, endDelimiter, stringStart, true, SqlTokens.SQL_CUSTOM_PARAM_LQUOTE, SqlTokens.SQL_CUSTOM_PARAM_RQUOTE, SqlTokens.SQL_IDENT);
        }

        return false;
    }
}


This lexer is used by a custom parser definition:

 
package org.intellij.plugins.kasper.parser.sql;

import com.intellij.lexer.Lexer;
import com.intellij.openapi.project.Project;
import com.intellij.psi.tree.IFileElementType;
import com.intellij.sql.dialects.oracle.OracleParserDefinition;
import org.jetbrains.annotations.NotNull;

public class KOracleSqlParserDefinition extends OracleParserDefinition {
    @NotNull
    @Override
    public Lexer createLexer(Project project) {
        return new KOracleLexer();
    }

    @Override
    public IFileElementType getFileNodeType() {
        return KOracleDialect.ORACLE_SQL_FILE;
    }
}


(KOracleDialect does nothing in particular except returning a custom dialect name, everything else is the same as in OracleDialect.INSTANCE).

Hope it helps.
Bastien

0
Comment actions Permalink

Thanks, Bastien.  I should be able to make it work from your examples, but I can't find the classes you're extending anywhere.  I thought it would be easier, but this plugin may be over my head :8}.

0
Comment actions Permalink

Oh, sorrry, I forgot to mention that I also modified plugin.xml to add extra dependencies to database libraries:

        <depends>com.intellij.modules.lang</depends>
        <depends optional="true">com.intellij.sql</depends>
        <depends optional="true">com.intellij.persistence.database</depends>
        <depends optional="true">com.intellij.javaee</depends>


IIRC IntelliJ will automatically add the corresponding jars to the classpath, and you will be able to override the needed classes. Keep trying, plugins can be hard to write but so much enjoying once they work ;)

0
Comment actions Permalink

Don't I still need the jars from somewhere to be able to make it?

0

Please sign in to leave a comment.