public void T07_Db2Db_SqlServer()
{
string tblName = $"T03_Db2Db_{Guid.NewGuid().ToString().Substring(0, 8)}";
var connstr = Environment.GetEnvironmentVariable("SQLCONNSTRING");
// skip if no db config found
Skip.If(string.IsNullOrWhiteSpace(connstr), "No database connection info found, is the environment variable 'SQLCONNSTRING' set?");
using (var utility = new Utility())
{
try
{
utility.PrepareTestInstanceDir();
utility.PrepareSqlServerDb(tblName, connstr);
// debug: get list of tables
/*
* var testCmd = utility.TestDbConnection.CreateCommand();
* testCmd.CommandText = "SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%'";
* var tbl = new DataTable();
* using (var rd = testCmd.ExecuteReader())
* {
* tbl.Load(rd);
* }
* string tablesStr = Utility.DumpDataTable(tbl);
* Console.Write($"========== List of tables: {tablesStr} ==========");
*/
var config = new MaskConfig()
{
DataSource = DataSource.Database,
DataDestination = DataDestination.Database,
SqlSelect = $"SELECT * FROM {utility.TestTableName}",
SqlCommand = $"UPDATE {utility.TestTableName} SET Name=@Name, Bankaccount=@Bankaccount, Address=@Address WHERE ID = @ID",
OutputFile = Path.Combine(utility.TestInstanceDir, "T01_Db2Csv.csv")
};
config.SetConnection(utility.TestDbConnection);
config.FieldMasks.Add(new FieldMask()
{
FieldName = "ID", MaskType = MaskType.None
});
config.FieldMasks.Add(new FieldMask()
{
FieldName = "Name", MaskType = MaskType.None
});
config.FieldMasks.Add(new FieldMask()
{
FieldName = "BankAccount", MaskType = MaskType.Asterisk
});
config.FieldMasks.Add(new FieldMask()
{
FieldName = "Address", MaskType = MaskType.List
});
config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement()
{
Selector = "country=CH", ReplacementList = "Bahnhofstrasse 41,Hauptstrasse 8,Berggasse 4"
});
config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement()
{
Selector = "country=GR", ReplacementList = "Evangelistrias 22,Thessalias 47,Eparhiaki Odos Lefkogion 6"
});
config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement()
{
Selector = "", ReplacementList = "Main Street 9,Fifth Avenue 104,Ranch rd. 1"
});
var masker = new DataMasking(config);
masker.Mask();
}
catch (System.Data.SqlClient.SqlException ex)
{
// error 40 - could not open connection to sql server
Skip.If(ex.Message.Contains("40"), $"Database seems not to respond, check if your SQL Server is running. {ex.Message}");
}
}
}
public void T08_Mask_MaskType_Query_Test()
{
// ===== Arrange
string csvContent = "ID;Name;City;Country\r\n1;Roche;Basel;CH\r\n2;ABB;Baden;CH\r\n3;BMW;München;DE\r\n4;Barilla;Parma;IT\r\n5;FAGE;Athens;GR";
string csvFilename = this.utility.CreateFileInTestInstanceDir(csvContent, "csv");
var config = new MaskConfig()
{
InputFile = csvFilename,
OutputFile = csvFilename.Replace(".csv", "_out.csv")
};
config.SetConnection(utility.TestDbConnection);
config.FieldMasks.Add(new FieldMask()
{
FieldName = "ID", MaskType = MaskType.None
});
config.FieldMasks.Add(new FieldMask()
{
FieldName = "Name", MaskType = MaskType.Asterisk
});
config.FieldMasks.Add(new FieldMask()
{
FieldName = "City", MaskType = MaskType.Query
});
config.FieldMasks.Add(new FieldMask()
{
FieldName = "Country", MaskType = MaskType.None
});
config.FieldMasks[2].QueryReplacement = new QueryReplacement()
{
Query = "SELECT city, country FROM cities",
GroupDbField = "country",
ValueDbField = "city",
SelectorField = "Country"
};
// prepare lookup table
var cmdTbl = utility.TestDbConnection.CreateCommand();
cmdTbl.CommandText = @"CREATE TABLE cities AS
SELECT 'Geneva' AS city, 'CH' as country UNION ALL
SELECT 'Bern' AS city, 'CH' as country UNION ALL
SELECT 'Thun' AS city, 'CH' as country UNION ALL
SELECT 'Köln' AS city, 'DE' as country UNION ALL
SELECT 'Berlin' AS city, 'DE' as country UNION ALL
SELECT 'Hamburg' AS city, 'DE' as country UNION ALL
SELECT 'Roma' AS city, 'IT' as country UNION ALL
SELECT 'Venezia' AS city, 'IT' as country UNION ALL
SELECT 'Milano' AS city, 'IT' as country UNION ALL
SELECT 'Rethimno' AS city, 'GR' as country UNION ALL
SELECT 'Trikala' AS city, 'GR' as country UNION ALL
SELECT 'Patra' AS city, 'GR' as country";
cmdTbl.ExecuteNonQuery();
utility.TestTablesToDrop = "cities";
// ===== Act
var masker = new DataMasking(config);
masker.Mask();
// ===== Assert
Assert.True(File.Exists(config.OutputFile));
var contents = new List <string>(File.ReadLines(config.OutputFile));
Assert.Equal(6, contents.Count);
for (int i = 1; i < contents.Count; i++)
{
var items = contents[i].Split(';');
string id = items[0];
string city = items[2];
string country = items[3];
// id
Assert.Equal(i.ToString(), id);
// city
switch (country)
{
case "CH":
Assert.Contains <string>(city, new string[] { "Geneva", "Bern", "Thun" });
break;
case "DE":
Assert.Contains <string>(city, new string[] { "Köln", "Berlin", "Hamburg" });
break;
case "IT":
Assert.Contains <string>(city, new string[] { "Roma", "Venezia", "Milano" });
break;
case "GR":
Assert.Contains <string>(city, new string[] { "Rethimno", "Trikala", "Patra" });
break;
default:
throw new NotSupportedException($"Unexpected country {country} found");
}
}
}