public override void Visit(TSqlBatch node)
{
var variableVisitor = new VariableVisitor();
node.Accept(variableVisitor);
VariableValues = variableVisitor.VariableValues;
}
public override void ExplicitVisit(TSqlBatch node)
{
if (node.Statements.Count > 0)
{
if (node.Statements[0] is PrintStatement print)
{
if (print.Expression is StringLiteral literal)
{
if (literal.Value.StartsWith("initializing", StringComparison.InvariantCultureIgnoreCase))
{
_buffer.AppendLine("do $$");
_buffer.AppendLine("begin");
for (int index = 1; index < node.Statements.Count; ++index)
{
node.Statements[index].Accept(this);
}
_buffer.AppendLine("end");
_buffer.AppendLine("$$ language plpgsql;");
return;
}
}
}
}
base.ExplicitVisit(node);
}
public string Convert(CSchema schema)
{
string[] parts = { schema.SchemaName };
var createSchemaStatement = new CreateSchemaStatement();
createSchemaStatement.Name = new Identifier {
Value = schema.SchemaName
};
//generate DDL
var script = new TSqlScript();
var batch = new TSqlBatch();
script.Batches.Add(batch);
batch.Statements.Add(createSchemaStatement);
var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
var existing = dacpacModel.GetObject(Schema.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
if (existing != null)
{
return(existing.GetScript());
}
dacpacModel.AddObjects(script);
existing = dacpacModel.GetObject(Schema.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
return(existing.GetScript());
}
/// <summary>
/// This is the entry point into the Visitor pattern and where all the work occurs
/// Note we are explicitly triggering on SqlStmt node type
/// This is because we only need to parameterize the search conditions
/// </summary>
/// <param name="node">This is the TsqlFrament sent to us from the Vistor.Accept method</param>
public override void ExplicitVisit(TSqlBatch node)
{
// First determine which tokens belongs to our where clause
int index = node.FirstTokenIndex;
int end = node.LastTokenIndex;
// Now process each token in an appropriate manner
while (index <= end)
{
// Use the TokenType to decide what processing needs to occur
TSqlParserToken token = node.ScriptTokenStream[index];
// Emit the token, and if necessary add a parameter to the parameters collection
EmitToken(node, token, index);
// Until we have processed all the tokens associated with the where clause
index++;
}
// Now we must emit the rest of the tokens to get our entire T-SQL script
if (reparse)
{
// Just a bonus - part of the ScriptDom namespace; side effect it revalidates our new code
FormatSQL();
}
// let the base class finish up
base.ExplicitVisit(node);
}
private Task ExecuteSqlAsync(TSqlBatch sqlBatch)
{
using (var command = _connection.CreateCommand())
{
command.CommandText = Generator.GetSql(sqlBatch);
return(command.ExecuteNonQueryAsync());
}
}
private TSqlFragment GetTablesScript(Entity[] entities, string namespaceName)
{
var batch = new TSqlBatch();
foreach (var entity in entities)
{
batch.Statements.Add(GetTableScript(entity, namespaceName));
}
return(batch);
}
public static TSqlBatch Batch(params TSqlStatement[] statements)
{
TSqlBatch batch = new TSqlBatch();
foreach (var statement in statements)
{
batch.Statements.Add(statement);
}
return(batch);
}
public string Convert(CTable table)
{
string[] parts = { table.Schema.SchemaName, table.TableName };
var createTable = new CreateTableStatement();
///set schema and table name
createTable.SchemaObjectName = new SchemaObjectName();
createTable.SchemaObjectName.Identifiers.Add(new Identifier {
Value = table.Schema.SchemaName
});
createTable.SchemaObjectName.Identifiers.Add(new Identifier {
Value = table.TableName
});
//add columns
createTable.Definition = new TableDefinition();
foreach (var col in table.Column)
{
var dataType = new SqlDataTypeReference {
SqlDataTypeOption = GetSqlDataTypeOption(col.ColumnType)
};
if (col.ColumnLength > 0)
{
dataType.Parameters.Add(new IntegerLiteral {
Value = col.ColumnLength.ToString()
});
}
var column = new ColumnDefinition
{
ColumnIdentifier = new Identifier {
Value = col.ColumnName
},
DataType = dataType
};
createTable.Definition.ColumnDefinitions.Add(column);
}
//generate DDL
var script = new TSqlScript();
var batch = new TSqlBatch();
script.Batches.Add(batch);
batch.Statements.Add(createTable);
var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
dacpacModel.AddObjects(script);
var existing = dacpacModel.GetObject(Table.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
return(existing.GetScript());
}
public override void Visit(TSqlBatch node)
{
var childTransactionVisitor = new ChildTransactionVisitor();
node.Accept(childTransactionVisitor);
if (childTransactionVisitor.TransactionLists.Exists(x => x.Commit == null))
{
var failed_transaction = childTransactionVisitor.TransactionLists.Where(x => x.Commit == null).First();
errorCallback(RULE_NAME, RULE_TEXT, failed_transaction.Begin.StartLine, GetColumnNumber(failed_transaction));
}
}
public override void Visit(TSqlBatch node)
{
var childTransactionVisitor = new ChildTransactionVisitor();
node.Accept(childTransactionVisitor);
foreach (var transaction in childTransactionVisitor.TransactionLists)
{
var childInsertUpdateQueryVisitor = new ChildInsertUpdateQueryVisitor(transaction);
node.Accept(childInsertUpdateQueryVisitor);
if (childInsertUpdateQueryVisitor.DatabasesUpdated.Count > 1)
{
errorCallback(
RULE_NAME,
RULE_TEXT,
transaction.Begin.StartLine,
transaction.Begin.StartColumn);
}
}
}
/// <summary>
/// This is the logic for how to handle the tokens - i.e. copy to output stream, add to parameter collection etc.
/// </summary>
/// <param name="node">This is the TsqlFrament sent to the ExplicitVisit method</param>
/// <param name="token">This is the current token that we are processing</param>
/// <param name="index">The index to process</param>
//protected void EmitToken(WhereClause node, TSqlParserToken token, int index)
protected void EmitToken(TSqlBatch node, TSqlParserToken token, int index)
{
switch (token.TokenType)
{
// for the majority of TokenTypes we just pass the token to our StringBuilder for inclusion
default:
SqlStmt.Append(node.ScriptTokenStream[index].Text);
break;
// for those token types that may need to be parameterized we capture name, data type and value for the parameters collection
case TSqlTokenType.AsciiStringLiteral:
case TSqlTokenType.Real:
case TSqlTokenType.Integer:
case TSqlTokenType.Money:
case TSqlTokenType.Numeric:
case TSqlTokenType.UnicodeStringLiteral:
// We just use a simple naming scheme - i.e. @p1, @p2 and so on
string p = "@p" + (++parameterNumber).ToString();
SqlStmt.Append(p);
// Now create the entry in the SqlCommand Parameters collection
AddToParameterCollection(token, parameterNumber);
break;
}
}
public override void ExplicitVisit(TSqlBatch fragment)
{
_fragments.Add(fragment);
}
protected override object InternalVisit(TSqlBatch node)
{
return(VisitCollection <SQLExecutionResult>(node.Statements).ToArray());
}
public void WriteBatch(TSqlBatch batch)
{
_generator.GenerateScript(batch, _writer);
WriteFragment(GO());
}
public override void Visit(TSqlBatch node) { this.action(node); }
public string GetSql(TSqlBatch sqlBatch)
{
_generator.GenerateScript(sqlBatch, out string sql);
return(sql);
}
protected override void OnExecute(DeploymentPlanContributorContext context)
{
// Obtain the first step in the Plan from the provided context
int batchId = 0;
DeploymentStep nextStep = context.PlanHandle.Head;
BeginPreDeploymentScriptStep beforePreDeploy = null;
while (nextStep != null)
{
DeploymentStep currentStep = nextStep;
nextStep = currentStep.Next;
if (currentStep is BeginPreDeploymentScriptStep)
{
beforePreDeploy = (BeginPreDeploymentScriptStep)currentStep;
continue;
}
if (currentStep is SqlPrintStep)
{
continue;
}
if (currentStep is BeginPostDeploymentScriptStep)
{
break;
}
if (beforePreDeploy == null)
{
continue;
}
DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep;
if (domStep == null)
{
continue;
}
TSqlScript script = domStep.Script as TSqlScript;
if (script == null)
{
continue;
}
// Loop through all the batches in the script for this step. All the statements
// in the batch will be enclosed in an if statement that will check the
// table to ensure that the batch has not already been executed
TSqlObject sqlObject;
string stepDescription;
GetStepInfo(domStep, out stepDescription, out sqlObject);
int batchCount = script.Batches.Count;
for (int batchIndex = 0; batchIndex < batchCount; batchIndex++)
{
// Create the if statement that will contain the batch's contents
IfStatement ifBatchNotExecutedStatement = CreateIfNotExecutedStatement(batchId);
BeginEndBlockStatement statementBlock = new BeginEndBlockStatement();
ifBatchNotExecutedStatement.ThenStatement = statementBlock;
statementBlock.StatementList = new StatementList();
TSqlBatch batch = script.Batches[batchIndex];
int statementCount = batch.Statements.Count;
// Loop through all statements in the batch, embedding those in an sp_execsql
// statement that must be handled this way (schemas, stored procedures,
// views, functions, and triggers).
for (int statementIndex = 0; statementIndex < statementCount; statementIndex++)
{
TSqlStatement smnt = batch.Statements[statementIndex];
if (IsStatementEscaped(sqlObject))
{
// "escape" this statement by embedding it in a sp_executesql statement
string statementScript;
domStep.ScriptGenerator.GenerateScript(smnt, out statementScript);
ExecuteStatement spExecuteSql = CreateExecuteSql(statementScript);
smnt = spExecuteSql;
}
statementBlock.StatementList.Statements.Add(smnt);
}
// Add an insert statement to track that all the statements in this
// batch were executed. Turn on nocount to improve performance by
// avoiding row inserted messages from the server
string batchDescription = string.Format(CultureInfo.InvariantCulture,
"{0} batch {1}", stepDescription, batchIndex);
PredicateSetStatement noCountOff = new PredicateSetStatement();
noCountOff.IsOn = false;
noCountOff.Options = SetOptions.NoCount;
PredicateSetStatement noCountOn = new PredicateSetStatement();
noCountOn.IsOn = true;
noCountOn.Options = SetOptions.NoCount;
InsertStatement batchCompleteInsert = CreateBatchCompleteInsert(batchId, batchDescription);
statementBlock.StatementList.Statements.Add(noCountOn);
statementBlock.StatementList.Statements.Add(batchCompleteInsert);
statementBlock.StatementList.Statements.Add(noCountOff);
// Remove all the statements from the batch (they are now in the if block) and add the if statement
// as the sole statement in the batch
batch.Statements.Clear();
batch.Statements.Add(ifBatchNotExecutedStatement);
// Next batch
batchId++;
}
}
// if we found steps that required processing, set up a temporary table to track the work that you are doing
if (beforePreDeploy != null)
{
// Declare a SqlCmd variables.
//
// CompletedBatches variable - defines the name of the table in tempdb that will track
// all the completed batches. The temporary table's name has the target database name and
// a guid embedded in it so that:
// * Multiple deployment scripts targeting different DBs on the same server
// * Failed deployments with old tables do not conflict with more recent deployments
//
// TotalBatchCount variable - the total number of batches surrounded by if statements. Using this
// variable pre/post deployment scripts can also use the CompletedBatches table to make their
// script rerunnable if there is an error during execution
StringBuilder sqlcmdVars = new StringBuilder();
sqlcmdVars.AppendFormat(CultureInfo.InvariantCulture, CompletedBatchesSqlCmd,
context.Options.TargetDatabaseName, Guid.NewGuid().ToString("D"));
sqlcmdVars.AppendLine();
sqlcmdVars.AppendFormat(CultureInfo.InvariantCulture, TotalBatchCountSqlCmd, batchId);
DeploymentScriptStep completedBatchesSetVarStep = new DeploymentScriptStep(sqlcmdVars.ToString());
base.AddBefore(context.PlanHandle, beforePreDeploy, completedBatchesSetVarStep);
// Create the temporary table we will use to track the work that we are doing
DeploymentScriptStep createStatusTableStep = new DeploymentScriptStep(CreateCompletedBatchesTable);
base.AddBefore(context.PlanHandle, beforePreDeploy, createStatusTableStep);
}
// Cleanup and drop the table
// DeploymentScriptStep dropStep = new DeploymentScriptStep(DropCompletedBatchesTable);
// base.AddAfter(context.PlanHandle, context.PlanHandle.Tail, dropStep);
}
public void WriteBatch(TSqlBatch batch)
{
_generator.GenerateScript(batch, _writer);
}
