public FieldConversionResult Convert(FieldConversionContext fieldConversionContext)
        {
            if (string.IsNullOrWhiteSpace(fieldConversionContext?.ConversionName))
            {
                return(null);
            }
            string formatedFieldName = null;

            switch (fieldConversionContext.ConversionName)
            {
            case FieldConversionNames.StringLength:
                formatedFieldName = string.IsNullOrWhiteSpace(fieldConversionContext.ObjectName)
                        ? $"LENGTH({fieldConversionContext.ObjectName}.{SQLiteManager.WrapKeyword(fieldConversionContext.FieldName)})"
                        : $"LENGTH({SQLiteManager.WrapKeyword(fieldConversionContext.FieldName)})";
                break;

            default:
                throw new EZNEWException($"{SQLiteManager.CurrentDatabaseServerType} does not support field conversion: {fieldConversionContext.ConversionName}");
            }

            return(new FieldConversionResult()
            {
                NewFieldName = formatedFieldName
            });
        }
Ejemplo n.º 2
0
        /// <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 = SQLiteManager.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 orderString         = string.IsNullOrWhiteSpace(tranResult.SortString) ? string.Empty : $"ORDER BY {tranResult.SortString}";
                var    queryFields         = SQLiteManager.GetQueryFields(command.Query, command.EntityType, true);
                string outputFormatedField = string.Join(",", SQLiteManager.FormatQueryFields(translator.ObjectPetName, queryFields, true));
                if (string.IsNullOrWhiteSpace(tranResult.CombineScript))
                {
                    cmdText = $"{tranResult.PreScript}SELECT {outputFormatedField} FROM {SQLiteManager.WrapKeyword(objectName)} AS {translator.ObjectPetName} {joinScript} {(string.IsNullOrWhiteSpace(tranResult.ConditionString) ? string.Empty : $"WHERE {tranResult.ConditionString}")} {orderString} {(size > 0 ? $"LIMIT 0,{size}" : string.Empty)}";
                }
Ejemplo n.º 3
0
        /// <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)
            });
        }
Ejemplo n.º 4
0
        /// <summary>
        /// Execute database command
        /// </summary>
        /// <param name="server">Database server</param>
        /// <param name="executionOptions">Execution options</param>
        /// <param name="databaseExecutionCommands">Database execution commands</param>
        /// <param name="useTransaction">Whether use transaction</param>
        /// <returns>Return affected data number</returns>
        async Task <int> ExecuteDatabaseCommandAsync(DatabaseServer server, CommandExecutionOptions executionOptions, IEnumerable <DatabaseExecutionCommand> databaseExecutionCommands, bool useTransaction)
        {
            int  resultValue = 0;
            bool success     = true;

            using (var conn = SQLiteManager.GetConnection(server))
            {
                IDbTransaction transaction = null;
                if (useTransaction)
                {
                    transaction = SQLiteManager.GetExecutionTransaction(conn, executionOptions);
                }
                try
                {
                    foreach (var cmd in databaseExecutionCommands)
                    {
                        var cmdDefinition      = new CommandDefinition(cmd.CommandText, SQLiteManager.ConvertCmdParameters(cmd.Parameters), transaction: transaction, commandType: cmd.CommandType, cancellationToken: executionOptions?.CancellationToken ?? default);
                        var executeResultValue = await conn.ExecuteAsync(cmdDefinition).ConfigureAwait(false);

                        success      = success && (cmd.MustAffectedData ? executeResultValue > 0 : true);
                        resultValue += executeResultValue;
                        if (useTransaction && !success)
                        {
                            break;
                        }
                    }
                    if (!useTransaction)
                    {
                        return(resultValue);
                    }
                    if (success)
                    {
                        transaction.Commit();
                    }
                    else
                    {
                        resultValue = 0;
                        transaction.Rollback();
                    }
                    return(resultValue);
                }
                catch (Exception ex)
                {
                    resultValue = 0;
                    transaction?.Rollback();
                    throw ex;
                }
            }
        }
Ejemplo n.º 5
0
        /// <summary>
        /// Get database execution command
        /// </summary>
        /// <param name="queryTranslator">Query translator</param>
        /// <param name="command">Command</param>
        /// <returns>Return a database execution command</returns>
        DatabaseExecutionCommand GetDatabaseExecutionCommand(IQueryTranslator queryTranslator, DefaultCommand command)
        {
            DatabaseExecutionCommand GetTextCommand()
            {
                return(new DatabaseExecutionCommand()
                {
                    CommandText = command.Text,
                    Parameters = SQLiteManager.ConvertParameter(command.Parameters),
                    CommandType = SQLiteManager.GetCommandType(command),
                    MustAffectedData = command.MustAffectedData,
                    HasPreScript = true
                });
            }

            if (command.ExecutionMode == CommandExecutionMode.CommandText)
            {
                return(GetTextCommand());
            }
            DatabaseExecutionCommand databaseExecutionCommand;

            switch (command.OperationType)
            {
            case CommandOperationType.Insert:
                databaseExecutionCommand = GetDatabaseInsertionCommand(queryTranslator, command);
                break;

            case CommandOperationType.Update:
                databaseExecutionCommand = GetDatabaseUpdateCommand(queryTranslator, command);
                break;

            case CommandOperationType.Delete:
                databaseExecutionCommand = GetDatabaseDeletionCommand(queryTranslator, command);
                break;

            default:
                databaseExecutionCommand = GetTextCommand();
                break;
            }
            return(databaseExecutionCommand);
        }
Ejemplo n.º 6
0
        /// <summary>
        /// Get database insertion execution command
        /// </summary>
        /// <param name="translator">Query translator</param>
        /// <param name="command">Command</param>
        /// <returns>Return a database insertion command</returns>
        DatabaseExecutionCommand GetDatabaseInsertionCommand(IQueryTranslator translator, DefaultCommand command)
        {
            translator.DataAccessContext.SetCommand(command);
            string objectName         = translator.DataAccessContext.GetCommandEntityObjectName(command);
            var    fields             = DataManager.GetEditFields(CurrentDatabaseServerType, command.EntityType);
            var    fieldCount         = fields.GetCount();
            var    insertFormatResult = SQLiteManager.FormatInsertionFields(command.EntityType, fieldCount, fields, command.Parameters, translator.ParameterSequence);

            if (insertFormatResult == null)
            {
                return(null);
            }
            string            cmdText    = $"INSERT INTO {SQLiteManager.WrapKeyword(objectName)} ({string.Join(",", insertFormatResult.Item1)}) VALUES ({string.Join(",", insertFormatResult.Item2)});";
            CommandParameters parameters = insertFormatResult.Item3;

            translator.ParameterSequence += fieldCount;
            return(new DatabaseExecutionCommand()
            {
                CommandText = cmdText,
                CommandType = SQLiteManager.GetCommandType(command),
                MustAffectedData = command.MustAffectedData,
                Parameters = parameters
            });
        }
        /// <summary>
        /// Execute translation
        /// </summary>
        /// <param name="query">Query object</param>
        /// <param name="location">Query location</param>
        /// <param name="parameters">Parameters</param>
        /// <param name="objectName">Entity object name</param>
        /// <param name="useSort">Indicates whether use sort</param>
        /// <returns>Return a translation result</returns>
        public QueryTranslationResult ExecuteTranslation(IQuery query, QueryLocation location, CommandParameters parameters = null, string objectName = "", bool useSort = true)
        {
            if (query == null)
            {
                return(QueryTranslationResult.Empty);
            }
            StringBuilder conditionBuilder = new StringBuilder();

            if (query.ExecutionMode == QueryExecutionMode.QueryObject)
            {
                StringBuilder orderBuilder = new StringBuilder();
                parameters = parameters ?? new CommandParameters();
                objectName = string.IsNullOrWhiteSpace(objectName) ? DefaultObjectPetName : objectName;
                List <string> withScripts         = new List <string>();
                string        recurveTableName    = string.Empty;
                string        recurveTablePetName = string.Empty;

                #region condition

                if (!query.Conditions.IsNullOrEmpty())
                {
                    int index = 0;
                    foreach (var condition in query.Conditions)
                    {
                        var conditionResult = TranslateCondition(query, condition, parameters, objectName);
                        if (!conditionResult.WithScripts.IsNullOrEmpty())
                        {
                            withScripts.AddRange(conditionResult.WithScripts);
                            recurveTableName    = conditionResult.RecurveObjectName;
                            recurveTablePetName = conditionResult.RecurvePetName;
                        }
                        conditionBuilder.Append($" {(index > 0 ? condition.Connector.ToString().ToUpper() : string.Empty)} {conditionResult.ConditionString}");
                        index++;
                    }
                }

                #endregion

                #region sort

                if (useSort && !query.Sorts.IsNullOrEmpty())
                {
                    foreach (var sortEntry in query.Sorts)
                    {
                        orderBuilder.Append($"{ConvertSortFieldName(query, objectName, sortEntry)} {(sortEntry.Desc ? DescKeyWord : AscKeyWord)},");
                    }
                }

                #endregion

                #region combine

                StringBuilder combineBuilder = new StringBuilder();
                if (!query.Combines.IsNullOrEmpty())
                {
                    foreach (var combineEntry in query.Combines)
                    {
                        if (combineEntry?.Query == null)
                        {
                            continue;
                        }
                        var    combineObjectPetName   = GetNewSubObjectPetName();
                        string combineObjectName      = DataAccessContext.GetCombineEntityObjectName(combineEntry.Query);
                        var    combineQueryResult     = ExecuteTranslation(combineEntry.Query, QueryLocation.Combine, parameters, combineObjectPetName, true);
                        string combineConditionString = string.IsNullOrWhiteSpace(combineQueryResult.ConditionString) ? string.Empty : $"WHERE {combineQueryResult.ConditionString}";
                        combineBuilder.Append($" {GetCombineOperator(combineEntry.Type)} SELECT {string.Join(",", SQLiteManager.FormatQueryFields(combineObjectPetName, query, query.GetEntityType(), true, false))} FROM {SQLiteManager.WrapKeyword(combineObjectName)} AS {combineObjectPetName} {(combineQueryResult.AllowJoin ? combineQueryResult.JoinScript : string.Empty)} {combineConditionString}");
                        if (!combineQueryResult.WithScripts.IsNullOrEmpty())
                        {
                            withScripts.AddRange(combineQueryResult.WithScripts);
                            recurveTableName    = combineQueryResult.RecurveObjectName;
                            recurveTablePetName = combineQueryResult.RecurvePetName;
                        }
                    }
                }

                #endregion

                #region join

                bool          allowJoin          = true;
                StringBuilder joinBuilder        = new StringBuilder();
                StringBuilder joinExtraCondition = new StringBuilder();
                if (!query.Joins.IsNullOrEmpty())
                {
                    foreach (var joinEntry in query.Joins)
                    {
                        if (joinEntry == null || joinEntry.JoinObjectFilter == null)
                        {
                            continue;
                        }
                        if (joinEntry.JoinObjectFilter.GetEntityType() == null)
                        {
                            throw new EZNEWException("IQuery object must set entity type if use in join operation");
                        }
                        string joinObjectPetName = GetNewSubObjectPetName();
                        var    joinQueryResult   = ExecuteTranslation(joinEntry.JoinObjectFilter, QueryLocation.Join, parameters, joinObjectPetName, true);
                        if (string.IsNullOrWhiteSpace(joinQueryResult.CombineScript))
                        {
                            var joinResult = GetJoinCondition(query, joinEntry, parameters, objectName, joinObjectPetName);
                            if (!joinResult.WithScripts.IsNullOrEmpty())
                            {
                                withScripts.AddRange(joinResult.WithScripts);
                                recurveTableName    = joinResult.RecurveObjectName;
                                recurveTablePetName = joinResult.RecurvePetName;
                            }
                            var joinConnection = joinResult.ConditionString;
                            if (!string.IsNullOrWhiteSpace(joinQueryResult.ConditionString))
                            {
                                if (joinQueryResult.AllowJoin && PositionJoinConditionToConnection(joinEntry.Type))
                                {
                                    joinConnection += $"{(string.IsNullOrWhiteSpace(joinConnection) ? " ON" : " AND ")}{joinQueryResult.ConditionString}";
                                }
                                else
                                {
                                    conditionBuilder.Append($"{(conditionBuilder.Length == 0 ? string.Empty : " AND ")}{joinQueryResult.ConditionString}");
                                }
                            }
                            if (!string.IsNullOrWhiteSpace(joinQueryResult.JoinExtraConditionString))
                            {
                                conditionBuilder.Append($"{(conditionBuilder.Length == 0 ? string.Empty : " AND ")}{joinQueryResult.JoinExtraConditionString}");
                            }
                            joinBuilder.Append($" {GetJoinOperator(joinEntry.Type)} {SQLiteManager.WrapKeyword(DataAccessContext.GetJoinEntityObjectName(joinEntry.JoinObjectFilter))} AS {joinObjectPetName}{joinConnection}");
                            if (joinEntry.JoinObjectExtraFilter != null)
                            {
                                var extraQueryResult = ExecuteTranslation(joinEntry.JoinObjectExtraFilter, QueryLocation.Join, parameters, joinObjectPetName, true);
                                if (!string.IsNullOrWhiteSpace(extraQueryResult.ConditionString))
                                {
                                    joinExtraCondition.Append(joinExtraCondition.Length > 0 ? $" AND {extraQueryResult.ConditionString}" : extraQueryResult.ConditionString);
                                }
                            }
                            if (joinQueryResult.AllowJoin && !string.IsNullOrWhiteSpace(joinQueryResult.JoinScript))
                            {
                                joinBuilder.Append($" {joinQueryResult.JoinScript}");
                            }
                        }
                        else
                        {
                            var combineJoinObjName = GetNewSubObjectPetName();
                            var joinResult         = GetJoinCondition(query, joinEntry, parameters, objectName, combineJoinObjName);
                            if (!joinResult.WithScripts.IsNullOrEmpty())
                            {
                                withScripts.AddRange(joinResult.WithScripts);
                                recurveTableName    = joinResult.RecurveObjectName;
                                recurveTablePetName = joinResult.RecurvePetName;
                            }
                            var joinConnection = joinResult.ConditionString;
                            joinBuilder.Append($" {GetJoinOperator(joinEntry.Type)} (SELECT {string.Join(",", SQLiteManager.FormatQueryFields(joinObjectPetName, joinEntry.JoinObjectFilter, joinEntry.JoinObjectFilter.GetEntityType(), false, false))} FROM {SQLiteManager.WrapKeyword(DataAccessContext.GetJoinEntityObjectName(joinEntry.JoinObjectFilter))} AS {joinObjectPetName} {(joinQueryResult.AllowJoin ? joinQueryResult.JoinScript : string.Empty)} {(string.IsNullOrWhiteSpace(joinQueryResult.ConditionString) ? string.Empty : "WHERE " + joinQueryResult.ConditionString)} {joinQueryResult.CombineScript}) AS {combineJoinObjName}{joinConnection}");
                        }
                        if (!joinQueryResult.WithScripts.IsNullOrEmpty())
                        {
                            withScripts.AddRange(joinQueryResult.WithScripts);
                            recurveTableName    = joinQueryResult.RecurveObjectName;
                            recurveTablePetName = joinQueryResult.RecurvePetName;
                        }
                    }
                }
                string joinScript = joinBuilder.ToString();

                #endregion

                #region recurve script

                string conditionString          = conditionBuilder.ToString();
                string joinExtraConditionString = joinExtraCondition.ToString();
                if (query.Recurve != null)
                {
                    allowJoin = false;
                    string nowConditionString = conditionString;
                    if (!string.IsNullOrWhiteSpace(joinExtraConditionString))
                    {
                        nowConditionString       = string.IsNullOrWhiteSpace(nowConditionString) ? joinExtraConditionString : $"{nowConditionString} AND {joinExtraConditionString}";
                        joinExtraConditionString = string.Empty;
                    }
                    EntityField recurveField         = DataManager.GetField(DatabaseServerType, query, query.Recurve.DataField);
                    EntityField recurveRelationField = DataManager.GetField(DatabaseServerType, query, query.Recurve.RelationField);
                    var         recurveTable         = GetNewRecurveTableName();
                    recurveTablePetName = recurveTable.Item1;
                    recurveTableName    = recurveTable.Item2;
                    conditionString     = $"{objectName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)} IN (SELECT {recurveTablePetName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)} FROM {SQLiteManager.WrapKeyword(recurveTableName)} AS {recurveTablePetName})";
                    DataAccessContext.SetActivityQuery(query, location);
                    string queryObjectName = DataManager.GetEntityObjectName(DataAccessContext);
                    string withScript      =
                        $"{recurveTableName} AS (SELECT {objectName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)},{objectName}.{SQLiteManager.WrapKeyword(recurveRelationField.FieldName)} FROM {SQLiteManager.WrapKeyword(queryObjectName)} AS {objectName} {joinScript} {(string.IsNullOrWhiteSpace(nowConditionString) ? string.Empty : $"WHERE {nowConditionString}")} " +
                        $"UNION ALL SELECT {objectName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)},{objectName}.{SQLiteManager.WrapKeyword(recurveRelationField.FieldName)} FROM {SQLiteManager.WrapKeyword(queryObjectName)} AS {objectName} JOIN {recurveTableName} AS {recurveTablePetName} " +
                        $"ON {(query.Recurve.Direction == RecurveDirection.Up ? $"{objectName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)}={recurveTablePetName}.{SQLiteManager.WrapKeyword(recurveRelationField.FieldName)}" : $"{objectName}.{SQLiteManager.WrapKeyword(recurveRelationField.FieldName)}={recurveTablePetName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)}")})";
Ejemplo n.º 8
0
        /// <summary>
        /// Execute command
        /// </summary>
        /// <param name="server">Database server</param>
        /// <param name="executionOptions">Execution options</param>
        /// <param name="commands">Commands</param>
        /// <returns>Return affected data number</returns>
        public async Task <int> ExecuteAsync(DatabaseServer server, CommandExecutionOptions executionOptions, IEnumerable <ICommand> commands)
        {
            #region group execution commands

            IQueryTranslator translator = SQLiteManager.GetQueryTranslator(DataAccessContext.Create(server));
            List <DatabaseExecutionCommand> databaseExecutionCommands = new List <DatabaseExecutionCommand>();
            var batchExecutionConfig = DataManager.GetBatchExecutionConfiguration(server.ServerType) ?? BatchExecutionConfiguration.Default;
            var groupStatementsCount = batchExecutionConfig.GroupStatementsCount;
            groupStatementsCount = groupStatementsCount < 0 ? 1 : groupStatementsCount;
            var groupParameterCount = batchExecutionConfig.GroupParametersCount;
            groupParameterCount = groupParameterCount < 0 ? 1 : groupParameterCount;
            StringBuilder     commandTextBuilder = new StringBuilder();
            CommandParameters parameters         = null;
            int  statementsCount  = 0;
            bool forceReturnValue = false;
            int  cmdCount         = 0;

            DatabaseExecutionCommand GetGroupExecuteCommand()
            {
                var executionCommand = new DatabaseExecutionCommand()
                {
                    CommandText      = commandTextBuilder.ToString(),
                    CommandType      = CommandType.Text,
                    MustAffectedData = forceReturnValue,
                    Parameters       = parameters
                };

                statementsCount = 0;
                translator.ParameterSequence = 0;
                commandTextBuilder.Clear();
                parameters       = null;
                forceReturnValue = false;
                return(executionCommand);
            }

            foreach (var cmd in commands)
            {
                DatabaseExecutionCommand databaseExecutionCommand = GetDatabaseExecutionCommand(translator, cmd as DefaultCommand);
                if (databaseExecutionCommand == null)
                {
                    continue;
                }

                //Trace log
                SQLiteManager.LogExecutionCommand(databaseExecutionCommand);

                cmdCount++;
                if (databaseExecutionCommand.PerformAlone)
                {
                    if (statementsCount > 0)
                    {
                        databaseExecutionCommands.Add(GetGroupExecuteCommand());
                    }
                    databaseExecutionCommands.Add(databaseExecutionCommand);
                    continue;
                }
                commandTextBuilder.AppendLine(databaseExecutionCommand.CommandText);
                parameters        = parameters == null ? databaseExecutionCommand.Parameters : parameters.Union(databaseExecutionCommand.Parameters);
                forceReturnValue |= databaseExecutionCommand.MustAffectedData;
                statementsCount++;
                if (translator.ParameterSequence >= groupParameterCount || statementsCount >= groupStatementsCount)
                {
                    databaseExecutionCommands.Add(GetGroupExecuteCommand());
                }
            }
            if (statementsCount > 0)
            {
                databaseExecutionCommands.Add(GetGroupExecuteCommand());
            }

            #endregion

            return(await ExecuteDatabaseCommandAsync(server, executionOptions, databaseExecutionCommands, executionOptions?.ExecutionByTransaction ?? cmdCount > 1).ConfigureAwait(false));
        }
Ejemplo n.º 9
0
        /// <summary>
        /// Get database update command
        /// </summary>
        /// <param name="translator">Query translator</param>
        /// <param name="command">Command</param>
        /// <returns>Return a database update command</returns>
        DatabaseExecutionCommand GetDatabaseUpdateCommand(IQueryTranslator translator, DefaultCommand command)
        {
            if (command?.Fields.IsNullOrEmpty() ?? true)
            {
                throw new EZNEWException($"No fields are set to update");
            }

            #region query translation

            translator.DataAccessContext.SetCommand(command);
            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

            CommandParameters parameters    = SQLiteManager.ConvertParameter(command.Parameters) ?? new CommandParameters();
            string            objectName    = translator.DataAccessContext.GetCommandEntityObjectName(command);
            var           fields            = SQLiteManager.GetFields(command.EntityType, command.Fields);
            int           parameterSequence = translator.ParameterSequence;
            List <string> updateSetArray    = new List <string>();
            foreach (var field in fields)
            {
                var    parameterValue     = parameters.GetParameterValue(field.PropertyName);
                var    parameterName      = field.PropertyName;
                string newValueExpression = string.Empty;
                if (parameterValue != null)
                {
                    parameterSequence++;
                    parameterName = SQLiteManager.FormatParameterName(parameterName, parameterSequence);
                    parameters.Rename(field.PropertyName, parameterName);
                    if (parameterValue is IModificationValue)
                    {
                        var modifyValue = parameterValue as IModificationValue;
                        parameters.ModifyValue(parameterName, modifyValue.Value);
                        if (parameterValue is CalculationModificationValue)
                        {
                            var    calculateModifyValue = parameterValue as CalculationModificationValue;
                            string calChar = SQLiteManager.GetSystemCalculationOperator(calculateModifyValue.Operator);
                            newValueExpression = $"{translator.ObjectPetName}.{SQLiteManager.WrapKeyword(field.FieldName)}{calChar}{SQLiteManager.ParameterPrefix}{parameterName}";
                        }
                    }
                }
                if (string.IsNullOrWhiteSpace(newValueExpression))
                {
                    newValueExpression = $"{SQLiteManager.ParameterPrefix}{parameterName}";
                }
                updateSetArray.Add($"{SQLiteManager.WrapKeyword(field.FieldName)}={newValueExpression}");
            }
            string cmdText        = string.Empty;
            string wrapObjectName = SQLiteManager.WrapKeyword(objectName);
            if (string.IsNullOrWhiteSpace(joinScript))
            {
                cmdText = $"{preScript}UPDATE {wrapObjectName} AS {translator.ObjectPetName} SET {string.Join(",", updateSetArray)} {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 updateTableShortName = "UTB";

                cmdText = $"{preScript}UPDATE {wrapObjectName} AS {updateTableShortName} SET {string.Join(",", updateSetArray)} WHERE {string.Join("||", primaryKeyFields.Select(pk => updateTableShortName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} IN (SELECT {string.Join("||", primaryKeyFields.Select(pk => translator.ObjectPetName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} FROM {wrapObjectName} AS {translator.ObjectPetName} {joinScript} {conditionString});";
            }
            translator.ParameterSequence = parameterSequence;

            #endregion

            #region parameter

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