public static ObjectName Object(PlSqlParser.ObjectNameContext context)
{
if (context == null)
return null;
var parts = context.id().Select(x => x.GetText()).ToArray();
var realParts = new List<string>();
foreach (var part in parts) {
if (!String.IsNullOrEmpty(part)) {
var sp = part.Split('.');
foreach (var s in sp) {
realParts.Add(s);
}
}
}
parts = realParts.ToArray();
ObjectName name = null;
for (int i = 0; i < parts.Length; i++) {
var part = InputString.AsNotQuoted(parts[i]);
if (name == null) {
name = new ObjectName(part);
} else {
name = new ObjectName(name, part);
}
}
return name;
}
public static string Simple(PlSqlParser.IdContext context)
{
if (context == null)
return null;
return InputString.AsNotQuoted(context.GetText());
}
public static SqlStatement Statement(PlSqlParser.AssignmentStatementContext context)
{
SqlExpression varRef;
if (context.general_element() != null) {
var element = ElementNode.Form(context.general_element());
if (element.Argument != null &&
element.Argument.Length > 0)
throw new ParseCanceledException("Invalid assignment: cannot assign a function");
var name = element.Id;
if (name.Parent != null)
throw new ParseCanceledException("Invalid assignment.");
varRef = SqlExpression.VariableReference(name.ToString());
} else if (context.bind_variable() != null) {
var varName = Name.Variable(context.bind_variable());
varRef = SqlExpression.VariableReference(varName);
} else {
throw new ParseCanceledException("Invalid assignment syntax");
}
var valueExp = Expression.Build(context.expression());
return new AssignVariableStatement(varRef, valueExp);
}
public static RoutineParameter Routine(PlSqlParser.ParameterContext context)
{
var paramName = Name.Simple(context.parameter_name());
var paramType = SqlTypeParser.Parse(context.datatype());
var paramDir = ParameterDirection.Input;
if (context.IN() != null) {
if (context.OUT() != null) {
paramDir = ParameterDirection.InputOutput;
} else {
paramDir = ParameterDirection.Input;
}
} else if (context.OUT() != null) {
paramDir = ParameterDirection.Output;
} else if (context.INOUT() != null) {
paramDir = ParameterDirection.InputOutput;
}
SqlExpression defaultValue = null;
var defaultPart = context.defaultValuePart();
if (defaultPart != null) {
defaultValue = Expression.Build(defaultPart.expression());
}
// TODO: Support default in RoutineParameter
return new RoutineParameter(paramName, paramType, paramDir);
}
public static CursorParameter Form(PlSqlParser.ParameterSpecContext context)
{
var paramName = Name.Simple(context.parameter_name());
var type = SqlTypeParser.Parse(context.datatype());
return new CursorParameter(paramName, type);
}
public static SqlStatement Alter(PlSqlParser.AlterUserStatementContext context)
{
var userName = context.userName().GetText();
var actions = new List<IAlterUserAction>();
foreach (var actionContext in context.alterUserAction()) {
if (actionContext.alterUserIdAction() != null) {
actions.Add(SetPassword(actionContext.alterUserIdAction()));
} else if (actionContext.setAccountAction() != null) {
actions.Add(SetAccount(actionContext.setAccountAction()));
} else if (actionContext.setRoleAction() != null) {
actions.Add(SetRole(actionContext.setRoleAction()));
}
}
if (actions.Count == 1)
return new AlterUserStatement(userName, actions[0]);
var seq = new SequenceOfStatements();
foreach (var action in actions) {
seq.Statements.Add(new AlterUserStatement(userName, action));
}
return seq;
}
private void Dispose(bool disposing)
{
if (disposing)
{
if (messages != null)
{
messages.Clear();
}
if (lexer != null)
{
lexer.Interpreter.ClearDFA();
lexer.Reset();
lexer.Interpreter = new LexerATNSimulator(new ATN(ATNType.Lexer, 2));
}
if (plSqlParser != null)
{
plSqlParser.Interpreter.ClearDFA();
plSqlParser.Reset();
plSqlParser.Interpreter = new ParserATNSimulator(new ATN(ATNType.Parser, 2));
}
}
lexer = null;
plSqlParser = null;
messages = null;
}
public static ObjectName Select(PlSqlParser.ObjectNameContext context, bool glob)
{
var name = Object(context);
if (glob)
name = new ObjectName(name, "*");
return name;
}
public static SqlType Parse(PlSqlParser.DatatypeContext context)
{
var typeInfo = new DataTypeVisitor().Visit(context);
if (!typeInfo.IsPrimitive)
throw new NotSupportedException();
return PrimitiveTypes.Resolve(typeInfo.TypeName, typeInfo.Metadata);
}
public static void analyze(TextReader file)
{
var input = new AntlrInputStream(file);
var lexer = new PlSqlLexer(input);
var tokens = new CommonTokenStream(lexer);
var parser = new PlSqlParser(tokens);
var tree = parser.sql_script();
}
public void processPackage(string inputFilePath)
{
#region ANTLR4 necessary stuff
var input = new AntlrInputStream(new StreamReader(inputFilePath));
var lexer = new PlSqlLexer(input);
var tokens = new CommonTokenStream(lexer);
var parser = new PlSqlParser(tokens);
PlSqlParser.Create_package_bodyContext context1 = parser.create_package_body();
ParseTreeWalker walker = new ParseTreeWalker();
var bListener = new BODYlistener();
walker.Walk(bListener, context1);
#endregion
//you can start making changes from this point
var converter = new JoinLinksConverter(bListener.joinsList);
var uniqueLinks = converter.MakeUniqueLinksList();
using (var storage = new StorageContext(StorageContext.constr))
{
Dictionary <string, string> linkToText = new Dictionary <string, string>();
foreach (var link in storage.links.ToList())
{
linkToText.Add(link.toText, "");
}
List <Link> linkListToConvert = new List <Link>();
linkListToConvert.AddRange(uniqueLinks.Where(link => dbObjectNames.ContainsKey(link.firstTable) && dbObjectNames.ContainsKey(link.secondTable) && !linkToText.ContainsKey(link.ToText())));
LinkDBFormatConverter linkConverter = new LinkDBFormatConverter(linkListToConvert);
foreach (var link in linkConverter.Result)
{
storage.links.Add(link);
}
storage.SaveChanges();
}
}
public static SqlTableConstraint Form(PlSqlParser.TableConstraintContext context)
{
string constraintName = Name.Simple(context.id());
ConstraintType type;
string[] columns = null;
string refTable = null;
string[] refColumns = null;
SqlExpression checkExp = null;
ForeignKeyAction? onDelete = null;
ForeignKeyAction? onUpdate = null;
if (context.primaryKeyConstraint() != null) {
type = ConstraintType.PrimaryKey;
columns = context.primaryKeyConstraint().columnList().columnName().Select(Name.Simple).ToArray();
} else if (context.uniqueKeyConstraint() != null) {
type = ConstraintType.Unique;
columns = context.uniqueKeyConstraint().columnList().columnName().Select(Name.Simple).ToArray();
} else if (context.checkConstraint() != null) {
type = ConstraintType.Check;
checkExp = Expression.Build(context.checkConstraint().expression());
} else if (context.foreignKeyConstraint() != null) {
type = ConstraintType.ForeignKey;
columns = context.foreignKeyConstraint().columns.columnName().Select(Name.Simple).ToArray();
refColumns = context.foreignKeyConstraint().refColumns.columnName().Select(Name.Simple).ToArray();
refTable = Name.Object(context.foreignKeyConstraint().objectName()).ToString();
var refActions = context.foreignKeyConstraint().referentialAction();
if (refActions != null && refActions.Length > 0) {
foreach (var action in refActions) {
if (action.onDelete() != null) {
var actionType = GetForeignKeyAction(action.onDelete().referentialActionType().GetText());
onDelete = actionType;
} else if (action.onUpdate() != null) {
var actionType = GetForeignKeyAction(action.onUpdate().referentialActionType().GetText());
onUpdate = actionType;
}
}
}
} else {
throw new ParseCanceledException("Invalid ");
}
var constraint = new SqlTableConstraint(constraintName, type, columns);
if (type == ConstraintType.ForeignKey) {
constraint.ReferenceTable = refTable;
constraint.ReferenceColumns = refColumns;
constraint.OnUpdate = onUpdate ?? ForeignKeyAction.NoAction;
constraint.OnDelete = onDelete ?? ForeignKeyAction.NoAction;
} else if (type == ConstraintType.Check) {
constraint.CheckExpression = checkExp;
}
return constraint;
}
public static WhereClause Form(PlSqlParser.WhereClauseContext context)
{
if (context.current_of_clause() != null) {
return new WhereClause {CurrentOf = context.current_of_clause().cursor_name().GetText()};
}
return new WhereClause {
Expression = new SqlExpressionVisitor().Visit(context.conditionWrapper())
};
}
public static SqlStatement Build(PlSqlParser.CreateTableStatementContext context)
{
var tableName = Name.Object(context.objectName());
var columnOrConstraints = context.columnOrConstraintList().columnOrConstraint();
bool ifNotExists = context.IF() != null && context.NOT() != null && context.EXISTS() != null;
bool temporary = context.TEMPORARY() != null;
var tableColumns = new List<SqlTableColumn>();
var tableConstraints = new List<SqlTableConstraint>();
var columnConstraints = new List<ColumnConstraint>();
foreach (var columnOrConstraint in columnOrConstraints) {
if (columnOrConstraint.tableColumn() != null) {
tableColumns.Add(TableColumn.Form(columnOrConstraint.tableColumn(), columnConstraints));
} else if (columnOrConstraint.tableConstraint() != null) {
tableConstraints.Add(TableConstraint.Form(columnOrConstraint.tableConstraint()));
}
}
if (columnConstraints.Count > 0) {
var constraintGroups = columnConstraints.GroupBy(x => x.Type);
foreach (var constraintGroup in constraintGroups) {
var columnNames = constraintGroup.Select(x => x.ColumnName).ToArray();
var index = tableConstraints.FindIndex(x => String.IsNullOrEmpty(x.ConstraintName) &&
x.ConstraintType == constraintGroup.Key);
if (index != -1) {
var unnamedConstraint = tableConstraints[index];
var columns = new List<string>(unnamedConstraint.Columns);
foreach (var columnName in columnNames) {
if (!columns.Contains(columnName))
columns.Add(columnName);
}
// TODO: set the new columns
} else {
tableConstraints.Add(new SqlTableConstraint(constraintGroup.Key, columnNames));
}
}
}
var block = new SequenceOfStatements();
block.Statements.Add(new CreateTableStatement(tableName, tableColumns) {
IfNotExists = ifNotExists,
Temporary = temporary
});
foreach (var constraint in tableConstraints) {
block.Statements.Add(new AlterTableStatement(tableName, new AddConstraintAction(constraint)));
}
return block;
}
public static SqlStatement Open(PlSqlParser.OpenStatementContext context)
{
var cursorName = Name.Simple(context.cursor_name());
SqlExpression[] args = null;
if (context.expression_list() != null) {
args = context.expression_list().expression().Select(Expression.Build).ToArray();
}
return new OpenStatement(cursorName, args);
}
public static SqlStatement Declare(PlSqlParser.CursorDeclarationContext context)
{
var cursorName = Name.Simple(context.cursor_name());
var query = Subquery.Form(context.subquery());
CursorParameter[] parameters = null;
if (context.parameterSpec() != null) {
parameters = context.parameterSpec().Select(Parameter.Form).ToArray();
}
return new DeclareCursorStatement(cursorName, parameters, query);
}
public static int? Integer(PlSqlParser.NumericContext context)
{
if (context == null)
return null;
var text = context.GetText();
int value;
if (!Int32.TryParse(text, out value))
throw new ParseCanceledException(String.Format("Numeric '{0}' is not an integer.", text));
return value;
}
public static FunctionArgument Form(PlSqlParser.ArgumentContext context)
{
if (context == null)
return null;
var id = Name.Simple(context.id());
var exp = Compile.Expression.Build(context.expressionWrapper());
return new FunctionArgument {
Id = id,
Expression = exp
};
}
private void MakeParser(string input, Action <SqlCompileMessage> messageReceiver)
{
using (var reader = new StringReader(input)) {
var inputStream = new AntlrInputStream(reader);
lexer = new PlSqlLexer(inputStream);
var commonTokenStream = new CommonTokenStream(lexer);
plSqlParser = new PlSqlParser(commonTokenStream);
plSqlParser.RemoveErrorListeners();
plSqlParser.AddErrorListener(new ErrorHandler(messageReceiver));
}
}
private static IAlterUserAction SetAccount(PlSqlParser.SetAccountActionContext context)
{
UserStatus status;
if (context.LOCK() != null) {
status = UserStatus.Locked;
} else if (context.UNLOCK() != null) {
status = UserStatus.Unlocked;
} else {
throw new ParseCanceledException();
}
return new SetAccountStatusAction(status);
}
private static LoopControlStatement Build(LoopControlType controlType, PlSqlParser.LabelNameContext labelContext, PlSqlParser.ConditionContext conditionContext)
{
string label = null;
SqlExpression whenExpression = null;
if (labelContext != null)
label = Name.Simple(labelContext);
if (conditionContext != null)
whenExpression = Expression.Build(conditionContext.expression());
return new LoopControlStatement(controlType, label, whenExpression);
}
public static SqlStatement Build(PlSqlParser.SelectStatementContext context)
{
IntoClause into;
var query = Subquery.Form(context.subquery(), out into);
if (into != null) {
SqlExpression reference;
if (into.TableName != null) {
reference = SqlExpression.Reference(into.TableName);
} else {
var vars = into.Variables;
reference = SqlExpression.Tuple(vars.Select(SqlExpression.VariableReference).Cast<SqlExpression>().ToArray());
}
return new SelectIntoStatement(query, reference);
}
var statement = new SelectStatement(query);
var orderBy = context.orderByClause();
var forUpdate = context.forUpdateClause();
if (orderBy != null) {
var sortColumns = orderBy.orderByElements().orderByElement().Select(x => {
bool asc = x.DESC() == null;
var exp = Expression.Build(x.expression());
return new SortColumn(exp, asc);
});
statement.OrderBy = sortColumns;
}
statement.ForUpdate = forUpdate != null;
var limit = context.queryLimitClause();
if (limit != null) {
var n1 = Number.PositiveInteger(limit.n1);
var n2 = Number.PositiveInteger(limit.n2);
if (n1 == null)
throw new ParseCanceledException("Invalid LIMIT clause");
if (n2 != null) {
statement.Limit = new QueryLimit(n1.Value, n2.Value);
} else {
statement.Limit = new QueryLimit(n1.Value);
}
}
return statement;
}
public static ElementNode Form(PlSqlParser.General_elementContext context)
{
var id = Name.Object(context.objectName());
var arg = context.function_argument();
IEnumerable<FunctionArgument> argNodes = null;
if (arg != null) {
argNodes = arg.argument().Select(FunctionArgument.Form);
}
return new ElementNode {
Id = id,
Argument = argNodes != null ? argNodes.ToArray() : null
};
}
public static SqlStatement Drop(PlSqlParser.DropSequenceStatementContext context)
{
var names = context.objectName().Select(Name.Object).ToArray();
var ifExists = context.IF() != null && context.EXISTS() != null;
if (names.Length == 1)
return new DropSequenceStatement(names[0]);
var sequence = new SequenceOfStatements();
foreach (var name in names) {
sequence.Statements.Add(new DropSequenceStatement(name));
}
return sequence;
}
public static SqlStatement Build(PlSqlParser.DropTableStatementContext context)
{
var tableNames = context.objectName().Select(Name.Object).ToArray();
bool ifExists = context.IF() != null && context.EXISTS() != null;
if (tableNames.Length == 1)
return new DropTableStatement(tableNames[0], ifExists);
var list = new SequenceOfStatements();
foreach (var tableName in tableNames) {
list.Statements.Add(new DropTableStatement(tableName, ifExists));
}
return list;
}
public static CreateViewStatement Create(PlSqlParser.CreateViewStatementContext context)
{
var orReplace = context.OR() != null && context.REPLACE() != null;
var viewName = Name.Object(context.objectName());
var query = (SqlQueryExpression) Expression.Build(context.subquery());
string[] columnNames = null;
if (context.columnList() != null) {
columnNames = context.columnList().columnName().Select(Name.Simple).ToArray();
}
return new CreateViewStatement(viewName, columnNames, query) {
ReplaceIfExists = orReplace
};
}
public static SqlStatement Build(PlSqlParser.AlterTableStatementContext context)
{
var tableName = Name.Object(context.objectName());
var actions = context.alterTableAction().Select(ActionBuilder.Build).ToArray();
if (actions.Length == 1)
return new AlterTableStatement(tableName, actions[0]);
var list = new SequenceOfStatements();
foreach (var action in actions) {
list.Statements.Add(new AlterTableStatement(tableName, action));
}
return list;
}
public static UpdateStatement Build(PlSqlParser.UpdateStatementContext context)
{
var tableName = Name.Object(context.objectName());
var setClause = context.updateSetClause();
var limitClause = context.updateLimitClause();
if (setClause != null) {
var assignments = new List<SqlColumnAssignment>();
var whereClause = context.whereClause();
int limit = -1;
if (limitClause != null)
limit = Number.PositiveInteger(limitClause.numeric()) ?? -1;
SqlExpression whereExpression = null;
if (whereClause != null)
whereExpression = Expression.Build(whereClause.conditionWrapper());
if (setClause.VALUE() != null) {
var columnName = Name.Simple(setClause.columnName());
var value = Expression.Build(setClause.expression());
assignments.Add(new SqlColumnAssignment(columnName, value));
} else {
var pairs = setClause.columnBasedUpdateClause().Select(x => new {
columnName = Name.Simple(x.columnName()),
value = Expression.Build(x.expression())
});
assignments = pairs.Select(x => new SqlColumnAssignment(x.columnName, x.value)).ToList();
}
return new UpdateStatement(tableName, whereExpression, assignments) {
Limit = limit
};
}
if (context.updateFromClause() != null) {
var query = Subquery.Form(context.updateFromClause().subquery());
}
throw new NotSupportedException();
}
public static SqlStatement Build(PlSqlParser.DeleteStatementContext context)
{
var tableName = Name.Object(context.objectName());
var whereClause = WhereClause.Form(context.whereClause());
if (whereClause.CurrentOf != null)
return new DeleteCurrentStatement(tableName, whereClause.CurrentOf);
var statement = new DeleteStatement(tableName, whereClause.Expression);
if (context.deleteLimit() != null) {
var limit = Number.PositiveInteger(context.deleteLimit().numeric());
if (limit == null)
throw new ParseCanceledException("Invalid delete limit.");
statement.Limit = limit.Value;
}
return statement;
}
public static SqlQueryExpression Form(PlSqlParser.SubqueryContext context, out IntoClause into)
{
var query = Form(context.subqueryBasicElements(), out into);
var opPart = context.subquery_operation_part();
if (opPart.Length > 0) {
if (into != null)
throw new InvalidOperationException("Cannot SELECT INTO in a composite query.");
foreach (var part in opPart) {
CompositeFunction function;
if (part.MINUS() != null || part.EXCEPT() != null) {
function = CompositeFunction.Except;
} else if (part.UNION() != null) {
function = CompositeFunction.Union;
} else if (part.INTERSECT() != null) {
function = CompositeFunction.Intersect;
} else {
throw new ParseCanceledException("Invalid composite function.");
}
bool isAll = part.ALL() != null;
var next = Form(part.subqueryBasicElements());
var prev = query.NextComposite;
if (prev == null) {
prev = next;
} else {
prev.NextComposite = next;
}
query.IsCompositeAll = isAll;
query.CompositeFunction = function;
query.NextComposite = prev;
}
}
return query;
}
public static SqlStatement Create(PlSqlParser.CreateUserStatementContext context)
{
var userName = context.userName().GetText();
SqlExpression arg;
SqlIdentificationType type;
if (context.byPassword() != null) {
arg = SqlExpression.Constant(InputString.AsNotQuoted(context.byPassword().CHAR_STRING().GetText()));
type = SqlIdentificationType.Password;
} else if (context.externalId() != null) {
arg = SqlExpression.Constant(InputString.AsNotQuoted(context.externalId().CHAR_STRING()));
type = SqlIdentificationType.External;
} else if (context.globalId() != null) {
arg = SqlExpression.Constant(InputString.AsNotQuoted(context.globalId().CHAR_STRING()));
type = SqlIdentificationType.Global;
} else {
throw new ParseCanceledException();
}
var id = new SqlUserIdentifier(type, arg);
return new CreateUserStatement(userName, id);
}
public static SqlStatement Create(PlSqlParser.CreateSequenceStatementContext context)
{
var seqName = Name.Object(context.objectName());
var statement = new CreateSequenceStatement(seqName);
var startWith = context.sequenceStartClause();
if (startWith != null) {
statement.StartWith = SqlExpression.Constant(Number.PositiveInteger(startWith.UNSIGNED_INTEGER()));
}
var specs = context.sequenceSpec();
if (specs != null && specs.Length > 0) {
foreach (var spec in specs) {
if (spec.INCREMENT() != null &&
spec.BY() != null) {
statement.IncrementBy = SqlExpression.Constant(Number.PositiveInteger(spec.UNSIGNED_INTEGER()));
} else if (spec.MAXVALUE() != null) {
statement.MaxValue = SqlExpression.Constant(Number.PositiveInteger(spec.UNSIGNED_INTEGER()));
} else if (spec.NOMAXVALUE() != null) {
statement.MaxValue = SqlExpression.Constant(null);
} else if (spec.MINVALUE() != null) {
statement.MinValue = SqlExpression.Constant(Number.PositiveInteger(spec.UNSIGNED_INTEGER()));
} else if (spec.NOMINVALUE() != null) {
statement.MinValue = SqlExpression.Constant(null);
} else if (spec.CACHE() != null) {
statement.Cache = SqlExpression.Constant(Number.PositiveInteger(spec.UNSIGNED_INTEGER()));
} else if (spec.NOCACHE() != null) {
statement.Cache = SqlExpression.Constant(null);
} else if (spec.CYCLE() != null) {
statement.Cycle = true;
} else if (spec.NOCYCLE() != null) {
statement.Cycle = false;
}
}
}
return statement;
}
public static IAlterTableAction Build(PlSqlParser.AlterTableActionContext context)
{
if (context.addColumnAction() != null) {
var column = TableColumn.Form(context.addColumnAction().tableColumn(), new List<ColumnConstraint>());
return new AddColumnAction(column);
}
if (context.addConstraintAction() != null) {
var constraint = TableConstraint.Form(context.addConstraintAction().tableConstraint());
return new AddConstraintAction(constraint);
}
if (context.dropColumnAction() != null) {
var columnName = context.dropColumnAction().id().GetText();
return new DropColumnAction(columnName);
}
if (context.dropConstraintAction() != null) {
var constraintName = context.dropConstraintAction().id().GetText();
return new DropConstraintAction(constraintName);
}
if (context.dropPrimaryKeyAction() != null) {
return new DropPrimaryKeyAction();
}
if (context.dropDefaultAction() != null) {
var columnName = Name.Simple(context.dropDefaultAction().columnName());
return new DropDefaultAction(columnName);
}
if (context.setDefaultAction() != null) {
var columnName = Name.Simple(context.setDefaultAction().columnName());
var defaultValue = Expression.Build(context.setDefaultAction().expressionWrapper());
return new SetDefaultAction(columnName, defaultValue);
}
throw new ParseCanceledException("The ALTER TABLE action is not supported");
}
public SqlType Parse(IContext context, string s)
{
var typeInfo = PlSqlParser.ParseType(s);
if (PrimitiveTypes.IsPrimitive(typeInfo.TypeName))
{
return(PrimitiveTypes.Resolver.Resolve(typeInfo));
}
if (context == null)
{
throw new Exception($"Type {typeInfo.TypeName} is not primitive and no context is provided");
}
var resolver = context.Scope.Resolve <ISqlTypeResolver>();
if (resolver == null)
{
throw new InvalidOperationException($"The type {typeInfo.TypeName} is not primitive and no resolver was found in context");
}
return(resolver.Resolve(typeInfo));
}
public SqlExpressionParseResult Parse(IContext context, string expression)
{
return(PlSqlParser.ParseExpression(context, expression));
}
private void MakeParser(string input, Action<SqlCompileMessage> messageReceiver)
{
using (var reader = new StringReader(input)) {
var inputStream = new AntlrInputStream(reader);
lexer = new PlSqlLexer(inputStream);
var commonTokenStream = new CommonTokenStream(lexer);
plSqlParser = new PlSqlParser(commonTokenStream);
plSqlParser.RemoveErrorListeners();
plSqlParser.AddErrorListener(new ErrorHandler(messageReceiver));
}
}
