Skip to content

Adjust Column Value

Jump to bottom
adriancs edited this page Jul 4, 2025 · 18 revisions

Column Value Adjustment Feature

Overview

The Column Value Adjustment feature enables developers to dynamically transform column values during MySQL database exports without modifying the source data. This powerful capability allows for real-time data processing, formatting, masking, and custom transformations as part of the export workflow.

Originally contributed by Tyler Pflueger (tpflueger) via pull request #113, the feature has evolved significantly since its introduction in v2.3.9, with the implementation being completely rewritten in v2.6 for improved performance and usability.

Key Benefits

Data Privacy & Security:

  • Mask sensitive information such as personally identifiable information (PII), emails, or confidential data before sharing exports with third parties or development teams.

Export-Time Formatting:

  • Apply consistent formatting to data values, normalize inconsistent entries, or convert data types to meet specific requirements without altering the original database.

Testing & Development:

  • Generate realistic but anonymized datasets for testing environments while maintaining referential integrity and data relationships.

Migration & Integration:

  • Transform data during export to match target system requirements, including value mapping, unit conversions, and schema adaptations.

Implementation

Before and After Comparison

To demonstrate the effect of column value adjustments, here's a comparison of SQL output:

Before (Original Data):

INSERT INTO `users` (`id`, `name`, `email`) VALUES
(1, 'Jennifer Wilson', 'jennifer.wilson@innovatetech.com'),
(2, 'Sarah Mitchell', 'sarah.mitchell@marssen.com'),
(3, 'Michael Chen', 'michael.chen@wendysam.com');

After (With Column Value Adjustments Applied):

INSERT INTO `users` (`id`, `name`, `email`) VALUES
(1, 'Jen************son', 'jen*****@innovatetech.com'),
(2, 'Sar************ell', 'sar*****@marssen.com'),
(3, 'Mic************hen', 'mic*****@wendysam.com');

As demonstrated above, the adjustment functions have:

  • Masked names: Showing first 3 characters + 12 asterisks + last 3 characters
  • Masked emails: Showing first 3 characters of local part + asterisks + full domain

This transformation occurs during the export process without modifying the original database, ensuring data privacy while maintaining the structural integrity of the exported SQL statements.

Basic Setup

Column value adjustments are configured through the ExportInformations.AddTableColumnValueAdjustment() method:

// Configure the backup instance
var backup = new MySqlBackup(command);

// Add column adjustment for specific table and column
backup.ExportInfo.AddTableColumnValueAdjustment("tableName", "columnName", adjustmentFunction);

Two Ways to Define Adjustment Functions

Method 1: Explicit Method Declaration (Recommended for Beginners)

For developers new to C# or those who prefer explicit method declarations, you can define adjustment functions as separate methods:

// Define adjustment methods explicitly
public object MaskEmail(object value)
{
    if (value == null) return null;
    
    string email = value.ToString();
    if (string.IsNullOrEmpty(email) || !email.Contains("@")) 
        return "***@***.***";
    
    var parts = email.Split('@');
    var maskedLocal = parts[0].Length >= 3 
        ? parts[0].Substring(0, 3) + "*****" 
        : "*****";
    
    return $"{maskedLocal}@{parts[1]}";
}

public object MaskName(object value)
{
    if (value == null) return null;
    
    string name = value.ToString();
    if (string.IsNullOrEmpty(name)) return "";
    
    return name.Length >= 6 
        ? $"{name.Substring(0, 3)}************{name.Substring(name.Length - 3)}"
        : new string('*', name.Length);
}

// Add the adjustment rules using method references
backup.ExportInfo.AddTableColumnValueAdjustment("users", "email", MaskEmail);
backup.ExportInfo.AddTableColumnValueAdjustment("users", "name", MaskName);

Method 2: Inline Lambda Functions (Advanced)

For experienced developers who prefer concise code, you can use inline lambda expressions:

// Mask email addresses
backup.ExportInfo.AddTableColumnValueAdjustment("users", "email", value => {
    if (value == null) return null;
    
    string email = value.ToString();
    if (string.IsNullOrEmpty(email) || !email.Contains("@")) 
        return "***@***.***";
    
    var parts = email.Split('@');
    var maskedLocal = parts[0].Length >= 3 
        ? parts[0].Substring(0, 3) + "*****" 
        : "*****";
    
    return $"{maskedLocal}@{parts[1]}";
});

// Mask personal names
backup.ExportInfo.AddTableColumnValueAdjustment("users", "name", value => {
    if (value == null) return null;
    
    string name = value.ToString();
    if (string.IsNullOrEmpty(name)) return "";
    
    return name.Length >= 6 
        ? $"{name.Substring(0, 3)}************{name.Substring(name.Length - 3)}"
        : new string('*', name.Length);
});

Complete Code Examples

Full Implementation with External Method Declaration

public class DatabaseBackupExample
{
    public void ExportWithAdjustments()
    {
        string constr = "server=localhost;user=root;pwd=1234;database=test1;convertzerodatetime=true;";
        string filePath = @"C:\backup.sql";

        using (var conn = new MySqlConnection(constr))
        using (var cmd = conn.CreateCommand())
        using (var mb = new MySqlBackup(cmd))
        {
            conn.Open();
            
            // Add column adjustment rules using method references
            mb.ExportInfo.AddTableColumnValueAdjustment("users", "email", MaskEmail);
            mb.ExportInfo.AddTableColumnValueAdjustment("users", "name", MaskName);
            
            mb.ExportToFile(filePath);
        }
    }

    // Define adjustment methods explicitly
    public object MaskEmail(object value)
    {
        if (value == null) return null;
        
        string email = value.ToString();
        if (string.IsNullOrEmpty(email) || !email.Contains("@")) 
            return "***@***.***";
        
        var parts = email.Split('@');
        var maskedLocal = parts[0].Length >= 3 
            ? parts[0].Substring(0, 3) + "*****" 
            : "*****";
        
        return $"{maskedLocal}@{parts[1]}";
    }

    public object MaskName(object value)
    {
        if (value == null) return null;
        
        string name = value.ToString();
        if (string.IsNullOrEmpty(name)) return "";
        
        return name.Length >= 6 
            ? $"{name.Substring(0, 3)}************{name.Substring(name.Length - 3)}"
            : new string('*', name.Length);
    }
}

Full Implementation with Inline Lambda Expressions

public class DatabaseBackupExample
{
    public void ExportWithAdjustments()
    {
        string constr = "server=localhost;user=root;pwd=1234;database=test1;convertzerodatetime=true;";
        string filePath = @"C:\backup.sql";

        using (var conn = new MySqlConnection(constr))
        using (var cmd = conn.CreateCommand())
        using (var mb = new MySqlBackup(cmd))
        {
            conn.Open();
            
            // Add column adjustment rules using inline lambda expressions
            mb.ExportInfo.AddTableColumnValueAdjustment("users", "email", value => {
                if (value == null) return null;
                
                string email = value.ToString();
                if (string.IsNullOrEmpty(email) || !email.Contains("@")) 
                    return "***@***.***";
                
                var parts = email.Split('@');
                var maskedLocal = parts[0].Length >= 3 
                    ? parts[0].Substring(0, 3) + "*****" 
                    : "*****";
                
                return $"{maskedLocal}@{parts[1]}";
            });

            mb.ExportInfo.AddTableColumnValueAdjustment("users", "name", value => {
                if (value == null) return null;
                
                string name = value.ToString();
                if (string.IsNullOrEmpty(name)) return "";
                
                return name.Length >= 6 
                    ? $"{name.Substring(0, 3)}************{name.Substring(name.Length - 3)}"
                    : new string('*', name.Length);
            });
            
            mb.ExportToFile(filePath);
        }
    }
}

Key Points

Both implementations produce identical results. The choice between them depends on your coding style and project requirements:

  • External Method Declaration: Better for complex logic, reusability across multiple exports, and easier unit testing
  • Inline Lambda Expressions: More concise for simple transformations and when the logic is specific to a single export operation

The column value adjustments will be applied automatically during the export process, transforming the specified columns while leaving all other data unchanged.

Advanced Example: Multi-Column Adjustments

// Configure multiple adjustments for different tables and columns
var adjustments = new Dictionary<(string table, string column), Func<object, object>>
{
    { ("employees", "salary"), value => Math.Round(Convert.ToDecimal(value ?? 0), 2) },
    { ("products", "price"), value => Convert.ToDecimal(value ?? 0) * 1.1m }, // 10% increase
    { ("orders", "status"), value => MapStatusCode(Convert.ToInt32(value ?? 0)) },
    { ("customers", "phone"), value => MaskPhoneNumber(value?.ToString()) },
    { ("inventory", "quantity"), value => value ?? 0 } // Default null values
};

foreach (var adjustment in adjustments)
{
    backup.ExportInfo.AddTableColumnValueAdjustment(
        adjustment.Key.table, 
        adjustment.Key.column, 
        adjustment.Value
    );
}

Use Cases

1. Data Privacy Compliance

Transform sensitive data to comply with regulations such as GDPR, HIPAA, or internal security policies:

// Mask Social Security Numbers
backup.ExportInfo.AddTableColumnValueAdjustment("employees", "ssn", value => 
    value == null ? null : "XXX-XX-XXXX");

// Anonymize customer data
backup.ExportInfo.AddTableColumnValueAdjustment("customers", "customer_id", value => 
    $"ANON_{new Random().Next(100000, 999999)}");

2. Data Formatting & Standardization

Ensure consistent data formatting across exports:

// Standardize date formats
backup.ExportInfo.AddTableColumnValueAdjustment("orders", "order_date", value => 
    value is DateTime date ? date.ToString("yyyy-MM-dd") : value);

// Normalize phone numbers
backup.ExportInfo.AddTableColumnValueAdjustment("contacts", "phone", value => {
    if (value == null) return null;
    string phone = Regex.Replace(value.ToString(), @"[^\d]", "");
    return phone.Length == 10 ? $"({phone.Substring(0,3)}) {phone.Substring(3,3)}-{phone.Substring(6,4)}" : value;
});

3. Testing Data Generation

Create realistic test datasets while protecting sensitive information:

// Generate test-friendly user data
backup.ExportInfo.AddTableColumnValueAdjustment("users", "username", value => 
    $"testuser_{new Random().Next(1000, 9999)}");

backup.ExportInfo.AddTableColumnValueAdjustment("users", "email", value => 
    $"test.user{new Random().Next(100, 999)}@example.com");

4. Business Logic Application

Apply business rules or calculations during export:

// Apply discount calculations
backup.ExportInfo.AddTableColumnValueAdjustment("products", "price", value => {
    decimal price = Convert.ToDecimal(value ?? 0);
    return price > 1000 ? price * 0.9m : price; // 10% discount for high-value items
});

// Convert units
backup.ExportInfo.AddTableColumnValueAdjustment("measurements", "weight_kg", value => 
    Convert.ToDouble(value ?? 0) * 2.20462); // Convert kg to pounds

Performance Considerations

The Column Value Adjustment feature is optimized for production use:

  • Selective Processing: Adjustments are applied only to specified table-column combinations
  • Efficient Lookup: Uses dictionary-based lookups for O(1) performance
  • Memory Optimization: Processes data in streams to minimize memory usage
  • Parallel Processing: Supports parallel export processing when enabled

Performance Guidelines

For optimal performance when working with large datasets:

  1. Minimize Complex Logic: Keep adjustment functions simple and fast
  2. Use Efficient Data Types: Prefer primitive operations over complex string manipulations
  3. Cache External Lookups: Pre-compute lookup tables for reference data
  4. Enable Parallel Processing: Use ExportInfo.EnableParallelProcessing = true for large exports

Error Handling

The feature includes robust error handling mechanisms:

// Implement safe adjustment functions with error handling
backup.ExportInfo.AddTableColumnValueAdjustment("products", "price", value => {
    try {
        return Math.Round(Convert.ToDecimal(value ?? 0), 2);
    }
    catch (Exception) {
        return 0.00m; // Default fallback value
    }
});

Migration from Previous Versions

Version 2.3.9 to 2.6.0

The API has been simplified and enhanced:

Previous Implementation (v2.3.9):

backup.ExportInfo.AdjustColumnValue = (columnInfo) => {
    if (columnInfo.TableName == "users" && columnInfo.ColumnName == "email") {
        return MaskEmail(columnInfo.Value);
    }
    return columnInfo.Value;
};

New Implementation (v2.6.0):

backup.ExportInfo.AddTableColumnValueAdjustment("users", "email", MaskEmail);

Benefits of the New API

  • Simplified Configuration: Direct table-column mapping eliminates complex conditional logic
  • Better Performance: Optimized lookup mechanism reduces processing overhead
  • Improved Maintainability: Clear separation of concerns for different table-column combinations
  • Enhanced Readability: More intuitive and self-documenting code structure

Best Practices

  1. Document Transformations: Clearly document what each adjustment function does and why it's needed
  2. Test Thoroughly: Validate adjustment functions with representative data samples
  3. Handle Null Values: Always check for null values in adjustment functions
  4. Maintain Data Integrity: Ensure transformations don't break referential integrity
  5. Consider Reversibility: For some use cases, consider whether transformations can be reversed if needed

Conclusion

The Column Value Adjustment feature provides a powerful, flexible, and efficient way to transform data during MySQL database exports. By enabling real-time data processing without modifying source databases, it supports a wide range of use cases from data privacy compliance to testing data generation, making it an essential tool for modern database management workflows.

Clone this wiki locally