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