protected string GenerateScript(TSqlFragment fragment)
{
string script;
var generator = new Sql120ScriptGenerator(SavedSettings.Get().GeneratorOptions);
generator.GenerateScript(fragment, out script);
return script;
}
public static string FormatTSql(this string tSql, string sqlHash)
{
if (string.IsNullOrWhiteSpace(tSql))
return string.Empty;
string formattedTSql;
if (_formattedSql.TryGetValue(sqlHash, out formattedTSql))
{
return formattedTSql;
}
var sqlFragment = SqlFragmentProvider.GetSqlFragment(tSql, sqlHash);
if (sqlFragment == null)
{
return tSql;
}
var scriptGenerator = new Sql120ScriptGenerator(new SqlScriptGeneratorOptions
{
SqlVersion = SqlVersion.Sql120,
KeywordCasing = KeywordCasing.Uppercase
});
scriptGenerator.GenerateScript(sqlFragment, out formattedTSql);
formattedTSql = !string.IsNullOrWhiteSpace(formattedTSql) ? formattedTSql.Trim() : tSql;
_formattedSql.Add(sqlHash, formattedTSql);
return formattedTSql;
}
private string ModifyScript(CreateTableStatement statement, string originalScript, string modifiedScript)
{
var oldScriptBlock = originalScript.Substring(statement.StartOffset, statement.FragmentLength);
var comments = GetComments(oldScriptBlock);
var generator = new Sql120ScriptGenerator(SavedSettings.Get().GeneratorOptions);
string newScriptBlock;
generator.GenerateScript(statement, out newScriptBlock);
if (string.IsNullOrEmpty(comments))
modifiedScript = modifiedScript.Replace(oldScriptBlock, newScriptBlock);
else
modifiedScript = modifiedScript.Replace(oldScriptBlock, newScriptBlock + "\r\n--These comments were saved after refactoring this table...\r\n" + comments);
return modifiedScript;
}
/// <summary>
/// Analyzes the SQL text asynchronously.
/// </summary>
/// <param name="inputScript">The input script.</param>
/// <returns></returns>
public async Task <ParserResults> AnalyzeSqlTextAsync(string inputScript)
{
ParserResults results = new ParserResults();
_sqlParser = CreateSqlParser();
Sql120ScriptGenerator _scriptGen;
SqlScriptGeneratorOptions options = new SqlScriptGeneratorOptions();
options.SqlVersion = _sqlVersion;
options.KeywordCasing = KeywordCasing.Uppercase;
_scriptGen = new Sql120ScriptGenerator(options);
TSqlFragment fragment;
IList <ParseError> errors;
using (StringReader sr = new StringReader(inputScript))
{
fragment = _sqlParser.Parse(sr, out errors);
}
if (errors.Count > 0)
{
results.ParsingExceptionDetail = string.Join(",", errors.Select(x => x.Message));
}
else
{
TSqlScript sqlScript = fragment as TSqlScript;
if (sqlScript != null)
{
foreach (TSqlBatch batch in sqlScript.Batches)
{
foreach (TSqlStatement statement in batch.Statements)
{
AnalyzeTsqlStatement(results, statement);
}
}
}
}
return(await Task.FromResult(results));
}
public string Convert(CTable table)
{
var converter = new CTableToCreateTableStatementConverter();
var createTable = converter.Convert(table);
string tableScript;
var scriptGen = new Sql120ScriptGenerator();
scriptGen.GenerateScript(createTable, out tableScript);
/*
* //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);
*
* string[] parts = { table.Schema.SchemaName, table.TableName };
*
* var existing = dacpacModel.GetObject(Table.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
* var tableScript = existing.GetScript();
*/
var codeWriter = new CodeWriter();
codeWriter.WriteLine(tableScript);
foreach (var column in table.Column)
{
if (!string.IsNullOrEmpty(column.ColumnDescription))
{
codeWriter.WriteLine();
codeWriter.WriteLine("GO");
codeWriter.WriteLine();
codeWriter.WriteLine(GetExtendedPropertyScript(table.Schema.SchemaName, table.TableName,
column.ColumnName, column.ColumnDescription));
}
}
return(codeWriter.ToString());
;
}
private static SqlScriptGenerator GetGenerator(GenerationOptions options)
{
SqlScriptGenerator generator = null;
switch (options.SqlVersion)
{
case SqlVersion.Sql80:
generator = new Sql80ScriptGenerator();
break;
case SqlVersion.Sql100:
generator = new Sql100ScriptGenerator();
break;
case SqlVersion.Sql110:
generator = new Sql110ScriptGenerator();
break;
case SqlVersion.Sql120:
generator = new Sql120ScriptGenerator();
break;
case SqlVersion.Sql130:
generator = new Sql130ScriptGenerator();
break;
case SqlVersion.Sql140:
generator = new Sql140ScriptGenerator();
break;
case SqlVersion.Sql150:
generator = new Sql150ScriptGenerator();
break;
default:
generator = new Sql90ScriptGenerator();
break;
}
generator.Options.Apply(options);
return(generator);
}
private string GetTableTypeBody(string sqlText)
{
var createTableTypeBody = new CodeWriter();
var parser = new TSql120Parser(false);
var statementList = new StatementList();
IList <ParseError> errors;
var script2 = parser.Parse(new StringReader(sqlText), out errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
var scriptGen = new Sql120ScriptGenerator();
foreach (var batch2 in script2.Batches)
{
foreach (var statement in batch2.Statements)
{
var createTypeTableStatement = statement as CreateTypeTableStatement;
if (createTypeTableStatement == null)
{
continue;
}
string scriptOut;
scriptGen.GenerateScript(createTypeTableStatement, out scriptOut);
createTableTypeBody.WriteLine(scriptOut);
}
}
return(createTableTypeBody.ToString());
}
public static SplitResult Split(string t)
{
var result = new SplitResult();
var parser = new TSql120Parser(false);
var sg = new Sql120ScriptGenerator();
using (StringReader sr = new StringReader(t))
{
IList <ParseError> err = new List <ParseError>();
var root = parser.Parse(sr, out err);
if (err.Count() != 0)
{
result.errors = err.ToArray();
result.queries = new string[0];
}
result.queries = GetStatements(root).ToArray();
}
return(result);
}
private void button1_Click(object sender, EventArgs e)
{
TextReader rdr = new StringReader(textBox1.Text);
IList<ParseError> errors = null;
TSql120Parser parser = new TSql120Parser(true);
TSqlFragment tree = parser.Parse(rdr, out errors);
foreach(ParseError err in errors)
{
Console.WriteLine(err.Message);
}
Sql120ScriptGenerator scrGen = new Sql120ScriptGenerator();
string formattedSQL = null;
scrGen.GenerateScript(tree, out formattedSQL);
textBox2.Text = formattedSQL;
rdr.Dispose();
}
protected override string GetReplacementScript(string script)
{
var parser = new TSql120Parser(true);
IList<ParseError> errors;
var fragment = parser.Parse(new StringReader(script), out errors);
var visitor = new IndexVisitior();
fragment.Accept(visitor);
var newScript = script;
foreach (var create in visitor.Creates)
{
var newCreate = GenerateCreateWithEditionCheck(create);
var generator = new Sql120ScriptGenerator();
string newStatement;
generator.GenerateScript(newCreate, out newStatement);
newScript = newScript.Replace(
script.Substring(fragment.StartOffset, fragment.FragmentLength), newStatement);
}
return newScript;
}
private string ModifyScript(CreateTableStatement statement, string originalScript, string modifiedScript)
{
var oldScriptBlock = originalScript.Substring(statement.StartOffset, statement.FragmentLength);
var comments = GetComments(oldScriptBlock);
var generator = new Sql120ScriptGenerator(SavedSettings.Get().GeneratorOptions);
string newScriptBlock;
generator.GenerateScript(statement, out newScriptBlock);
if (string.IsNullOrEmpty(comments))
{
modifiedScript = modifiedScript.Replace(oldScriptBlock, newScriptBlock);
}
else
{
modifiedScript = modifiedScript.Replace(oldScriptBlock, newScriptBlock + "\r\n--These comments were saved after refactoring this table...\r\n" + comments);
}
return(modifiedScript);
}
public static string GetScript(this MergeStatement source)
{
var script = "";
var generator =
new Sql120ScriptGenerator(new SqlScriptGeneratorOptions
{
IncludeSemicolons = true,
AlignClauseBodies = true,
AlignColumnDefinitionFields = true,
AsKeywordOnOwnLine = true,
MultilineInsertSourcesList = true,
MultilineInsertTargetsList = true,
MultilineSelectElementsList = true,
NewLineBeforeOpenParenthesisInMultilineList = true
});
generator.GenerateScript(source, out script);
if (!script.EndsWith(";"))
{
script = script + ";";
}
return script;
}
protected override string GetReplacementScript(string script)
{
var parser = new TSql120Parser(true);
IList <ParseError> errors;
var fragment = parser.Parse(new StringReader(script), out errors);
var visitor = new IndexVisitior();
fragment.Accept(visitor);
var newScript = script;
foreach (var create in visitor.Creates)
{
var newCreate = GenerateCreateWithEditionCheck(create);
var generator = new Sql120ScriptGenerator();
string newStatement;
generator.GenerateScript(newCreate, out newStatement);
newScript = newScript.Replace(
script.Substring(fragment.StartOffset, fragment.FragmentLength), newStatement);
}
return(newScript);
}
public static string GetScript(this MergeStatement source)
{
var script = "";
var generator =
new Sql120ScriptGenerator(new SqlScriptGeneratorOptions
{
IncludeSemicolons = true,
AlignClauseBodies = true,
AlignColumnDefinitionFields = true,
AsKeywordOnOwnLine = true,
MultilineInsertSourcesList = true,
MultilineInsertTargetsList = true,
MultilineSelectElementsList = true,
NewLineBeforeOpenParenthesisInMultilineList = true
});
generator.GenerateScript(source, out script);
if (!script.EndsWith(";"))
{
script = script + ";";
}
return(script);
}
/// <summary>
/// Format SQL off one line to indented into many lines
/// </summary>
/// <param name="query">SQL statement to format</param>
/// <returns>Formatted SQL</returns>
/// <remarks>
/// Can be used to document or for debugging.
/// </remarks>
public static string Format(string query)
{
var parser = new TSql120Parser(false);
var parsedQuery = parser.Parse(new StringReader(query), out var errors);
if (errors.Count > 0)
{
ParseErrors = errors;
}
var generator = new Sql120ScriptGenerator(new SqlScriptGeneratorOptions()
{
KeywordCasing = KeywordCasing.Uppercase,
IncludeSemicolons = true,
NewLineBeforeFromClause = true,
NewLineBeforeOrderByClause = true,
NewLineBeforeWhereClause = true,
AlignClauseBodies = false
});
generator.GenerateScript(parsedQuery, out var formattedQuery);
return(formattedQuery);
}
public string Convert(CStoredProcedure storedProcedure)
{
var converter = new CStoredProcedureToCreateProcedureStatementConverter();
var createStoredProcedure = converter.Convert(storedProcedure);
var snakeCaseVisitor = new SnakeCaseVisitor();
createStoredProcedure.Accept(snakeCaseVisitor);
var codeWriter = new CodeWriter();
codeWriter.WriteLine($@"CREATE OR REPLACE FUNCTION {storedProcedure.Schema.SchemaName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)}.{storedProcedure.StoredProcedureName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)} ");
codeWriter.WriteLine($@"(");
bool first = true;
foreach (var parameter in storedProcedure.Parameter)
{
if (!first)
{
codeWriter.Write(", ");
}
first = false;
var parameterType = string.Empty;
if (parameter.ParameterTypeIsUserDefined)
{
if (parameter.ParameterTypeRaw == "sysname")
{
//fixup. Todo: implement a better way
parameterType = SqlMapper.NpgsqlDbTypeToPostgres(NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.ParameterLength = 128;
}
else
{
//todo: need schema included
parameterType = $"{parameter.ParameterTypeRawSchema.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)}.{parameter.ParameterTypeRaw.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)} []";
}
}
else
{
parameterType = SqlMapper.NpgsqlDbTypeToPostgres(SqlMapper.DbTypeToNpgsqlDbType(parameter.ParameterType));
}
//todo: remove p_ prefix. Too much UI change, better to manually fix the stored procs
var parameterName = "p_" + parameter.ParameterName;
codeWriter.Write($@"{parameterName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)} {parameterType} ");
if (parameter.DoesNeedLength())
{
codeWriter.Write($"({parameter.ParameterLength})");
}
}
codeWriter.WriteLine();
codeWriter.WriteLine($@")");
if (storedProcedure.ResultSet.Count > 0)
{
codeWriter.Write($@"RETURNS TABLE (");
{
var first2 = true;
foreach (var resultCol in storedProcedure.ResultSet)
{
if (!first2)
{
codeWriter.WriteLine(",");
}
first2 = false;
codeWriter.Write($"{resultCol.ColumnName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)}");
codeWriter.Write($" {SqlMapper.NpgsqlDbTypeToPostgres(SqlMapper.DbTypeToNpgsqlDbType(resultCol.ColumnType))}");
if (resultCol.DoesNeedLength())
{
codeWriter.Write($"({resultCol.ColumnLength})");
}
if (!resultCol.IsNullable)
{
codeWriter.Write(" NOT NULL");
}
}
}
codeWriter.Write($@") ");
}
else
{
codeWriter.Write("RETURNS void ");
}
codeWriter.WriteLine($@"AS $func$");
codeWriter.WriteLine($@"BEGIN");
codeWriter.Indent();
if (storedProcedure.ResultSet.Count > 0)
{
codeWriter.WriteLine($@"RETURN QUERY");
}
codeWriter.WriteLine("--TODO: Manually convert the sql below to Postgresql");
if (storedProcedure.ResultSet.Count > 0)
{
codeWriter.Write("SELECT ");
var first3 = true;
var sampleDataService = new SamplePostgresDataService();
foreach (var resultCol in storedProcedure.ResultSet)
{
if (!first3)
{
codeWriter.Write(",");
}
first3 = false;
var npgsqlType = SqlMapper.DbTypeToNpgsqlDbType(resultCol.ColumnType);
var postgresType = SqlMapper.NpgsqlDbTypeToPostgres(npgsqlType);
codeWriter.Write($"CAST ({sampleDataService.GetSampleData(npgsqlType, resultCol.ColumnLength)} AS {postgresType} ) AS {resultCol.ColumnName.WrapReservedAndSnakeCase(DataStoreTypes.Postgres, storedProcedure.ConvertToSnakeCase)}");
}
codeWriter.WriteLine(";");
}
codeWriter.WriteLine("/*");
//codeWriter.WriteLine(storedProcedure.StoredProcedureBody);
var scriptGen = new Sql120ScriptGenerator();
foreach (var statement2 in createStoredProcedure.StatementList.Statements)
{
string scriptOut;
scriptGen.GenerateScript(statement2, out scriptOut);
codeWriter.WriteLine(scriptOut);
}
codeWriter.WriteLine("*/");
codeWriter.Unindent();
codeWriter.WriteLine($@"END");
codeWriter.WriteLine($@"$func$ LANGUAGE plpgsql;");
return(codeWriter.ToString());
}
private List <KTable> CreateSqlTablesInDb(KDataStoreProject kDataStoreProject, string connectionString, string sqlText)
{
var tablesNames = new List <KTable>();
var scriptGen = new Sql120ScriptGenerator();
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
var parser = new TSql120Parser(false);
var script2 = parser.Parse(new StringReader(sqlText), out var errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
foreach (var batch2 in script2.Batches)
{
foreach (var statement in batch2.Statements)
{
var createTableStatement = statement as CreateTableStatement;
var alterTableStatement = statement as AlterTableStatement;
if (createTableStatement == null && alterTableStatement == null)
{
continue;
}
string viewSchemaName = string.Empty;
if (createTableStatement != null)
{
viewSchemaName = createTableStatement.SchemaObjectName.SchemaIdentifier.Value;
}
else if (alterTableStatement != null)
{
viewSchemaName = alterTableStatement.SchemaObjectName.SchemaIdentifier.Value;
}
var createSchemaCommand = new SqlCommand(
$@" IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'{viewSchemaName}')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA [{viewSchemaName}]'
END", sqlConnection);
createSchemaCommand.ExecuteNonQuery();
scriptGen.GenerateScript(statement, out var scriptOut);
var sqlCommand = new SqlCommand(scriptOut, sqlConnection);
sqlCommand.ExecuteNonQuery();
if (createTableStatement != null)
{
var oldKTable = kDataStoreProject.OldTable.FirstOrDefault(t =>
t.Schema.SchemaName == createTableStatement.SchemaObjectName.SchemaIdentifier.Value &&
t.TableName == createTableStatement.SchemaObjectName.BaseIdentifier.Value);
//copy some attributes
var newKTable = new KTable
{
Schema =
new CSchema
{
SchemaName = createTableStatement.SchemaObjectName.SchemaIdentifier.Value
},
TableName =
createTableStatement.SchemaObjectName.BaseIdentifier.Value,
TableText = scriptOut
};
if (oldKTable != null)
{
newKTable.DerivedFrom = oldKTable.DerivedFrom;
}
tablesNames.Add(newKTable);
}
else if (alterTableStatement != null)
{
//assumes ALTER TABLE come before CREATE TABLE
var existingTable = tablesNames.Single(t =>
t.Schema.SchemaName == alterTableStatement.SchemaObjectName.SchemaIdentifier.Value &&
t.TableName == alterTableStatement.SchemaObjectName.BaseIdentifier.Value);
existingTable.TableText += Environment.NewLine + "GO" + Environment.NewLine + Environment.NewLine;
existingTable.TableText += scriptOut;
existingTable.TableText += Environment.NewLine + "GO" + Environment.NewLine + Environment.NewLine;
}
}
}
sqlConnection.Close();
}
return(tablesNames);
}
protected IList<TSqlParserToken> GetTokens(TSqlFragment fragment)
{
var generator = new Sql120ScriptGenerator(SavedSettings.Get().GeneratorOptions);
return generator.GenerateTokens(fragment);
}
public KDataStoreProject BuildSqlMeta(string connectionString, string outputRootPath,
KDataStoreProject dataStoreProject)
{
dataStoreProject.ClearSqlMeta(); //clear what was generated during user config, going to regenerate
var stopWatch = Stopwatch.StartNew();
var connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
var timeString = DateTime.Now.ToString("yyyyMMddHHmmss");
var newDbName = $"WorkDb{timeString}";
connectionStringBuilder.InitialCatalog = "master";
connectionStringBuilder.IntegratedSecurity = true;
//connectionStringBuilder.UserID = "fillthisin";
//connectionStringBuilder.Password = "******";
connectionStringBuilder.ConnectTimeout = 60;
var scriptGen = new Sql120ScriptGenerator();
using (var sqlConnection = new SqlConnection(connectionStringBuilder.ConnectionString))
{
sqlConnection.Open();
var createDatabaseStatement =
new CreateDatabaseStatement {
DatabaseName = new Identifier {
Value = newDbName
}
};
string createDatabaseScriptOut;
scriptGen.GenerateScript(createDatabaseStatement, out createDatabaseScriptOut);
var sqlCommandCreateDatabase = new SqlCommand(createDatabaseScriptOut, sqlConnection);
sqlCommandCreateDatabase.ExecuteNonQuery();
sqlConnection.Close();
}
connectionStringBuilder.InitialCatalog = newDbName;
if (!string.IsNullOrEmpty(dataStoreProject.SqlTableText))
{
var tableNames = CreateSqlTablesInDb(dataStoreProject, connectionStringBuilder.ConnectionString,
dataStoreProject.SqlTableText);
foreach (var tablePair in tableNames)
{
_sqlServerTableReader.ConnectionString = connectionStringBuilder.ConnectionString;
var dataReader = _sqlServerTableReader.Read(tablePair.Schema.SchemaName, tablePair.TableName);
var foreignKeyData = _sqlServerTableReader.ReadForeignKeys(tablePair.Schema.SchemaName, tablePair.TableName);
var table2 = _sqlServerTableToCTableConverter.Convert(dataReader, foreignKeyData);
table2.TableText = tablePair.TableText;
table2.DatabaseType = dataStoreProject.DataStoreType;
table2.ConvertToSnakeCase = dataStoreProject.ConvertToSnakeCase;
tablePair.GeneratedTable = table2;
dataStoreProject.Table.Add(tablePair);
}
_sqlServerTableToCTableConverter.FixupForeignKeyTables(dataStoreProject.Table.Select(kt => kt.GeneratedTable));
foreach (var tablePair in tableNames)
{
tablePair.GeneratedTable.DerivedFrom = tablePair.DerivedFrom;
}
}
if (!string.IsNullOrEmpty(dataStoreProject.MockSqlViewText))
{
var mockViewNames = CreateMockSqlViewsInDb(connectionStringBuilder.ConnectionString,
dataStoreProject.MockSqlViewText);
foreach (var viewPair in mockViewNames)
{
var dataReader = _sqlServerViewReader.Read(connectionStringBuilder.ConnectionString, viewPair.ViewName, viewPair.ViewText);
var view2 = _sqlServerViewToCViewConverter.Convert(dataReader);
if (dataStoreProject.KickstartCreatedBy)
{
AddCreatedByToView(view2);
}
if (dataStoreProject.KickstartCreatedDateUtc)
{
AddCreatedDateUtcToView(view2);
}
if (dataStoreProject.KickstartModifiedBy)
{
AddModifiedByToView(view2);
}
if (dataStoreProject.KickstartModifiedDateUtc)
{
AddModifiedDateUtcToView(view2);
}
if (dataStoreProject.KickstartLoadDateUtc)
{
AddLoadDateUtcToView(view2);
}
if (dataStoreProject.KickstartEffectiveDateUtc)
{
AddEffectiveDateUtcToView(view2);
}
if (dataStoreProject.KickstartHashDiff)
{
AddHashDiffToView(view2);
}
viewPair.GeneratedView = view2;
dataStoreProject.MockView.Add(viewPair);
}
}
if (!string.IsNullOrEmpty(dataStoreProject.SqlTableTypeText))
{
var tableTypeNames = CreateTableTypesInDb(dataStoreProject, connectionStringBuilder.ConnectionString,
dataStoreProject.SqlTableTypeText);
foreach (var tableType in tableTypeNames)
{
_sqlServerTableTypeReader.ConnectionString = connectionStringBuilder.ConnectionString;
var tt = _sqlServerTableTypeReader.Read(tableType.Schema, tableType.TableTypeName, tableType.TableTypeText);
tt.DerivedFrom = tableType.DerivedFrom as CPart;
tt.DatabaseType = dataStoreProject.DataStoreType;
tt.ConvertToSnakeCase = dataStoreProject.ConvertToSnakeCase;
tableType.GeneratedTableType = tt;
dataStoreProject.TableType.Add(tableType);
}
}
if (!string.IsNullOrEmpty(dataStoreProject.SqlViewText))
{
var viewNames =
CreateSqlViewsInDb(connectionStringBuilder.ConnectionString, dataStoreProject.SqlViewText);
foreach (var viewPair in viewNames)
{
var dataReader = _sqlServerViewReader.Read(connectionStringBuilder.ConnectionString, viewPair.ViewName, viewPair.ViewText);
var view2 = _sqlServerViewToCViewConverter.Convert(dataReader);
view2.DatabaseType = dataStoreProject.DataStoreType;
view2.ConvertToSnakeCase = dataStoreProject.ConvertToSnakeCase;
view2.ViewText = viewPair.ViewText;
viewPair.GeneratedView = view2;
dataStoreProject.View.Add(viewPair);
}
}
if (!string.IsNullOrEmpty(dataStoreProject.SqlFunctionText))
{
var functionNames =
CreateSqlFunctionsInDb(dataStoreProject, connectionStringBuilder.ConnectionString, dataStoreProject.SqlFunctionText);
foreach (var functionPair in functionNames)
{
var function2 = _sqlServerFunctionReader.Read(connectionStringBuilder.ConnectionString, functionPair.Schema.SchemaName, functionPair.FunctionName, functionPair.FunctionText);
//var function2 = _sqlServerFunctionToCFunctionConverter.Convert(dataReader);
functionPair.DatabaseType = dataStoreProject.DataStoreType;
functionPair.ConvertToSnakeCase = dataStoreProject.ConvertToSnakeCase;
functionPair.FunctionText = functionPair.FunctionText;
functionPair.GeneratedFunction = functionPair;
dataStoreProject.Function.Add(functionPair);
}
}
if (!string.IsNullOrEmpty(dataStoreProject.SqlStoredProcedureText))
{
var storedProcedureNames = CreateSqlStoredProcsInDb(dataStoreProject, connectionStringBuilder.ConnectionString,
dataStoreProject.SqlStoredProcedureText);
foreach (var sp in storedProcedureNames)
{
var storedProceure = _sqlServerStoredProcedureReader.Read(connectionStringBuilder.ConnectionString, sp.Schema, sp.StoredProcedureName, sp.StoredProcedureText);
storedProceure.DatabaseType = dataStoreProject.DataStoreType;
storedProceure.DerivedFrom = sp.DerivedFrom as CPart;
storedProceure.ConvertToSnakeCase = dataStoreProject.ConvertToSnakeCase;
sp.GeneratedStoredProcedure = storedProceure;
sp.GeneratedStoredProcedure.StoredProcedureDescription = sp.StoredProcedureDescription;
sp.GeneratedStoredProcedure.GenerateAsEmbeddedQuery = dataStoreProject.GenerateStoredProcAsEmbeddedQuery;
dataStoreProject.StoredProcedure.Add(sp);
}
}
if (dataStoreProject.Query.Any())
{
foreach (var kQuery in dataStoreProject.Query)
{
var reader =
new SqlServerQueryReader
{
ConnectionString = connectionStringBuilder.ConnectionString
};
kQuery.GeneratedQuery = new CQuery();
//databaseProject.Query.Add()
/*
* var query = reader.Read(sp.Schema, sp.StoredProcedureName, sp.StoredProcedureText);
* sp.GeneratedStoredProcedure = storedProceure;
* sp.GeneratedStoredProcedure.StoredProcedureDescription = sp.StoredProcedureDescription;
* databaseProject.StoredProcedure.Add(sp);
*/
}
}
DropWorkDb(connectionStringBuilder, newDbName);
dataStoreProject.ConfigureMetaData();
dataStoreProject.AddSeedData();
ConvertMockViewsToTables(dataStoreProject);
var currentSPs = new List <KStoredProcedure>();
currentSPs.AddRange(dataStoreProject.StoredProcedure);
dataStoreProject.StoredProcedure.Clear();
if (dataStoreProject.KickstartCRUDStoredProcedures)
{
AddInsertUpdateStoredProcedures(dataStoreProject);
}
dataStoreProject.StoredProcedure.AddRange(currentSPs);
if (dataStoreProject.KickstartCRUDStoredProcedures)
{
AddDeleteStoredProcedures(dataStoreProject);
}
dataStoreProject.ConfigureMetaData2();
//todo; clean this up
foreach (var sp in dataStoreProject.StoredProcedure)
{
sp.GeneratedStoredProcedure.ParameterSetName = sp.ParameterSetName;
sp.GeneratedStoredProcedure.ResultSetName = sp.ResultSetName;
sp.GeneratedStoredProcedure.ReturnsMultipleRows = sp.ReturnsMultipleRows;
}
/*
* var protoRpcRefs = new List<SProtoRpcRef>();
* foreach (var protoRpcRef in mSolution.ProtoRpcRef)
* {
*
* protoRpcRefs.Add(protoRpcRef);
* }*/
return(dataStoreProject);
}
public static NormalizedSqlResult GetNormalizedSqlHash(string sql, string sqlHash)
{
NormalizedSqlResult result;
if (_normalizedSqlHashCache.TryGetValue(sqlHash, out result))
{
return result;
}
var scriptFragment = AnalyzeFragmentVisitor(sql, sqlHash, new SqlNormalizerVisitor());
var scriptGenerator = new Sql120ScriptGenerator();
string script;
scriptGenerator.GenerateScript(scriptFragment, out script);
result = new NormalizedSqlResult
{
OriginalSqlHash = sqlHash,
NormalizedSqlHash = script.Trim().ComputeHash()
};
_normalizedSqlHashCache.TryAdd(sqlHash, result);
return result;
}
private List <KTableType> CreateTableTypesInDb(KDataStoreProject kDataStoreProject, string connectionString, string sqlText)
{
var tableTypes = new List <KTableType>();
var scriptGen = new Sql120ScriptGenerator();
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
var parser = new TSql120Parser(false);
var script2 = parser.Parse(new StringReader(sqlText), out var errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
foreach (var batch2 in script2.Batches)
{
foreach (var statement in batch2.Statements)
{
var createTypeTableStatement = statement as CreateTypeTableStatement;
if (createTypeTableStatement == null)
{
continue;
}
var viewSchemaName = createTypeTableStatement.Name.SchemaIdentifier.Value;
var createSchemaCommand = new SqlCommand(
$@" IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'{viewSchemaName}')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA {viewSchemaName}'
END", sqlConnection);
createSchemaCommand.ExecuteNonQuery();
scriptGen.GenerateScript(statement, out var scriptOut);
var sqlCommand = new SqlCommand(scriptOut, sqlConnection);
sqlCommand.ExecuteNonQuery();
var tableType = new KTableType
{
Schema =
createTypeTableStatement.Name.SchemaIdentifier.Value,
TableTypeName =
createTypeTableStatement.Name.BaseIdentifier.Value,
TableTypeText = scriptOut
};
var oldTableType = kDataStoreProject.OldTableType.FirstOrDefault(tt => tt.TableTypeName == tableType.TableTypeName); //todo: compare schema
if (oldTableType != null)
{
tableType.DerivedFrom = oldTableType.DerivedFrom;
}
tableTypes.Add(tableType);
}
}
sqlConnection.Close();
}
return(tableTypes);
}
private List <KView> CreateMockSqlViewsInDb(string connectionString, string sqlText)
{
var viewNames = new List <KView>();
var scriptGen = new Sql120ScriptGenerator();
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
var parser = new TSql120Parser(false);
var script2 = parser.Parse(new StringReader(sqlText), out var errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
for (var batchNo = 0; batchNo < script2.Batches.Count; batchNo++)
{
var batch2 = script2.Batches[batchNo];
foreach (var statement in batch2.Statements)
{
var createViewStatement = statement as CreateViewStatement;
if (createViewStatement == null)
{
continue;
}
var viewSchemaName = createViewStatement.SchemaObjectName.SchemaIdentifier.Value;
var createSchemaCommand = new SqlCommand(
$@" IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'{viewSchemaName}')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA {viewSchemaName}'
END", sqlConnection);
createSchemaCommand.ExecuteNonQuery();
//createViewStatement.SelectStatement.QueryExpression.
string scriptOut;
scriptGen.GenerateScript(statement, out scriptOut);
//var batchStartLineNo = batch2.StartLine;
var batchStartCharNo = batch2.StartOffset;
var batchCharLength = batch2.FragmentLength;
/*
* if (batchNo < script2.Batches.Count)
* batchEndCharNo = script2.Batches[batchNo].StartOffset - 1;
* else
* batchEndCharNo = sqlText.Length;
*/
var batchScript = sqlText.Substring(batchStartCharNo, batchCharLength);
var sqlCommand = new SqlCommand(scriptOut, sqlConnection);
sqlCommand.ExecuteNonQuery();
viewNames.Add(new KView
{
Schema =
new CSchema {
SchemaName = createViewStatement.SchemaObjectName.SchemaIdentifier.Value
},
ViewName =
createViewStatement.SchemaObjectName.BaseIdentifier.Value,
ViewText = batchScript
});
}
}
sqlConnection.Close();
}
return(viewNames);
}
protected IList <TSqlParserToken> GetTokens(TSqlFragment fragment)
{
var generator = new Sql120ScriptGenerator(SavedSettings.Get().GeneratorOptions);
return(generator.GenerateTokens(fragment));
}
private bool HandleCustom(object step, int depth)
{
if (step is TSqlScript)
{
var script = step as TSqlScript;
var generator = new Sql120ScriptGenerator();
string scriptText;
generator.GenerateScript(script, out scriptText);
Console.WriteLine("".PadRight(depth, '\t') + " Script: " + scriptText);
foreach (var batch in script.Batches)
{
foreach (var statement in batch.Statements)
{
Console.WriteLine("".PadRight(depth, '\t') + statement.GetType());
DumpDeploymentStep(statement, depth + 1);
}
}
return true;
}
if (step is Identifier)
{
var id = step as Identifier;
Console.WriteLine("".PadRight(depth, '\t') + id.Value);
return true;
}
if (step is SchemaObjectName)
{
var id = step as SchemaObjectName;
Console.WriteLine("".PadRight(depth, '\t') +
string.Format("{0}.{1}.{2}.{3}",
id.ServerIdentifier != null ? id.ServerIdentifier.Value : "",
id.DatabaseIdentifier != null ? id.DatabaseIdentifier.Value : "",
id.SchemaIdentifier != null ? id.SchemaIdentifier.Value : "",
id.BaseIdentifier != null ? id.BaseIdentifier.Value : ""));
return true;
}
return false;
}
private List <KFunction> CreateSqlFunctionsInDb(KDataStoreProject kDataStoreProject, string connectionString, string sqlText)
{
var functions = new List <KFunction>();
var scriptGen = new Sql120ScriptGenerator(new SqlScriptGeneratorOptions {
IncludeSemicolons = true
});
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
var parser = new TSql120Parser(false);
var script2 = parser.Parse(new StringReader(sqlText), out var errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
for (var batchNo = 0; batchNo < script2.Batches.Count; batchNo++)
{
var batch2 = script2.Batches[batchNo];
//get the doc above the stored proc
var betweenBatchStart = 0;
if (batchNo > 0)
{
betweenBatchStart = script2.Batches[batchNo - 1].StartOffset +
script2.Batches[batchNo - 1].FragmentLength;
}
var betweenBatchEnd = batch2.StartOffset - 1;
string batchText = null;
if (betweenBatchEnd > 0)
{
batchText = sqlText.Substring(betweenBatchStart, betweenBatchEnd - betweenBatchStart);
//clean up the doc
batchText = batchText.Replace("GO", "");
batchText = batchText.Replace(Environment.NewLine, "");
batchText = batchText.Replace("\r", "");
batchText = batchText.Replace("/*", "");
batchText = batchText.Replace("*/", "");
batchText = batchText.Trim();
}
foreach (var statement in batch2.Statements)
{
var createFunctionStatement = statement as CreateFunctionStatement;
if (createFunctionStatement == null)
{
continue;
}
var viewSchemaName = createFunctionStatement.Name.SchemaIdentifier.Value;
var createSchemaCommand = new SqlCommand(
$@" IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'{viewSchemaName}')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA {viewSchemaName}'
END", sqlConnection);
createSchemaCommand.ExecuteNonQuery();
scriptGen.GenerateScript(statement, out var scriptOut, out var errors2);
//fixup CTE
//var tempScript = scriptOut.Replace(Environment.NewLine, " ").ToUpper();
//might insert extra ; it won't hurt anything
if (scriptOut.Contains(" WITH "))
{
scriptOut = scriptOut.Replace(" WITH ", "; WITH ");
}
else if (scriptOut.Contains(Environment.NewLine + "WITH "))
{
scriptOut = scriptOut.Replace(Environment.NewLine + "WITH ", ";" + Environment.NewLine + " WITH ");
}
var sqlCommand = new SqlCommand(scriptOut, sqlConnection);
sqlCommand.ExecuteNonQuery();
var function = new KFunction(DataStoreTypes.SqlServer)
{
Schema = new CSchema()
{
SchemaName =
createFunctionStatement.Name.SchemaIdentifier.Value
},
FunctionName =
createFunctionStatement.Name.BaseIdentifier.Value,
FunctionText = scriptOut,
//StoredProcedureDescription = batchText,
ResultSetName = createFunctionStatement.Name.BaseIdentifier.Value + "Dto"// "ResultSet"
};
/*
* var oldStoredProc = kDataStoreProject.OldStoredProcedure.FirstOrDefault(s => s.StoredProcedureName == function.FunctionName); //todo: compare schema
* if (oldStoredProc != null)
* {
* function.DerivedFrom = oldStoredProc.DerivedFrom;
* }
*/
functions.Add(function);
}
}
sqlConnection.Close();
}
return(functions);
}
public string Convert(CTableType tableType)
{
string[] parts = { tableType.Schema.SchemaName, tableType.TableName };
var createTypeTable = new CreateTypeTableStatement();
///set schema and table name
createTypeTable.Name = new SchemaObjectName();
createTypeTable.Name.Identifiers.Add(new Identifier {
Value = tableType.Schema.SchemaName
});
createTypeTable.Name.Identifiers.Add(new Identifier {
Value = tableType.TableName
});
//add columns
createTypeTable.Definition = new TableDefinition();
foreach (var col in tableType.Column)
{
var dataType = new SqlDataTypeReference
{
SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(col.ColumnTypeRaw)
};
if (DoesNeedLength(col.ColumnSqlDbType))
{
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
};
if (col.IsIdentity)
{
column.IdentityOptions = new IdentityOptions
{
IdentitySeed = new IntegerLiteral {
Value = "1000"
},
IdentityIncrement = new IntegerLiteral {
Value = "1"
}
}
}
;
column.Constraints.Add(new NullableConstraintDefinition {
Nullable = col.IsNullable
});
if (col.IsUnique)
{
column.Constraints.Add(new UniqueConstraintDefinition());
}
if (col.IsIndexed)
{
column.Index = new IndexDefinition
{
Name = new Identifier {
Value = $"IX_{col.ColumnName}"
},
IndexType = new IndexType
{
IndexTypeKind = IndexTypeKind.NonClustered
}
}
}
;
createTypeTable.Definition.ColumnDefinitions.Add(column);
}
/*
* //generate DDL
* var script = new TSqlScript();
* var batch = new TSqlBatch();
* script.Batches.Add(batch);
* batch.Statements.Add(createTypeTable);
* 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();
*/
string scriptOut;
var scriptGen = new Sql120ScriptGenerator();
scriptGen.GenerateScript(createTypeTable, out scriptOut);
return(scriptOut);
}
private List <KStoredProcedure> CreateSqlStoredProcsInDb(string connectionString, string sqlText)
{
var storedProcedures = new List <KStoredProcedure>();
var scriptGen = new Sql120ScriptGenerator();
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
var parser = new TSql120Parser(false);
var script2 = parser.Parse(new StringReader(sqlText), out var errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
for (var batchNo = 0; batchNo < script2.Batches.Count; batchNo++)
{
var batch2 = script2.Batches[batchNo];
//get the doc above the stored proc
var betweenBatchStart = 0;
if (batchNo > 0)
{
betweenBatchStart = script2.Batches[batchNo - 1].StartOffset +
script2.Batches[batchNo - 1].FragmentLength;
}
var betweenBatchEnd = batch2.StartOffset - 1;
string batchText = null;
if (betweenBatchEnd > 0)
{
batchText = sqlText.Substring(betweenBatchStart, betweenBatchEnd - betweenBatchStart);
//clean up the doc
batchText = batchText.Replace("GO", "");
batchText = batchText.Replace(Environment.NewLine, "");
batchText = batchText.Replace("\r", "");
batchText = batchText.Replace("/*", "");
batchText = batchText.Replace("*/", "");
batchText = batchText.Trim();
}
foreach (var statement in batch2.Statements)
{
var createProcedureStatement = statement as CreateProcedureStatement;
if (createProcedureStatement == null)
{
continue;
}
var viewSchemaName = createProcedureStatement.ProcedureReference.Name.SchemaIdentifier.Value;
var createSchemaCommand = new SqlCommand(
$@" IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'{viewSchemaName}')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA {viewSchemaName}'
END", sqlConnection);
createSchemaCommand.ExecuteNonQuery();
scriptGen.GenerateScript(statement, out var scriptOut);
var sqlCommand = new SqlCommand(scriptOut, sqlConnection);
sqlCommand.ExecuteNonQuery();
storedProcedures.Add(new KStoredProcedure
{
Schema =
createProcedureStatement.ProcedureReference.Name.SchemaIdentifier.Value,
StoredProcedureName =
createProcedureStatement.ProcedureReference.Name.BaseIdentifier.Value,
StoredProcedureText = scriptOut,
StoredProcedureDescription = batchText
});
}
}
sqlConnection.Close();
}
return(storedProcedures);
}
private List <CStoredProcedureParameter> GetParameters(string connectionString, string sqlText,
CFunction function)
{
var functionParameters = new List <CStoredProcedureParameter>();
var scriptGen = new Sql120ScriptGenerator();
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
var parser = new TSql120Parser(false);
var statementList = new StatementList();
IList <ParseError> errors;
var script2 = parser.Parse(new StringReader(sqlText), out errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
foreach (var batch2 in script2.Batches)
{
foreach (var statement in batch2.Statements)
{
var createProcedureStatement = statement as CreateProcedureStatement;
if (createProcedureStatement == null)
{
continue;
}
foreach (var param in createProcedureStatement.Parameters)
{
//(new System.Collections.Generic.Mscorlib_CollectionDebugView<Microsoft.SqlServer.TransactSql.ScriptDom.Literal>
// (((Microsoft.SqlServer.TransactSql.ScriptDom.ParameterizedDataTypeReference)param.DataType).Parameters).Items[0]).Value;
var length = 0;
if ((param.DataType as ParameterizedDataTypeReference).Parameters.Count > 0)
{
var lengthString = (param.DataType as ParameterizedDataTypeReference).Parameters[0].Value;
if ((param.DataType as ParameterizedDataTypeReference).Parameters[0] is Microsoft.SqlServer.TransactSql.ScriptDom.MaxLiteral)
{
length = -1;
}
else
{
length = int.Parse(lengthString);
}
}
var storedProcedureParameter = new CStoredProcedureParameter
{
ParameterName = param.VariableName.Value.Replace("@", "").Replace("_Collection", ""),
ParameterTypeIsUserDefined = param.DataType is UserDataTypeReference,
ParameterTypeRaw = param.DataType.Name.BaseIdentifier.Value,
ParameterTypeRawSchema = param.DataType.Name?.SchemaIdentifier?.Value,
SourceColumn =
new CColumn(function)
{
ColumnName = param.VariableName.Value.Replace("@", "")
},
IsCollection = param.VariableName.Value.EndsWith("_Collection")
};
if (length > 0)
{
storedProcedureParameter.ParameterLength = length;
storedProcedureParameter.SourceColumn.ColumnLength = length;
}
if (!storedProcedureParameter.ParameterTypeIsUserDefined)
{
storedProcedureParameter.ParameterType =
SqlMapper.SqlDbTypeToDbType(
SqlMapper.ParseValueAsSqlDbType(param.DataType.Name.BaseIdentifier.Value));
}
functionParameters.Add(storedProcedureParameter);
}
}
}
sqlConnection.Close();
}
return(functionParameters);
}
private List <KTable> CreateSqlTablesInDb(string connectionString, string sqlText)
{
var tablesNames = new List <KTable>();
var scriptGen = new Sql120ScriptGenerator();
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
var parser = new TSql120Parser(false);
var script2 = parser.Parse(new StringReader(sqlText), out var errors) as TSqlScript;
if (errors.Count > 0)
{
var errorList = new StringBuilder();
foreach (var error in errors)
{
errorList.AppendLine($"{error.Message}<br/>");
}
throw new ApplicationException(errorList.ToString());
}
foreach (var batch2 in script2.Batches)
{
foreach (var statement in batch2.Statements)
{
var createTableStatement = statement as CreateTableStatement;
if (createTableStatement == null)
{
continue;
}
var viewSchemaName = createTableStatement.SchemaObjectName.SchemaIdentifier.Value;
var createSchemaCommand = new SqlCommand(
$@" IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'{viewSchemaName}')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA {viewSchemaName}'
END", sqlConnection);
createSchemaCommand.ExecuteNonQuery();
//createViewStatement.SelectStatement.QueryExpression.
scriptGen.GenerateScript(statement, out var scriptOut);
var sqlCommand = new SqlCommand(scriptOut, sqlConnection);
sqlCommand.ExecuteNonQuery();
tablesNames.Add(new KTable
{
Schema =
new CSchema {
SchemaName = createTableStatement.SchemaObjectName.SchemaIdentifier.Value
},
TableName =
createTableStatement.SchemaObjectName.BaseIdentifier.Value,
TableText = scriptOut
});
}
}
sqlConnection.Close();
}
return(tablesNames);
}
