public string ExtractIntoFunction()
{
var browser = new SolutionBrowserForm("");
browser.ShowDialog();
var destination = browser.DestinationItem;
if (destination == null)
{
return(null);
}
var name = browser.GetObjectName();
var function = new CreateFunctionStatement();
var returnSelect = (function.ReturnType = new SelectFunctionReturnType()) as SelectFunctionReturnType;
returnSelect.SelectStatement = GetSelectStatementForQuery();
function.Name = name.ToSchemaObjectName();
var classFolder = destination.ProjectItems.AddFromTemplate("Procedure", name.UnQuote() + ".sql");
var filePath = classFolder.GetStringProperty("FullPath");
File.WriteAllText(filePath, ScriptDom.GenerateTSql(function));
classFolder.Open().Visible = true;
return(GetCallingCode(function));
}
public override void ExplicitVisit(CreateFunctionStatement func)
{
if (IsSupportedForCurrentType(func.GetType()))
{
Name = func.Name;
}
}
public string ExtractIntoFunction()
{
var browser = new SolutionBrowserForm("");
browser.ShowDialog();
var destination = browser.DestinationItem;
if (destination == null)
return null;
var name = browser.GetObjectName();
var function = new CreateFunctionStatement();
var returnSelect = (function.ReturnType = new SelectFunctionReturnType()) as SelectFunctionReturnType;
returnSelect.SelectStatement = GetSelectStatementForQuery();
function.Name = name.ToSchemaObjectName();
var classFolder = destination.ProjectItems.AddFromTemplate("Procedure", name.UnQuote() + ".sql");
var filePath = classFolder.GetStringProperty("FullPath");
File.WriteAllText(filePath, ScriptDom.GenerateTSql(function));
classFolder.Open().Visible = true;
return GetCallingCode(function);
}
public override void Visit(CreateFunctionStatement node)
{
base.Visit(node);
CheckFirstStatementInBatch(node, "CREATE FUNCTION");
CheckLastStatementInBatch(node, "CREATE FUNCTION");
}
internal static TestFunction LoadFunctionMetatadaFromDDL(TestSchema schema, string ddl)
{
TSqlFragment sqlF = ScriptDomFacade.Parse(ddl);
CreateFunctionStatement stmt_CreateFunction = (CreateFunctionStatement)((TSqlScript)sqlF).Batches[0].Statements[0];
string schemaName = stmt_CreateFunction.Name.SchemaIdentifier.Dequote();
string functionName = stmt_CreateFunction.Name.BaseIdentifier.Dequote();
if (stmt_CreateFunction.StatementList != null)
{
// 'hlsyssec_query_agentsystemacl'
return(LoadMultiStatementTableValuedFunction(schemaName, functionName, stmt_CreateFunction));
}
// inline 'hlsur_query_surveyresults'
string functionBody = GetFragmentStreamAsText(((SelectFunctionReturnType)stmt_CreateFunction.ReturnType).SelectStatement);
var function = new TestFunction(schemaName, functionName, functionBody, f => LoadFunctionOutputColumns(schema, f, stmt_CreateFunction));
foreach (ProcedureParameter prm in stmt_CreateFunction.Parameters)
{
string parameterName = prm.VariableName.Dequote();
var parameterDbType = ProcedureGenerator.ResolveToDbDataType(prm.DataType);
function.AddParameter(parameterName, parameterDbType, true);
}
return(function);
}
private static string ChangeCreateToAlter(CreateFunctionStatement function, string wholeScript)
{
//get part of script we are interested in...
var subScript = wholeScript.Substring(function.StartOffset, function.FragmentLength);
IList <ParseError> errors;
var fragment = new TSql130Parser(false).Parse(new StringReader(subScript), out errors);
bool haveCreate = false;
var output = new StringBuilder();
foreach (var token in fragment.ScriptTokenStream)
{
if (!haveCreate && token.TokenType == TSqlTokenType.Create)
{
var alterToken = new TSqlParserToken(TSqlTokenType.Alter, "alter");
output.Append(alterToken.Text);
haveCreate = true;
continue;
}
output.Append(token.Text);
}
return(output.ToString());
}
private static string BuildIfNotExistsStatementsInlineFunction(CreateFunctionStatement function)
{
var generateIfExists =
string.Format("if object_id('{0}') is null\r\nbegin\r\n execute sp_executeSql N' create function {0}() \r\n RETURNS TABLE as RETURN SELECT 1 as a' \r\nEND;",
function.Name.ToQuotedString());
return(generateIfExists);
}
private static string BuildIfNotExistsStatements(CreateFunctionStatement function)
{
var generateIfExists =
string.Format("if object_id('{0}') is null\r\nbegin\r\n execute sp_executeSql N' create function {0}() \r\n RETURNS @t TABLE (stringValue VARCHAR(128))\r\n as \r\nbegin\r\ninsert into @t values(''ee'')\r\n return\r\nend';\r\nend",
function.Name.ToQuotedString());
return(generateIfExists);
}
public override void Visit(CreateFunctionStatement node)
{
base.Visit(node);
var name = node.Name.SchemaIdentifier.Value + '.' + node.Name.BaseIdentifier.Value;
functions.Add(name, node);
}
private static string BuildIfNotExistsStatements(CreateFunctionStatement function)
{
var generateIfExists =
string.Format("if object_id('{0}') is null\r\nbegin\r\n execute sp_executeSql N' create function {0}() \r\n RETURNS @t TABLE (stringValue VARCHAR(128))\r\n as \r\nbegin\r\ninsert into @t values(''ee'')\r\n return\r\nend';\r\nend",
function.Name.ToQuotedString());
return generateIfExists;
}
private List <Statement> BuildStatements(string filePath)
{
var statements = _planParser.GetStatements(File.ReadAllText(filePath));
var f = new CreateFunctionStatement();
return(statements);
}
private static string BuildIfNotExistsStatementsInlineFunction(CreateFunctionStatement function)
{
var generateIfExists =
string.Format("if object_id('{0}') is null\r\nbegin\r\n execute sp_executeSql N' create function {0}() \r\n RETURNS TABLE as RETURN SELECT 1 as a' \r\nEND;",
function.Name.ToQuotedString());
return generateIfExists;
}
private string GetCallingCode(CreateFunctionStatement function)
{
var callingSelect = new SelectStatement();
var spec = (callingSelect.QueryExpression = new QuerySpecification()) as QuerySpecification;
spec.FromClause = new FromClause();
spec.FromClause.TableReferences.Add(new SchemaObjectFunctionTableReference()
{
SchemaObject = function.Name
});
spec.SelectElements.Add(new SelectStarExpression());
return ScriptDom.GenerateTSql(spec);
}
private string GetCallingCode(CreateFunctionStatement function)
{
var callingSelect = new SelectStatement();
var spec = (callingSelect.QueryExpression = new QuerySpecification()) as QuerySpecification;
spec.FromClause = new FromClause();
spec.FromClause.TableReferences.Add(new SchemaObjectFunctionTableReference()
{
SchemaObject = function.Name
});
spec.SelectElements.Add(new SelectStarExpression());
return(ScriptDom.GenerateTSql(spec));
}
public override void ExplicitVisit(CreateFunctionStatement node)
{
var target = new AlterFunctionStatement();
target.Name = node.Name;
target.Parameters.AddRange(node.Parameters);
target.ReturnType = node.ReturnType;
target.Options.AddRange(node.Options);
target.StatementList = node.StatementList;
target.MethodSpecifier = node.MethodSpecifier;
target.OrderHint = node.OrderHint;
//base.ExplicitVisit(node);
this._ReplaceSource = node;
this._ReplaceTarget = target;
}
public override void Visit(CreateFunctionStatement node)
{
base.Visit(node);
_statements.Add(node);
}
public override void ExplicitVisit(CreateFunctionStatement node)
{
IsFunction = true;
}
public override void Visit(CreateFunctionStatement node) { this.action(node); }
protected override void VisitCreateFunctionStatement(CreateFunctionStatement statement)
{
VisitProcedureAndBodyParameters(statement, false, statement.CheckIfNotExists);
}
public override void ExplicitVisit(CreateFunctionStatement node)
{
FunctionName = node.Name.BaseIdentifier.Value;
ParseParameters(node.Parameters);
base.ExplicitVisit(node);
}
protected override void VisitCreateFunctionStatement(CreateFunctionStatement statement)
{
this.CreateOrReplaceFunction(statement);
}
public override void ExplicitVisit(CreateFunctionStatement fragment)
{
_fragments.Add(fragment);
}
public override void ExplicitVisit(CreateFunctionStatement func)
{
if (IsSupportedForCurrentType(func.GetType()))
{
Name = func.Name;
}
}
private static TestFunction LoadMultiStatementTableValuedFunction(string schemaName, string functionName, CreateFunctionStatement stmt_CreateFunction)
{
if (stmt_CreateFunction.ReturnType is TableValuedFunctionReturnType tvfReturnType)
{
TestFunction function = new TestFunction(schemaName, functionName, "", (x) =>
{
});
//tvfReturnType.DeclareTableVariableBody.VariableName
var tableBody = tvfReturnType.DeclareTableVariableBody;
foreach (ColumnDefinition col in tableBody.Definition.ColumnDefinitions)
{
string columnName = col.ColumnIdentifier.Dequote();
var columnDbType = ProcedureGenerator.ResolveToDbDataType(col.DataType);
function.AddFunctionColumn(columnName, columnDbType, true);
}
return(function);
}
throw new NotImplementedException(stmt_CreateFunction.ReturnType.WhatIsThis());
}
public override void ExplicitVisit(CreateFunctionStatement node)
{
this.HandleFunction(node);
}
protected abstract void VisitCreateFunctionStatement(CreateFunctionStatement statement);
private List<Statement> BuildStatements(string filePath)
{
var statements = _planParser.GetStatements(File.ReadAllText(filePath));
var f = new CreateFunctionStatement();
return statements;
}
private static string ChangeCreateToAlter(CreateFunctionStatement function, string wholeScript)
{
//get part of script we are interested in...
var subScript = wholeScript.Substring(function.StartOffset, function.FragmentLength);
IList<ParseError> errors;
var fragment = new TSql130Parser(false).Parse(new StringReader(subScript), out errors);
bool haveCreate = false;
var output = new StringBuilder();
foreach (var token in fragment.ScriptTokenStream)
{
if (!haveCreate && token.TokenType == TSqlTokenType.Create)
{
var alterToken = new TSqlParserToken(TSqlTokenType.Alter, "alter");
output.Append(alterToken.Text);
haveCreate = true;
continue;
}
output.Append(token.Text);
}
return output.ToString();
}
private static void LoadFunctionOutputColumns(TestSchema schema, TestFunction function_source, CreateFunctionStatement stmt_CreateFunction)
{
ProcedureGenerator.LoadFunctionOutputColumns(schema, function_source, function_source.FunctionBodyScript, (col) =>
{
function_source.AddFunctionColumn(col.OutputColumnName, col.ColumnType.ColumnDbType, col.ColumnType.AllowNull);
});
}
public override void Visit(CreateFunctionStatement node)
{
Functions.Add(node);
}
public override void Visit(CreateFunctionStatement node)
{
Statements.Add(node);
}
protected override void VisitCreateFunctionStatement(CreateFunctionStatement statement)
{
throw new NotImplementedException();
}
