private string UpdateTriggerBodyText()
        {
            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendLine();
            stringBuilder.AppendLine($"Begin ");
            stringBuilder.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()} ");
            stringBuilder.AppendLine("\tSET `update_scope_id` = NULL -- since the update if from local, it's a NULL");
            stringBuilder.AppendLine($"\t\t,`update_timestamp` = {MySqlObjectNames.TimestampValue}");
            stringBuilder.AppendLine($"\t\t,`timestamp` = {MySqlObjectNames.TimestampValue}");
            stringBuilder.AppendLine("\t\t,`last_change_datetime` = utc_timestamp()");

            if (this.Filters != null && Filters.Count > 0)
            {
                foreach (var filterColumn in this.Filters)
                {
                    if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
                    {
                        continue;
                    }

                    var columnName = ParserName.Parse(filterColumn.ColumnName, "`").Quoted().ToString();

                    stringBuilder.AppendLine($"\t\t,{columnName} = new.{columnName}");
                }
                stringBuilder.AppendLine();
            }

            stringBuilder.Append($"\tWhere ");
            stringBuilder.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.Quoted().ToString(), "new"));
            stringBuilder.AppendLine($"; ");
            stringBuilder.AppendLine($"End; ");
            return(stringBuilder.ToString());
        }
        public bool NeedToCreateTrigger(DbTriggerType type)
        {
            var updTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger).name, tableName.Unquoted().Normalized().ToString());
            var delTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name, tableName.Unquoted().Normalized().ToString());
            var insTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name, tableName.Unquoted().Normalized().ToString());

            string triggerName = string.Empty;

            switch (type)
            {
            case DbTriggerType.Insert:
            {
                triggerName = insTriggerName;
                break;
            }

            case DbTriggerType.Update:
            {
                triggerName = updTriggerName;
                break;
            }

            case DbTriggerType.Delete:
            {
                triggerName = delTriggerName;
                break;
            }
            }

            return(!MySqlManagementUtils.TriggerExists(connection, transaction, triggerName));
        }
        private string DeleteTriggerBodyText()
        {
            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendLine();
            stringBuilder.AppendLine("BEGIN");
            stringBuilder.AppendLine($"UPDATE {trackingName.Quoted().ToString()} ");
            stringBuilder.AppendLine("SET `sync_row_is_tombstone` = 1");
            stringBuilder.AppendLine("\t,`update_scope_id` = NULL -- since the update if from local, it's a NULL");
            stringBuilder.AppendLine($"\t,`update_timestamp` = {MySqlObjectNames.TimestampValue}");
            stringBuilder.AppendLine($"\t,`timestamp` = {MySqlObjectNames.TimestampValue}");
            stringBuilder.AppendLine("\t,`last_change_datetime` = utc_timestamp()");

            // Filter columns
            if (this.Filters != null)
            {
                foreach (var filterColumn in this.Filters)
                {
                    if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
                    {
                        continue;
                    }
                    var columnName = ParserName.Parse(filterColumn.ColumnName, "`").Quoted().ToString();

                    stringBuilder.AppendLine($"\t,{columnName} = old.{columnName}");
                }
                stringBuilder.AppendLine();
            }

            stringBuilder.Append($"WHERE ");
            stringBuilder.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.Quoted().ToString(), "old"));
            stringBuilder.AppendLine(";");
            stringBuilder.AppendLine("END;");
            return(stringBuilder.ToString());
        }
示例#4
0
        //------------------------------------------------------------------
        // Delete command
        //------------------------------------------------------------------
        private MySqlCommand BuildDeleteCommand()
        {
            MySqlCommand sqlCommand = new MySqlCommand();

            this.AddPkColumnParametersToCommand(sqlCommand);
            MySqlParameter sqlParameter = new MySqlParameter();

            sqlParameter.ParameterName = "sync_force_write";
            sqlParameter.MySqlDbType   = MySqlDbType.Int32;
            sqlCommand.Parameters.Add(sqlParameter);

            MySqlParameter sqlParameter1 = new MySqlParameter();

            sqlParameter1.ParameterName = "sync_min_timestamp";
            sqlParameter1.MySqlDbType   = MySqlDbType.Int64;
            sqlCommand.Parameters.Add(sqlParameter1);

            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendLine();
            stringBuilder.AppendLine("DECLARE ts BIGINT;");
            stringBuilder.AppendLine("SET ts = 0;");
            stringBuilder.AppendLine($"SELECT `timestamp` FROM {trackingName.QuotedObjectName} WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, trackingName.QuotedObjectName)} LIMIT 1 INTO ts;");
            stringBuilder.AppendLine($"DELETE FROM {tableName.FullQuotedString} WHERE");
            stringBuilder.AppendLine(MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, ""));
            stringBuilder.AppendLine("AND (ts <= sync_min_timestamp  OR sync_force_write = 1);");
            sqlCommand.CommandText = stringBuilder.ToString();
            return(sqlCommand);
        }
示例#5
0
        //------------------------------------------------------------------
        // Update command
        //------------------------------------------------------------------
        private MySqlCommand BuildUpdateCommand()
        {
            MySqlCommand sqlCommand = new MySqlCommand();

            StringBuilder stringBuilder = new StringBuilder();

            this.AddColumnParametersToCommand(sqlCommand);

            MySqlParameter sqlParameter = new MySqlParameter("sync_force_write", MySqlDbType.Int32);

            sqlCommand.Parameters.Add(sqlParameter);
            MySqlParameter sqlParameter1 = new MySqlParameter("sync_min_timestamp", MySqlDbType.Int64);

            sqlCommand.Parameters.Add(sqlParameter1);

            stringBuilder.AppendLine("DECLARE ts BIGINT;");
            stringBuilder.AppendLine("SET ts = 0;");
            stringBuilder.AppendLine($"SELECT `timestamp` FROM {trackingName.QuotedObjectName} WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, trackingName.QuotedObjectName)} LIMIT 1 INTO ts;");

            stringBuilder.AppendLine($"UPDATE {tableName.QuotedString}");
            stringBuilder.Append($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.tableDescription)}");
            stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")}");
            stringBuilder.AppendLine($" AND (ts <= sync_min_timestamp OR sync_force_write = 1);");

            sqlCommand.CommandText = stringBuilder.ToString();
            return(sqlCommand);
        }
示例#6
0
        public bool NeedToCreateForeignKeyConstraints(SyncRelation relation)
        {
            string tableName = relation.GetTable().TableName;

            var relationName = NormalizeRelationName(relation.RelationName);

            bool alreadyOpened = this.connection.State == ConnectionState.Open;

            try
            {
                if (!alreadyOpened)
                {
                    this.connection.Open();
                }

                var relations = MySqlManagementUtils.RelationsForTable(this.connection, this.transaction, tableName);

                var foreignKeyExist = relations.Rows.Any(r =>
                                                         string.Equals(r["ForeignKey"].ToString(), relationName, SyncGlobalization.DataSourceStringComparison));

                return(!foreignKeyExist);
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"Error during checking foreign keys: {ex}");
                throw;
            }
            finally
            {
                if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
                {
                    this.connection.Close();
                }
            }
        }
示例#7
0
        //------------------------------------------------------------------
        // Delete Metadata command
        //------------------------------------------------------------------
        private MySqlCommand BuildDeleteMetadataCommand()
        {
            MySqlCommand sqlCommand = new MySqlCommand();

            this.AddPkColumnParametersToCommand(sqlCommand);
            MySqlParameter sqlParameter = new MySqlParameter();

            sqlParameter.ParameterName = "sync_check_concurrency";
            sqlParameter.MySqlDbType   = MySqlDbType.Int32;
            sqlCommand.Parameters.Add(sqlParameter);

            MySqlParameter sqlParameter1 = new MySqlParameter();

            sqlParameter1.ParameterName = "sync_row_timestamp";
            sqlParameter1.MySqlDbType   = MySqlDbType.Int64;
            sqlCommand.Parameters.Add(sqlParameter1);

            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendLine();
            stringBuilder.AppendLine($"DELETE FROM {trackingName.FullQuotedString} ");
            stringBuilder.Append($"WHERE ");
            stringBuilder.AppendLine(MySqlManagementUtils.ColumnsAndParameters(this.tableDescription.PrimaryKey.Columns, ""));
            stringBuilder.Append(";");
            sqlCommand.CommandText = stringBuilder.ToString();
            return(sqlCommand);
        }
示例#8
0
        //------------------------------------------------------------------
        // Insert command
        //------------------------------------------------------------------
        private MySqlCommand BuildInsertCommand()
        {
            MySqlCommand  sqlCommand              = new MySqlCommand();
            StringBuilder stringBuilder           = new StringBuilder();
            StringBuilder stringBuilderArguments  = new StringBuilder();
            StringBuilder stringBuilderParameters = new StringBuilder();

            this.AddColumnParametersToCommand(sqlCommand);

            stringBuilder.Append(string.Concat("IF ((SELECT COUNT(*) FROM ", trackingName.FullQuotedString, " WHERE "));
            stringBuilder.Append(MySqlManagementUtils.ColumnsAndParameters(this.tableDescription.PrimaryKey.Columns, string.Empty));
            stringBuilder.AppendLine(") <= 0) THEN");

            string empty = string.Empty;

            foreach (var mutableColumn in this.tableDescription.Columns.Where(c => !c.IsReadOnly))
            {
                ObjectNameParser columnName = new ObjectNameParser(mutableColumn.ColumnName, "`", "`");
                stringBuilderArguments.Append(string.Concat(empty, columnName.FullQuotedString));
                stringBuilderParameters.Append(string.Concat(empty, $"{MYSQL_PREFIX_PARAMETER}{columnName.FullUnquotedString}"));
                empty = ", ";
            }
            stringBuilder.AppendLine($"\tINSERT INTO {tableName.FullQuotedString}");
            stringBuilder.AppendLine($"\t({stringBuilderArguments.ToString()})");
            stringBuilder.AppendLine($"\tVALUES ({stringBuilderParameters.ToString()});");
            stringBuilder.AppendLine();

            stringBuilder.AppendLine("END IF;");
            sqlCommand.CommandText = stringBuilder.ToString();
            return(sqlCommand);
        }
        public bool NeedToCreateForeignKeyConstraints(DmRelation foreignKey)
        {
            string parentTable    = foreignKey.ParentTable.TableName;
            string parentSchema   = foreignKey.ParentTable.Schema;
            string parentFullName = String.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";

            bool alreadyOpened = connection.State == ConnectionState.Open;

            try
            {
                if (!alreadyOpened)
                {
                    connection.Open();
                }

                var dmTable = MySqlManagementUtils.RelationsForTable(connection, transaction, parentFullName);

                var foreignKeyExist = dmTable.Rows.Any(r =>
                                                       dmTable.IsEqual(r["ForeignKey"].ToString(), foreignKey.RelationName));

                return(!foreignKeyExist);
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"Error during checking foreign keys: {ex}");
                throw;
            }
            finally
            {
                if (!alreadyOpened && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }
        public async Task <bool> NeedToCreateTriggerAsync(DbTriggerType type, DbConnection connection, DbTransaction transaction)
        {
            var updTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger).name, tableName.Unquoted().Normalized().ToString());
            var delTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name, tableName.Unquoted().Normalized().ToString());
            var insTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name, tableName.Unquoted().Normalized().ToString());

            string triggerName = string.Empty;

            switch (type)
            {
            case DbTriggerType.Insert:
            {
                triggerName = insTriggerName;
                break;
            }

            case DbTriggerType.Update:
            {
                triggerName = updTriggerName;
                break;
            }

            case DbTriggerType.Delete:
            {
                triggerName = delTriggerName;
                break;
            }
            }

            return(!await MySqlManagementUtils.TriggerExistsAsync((MySqlConnection)connection, (MySqlTransaction)transaction, triggerName).ConfigureAwait(false));
        }
示例#11
0
        public bool NeedToCreateTrackingTable(DbBuilderOption builderOption)
        {
            if (builderOption.HasFlag(DbBuilderOption.CreateOrUseExistingSchema))
            {
                return(!MySqlManagementUtils.TableExists(connection, transaction, trackingName.UnquotedString));
            }

            return(false);
        }
        /// <summary>
        /// Check if we need to create the stored procedure
        /// </summary>
        public async Task <bool> NeedToCreateProcedureAsync(DbCommandType commandType)
        {
            if (connection.State != ConnectionState.Open)
            {
                throw new ArgumentException("Here, we need an opened connection please");
            }

            var commandName = this.mySqlObjectNames.GetCommandName(commandType).name;

            return(!(await MySqlManagementUtils.ProcedureExistsAsync(connection, transaction, commandName).ConfigureAwait(false)));
        }
示例#13
0
        private void CreateDeleteMetadataCommandText()
        {
            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendLine($"DELETE FROM {trackingName.QuotedString} ");
            stringBuilder.Append($"WHERE ");
            stringBuilder.AppendLine(MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, ""));
            stringBuilder.Append(";");

            this.AddName(DbCommandType.DeleteMetadata, stringBuilder.ToString());
        }
示例#14
0
        /// <summary>
        /// Check if we need to create the stored procedure
        /// </summary>
        public bool NeedToCreateProcedure(DbCommandType commandType)
        {
            if (connection.State != ConnectionState.Open)
            {
                throw new ArgumentException("Here, we need an opened connection please");
            }

            var commandName = this.sqlObjectNames.GetCommandName(commandType);

            return(!MySqlManagementUtils.ProcedureExists(connection, transaction, commandName));
        }
示例#15
0
        public async Task <bool> NeedToCreateForeignKeyConstraintsAsync(SyncRelation relation, DbConnection connection, DbTransaction transaction)
        {
            string tableName = relation.GetTable().TableName;

            var relationName = NormalizeRelationName(relation.RelationName);

            var relations = await MySqlManagementUtils.GetRelationsForTableAsync((MySqlConnection)connection, (MySqlTransaction)transaction, tableName).ConfigureAwait(false);

            var foreignKeyExist = relations.Rows.Any(r =>
                                                     string.Equals(r["ForeignKey"].ToString(), relationName, SyncGlobalization.DataSourceStringComparison));

            return(!foreignKeyExist);
        }
示例#16
0
        private void CreateDeleteCommandText()
        {
            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendLine($"DELETE FROM {tableName.QuotedString} ");
            stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}");
            stringBuilder.AppendLine($" AND ((SELECT `timestamp` FROM {trackingName.QuotedObjectName} ");
            stringBuilder.AppendLine($"  WHERE {MySqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKey.Columns, tableName.QuotedObjectName, trackingName.QuotedObjectName)}");
            stringBuilder.AppendLine(" ) <= @sync_min_timestamp OR @sync_force_write = 1");
            stringBuilder.AppendLine(");");

            this.AddName(DbCommandType.DeleteRow, stringBuilder.ToString());
        }
示例#17
0
        private void CreateUpdatedMetadataCommandText()
        {
            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendLine($"UPDATE {trackingName.QuotedString}");
            stringBuilder.AppendLine($"SET `update_scope_id` = @update_scope_id, ");
            stringBuilder.AppendLine($"\t `update_timestamp` = @update_timestamp, ");
            stringBuilder.AppendLine($"\t `sync_row_is_tombstone` = @sync_row_is_tombstone, ");
            stringBuilder.AppendLine($"\t `timestamp` = {MySqlObjectNames.TimestampValue}, ");
            stringBuilder.AppendLine($"\t `last_change_datetime` = now() ");
            stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}");

            this.AddName(DbCommandType.UpdateMetadata, stringBuilder.ToString());
        }
示例#18
0
        //------------------------------------------------------------------
        // Update Metadata command
        //------------------------------------------------------------------
        private MySqlCommand BuildUpdateMetadataCommand()
        {
            MySqlCommand  sqlCommand    = new MySqlCommand();
            StringBuilder stringBuilder = new StringBuilder();

            this.AddPkColumnParametersToCommand(sqlCommand);
            MySqlParameter sqlParameter = new MySqlParameter("sync_scope_id", MySqlDbType.Guid);

            sqlParameter.Size = 36;
            sqlCommand.Parameters.Add(sqlParameter);
            MySqlParameter sqlParameter1 = new MySqlParameter("sync_row_is_tombstone", MySqlDbType.Int32);

            sqlCommand.Parameters.Add(sqlParameter1);
            MySqlParameter sqlParameter3 = new MySqlParameter("create_timestamp", MySqlDbType.Int64);

            sqlCommand.Parameters.Add(sqlParameter3);
            MySqlParameter sqlParameter5 = new MySqlParameter("update_timestamp", MySqlDbType.Int64);

            sqlCommand.Parameters.Add(sqlParameter5);


            stringBuilder.AppendLine($"DECLARE was_tombstone int;");
            stringBuilder.AppendLine($"SET was_tombstone = 1;");
            stringBuilder.AppendLine($"SELECT was_tombstone = `sync_row_is_tombstone` FROM {trackingName.QuotedObjectName} WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, trackingName.QuotedObjectName)};");
            stringBuilder.AppendLine($"IF (was_tombstone is not null and was_tombstone = 1 and sync_row_is_tombstone = 0) THEN");

            stringBuilder.AppendLine($"UPDATE {trackingName.QuotedString}");
            stringBuilder.AppendLine($"SET `create_scope_id` = sync_scope_id, ");
            stringBuilder.AppendLine($"\t `update_scope_id` = sync_scope_id, ");
            stringBuilder.AppendLine($"\t `create_timestamp` = create_timestamp, ");
            stringBuilder.AppendLine($"\t `update_timestamp` = update_timestamp, ");
            stringBuilder.AppendLine($"\t `sync_row_is_tombstone` = sync_row_is_tombstone, ");
            stringBuilder.AppendLine($"\t `timestamp` = {MySqlObjectNames.TimestampValue}, ");
            stringBuilder.AppendLine($"\t `last_change_datetime` = now() ");
            stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")};");

            stringBuilder.AppendLine($"ELSE");

            stringBuilder.AppendLine($"UPDATE {trackingName.QuotedString}");
            stringBuilder.AppendLine($"SET `update_scope_id` = sync_scope_id, ");
            stringBuilder.AppendLine($"\t `update_timestamp` = update_timestamp, ");
            stringBuilder.AppendLine($"\t `sync_row_is_tombstone` = sync_row_is_tombstone, ");
            stringBuilder.AppendLine($"\t `timestamp` = {MySqlObjectNames.TimestampValue}, ");
            stringBuilder.AppendLine($"\t `last_change_datetime` = now() ");
            stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")};");
            stringBuilder.AppendLine("END IF;");
            sqlCommand.CommandText = stringBuilder.ToString();
            return(sqlCommand);
        }
示例#19
0
        /// <summary>
        /// Check if we need to create the stored procedure
        /// </summary>
        public bool NeedToCreateProcedure(DbCommandType commandType, DbBuilderOption option)
        {
            if (connection.State != ConnectionState.Open)
            {
                throw new ArgumentException("Here, we need an opened connection please");
            }

            var commandName = this.sqlObjectNames.GetCommandName(commandType);

            if (option.HasFlag(DbBuilderOption.CreateOrUseExistingSchema))
            {
                return(!MySqlManagementUtils.ProcedureExists(connection, transaction, commandName));
            }

            return(false);
        }
示例#20
0
        /// <summary>
        /// For a foreign key, check if the Parent table exists
        /// </summary>
        private bool EnsureForeignKeysTableExist(DmRelation foreignKey)
        {
            var childTable  = foreignKey.ChildTable;
            var parentTable = foreignKey.ParentTable;

            // The foreignkey comes from the child table
            var ds = foreignKey.ChildTable.DmSet;

            if (ds == null)
            {
                return(false);
            }

            // Check if the parent table is part of the sync configuration
            var exist = ds.Tables.Any(t => ds.IsEqual(t.TableName.ToLowerInvariant(), parentTable.TableName.ToLowerInvariant()));

            if (!exist)
            {
                return(false);
            }

            bool alreadyOpened = connection.State == ConnectionState.Open;

            try
            {
                if (!alreadyOpened)
                {
                    connection.Open();
                }

                return(MySqlManagementUtils.TableExists(connection, transaction, parentTable.TableName.ToLowerInvariant()));
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"Error during EnsureForeignKeysTableExist : {ex}");
                throw;
            }
            finally
            {
                if (!alreadyOpened && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }
示例#21
0
        public bool NeedToCreateForeignKeyConstraints(DmRelation foreignKey)
        {
            string parentTable    = foreignKey.ParentTable.TableName;
            string parentSchema   = foreignKey.ParentTable.Schema;
            string parentFullName = string.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";

            var relationName = NormalizeRelationName(foreignKey.RelationName);

            bool alreadyOpened = this.connection.State == ConnectionState.Open;

            // Don't want foreign key on same table since it could be a problem on first
            // sync. We are not sure that parent row will be inserted in first position
            if (string.Equals(parentTable, foreignKey.ChildTable.TableName, StringComparison.CurrentCultureIgnoreCase))
            {
                return(false);
            }

            try
            {
                if (!alreadyOpened)
                {
                    this.connection.Open();
                }

                var dmTable = MySqlManagementUtils.RelationsForTable(this.connection, this.transaction, parentFullName);

                var foreignKeyExist = dmTable.Rows.Any(r =>
                                                       dmTable.IsEqual(r["ForeignKey"].ToString(), relationName));

                return(!foreignKeyExist);
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"Error during checking foreign keys: {ex}");
                throw;
            }
            finally
            {
                if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
                {
                    this.connection.Close();
                }
            }
        }
示例#22
0
        //------------------------------------------------------------------
        // Update command
        //------------------------------------------------------------------
        private MySqlCommand BuildUpdateCommand()
        {
            MySqlCommand sqlCommand = new MySqlCommand();

            StringBuilder stringBuilder = new StringBuilder();

            this.AddColumnParametersToCommand(sqlCommand);

            MySqlParameter sqlParameter = new MySqlParameter();

            sqlParameter.ParameterName = "sync_force_write";
            sqlParameter.MySqlDbType   = MySqlDbType.Int32;
            sqlCommand.Parameters.Add(sqlParameter);

            MySqlParameter sqlParameter1 = new MySqlParameter();

            sqlParameter1.ParameterName = "sync_min_timestamp";
            sqlParameter1.MySqlDbType   = MySqlDbType.Int64;
            sqlCommand.Parameters.Add(sqlParameter1);


            stringBuilder.AppendLine("DECLARE ts BIGINT;");
            stringBuilder.AppendLine("SET ts = 0;");
            stringBuilder.AppendLine($"SELECT `timestamp` FROM {trackingName.QuotedObjectName} WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, trackingName.QuotedObjectName)} LIMIT 1 INTO ts;");

            stringBuilder.AppendLine($"UPDATE {tableName.FullQuotedString}");
            stringBuilder.Append($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.tableDescription)}");
            stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")}");
            stringBuilder.AppendLine($" AND (ts <= sync_min_timestamp OR sync_force_write = 1);");

            stringBuilder.AppendLine();
            // Can't rely on rows count since MySql will return 0 if an update don't update any columns

            //stringBuilder.AppendLine($"/* Since the update 'could' potentially returns 0 as row affected count when we make a double update with the same values, to be sure, make a fake update on metadatas time column */");
            //stringBuilder.AppendLine($"UPDATE {trackingName.QuotedObjectName} ");
            //stringBuilder.AppendLine($"SET `timestamp` = {MySqlObjectNames.TimestampValue}");
            //stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")} AND (ts <= sync_min_timestamp OR sync_force_write = 1);");

            sqlCommand.CommandText = stringBuilder.ToString();
            return(sqlCommand);
        }
示例#23
0
        public bool NeedToCreateTrigger(DbTriggerType type, DbBuilderOption option)
        {
            var updTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.UpdateTrigger), tableName.UnquotedStringWithUnderScore);
            var delTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.DeleteTrigger), tableName.UnquotedStringWithUnderScore);
            var insTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.InsertTrigger), tableName.UnquotedStringWithUnderScore);

            if (option.HasFlag(DbBuilderOption.CreateOrUseExistingSchema))
            {
                string triggerName = string.Empty;
                switch (type)
                {
                case DbTriggerType.Insert:
                {
                    triggerName = insTriggerName;
                    break;
                }

                case DbTriggerType.Update:
                {
                    triggerName = updTriggerName;
                    break;
                }

                case DbTriggerType.Delete:
                {
                    triggerName = delTriggerName;
                    break;
                }
                }

                return(!MySqlManagementUtils.TriggerExists(connection, transaction, triggerName));
            }

            if (option.HasFlag(DbBuilderOption.UseExistingSchema))
            {
                return(false);
            }

            return(false);
        }
示例#24
0
        private string CreateUpdateUntrackedRowsCommand()
        {
            var stringBuilder = new StringBuilder();
            var str1          = new StringBuilder();
            var str2          = new StringBuilder();
            var str3          = new StringBuilder();
            var str4          = MySqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.GetPrimaryKeysColumns(), "`side`", "`base`");

            stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");


            var comma = "";

            foreach (var pkeyColumn in TableDescription.GetPrimaryKeysColumns())
            {
                var pkeyColumnName = ParserName.Parse(pkeyColumn, "`").Quoted().ToString();

                str1.Append($"{comma}{pkeyColumnName}");
                str2.Append($"{comma}`base`.{pkeyColumnName}");
                str3.Append($"{comma}`side`.{pkeyColumnName}");

                comma = ", ";
            }
            stringBuilder.Append(str1.ToString());
            stringBuilder.AppendLine($", `update_scope_id`, `sync_row_is_tombstone`, `timestamp`, `last_change_datetime`");
            stringBuilder.AppendLine($")");
            stringBuilder.Append($"SELECT ");
            stringBuilder.Append(str2.ToString());
            stringBuilder.AppendLine($", NULL, 0, {MySqlObjectNames.TimestampValue}, now()");
            stringBuilder.AppendLine($"FROM {tableName.Schema().Quoted().ToString()} as `base` WHERE NOT EXISTS");
            stringBuilder.Append($"(SELECT ");
            stringBuilder.Append(str3.ToString());
            stringBuilder.AppendLine($" FROM {trackingName.Schema().Quoted().ToString()} as `side` ");
            stringBuilder.AppendLine($"WHERE {str4})");

            var r = stringBuilder.ToString();

            return(r);
        }
示例#25
0
        private void CreateUpdateCommandText()
        {
            //StringBuilder stringBuilder = new StringBuilder();
            //stringBuilder.AppendLine($"SELECT @ts := `timestamp` ");
            //stringBuilder.AppendLine($"FROM {trackingName.QuotedObjectName}");
            //stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")};");
            //stringBuilder.AppendLine();
            //stringBuilder.AppendLine($"UPDATE {tableName.QuotedString}");
            //stringBuilder.AppendLine($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.TableDescription)}");
            //stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}");
            //stringBuilder.AppendLine($"AND (@ts <= @sync_min_timestamp OR @sync_force_write = 1);");

            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendLine($"UPDATE {tableName.QuotedString}");
            stringBuilder.Append($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.TableDescription)}");
            stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}");
            stringBuilder.AppendLine($" AND ((SELECT `timestamp` FROM {trackingName.QuotedObjectName} ");
            stringBuilder.AppendLine($"  WHERE {MySqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKey.Columns, tableName.QuotedObjectName, trackingName.QuotedObjectName)}");
            stringBuilder.AppendLine(" ) <= @sync_min_timestamp OR @sync_force_write = 1");
            stringBuilder.AppendLine(");");
            this.AddName(DbCommandType.UpdateRow, stringBuilder.ToString());
        }
示例#26
0
 public bool NeedToCreateTrackingTable()
 {
     return(!MySqlManagementUtils.TableExists(connection, transaction, trackingName.FullUnquotedString));
 }
示例#27
0
        private string UpdateTriggerBodyText()
        {
            var stringBuilder = new StringBuilder();
            stringBuilder.AppendLine();
            stringBuilder.AppendLine($"Begin ");
            stringBuilder.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()} ");
            stringBuilder.AppendLine("\tSET `update_scope_id` = NULL ");
            stringBuilder.AppendLine($"\t\t,`timestamp` = {MySqlObjectNames.TimestampValue}");
            stringBuilder.AppendLine("\t\t,`last_change_datetime` = utc_timestamp()");

            stringBuilder.Append($"\tWhere ");
            stringBuilder.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.GetPrimaryKeysColumns(), trackingName.Quoted().ToString(), "new"));

            if (this.tableDescription.GetMutableColumns().Count() > 0)
            {
                stringBuilder.AppendLine();
                stringBuilder.AppendLine("\t AND (");
                string or = "    ";
                foreach (var column in this.tableDescription.GetMutableColumns())
                {
                    var quotedColumn = ParserName.Parse(column, "`").Quoted().ToString();

                    stringBuilder.Append("\t");
                    stringBuilder.Append(or);
                    stringBuilder.Append("IFNULL(");
                    stringBuilder.Append("NULLIF(");
                    stringBuilder.Append("`old`.");
                    stringBuilder.Append(quotedColumn);
                    stringBuilder.Append(", ");
                    stringBuilder.Append("`new`.");
                    stringBuilder.Append(quotedColumn);
                    stringBuilder.Append(")");
                    stringBuilder.Append(", ");
                    stringBuilder.Append("NULLIF(");
                    stringBuilder.Append("`new`.");
                    stringBuilder.Append(quotedColumn);
                    stringBuilder.Append(", ");
                    stringBuilder.Append("`old`.");
                    stringBuilder.Append(quotedColumn);
                    stringBuilder.Append(")");
                    stringBuilder.AppendLine(") IS NOT NULL");

                    or = " OR ";
                }
                stringBuilder.AppendLine("\t ) ");
            }
            stringBuilder.AppendLine($"; ");

            stringBuilder.AppendLine("IF (SELECT ROW_COUNT() = 0) THEN ");

            stringBuilder.AppendLine($"\tINSERT INTO {trackingName.Quoted().ToString()} (");

            StringBuilder stringBuilderArguments = new StringBuilder();
            StringBuilder stringBuilderArguments2 = new StringBuilder();
            StringBuilder stringPkAreNull = new StringBuilder();

            string argComma = string.Empty;
            string argAnd = string.Empty;
            foreach (var mutableColumn in this.tableDescription.GetPrimaryKeysColumns().Where(c => !c.IsReadOnly))
            {
                var columnName = ParserName.Parse(mutableColumn, "`").Quoted().ToString();

                stringBuilderArguments.AppendLine($"\t\t{argComma}{columnName}");
                stringBuilderArguments2.AppendLine($"\t\t{argComma}new.{columnName}");
                stringPkAreNull.Append($"{argAnd}{trackingName.Quoted().ToString()}.{columnName} IS NULL");
                argComma = ",";
                argAnd = " AND ";
            }

            stringBuilder.Append(stringBuilderArguments.ToString());
            stringBuilder.AppendLine("\t\t,`update_scope_id`");
            stringBuilder.AppendLine("\t\t,`timestamp`");
            stringBuilder.AppendLine("\t\t,`sync_row_is_tombstone`");
            stringBuilder.AppendLine("\t\t,`last_change_datetime`");

            var filterColumnsString = new StringBuilder();
            var filterColumnsString2 = new StringBuilder();
            var filterColumnsString3 = new StringBuilder();

            stringBuilder.AppendLine("\t) ");
            stringBuilder.AppendLine("\tVALUES (");
            stringBuilder.Append(stringBuilderArguments2.ToString());
            stringBuilder.AppendLine("\t\t,NULL");
            stringBuilder.AppendLine($"\t\t,{MySqlObjectNames.TimestampValue}");
            stringBuilder.AppendLine("\t\t,0");
            stringBuilder.AppendLine("\t\t,utc_timestamp()");


            stringBuilder.AppendLine("\t)");
            stringBuilder.AppendLine("ON DUPLICATE KEY UPDATE");
            stringBuilder.AppendLine("\t`update_scope_id` = NULL, ");
            stringBuilder.AppendLine("\t`sync_row_is_tombstone` = 0, ");
            stringBuilder.AppendLine($"\t`timestamp` = {MySqlObjectNames.TimestampValue}, ");
            stringBuilder.AppendLine("\t`last_change_datetime` = utc_timestamp();");

            stringBuilder.AppendLine("END IF;");

            stringBuilder.AppendLine($"End; ");
            return stringBuilder.ToString();
        }
        //------------------------------------------------------------------
        // Delete command
        //------------------------------------------------------------------
        private MySqlCommand BuildDeleteCommand()
        {
            MySqlCommand sqlCommand = new MySqlCommand();

            this.AddPkColumnParametersToCommand(sqlCommand);

            var sqlParameter1 = new MySqlParameter();

            sqlParameter1.ParameterName = "sync_scope_id";
            sqlParameter1.MySqlDbType   = MySqlDbType.Guid;
            sqlParameter1.Size          = 36;
            sqlCommand.Parameters.Add(sqlParameter1);

            var sqlParameter = new MySqlParameter();

            sqlParameter.ParameterName = "sync_force_write";
            sqlParameter.MySqlDbType   = MySqlDbType.Int32;
            sqlCommand.Parameters.Add(sqlParameter);

            var sqlParameter2 = new MySqlParameter();

            sqlParameter2.ParameterName = "sync_min_timestamp";
            sqlParameter2.MySqlDbType   = MySqlDbType.Int64;
            sqlCommand.Parameters.Add(sqlParameter2);

            sqlParameter = new MySqlParameter();
            sqlParameter.ParameterName = "sync_row_count";
            sqlParameter.MySqlDbType   = MySqlDbType.Int32;
            sqlParameter.Direction     = ParameterDirection.Output;
            sqlCommand.Parameters.Add(sqlParameter);


            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendLine();
            stringBuilder.AppendLine("DECLARE ts BIGINT;");
            stringBuilder.AppendLine("DECLARE t_update_scope_id VARCHAR(36);");
            stringBuilder.AppendLine("SET ts = 0;");
            stringBuilder.AppendLine($"SELECT `timestamp`, `update_scope_id` FROM {trackingName.Quoted().ToString()} WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), trackingName.Quoted().ToString())} LIMIT 1 INTO ts, t_update_scope_id;");
            stringBuilder.AppendLine($"DELETE FROM {tableName.Quoted().ToString()} WHERE");
            stringBuilder.AppendLine(MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), ""));
            stringBuilder.AppendLine("AND (ts <= sync_min_timestamp OR ts IS NULL OR t_update_scope_id = sync_scope_id OR sync_force_write = 1);");

            stringBuilder.AppendLine();
            stringBuilder.AppendLine($"SELECT ROW_COUNT() LIMIT 1 INTO sync_row_count;");
            stringBuilder.AppendLine();

            stringBuilder.AppendLine();
            stringBuilder.AppendLine($"IF (sync_row_count > 0) THEN");
            stringBuilder.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()}");
            stringBuilder.AppendLine($"\tSET `update_scope_id` = sync_scope_id, ");
            stringBuilder.AppendLine($"\t\t `sync_row_is_tombstone` = 1, ");
            stringBuilder.AppendLine($"\t\t `timestamp` = {MySqlObjectNames.TimestampValue}, ");
            stringBuilder.AppendLine($"\t\t `last_change_datetime` = now() ");
            stringBuilder.AppendLine($"\tWHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), "")};");
            stringBuilder.AppendLine($"END IF;");


            sqlCommand.CommandText = stringBuilder.ToString();
            return(sqlCommand);
        }
示例#29
0
 public bool NeedToCreateTrackingTable()
 => !MySqlManagementUtils.TableExists(connection, transaction, trackingName);
        //------------------------------------------------------------------
        // Update command
        //------------------------------------------------------------------
        private MySqlCommand BuildUpdateCommand(bool hasMutableColumns)
        {
            var sqlCommand = new MySqlCommand();

            var stringBuilder = new StringBuilder();

            this.AddColumnParametersToCommand(sqlCommand);

            var sqlParameter = new MySqlParameter();

            sqlParameter.ParameterName = "sync_scope_id";
            sqlParameter.MySqlDbType   = MySqlDbType.Guid;
            sqlParameter.Size          = 36;
            sqlCommand.Parameters.Add(sqlParameter);

            sqlParameter = new MySqlParameter();
            sqlParameter.ParameterName = "sync_force_write";
            sqlParameter.MySqlDbType   = MySqlDbType.Int32;
            sqlCommand.Parameters.Add(sqlParameter);

            sqlParameter = new MySqlParameter();
            sqlParameter.ParameterName = "sync_min_timestamp";
            sqlParameter.MySqlDbType   = MySqlDbType.Int64;
            sqlCommand.Parameters.Add(sqlParameter);

            sqlParameter = new MySqlParameter();
            sqlParameter.ParameterName = "sync_row_count";
            sqlParameter.MySqlDbType   = MySqlDbType.Int32;
            sqlParameter.Direction     = ParameterDirection.Output;
            sqlCommand.Parameters.Add(sqlParameter);

            var listColumnsTmp  = new StringBuilder();
            var listColumnsTmp2 = new StringBuilder();
            var listColumnsTmp3 = new StringBuilder();


            var and = "";

            foreach (var column in this.tableDescription.GetPrimaryKeysColumns())
            {
                var param      = GetMySqlParameter(column);
                var declar     = CreateParameterDeclaration(param);
                var columnName = ParserName.Parse(column, "`").Quoted().ToString();

                // Primary keys column name, with quote
                listColumnsTmp.Append($"{columnName}, ");

                // param name without type
                listColumnsTmp2.Append($"t_{param.ParameterName}, ");

                // param name with type
                stringBuilder.AppendLine($"DECLARE t_{declar};");

                // Param equal IS NULL
                listColumnsTmp3.Append($"{and}t_{param.ParameterName} IS NULL");

                and = " AND ";
            }



            stringBuilder.AppendLine("DECLARE ts BIGINT;");

            stringBuilder.AppendLine("DECLARE t_update_scope_id VARCHAR(36);");
            stringBuilder.AppendLine("SET ts = 0;");
            stringBuilder.AppendLine($"SELECT {listColumnsTmp.ToString()}");
            stringBuilder.AppendLine($"`timestamp`, `update_scope_id` FROM {trackingName.Quoted().ToString()} ");
            stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), trackingName.Quoted().ToString())} LIMIT 1 ");
            stringBuilder.AppendLine($"INTO {listColumnsTmp2.ToString()} ts, t_update_scope_id;");
            stringBuilder.AppendLine();

            if (hasMutableColumns)
            {
                stringBuilder.AppendLine($"UPDATE {tableName.Quoted().ToString()}");
                stringBuilder.Append($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.tableDescription)}");
                stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), "")}");
                stringBuilder.AppendLine($" AND (ts <= sync_min_timestamp OR ts IS NULL OR t_update_scope_id  = sync_scope_id OR sync_force_write = 1);");
                stringBuilder.AppendLine();
                stringBuilder.AppendLine($"SELECT ROW_COUNT() LIMIT 1 INTO sync_row_count;");
                stringBuilder.AppendLine($"IF (sync_row_count = 0) THEN");
            }

            string empty = string.Empty;
            var    stringBuilderArguments  = new StringBuilder();
            var    stringBuilderParameters = new StringBuilder();

            foreach (var mutableColumn in this.tableDescription.Columns.Where(c => !c.IsReadOnly))
            {
                var columnName    = ParserName.Parse(mutableColumn, "`").Quoted().ToString();
                var parameterName = ParserName.Parse(mutableColumn, "`").Unquoted().Normalized().ToString();

                stringBuilderArguments.Append(string.Concat(empty, columnName));
                stringBuilderParameters.Append(string.Concat(empty, $"{MYSQL_PREFIX_PARAMETER}{parameterName}"));
                empty = ", ";
            }

            // If we don't have any mutable column, we can't update, and the Insert
            // will fail if we don't ignore the insert (on Reinitialize for example)
            var ignoreKeyWord = hasMutableColumns ? "" : "IGNORE";

            stringBuilder.AppendLine($"\tINSERT {ignoreKeyWord} INTO {tableName.Quoted().ToString()}");
            stringBuilder.AppendLine($"\t({stringBuilderArguments.ToString()})");
            stringBuilder.AppendLine($"\tSELECT * FROM ( SELECT {stringBuilderParameters.ToString()}) as TMP ");
            stringBuilder.AppendLine($"\tWHERE ( {listColumnsTmp3.ToString()} )");
            stringBuilder.AppendLine($"\tOR (ts <= sync_min_timestamp OR ts IS NULL OR t_update_scope_id = sync_scope_id OR sync_force_write = 1)");
            stringBuilder.AppendLine($"\tLIMIT 1;");
            stringBuilder.AppendLine();
            stringBuilder.AppendLine($"SELECT ROW_COUNT() LIMIT 1 INTO sync_row_count;");
            stringBuilder.AppendLine();

            if (hasMutableColumns)
            {
                stringBuilder.AppendLine("END IF;");
            }

            stringBuilder.AppendLine();
            stringBuilder.AppendLine($"IF (sync_row_count > 0) THEN");
            stringBuilder.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()}");
            stringBuilder.AppendLine($"\tSET `update_scope_id` = sync_scope_id, ");
            stringBuilder.AppendLine($"\t\t `sync_row_is_tombstone` = 0, ");
            stringBuilder.AppendLine($"\t\t `timestamp` = {MySqlObjectNames.TimestampValue}, ");
            stringBuilder.AppendLine($"\t\t `last_change_datetime` = now() ");
            stringBuilder.AppendLine($"\tWHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), "")};");
            stringBuilder.AppendLine($"END IF;");

            sqlCommand.CommandText = stringBuilder.ToString();
            return(sqlCommand);
        }