/// <summary>
/// Generate the query SQL. Do not actually run it.
/// </summary>
/// <param name="query">The structured query object to convert.</param>
/// <param name="settings">Build-time settings for the conversion.</param>
/// <returns>An object structure containing SQL, and other discovered information.</returns>
public QueryBuild BuildSql(StructuredQuery query, QuerySqlBuilderSettings settings)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
// Initialise settings
if (settings == null)
{
settings = new QuerySqlBuilderSettings( );
}
// Optimise tree
StructuredQuery optimisedQuery = StructuredQueryHelper.PruneQueryTree(query);
// Generate SQL
QueryBuilder queryBuilder = new QueryBuilder(optimisedQuery, settings);
QueryBuild result = queryBuilder.GetSqlInternal( );
// Logging
using (MessageContext msg = new MessageContext("Reports"))
{
msg.Append(() => new string( '-', 50 ));
msg.Append(() => "Final structured query:\n" + StructuredQueryHelper.ToXml(optimisedQuery));
msg.Append(() => new string( '-', 50 ));
msg.Append(() => "SQL:\n" + result.Sql);
msg.Append(() => new string( '-', 50 ));
}
// Note: identify cache dependencies after getting SQL, so that any calculations are resolved into the structured query.
IdentifyCacheDependencies(optimisedQuery, settings);
return(result);
}
public void Test_SqlIsUncacheable( )
{
CachingQuerySqlBuilder cachingQuerySqlBuilder;
Mock <IQuerySqlBuilder> mockQuerySqlBuilder;
IQuerySqlBuilder querySqlBuilder;
StructuredQuery structuredQuery;
QuerySqlBuilderSettings settings;
QueryBuild queryBuild;
IUserRuleSetProvider userRuleSetProvider = MockUserRuleSetProvider( );
structuredQuery = new StructuredQuery( );
settings = new QuerySqlBuilderSettings( );
queryBuild = new QueryBuild( );
queryBuild.SqlIsUncacheable = true;
mockQuerySqlBuilder = new Mock <IQuerySqlBuilder>(MockBehavior.Strict);
mockQuerySqlBuilder.Setup(x => x.BuildSql(structuredQuery, settings))
.Returns(() => queryBuild)
.Verifiable( );
querySqlBuilder = mockQuerySqlBuilder.Object;
cachingQuerySqlBuilder = new CachingQuerySqlBuilder(querySqlBuilder, userRuleSetProvider);
cachingQuerySqlBuilder.BuildSql(structuredQuery, settings);
cachingQuerySqlBuilder.BuildSql(structuredQuery, settings);
mockQuerySqlBuilder.Verify(x => x.BuildSql(structuredQuery, settings), Times.Exactly(2));
mockQuerySqlBuilder.VerifyAll( );
}
/// <summary>
/// ログイン情報取得
/// </summary>
/// <remarks>
/// 作成者 ： sesaki
/// 作成日 ： 2019/10/30
/// </remarks>
public DataRow GetLoginUser(Condition condition)
{
DataTable dtTable;
DataRow dtRow = null;
List <object> paraList = new List <object>();
string queryStr = QueryBuild.SystemLogin(condition, ref paraList);
DataBase dataBase = new DataBase();
dataBase.ConnectDB();
DataSet dtSet = dataBase.GetDataSet(queryStr, paraList.ToArray());
dtTable = dtSet.Tables[0];
if (dtTable.Rows.Count != 0)
{
dtRow = dtTable.Rows[0];
}
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
return(dtRow);
}
public void Test_Cached( )
{
CachingQueryRunner cachingQueryRunner;
Mock <IQueryRunner> mockQueryRunner;
IQueryRunner queryRunner;
StructuredQuery structuredQuery;
QuerySettings settings;
QueryBuild queryBuild;
QueryResult result;
IQuerySqlBuilder queryBuilder = MockQuerySqlBuilder( );
IUserRuleSetProvider userRuleSetProvider = MockUserRuleSetProvider( );
structuredQuery = new StructuredQuery( );
settings = new QuerySettings( );
queryBuild = new QueryBuild( );
result = new QueryResult(queryBuild);
mockQueryRunner = new Mock <IQueryRunner>(MockBehavior.Strict);
mockQueryRunner.Setup(x => x.ExecutePrebuiltQuery(structuredQuery, settings, It.IsNotNull <QueryBuild>()))
.Returns(() =>
{
return(result);
})
.Verifiable( );
queryRunner = mockQueryRunner.Object;
cachingQueryRunner = new CachingQueryRunner(queryRunner, userRuleSetProvider, queryBuilder);
Assert.That(cachingQueryRunner.ExecuteQuery(structuredQuery, settings),
Is.SameAs(result));
Assert.That(cachingQueryRunner.Cache,
Has.Count.EqualTo(1));
mockQueryRunner.Verify(x => x.ExecutePrebuiltQuery(structuredQuery, settings, It.IsNotNull <QueryBuild>( )), Times.Exactly(1));
mockQueryRunner.VerifyAll( );
}
public void Test_EnsureCached_TwoThreads( )
{
CachingQuerySqlBuilder cachingQuerySqlBuilder;
Mock <IQuerySqlBuilder> mockQuerySqlBuilder;
IQuerySqlBuilder querySqlBuilder;
StructuredQuery structuredQuery;
QuerySqlBuilderSettings settings;
QueryBuild queryBuild;
IUserRuleSetProvider userRuleSetProvider = MockUserRuleSetProvider( );
structuredQuery = new StructuredQuery( );
settings = new QuerySqlBuilderSettings( );
queryBuild = new QueryBuild( );
mockQuerySqlBuilder = new Mock <IQuerySqlBuilder>(MockBehavior.Strict);
mockQuerySqlBuilder.Setup(x => x.BuildSql(structuredQuery, settings))
.Returns(() => {
Thread.Sleep(100);
return(queryBuild);
})
.Verifiable( );
querySqlBuilder = mockQuerySqlBuilder.Object;
cachingQuerySqlBuilder = new CachingQuerySqlBuilder(querySqlBuilder, userRuleSetProvider);
Task task1 = Task.Factory.StartNew(() => cachingQuerySqlBuilder.BuildSql(structuredQuery, settings));
Thread.Sleep(1); // BlockIfPending can handle overlapping, but no coincident requests
Task task2 = Task.Factory.StartNew(() => cachingQuerySqlBuilder.BuildSql(structuredQuery, settings));
Task.WaitAll(task1, task2);
mockQuerySqlBuilder.Verify(x => x.BuildSql(structuredQuery, settings), Times.Exactly(1));
mockQuerySqlBuilder.VerifyAll( );
}
public void Test_Cached()
{
CachingQuerySqlBuilder cachingQuerySqlBuilder;
Mock <IQuerySqlBuilder> mockQuerySqlBuilder;
IQuerySqlBuilder querySqlBuilder;
StructuredQuery structuredQuery;
QuerySqlBuilderSettings settings;
QueryBuild queryBuild;
IUserRuleSetProvider userRuleSetProvider = MockUserRuleSetProvider( );
structuredQuery = new StructuredQuery( );
settings = new QuerySqlBuilderSettings( );
queryBuild = new QueryBuild( );
mockQuerySqlBuilder = new Mock <IQuerySqlBuilder>(MockBehavior.Strict);
mockQuerySqlBuilder.Setup(x => x.BuildSql(structuredQuery, settings))
.Returns(() => queryBuild)
.Verifiable();
querySqlBuilder = mockQuerySqlBuilder.Object;
cachingQuerySqlBuilder = new CachingQuerySqlBuilder(querySqlBuilder, userRuleSetProvider);
Assert.That(cachingQuerySqlBuilder.BuildSql(structuredQuery, settings),
Is.SameAs(queryBuild), "Incorrect conversion");
Assert.That(cachingQuerySqlBuilder.Cache,
Has.Count.EqualTo(1));
mockQuerySqlBuilder.Verify(x => x.BuildSql(structuredQuery, settings), Times.Exactly(1));
mockQuerySqlBuilder.VerifyAll();
}
internal static void Test_Scenario(StructuredQuery structuredQuery, Action invalidationCallback, bool expectInvalidation)
{
CachingQuerySqlBuilder cachingQuerySqlBuilder;
Mock <IQuerySqlBuilder> mockQuerySqlBuilder;
IQuerySqlBuilder querySqlBuilder;
QuerySettings settings;
QueryBuild queryBuild;
IUserRuleSetProvider userRuleSetProvider = MockUserRuleSetProvider( );
settings = new QuerySettings( );
queryBuild = new QueryBuild( );
var cacheInvalidators = new CacheInvalidatorFactory( ).CacheInvalidatorsList_TestOnly;
mockQuerySqlBuilder = new Mock <IQuerySqlBuilder>(MockBehavior.Strict);
mockQuerySqlBuilder.Setup(x => x.BuildSql(It.IsAny <StructuredQuery>( ), settings))
.Returns <StructuredQuery, QuerySettings>((sq, qs) =>
{
QuerySqlBuilder.IdentifyCacheDependencies(sq, settings);
return(queryBuild);
})
.Verifiable( );
querySqlBuilder = mockQuerySqlBuilder.Object;
cachingQuerySqlBuilder = new CachingQuerySqlBuilder(querySqlBuilder, userRuleSetProvider);
try
{
// Add current cache invalidator to global factory
cacheInvalidators.Add(cachingQuerySqlBuilder.CacheInvalidator);
using (var scope = Factory.Current.BeginLifetimeScope(cb =>
{
cb.Register(c => cachingQuerySqlBuilder).As <ICacheService>( );
}))
using (Factory.SetCurrentScope(scope))
{
// Run first time
cachingQuerySqlBuilder.BuildSql(structuredQuery.DeepCopy( ), settings);
// Perform potential invalidation task
using (new SecurityBypassContext( ))
{
invalidationCallback( );
}
// Run second time
cachingQuerySqlBuilder.BuildSql(structuredQuery.DeepCopy( ), settings);
}
int times = expectInvalidation ? 2 : 1;
mockQuerySqlBuilder.Verify(x => x.BuildSql(It.IsAny <StructuredQuery>( ), settings), Times.Exactly(times));
mockQuerySqlBuilder.VerifyAll( );
}
finally
{
// Restore cache invalidators
cacheInvalidators.Remove(cachingQuerySqlBuilder.CacheInvalidator);
}
}
/// <summary>
///
/// </summary>
/// <param name="queryResult">The container that describes what SQL needs to be run, and what parameters need to be bound.</param>
/// <param name="fieldType">Type of field that this query resolves.</param>
internal EntityResolver([NotNull] QueryBuild queryResult, DataType fieldType)
{
if (queryResult == null)
{
throw new ArgumentNullException("queryResult");
}
_queryResult = queryResult;
_fieldType = fieldType;
}
public void Test_EnsureResultsFromDifferentInstancesReturnCorrectRequestColumnData( )
{
CachingQueryRunner cachingQueryRunner;
Mock <IQueryRunner> mockQueryRunner;
IQueryRunner queryRunner;
QuerySettings settings;
QueryBuild queryBuild;
QueryResult result;
IQuerySqlBuilder queryBuilder = MockQuerySqlBuilder( );
IUserRuleSetProvider userRuleSetProvider = MockUserRuleSetProvider( );
StructuredQuery sq1 = new StructuredQuery {
RootEntity = new ResourceEntity("test:person")
};
sq1.SelectColumns.Add(new SelectColumn {
Expression = new ResourceExpression(sq1.RootEntity, "core:name")
});
StructuredQuery sq2 = new StructuredQuery {
RootEntity = new ResourceEntity("test:person")
};
sq2.SelectColumns.Add(new SelectColumn {
Expression = new ResourceExpression(sq2.RootEntity, "core:name")
});
Assert.That(sq1.SelectColumns [0].ColumnId, Is.Not.EqualTo(sq2.SelectColumns [0].ColumnId));
settings = new QuerySettings( );
queryBuild = new QueryBuild( );
result = new QueryResult(queryBuild);
result.Columns.Add(new ResultColumn {
RequestColumn = sq1.SelectColumns [0]
});
mockQueryRunner = new Mock <IQueryRunner>(MockBehavior.Strict);
mockQueryRunner.Setup(x => x.ExecutePrebuiltQuery(sq1, settings, It.IsNotNull <QueryBuild>( )))
.Returns(() => result)
.Verifiable( );
queryRunner = mockQueryRunner.Object;
cachingQueryRunner = new CachingQueryRunner(queryRunner, userRuleSetProvider, queryBuilder);
QueryResult result1 = cachingQueryRunner.ExecuteQuery(sq1, settings);
Assert.That(result1.Columns [0].RequestColumn, Is.EqualTo(sq1.SelectColumns [0]));
QueryResult result2 = cachingQueryRunner.ExecuteQuery(sq2, settings);
Assert.That(result2.Columns [0].RequestColumn, Is.EqualTo(sq2.SelectColumns [0]));
mockQueryRunner.Verify(x => x.ExecutePrebuiltQuery(sq1, settings, It.IsNotNull <QueryBuild>( )), Times.Exactly(1));
mockQueryRunner.VerifyAll( );
}
/// <summary>
/// 検索結果取得
/// </summary>
/// <remarks>
/// 作成者 ： nakao
/// 作成日 ： 2019/11/08
/// </remarks>
public void GetSearchResults()
{
// 検索条件が正しければ検索実施
if (Condition.ValidateSearch() == false)
{
return;
}
DataSet dtSet = null;
DataBase dataBase = null;
try
{
long unfinishedCount = 0;
SearchResults = new List <SearchResult>();
List <object> paraList = new List <object>();
string queryStr = QueryBuild.GetSeihanMgmtList(Condition, ref paraList);
dataBase = new DataBase();
dataBase.ConnectDB();
dtSet = dataBase.GetDataSet(queryStr, paraList.ToArray()); // クエリ実行
foreach (DataRow row in dtSet.Tables[0].Rows)
{
SearchResult sr = new SearchResult(row);
SearchResults.Add(sr);
if (sr.IsUnfinished())
{
unfinishedCount++; // 未完了集計
}
}
ResultCount = unfinishedCount.ToString() + "/" + SearchResults.Count.ToString();
dataBase.DisconnectDB();
}
catch (Exception ex)
{
SearchErrorMessage = Resources.TextResource.ErrorGetSearchResult;
}
finally
{
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
}
}
/// <summary>
/// 検索結果取得
/// </summary>
/// <remarks>
/// 作成者 ： sesaki
/// 作成日 ： 2019/09/19
/// </remarks>
public void GetSearchResults()
{
// 検索条件が正しければ検索実施
if (Condition.ValidateSearch() == false)
{
return;
}
DataSet dtSet = null;
DataBase dataBase = null;
try
{
long totalCount = 0;
SearchResults = new List <SearchResult>();
List <object> paraList = new List <object>();
string queryStr = QueryBuild.GetSappanSearch(Condition, ref paraList);
dataBase = new DataBase();
dataBase.ConnectDB();
dtSet = dataBase.GetDataSet(queryStr, paraList.ToArray()); // クエリ実行
totalCount = dtSet.Tables[0].Rows.Count; // 全体件数
foreach (DataRow row in dtSet.Tables[0].Rows)
{
SearchResult sr = new SearchResult(row);
if (Condition.OutPut_Chk || sr.CheckOutPut(Condition.Process))
{
SearchResults.Add(sr);
}
}
ResultCount = SearchResults.Count.ToString() + "/" + totalCount.ToString();
dataBase.DisconnectDB();
}
catch (Exception ex)
{
SearchErrorMessage = Resources.TextResource.ErrorGetSearchResult;
}
finally
{
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
}
}
/// <summary>
/// Legacy API for accessing the query builder.
/// </summary>
public static string GetSql(StructuredQuery query, bool debugQueries = false)
{
var settings = new QuerySettings
{
DebugMode = debugQueries,
Hint = "QueryBuilder.GetSql"
};
QueryBuild result = GetSql(query, settings);
return(result.Sql);
}
/// <summary>
/// 進捗データ取得
/// </summary>
/// <param name="dpyno">伝票No</param>
/// <param name="process">工程</param>
/// <remarks>
/// 作成者 ： kawana
/// 作成日 ： 2019/10/24
/// </remarks>
private void GetMgmt(string dpyno, string process)
{
DataSet dtSet = null;
DataBase dataBase = null;
try
{
dataBase = new DataBase();
List <object> paraList = new List <object>();
string sqlStr = QueryBuild.GetJushihanMgmt(dpyno, ref paraList);
dataBase.ConnectDB();
dtSet = dataBase.GetDataSet(sqlStr, paraList.ToArray());
dataBase.DisconnectDB();
foreach (DataRow row in dtSet.Tables[0].Rows)
{
string cd = row["PROCESS_CD"].ToString();
bool disabled_flg = (cd != process);
switch (cd)
{
case Constants.PROCESS_SPNSEIZO:
Spnseizo = new Register(row, disabled_flg);
break;
case Constants.PROCESS_SPNKENSA:
Spnkensa = new Register(row, disabled_flg);
break;
case Constants.PROCESS_GYOUMU:
Gyoumu = new Register(row, disabled_flg);
break;
}
}
}
catch (Exception ex)
{
ErrorGetMgmtMessage = Resources.TextResource.ErrorGetMgmt;
}
finally
{
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
}
}
private void RunTest(string testName, Action <StructuredQuery> additionalProcessing = null, int derivedTypesTempTableThreshold = -1)
{
XmlDocument doc = GetTestCaseXml( );
XmlNode test = doc.DocumentElement.SelectSingleNode("Test[@name='" + testName + "']");
if (test == null)
{
throw new Exception("Test not found in Query tests.xml: " + testName);
}
// Load test
StructuredQuery query;
try
{
query = StructuredQueryHelper.FromXml(test.FirstChild);
}
catch (Exception ex)
{
throw new Exception("Failed to load query for " + testName, ex);
}
query.TimeZoneName = TimeZoneHelper.SydneyTimeZoneName;
if (additionalProcessing != null)
{
additionalProcessing(query);
}
// Generate SQL
var settings = new QuerySettings {
Hint = "test", DebugMode = true, DerivedTypesTempTableThreshold = derivedTypesTempTableThreshold
};
QueryBuild result = QueryBuilder.GetSql(query, settings);
string sql = result.Sql;
sql = Canonical(sql);
// Get expected results
XmlNode selectSingleNode = test.SelectSingleNode("Expect/text()");
if (selectSingleNode != null)
{
string expected = Canonical(selectSingleNode.Value);
expected = expected.Replace("{userResource}", new EntityRef("core", "userResource").Id.ToString(CultureInfo.InvariantCulture));
Assert.AreEqual(expected, sql, "Generated SQL did not match expected.");
}
StructuredQueryHelper.ToXml(query);
}
[Ignore] // Blocking layer has been disabled for this cache.
public void Test_EnsureCached_TwoThreads(bool resultIsUserSpecific, bool runAsDifferentUser, bool expectedToCache)
{
CachingQueryRunner cachingQueryRunner;
Mock <IQuerySqlBuilder> mockQuerySqlBuilder;
Mock <IQueryRunner> mockQueryRunner;
IQueryRunner queryRunner;
StructuredQuery structuredQuery;
QuerySettings settings1;
QuerySettings settings2;
QueryBuild queryBuild;
QueryResult result;
IQuerySqlBuilder queryBuilder;
IUserRuleSetProvider userRuleSetProvider = MockUserRuleSetProvider( );
structuredQuery = new StructuredQuery( );
settings1 = new QuerySettings( );
settings1.RunAsUser = 1;
settings2 = new QuerySettings( );
settings2.RunAsUser = runAsDifferentUser ? 2 : 1;
queryBuild = new QueryBuild( );
result = new QueryResult(queryBuild);
queryBuild.DataReliesOnCurrentUser = resultIsUserSpecific;
mockQuerySqlBuilder = new Mock <IQuerySqlBuilder>(MockBehavior.Strict);
mockQuerySqlBuilder.Setup(x => x.BuildSql(It.IsAny <StructuredQuery>( ), It.IsNotNull <QuerySqlBuilderSettings>( ))).Returns(queryBuild);
queryBuilder = mockQuerySqlBuilder.Object;
mockQueryRunner = new Mock <IQueryRunner>(MockBehavior.Strict);
mockQueryRunner.Setup(x => x.ExecutePrebuiltQuery(structuredQuery, It.IsNotNull <QuerySettings>(), It.IsNotNull <QueryBuild>( )))
.Returns(() =>
{
Thread.Sleep(100);
return(result);
})
.Verifiable( );
queryRunner = mockQueryRunner.Object;
cachingQueryRunner = new CachingQueryRunner(queryRunner, userRuleSetProvider, queryBuilder);
Task task1 = Task.Factory.StartNew(() => cachingQueryRunner.ExecuteQuery(structuredQuery, settings1));
Thread.Sleep(1); // BlockIfPending can handle overlapping, but no coincident requests
Task task2 = Task.Factory.StartNew(() => cachingQueryRunner.ExecuteQuery(structuredQuery, settings2));
Task.WaitAll(task1, task2);
int calls = expectedToCache ? 1 : 2;
mockQueryRunner.Verify(x => x.ExecutePrebuiltQuery(structuredQuery, It.IsNotNull <QuerySettings>(), It.IsNotNull <QueryBuild>( )), Times.Exactly(calls));
mockQueryRunner.VerifyAll( );
}
/// <summary>
/// 進捗データ登録
/// </summary>
/// <param name="dpyno">伝票No</param>
/// <param name="process">工程コード</param>
/// <param name="uid">ユーザーID</param>
/// <returns>処理成否</returns>
/// <remarks>
/// 作成者 ： nakao
/// 作成日 ： 2019/09/01
/// </remarks>
public bool RegistMgmt(string dpyno, string process, string uid)
{
DataSet dtSet = null;
DataBase dataBase = null;
try
{
string sqlStr;
Register register;
List<object> paraList = new List<object>();
dataBase = new DataBase();
dataBase.ConnectDB();
// サブ１登録
register = GetTargetRegister(process, Constants.PROCESS_SUB1);
sqlStr = QueryBuild.RegistSeihanMgmt(register, dpyno, process, Constants.PROCESS_SUB1, uid, ref paraList);
dataBase.CommitData(sqlStr, paraList.ToArray());
// 編集と検査はサブ２登録
if (process.Equals(Constants.PROCESS_HENSHU) || process.Equals(Constants.PROCESS_KENSA))
{
paraList.Clear();
register = GetTargetRegister(process, Constants.PROCESS_SUB2);
sqlStr = QueryBuild.RegistSeihanMgmt(register, dpyno, process, Constants.PROCESS_SUB2, uid, ref paraList);
dataBase.CommitData(sqlStr, paraList.ToArray());
}
dataBase.DisconnectDB();
}
catch
{
return false;
}
finally
{
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
}
return true;
}
/// <summary>
/// ヘッダ情報取得
/// </summary>
/// <param name="customer">得意先コード</param>
/// <remarks>
/// 作成者 ： sesaki
/// 作成日 ： 2019/11/21
/// </remarks>
private void GetHeader(string customer)
{
DataSet dtSet = null;
DataBase dataBase = null;
try
{
dataBase = new DataBase();
List <object> paraList = new List <object>();
string sqlStr;
if (GurabiaCustomer.IsTokan(customer))
{
sqlStr = QueryBuild.GetGurabiaHeaderTokan(DPY_NO, ref paraList);
}
else
{
sqlStr = QueryBuild.GetGurabiaHeaderToyo(DPY_NO, ref paraList);
}
dataBase.ConnectDB();
dtSet = dataBase.GetDataSet(sqlStr, paraList.ToArray());
DataRow row = dtSet.Tables[0].Rows[0];
CUSTMER_RNM = row["CUSTMER_RNM"].ToString();
SUBNEGA_NO = row["SUBNEGA_NO"].ToString();
BURANDO_NM = row["BURANDO_NM"].ToString();
SYOHIN_NM = row["SYOHIN_NM"].ToString();
KAN_NM = row["KAN_NM"].ToString();
KSKBN_NM = row["KSKBN_NM"].ToString();
COLOR_NM = row["COLOR_NM"].ToString();
dataBase.DisconnectDB();
}
catch (Exception ex)
{
}
finally
{
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
}
}
/// <summary>
/// Constructor.
/// </summary>
public CachingQuerySqlBuilderValue(StructuredQuery originalQuery, QueryBuild queryResult)
{
if (originalQuery == null)
{
throw new ArgumentNullException("originalQuery");
}
if (queryResult == null)
{
throw new ArgumentNullException("queryResult");
}
OriginalQuery = originalQuery;
QueryResult = queryResult;
CacheTime = DateTime.Now;
}
/// <summary>
/// CSV出力データ取得
/// </summary>
/// <returns>取得処理成否</returns>
/// <remarks>
/// 作成者 ： nakao
/// 作成日 ： 2019/09/01
/// </remarks>
public bool GetCsvData()
{
// 出力条件が正しければCSVデータ取得
if (Condition.ValidateOutput() == false)
{
return(false);
}
DataSet dtSet = null;
DataBase dataBase = null;
try
{
dataBase = new DataBase();
List <object> paraList = new List <object>();
string queryStr = QueryBuild.GetSeihanCsv(Condition, ref paraList);
dataBase.ConnectDB();
dtSet = dataBase.GetDataSet(queryStr, paraList.ToArray()); // クエリ実行
DataTable table = dtSet.Tables[0];
CsvData = Utilities.DataTableToCsv(table);
dataBase.DisconnectDB();
CsvName = Utilities.GetCsvFileName(Resources.TextResource.ProgressSeihan);
return(true);
}
catch (Exception ex)
{
CsvErrorMessage = Resources.TextResource.ErrorOutputCsv;
return(false);
}
finally
{
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
}
}
/// <summary>
/// Refer to CachingQuerySqlBuilder.MutateResultToMatchCurrentQuery
/// </summary>
internal static QueryResult MutateResultToMatchCurrentQuery(CachingQueryRunnerValue cacheValue, StructuredQuery currentQuery)
{
if (cacheValue == null)
{
throw new ArgumentNullException("cacheValue");
}
if (currentQuery == null)
{
throw new ArgumentNullException("currentQuery");
}
// Clone result
QueryBuild queryBuild = CachingQuerySqlBuilder.MutateResultToMatchCurrentQuery(cacheValue.QueryResult.QueryBuild, cacheValue.OriginalQuery, currentQuery);
QueryResult result = cacheValue.QueryResult.ShallowClone(queryBuild);
return(result);
}
//[Explicit]
//[Test]
//[TestCaseSource( "RunReport_GetTestData" )]
public void RunQuery(TenantInfo tenant, long reportId, string reportName)
{
using (tenant.GetTenantAdminContext( ))
{
// Load report
Report report = Factory.GraphEntityRepository.Get <Report>(reportId, ReportHelpers.ReportPreloaderQuery);
Assert.That(report, Is.Not.Null, "Report entity not null");
// Convert to structured query
StructuredQuery structuredQuery = Factory.ReportToQueryConverter.Convert(report);
Assert.That(structuredQuery, Is.Not.Null, "StructuredQuery entity not null");
// Check for errors reported during conversionduring reporting (e.g. missing schema entities can't be found)
if (structuredQuery.InvalidReportInformation != null)
{
foreach (var pair in structuredQuery.InvalidReportInformation)
{
Assert.That(pair.Value, Is.Empty, "Errors detected for " + pair.Key);
}
}
// Build query
QuerySqlBuilderSettings buildSettings = new QuerySqlBuilderSettings {
Hint = "ReportTenantTests",
SecureQuery = true,
SupportPaging = true
};
QueryBuild queryBuild = Factory.NonCachedQuerySqlBuilder.BuildSql(structuredQuery, buildSettings);
Assert.That(queryBuild, Is.Not.Null, "QueryBuild entity not null");
Assert.That(queryBuild.Sql, Is.Not.Null, "QueryBuild.Sql entity not null");
// Run query
QuerySettings runSettings = new QuerySettings
{
Hint = "ReportTenantTests",
FirstRow = 0,
PageSize = 1,
SecureQuery = true,
SupportPaging = true
};
QueryResult result = Factory.Current.ResolveNamed <IQueryRunner>(Factory.NonCachedKey).ExecutePrebuiltQuery(structuredQuery, runSettings, queryBuild);
//QueryResult result = Factory.NonCachedQueryRunner.ExecutePrebuiltQuery( structuredQuery, runSettings, queryBuild );
Assert.That(result, Is.Not.Null, "QueryResult entity not null");
}
}
/// <summary>
/// ヘッダ情報取得
/// </summary>
/// <remarks>
/// 作成者 ： nakao
/// 作成日 ： 2019/09/01
/// </remarks>
private void GetHeader()
{
DataSet dtSet = null;
DataBase dataBase = null;
try
{
dataBase = new DataBase();
List <object> paraList = new List <object>();
string sqlStr = QueryBuild.GetSeihanHeader(DPY_NO, ref paraList);
dataBase.ConnectDB();
dtSet = dataBase.GetDataSet(sqlStr, paraList.ToArray());
DataRow row = dtSet.Tables[0].Rows[0];
SUBNEGA_NO = row["SUBNEGA_NO"].ToString();
CMNY = row["CMNY_NM"].ToString();
CMKBN = row["CMKBN_NM"].ToString();
BURANDO_NM = row["BURANDO_NM"].ToString();
SYOHIN_NM = row["SYOHIN_NM"].ToString();
KAN_NM = row["KAN_NM"].ToString();
ZUMEN_NO = row["ZUMEN_NO"].ToString();
HAN_KBN = row["HAN_KBN"].ToString();
COLOR_NM = row["COLOR_NM"].ToString();
KSKBN = row["KSKBN_NM"].ToString();
KSZAI = row["KOSEIZAI"].ToString();
KSBUSU = row["GOKEI_CT"].ToString();
dataBase.DisconnectDB();
}
catch (Exception ex)
{
}
finally
{
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
}
}
/// <summary>
/// 工程リスト取得
/// </summary>
/// <remarks>
/// 作成者 ： kawana
/// 作成日 ： 2019/10/24
/// </remarks>
private void GetProcessList()
{
DataSet dtSet = null;
DataBase dataBase = null;
List <SelectListItem> list = new List <SelectListItem>();
try
{
dataBase = new DataBase();
string sqlStr = QueryBuild.GetProcess(Constants.PRODUCT_JUSHIHAN);
dataBase.ConnectDB();
dtSet = dataBase.GetDataSet(sqlStr, null);
foreach (DataRow row in dtSet.Tables[0].Rows)
{
list.Add(new SelectListItem
{
Text = row["PROCESS_NM"].ToString(),
Value = row["PROCESS_CD"].ToString()
});
}
dataBase.DisconnectDB();
}
catch (Exception ex)
{
ConditionErrorMessage = Resources.TextResource.ErrorGetCondition;
}
finally
{
ProcessList = new SelectList(list, "Value", "Text");
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
}
}
public void Test_EnsureCached(bool resultIsUserSpecific, bool runAsDifferentUser, bool expectedToCache)
{
CachingQueryRunner cachingQueryRunner;
Mock <IQuerySqlBuilder> mockQuerySqlBuilder;
Mock <IQueryRunner> mockQueryRunner;
IQueryRunner queryRunner;
StructuredQuery structuredQuery;
QuerySettings settings;
QueryBuild queryBuild;
QueryResult result;
IQuerySqlBuilder queryBuilder;
IUserRuleSetProvider userRuleSetProvider = MockUserRuleSetProvider( );
structuredQuery = new StructuredQuery( );
settings = new QuerySettings( );
settings.RunAsUser = 1;
queryBuild = new QueryBuild( );
result = new QueryResult(queryBuild);
queryBuild.DataReliesOnCurrentUser = resultIsUserSpecific;
mockQuerySqlBuilder = new Mock <IQuerySqlBuilder>(MockBehavior.Strict);
mockQuerySqlBuilder.Setup(x => x.BuildSql(It.IsAny <StructuredQuery>( ), It.IsNotNull <QuerySqlBuilderSettings>( ))).Returns(queryBuild);
queryBuilder = mockQuerySqlBuilder.Object;
mockQueryRunner = new Mock <IQueryRunner>(MockBehavior.Strict);
mockQueryRunner.Setup(x => x.ExecutePrebuiltQuery(structuredQuery, settings, It.IsNotNull <QueryBuild>( )))
.Returns(() => result)
.Verifiable( );
queryRunner = mockQueryRunner.Object;
cachingQueryRunner = new CachingQueryRunner(queryRunner, userRuleSetProvider, queryBuilder);
cachingQueryRunner.ExecuteQuery(structuredQuery, settings);
if (runAsDifferentUser)
{
settings.RunAsUser = 2;
}
cachingQueryRunner.ExecuteQuery(structuredQuery, settings);
int calls = expectedToCache ? 1 : 2;
mockQueryRunner.Verify(x => x.ExecutePrebuiltQuery(structuredQuery, settings, It.IsNotNull <QueryBuild>( )), Times.Exactly(calls));
mockQueryRunner.VerifyAll( );
}
/// <summary>
/// 担当者リスト取得
/// </summary>
/// <remarks>
/// 作成者 ： nakao
/// 作成日 ： 2019/09/01
/// </remarks>
private void GetEmployeeList()
{
DataSet dtSet = null;
DataBase dataBase = null;
List<SelectListItem> list = new List<SelectListItem>();
try
{
dataBase = new DataBase();
string sqlStr = QueryBuild.GetEmployee();
dataBase.ConnectDB();
dtSet = dataBase.GetDataSet(sqlStr, null);
foreach (DataRow row in dtSet.Tables[0].Rows)
{
list.Add(new SelectListItem
{
Text = row["EMPLOYEE_NM"].ToString(),
Value = row["EMPLOYEE_CD"].ToString()
});
}
dataBase.DisconnectDB();
}
catch (Exception ex)
{
}
finally
{
EmployeeList = new SelectList(list, "Value", "Text");
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
}
}
/// <summary>
/// 進捗データ登録
/// </summary>
/// <param name="dpyno">伝票No</param>
/// <param name="process">工程</param>
/// <param name="uid">ユーザーID</param>
/// <returns>処理成否</returns>
/// <remarks>
/// 作成者 ： kawana
/// 作成日 ： 2019/10/24
/// </remarks>
public bool RegistMgmt(string dpyno, string process, string uid)
{
DataSet dtSet = null;
DataBase dataBase = null;
try
{
string sqlStr;
Register register;
List <object> paraList = new List <object>();
dataBase = new DataBase();
dataBase.ConnectDB();
register = GetTargetRegister(process);
sqlStr = QueryBuild.RegistJushihanMgmt(register, dpyno, process, Constants.PROCESS_SUB1, uid, ref paraList);
dataBase.CommitData(sqlStr, paraList.ToArray());
dataBase.DisconnectDB();
}
catch
{
return(false);
}
finally
{
if (dataBase != null)
{
dataBase.DisconnectDB();
}
if (dtSet != null)
{
dtSet.Dispose();
}
}
return(true);
}
public void Test_EnsureDifferentInstancesCanCacheMatch( )
{
CachingQueryRunner cachingQueryRunner;
Mock <IQueryRunner> mockQueryRunner;
IQueryRunner queryRunner;
QuerySettings settings;
QueryBuild queryBuild;
QueryResult result;
IQuerySqlBuilder queryBuilder = MockQuerySqlBuilder( );
IUserRuleSetProvider userRuleSetProvider = MockUserRuleSetProvider( );
StructuredQuery sq1 = ReportHelpers.BuildFilterQuery("Name='test1'", new EntityRef("test:person"), true);
sq1.SelectColumns.Add(new SelectColumn {
Expression = new ResourceExpression(sq1.RootEntity, "core:name")
});
StructuredQuery sq2 = ReportHelpers.BuildFilterQuery("Name='test1'", new EntityRef("test:person"), true);
sq2.SelectColumns.Add(new SelectColumn {
Expression = new ResourceExpression(sq2.RootEntity, "core:name")
});
settings = new QuerySettings( );
queryBuild = new QueryBuild( );
result = new QueryResult(queryBuild);
mockQueryRunner = new Mock <IQueryRunner>(MockBehavior.Strict);
mockQueryRunner.Setup(x => x.ExecutePrebuiltQuery(sq1, settings, It.IsNotNull <QueryBuild>( )))
.Returns(() => result)
.Verifiable( );
queryRunner = mockQueryRunner.Object;
cachingQueryRunner = new CachingQueryRunner(queryRunner, userRuleSetProvider, queryBuilder);
cachingQueryRunner.ExecuteQuery(sq1, settings);
cachingQueryRunner.ExecuteQuery(sq2, settings);
mockQueryRunner.Verify(x => x.ExecutePrebuiltQuery(sq1, settings, It.IsNotNull <QueryBuild>( )), Times.Exactly(1));
mockQueryRunner.VerifyAll( );
}
/// <summary>
/// The problem: we want to cache queries, but the StructuredQueries contain GUIDs that vary from call to call.
/// The CachingQuerySqlBuilderKey happily handles this via StructuredQuery.CacheKeyToken. However, the result contains
/// references back to the original query. And stuff down stream relies on having the correct GUIDs in the referenced columns.
/// The solution: we take a shallowing copy of the result, and substutite in the columns from our current query.
/// </summary>
internal static QueryBuild MutateResultToMatchCurrentQuery(QueryBuild queryBuild, StructuredQuery originalQuery, StructuredQuery currentQuery)
{
if (queryBuild == null)
{
throw new ArgumentNullException("queryBuild");
}
if (originalQuery == null)
{
throw new ArgumentNullException("originalQuery");
}
if (currentQuery == null)
{
throw new ArgumentNullException("currentQuery");
}
// Clone result
QueryBuild result = queryBuild.ShallowClone();
result.Columns = MutateColumnList(result.Columns, originalQuery.SelectColumns, currentQuery.SelectColumns);
result.AggregateColumns = MutateColumnList(result.AggregateColumns, originalQuery.SelectColumns, currentQuery.SelectColumns);
return(result);
}
/// <summary>
/// Add all database query parameters.
/// </summary>
private void AddAllParameters(QuerySettings settings, QueryBuild result, IDatabaseContext context, IDbDataAdapter adapter)
{
IDbCommand selectCommand = adapter.SelectCommand;
selectCommand.AddParameter(TenantParameterName, DatabaseType.IdentifierType, RequestContext.TenantId);
if (result.DataReliesOnCurrentUser)
{
selectCommand.AddParameter(UserParameterName, DatabaseType.IdentifierType, settings.RunAsUser);
}
if (result.EntityBatchDataTable != null)
{
selectCommand.AddTableValuedParameter(EntityBatchParameterName, result.EntityBatchDataTable);
}
AddQuickSearchParameter(settings, selectCommand);
AddFauxRelationshipParameters(settings, selectCommand);
AddRootIdFilterParameter(settings, selectCommand);
if (settings.ValueList != null)
{
selectCommand.AddStringListParameter("@valueList", settings.ValueList);
}
if (settings.SupportPaging)
{
SetPage(settings.FirstRow, settings.FirstRow + settings.PageSize, selectCommand);
}
if (result.SharedParameters != null)
{
foreach (KeyValuePair <ParameterValue, string> parameter in result.SharedParameters)
{
selectCommand.AddParameter(parameter.Value, parameter.Key.Type, parameter.Key.Value);
}
}
}
/// <summary>
/// Executes the specified query.
/// </summary>
/// <param name="query">The query. This cannot be null.</param>
/// <param name="settings">Settings to control SQL generation. Defaults are used if omitted.</param>
/// <param name="queryBuild">The prebuilt query.</param>
/// <returns>
/// A <see cref="QueryResult"/> containing the query and related details.
/// </returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="query"/> cannot be null.
/// </exception>
public QueryResult ExecutePrebuiltQuery(StructuredQuery query, QuerySettings settings, QueryBuild queryBuild)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
if (queryBuild == null)
{
throw new ArgumentNullException(nameof(queryBuild));
}
if (settings == null)
{
settings = new QuerySettings
{
Hint = "QueryEngine"
};
}
using (Profiler.Measure("QueryRunner.ExecuteQuery {0}", settings.Hint))
{
QueryResult result = new QueryResult(queryBuild);
string sql;
DataTable dataTable;
if (settings.ResultSchemaOnly)
{
// This is a bit of a hack. Ideally someone who wants schema only could just use the SqlBuilder .. but that isn't working.
// Likely due to the mutate that's currently happening in the runner caching layer. Maybe we can move it to the builder layer.
return(result);
}
// Check security settings (ok if it happens twice)
VerifySecuritySettings(settings);
sql = queryBuild.Sql;
using (IDatabaseContext context = DatabaseProvider.GetContext())
{
IDbDataAdapter adapter = context.CreateDataAdapter(sql);
using (adapter as IDisposable)
{
try
{
AddAllParameters(settings, queryBuild, context, adapter);
dataTable = new DataTable();
FillFromAdapter(dataTable, context, adapter, settings);
}
catch (SqlException ex)
{
if (ex.ErrorCode == ExceededCpuErrorCode && ex.Class == SeverityLevelResourceExhausted) // Exceeded report CPU limit
{
EventLog.Application.WriteTrace("{0}\r\nFailed to run report query due to Cpu Limit exceeded. Hint: {1}\r\n\r\n{2}\r\n\r\n{3}", ex.Message, settings.Hint, ex, sql);
throw new ExceededReportCpuLimitException(ex);
}
EventLog.Application.WriteError("{0}\r\nFailed to run report query due to sql error. Hint: {1}\r\n\r\n{2}\r\n\r\n{3}", ex.Message, settings.Hint, ex, sql);
throw;
}
catch (Exception ex)
{
EventLog.Application.WriteError("{0}\r\nFailed to run report query. Hint: {1}\r\n\r\n{2}\r\n\r\n{3}", ex.Message, settings.Hint, ex, sql);
throw;
}
}
StructureViewHelper.SecureStructureViewData(settings, result, dataTable);
MetadataHelper.CaptureMetadata(query, settings, result, dataTable);
}
// Note: identify cache dependencies after getting SQL, so that any calculations are resolved into the structured query.
IdentifyCacheDependencies(queryBuild.FinalStructuredQuery, settings);
return(result);
}
}
