public static void ExecuteDataDependentRoutingQuery(RangeShardMap<int> shardMap, string credentialsConnectionString)
{
// 亂數產生一筆 Key
//亂數最高範圍
int currentMaxHighKey = shardMap.GetMappings().Max(m => m.Value.High);
//亂數取得 CustomerID
int customerId = GetCustomerId(currentMaxHighKey);
//亂數產生 Customer Name
string customerName = s_customerNames[s_r.Next(s_customerNames.Length)];
int regionId = 0;
int productId = 0;
AddCustomer(
shardMap,
credentialsConnectionString,
customerId,
customerName,
regionId);
AddOrder(
shardMap,
credentialsConnectionString,
customerId,
productId);
}
/// <summary>
/// ExecuteMultiShardQuery method to get the database records using MultiShardConnection,
/// MultiShardCommand,multiShardDataReader class
/// </summary>
/// <param name="shardMap"></param>
/// <param name="credentialsConnectionString"></param>
public static void ExecuteMultiShardQuery(RangeShardMap <int> shardMap)
{
// Get the Shards from Shard Map manager
MultiShardConfiguration.Shards = shardMap.GetShards();
TestParameterValue testParameterValue
= new TestParameterValue(
"MultiShard", "ExecMultiShard", "SelectAll_DR",
"SqlDbWithMultiShard" + "%"
+ "individual" + "%"
+ "-",
new MyUserInfo("MultiShard", "MultiShard"));
// 分離レベルの設定
DbEnum.IsolationLevelEnum iso = DbEnum.IsolationLevelEnum.NoTransaction;
// B層を生成
LayerB myBusiness = new LayerB();
// 業務処理を実行
TestReturnValue testReturnValue =
(TestReturnValue)myBusiness.DoBusinessLogic(
(BaseParameterValue)testParameterValue, iso);
string strErrorMsg = "";
if (testReturnValue.ErrorFlag == true)
{
// 結果（業務続行可能なエラー）
strErrorMsg = "ErrorMessageID:" + testReturnValue.ErrorMessageID + "\r\n";
strErrorMsg += "ErrorMessage:" + testReturnValue.ErrorMessage + "\r\n";
strErrorMsg += "ErrorInfo:" + testReturnValue.ErrorInfo + "\r\n";
Console.WriteLine("Inserted failed for Error message : {0}", strErrorMsg);
}
else
{
//Converts Return value object to dataTable data to display the data in screen
DataTable dtTable = (DataTable)testReturnValue.Obj;
int rows = 0;
// Get the column names
TableFormatter formatter = new TableFormatter(ShardManagementUtils.GetColumnNames(dtTable).ToArray());
foreach (DataRow dr in dtTable.Rows)
{
// Extract just database name from the $ShardLocation pseudocolumn to make the output formater cleaner.
// Note that the $ShardLocation pseudocolumn is always the last column
int shardLocationOrdinal = dr.ItemArray.Length - 1;
dr.ItemArray[shardLocationOrdinal] = ShardManagementUtils.ExtractDatabaseName(dr.ItemArray[shardLocationOrdinal].ToString());
// Add values to output formatter
formatter.AddRow(dr.ItemArray);
rows++;
}
Console.WriteLine(formatter.ToString());
Console.WriteLine("({0} rows returned)", rows);
}
}
public void LoadTestRangeMappingDDR()
{
try
{
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
RangeShardMap <int> rsm = smm.GetRangeShardMap <int>(ShardMapManagerLoadTests.s_rangeShardMapName);
Assert.IsNotNull(rsm);
RangeMapping <int> r1 = this.GetRandomRangeMapping(rsm);
if (r1 != null)
{
int keyToValidate = _r.Next((int)(r1.Range.Low.Value), (int)(r1.Range.High.Value));
Debug.WriteLine("Trying to validate mapping for key {0}", keyToValidate);
// Validate mapping by trying to connect
s_retryPolicy.ExecuteAction(
() => ValidateImpl(
(ShardMap)rsm,
keyToValidate));
}
}
catch (ShardManagementException sme)
{
Debug.WriteLine("Exception caught: {0}", sme.Message);
}
}
public void LoadTestSplitRangeNoLock()
{
try
{
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
RangeShardMap <int> rsm = smm.GetRangeShardMap <int>(ShardMapManagerLoadTests.s_rangeShardMapName);
Assert.IsNotNull(rsm);
RangeMapping <int> r1 = this.GetRandomRangeMapping(rsm, 2);
if (r1 != null)
{
int splitPoint = _r.Next((int)(r1.Range.Low.Value) + 1, (int)(r1.Range.High.Value) - 1);
Debug.WriteLine("Trying to split range mapping for key range ({0} - {1}) at {2}", r1.Range.Low.Value, r1.Range.High.Value, splitPoint);
IReadOnlyList <RangeMapping <int> > rList = rsm.SplitMapping(r1, splitPoint);
Assert.AreEqual(2, rList.Count);
}
}
catch (ShardManagementException sme)
{
Debug.WriteLine("Exception caught: {0}", sme.Message);
}
}
public void LoadTestMarkAllShardsAsOnlineInRangeShardMap()
{
try
{
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
RangeShardMap <int> rsm = smm.GetRangeShardMap <int>(ShardMapManagerLoadTests.s_rangeShardMapName);
Assert.IsNotNull(rsm);
foreach (Shard s in rsm.GetShards())
{
if (s.Status == ShardStatus.Offline)
{
rsm.UpdateShard(s,
new ShardUpdate
{
Status = ShardStatus.Online
});
}
}
}
catch (ShardManagementException sme)
{
Debug.WriteLine("Exception caught: {0}", sme.Message);
}
}
/// <summary>
/// 建立 new shard, 或取得現有空的 shard (i.e. a shard 可能還沒 mapper ).
/// 空的 shard 可能存在的原因是因為，創建和初始化的時候，我們無法 mapping 他
/// </summary>
private static Shard CreateOrGetEmptyShard(RangeShardMap<int> shardMap)
{
// 假如已經有了，則取得空的 shard
Shard shard = FindEmptyShard(shardMap);
if (shard == null)
{
// 為 Null，表示沒有空的，所以要建立
// 更改 shard name
string databaseName = string.Format(ShardNameFormat, shardMap.GetShards().Count());
// 假如資料庫不存在，則建立資料庫
// 假如存在，則會返回
if (!SqlDatabaseUtils.DatabaseExists(Configuration.ShardMapManagerServerName, databaseName))
{
SqlDatabaseUtils.CreateDatabase(Configuration.ShardMapManagerServerName, databaseName);
}
// 建立 schema 和相關的資料到 db
// 並使用初始化腳本初始化，如果資料庫已經存在，會發生問題
//
SqlDatabaseUtils.ExecuteSqlScript(
Configuration.ShardMapManagerServerName, databaseName, InitializeShardScriptFile);
// 產生shard Location ， 並取得 Shard
ShardLocation shardLocation = new ShardLocation(Configuration.ShardMapManagerServerName, databaseName);
shard = ShardManagementUtils.CreateOrGetShard(shardMap, shardLocation);
}
return shard;
}
/// <summary>
/// To Delete database records
/// </summary>
/// <param name="shardMap"></param>
/// <param name="credentialsConnectionString"></param>
public static void ExecuteDataDependentRoutingQueryForDelete(RangeShardMap <int> shardMap)
{
TestParameterValue testParameterValue
= new TestParameterValue(
"DataDependentRouting", "ExecuteDataDependentRouting", "Delete",
"SqlDbWithDataDependent" + "%"
+ "individual" + "%"
+ "-",
new MyUserInfo("DataDependentRouting", "DataDependentRouting"));
// 情報の設定
testParameterValue.CustomerId = MultiShardConfiguration.customerId;
// 分離レベルの設定
DbEnum.IsolationLevelEnum iso = DbEnum.IsolationLevelEnum.DefaultTransaction;
// B層を生成
LayerB myBusiness = new LayerB();
// 業務処理を実行
TestReturnValue testReturnValue =
(TestReturnValue)myBusiness.DoBusinessLogic(
(BaseParameterValue)testParameterValue, iso);
Console.WriteLine("Deleted order for customer ID: {0}", testParameterValue.CustomerId);
}
public SqlDataManager(
Logger logger,
GrainStateMap grainStateMap,
string connectionString,
string shardCredentials,
string mapName,
BatchingOptions batchingOptions = null)
{
Logger = logger;
Guard.NotNullOrEmpty(connectionString, "conectionString");
Guard.NotNullOrEmpty(shardCredentials, "shardCredentials");
Guard.NotNullOrEmpty(mapName, "mapName");
// Try to get a reference to the Shard Map Manager via the Shard Map Manager database.
// If it doesn't already exist, then fail
var shardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(connectionString, ShardMapManagerLoadPolicy.Lazy);
var shardMap = (RangeShardMap <int>)shardMapManager.GetShardMap(mapName);
var shardBatchers = new ConcurrentDictionary <Range <int>, ShardBatcher>();
foreach (var rangeMapping in shardMap.GetMappings())
{
Range <int> range = rangeMapping.Value;
shardBatchers.TryAdd(range, new ShardBatcher(logger, grainStateMap, rangeMapping.Shard, shardCredentials, batchingOptions));
}
_shardBatchers = shardBatchers;
_shardMap = shardMap;
}
public void LoadTestDeleteRangeMapping()
{
try
{
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
RangeShardMap <int> rsm = smm.GetRangeShardMap <int>(ShardMapManagerLoadTests.s_rangeShardMapName);
Assert.IsNotNull(rsm);
RangeMapping <int> r1 = this.GetRandomRangeMapping(rsm);
if (r1 != null)
{
Debug.WriteLine("Trying to delete mapping for range with low value = {0}", r1.Range.Low);
RangeMappingUpdate ru = new RangeMappingUpdate();
ru.Status = MappingStatus.Offline;
RangeMapping <int> mappingToDelete = rsm.UpdateMapping(r1, ru);
rsm.DeleteMapping(mappingToDelete);
}
}
catch (ShardManagementException sme)
{
Debug.WriteLine("Exception caught: {0}", sme.Message);
}
}
private RangeShardMap <TKey> GetRangeShardMap <TKey>(string mapName)
{
RangeShardMap <TKey> rangeShardMap = null;
ShardMapManager.TryGetRangeShardMap(mapName, out rangeShardMap);
return(rangeShardMap);
}
/// <summary>
/// Creates a new shard, or gets an existing empty shard (i.e. a shard that has no mappings).
/// The reason why an empty shard might exist is that it was created and initialized but we
/// failed to create a mapping to it.
/// </summary>
private static Shard CreateOrGetEmptyShard(RangeShardMap <int> shardMap)
{
// Get an empty shard if one already exists, otherwise create a new one
Shard shard = FindEmptyShard(shardMap);
if (shard == null)
{
// No empty shard exists, so create one
// Choose the shard name
string databaseName = string.Format(ShardNameFormat, shardMap.GetShards().Count());
// Only create the database if it doesn't already exist. It might already exist if
// we tried to create it previously but hit a transient fault.
if (!SqlDatabaseUtils.DatabaseExists(Configuration.ShardMapManagerServerName, databaseName))
{
SqlDatabaseUtils.CreateDatabase(Configuration.ShardMapManagerServerName, databaseName);
}
// Create schema and populate reference data on that database
// The initialize script must be idempotent, in case it was already run on this database
// and we failed to add it to the shard map previously
SqlDatabaseUtils.ExecuteSqlScript(
Configuration.ShardMapManagerServerName, databaseName, InitializeShardScriptFile);
// Add it to the shard map
ShardLocation shardLocation = new ShardLocation(Configuration.ShardMapManagerServerName, databaseName);
shard = ShardManagementUtils.CreateOrGetShard(shardMap, shardLocation);
}
return(shard);
}
public SqlDataManager(
Logger logger,
GrainStateMap grainStateMap,
string connectionString,
string shardCredentials,
string mapName,
BatchingOptions batchingOptions =null)
{
Logger = logger;
Guard.NotNullOrEmpty(connectionString, "conectionString");
Guard.NotNullOrEmpty(shardCredentials, "shardCredentials");
Guard.NotNullOrEmpty(mapName, "mapName");
// Try to get a reference to the Shard Map Manager via the Shard Map Manager database.
// If it doesn't already exist, then fail
var shardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(connectionString, ShardMapManagerLoadPolicy.Lazy);
var shardMap = (RangeShardMap<int>)shardMapManager.GetShardMap(mapName);
var shardBatchers = new ConcurrentDictionary<Range<int>, ShardBatcher>();
foreach (var rangeMapping in shardMap.GetMappings())
{
Range<int> range = rangeMapping.Value;
shardBatchers.TryAdd(range, new ShardBatcher(logger, grainStateMap, rangeMapping.Shard, shardCredentials, batchingOptions));
}
_shardBatchers = shardBatchers;
_shardMap = shardMap;
}
private static Shard CreateOrGetEmptyShard(RangeShardMap <int> shardMap)
{
// Get an empty shard if one already exists, otherwise create a new one
Shard shard = FindEmptyShard(shardMap);
if (shard == null)
{
// No empty shard exists, so create one
// Choose the shard name
string databaseName = string.Format(ConfigHelper.DatabaseName, $"VoteShard_{shardMap.GetShards().Count():00}");
// Only create the database if it doesn't already exist. It might already exist if
// we tried to create it previously but hit a transient fault.
if (!SqlHelper.DatabaseExists(ConfigHelper.DataSource, databaseName))
{
SqlHelper.CreateDatabase(ConfigHelper.DataSource, databaseName);
}
// Add it to the shard map
ShardLocation shardLocation = new ShardLocation(ConfigHelper.DataSource, databaseName);
shard = CreateOrGetShard(shardMap, shardLocation);
}
return(shard);
}
/// <summary>
/// Creates a shard map manager, creates a shard map, and creates a shard
/// with a mapping for the full range of 32-bit integers.
/// </summary>
private static void CreateShardMapManagerAndShard()
{
if (MultiShardConfiguration.objShardMapManager != null)
{
ConsoleUtils.WriteWarning("Shard Map Manager already exists");
return;
}
// Create shard map manager database
if (!SqlDatabaseUtils.ExistsDatabase(MultiShardConfiguration.ShardMapManagerServerName, MultiShardConfiguration.ShardMapManagerDatabaseName))
{
SqlDatabaseUtils.CreateDatabase(MultiShardConfiguration.ShardMapManagerServerName, MultiShardConfiguration.ShardMapManagerDatabaseName);
}
// Create shard map manager
string shardMapManagerConnectionString = MultiShardConfiguration.GetConnectionString();
MultiShardConfiguration.objShardMapManager = ShardManagementUtils.CreateOrGetShardMapManager(shardMapManagerConnectionString);
// Create shard map
RangeShardMap <int> shardMap = ShardManagementUtils.CreateOrGetRangeShardMap <int>(
MultiShardConfiguration.objShardMapManager, MultiShardConfiguration.ShardMapName);
// Create schema info so that the split-merge service can be used to move data in sharded tables
// and reference tables.
CreateSchemaInfo(shardMap.Name);
// If there are no shards, add two shards: one for [0,100) and one for [100,+inf)
if (!shardMap.GetShards().Any())
{
CreateShardSample.CreateShard(shardMap, new Range <int>(0, 100));
CreateShardSample.CreateShard(shardMap, new Range <int>(100, 200));
}
}
public void CreateRangeShardMapDefault()
{
CountingCacheStore cacheStore =
new CountingCacheStore(
new CacheStore());
ShardMapManager smm = new ShardMapManager(
new SqlShardMapManagerCredentials(Globals.ShardMapManagerConnectionString),
new SqlStoreConnectionFactory(),
new StoreOperationFactory(),
cacheStore,
ShardMapManagerLoadPolicy.Lazy,
RetryPolicy.DefaultRetryPolicy,
RetryBehavior.DefaultRetryBehavior);
RangeShardMap <int> rsm = smm.CreateRangeShardMap <int>(ShardMapManagerTests.s_shardMapName);
Assert.IsNotNull(rsm);
Assert.AreEqual(ShardMapManagerTests.s_shardMapName, rsm.Name);
ShardMap smLookup = smm.LookupShardMapByName("LookupShardMapByName", ShardMapManagerTests.s_shardMapName, true);
Assert.IsNotNull(smLookup);
Assert.AreEqual(ShardMapManagerTests.s_shardMapName, smLookup.Name);
Assert.AreEqual(1, cacheStore.LookupShardMapCount);
Assert.AreEqual(1, cacheStore.LookupShardMapHitCount);
}
public void CreateRangeShardMapDuplicate()
{
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
ShardMap sm = smm.CreateRangeShardMap <int>(ShardMapManagerTests.s_shardMapName);
Assert.IsNotNull(sm);
Assert.AreEqual(ShardMapManagerTests.s_shardMapName, sm.Name);
bool creationFailed = false;
try
{
RangeShardMap <int> rsm = smm.CreateRangeShardMap <int>(ShardMapManagerTests.s_shardMapName);
}
catch (ShardManagementException sme)
{
Assert.AreEqual(ShardManagementErrorCategory.ShardMapManager, sme.ErrorCategory);
Assert.AreEqual(ShardManagementErrorCode.ShardMapAlreadyExists, sme.ErrorCode);
creationFailed = true;
}
Assert.IsTrue(creationFailed);
}
/// <summary>
/// Creates a new shard, or gets an existing empty shard (i.e. a shard that has no mappings).
/// The reason why an empty shard might exist is that it was created and initialized but we
/// failed to create a mapping to it.
/// </summary>
private static Shard CreateOrGetEmptyShard(RangeShardMap<int> shardMap)
{
// Get an empty shard if one already exists, otherwise create a new one
Shard shard = FindEmptyShard(shardMap);
if (shard == null)
{
// No empty shard exists, so create one
// Choose the shard name
string databaseName = string.Format(ShardNameFormat, shardMap.GetShards().Count());
// Only create the database if it doesn't already exist. It might already exist if
// we tried to create it previously but hit a transient fault.
if (!SqlDatabaseUtils.DatabaseExists(Configuration.ShardMapManagerServerName, databaseName))
{
SqlDatabaseUtils.CreateDatabase(Configuration.ShardMapManagerServerName, databaseName);
}
// Create schema and populate reference data on that database
// The initialize script must be idempotent, in case it was already run on this database
// and we failed to add it to the shard map previously
SqlDatabaseUtils.ExecuteSqlScript(
Configuration.ShardMapManagerServerName, databaseName, InitializeShardScriptFile);
// Add it to the shard map
ShardLocation shardLocation = new ShardLocation(Configuration.ShardMapManagerServerName, databaseName);
shard = ShardManagementUtils.CreateOrGetShard(shardMap, shardLocation);
}
return shard;
}
public void CreateRangeShardMapAbortGSM()
{
ShardMapManager smm = new ShardMapManager(
new SqlShardMapManagerCredentials(Globals.ShardMapManagerConnectionString),
new SqlStoreConnectionFactory(),
new StubStoreOperationFactory()
{
CallBase = true,
CreateAddShardMapGlobalOperationShardMapManagerStringIStoreShardMap =
(_smm, _opname, _ssm) => new NTimeFailingAddShardMapGlobalOperation(10, _smm, _opname, _ssm)
},
new CacheStore(),
ShardMapManagerLoadPolicy.Lazy,
new RetryPolicy(1, TimeSpan.Zero, TimeSpan.Zero, TimeSpan.Zero), RetryBehavior.DefaultRetryBehavior);
bool storeOperationFailed = false;
try
{
RangeShardMap <int> rsm = smm.CreateRangeShardMap <int>(ShardMapManagerTests.s_shardMapName);
Assert.IsNotNull(rsm);
Assert.AreEqual(ShardMapManagerTests.s_shardMapName, rsm.Name);
}
catch (ShardManagementException sme)
{
Assert.AreEqual(ShardManagementErrorCategory.ShardMapManager, sme.ErrorCategory);
Assert.AreEqual(ShardManagementErrorCode.StorageOperationFailure, sme.ErrorCode);
storeOperationFailed = true;
}
Assert.IsTrue(storeOperationFailed);
}
public static void ShardMapperTestsInitialize(TestContext testContext)
{
// Clear all connection pools.
SqlConnection.ClearAllPools();
using (SqlConnection conn = new SqlConnection(Globals.ShardMapManagerTestConnectionString))
{
conn.Open();
// Create ShardMapManager database
using (SqlCommand cmd = new SqlCommand(
string.Format(Globals.CreateDatabaseQuery, Globals.ShardMapManagerDatabaseName),
conn))
{
cmd.ExecuteNonQuery();
}
// Create shard databases
for (int i = 0; i < DateTimeShardMapperTests.s_shardedDBs.Length; i++)
{
using (SqlCommand cmd = new SqlCommand(
string.Format(Globals.DropDatabaseQuery, DateTimeShardMapperTests.s_shardedDBs[i]),
conn))
{
cmd.ExecuteNonQuery();
}
using (SqlCommand cmd = new SqlCommand(
string.Format(Globals.CreateDatabaseQuery, DateTimeShardMapperTests.s_shardedDBs[i]),
conn))
{
cmd.ExecuteNonQuery();
}
}
}
// Create shard map manager.
ShardMapManagerFactory.CreateSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerCreateMode.ReplaceExisting);
// Create list shard map.
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
ListShardMap <DateTime> lsm = smm.CreateListShardMap <DateTime>(DateTimeShardMapperTests.s_listShardMapName);
Assert.IsNotNull(lsm);
Assert.AreEqual(DateTimeShardMapperTests.s_listShardMapName, lsm.Name);
// Create range shard map.
RangeShardMap <DateTime> rsm = smm.CreateRangeShardMap <DateTime>(DateTimeShardMapperTests.s_rangeShardMapName);
Assert.IsNotNull(rsm);
Assert.AreEqual(DateTimeShardMapperTests.s_rangeShardMapName, rsm.Name);
}
public void LoadTestRemoveShardFromRangeShardMap()
{
try
{
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
RangeShardMap <int> rsm = smm.GetRangeShardMap <int>(ShardMapManagerLoadTests.s_rangeShardMapName);
Assert.IsNotNull(rsm);
List <Shard> existingShards = rsm.GetShards().ToList();
if (existingShards.Count == 0)
{
return;
}
// If there is already a shard marked as offline, chose that one to delete.
// This can happend if earlier remove operation was terminated for some reason - ex. killing connections.
Shard offlineShard = existingShards.Find(e => e.Status == ShardStatus.Offline);
if (offlineShard == null)
{
offlineShard = existingShards[_r.Next(existingShards.Count)];
// First mark shard as offline so that other test threads will not add new mappings to it.
offlineShard = rsm.UpdateShard(offlineShard,
new ShardUpdate
{
Status = ShardStatus.Offline
});
}
Debug.WriteLine("Trying to remove shard at location {0}", offlineShard.Location);
RangeMappingUpdate ru = new RangeMappingUpdate();
ru.Status = MappingStatus.Offline;
// Remove all mappings from this shard for given shard map.
foreach (RangeMapping <int> rm in rsm.GetMappings(offlineShard))
{
RangeMapping <int> mappingToDelete = rsm.UpdateMapping(rm, ru);
rsm.DeleteMapping(mappingToDelete);
}
// get shard object again.
Shard deleteShard = rsm.GetShard(offlineShard.Location);
// now remove shard.
rsm.DeleteShard(deleteShard);
Debug.WriteLine("Removed shard at location {0} from shard map {1}", deleteShard.Location, rsm);
}
catch (ShardManagementException sme)
{
Debug.WriteLine("Exception caught: {0}", sme.Message);
}
}
private static void CreateShard(RangeShardMap <int> shardMap, Range <int> rangeForNewShard)
{
// Create a new shard, or get an existing empty shard (if a previous create partially succeeded).
Shard shard = CreateOrGetEmptyShard(shardMap);
// Create a mapping to that shard.
RangeMapping <int> mappingForNewShard = shardMap.CreateRangeMapping(rangeForNewShard, shard);
}
/// <summary>
/// Creates a new shard (or uses an existing empty shard), adds it to the shard map,
/// and assigns it the specified range if possible.
/// </summary>
public static void CreateShard(RangeShardMap<int> shardMap, Range<int> rangeForNewShard)
{
// Create a new shard, or get an existing empty shard (if a previous create partially succeeded).
Shard shard = CreateOrGetEmptyShard(shardMap);
// Create a mapping to that shard.
RangeMapping<int> mappingForNewShard = shardMap.CreateRangeMapping(rangeForNewShard, shard);
ConsoleUtils.WriteInfo("Mapped range {0} to shard {1}", mappingForNewShard.Value, shard.Location.Database);
}
/// <summary>
/// 建立，或使用現有的 shard , 並加入到 shard map,
/// 並且分配指定位置
/// </summary>
public static void CreateShard(RangeShardMap<int> shardMap, Range<int> rangeForNewShard)
{
// 建立 new shard, or 取得現有的 shard
Shard shard = CreateOrGetEmptyShard(shardMap);
// 針對 shard 建立 mapping
RangeMapping<int> mappingForNewShard = shardMap.CreateRangeMapping(rangeForNewShard, shard);
ConsoleUtils.WriteInfo("Mapped range {0} to shard {1}", mappingForNewShard.Value, shard.Location.Database);
}
/// <summary>
/// Executes the Multi-Shard Query sample.
/// </summary>
private static void MultiShardQuery()
{
RangeShardMap <int> shardMap = MultiShardConfiguration.TryGetShardMap();
if (shardMap != null)
{
MultiShardQuerySupport.ExecuteMultiShardQuery(shardMap);
}
}
/// <summary>
/// Executes the Data-Dependent Routing sample.
/// </summary>
private static void DataDepdendentRoutingForDelete()
{
RangeShardMap <int> shardMap = MultiShardConfiguration.TryGetShardMap();
if (shardMap != null)
{
DataDependentRoutingSupport.ExecuteDataDependentRoutingQueryForDelete(shardMap);
}
}
public static bool RangeShardMapExists <TKey>(string connectionString, string mapName)
{
var shardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(
connectionString,
ShardMapManagerLoadPolicy.Lazy);
RangeShardMap <TKey> _ = null;
return(shardMapManager.TryGetRangeShardMap(mapName, out _));
}
/// <summary>
/// Creates a new shard (or uses an existing empty shard), adds it to the shard map,
/// and assigns it the specified range if possible.
/// </summary>
public static void CreateShard(RangeShardMap <int> shardMap, Range <int> rangeForNewShard)
{
// Create a new shard, or get an existing empty shard (if a previous create partially succeeded).
Shard shard = CreateOrGetEmptyShard(shardMap);
// Create a mapping to that shard.
RangeMapping <int> mappingForNewShard = shardMap.CreateRangeMapping(rangeForNewShard, shard);
ConsoleUtils.WriteInfo("Mapped range {0} to shard {1}", mappingForNewShard.Value, shard.Location.Database);
}
/// <summary>
/// Executes the Multi-Shard Query sample.
/// </summary>
private static void MultiShardQuery()
{
RangeShardMap <int> shardMap = TryGetShardMap();
if (shardMap != null)
{
MultiShardQuerySample.ExecuteMultiShardQuery(
shardMap,
Configuration.GetCredentialsConnectionString());
}
}
/// <summary>
/// Executes the Data-Dependent Routing sample.
/// </summary>
private static void DataDepdendentRouting()
{
RangeShardMap <int> shardMap = TryGetShardMap();
if (shardMap != null)
{
DataDependentRoutingSample.ExecuteDataDependentRoutingQuery(
shardMap,
Configuration.GetCredentialsConnectionString());
}
}
/// <summary>
/// To create database records
/// </summary>
/// <param name="shardMap"></param>
/// <param name="credentialsConnectionString"></param>
public static void ExecuteDataDependentRoutingQuery(RangeShardMap <int> shardMap)
{
// we just choose a random key out of the range that is mapped. Here we assume that the ranges
// start at 0, are contiguous, and are bounded (i.e. there is no range where HighIsMax == true)
int currentMaxHighKey = shardMap.GetMappings().Max(m => m.Value.High);
MultiShardConfiguration.customerId = GetCustomerId(currentMaxHighKey);
string customerName = shardCustomerNames[randCustomer.Next(shardCustomerNames.Length)];
int regionId = 0;
int productId = 0;
TestParameterValue testParameterValue
= new TestParameterValue(
"DataDependentRouting", "ExecuteDataDependentRouting", "Insert",
"SqlDbWithDataDependent" + "%"
+ "individual" + "%"
+ "-",
new MyUserInfo("DataDependentRouting", "DataDependentRouting"));
// 情報の設定
testParameterValue.CustomerId = MultiShardConfiguration.customerId;
testParameterValue.Name = customerName;
testParameterValue.RegionId = regionId;
testParameterValue.ProductId = productId;
// 分離レベルの設定
DbEnum.IsolationLevelEnum iso = DbEnum.IsolationLevelEnum.DefaultTransaction;
// B層を生成
LayerB myBusiness = new LayerB();
// 業務処理を実行
TestReturnValue testReturnValue =
(TestReturnValue)myBusiness.DoBusinessLogic(
(BaseParameterValue)testParameterValue, iso);
string strErrorMsg = "";
if (testReturnValue.ErrorFlag == true)
{
// 結果（業務続行可能なエラー）
strErrorMsg = "ErrorMessageID:" + testReturnValue.ErrorMessageID + "\r\n";
strErrorMsg += "ErrorMessage:" + testReturnValue.ErrorMessage + "\r\n";
strErrorMsg += "ErrorInfo:" + testReturnValue.ErrorInfo + "\r\n";
Console.WriteLine("Inserted failed for customer ID: {0},{1}", testParameterValue.CustomerId, strErrorMsg);
}
else
{
// 結果（正常系）
Console.WriteLine("Inserted order for customer ID: {0}", testParameterValue.CustomerId);
}
}
public void LoadTestMergeRangesWithLock()
{
try
{
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
RangeShardMap <int> rsm = smm.GetRangeShardMap <int>(ShardMapManagerLoadTests.s_rangeShardMapName);
Assert.IsNotNull(rsm);
IEnumerable <RangeMapping <int> > existingMappings = rsm.GetMappings(new Range <int>(MinMappingPoint, MaxMappingPoint));
IQueryable <RangeMapping <int> > qr = Queryable.AsQueryable(existingMappings);
// Find pair of adjacent mappings.
var test = from a in qr
join b in qr on
new { a.Range.High.Value, a.StoreMapping.StoreShard.Id, a.StoreMapping.Status } equals
new { b.Range.Low.Value, b.StoreMapping.StoreShard.Id, b.StoreMapping.Status }
select new { a, b };
if (test.Count() > 0)
{
var t = test.First();
Debug.WriteLine("Trying to merge range mapping for key range ({0} - {1}) and ({2} - {3})", t.a.Range.Low.Value, t.a.Range.High.Value, t.b.Range.Low.Value, t.b.Range.High.Value);
MappingLockToken mappingLockTokenLeft = MappingLockToken.Create();
rsm.LockMapping(t.a, mappingLockTokenLeft);
MappingLockToken mappingLockTokenRight = MappingLockToken.Create();
rsm.LockMapping(t.b, mappingLockTokenLeft);
RangeMapping <int> rMerged = rsm.MergeMappings(t.a, t.b, mappingLockTokenLeft, mappingLockTokenRight);
Assert.IsNotNull(rMerged);
MappingLockToken storeMappingLockToken = rsm.GetMappingLockOwner(rMerged);
Assert.AreEqual(storeMappingLockToken, mappingLockTokenLeft, "Expected merged mapping lock id to equal left mapping id!");
rsm.UnlockMapping(rMerged, storeMappingLockToken);
storeMappingLockToken = rsm.GetMappingLockOwner(rMerged);
Assert.AreEqual(storeMappingLockToken, MappingLockToken.NoLock, "Expected merged mapping lock id to equal default mapping id after unlock!");
}
}
catch (ShardManagementException sme)
{
Debug.WriteLine("Exception caught: {0}", sme.Message);
}
}
/// <summary>
/// Finds an existing empty shard, or returns null if none exist.
/// </summary>
private static Shard FindEmptyShard(RangeShardMap<int> shardMap)
{
// Get all shards in the shard map
IEnumerable<Shard> allShards = shardMap.GetShards();
// Get all mappings in the shard map
IEnumerable<RangeMapping<int>> allMappings = shardMap.GetMappings();
// Determine which shards have mappings
HashSet<Shard> shardsWithMappings = new HashSet<Shard>(allMappings.Select(m => m.Shard));
// Get the first shard (ordered by name) that has no mappings, if it exists
return allShards.OrderBy(s => s.Location.Database).FirstOrDefault(s => !shardsWithMappings.Contains(s));
}
private static Shard FindEmptyShard(RangeShardMap <int> shardMap)
{
// Get all shards in the shard map
IEnumerable <Shard> allShards = shardMap.GetShards();
// Get all mappings in the shard map
IEnumerable <RangeMapping <int> > allMappings = shardMap.GetMappings();
// Determine which shards have mappings
HashSet <Shard> shardsWithMappings = new HashSet <Shard>(allMappings.Select(m => m.Shard));
// Get the first shard (ordered by name) that has no mappings, if it exists
return(allShards.OrderBy(s => s.Location.Database).FirstOrDefault(s => !shardsWithMappings.Contains(s)));
}
public void LoadTestAddRangeMapping()
{
try
{
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
RangeShardMap <int> rsm = smm.GetRangeShardMap <int>(ShardMapManagerLoadTests.s_rangeShardMapName);
Assert.IsNotNull(rsm);
do
{
// Chose a random shard to add mapping.
Shard s = GetRandomOnlineShardFromShardMap((ShardMap)rsm);
if (s == null)
{
continue;
}
// generate a random range to add a new range mapping and verify that its not already mapped.
int minKey = _r.Next(MinMappingPoint, MaxMappingPoint);
int maxKey = minKey + _r.Next(1, MaxRangeMappingSize);
maxKey = (maxKey <= MaxMappingPoint) ? maxKey : MaxMappingPoint;
IReadOnlyList <RangeMapping <int> > existingMapping = rsm.GetMappings(new Range <int>(minKey, maxKey));
if (existingMapping.Count > 0)
{
continue;
}
Debug.WriteLine("Trying to add range mapping for key range ({0} - {1}) to shard location {2}", minKey, maxKey, s.Location);
RangeMapping <int> r1 = rsm.CreateRangeMapping(new Range <int>(minKey, maxKey), s);
Assert.IsNotNull(r1);
// Validate mapping by trying to connect
s_retryPolicy.ExecuteAction(
() => ValidateImpl(
(ShardMap)rsm,
minKey));
}while (false);
}
catch (ShardManagementException sme)
{
Debug.WriteLine("Exception caught: {0}", sme.Message);
}
}
public static RangeShardMap <int> CreateOrGetRangeShardMap()
{
string shardMapName = "CaptainShardMap";
string shardDbName = string.Format(ConfigHelper.DatabaseName, "ShardMap");
if (!SqlHelper.DatabaseExists(ConfigHelper.DataSource, shardDbName))
{
SqlHelper.CreateDatabase(ConfigHelper.DataSource, shardDbName);
}
var shardMapManager = CreateOrGetShardMapManager(SqlHelper.GetConnectionString(ConfigHelper.DataSource, shardDbName));
RangeShardMap <int> shardMap = CreateOrGetRangeShardMap(shardMapManager, shardMapName);
return(shardMap);
}
/// <summary>
/// Get existing range mapping from a range shard map
/// </summary>
/// <param name="lsm">Range shard map</param>
/// <param name="minimumRangeSize">Minimum size of range mapping, this can be used for AddRangeWithinRange and RemoveRangeFromRange tests</param>
/// <returns>Valid existing range mapping, null if no range mappings found in given shard map</returns>
private RangeMapping <int> GetRandomRangeMapping(RangeShardMap <int> rsm, int minimumRangeSize = 1)
{
IReadOnlyList <RangeMapping <int> > allMappings = rsm.GetMappings();
List <RangeMapping <int> > filteredList = new List <RangeMapping <int> >(
from m in allMappings
where ((int)m.Range.High.Value - (int)m.Range.Low.Value) >= minimumRangeSize
select m);
if (filteredList.Count == 0)
{
return(null);
}
return(filteredList[_r.Next(filteredList.Count)]);
}
public void LoadTestSplitRangeWithLock()
{
try
{
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(
Globals.ShardMapManagerConnectionString,
ShardMapManagerLoadPolicy.Lazy);
RangeShardMap <int> rsm = smm.GetRangeShardMap <int>(ShardMapManagerLoadTests.s_rangeShardMapName);
Assert.IsNotNull(rsm);
RangeMapping <int> r1 = this.GetRandomRangeMapping(rsm, 2);
if (r1 != null)
{
int splitPoint = _r.Next((int)(r1.Range.Low.Value) + 1, (int)(r1.Range.High.Value) - 1);
Debug.WriteLine("Trying to split range mapping for key range ({0} - {1}) at {2}", r1.Range.Low.Value, r1.Range.High.Value, splitPoint);
// Lock the mapping
MappingLockToken mappingLockToken = MappingLockToken.Create();
rsm.LockMapping(r1, mappingLockToken);
IReadOnlyList <RangeMapping <int> > rList = rsm.SplitMapping(r1, splitPoint, mappingLockToken);
Assert.AreEqual(2, rList.Count);
foreach (RangeMapping <int> r2 in rList)
{
Assert.IsNotNull(r2);
Assert.AreEqual(mappingLockToken, rsm.GetMappingLockOwner(r2),
String.Format("LockOwnerId of mapping: {0} does not match id in store!", r2));
// Unlock each mapping and verify
rsm.UnlockMapping(r2, mappingLockToken);
Assert.AreEqual(MappingLockToken.NoLock, rsm.GetMappingLockOwner(r2),
String.Format("Mapping: {0} not unlocked as expected!", r2));
}
}
}
catch (ShardManagementException sme)
{
Debug.WriteLine("Exception caught: {0}", sme.Message);
}
}
public static void ExecuteDataDependentRoutingQuery(RangeShardMap<int> shardMap, string credentialsConnectionString)
{
// A real application handling a request would need to determine the request's customer ID before connecting to the database.
// Since this is a demo app, we just choose a random key out of the range that is mapped. Here we assume that the ranges
// start at 0, are contiguous, and are bounded (i.e. there is no range where HighIsMax == true)
int currentMaxHighKey = shardMap.GetMappings().Max(m => m.Value.High);
int customerId = GetCustomerId(currentMaxHighKey);
string customerName = s_customerNames[s_r.Next(s_customerNames.Length)];
int regionId = 0;
int productId = 0;
AddCustomer(
shardMap,
credentialsConnectionString,
customerId,
customerName,
regionId);
AddOrder(
shardMap,
credentialsConnectionString,
customerId,
productId);
}
/// <summary>
/// Get existing range mapping from a range shard map
/// </summary>
/// <param name="lsm">Range shard map</param>
/// <param name="minimumRangeSize">Minimum size of range mapping, this can be used for AddRangeWithinRange and RemoveRangeFromRange tests</param>
/// <returns>Valid existing range mapping, null if no range mappings found in given shard map</returns>
private RangeMapping<int> GetRandomRangeMapping(RangeShardMap<int> rsm, int minimumRangeSize = 1)
{
IReadOnlyList<RangeMapping<int>> allMappings = rsm.GetMappings();
List<RangeMapping<int>> filteredList = new List<RangeMapping<int>>(
from m in allMappings
where ((int)m.Range.High.Value - (int)m.Range.Low.Value) >= minimumRangeSize
select m);
if (filteredList.Count == 0)
return null;
return filteredList[_r.Next(filteredList.Count)];
}
/// <summary>
/// 尋找現有為空的 Shard ，假如沒有回傳 null
/// </summary>
private static Shard FindEmptyShard(RangeShardMap<int> shardMap)
{
// 從 shard map 取得所有 Shard
IEnumerable<Shard> allShards = shardMap.GetShards();
// 從 shard map 取得所有 mappings
IEnumerable<RangeMapping<int>> allMappings = shardMap.GetMappings();
// 確認 shards 是否有 mappings
HashSet<Shard> shardsWithMappings = new HashSet<Shard>(allMappings.Select(m => m.Shard));
// 取得第一個 shard ( 依據名稱排序 )
return allShards.OrderBy(s => s.Location.Database).FirstOrDefault(s => !shardsWithMappings.Contains(s));
}
public static void ExecuteMultiShardQuery(RangeShardMap<int> shardMap, string credentialsConnectionString)
{
// Get the shards to connect to
IEnumerable<Shard> shards = shardMap.GetShards();
// Create the multi-shard connection
using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString))
{
// Create a simple command
using (MultiShardCommand cmd = conn.CreateCommand())
{
// Because this query is grouped by CustomerID, which is sharded,
// we will not get duplicate rows.
cmd.CommandText = @"
SELECT
c.CustomerId,
c.Name AS CustomerName,
COUNT(o.OrderID) AS OrderCount
FROM
dbo.Customers AS c INNER JOIN
dbo.Orders AS o
ON c.CustomerID = o.CustomerID
GROUP BY
c.CustomerId,
c.Name
ORDER BY
OrderCount";
// Append a column with the shard name where the row came from
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
// Allow for partial results in case some shards do not respond in time
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
// Allow the entire command to take up to 30 seconds
cmd.CommandTimeout = 30;
// Execute the command.
// We do not need to specify retry logic because MultiShardDataReader will internally retry until the CommandTimeout expires.
using (MultiShardDataReader reader = cmd.ExecuteReader())
{
// Get the column names
TableFormatter formatter = new TableFormatter(GetColumnNames(reader).ToArray());
int rows = 0;
while (reader.Read())
{
// Read the values using standard DbDataReader methods
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
// Extract just database name from the $ShardLocation pseudocolumn to make the output formater cleaner.
// Note that the $ShardLocation pseudocolumn is always the last column
int shardLocationOrdinal = values.Length - 1;
values[shardLocationOrdinal] = ExtractDatabaseName(values[shardLocationOrdinal].ToString());
// Add values to output formatter
formatter.AddRow(values);
rows++;
}
Console.WriteLine(formatter.ToString());
Console.WriteLine("({0} rows returned)", rows);
}
}
}
}
