API
Classes
- SQLSurveyor
- OutputColumn
- ParsedQuery
- ParsedSql
- ParsingError
- ParsingErrorType
- QueryType
- ReferencedColumn
- ReferencedTable
- SQLDialect
- SQLSurveyorOptions
- Token
- TokenLocation
- TokenType
SQLSurveyor
This class is the main entry point to this library. First, you instantiate an instance of this class with the SQL dialect you require. Then, you can use the survey method to analyze your SQL script.
Methods
Constructor
Requires the SQL dialect you will be parsing. See SQLDialect.
Optionally, you can provide an options object matching the SQLSurveyorOptions interface.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
survey(sqlScript: string): ParsedSql
This method returns a ParsedSql object that describes the key attributes of the SQL script.
sqlScript: The string containing the SQL script to be surveyed.
This can be one or more SQL statements. Incomplete statements are also handled gracefully,
as described in the ParsedQuery documentation.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
const sql = 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id';
const parsedSql = surveyor.survey(sql);
console.dir(parsedSql, { depth: null });
// ParsedSql {
// parsedQueries: {
// '0': ParsedQuery {
// outputColumns: [
// OutputColumn { columnName: 'columnA', columnAlias: null, tableName: 'table1', tableAlias: 't1'},
// OutputColumn { columnName: 'columnB', columnAlias: null, tableName: 'table2', tableAlias: 't2'}
// ],
// referencedColumns: [
// ReferencedColumn {
// columnName: 'id', tableName: 'table1', tableAlias: 't1',
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 67 }
// }
// },
// ReferencedColumn {
// columnName: 'table1_id', tableName: 'table2', tableAlias: 't2',
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 82 }
// }
// }
// ],
// referencedTables: {
// table1: ReferencedTable {
// tableName: 'table1', schemaName: null, databaseName: null, aliases: Set { 't1' },
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 35, stopIndex: 40 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 64 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 7, stopIndex: 8 } }
// },
// table2: ReferencedTable {
// tableName: 'table2', schemaName: null, databaseName: null, aliases: Set { 't2' },
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 50, stopIndex: 55 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 72 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 19, stopIndex: 20 }
// }
// }
// },
// tokens: {
// '0': Token { value: 'SELECT', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 0, stopIndex: 5 }},
// '7': Token { value: 't1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 7, stopIndex: 8 }},
// '9': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 9, stopIndex: 9 }},
// '10': Token { value: 'columnA', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 10, stopIndex: 16 }},
// '17': Token { value: ',', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 17, stopIndex: 17 }},
// '19': Token { value: 't2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 19, stopIndex: 20 }},
// '21': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 21, stopIndex: 21 }},
// '22': Token { value: 'columnB', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 22, stopIndex: 28 }},
// '30': Token { value: 'FROM', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 30, stopIndex: 33 }},
// '35': Token { value: 'table1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 35, stopIndex: 40 }},
// '42': Token { value: 't1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 42, stopIndex: 43 }},
// '45': Token { value: 'JOIN', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 45, stopIndex: 48 }},
// '50': Token { value: 'table2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 50, stopIndex: 55 }},
// '57': Token { value: 't2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 57, stopIndex: 58 }},
// '60': Token { value: 'ON', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 60, stopIndex: 61 }},
// '63': Token { value: 't1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 64 }},
// '65': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 65, stopIndex: 65 }},
// '66': Token { value: 'id', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 66, stopIndex: 67 }},
// '69': Token { value: '=', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 69, stopIndex: 69 }},
// '71': Token { value: 't2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 72 }},
// '73': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 73, stopIndex: 73 }},
// '74': Token { value: 'table1_id', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 74, stopIndex: 82 }}
// },
// query: 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id',
// queryType: 'DML',
// queryLocation: TokenLocation { lineStart: 1,lineEnd: 1,startIndex: 0,stopIndex: 82 },
// queryErrors: [],
// subqueries: {},
// commonTableExpressions: {}
// }
// }
// }
setDialect(dialect: SQLDialect): void
Changes the SQL dialect to be used for surveying future SQL strings.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
const sql = 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id';
const parsedSql = surveyor.survey(sql);
const tsql = 'SELECT [t1].[columnA], [t2].[columnB] FROM [table1] [t1] JOIN [table2] [t2] ON [t1].[id] = [t2].[table1_id]';
surveyor.setDialect(SQLDialect.TSQL);
const parsedTSql = surveyor.survey(tsql);
OutputColumn
This class represents a column that will be output or returned by a given SQL statement.
For example, in the SELECT statement SELECT columnA FROM table1 WHERE columnB > 0,
the output column is columnA.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
const sql = 'SELECT t1.columnA cA, t2.columnB cB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id';
const parsedSql = surveyor.survey(sql);
console.dir(parsedSql.parsedQueries[0].outputColumns, { depth: null });
// [
// OutputColumn { columnName: 'columnA', columnAlias: 'cA', tableName: 'table1', tableAlias: 't1'},
// OutputColumn { columnName: 'columnB', columnAlias: 'cB', tableName: 'table2', tableAlias: 't2'}
// ]
Properties
columnName: string
The name of the column being output.
columnAlias: string
The alias for the column (if one was used when defining the output column).
tableName: string
The name of the table that the column belongs to (if it, or an alias, is used when defining the output column).
tableAlias: string
The alias of the table that the column belongs to (if it is used when defining the output column)
ParsedQuery
Each query that is parsed in the SQL Script provided to the SQLSurveyor.survey method,
will be represented by a ParsedQuery object.
Each subquery or common table expression within the query will also be defined as a
separate ParsedQuery object (stored in the subqueries and commonTableExpressions properties).
If you need to access the values that pertain only to a specific ParsedQuery object, you should
access their properties directly.
If you need to retrieve all values for the entire query (and all the subqueries and common table expressions),
then you should use the relevant method instead.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
const sql = 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id';
const parsedSql = surveyor.survey(sql);
console.dir(parsedSql.parsedQueries[0], { depth: null });
// ParsedQuery {
// outputColumns: [
// OutputColumn { columnName: 'columnA', columnAlias: null, tableName: 'table1', tableAlias: 't1'},
// OutputColumn { columnName: 'columnB', columnAlias: null, tableName: 'table2', tableAlias: 't2'}
// ],
// referencedColumns: [
// ReferencedColumn {
// columnName: 'id', tableName: 'table1', tableAlias: 't1',
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 67 }
// }
// },
// ReferencedColumn {
// columnName: 'table1_id', tableName: 'table2', tableAlias: 't2',
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 82 }
// }
// }
// ],
// referencedTables: {
// table1: ReferencedTable {
// tableName: 'table1', schemaName: null, databaseName: null, aliases: Set { 't1' },
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 35, stopIndex: 40 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 64 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 7, stopIndex: 8 } }
// },
// table2: ReferencedTable {
// tableName: 'table2', schemaName: null, databaseName: null, aliases: Set { 't2' },
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 50, stopIndex: 55 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 72 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 19, stopIndex: 20 }
// }
// }
// },
// tokens: {
// '0': Token { value: 'SELECT', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 0, stopIndex: 5 }},
// '7': Token { value: 't1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 7, stopIndex: 8 }},
// '9': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 9, stopIndex: 9 }},
// '10': Token { value: 'columnA', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 10, stopIndex: 16 }},
// '17': Token { value: ',', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 17, stopIndex: 17 }},
// '19': Token { value: 't2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 19, stopIndex: 20 }},
// '21': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 21, stopIndex: 21 }},
// '22': Token { value: 'columnB', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 22, stopIndex: 28 }},
// '30': Token { value: 'FROM', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 30, stopIndex: 33 }},
// '35': Token { value: 'table1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 35, stopIndex: 40 }},
// '42': Token { value: 't1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 42, stopIndex: 43 }},
// '45': Token { value: 'JOIN', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 45, stopIndex: 48 }},
// '50': Token { value: 'table2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 50, stopIndex: 55 }},
// '57': Token { value: 't2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 57, stopIndex: 58 }},
// '60': Token { value: 'ON', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 60, stopIndex: 61 }},
// '63': Token { value: 't1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 64 }},
// '65': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 65, stopIndex: 65 }},
// '66': Token { value: 'id', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 66, stopIndex: 67 }},
// '69': Token { value: '=', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 69, stopIndex: 69 }},
// '71': Token { value: 't2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 72 }},
// '73': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 73, stopIndex: 73 }},
// '74': Token { value: 'table1_id', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 74, stopIndex: 82 }}
// },
// query: 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id',
// queryType: 'DML',
// queryLocation: TokenLocation { lineStart: 1,lineEnd: 1,startIndex: 0,stopIndex: 82 },
// queryErrors: [],
// subqueries: {},
// commonTableExpressions: {}
// }
// }
Methods
Constructor
Requires the SQL dialect you will be parsing. See SQLDialect.
getAllReferencedTables(): { [tableName: string]: ReferencedTable }
Retrieves the ReferencedTable objects for all tables that were detected in the query, and any subqueries and common table expressions, indexed by the name of the table.
getAllReferencedColumns(): ReferencedColumn[]
Retrieves the ReferencedColumn objects for all columns that were detected in the query, and any subqueries and common table expressions.
getTableFromAlias(alias: string): string
Returns the name of the table, based on the provided alias.
getAliasesForTable(tableName: string): string[]
Returns all aliases used for a table with the provided name.
getTokenAtLocation(stringIndex: number): Token
Retrieves the token located at the zero-based index within the query.
The index is based on the token’s index in the entire SQL script object that was parsed.
Returns null if no token matches the stringIndex.
getPreviousTokenFromLocation(stringIndex: number): Token
Retrieves the token immediately preceding the token at the given string location.
The index is based on the token’s index in the entire SQL script object that was parsed.
Returns null if no token is found before the stringIndex.
getNextTokenFromLocation(stringIndex: number): Token
Retrieves the token immediately after the token at the given string location.
The index is based on the token’s index in the entire SQL script object that was parsed.
Returns null if no token is found after the stringIndex.
getReferencedColumn(columnName: string, tableName?: string, tableAlias?: string): ReferencedColumn
Get the ReferencedColumn from the query if it matches the columnName and tableName (optional) and tableAlias (optional) provided.
getSmallestQueryAtLocation(stringIndex: number): ParsedQuery
Retrieves the lowest level ParsedQuery object at the given string location. That is, if there is a subquery at that location (or a subquery within that subquery, etc.), return the ParsedQuery representing the deepest (or smallest).
Properties
query: string
The string with the full query that was parsed and identified.
queryType: QueryType
The type of query (i.e. DDL, DML, etc.). See QueryType for all possible types.
outputColumns: OutputColumn[]
The columns that are output by this query. See OutputColumn for more details. Does not include columns output by subqueries or common table expressions within the given query.
referencedColumns: ReferencedColumn[]
The columns referenced by this query. See ReferencedColumn for more details. Does not include columns referenced by subqueries or common table expressions within the given query.
referencedTables: { [tableName: string]: ReferencedTable }
The tables referenced by this query. See ReferencedTable for more details. Does not include columns output by subqueries or common table expressions within the given query.
tokens: { [queryStartIndex: number]: Token }
All the individual tokens that have been identified in the query. See Token for more details. This does include tokens in subqueries and common table expressions within the given query.
queryLocation: TokenLocation
The location within the originally surveyed SQL script. See TokenLocation for more details.
queryErrors: ParsingError[]
If parsing the SQL in this query caused an error, this property will be populated. See ParsingError for more details.
commonTableExpressionName: string
If this ParsedQuery is a common table expression (CTE), the name or alias for this CTE will be populated here.
subqueries: { [subqueryStartIndex: number]: ParsedQuery }
The subqueries that are contained within this query, indexed by their location within the SQL script that was originally surveyed.
commonTableExpressions: { [cteStartIndex: number]: ParsedQuery }
The common table expressions that are contained within this query, indexed by their location within the SQL script that was originally surveyed.
ParsedSql
This class represents all the queries that were identified by the SQL surveyor.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
const sql = 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id';
const parsedSql = surveyor.survey(sql);
console.dir(parsedSql, { depth: null });
// ParsedSql {
// parsedQueries: {
// '0': ParsedQuery {
// outputColumns: [
// OutputColumn { columnName: 'columnA', columnAlias: null, tableName: 'table1', tableAlias: 't1'},
// OutputColumn { columnName: 'columnB', columnAlias: null, tableName: 'table2', tableAlias: 't2'}
// ],
// referencedColumns: [
// ReferencedColumn {
// columnName: 'id', tableName: 'table1', tableAlias: 't1',
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 67 }
// }
// },
// ReferencedColumn {
// columnName: 'table1_id', tableName: 'table2', tableAlias: 't2',
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 82 }
// }
// }
// ],
// referencedTables: {
// table1: ReferencedTable {
// tableName: 'table1', schemaName: null, databaseName: null, aliases: Set { 't1' },
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 35, stopIndex: 40 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 64 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 7, stopIndex: 8 } }
// },
// table2: ReferencedTable {
// tableName: 'table2', schemaName: null, databaseName: null, aliases: Set { 't2' },
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 50, stopIndex: 55 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 72 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 19, stopIndex: 20 }
// }
// }
// },
// tokens: {
// '0': Token { value: 'SELECT', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 0, stopIndex: 5 }},
// '7': Token { value: 't1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 7, stopIndex: 8 }},
// '9': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 9, stopIndex: 9 }},
// '10': Token { value: 'columnA', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 10, stopIndex: 16 }},
// '17': Token { value: ',', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 17, stopIndex: 17 }},
// '19': Token { value: 't2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 19, stopIndex: 20 }},
// '21': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 21, stopIndex: 21 }},
// '22': Token { value: 'columnB', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 22, stopIndex: 28 }},
// '30': Token { value: 'FROM', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 30, stopIndex: 33 }},
// '35': Token { value: 'table1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 35, stopIndex: 40 }},
// '42': Token { value: 't1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 42, stopIndex: 43 }},
// '45': Token { value: 'JOIN', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 45, stopIndex: 48 }},
// '50': Token { value: 'table2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 50, stopIndex: 55 }},
// '57': Token { value: 't2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 57, stopIndex: 58 }},
// '60': Token { value: 'ON', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 60, stopIndex: 61 }},
// '63': Token { value: 't1', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 64 }},
// '65': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 65, stopIndex: 65 }},
// '66': Token { value: 'id', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 66, stopIndex: 67 }},
// '69': Token { value: '=', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 69, stopIndex: 69 }},
// '71': Token { value: 't2', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 72 }},
// '73': Token { value: '.', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 73, stopIndex: 73 }},
// '74': Token { value: 'table1_id', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 74, stopIndex: 82 }}
// },
// query: 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id',
// queryType: 'DML',
// queryLocation: TokenLocation { lineStart: 1,lineEnd: 1,startIndex: 0,stopIndex: 82 },
// queryErrors: [],
// subqueries: {},
// commonTableExpressions: {}
// }
// }
// }
Methods
getQueryAtLocation(stringIndex: number): ParsedQuery
Retrieves the ParsedQuery representing the query located at the zero-based string index within the SQL script originally surveyed.
getQueryIndexAtLocation(stringIndex: number): number
Retrieves the index of the query located at the zero-based string index within the SQL script originally surveyed. The query index corresponds to the indexes within the parsedQueries property.
getQueryLocations(): TokenLocation[]
Retrieves the locations of all the queries. See TokenLocation for more details.
Properties
parsedQueries: { [queryStartIndex: number]: ParsedQuery }
The queries that are contained within this SQL script, indexed by the index within the SQL script string that the query starts.
ParsingError
If a SQL query is not able to be parsed by our ANTLR4 parser, because it is incomplete or incorrect, the error will be defined by this class.
See ParsingErrorType for more details on the types of errors that can be reported.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
const sql = 'SELECT * FROM WHERE ';
const parsedSql = surveyor.survey(sql);
console.dir(parsedSql.parsedQueries[0].queryErrors, { depth: null });
// [
// ParsingError {
// token: Token {
// value: 'WHERE',
// location: TokenLocation {
// lineStart: 1,
// lineEnd: 1,
// startIndex: 14,
// stopIndex: 18
// }
// },
// type: 'MISMATCHED_INPUT'
// }
// ]
Properties
token: Token
The Token where the parsing error occurred.
type: ParsingErrorType
The type of parsing error that occurred.
ParsingErrorType
This class enumerates the parsing error types that can be reported. This parsing errors are defined by ANTLR4. Possible values include:
FAILED_PREDICATE
MISSING_TOKEN
MISMATCHED_INPUT
NO_VIABLE_ALTERNATIVE
UNWANTED_TOKEN
FAILED_PREDICATE
There is a syntax error with the token.
See the official ANTLR4 documentation for this error here.
MISSING_TOKEN
There is a token (or multiple tokens) missing before the token at this location.
MISMATCHED_INPUT
The query is likely incomplete.
See the official ANTLR4 documentation for this error here.
NO_VIABLE_ALTERNATIVE
Either the token specified, or the tokens that follow it are not valid at their current location.
See the official ANTLR4 documentation for this error here.
UNWANTED_TOKEN
Likely, if the specified token was removed, the query would be correct.
QueryType
This class enumerates the types of queries that can be identified. Possible values include:
DML // Data Manipulation Language
// - Queries like SELECT, UPDATE, DELETE that handle data
DDL // Data Definition Language
// - Queries like CREATE, ALTER, DROP that handle data structures
STORED_PROCEDURE // Stored Procedures
ReferencedColumn
This class defines columns that have been referenced in the query, but are not output by the query.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
const sql = 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id';
const parsedSql = surveyor.survey(sql);
console.dir(parsedSql.parsedQueries[0].referencedColumns, { depth: null });
// [
// ReferencedColumn {
// columnName: 'id', tableName: 'table1', tableAlias: 't1',
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 67 }
// }
// },
// ReferencedColumn {
// columnName: 'table1_id', tableName: 'table2', tableAlias: 't2',
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 82 }
// }
// }
// ]
Properties
columnName: string
The name of the column that has been referenced.
tableName: string
The name of the table that the column belongs to (if it, or an alias, is used when referencing the column).
tableAlias: string
The name of the table that the column belongs to (if it is used when referencing the column).
locations: Set
The locations within the original SQL script that this column is referenced at.
ReferencedTable
This class defines tables that have been referenced in the query.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
const sql = 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id';
const parsedSql = surveyor.survey(sql);
console.dir(parsedSql.parsedQueries[0].referencedTables, { depth: null });
// {
// table1: ReferencedTable {
// tableName: 'table1', schemaName: null, databaseName: null, aliases: Set { 't1' },
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 35, stopIndex: 40 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 64 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 7, stopIndex: 8 } }
// },
// table2: ReferencedTable {
// tableName: 'table2', schemaName: null, databaseName: null, aliases: Set { 't2' },
// locations: Set {
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 50, stopIndex: 55 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 72 },
// TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 19, stopIndex: 20 }
// }
// }
// }
Methods
static clone(referencedTable: ReferencedTable): ReferencedTable
Creates an idenitical copy of the referenced table.
Properties
tableName: string
The name of the table, as used in the query.
schemaName: string
The schema the table belongs to. Populated if a schema hint was used in the query.
databaseName: string
The database the table belongs to. Populated if a database hint was used in the query (currently only used for T-SQL).
aliases: Set
All aliases used for the table in the query.
locations: Set
The locations within the original SQL script that this table is referenced at.
SQLDialect
This class enumerates the SQL dialects that are supported. Possible values include:
MYSQL
TSQL // For SQL Server
PLSQL // For Oracle
PLpgSQL // For PostgreSQL
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
SQLSurveyorOptions
This interface describes customizable behaviour for the SQLSurveyor instance.
Properties
logErrors: boolean
Controls whether errors that occur during processing are logged to the console. This option does not affect error tracking due to SQL syntax errors, only runtime errors.
Default: false
throwErrors: boolean
Controls whether errors that occur during processing are thrown. This option does not affect error tracking due to SQL syntax errors, only runtime errors.
Default: true
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const options = {
logErrors: true,
throwErrors: false
};
const surveyor = new SQLSurveyor(SQLDialect.MYSQL, options);
Token
A single word or punctuation used in the SQL script. A token could be a SQL keyword, a comma, a column or table, a semi-colon, etc. Whitespace is not included as a token.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
const sql = 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id';
const parsedSql = surveyor.survey(sql);
console.dir(parsedSql.parsedQueries[0].tokens, { depth: null });
// {
// '0': Token { value: 'SELECT', type: 'KEYWORD', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 0, stopIndex: 5 }},
// '7': Token { value: 't1', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 7, stopIndex: 8 }},
// '9': Token { value: '.', type: 'OPERATOR', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 9, stopIndex: 9 }},
// '10': Token { value: 'columnA', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 10, stopIndex: 16 }},
// '17': Token { value: ',', type: 'OPERATOR', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 17, stopIndex: 17 }},
// '19': Token { value: 't2', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 19, stopIndex: 20 }},
// '21': Token { value: '.', type: 'OPERATOR', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 21, stopIndex: 21 }},
// '22': Token { value: 'columnB', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 22, stopIndex: 28 }},
// '30': Token { value: 'FROM', type: 'KEYWORD', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 30, stopIndex: 33 }},
// '35': Token { value: 'table1', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 35, stopIndex: 40 }},
// '42': Token { value: 't1', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 42, stopIndex: 43 }},
// '45': Token { value: 'JOIN', type: 'KEYWORD', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 45, stopIndex: 48 }},
// '50': Token { value: 'table2', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 50, stopIndex: 55 }},
// '57': Token { value: 't2', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 57, stopIndex: 58 }},
// '60': Token { value: 'ON', type: 'KEYWORD', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 60, stopIndex: 61 }},
// '63': Token { value: 't1', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 63, stopIndex: 64 }},
// '65': Token { value: '.', type: 'OPERATOR', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 65, stopIndex: 65 }},
// '66': Token { value: 'id', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 66, stopIndex: 67 }},
// '69': Token { value: '=', type: 'OPERATOR', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 69, stopIndex: 69 }},
// '71': Token { value: 't2', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 71, stopIndex: 72 }},
// '73': Token { value: '.', type: 'OPERATOR', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 73, stopIndex: 73 }},
// '74': Token { value: 'table1_id', type: 'IDENTIFIER', location: TokenLocation { lineStart: 1, lineEnd: 1, startIndex: 74, stopIndex: 82 }}
// }
Methods
setValue(input: string): void
Based on the location already populated by the token, set the value of the token by getting the substring from the provided input.
Properties
value: string
The text of the token.
location: TokenLocation
The location, within the SQL script of the token.
TokenLocation
Defines the location of a token within the SQL script.
Usage Example
import { SQLSurveyor, SQLDialect } from 'sql-surveyor';
const surveyor = new SQLSurveyor(SQLDialect.MYSQL);
const sql = 'SELECT t1.columnA, t2.columnB FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id';
const parsedSql = surveyor.survey(sql);
console.dir(parsedSql.parsedQueries[0].tokens[0].location, { depth: null });
// { lineStart: 1, lineEnd: 1, startIndex: 0, stopIndex: 5 }
Methods
getToken(input: string): string
Get the value of the token by getting the substring from the provided input.
static clone(token: TokenLocation): TokenLocation
Creates an idenitical copy of the token.
Properties
startIndex: number
The zero-based index where the token starts (inclusive of this index).
stopIndex: number
The zero-based index where the token stops (inclusive of this index).
lineStart: number
The one-based row number where the token starts.
lineEnd: number
The one-based row number where the token ends.
TokenType
This class enumerates types of Tokens that will be identified. Possible values include:
COMMENT // i.e. -- This is a comment
IDENTIFIER // i.e. tableName
KEYWORD // i.e. SELECT
LITERAL // i.e. 100 or 'This is a string literal'
OPERATOR // i.e. >, <, = or .