public override IOperation Extract(Process process, Entity entity, bool firstRun) { if (Schemas && entity.Schema.Equals(string.Empty)) { entity.Schema = DefaultSchema; } var p = new PartialProcessOperation(process); if (entity.HasSqlOverride()) { p.Register(new SqlOverrideOperation(entity, this)); } else { if (entity.PrimaryKey.WithInput().Any()) { p.Register(new EntityKeysSaveOperation(entity)); p.Register(new EntityKeysToOperations(ref entity, this, firstRun)); p.Register(new SerialUnionAllOperation(entity)); } else { entity.SqlOverride = SqlTemplates.Select(entity, this); p.Register(new SqlOverrideOperation(entity, this)); } } return(p); }
/// <summary> /// Run sql query and execute Script for found rows. This function is intended to use from Dialog. /// </summary> /// <param name="isWithAsk">True: Ask for each found rows if to execute</param> void RunScriptWithAskGui(bool isWithAsk = false) { if (tabControlSql.SelectedIndex == -1) { return; } Cursor.Current = Cursors.WaitCursor; // get TabPage TabPage tabPage = tabControlSql.TabPages[tabControlSql.SelectedIndex]; // get TextBox TextBox textBox = (TextBox)tabPage.Controls[0]; // get Script and its parameter to run DataGridViewRow rowToRun = dataGridViewScripts.Rows[_rowScriptsIndex]; DataRowView row = rowToRun.DataBoundItem as DataRowView; var scriptFunction = row["FunctionObj"] as ScriptFunction; // replace templates, search term and more string sql = SqlTemplates.ReplaceMacro(Repository, textBox.Text, GetSearchTerm()); if (sql == "") { return; } // run SQL, Script and ask whether to execute, skip script or break all together GuiFunction.RunScriptWithAsk(Model, sql, scriptFunction, isWithAsk: isWithAsk); Cursor.Current = Cursors.Default; }
public SqlTemplatesBenchmark() { var mappers = new NPoco.MapperCollection(); var factory = new FluentPocoDataFactory((type, iPocoDataFactory) => new PocoDataBuilder(type, mappers).Init(), mappers); SqlContext = new SqlContext(new SqlServerSyntaxProvider(Options.Create(new GlobalSettings())), DatabaseType.SQLCe, factory); SqlTemplates = new SqlTemplates(SqlContext); }
/// <summary> /// Output Help of macros and templates in a text editor /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void templatesAndMacrosToolStripMenuItem_Click(object sender, EventArgs e) { string content = SqlTemplates.GetTemplateText(SqlTemplates.SqlTemplateId.MacrosHelp); // write it do EA home (%appdata%Sparx System\EA\hoTools_SqlTemplatesAndMacros.txt) SqlError.WriteSqlTemplatesAndMacros(content); // Show it in Editor Util.StartFile(SqlError.GetSqlTemplatesAndMacrosFilePath()); }
/// <summary> /// Initialize setting. Only call after Repository is known. /// <para/>- Tag ( /// <para/>- Model /// <para/>- Settings /// updated /// </summary> /// <returns></returns> bool InitializeSettings() { // default _addinType = AddinType.Sql; _addinTabName = TabulatorSql; if ((string)Tag != TabulatorSql) { _addinType = AddinType.Script; _addinTabName = TabulatorScript; } // set title lblTitle.Text = _addinTabName; // Tab Pages for *.sql queries update // Make sure the Container is initialized if (components == null) { components = new System.ComponentModel.Container(); } // the sql tabulators might already be available if (_sqlTabCntrls == null) { _sqlTabCntrls = new SqlTabPagesCntrl(Model, AddinSettings, components, tabControlSql, txtSearchTerm, _newTabFromRecentToolStripMenuItem, _loadTabFromRecentToolStripMenuItem, _addinTabName); } if (tabControlSql.TabPages.Count == 0) { // first tab with Element Template _sqlTabCntrls.AddTab(SqlTemplates.GetTemplateText(SqlTemplates.SqlTemplateId.ElementTemplate)); } // run for SQL / Query if (_addinType == AddinType.Sql) { // don't show Script container splitContainer.Panel2Collapsed = true; // don't show Menu item LoadScripts //loadStandardScriptsToolStripMenuItem.Visible = false; } else // run for Script (includes SQL / Query) { float distance = splitContainer.Height * (float)0.5; try { splitContainer.SplitterDistance = (int)distance; } catch // suppress any error, use default SplitterDistance { } // available script updates ReloadScripts(); } return(true); }
public void TestDropSqlNoSchema() { Assert.AreEqual(@" IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TEST' ) DROP TABLE [TEST]; ", SqlTemplates.DropTable("TEST", string.Empty)); }
/// <summary> /// Initializes a new instance of the <see cref="SqlContext" /> class. /// </summary> /// <param name="sqlSyntax">The sql syntax provider.</param> /// <param name="pocoDataFactory">The Poco data factory.</param> /// <param name="databaseType">The database type.</param> /// <param name="mappers">The mappers.</param> public SqlContext(ISqlSyntaxProvider sqlSyntax, DatabaseType databaseType, IPocoDataFactory pocoDataFactory, IMapperCollection?mappers = null) { // for tests Mappers = mappers; SqlSyntax = sqlSyntax ?? throw new ArgumentNullException(nameof(sqlSyntax)); PocoDataFactory = pocoDataFactory ?? throw new ArgumentNullException(nameof(pocoDataFactory)); DatabaseType = databaseType ?? throw new ArgumentNullException(nameof(databaseType)); Templates = new SqlTemplates(this); }
public SqlTemplatesBenchmark() { var mappers = new NPoco.MapperCollection { new PocoMapper() }; var factory = new FluentPocoDataFactory((type, iPocoDataFactory) => new PocoDataBuilder(type, mappers).Init()); SqlContext = new SqlContext(new SqlCeSyntaxProvider(), DatabaseType.SQLCe, factory); SqlTemplates = new SqlTemplates(SqlContext); }
public void TestTruncateSql() { Assert.AreEqual(@" IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Test' ) TRUNCATE TABLE [dbo].[Test]; ", SqlTemplates.TruncateTable("Test", "dbo")); }
public void TestDropSql() { Assert.AreEqual(@" IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TEST' ) DROP TABLE [dbo].[TEST]; ", SqlTemplates.DropTable("TEST", "dbo")); }
public string SelectByKeys(IEnumerable<Row> rows) { var tableName = _connection.TableVariable ? "@KEYS" : "keys_" + _entity.Name; var noCount = _connection.NoCount ? "SET NOCOUNT ON;\r\n" : string.Empty; var sql = noCount + _connection.TableQueryWriter.WriteTemporary(_connection, tableName, _key, false) + SqlTemplates.BatchInsertValues(50, tableName, _key, rows, _connection) + Environment.NewLine + SqlTemplates.Select(_entity.Fields, _entity.Name, tableName, _connection, _entity.Schema, string.Empty) + (_connection.TableVariable ? string.Empty : string.Format("DROP TABLE {0};", tableName)); Debug(sql); return sql; }
public void SqlTemplates() { var sqlContext = new SqlContext(new SqlServerSyntaxProvider(Options.Create(new GlobalSettings())), DatabaseType.SqlServer2012, Mock.Of <IPocoDataFactory>()); var sqlTemplates = new SqlTemplates(sqlContext); // this can be used for queries that we know we'll use a *lot* and // want to cache as a (static) template for ever, and ever - note // that using a MemoryCache would allow us to set a size limit, or // something equivalent, to reduce risk of memory explosion Sql <ISqlContext> sql = sqlTemplates.Get("xxx", s => s .SelectAll() .From("zbThing1") .Where("id=@id", new { id = SqlTemplate.Arg("id") })).Sql(new { id = 1 }); Sql <ISqlContext> sql2 = sqlTemplates.Get("xxx", x => throw new InvalidOperationException("Should be cached.")).Sql(1); Sql <ISqlContext> sql3 = sqlTemplates.Get("xxx", x => throw new InvalidOperationException("Should be cached.")).Sql(new { id = 1 }); }
/// <summary> /// Run an SQL string and if query output the result in EA Search Window. If update, insert, delete execute SQL. /// It return "" for nothing found or the SQL result string. /// <para/>- replacement of macros /// <para/>- run query /// <para/>- format to output /// </summary> /// <param name="sqlName"></param> /// <param name="sql"></param> /// <param name="searchText">Search Text to replace 'Search Term' macro</param> /// <param name="exportToExcel"></param> /// <returns>"" for nothing found or the EA SQL XML string with the found information</returns> public string SqlRun(string sqlName, string sql, string searchText, bool exportToExcel = false) { // replace templates sql = SqlTemplates.ReplaceMacro(Repository, sql, searchText); if (String.IsNullOrWhiteSpace(sql)) { return(""); } // normalize according to linefeed sql = Regex.Replace(sql, @"\r\n |\r\n|\n\r|\n|\r", "\r\n "); // check whether select or update, delete, insert sql if (Regex.IsMatch(sql, @"^\s*select ", RegexOptions.IgnoreCase | RegexOptions.Multiline)) { // run the SQL select query var xmlSqlQueryResult = SqlQueryWithException(sql) ?? ""; // output the query in EA Search Window format string xmlEaOutput = MakeEaXmlOutput(xmlSqlQueryResult); if (exportToExcel) { Excel.MakeExcelFileFromSqlResult(xmlSqlQueryResult, @"d:\temp\sql\" + Path.GetFileNameWithoutExtension(sqlName) + ".xlsx"); } Repository.RunModelSearch("", "", "", xmlEaOutput); return(xmlSqlQueryResult); } else { // run the update, delete, insert sql bool ret = SqlExecuteWithException(sql); // if ok output the SQL if (ret) { string sqlText = $"Path SQL:\r\n{SqlError.GetHoToolsLastSqlFilePath()}\r\n\r\n{SqlError.ReadHoToolsLastSql()}"; MessageBox.Show(sqlText, @"SQL executed!\r\n\r\nCtrl+C to copy it to clipboard (ignore beep)."); } return(""); } }
private string PrepareSqlWithInputKeys() { const string sqlPattern = @" {0} SELECT e.{1}, e.TflKey{2} FROM {3} e WITH (NOLOCK) INNER JOIN @KEYS k ON ({4}); "; var builder = new StringBuilder(); builder.AppendLine(_connection.WriteTemporaryTable("@KEYS", _key.WithInput())); builder.AppendLine(SqlTemplates.BatchInsertValues(50, "@KEYS", _key.WithInput(), _entity.InputKeys, _connection)); var selectKeys = new FieldSqlWriter(_entity.PrimaryKey).AddDeleted(_entity).Alias(_connection.L, _connection.R).Write(", e.", false); var joinKeys = new FieldSqlWriter(_entity.PrimaryKey).Input().Alias(_connection.L, _connection.R).Set("e", "k").Write(" AND "); return(string.Format(sqlPattern, builder, selectKeys, PrepareVersion(), _connection.Enclose(_entity.OutputName()), joinKeys)); }
public void TestSelectByKeysSql() { var entity = _process.Entities.First(); var actual = SqlTemplates.Select(entity.Fields, entity.OutputName(), "@KEYS", _process.OutputConnection, "dbo", _process.OutputConnection.DefaultSchema); const string expected = @" SELECT l.[OrderDetailKey], l.[OrderKey], l.[ProductKey], l.[Qty] AS [Quantity], l.[Price], l.[Properties], l.[RowVersion] AS [OrderDetailRowVersion] FROM [dbo].[TestOrderDetail] l INNER JOIN @KEYS r ON (l.[OrderDetailKey] = r.[OrderDetailKey]) OPTION (MAXDOP 2);"; Assert.AreEqual(expected, actual); }
public void TestKeyInserts() { var entity = _process.Entities.First(); _process.OutputConnection.IsReady(); var rows = TestOperation(_entityKeysExtract.Object); Assert.AreEqual(4, rows.Count); var actual = SqlTemplates.BatchInsertValues(2, "@KEYS", entity.PrimaryKey, rows, _process.OutputConnection); const string expected = @" INSERT INTO @KEYS SELECT 1 UNION ALL SELECT 2; INSERT INTO @KEYS SELECT 3 UNION ALL SELECT 4;"; Assert.AreEqual(expected, actual); }
public void SqlTemplateArgs() { var mappers = new NPoco.MapperCollection { new NullableDateMapper() }; var factory = new FluentPocoDataFactory((type, iPocoDataFactory) => new PocoDataBuilder(type, mappers).Init()); var sqlContext = new SqlContext(new SqlServerSyntaxProvider(Options.Create(new GlobalSettings())), DatabaseType.SQLCe, factory); var sqlTemplates = new SqlTemplates(sqlContext); const string sqlBase = "SELECT [zbThing1].[id] AS [Id], [zbThing1].[name] AS [Name] FROM [zbThing1] WHERE "; SqlTemplate template = sqlTemplates.Get("sql1", s => s.Select <Thing1Dto>().From <Thing1Dto>() .Where <Thing1Dto>(x => x.Name == SqlTemplate.Arg <string>("value"))); Sql <ISqlContext> sql = template.Sql("foo"); Assert.AreEqual(sqlBase + "(([zbThing1].[name] = @0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual("foo", sql.Arguments[0]); sql = template.Sql(123); Assert.AreEqual(sqlBase + "(([zbThing1].[name] = @0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual(123, sql.Arguments[0]); template = sqlTemplates.Get("sql2", s => s.Select <Thing1Dto>().From <Thing1Dto>() .Where <Thing1Dto>(x => x.Name == SqlTemplate.Arg <string>("value"))); sql = template.Sql(new { value = "foo" }); Assert.AreEqual(sqlBase + "(([zbThing1].[name] = @0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual("foo", sql.Arguments[0]); sql = template.Sql(new { value = 123 }); Assert.AreEqual(sqlBase + "(([zbThing1].[name] = @0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual(123, sql.Arguments[0]); Assert.Throws <InvalidOperationException>(() => template.Sql(new { xvalue = 123 })); Assert.Throws <InvalidOperationException>(() => template.Sql(new { value = 123, xvalue = 456 })); var i = 666; template = sqlTemplates.Get("sql3", s => s.Select <Thing1Dto>().From <Thing1Dto>() .Where <Thing1Dto>(x => x.Id == i)); sql = template.Sql("foo"); Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual("foo", sql.Arguments[0]); sql = template.Sql(123); Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual(123, sql.Arguments[0]); // but we cannot name them, because the arg name is the value of "i" // so we have to explicitely create the argument template = sqlTemplates.Get("sql4", s => s.Select <Thing1Dto>().From <Thing1Dto>() .Where <Thing1Dto>(x => x.Id == SqlTemplate.Arg <int>("i"))); sql = template.Sql("foo"); Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual("foo", sql.Arguments[0]); sql = template.Sql(123); Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual(123, sql.Arguments[0]); // and thanks to a patched visitor, this now works sql = template.Sql(new { i = "foo" }); Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual("foo", sql.Arguments[0]); sql = template.Sql(new { i = 123 }); Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual(123, sql.Arguments[0]); Assert.Throws <InvalidOperationException>(() => template.Sql(new { j = 123 })); Assert.Throws <InvalidOperationException>(() => template.Sql(new { i = 123, j = 456 })); // now with more arguments template = sqlTemplates.Get("sql4a", s => s.Select <Thing1Dto>().From <Thing1Dto>() .Where <Thing1Dto>(x => x.Id == SqlTemplate.Arg <int>("i") && x.Name == SqlTemplate.Arg <string>("name"))); sql = template.Sql(0, 1); Assert.AreEqual(sqlBase + "((([zbThing1].[id] = @0) AND ([zbThing1].[name] = @1)))", sql.SQL.NoCrLf()); Assert.AreEqual(2, sql.Arguments.Length); Assert.AreEqual(0, sql.Arguments[0]); Assert.AreEqual(1, sql.Arguments[1]); template = sqlTemplates.Get("sql4b", s => s.Select <Thing1Dto>().From <Thing1Dto>() .Where <Thing1Dto>(x => x.Id == SqlTemplate.Arg <int>("i")) .Where <Thing1Dto>(x => x.Name == SqlTemplate.Arg <string>("name"))); sql = template.Sql(0, 1); Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0)) AND (([zbThing1].[name] = @1))", sql.SQL.NoCrLf()); Assert.AreEqual(2, sql.Arguments.Length); Assert.AreEqual(0, sql.Arguments[0]); Assert.AreEqual(1, sql.Arguments[1]); // works, magic template = sqlTemplates.Get("sql5", s => s.Select <Thing1Dto>().From <Thing1Dto>() .WhereIn <Thing1Dto>(x => x.Id, SqlTemplate.ArgIn <int>("i"))); sql = template.Sql("foo"); Assert.AreEqual(sqlBase + "([zbThing1].[id] IN (@0))", sql.SQL.NoCrLf()); Assert.AreEqual(1, sql.Arguments.Length); Assert.AreEqual("foo", sql.Arguments[0]); sql = template.Sql(new[] { 1, 2, 3 }); Assert.AreEqual(sqlBase + "([zbThing1].[id] IN (@0,@1,@2))", sql.SQL.NoCrLf()); Assert.AreEqual(3, sql.Arguments.Length); Assert.AreEqual(1, sql.Arguments[0]); Assert.AreEqual(2, sql.Arguments[1]); Assert.AreEqual(3, sql.Arguments[2]); template = sqlTemplates.Get("sql5a", s => s.Select <Thing1Dto>().From <Thing1Dto>() .WhereIn <Thing1Dto>(x => x.Id, SqlTemplate.ArgIn <int>("i")) .Where <Thing1Dto>(x => x.Name == SqlTemplate.Arg <string>("name"))); sql = template.Sql("foo", "bar"); Assert.AreEqual(sqlBase + "([zbThing1].[id] IN (@0)) AND (([zbThing1].[name] = @1))", sql.SQL.NoCrLf()); Assert.AreEqual(2, sql.Arguments.Length); Assert.AreEqual("foo", sql.Arguments[0]); Assert.AreEqual("bar", sql.Arguments[1]); sql = template.Sql(new[] { 1, 2, 3 }, "bar"); Assert.AreEqual(sqlBase + "([zbThing1].[id] IN (@0,@1,@2)) AND (([zbThing1].[name] = @3))", sql.SQL.NoCrLf()); Assert.AreEqual(4, sql.Arguments.Length); Assert.AreEqual(1, sql.Arguments[0]); Assert.AreEqual(2, sql.Arguments[1]); Assert.AreEqual(3, sql.Arguments[2]); Assert.AreEqual("bar", sql.Arguments[3]); // note however that using WhereIn in a template means that the SQL is going // to be parsed and arguments are going to be expanded etc - it *may* be a better // idea to just add the WhereIn to a templated, immutable SQL template // more fun... template = sqlTemplates.Get("sql6", s => s.Select <Thing1Dto>().From <Thing1Dto>() // do NOT do this, this is NOT a visited expression //// .Append(" AND whatever=@0", SqlTemplate.Arg<string>("j")) // does not work anymore - due to proper TemplateArg //// instead, directly name the argument ////.Append("AND whatever=@0", "j") ////.Append("AND whatever=@0", "k") // instead, explicitely create the argument .Append("AND whatever=@0", SqlTemplate.Arg("j")) .Append("AND whatever=@0", SqlTemplate.Arg("k"))); sql = template.Sql(new { j = new[] { 1, 2, 3 }, k = "oops" }); Assert.AreEqual(sqlBase.TrimEnd("WHERE ") + "AND whatever=@0,@1,@2 AND whatever=@3", sql.SQL.NoCrLf()); Assert.AreEqual(4, sql.Arguments.Length); Assert.AreEqual(1, sql.Arguments[0]); Assert.AreEqual(2, sql.Arguments[1]); Assert.AreEqual(3, sql.Arguments[2]); Assert.AreEqual("oops", sql.Arguments[3]); }