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); }