/// <summary>
/// Query datas
/// </summary>
/// <typeparam name="T">Data type</typeparam>
/// <param name="server">Database server</param>
/// <param name="command">Command</param>
/// <returns>Return datas</returns>
public async Task <IEnumerable <T> > QueryAsync <T>(DatabaseServer server, ICommand command)
{
if (command.Query == null)
{
throw new EZNEWException("ICommand.Query is null");
}
#region query translate
IQueryTranslator translator = MySqlFactory.GetQueryTranslator(server);
var tranResult = translator.Translate(command.Query);
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
#region script
string cmdText;
switch (command.Query.QueryType)
{
case QueryCommandType.Text:
cmdText = tranResult.ConditionString;
break;
case QueryCommandType.QueryObject:
default:
int size = command.Query.QuerySize;
string objectName = DataManager.GetEntityObjectName(DatabaseServerType.MySQL, command.EntityType, command.ObjectName);
cmdText = $"{tranResult.PreScript}SELECT {string.Join(",", FormatQueryFields(translator.ObjectPetName, command.Query, command.EntityType, out var defaultFieldName))} FROM `{objectName}` AS {translator.ObjectPetName} {joinScript} {(string.IsNullOrWhiteSpace(tranResult.ConditionString) ? string.Empty : $"WHERE {tranResult.ConditionString}")} {(string.IsNullOrWhiteSpace(tranResult.OrderString) ? string.Empty : $"ORDER BY {tranResult.OrderString}")} {(size > 0 ? $"LIMIT 0,{size}" : string.Empty)}";
break;
}
private static TResult AppendFunction <TResult>(string function, Expression <Func <TEntity, object> > lambdaExpression, Expression <Func <TEntity, object> > overLambdaExpression, bool distinct, Expression <Func <TEntity, object> > caseStatemenResultLambdaExpression, bool useExpressionSelectorAsAlias)
where TResult : struct
{
IQueryTranslator queryTranslator = GetQueryTranslator();
string translatedExpression = queryTranslator.Translate(lambdaExpression, useExpressionSelectorAsAlias: useExpressionSelectorAsAlias);
string result = string.Empty;
if (string.IsNullOrEmpty(translatedExpression))
{
throw new InvalidOperationException("Invalid property");
}
else
{
string overExpression = queryTranslator.Translate(overLambdaExpression, useExpressionSelectorAsAlias: useExpressionSelectorAsAlias);
string caseStatemenResultExpression = (caseStatemenResultLambdaExpression != null) ? queryTranslator.Translate(caseStatemenResultLambdaExpression, useExpressionSelectorAsAlias: useExpressionSelectorAsAlias) : string.Empty;
if (GetExpressionOperandType(lambdaExpression) != typeof(Boolean))
{
result = string.Format(" {0}({1}{2})", function, ((distinct == true) ? "distinct " : string.Empty), translatedExpression);
}
else
{
result = string.Format(" {0}({1}CASE WHEN {2} THEN {3} END)", function, ((distinct == true) ? "distinct " : string.Empty), translatedExpression, (string.IsNullOrEmpty(caseStatemenResultExpression) ? "1" : caseStatemenResultExpression));
}
if (!string.IsNullOrEmpty(overExpression))
{
result += string.Format(" OVER(PARTITION BY {0})", overExpression);
}
return(SaveTextResultAndReturnGenericResult <TResult>(result));
}
}
/// <summary>
/// data config
/// </summary>
/// <param name="config">data config</param>
public static void Config(DataConfig config)
{
if (config == null || config.Servers == null || config.Servers.Count <= 0)
{
return;
}
foreach (var serverItem in config.Servers)
{
if (serverItem.Value == null)
{
continue;
}
//register dbengine
if (!serverItem.Value.EngineFullTypeName.IsNullOrEmpty())
{
IDbEngine engine = (IDbEngine)Activator.CreateInstance(Type.GetType(serverItem.Value.EngineFullTypeName));
RegisterDBEngine(serverItem.Key, engine);
}
//register query translator
if (!serverItem.Value.QueryTranslatorFullTypeName.IsNullOrEmpty())
{
IQueryTranslator translator = (IQueryTranslator)Activator.CreateInstance(Type.GetType(serverItem.Value.QueryTranslatorFullTypeName));
RegisterQueryTranslator(serverItem.Key, translator);
}
//entity config
if (!serverItem.Value.EntityConfigs.IsNullOrEmpty())
{
foreach (var entityConfig in serverItem.Value.EntityConfigs)
{
ConfigServerTypeEntity(serverItem.Key, entityConfig.Key, entityConfig.Value);
}
}
}
}
/// <summary>
/// get translator
/// </summary>
/// <param name="server"></param>
/// <returns></returns>
public static IQueryTranslator GetTranslator(ServerInfo server)
{
if (server == null)
{
return(null);
}
IQueryTranslator translator = null;
if (DataManager.Translators.TryGetValue(server.ServerType, out translator) && translator != null)
{
return(translator);
}
switch (server.ServerType)
{
case ServerType.SQLServer:
translator = new SqlServerQueryTranslator();
break;
case ServerType.MySQL:
translator = new MySqlQueryTranslator();
break;
case ServerType.Oracle:
translator = new OracleQueryTranslator();
break;
}
return(translator);
}
public QueryCompiler(IQueryOptimizer queryOptimizer, IQueryTranslator queryTranslator, IQuerySender requestSender, string?discriminator)
{
_queryOptimizer = queryOptimizer;
_queryTranslator = queryTranslator;
_requestSender = requestSender;
_discriminator = discriminator;
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="mapping">Mappings</param>
/// <param name="queryTranslator">Query translator</param>
/// <param name="typeRegister">Type Register</param>
public QueryPlan(IMapping mapping, IQueryTranslator queryTranslator, ITypeRegister typeRegister)
{
this._mapping = mapping;
this._query = CreateQueryHeader();
this._queryTranslator = queryTranslator;
this._typeRegister = typeRegister;
}
/// <summary>
/// Construtor padrão.
/// </summary>
/// <param name="translator"></param>
/// <param name="info">Informações da query.</param>
public CacheQueryParser(IQueryTranslator translator, QueryInfo info)
{
translator.Require("translator").NotNull();
info.Require("info").NotNull();
_translator = translator;
_queryInfo = info;
}
/// <summary>
/// Construtor da classe
/// </summary>
/// <param name="translator">Tradutor de nomes dos objetos CLR para tabelas SQL.</param>
/// <param name="typeSchema">Classe de recuperação de metadados.</param>
public SqlQueryParser(IQueryTranslator translator, ITypeSchema typeSchema)
{
translator.Require("translator").NotNull();
typeSchema.Require("typeSchema").NotNull();
_typeSchema = typeSchema;
_translator = translator;
}
/// <summary>
/// Query datas
/// </summary>
/// <typeparam name="T">Data type</typeparam>
/// <param name="server">Database server</param>
/// <param name="command">Command</param>
/// <returns>Return datas</returns>
public async Task <IEnumerable <T> > QueryAsync <T>(DatabaseServer server, ICommand command)
{
if (command.Query == null)
{
throw new EZNEWException($"{nameof(ICommand.Query)} is null");
}
#region query translation
IQueryTranslator translator = PostgreSqlManager.GetQueryTranslator(DataAccessContext.Create(server, command));
var tranResult = translator.Translate(command.Query);
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
#region script
string cmdText;
switch (command.Query.ExecutionMode)
{
case QueryExecutionMode.Text:
cmdText = tranResult.ConditionString;
break;
case QueryExecutionMode.QueryObject:
default:
int size = command.Query.QuerySize;
string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command);
string sortString = string.IsNullOrWhiteSpace(tranResult.SortString) ? string.Empty : $"ORDER BY {tranResult.SortString}";
var queryFields = PostgreSqlManager.GetQueryFields(command.Query, command.EntityType, true);
string outputFormatedField = string.Join(",", PostgreSqlManager.FormatQueryFields(translator.ObjectPetName, queryFields, true));
if (string.IsNullOrWhiteSpace(tranResult.CombineScript))
{
cmdText = $"{tranResult.PreScript}SELECT {outputFormatedField} FROM {PostgreSqlManager.WrapKeyword(objectName)} AS {translator.ObjectPetName} {joinScript} {(string.IsNullOrWhiteSpace(tranResult.ConditionString) ? string.Empty : $"WHERE {tranResult.ConditionString}")} {sortString} {(size > 0 ? $"LIMIT {size} OFFSET 0" : string.Empty)}";
}
/// <summary>
/// query data list offset the specified numbers
/// </summary>
/// <typeparam name="T">data type</typeparam>
/// <param name="server">database server</param>
/// <param name="cmd">command</param>
/// <param name="offsetNum">offset num</param>
/// <param name="size">query size</param>
/// <returns></returns>
public async Task <IEnumerable <T> > QueryOffsetAsync <T>(ServerInfo server, ICommand cmd, int offsetNum = 0, int size = int.MaxValue)
{
if (cmd.Query == null)
{
throw new EZNEWException("ICommand.Query is null");
}
#region query object translate
IQueryTranslator translator = QueryTranslator.GetTranslator(server);
var tranResult = translator.Translate(cmd.Query);
#endregion
#region execute
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
StringBuilder cmdText = new StringBuilder();
switch (cmd.Query.QueryType)
{
case QueryCommandType.Text:
cmdText.Append(tranResult.ConditionString);
break;
case QueryCommandType.QueryObject:
default:
string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName);
string defaultFieldName = string.Empty;
List <string> formatQueryFields = FormatQueryFields(translator.ObjectPetName, cmd.Query, cmd.EntityType, out defaultFieldName);
cmdText.AppendFormat("{4}SELECT COUNT({3}.[{0}]) OVER() AS QueryDataTotalCount,{1} FROM [{2}] AS {3}{5}"
, defaultFieldName
, string.Join(",", formatQueryFields)
, objectName
, translator.ObjectPetName
, tranResult.PreScript
, joinScript);
if (!tranResult.ConditionString.IsNullOrEmpty())
{
cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
}
if (!tranResult.OrderString.IsNullOrEmpty())
{
cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
}
else
{
cmdText.AppendFormat(" ORDER BY {0}.[{1}] DESC", translator.ObjectPetName, defaultFieldName);
}
cmdText.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", offsetNum, size);
break;
}
#endregion
using (var conn = DbServerFactory.GetConnection(server))
{
var tran = GetQueryTransaction(conn, cmd.Query);
return(await conn.QueryAsync <T>(cmdText.ToString(), tranResult.Parameters, transaction : tran, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false));
}
}
/// <summary>
/// get insert execute DbCommand
/// </summary>
/// <param name="translator">translator</param>
/// <param name="cmd">cmd</param>
/// <returns></returns>
DbExecuteCommand GetInsertExecuteDbCommand(IQueryTranslator translator, RdbCommand cmd)
{
string cmdText = string.Empty;
CmdParameters parameters = null;
CommandType commandType = GetCommandType(cmd);
if (cmd.ExecuteMode == CommandExecuteMode.CommandText)
{
cmdText = cmd.CommandText;
parameters = ParseParameters(cmd.Parameters);
}
else
{
string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName);
var fields = DataManager.GetEditFields(ServerType.SQLServer, cmd.EntityType);
var insertFormatResult = FormatInsertFields(fields, cmd.Parameters, translator.ParameterSequence);
if (insertFormatResult == null)
{
return(null);
}
cmdText = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2});"
, objectName
, string.Join(",", insertFormatResult.Item1)
, string.Join(",", insertFormatResult.Item2));
parameters = insertFormatResult.Item3;
translator.ParameterSequence += fields.Count;
}
return(new DbExecuteCommand()
{
CommandText = cmdText,
CommandType = commandType,
ForceReturnValue = cmd.MustReturnValueOnSuccess,
Parameters = parameters
});
}
/// <summary>
/// Aggregate Function
/// </summary>
/// <typeparam name="T">data type</typeparam>
/// <param name="server">database server</param>
/// <param name="cmd">command</param>
/// <returns></returns>
async Task <T> AggregateFunctionAsync <T>(ServerInfo server, ICommand cmd)
{
cmd.Query = cmd.Query ?? QueryFactory.Create();
#region query object translate
IQueryTranslator translator = QueryTranslator.GetTranslator(server);
var tranResult = translator.Translate(cmd.Query);
#endregion
#region execute
StringBuilder cmdText = new StringBuilder();
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
switch (cmd.Query.QueryType)
{
case QueryCommandType.Text:
cmdText.Append(tranResult.ConditionString);
break;
case QueryCommandType.QueryObject:
default:
string funcName = GetAggregateFunctionName(cmd.Operate);
if (funcName.IsNullOrEmpty() || cmd.Fields.IsNullOrEmpty())
{
return(default(T));
}
string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName);
if (cmd.Query.QueryFields.IsNullOrEmpty())
{
throw new EZNEWException("must set one field");
}
var field = DataManager.GetField(ServerType.SQLServer, cmd.EntityType, cmd.Query.QueryFields[0]);
cmdText.AppendFormat("{4}SELECT {0}({3}.[{1}]) FROM [{2}] AS {3}{5}"
, funcName
, field.FieldName
, objectName
, translator.ObjectPetName
, tranResult.PreScript
, joinScript);
if (!tranResult.ConditionString.IsNullOrEmpty())
{
cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
}
if (!tranResult.OrderString.IsNullOrEmpty())
{
cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
}
break;
}
#endregion
using (var conn = DbServerFactory.GetConnection(server))
{
return(await conn.ExecuteScalarAsync <T>(cmdText.ToString(), tranResult.Parameters, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false));
}
}
/// <summary>
/// Construtor padrão
/// </summary>
/// <param name="translator">Interface de mapeamento de entidade-tabela no banco de dados</param>
/// <param name="typeSchema">Classe de recuperação de metadados</param>
public PersistenceSqlParser(IQueryTranslator translator, ITypeSchema typeSchema)
{
translator.Require("translator");
translator.Require("typeSchema");
translator.Require("primaryKeyRepository");
_translator = translator;
_typeSchema = typeSchema;
}
public void SetUp()
{
_dslParser = new Mock <IDslParser>();
_queryBlockTranslationManager = new Mock <IQueryBlockTranslationManager>();
_target = new QueryTranslator(_dslParser.Object, _queryBlockTranslationManager.Object);
}
/// <summary>
/// Construtor padrão.
/// </summary>
/// <param name="cacheProvider">Instancia do provedor do cache.</param>
/// <param name="typeSchema">Esquema dos tipos do sistema.</param>
public CacheDataSource(ICacheProvider cacheProvider, ITypeSchema typeSchema)
{
cacheProvider.Require("cacheProvider").NotNull();
typeSchema.Require("typeSchema").NotNull();
_cacheProvider = cacheProvider;
_typeSchema = typeSchema;
_translator = new CacheQueryTranslator(_typeSchema);
}
/// <summary>
/// register query translator
/// </summary>
/// <param name="serverType">server type</param>
/// <param name="queryTranslator">query translator</param>
public static void RegisterQueryTranslator(ServerType serverType, IQueryTranslator queryTranslator)
{
if (queryTranslator == null)
{
return;
}
Translators[serverType] = queryTranslator;
}
public GetQueryModelQueryHandler([NotNull] IQueryTranslator queryTranslator)
{
if (queryTranslator == null)
{
throw new ArgumentNullException(nameof(queryTranslator));
}
_queryTranslator = queryTranslator;
}
/// <summary>
/// query data list
/// </summary>
/// <typeparam name="T">data type</typeparam>
/// <param name="server">database server</param>
/// <param name="cmd">command</param>
/// <returns>data list</returns>
public async Task <IEnumerable <T> > QueryAsync <T>(ServerInfo server, ICommand cmd)
{
if (cmd.Query == null)
{
throw new EZNEWException("ICommand.Query is null");
}
#region query object translate
IQueryTranslator translator = QueryTranslator.GetTranslator(server);
var tranResult = translator.Translate(cmd.Query);
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
#region execute
StringBuilder cmdText = new StringBuilder();
switch (cmd.Query.QueryType)
{
case QueryCommandType.Text:
cmdText.Append(tranResult.ConditionString);
break;
case QueryCommandType.QueryObject:
default:
int size = cmd.Query.QuerySize;
string objectName = DataManager.GetEntityObjectName(ServerType.MySQL, cmd.EntityType, cmd.ObjectName);
cmdText.AppendFormat("{3}SELECT {1} FROM `{2}` AS {0}{4}"
, translator.ObjectPetName
, string.Join(",", FormatQueryFields(translator.ObjectPetName, cmd.Query, cmd.EntityType, out var defaultFieldName))
, objectName
, tranResult.PreScript
, joinScript);
if (!tranResult.ConditionString.IsNullOrEmpty())
{
cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
}
if (!tranResult.OrderString.IsNullOrEmpty())
{
cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
}
if (size > 0)
{
cmdText.AppendFormat(" LIMIT 0,{0}", size);
}
break;
}
#endregion
using (var conn = DbServerFactory.GetConnection(server))
{
var tran = GetQueryTransaction(conn, cmd.Query);
return(await conn.QueryAsync <T>(cmdText.ToString(), tranResult.Parameters, transaction : tran, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false));
}
}
/// <summary>
/// Get database deletion command
/// </summary>
/// <param name="translator">Query translator</param>
/// <param name="command">Command</param>
/// <returns>Return a database deletion command</returns>
DatabaseExecutionCommand GetDatabaseDeletionCommand(IQueryTranslator translator, DefaultCommand command)
{
translator.DataAccessContext.SetCommand(command);
#region query translation
var tranResult = translator.Translate(command.Query);
string conditionString = string.Empty;
if (!string.IsNullOrWhiteSpace(tranResult.ConditionString))
{
conditionString += "WHERE " + tranResult.ConditionString;
}
string preScript = tranResult.PreScript;
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
#region script
string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command);
string cmdText = string.Empty;
string wrapObjectName = SQLiteManager.WrapKeyword(objectName);
if (string.IsNullOrWhiteSpace(joinScript))
{
cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {translator.ObjectPetName} {conditionString};";
}
else
{
var primaryKeyFields = DataManager.GetFields(CurrentDatabaseServerType, command.EntityType, EntityManager.GetPrimaryKeys(command.EntityType)).ToList();
if (primaryKeyFields.IsNullOrEmpty())
{
throw new EZNEWException($"{command.EntityType?.FullName} not set primary key");
}
string deleteTableShortName = "DTB";
cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {deleteTableShortName} WHERE {string.Join("||", primaryKeyFields.Select(pk => deleteTableShortName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} IN (SELECT {string.Join("||", primaryKeyFields.Select(pk => translator.ObjectPetName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} FROM {wrapObjectName} AS {translator.ObjectPetName} {joinScript} {conditionString});";
}
#endregion
#region parameter
CommandParameters parameters = SQLiteManager.ConvertParameter(command.Parameters) ?? new CommandParameters();
var queryParameters = SQLiteManager.ConvertParameter(tranResult.Parameters);
parameters.Union(queryParameters);
#endregion
return(new DatabaseExecutionCommand()
{
CommandText = cmdText,
CommandType = SQLiteManager.GetCommandType(command),
MustAffectedData = command.MustAffectedData,
Parameters = parameters,
HasPreScript = !string.IsNullOrWhiteSpace(preScript)
});
}
private int BindQueryParameters(IDbCommand command, int colIndex)
{
for (int i = 0; i < queries.Count; i++)
{
IQueryTranslator translator = Translators[i];
QueryParameters parameter = Parameters[i];
colIndex += parameter.BindParameters(command, translator.Loader.GetNamedParameterLocs, colIndex, session);
}
return(colIndex);
}
/// <summary>
/// get delete execute command
/// </summary>
/// <param name="translator">translator</param>
/// <param name="cmd">cmd</param>
/// <returns></returns>
DbExecuteCommand GetDeleteExecuteDbCommand(IQueryTranslator translator, RdbCommand cmd)
{
#region query translate
var tranResult = translator.Translate(cmd.Query);
string conditionString = string.Empty;
if (!tranResult.ConditionString.IsNullOrEmpty())
{
conditionString += "WHERE " + tranResult.ConditionString;
}
string preScript = tranResult.PreScript;
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
string cmdText = string.Empty;
CmdParameters parameters = ParseParameters(cmd.Parameters);
if (cmd.ExecuteMode == CommandExecuteMode.CommandText)
{
cmdText = cmd.CommandText;
}
else
{
string objectName = DataManager.GetEntityObjectName(ServerType.MySQL, cmd.EntityType, cmd.ObjectName);
cmdText = string.Format("{3}DELETE {0} FROM `{1}` AS {0}{4} {2};"
, translator.ObjectPetName
, objectName
, conditionString
, preScript
, joinScript);
}
//combine parameters
if (tranResult.Parameters != null)
{
var queryParameters = ParseParameters(tranResult.Parameters);
if (parameters != null)
{
parameters.Union(queryParameters);
}
else
{
parameters = queryParameters;
}
}
CommandType commandType = GetCommandType(cmd);
return(new DbExecuteCommand()
{
CommandText = cmdText,
CommandType = commandType,
ForceReturnValue = cmd.MustReturnValueOnSuccess,
Parameters = parameters,
HasPreScript = !string.IsNullOrWhiteSpace(preScript)
});
}
/// <summary>
/// Get database deletion command
/// </summary>
/// <param name="translator">Query translator</param>
/// <param name="command">Command</param>
/// <returns>Return a database deletion command</returns>
DatabaseExecutionCommand GetDatabaseDeletionCommand(IQueryTranslator translator, DefaultCommand command)
{
translator.DataAccessContext.SetCommand(command);
#region query translation
var tranResult = translator.Translate(command.Query);
string conditionString = string.Empty;
if (!string.IsNullOrWhiteSpace(tranResult.ConditionString))
{
conditionString += "WHERE " + tranResult.ConditionString;
}
string preScript = tranResult.PreScript;
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
#region script
string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command);
string cmdText;
string wrapObjectName = PostgreSqlManager.WrapKeyword(objectName);
if (string.IsNullOrWhiteSpace(joinScript))
{
cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {translator.ObjectPetName} {conditionString};";
}
else
{
string deleteTableShortName = "DTB";
string deleteJoinTableShortName = "DJTB";
var primaryKeyFormatedResult = FormatWrapJoinPrimaryKeys(command.EntityType, translator.ObjectPetName, deleteTableShortName, deleteJoinTableShortName);
cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {deleteTableShortName} USING (SELECT {string.Join(",", primaryKeyFormatedResult.Item1)} FROM {wrapObjectName} AS {translator.ObjectPetName} {joinScript} {conditionString}) AS {deleteJoinTableShortName} WHERE {string.Join(" AND ", primaryKeyFormatedResult.Item2)};";
}
#endregion
#region parameter
CommandParameters parameters = PostgreSqlManager.ConvertParameter(command.Parameters) ?? new CommandParameters();
var queryParameters = PostgreSqlManager.ConvertParameter(tranResult.Parameters);
parameters.Union(queryParameters);
#endregion
return(new DatabaseExecutionCommand()
{
CommandText = cmdText,
CommandType = PostgreSqlManager.GetCommandType(command),
MustAffectedData = command.MustAffectedData,
Parameters = parameters,
HasPreScript = !string.IsNullOrWhiteSpace(preScript)
});
}
public DatabaseContext(DatabaseContextOptions options)
{
options_ = options;
modelDataStorage_ = new ModelDataStorage <Type>();
dbQueryTranslator_ = new DatabaseCreationQueryTranslator(modelDataStorage_);
constraintTranslator_ = new ConstraintTranslator(options);
dbTableToPropertyName_ = new Dictionary <Type, string>();
stateManager_ = new StateManager(modelDataStorage_);
database_ = new SqlDatabase(new SqlEntityDeserializer(modelDataStorage_, stateManager_), options_.ConnectionString);
tableNameProvider_ = new TableNameProvider();
changeDetector_ = new ChangeDetector(tableNameProvider_, modelDataStorage_);
updateQueryTranslator_ = new UpdateQueryTranslator(new WhereQueryTranslator());
}
/// <summary>
/// query data list
/// </summary>
/// <typeparam name="T">data type</typeparam>
/// <param name="server">database server</param>
/// <param name="cmd">command</param>
/// <returns>data list</returns>
public async Task <IEnumerable <T> > QueryAsync <T>(ServerInfo server, ICommand cmd)
{
cmd.Query = cmd.Query ?? QueryFactory.Create();
#region query object translate
IQueryTranslator translator = QueryTranslator.GetTranslator(server);
var tranResult = translator.Translate(cmd.Query);
string preScript = tranResult.PreScript;
string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
#endregion
#region execute
StringBuilder cmdText = new StringBuilder();
switch (cmd.Query.QueryType)
{
case QueryCommandType.Text:
cmdText.Append(tranResult.ConditionString);
break;
case QueryCommandType.QueryObject:
default:
int size = cmd.Query == null ? 0 : cmd.Query.QuerySize;
string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName);
cmdText.AppendFormat("{4}SELECT {0} {1} FROM [{2}] AS {3}{5}"
, size > 0 ? "TOP " + size : string.Empty
, string.Join(",", FormatQueryFields(translator.ObjectPetName, cmd.Query, cmd.EntityType, out var defaultFieldName))
, objectName
, translator.ObjectPetName
, preScript
, joinScript);
if (!tranResult.ConditionString.IsNullOrEmpty())
{
cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
}
if (!tranResult.OrderString.IsNullOrEmpty())
{
cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
}
break;
}
#endregion
using (var conn = DbServerFactory.GetConnection(server))
{
return(await conn.QueryAsync <T>(cmdText.ToString(), tranResult.Parameters, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false));
}
}
public DeliciousQueryProviderTest()
{
delayer = mocks.StrictMock<IDelayer>();
translatorFactory = mocks.StrictMock<IQueryTranslatorFactory>();
translator = mocks.StrictMock<IQueryTranslator>();
requestFactory = mocks.StrictMock<IHttpWebRequestFactory>();
request = mocks.StrictMock<HttpWebRequest>();
response = mocks.StrictMock<HttpWebResponse>();
stream.Write(documentBytes, 0, documentBytes.Length);
stream.Seek(0, SeekOrigin.Begin);
provider = new DeliciousQueryProvider(requestFactory, delayer, translatorFactory);
}
/// <summary>
/// execute by nonee transaction
/// </summary>
/// <param name="serverInfo">database server</param>
/// <param name="cmds">command</param>
/// <returns></returns>
int ExecuteByNoneTransaction(ServerInfo serverInfo, IEnumerable <RdbCommand> cmds)
{
IQueryTranslator queryTranslator = QueryTranslator.GetTranslator(serverInfo);
using (var conn = DbServerFactory.GetConnection(serverInfo))
{
int executeRows = 0;
foreach (var cmd in cmds)
{
executeRows += ExecuteSingleCommand(queryTranslator, conn, cmd);
}
return(executeRows);
}
}
/// <summary>
/// query data list offset the specified numbers
/// </summary>
/// <typeparam name="T">data type</typeparam>
/// <param name="server">database server</param>
/// <param name="cmd">command</param>
/// <param name="offsetNum">offset num</param>
/// <param name="size">query size</param>
/// <returns></returns>
public IEnumerable <T> QueryOffset <T>(ServerInfo server, ICommand cmd, int offsetNum = 0, int size = int.MaxValue)
{
var verifyResults = VerifyCommand(cmd);
cmd.Query = cmd.Query ?? QueryFactory.Create();
#region query object translate
IQueryTranslator queryTranslator = QueryTranslator.GetTranslator(server);
var tranResult = queryTranslator.Translate(cmd.Query);
#endregion
#region execute
StringBuilder cmdText = new StringBuilder();
switch (cmd.Query.QueryType)
{
case QueryCommandType.Text:
cmdText.Append(tranResult.ConditionString);
break;
case QueryCommandType.QueryObject:
default:
string fieldSplitChar = string.Format(",{0}.", queryTranslator.ObjectPetName);
cmdText.AppendFormat("{4}SELECT COUNT({3}.[{0}]) OVER() AS PagingTotalCount,{3}.{1} FROM [{2}] AS {3}", cmd.Fields.ElementAt(0), string.Join(fieldSplitChar, FormatFields(cmd.Fields)), cmd.ObjectName, queryTranslator.ObjectPetName, tranResult.PreScript);
if (!tranResult.ConditionString.IsNullOrEmpty())
{
cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
}
if (!tranResult.OrderString.IsNullOrEmpty())
{
cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
}
else
{
cmdText.AppendFormat(" ORDER BY {0}.[{1}] DESC", queryTranslator.ObjectPetName, cmd.Fields.ElementAt(0));
}
cmdText.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", offsetNum, size);
break;
}
#endregion
using (var conn = DbServerFactory.GetConnection(server))
{
return(conn.Query <T>(cmdText.ToString(), tranResult.Parameters, commandType: GetCommandType(verifyResults.Item1.First())));
}
}
/// <summary>
/// query data list
/// </summary>
/// <typeparam name="T">data type</typeparam>
/// <param name="server">database server</param>
/// <param name="cmd">command</param>
/// <returns>data list</returns>
public IEnumerable <T> Query <T>(ServerInfo server, ICommand cmd)
{
var verifyResults = VerifyCommand(cmd);
cmd.Query = cmd.Query ?? QueryFactory.Create();
#region query object translate
IQueryTranslator queryTranslator = QueryTranslator.GetTranslator(server);
var tranResult = queryTranslator.Translate(cmd.Query);
#endregion
#region execute
StringBuilder cmdText = new StringBuilder();
switch (cmd.Query.QueryType)
{
case QueryCommandType.Text:
cmdText.Append(tranResult.ConditionString);
break;
case QueryCommandType.QueryObject:
default:
int size = cmd.Query == null ? 0 : cmd.Query.QuerySize;
string fieldSplitChar = string.Format(",{0}.", queryTranslator.ObjectPetName);
cmdText.AppendFormat("{3}SELECT {0}.{1} FROM `{2}` AS {0}", queryTranslator.ObjectPetName, string.Join(fieldSplitChar, FormatFields(cmd.Fields)), cmd.ObjectName, tranResult.PreScript);
if (!tranResult.ConditionString.IsNullOrEmpty())
{
cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
}
if (!tranResult.OrderString.IsNullOrEmpty())
{
cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
}
if (size > 0)
{
cmdText.AppendFormat(" LIMIT 0,{0}", size);
}
break;
}
#endregion
using (var conn = DbServerFactory.GetConnection(server))
{
return(conn.Query <T>(cmdText.ToString(), tranResult.Parameters, commandType: GetCommandType(verifyResults.Item1.First())));
}
}
/// <summary>
/// register query translator
/// </summary>
/// <param name="serverType">server type</param>
/// <param name="queryTranslator">query translator</param>
public static void RegisterQueryTranslator(ServerType serverType, IQueryTranslator queryTranslator)
{
if (queryTranslator == null)
{
return;
}
if (!Translators.ContainsKey(serverType))
{
Translators.Add(serverType, queryTranslator);
}
else
{
Translators[serverType] = queryTranslator;
}
}
public QueryModelProcessor([NotNull] IQueryTranslator queryTranslator,
[NotNull] IQueryModelAccessValidator queryModelAccessValidator)
{
if (queryTranslator == null)
{
throw new ArgumentNullException(nameof(queryTranslator));
}
if (queryModelAccessValidator == null)
{
throw new ArgumentNullException(nameof(queryModelAccessValidator));
}
_queryTranslator = queryTranslator;
_queryModelAccessValidator = queryModelAccessValidator;
}
/// <summary>
/// Aggregate Function
/// </summary>
/// <typeparam name="T">data type</typeparam>
/// <param name="server">database server</param>
/// <param name="cmd">command</param>
/// <returns></returns>
T AggregateFunction <T>(ServerInfo server, ICommand cmd)
{
var verifyResults = VerifyCommand(cmd);
cmd.Query = cmd.Query ?? QueryFactory.Create();
#region query object translate
IQueryTranslator queryTranslator = QueryTranslator.GetTranslator(server);
var tranResult = queryTranslator.Translate(cmd.Query);
#endregion
#region query
StringBuilder cmdText = new StringBuilder();
switch (cmd.Query.QueryType)
{
case QueryCommandType.Text:
cmdText.Append(tranResult.ConditionString);
break;
case QueryCommandType.QueryObject:
default:
string funcName = GetAggregateFunctionName(cmd.Operate);
if (funcName.IsNullOrEmpty() || cmd.Fields.IsNullOrEmpty())
{
return(default(T));
}
cmdText.AppendFormat("{4}SELECT {0}({3}.`{1}`) FROM `{2}` AS {3}", funcName, cmd.Fields.ElementAt(0), cmd.ObjectName, queryTranslator.ObjectPetName, tranResult.PreScript);
if (!tranResult.ConditionString.IsNullOrEmpty())
{
cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
}
if (!tranResult.OrderString.IsNullOrEmpty())
{
cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
}
break;
}
#endregion
using (var conn = DbServerFactory.GetConnection(server))
{
return(conn.ExecuteScalar <T>(cmdText.ToString(), tranResult.Parameters, commandType: GetCommandType(verifyResults.Item1.First())));
}
}
private static IDictionary<string, string> GetColumnNamesAndColumnAliases(IQueryTranslator translator)
{
var loader = translator.Loader;
var entityPersisters = loader.EntityPersisters;
var entityAliases = (IEntityAliases[])loader.GetType().GetProperty("EntityAliases", InstanceNonPublicBinding | BindingFlags.GetProperty).GetValue(loader, new object[0]);
var columnNames = entityPersisters[0].PropertyNames.Select(x => x).ToList();
var mappedColumns = GetMappedColumns(entityPersisters[0], columnNames);
mappedColumns.AddRange(entityPersisters[0].IdentifierColumnNames);
var columnAliases = entityAliases[0].SuffixedPropertyAliases.Select(x =>
{
return x.Length > 0 ? x[0] : String.Empty;
}).ToList();
columnAliases.AddRange(entityAliases[0].SuffixedKeyAliases);
if(mappedColumns.Count != columnAliases.Count)
{
throw new ApplicationException("Cannot unalias sql when column alias count are not equal to mapped column count");
}
var result = new Dictionary<string, string>();
for(var i = 0; i < columnAliases.Count; i++)
{
var badAlias = columnAliases[i];
var newGoodAlias = mappedColumns[i];
if(String.IsNullOrWhiteSpace(badAlias) || String.IsNullOrWhiteSpace(newGoodAlias))
{
continue;
}
if (result.ContainsKey(badAlias))
{
throw new ApplicationException(String.Format("Cannot unalias sql because of duplicate alias: {0}", badAlias));
}
result[badAlias] = newGoodAlias;
}
return result;
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="mappingManager">Mapping Manager</param>
/// <param name="typeRegister">Types Register</param>
/// <param name="queryTranslator">Query translator</param>
public ExecutionPlanManager(IMappingManager mappingManager, ITypeRegister typeRegister, IQueryTranslator queryTranslator)
{
this._mappingManager = mappingManager;
this._typeRegister = typeRegister;
this._queryTranslator = queryTranslator;
}
protected QueryExpressionPlan(string key, IQueryTranslator[] translators)
: base(key, translators)
{
}
private IQueryTranslator[] CreateQueryTranslators(string hql, string[] concreteQueryStrings, QueryCacheKey cacheKey,
IDictionary<string, IFilter> enabledFilters)
{
int length = concreteQueryStrings.Length;
IQueryTranslator[] queries = new IQueryTranslator[length];
for (int i = 0; i < length; i++)
{
queries[i] = settings.QueryTranslatorFactory.CreateQueryTranslator(hql, concreteQueryStrings[i], enabledFilters, this);
}
Put(cacheKey, queries);
return queries;
}
