1. Introduction
The SQL Query Parser (also
referred to as ‘parser’ in this document) is a generated parser. LPG (Lexer Parser Generator) is used to generate the parser,
which, based on a set of grammar rules, generates the Parser and Lexer source code in Java. The focus of this document is on
how to use the generated parser.
The parser takes SQL DML
statements as input and creates instances of SQL Query Model classes if the SQL
is syntactically valid. In addition to the syntactic validation, the parser can
also perform a semantic validation. The parser is extensible to support vendor
specific dialects and custom source generation.
2. Usage
// imports needed
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.eclipse.datatools.modelbase.sql.datatypes.DataType;
import
org.eclipse.datatools.modelbase.sql.query.QueryStatement;
import
org.eclipse.datatools.modelbase.sql.query.ValueExpressionColumn;
import
org.eclipse.datatools.modelbase.sql.query.helper.StatementHelper;
import
org.eclipse.datatools.modelbase.sql.query.util.SQLQuerySourceFormat;
import org.eclipse.datatools.modelbase.sql.schema.Database;
import
org.eclipse.datatools.sqltools.parsers.sql.SQLParseErrorInfo;
import org.eclipse.datatools.sqltools.parsers.sql.SQLParserException;
import
org.eclipse.datatools.sqltools.parsers.sql.SQLParserInternalException;
import
org.eclipse.datatools.sqltools.parsers.sql.postparse.PostParseProcessor;
import
org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParseResult;
import
org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParserManager;
import
org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParserManagerProvider;
import
org.eclipse.datatools.sqltools.parsers.sql.query.postparse.DataTypeResolver;
import
org.eclipse.datatools.sqltools.parsers.sql.query.postparse.TableReferenceResolver;
2.1
Invoking the parser
This code snippet below shows how to use the parser without
using the semantic validation (known as post parse processing) and source formatting.
try
{
//
Create an instance the Parser Manager
//
SQLQueryParserManagerProvider.getInstance().getParserManager
//
returns the best compliant SQLQueryParserManager
//
supporting the SQL dialect of the database described by the given
//
database product information. In the code below null is passed for both the
database and version
//
in which case a generic parser is returned
SQLQueryParserManager
parserManager = SQLQueryParserManagerProvider
.getInstance().getParserManager(null,
null);
//Sample
query
String
sql = "SELECT * FROM TABLE1";
//Parse
SQLQueryParseResult
parseResult = parserManager.parseQuery(sql);
//
Get the Query Model object from the result
QueryStatement
resultObject = parseResult.getQueryStatement();
//
Get the SQL text
String
parsedSQL = resultObject.getSQL();
System.out.println(parsedSQL);
}
catch (SQLParserException spe) {
//
handle the syntax error
System.out.println(spe.getMessage());
List
syntacticErrors = spe.getErrorInfoList();
Iterator
itr = syntacticErrors.iterator();
while
(itr.hasNext()) {
SQLParseErrorInfo
errorInfo = (SQLParseErrorInfo) itr.next();
//
Example usage of the SQLParseErrorInfo object
//
the error message
String
errorMessage = errorInfo.getParserErrorMessage();
//
the line numbers of error
int
errorLine = errorInfo.getLineNumberStart();
int
errorColumn = errorInfo.getColumnNumberStart();
}
}
catch (SQLParserInternalException spie) {
//
handle the exception
System.out.println(spie.getMessage());
}
2.2
Getting a parser manager for a specific vendor
The recommended way is to instantiate a parser based on the
database vendor information so as to handle vendor specific variations of SQL.
The following code snippet shows how to create the parser based on the vendor
information
//
TODO initialize the variable db with the
//
org.eclipse.wst.rdb.internal.models.sql.schema.Database intance obtained from
//
the database connection
//
Database db;
//
TODO get the vendorname and version
after the variable is initialized
//
String dbName = db.getVendor();
//
String dbVersion = db.getVersion();
//
get the best matching parser manager depending on what parser extension are
plugged in
SQLQueryParserManager
parserManager = SQLQueryParserManagerProvider
.getInstance().getParserManager(dbName,
dbVersion);
If not matching parser is found, then the generic parser is
retuned.
2.3
Using post parse processing and source formatting
The semantic validation of the parsed
SQL is done in a step called the Post Parse Processing.
At present there are two types of
semantic validations are supported.
- Table reference resolving – verifying that the
tables referred to in the SQL are existing in the database
- Data type resolving – verifying the data types
of the result columns
Appending the schema name to the
database object (for example schema1.table1) is optional, when the parser
generates the SQL after a successful parse.
The following code snippet shows how to
configure post parse processors and the source formatting for the parser.
//
TODO initialize the variable db with the
//
org.eclipse.wst.rdb.internal.models.sql.schema.Database intance obtained from
//
the database connection
//
initialize the variable currentSchemaName to be the schema name
//
Database database;
//
String currentSchemaName;
//
String dbName = db.getVendor();
//
String dbVersion = db.getVersion();
String
sql = "SELECT * FROM TABLE1";
//
create the post parse processors, maybe wrapped in createPostParseProcessorList()
PostParseProcessor tableRefResolver
= new TableReferenceResolver(
database,
currentSchemaName);
PostParseProcessor dataTypeResolver
= new DataTypeResolver();
//
ordering is important for post parse processing! first we need to fill
//
in the database information for table references and column types
List
postParseProcessors = new ArrayList();
postParseProcessors.add(0, tableRefResolver);
postParseProcessors.add(1, dataTypeResolver);
//
get the SQL source format options and set at least the current schema
//
that is omited but implicit for any unqualified table
references
//
important for later resolving of table references!
SQLQuerySourceFormat sourceFormat
= SQLQuerySourceFormat
.copyDefaultFormat();
sourceFormat.setOmitSchema(currentSchemaName);
SQLQueryParserManager
parserManager = SQLQueryParserManagerProvider
.getInstance().getParserManager(dbName,
dbVersion);
parserManager.configParser(sourceFormat,
postParseProcessors);
//
parse the SQL statement
try
{
SQLQueryParseResult
parseResult = parserManager.parseQuery(sql);
List
semanticErrors = parseResult.getErrorList();
Iterator
itr = semanticErrors.iterator();
while
(itr.hasNext()) {
SQLParseErrorInfo
errorInfo = (SQLParseErrorInfo) itr.next();
//
the error message
String
errorMessage = errorInfo.getParserErrorMessage();
//
the line numbers of error
int
errorLine = errorInfo.getLineNumberStart();
int
errorColumn = errorInfo.getColumnNumberStart();
//
Error processing for specific errors
String
errorCode = errorInfo.getErrorCode();
if
(TableReferenceResolver.ERROR_CODE_TABLE_UNRESOLVED
.equals(errorCode)) {
//
table not found
}
else if (TableReferenceResolver.ERROR_CODE_COLUMN_UNRESOLVED
.equals(errorCode)
||
TableReferenceResolver.ERROR_CODE_NONEXISTENT_COLUMN
.equals(errorCode)) {
//
column not found
}
}
}
catch (SQLParserException spe) {
//
handle the syntax exception
System.out.println(spe.getMessage());
}
catch (SQLParserInternalException spie) {
//
handle the exception
System.out.println(spie.getMessage());
}
2.4
Getting result columns
//
the effective result columns of a QuerySelectStatement
are the columns that are returned by
//
the select statement
//
e.g.: SELECT SUBQRY.* FROM (SELECT
COL1, COL2 FROM TABLEA) AS SUBQRY
//
QueryStatement queryStmt =
parserManager.parseQuery("SELECT SUBQRY.* FROM (SELECT COL1, COL2 FROM
TABLEA) AS SUBQRY");
// get the List of effective result
columns with data types
List
columnExprList = StatementHelper
.getEffectivesResultColumns(queryStmt);
Iterator
columnIt = columnExprList.iterator();
while
(columnIt.hasNext()) {
ValueExpressionColumn colExpr = (ValueExpressionColumn) columnIt
.next();
DataType dataType = colExpr.getDataType();
System.out.println("effective
result column: " + colExpr.getName()
+
" with data type: " + dataType.getPrimitiveType());
}
//
expected Output of example "SELECT SUBQRY.* FROM (SELECT COL1, COL2 FROM
TABLEA) AS SUBQRY":
// effective result column: COL1 with data
type: VARCHAR
// effective result column: COL2 with data
type: VARCHAR
}
3
Parser Plugins
Plugins
|
Description
|
org.eclipse.datatools.sqltools.parsers.sql.lexer
org.eclipse.datatools.sqltools.parsers.sql.lexer.db2
|
SQL Lexer
and lexer to
support DB2 specific keywords
|
org.eclipse.datatools.sqltools.parsers.sql
|
|
org.eclipse.datatools.sqltools.parsers.sql.query
org.eclipse.datatools.sqltools.parsers.sql.query.db2
|
DML Parser and extension to support SQL dialect specific to DB2
|
org.eclipse.datatools.sqltools.parsers.sql.xml.query
|
Parser for SQL/XML:
|
org.eclipse.datatools.sqltools.parsers.sql.query.db2.test
org.eclipse.datatools.sqltools.parsers.sql.query.test
org.eclipse.datatools.sqltools.parsers.sql.test
org.eclipse.datatools.sqltools.parsers.sql.xml.query.test
|
JUint
test cases for the parser
|