public MultiShardAggregateException TestSelectFailure(string commandText, MultiShardExecutionPolicy policy)
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
cmd.ExecutionPolicy = policy;
// ExecuteReader should fail
MultiShardAggregateException aggregateException =
AssertExtensions.AssertThrows <MultiShardAggregateException>(() => cmd.ExecuteReader());
// Sanity check the exceptions are the correct type
foreach (Exception e in aggregateException.InnerExceptions)
{
Assert.IsInstanceOfType(e, typeof(MultiShardException));
Assert.IsInstanceOfType(e.InnerException, typeof(SqlException));
}
// Return the exception so that the caller can do additional validation
return(aggregateException);
}
}
}
public void TestSelectNoRows(string commandText, MultiShardExecutionPolicy policy)
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
cmd.ExecutionPolicy = policy;
// Read first
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
Assert.AreEqual(0, sdr.MultiShardExceptions.Count);
while (sdr.Read())
{
Assert.Fail("Should not have gotten any records.");
}
Assert.IsFalse(sdr.HasRows);
}
// HasRows first
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
Assert.AreEqual(0, sdr.MultiShardExceptions.Count);
Assert.IsFalse(sdr.HasRows);
while (sdr.Read())
{
Assert.Fail("Should not have gotten any records.");
}
}
}
}
}
public void TestQueryShardsInvalidConnectionSync()
{
var badShard = new ShardLocation("badLocation", "badDatabase");
var bldr = new SqlConnectionStringBuilder();
bldr.DataSource = badShard.DataSource;
bldr.InitialCatalog = badShard.Database;
var badConn = new SqlConnection(bldr.ConnectionString);
try
{
using (var conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
conn.GetShardConnections().Add(new Tuple <ShardLocation, DbConnection>(badShard,
badConn));
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select 1";
cmd.ExecuteReader();
}
}
}
catch (Exception ex)
{
if (ex is MultiShardAggregateException)
{
var maex = (MultiShardAggregateException)ex;
Logger.Log("Exception encountered: " + maex.ToString());
throw ((MultiShardException)(maex.InnerException)).InnerException;
}
throw;
}
}
public void TestQueryShardsAsync()
{
// Create new sharded connection so we can test the OpenAsync call as well.
//
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
using (MultiShardDataReader sdr = ExecAsync(conn, cmd).Result)
{
int recordsRetrieved = 0;
while (sdr.Read())
{
recordsRetrieved++;
var dbNameField = sdr.GetString(0);
var testIntField = sdr.GetFieldValue <int>(1);
var testBigIntField = sdr.GetFieldValue <Int64>(2);
Logger.Log("RecordRetrieved: dbNameField: {0}, TestIntField: {1}, TestBigIntField: {2}, RecordCount: {3}",
dbNameField, testIntField, testBigIntField, recordsRetrieved);
}
Assert.AreEqual(recordsRetrieved, 9);
}
}
}
}
public void TestFailedCommandWithConnectionCloseCmdBehavior()
{
Parallel.For(0, 100, i =>
{
try
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from table_does_not_exist";
cmd.CommandType = CommandType.Text;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Encountered exception: {0} in iteration: {1}",
ex.ToString(), i);
}
finally
{
Console.WriteLine("Completed execution of iteration: {0}", i);
}
});
}
/// <summary>
/// Executes the SQL command and returns the output in text format.
/// </summary>
private static string ExecuteCommand(MultiShardConnection conn, string commandText)
{
try
{
StringBuilder output = new StringBuilder();
output.AppendLine();
int rowsAffected = 0;
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
cmd.CommandTimeout = s_commandLine.QueryTimeout;
cmd.CommandTimeoutPerShard = s_commandLine.QueryTimeout;
// Execute command and time with a stopwatch
Stopwatch stopwatch = Stopwatch.StartNew();
cmd.ExecutionPolicy = s_commandLine.ExecutionPolicy;
cmd.ExecutionOptions = s_commandLine.ExecutionOptions;
using (MultiShardDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
{
stopwatch.Stop();
// Get column names
IEnumerable <string> columnNames = GetColumnNames(reader).ToArray();
// Create table formatter
TableFormatter tableFormatter = new TableFormatter(columnNames.ToArray());
// Read results from db
while (reader.Read())
{
rowsAffected++;
// Add the row to the table formatter
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
tableFormatter.AddRow(values);
}
// Write formatter output
output.AppendLine(tableFormatter.ToString());
}
output.AppendLine();
output.AppendFormat("({0} rows affected - {1:hh}:{1:mm}:{1:ss} elapsed)", rowsAffected, stopwatch.Elapsed);
output.AppendLine();
}
return(output.ToString());
}
catch (MultiShardAggregateException e)
{
return(e.ToString());
}
}
public void TestSchemaMismatchErrorPropagation()
{
// First we need to alter the schema on one of the shards - we'll choose the last one.
//
string origColName = "Test_bigint_Field";
string newColName = "ModifiedName";
MultiShardTestUtils.ChangeColumnNameOnShardedTable(2, origColName, newColName);
// Then create new sharded connection so we can test the error handling logic.
// We'll wrap this all in a try-catch-finally block so that we can change the schema back
// to what the other tests will expect it to be in the finally.
//
try
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
// Need to do a SELECT * in order to get the column name error as a schema mismatcherror. If we name it explicitly
// we will get a command execution error instead.
//
cmd.CommandText = "SELECT * FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
using (MultiShardDataReader sdr = ExecAsync(conn, cmd).Result)
{
// The number of errors we have depends on which shard executed first.
// So, we know it should be 1 OR 2.
//
Assert.IsTrue(
((sdr.MultiShardExceptions.Count == 1) || (sdr.MultiShardExceptions.Count == 2)),
string.Format("Expected 1 or 2 execution erros, but saw {0}", sdr.MultiShardExceptions.Count));
int recordsRetrieved = 0;
while (sdr.Read())
{
recordsRetrieved++;
var dbNameField = sdr.GetString(0);
}
// We should see 9 records less 3 for each one that had a schema error.
int expectedRecords = ((9 - (3 * sdr.MultiShardExceptions.Count)));
Assert.AreEqual(recordsRetrieved, expectedRecords);
}
}
}
}
finally
{
MultiShardTestUtils.ChangeColumnNameOnShardedTable(2, newColName, origColName);
}
}
// GET: Captain
public ActionResult Index()
{
Dictionary <int, int> votes = new Dictionary <int, int>();
//Perform a multi shard query to tally all the votes
// Get the shards to connect to
var shards = _shardMap.GetShards();
// Create the multi-shard connection
using (var conn = new MultiShardConnection(shards, Util.SqlHelper.GetCredentialsConnectionString()))
{
// 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 Captain, COUNT(*) AS Votes FROM [dbo].[Votes] GROUP BY [Captain]";
// 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())
{
while (reader.Read())
{
votes.Add(reader.GetInt32(0), reader.GetInt32(1));
}
}
}
}
var vm = new ViewModels.Captain.IndexViewModel();
foreach (var captain in _captainContext.Captains)
{
int voteCount = 0;
votes.TryGetValue(captain.ID, out voteCount);
vm.Captains.Add(new ViewModels.Captain.IndexViewModelCaptain()
{
ID = captain.ID,
Name = captain.Name,
Votes = voteCount
});
}
return(View(vm));
}
// POST api/CustomLogin
public HttpResponseMessage Post(LoginRequest loginRequest)
{
Guid shardKey;
// SEND A QUERY TO ALL SHARD TO DETECT OUR SHARD!!!!
// SAVE companiesId to shardKey!
using (MultiShardConnection conn = new MultiShardConnection(WebApiConfig.ShardingObj.ShardMap.GetShards(), WebApiConfig.ShardingObj.connstring))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
// CHECK SCHEMA
// SQL INJECTION SECURITY ISSUE
cmd.CommandText = "SELECT CompaniesID FROM [mpbdm].[Accounts] JOIN [mpbdm].[Users] ON [mpbdm].[Users].Id = [mpbdm].[Accounts].User_Id WHERE email='" + loginRequest.email + "'";
cmd.CommandType = CommandType.Text;
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
// Async
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
bool res = sdr.Read();
if (res != false)
{
shardKey = new Guid(sdr.GetString(0));
}
else
{
return(this.Request.CreateResponse(HttpStatusCode.Unauthorized, "Account doesn't exist!"));
}
}
}
}
// Connect with entity framework to the specific shard
mpbdmContext <Guid> context = new mpbdmContext <Guid>(WebApiConfig.ShardingObj.ShardMap, shardKey, WebApiConfig.ShardingObj.connstring);
Account account = context.Accounts.Include("User").Where(a => a.User.Email == loginRequest.email).SingleOrDefault();
if (account != null)
{
byte[] incoming = CustomLoginProviderUtils.hash(loginRequest.password, account.Salt);
if (CustomLoginProviderUtils.slowEquals(incoming, account.SaltedAndHashedPassword))
{
ClaimsIdentity claimsIdentity = new ClaimsIdentity();
claimsIdentity.AddClaim(new Claim(ClaimTypes.NameIdentifier, account.User.Email));
// Custom Claim must be added to CustomLoginProvider too !!
claimsIdentity.AddClaim(new Claim("shardKey", account.User.CompaniesID));
var customLoginProvider = new CustomLoginProvider(handler);
LoginResult loginResult = customLoginProvider.CreateLoginResult(claimsIdentity, Services.Settings.MasterKey);
MobileLoginResult res = new MobileLoginResult(account, loginResult);
return(this.Request.CreateResponse(HttpStatusCode.OK, res));
}
}
return(this.Request.CreateResponse(HttpStatusCode.Unauthorized, "Invalid username or password"));
}
public void TestShardCommandBehavior()
{
var shardConnections = CreateConnections(10, () => { });
using (var conn = new MultiShardConnection(shardConnections))
{
using (var cmd = conn.CreateCommand())
{
CommandBehavior behavior = CommandBehavior.SingleResult;
behavior &= CommandBehavior.SingleRow;
cmd.ExecuteReader(behavior);
}
}
}
public void TestSimpleSelect(MultiShardExecutionPolicy policy)
{
// What we're doing:
// Grab all rows from each test database.
// Load them into a MultiShardDataReader.
// Iterate through the rows and make sure that we have 9 total.
//
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable";
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
cmd.ExecutionPolicy = policy;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
int recordsRetrieved = 0;
Logger.Log("Starting to get records");
while (sdr.Read())
{
recordsRetrieved++;
string dbNameField = sdr.GetString(0);
int testIntField = sdr.GetFieldValue <int>(1);
Int64 testBigIntField = sdr.GetFieldValue <Int64>(2);
string shardIdPseudoColumn = sdr.GetFieldValue <string>(3);
string logRecord =
string.Format(
"RecordRetrieved: dbNameField: {0}, TestIntField: {1}, TestBigIntField: {2}, shardIdPseudoColumnField: {3}, RecordCount: {4}",
dbNameField, testIntField, testBigIntField, shardIdPseudoColumn, recordsRetrieved);
Logger.Log(logRecord);
Debug.WriteLine(logRecord);
}
sdr.Close();
Assert.AreEqual(recordsRetrieved, 9);
}
}
}
}
private static List <dynamic> ExecuteMultiShardQuery(ref ShardingClient sharding, string connectionString)
{
dynamic result = new List <dynamic>();
var multiShardConnection = new MultiShardConnection(sharding.ShardMap.GetShards(), connectionString);
using (var cmd = multiShardConnection.CreateCommand()) {
cmd.CommandText = @"SELECT * FROM dbo.Blogs";
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandTimeoutPerShard = 60;
using (var reader = cmd.ExecuteReader()) {
if (reader.HasRows)
{
result = reader.ToExpandoList();
}
}
}
return(result);
}
public void TestSelectNonQuery(string commandText, MultiShardExecutionPolicy policy)
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
cmd.ExecutionPolicy = policy;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
Assert.AreEqual(0, sdr.MultiShardExceptions.Count);
// TODO: This is a weird error message, but it's good enough for now
// Fixing this will require significant refactoring of MultiShardDataReader,
// we should fix it when we finish implementing async adding of child readers
AssertExtensions.AssertThrows <MultiShardDataReaderClosedException>(() => sdr.Read());
}
}
}
}
public void TestSelect_PartialFailure_PartialResults()
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = GetPartialFailureQuery();
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
// Exactly one should have failed
Assert.AreEqual(1, sdr.MultiShardExceptions.Count);
// We should be able to read
while (sdr.Read())
{
}
}
}
}
}
public async Task <IEnumerable <Lot> > ListLotsAsync()
{
var shardMap = this.elasticScaleClient.CreateOrGetListShardMap();
var shards = shardMap.GetShards();
using (var multiShardConnection = new MultiShardConnection(shards, this.elasticScaleClient.GetConnectionString()))
using (var multiShardCommand = multiShardConnection.CreateCommand())
{
multiShardCommand.CommandText = @"
SELECT TOP (1000) l.Id
,l.StartPrice
,l.ReservePrice
,l.SaleId
,ls.Value AS LotStatus
,v.Make
,v.Model
,v.VIN
,v.Color
,v.Mileage
,v.ImageUrl
,v.FirstRegistrationDate
,v.EngineCapacity
,v.EngineType
,v.NumberOfDoors
,v.FuelType
,v.EquipmentInterior
,v.EquipmentExterior
,v.EquipmentInfotainment
,v.EquipmentEngineTechnology
,v.VehicleSource
,v.CurrentCountryOfRegistration
,v.HasServiceHistory
,v.EuroEmissionStandard
,v.HasAccidentDamage
,v.HasSecondKeyAvailable
,v.TransmissionType
,v.EnginePower
,c.Code
FROM [dbo].[Lot] l
INNER JOIN [dbo].[LotStatus] ls ON l.LotStatusId = ls.Id
INNER JOIN [dbo].[LotItem] li ON l.Id = li.LotId
INNER JOIN [dbo].[Vehicle] v ON li.Id = v.LotItemId
INNER JOIN [dbo].[Sale] s ON l.SaleId = s.Id
INNER JOIN [dbo].[Location] lo ON s.LocationId = lo.Id
INNER JOIN [dbo].[Country] c ON lo.CountryId = c.Id";
// Allow for partial results in case some shards do not respond in time
multiShardCommand.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
multiShardCommand.CommandTimeout = 30;
var lots = new List <Lot>();
// Execute the command.
// We do not need to specify retry logic because MultiShardDataReader will internally retry until the CommandTimeout expires.
using (var reader = await multiShardCommand.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var columnIndex = 0;
var lot = new Lot {
Bids = new List <Bid>(), Sale = new Sale(), Vehicle = new Vehicle()
};
lot.Id = reader.GetInt32(columnIndex++);
lot.StartPrice = reader.GetDecimal(columnIndex++);
lot.ReservePrice = reader.GetDecimal(columnIndex++);
lot.SaleId = reader.GetInt32(columnIndex++);
lot.LotStatus = (LotStatus)Enum.Parse(typeof(LotStatus), reader.GetString(columnIndex++));
lot.Vehicle.Make = reader.GetString(columnIndex++);
lot.Vehicle.Model = reader.GetString(columnIndex++);
lot.Vehicle.VIN = reader.GetString(columnIndex++);
lot.Vehicle.Color = reader.GetString(columnIndex++);
lot.Vehicle.Mileage = reader.GetInt32(columnIndex++);
lot.Vehicle.ImageUrl = reader.GetString(columnIndex++);
lot.Vehicle.FirstRegistrationDate = reader.GetDateTime(columnIndex++);
lot.Vehicle.EngineCapacity = reader.GetDouble(columnIndex++);
lot.Vehicle.EngineType = reader.GetString(columnIndex++);
lot.Vehicle.NumberOfDoors = reader.GetInt16(columnIndex++);
lot.Vehicle.FuelType = reader.GetString(columnIndex++);
lot.Vehicle.EquipmentInterior = reader.GetString(columnIndex++);
lot.Vehicle.EquipmentExterior = reader.GetString(columnIndex++);
lot.Vehicle.EquipmentInfotainment = reader.GetString(columnIndex++);
lot.Vehicle.EquipmentEngineTechnology = reader.GetString(columnIndex++);
lot.Vehicle.VehicleSource = reader.GetString(columnIndex++);
lot.Vehicle.CurrentCountryOfRegistration = reader.GetString(columnIndex++);
lot.Vehicle.HasServiceHistory = reader.GetBoolean(columnIndex++);
lot.Vehicle.EuroEmissionStandard = reader.GetInt16(columnIndex++);
lot.Vehicle.HasAccidentDamage = reader.GetBoolean(columnIndex++);
lot.Vehicle.HasSecondKeyAvailable = reader.GetBoolean(columnIndex++);
lot.Vehicle.TransmissionType = reader.GetString(columnIndex++);
lot.Vehicle.EnginePower = reader.GetString(columnIndex++);
lot.CountryCode = reader.GetString(columnIndex++);
lots.Add(lot);
}
}
return(lots);
}
}
public void TestShardNamePseudoColumnOption()
{
bool[] pseudoColumnOptions = new bool[2];
pseudoColumnOptions[0] = true;
pseudoColumnOptions[1] = false;
// do the loop over the options.
// add the excpetion handling when referencing the pseudo column
//
foreach (bool pseudoColumnPresent in pseudoColumnOptions)
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.CompleteResults;
cmd.ExecutionOptions = pseudoColumnPresent ? MultiShardExecutionOptions.IncludeShardNameColumn :
MultiShardExecutionOptions.None;
using (MultiShardDataReader sdr = cmd.ExecuteReader(CommandBehavior.Default))
{
Assert.AreEqual(0, sdr.MultiShardExceptions.Count);
int recordsRetrieved = 0;
int expectedFieldCount = pseudoColumnPresent ? 4 : 3;
int expectedVisibleFieldCount = pseudoColumnPresent ? 4 : 3;
Assert.AreEqual(expectedFieldCount, sdr.FieldCount);
Assert.AreEqual(expectedVisibleFieldCount, sdr.VisibleFieldCount);
while (sdr.Read())
{
recordsRetrieved++;
var dbNameField = sdr.GetString(0);
var testIntField = sdr.GetFieldValue <int>(1);
var testBigIntField = sdr.GetFieldValue <Int64>(2);
try
{
string shardIdPseudoColumn = sdr.GetFieldValue <string>(3);
if (!pseudoColumnPresent)
{
Assert.Fail("Should not have been able to pull the pseudo column.");
}
}
catch (IndexOutOfRangeException)
{
if (pseudoColumnPresent)
{
Assert.Fail("Should not have encountered an exception.");
}
}
}
Assert.AreEqual(recordsRetrieved, 9);
}
}
}
}
}
/// <summary>
/// Executes a sql command on an elastic scale DB over all shards available in the provided shard map and returns incidents.
/// </summary>
public static IList <IncidentModel> ExecuteMultiShardQuery(string credentialsConnectionString, string commandText, params Shard[] shards)
{
if (shards == null)
{
throw new ArgumentNullException(nameof(shards));
}
if (credentialsConnectionString == null)
{
throw new ArgumentNullException(nameof(credentialsConnectionString));
}
if (commandText == null)
{
throw new ArgumentNullException(nameof(commandText));
}
// Get the shards to connect to
List <IncidentModel> result = new List <IncidentModel>();
// Create the multi-shard connection
using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString))
{
// Create a simple command
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
// 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())
{
while (reader.Read())
{
var incidentId = reader.GetFieldValue <int>(0);
var departmentType = reader.GetFieldValue <int>(1);
var title = reader.GetFieldValue <string>(2);
var desc = reader.GetFieldValue <string>(3);
var region = reader.GetFieldValue <int>(4);
var shardName = ExtractDatabaseName(reader.GetFieldValue <string>(5));
var incident = new IncidentModel
{
IncidentId = incidentId,
DepartmentType = departmentType,
ShardName = shardName,
Description = desc,
Title = title,
RegionId = region
};
result.Add(incident);
}
}
}
}
return(result);
}
public void TestShardNamePseudoColumnOption()
{
bool[] pseudoColumnOptions = new bool[2];
pseudoColumnOptions[0] = true;
pseudoColumnOptions[1] = false;
// do the loop over the options.
// add the excpetion handling when referencing the pseudo column
//
foreach (bool pseudoColumnPresent in pseudoColumnOptions)
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.CompleteResults;
cmd.ExecutionOptions = pseudoColumnPresent ? MultiShardExecutionOptions.IncludeShardNameColumn :
MultiShardExecutionOptions.None;
using (MultiShardDataReader sdr = cmd.ExecuteReader(CommandBehavior.Default))
{
Assert.AreEqual(0, sdr.MultiShardExceptions.Count);
int recordsRetrieved = 0;
int expectedFieldCount = pseudoColumnPresent ? 4 : 3;
int expectedVisibleFieldCount = pseudoColumnPresent ? 4 : 3;
Assert.AreEqual(expectedFieldCount, sdr.FieldCount);
Assert.AreEqual(expectedVisibleFieldCount, sdr.VisibleFieldCount);
while (sdr.Read())
{
recordsRetrieved++;
var dbNameField = sdr.GetString(0);
var testIntField = sdr.GetFieldValue<int>(1);
var testBigIntField = sdr.GetFieldValue<Int64>(2);
try
{
string shardIdPseudoColumn = sdr.GetFieldValue<string>(3);
if (!pseudoColumnPresent)
{
Assert.Fail("Should not have been able to pull the pseudo column.");
}
}
catch (IndexOutOfRangeException)
{
if (pseudoColumnPresent)
{
Assert.Fail("Should not have encountered an exception.");
}
}
}
Assert.AreEqual(recordsRetrieved, 9);
}
}
}
}
}
public static void Main()
{
SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder
{
UserID = userName,
Password = password,
ApplicationName = applicationName
};
#region Create Shards
// Bootstrap the shard map manager, register shards, and store mappings of tenants to shards
// Note that you can keep working with existing shard maps. There is no need to
// re-create and populate the shard map from scratch every time.
Console.WriteLine("Checking for existing shard map and creating new shard map if necessary.");
Sharding sharding = new Sharding(server, shardmapmgrdb, connStrBldr.ConnectionString);
sharding.RegisterNewShard(server, shard1, connStrBldr.ConnectionString, tenantId1);
sharding.RegisterNewShard(server, shard2, connStrBldr.ConnectionString, tenantId2);
#endregion
#region Insert Items
// Do work for tenant 1 :-)
// Create and save a new Blog
Console.Write("Enter a name for a new Blog: ");
var name = Console.ReadLine();
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext <int>(sharding.ShardMap, tenantId1, connStrBldr.ConnectionString))
{
var blog = new Blog {
Name = name
};
db.Blogs.Add(blog);
db.SaveChanges();
}
});
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext <int>(sharding.ShardMap, tenantId1, connStrBldr.ConnectionString))
{
// Display all Blogs for tenant 1
var query = from b in db.Blogs
orderby b.Name
select b;
Console.WriteLine("All blogs for tenant id {0}:", tenantId1);
foreach (var item in query)
{
Console.WriteLine(item.Name);
}
}
});
// Do work for tenant 2 :-)
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext <int>(sharding.ShardMap, tenantId2, connStrBldr.ConnectionString))
{
// Display all Blogs from the database
var query = from b in db.Blogs
orderby b.Name
select b;
Console.WriteLine("All blogs for tenant id {0}:", tenantId2);
foreach (var item in query)
{
Console.WriteLine(item.Name);
}
}
});
// Create and save a new Blog
Console.Write("Enter a name for a new Blog: ");
var name2 = Console.ReadLine();
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext <int>(sharding.ShardMap, tenantId2, connStrBldr.ConnectionString))
{
var blog = new Blog {
Name = name2
};
db.Blogs.Add(blog);
db.SaveChanges();
}
});
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (var db = new ElasticScaleContext <int>(sharding.ShardMap, tenantId2, connStrBldr.ConnectionString))
{
// Display all Blogs from the database
var query = from b in db.Blogs
orderby b.Name
select b;
Console.WriteLine("All blogs for tenant id {0}:", tenantId2);
foreach (var item in query)
{
Console.WriteLine(item.Name);
}
}
});
#endregion
#region Query
Console.WriteLine("Staring Multi Shard Read via MultiShardCommand");
/// Multi Shard querying
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (MultiShardConnection conn = new MultiShardConnection(
sharding.ShardMap.GetShards(),
connStrBldr.ConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT BlogId, Name FROM Blogs";
cmd.CommandType = CommandType.Text;
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
var c2Field = sdr.GetFieldValue <int>(0);
var c1Field = sdr.GetFieldValue <string>(1);
Blog blog = new Blog(c2Field, c1Field, null);
Console.WriteLine("Blog Entry: " + blog.Name);
}
}
}
}
});
Console.WriteLine("Staring Multi Shard Read via Elastic Scale");
//Elastic Query
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (SqlConnection conn = new SqlConnection(elasticqueryString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT BlogId, Name FROM Blogs";
cmd.CommandType = CommandType.Text;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
var c2Field = sdr.GetFieldValue <int>(0);
var c1Field = sdr.GetFieldValue <string>(1);
Blog blog = new Blog(c2Field, c1Field, null);
Console.WriteLine("Blog Entry: " + blog.Name);
}
}
}
}
});
#endregion
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
}
/// <summary>
/// Executes a sql command on an elastic scale DB over all shards available in the provided shard map and returns incidents.
/// </summary>
public static IList <IncidentModel> ExecuteMultiShardQuery(string credentialsConnectionString, string commandText, params Shard[] shards)
{
if (shards == null)
{
throw new ArgumentNullException(nameof(shards));
}
if (credentialsConnectionString == null)
{
throw new ArgumentNullException(nameof(credentialsConnectionString));
}
if (commandText == null)
{
throw new ArgumentNullException(nameof(commandText));
}
// Get the shards to connect to
List <IncidentModel> result = new List <IncidentModel>();
// Create the multi-shard connection
using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString))
{
// Create a simple command
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
// 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())
{
while (reader.Read())
{
var id = reader.GetFieldValue <int>(0);
var cityId = reader.GetFieldValue <int>(1);
var callNumber = reader.GetFieldValue <string>(2);
var phone = reader.GetFieldValue <string>(3);
var unmaskedPhone = reader.GetFieldValue <string>(4);
var title = reader.GetFieldValue <string>(5);
var receivedTime = reader.GetFieldValue <DateTime>(6);
var address = reader.GetFieldValue <string>(7);
var reportingParty = reader.GetFieldValue <string>(8);
var unmaskedReportingParty = reader.GetFieldValue <string>(9);
var description = reader.GetFieldValue <string>(10);
var updateDescription = reader.GetFieldValue <string>(11);
var longitude = reader.GetFieldValue <double>(12);
var latitude = reader.GetFieldValue <double>(13);
var isHighPriority = reader.GetFieldValue <bool>(14);
var incidentCategory = reader.GetFieldValue <IncidentType>(15);
var incident = new IncidentModel
{
Id = id,
CityId = cityId,
CallNumber = callNumber,
Phone = phone,
UnmaskedPhone = unmaskedPhone,
Title = title,
ReceivedTime = receivedTime,
Address = address,
ReportingParty = reportingParty,
UnmaskedReportingParty = unmaskedReportingParty,
Description = description,
UpdateDescription = updateDescription,
Longitude = longitude,
Latitude = latitude,
IsHighPriority = isHighPriority,
IncidentCategory = incidentCategory,
SearchAreaId = null
};
result.Add(incident);
}
}
}
}
return(result);
}
public void TestSelectNonQuery(string commandText, MultiShardExecutionPolicy policy)
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
cmd.ExecutionPolicy = policy;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
Assert.AreEqual(0, sdr.MultiShardExceptions.Count);
// TODO: This is a weird error message, but it's good enough for now
// Fixing this will require significant refactoring of MultiShardDataReader,
// we should fix it when we finish implementing async adding of child readers
AssertExtensions.AssertThrows<MultiShardDataReaderClosedException>(() => sdr.Read());
}
}
}
}
public async Task <IEnumerable <Sale> > ListActiveSalesAsync()
{
var shardMap = this.elasticScaleClient.CreateOrGetListShardMap();
var shards = shardMap.GetShards();
using (var multiShardConnection = new MultiShardConnection(shards, this.elasticScaleClient.GetConnectionString()))
using (var multiShardCommand = multiShardConnection.CreateCommand())
{
multiShardCommand.CommandText = @"
SELECT
-- Sale
s.Id
,s.Name
,s.StartDate
,s.EndDate
,s.BidIncrement
,st.Value AS SaleType
-- Seller
,sl.CompanyName
-- Location
,l.StreetAddress
,l.PostalCode
,l.City
,l.StateOrProvince
-- Country
,c.Code
,c.Name
FROM [dbo].[Sale] s WITH (NOLOCK)
INNER JOIN [dbo].[SaleType] st WITH (NOLOCK) ON s.SaleTypeId = st.Id
INNER JOIN [dbo].[Seller] sl WITH (NOLOCK) ON s.SellerId = sl.Id
INNER JOIN [dbo].[Location] l WITH (NOLOCK) ON s.LocationId = l.Id
INNER JOIN [dbo].[Country] c WITH (NOLOCK) ON l.CountryId = c.Id";
// Allow for partial results in case some shards do not respond in time
multiShardCommand.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
multiShardCommand.CommandTimeout = 30;
var sales = new List <Sale>();
// Execute the command.
// We do not need to specify retry logic because MultiShardDataReader will internally retry until the CommandTimeout expires.
using (var reader = await multiShardCommand.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var columnIndex = 0;
var sale = new Sale {
Seller = new Seller(), Location = new Location {
Country = new Country()
}
};
sale.Id = reader.GetInt32(columnIndex++);
sale.Name = reader.GetString(columnIndex++);
sale.StartDate = reader.GetDateTime(columnIndex++);
sale.EndDate = reader.GetDateTime(columnIndex++);
sale.BidIncrement = reader.GetDecimal(columnIndex++);
sale.SaleType = (SaleType)Enum.Parse(typeof(SaleType), reader.GetString(columnIndex++));
sale.Seller.CompanyName = reader.GetString(columnIndex++);
sale.Location.StreetAddress = reader.GetString(columnIndex++);
sale.Location.PostalCode = reader.GetString(columnIndex++);
sale.Location.City = reader.GetString(columnIndex++);
sale.Location.StateOrProvince = reader.GetString(columnIndex++);
sale.Location.Country.Code = reader.GetString(columnIndex++);
sale.Location.Country.Name = reader.GetString(columnIndex++);
sales.Add(sale);
}
}
foreach (var sale in sales)
{
sale.NumberOfLots = await GetLotCountAsync(sale.Id, sale.Location.Country.Code);
}
return(sales);
}
}
public MultiShardAggregateException TestSelectFailure(string commandText, MultiShardExecutionPolicy policy)
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
cmd.ExecutionPolicy = policy;
// ExecuteReader should fail
MultiShardAggregateException aggregateException =
AssertExtensions.AssertThrows<MultiShardAggregateException>(() => cmd.ExecuteReader());
// Sanity check the exceptions are the correct type
foreach (Exception e in aggregateException.InnerExceptions)
{
Assert.IsInstanceOfType(e, typeof(MultiShardException));
Assert.IsInstanceOfType(e.InnerException, typeof(SqlException));
}
// Return the exception so that the caller can do additional validation
return aggregateException;
}
}
}
public void TestSelect_PartialFailure_PartialResults()
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = GetPartialFailureQuery();
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
// Exactly one should have failed
Assert.AreEqual(1, sdr.MultiShardExceptions.Count);
// We should be able to read
while (sdr.Read())
{
}
}
}
}
}
public void TestQueryShardsInvalidConnectionSync()
{
var badShard = new ShardLocation("badLocation", "badDatabase");
var bldr = new SqlConnectionStringBuilder();
bldr.DataSource = badShard.DataSource;
bldr.InitialCatalog = badShard.Database;
var badConn = new SqlConnection(bldr.ConnectionString);
try
{
using (var conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
conn.ShardConnections.Add(new Tuple<ShardLocation, System.Data.Common.DbConnection>(badShard,
badConn));
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select 1";
cmd.ExecuteReader();
}
}
}
catch (Exception ex)
{
if (ex is MultiShardAggregateException)
{
var maex = (MultiShardAggregateException)ex;
Logger.Log("Exception encountered: " + maex.ToString());
throw ((MultiShardException)(maex.InnerException)).InnerException;
}
throw;
}
}
public void TestSchemaMismatchErrorPropagation()
{
// First we need to alter the schema on one of the shards - we'll choose the last one.
//
string origColName = "Test_bigint_Field";
string newColName = "ModifiedName";
MultiShardTestUtils.ChangeColumnNameOnShardedTable(2, origColName, newColName);
// Then create new sharded connection so we can test the error handling logic.
// We'll wrap this all in a try-catch-finally block so that we can change the schema back
// to what the other tests will expect it to be in the finally.
//
try
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
// Need to do a SELECT * in order to get the column name error as a schema mismatcherror. If we name it explicitly
// we will get a command execution error instead.
//
cmd.CommandText = "SELECT * FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
using (MultiShardDataReader sdr = ExecAsync(conn, cmd).Result)
{
// The number of errors we have depends on which shard executed first.
// So, we know it should be 1 OR 2.
//
Assert.IsTrue(
((sdr.MultiShardExceptions.Count == 1) || (sdr.MultiShardExceptions.Count == 2)),
string.Format("Expected 1 or 2 execution erros, but saw {0}", sdr.MultiShardExceptions.Count));
int recordsRetrieved = 0;
while (sdr.Read())
{
recordsRetrieved++;
var dbNameField = sdr.GetString(0);
}
// We should see 9 records less 3 for each one that had a schema error.
int expectedRecords = ((9 - (3 * sdr.MultiShardExceptions.Count)));
Assert.AreEqual(recordsRetrieved, expectedRecords);
}
}
}
}
finally
{
MultiShardTestUtils.ChangeColumnNameOnShardedTable(2, newColName, origColName);
}
}
// POST api/CustomRegistration
public HttpResponseMessage Post(RegistrationRequest registrationRequest)
{
if (!Regex.IsMatch(registrationRequest.email, "^([a-z.A-Z0-9]{1,})@([a-z]{2,}).[a-z]{2,}$"))
{
return(this.Request.CreateResponse(HttpStatusCode.BadRequest, "Invalid email!"));
}
else if (registrationRequest.password.Length < 8)
{
return(this.Request.CreateResponse(HttpStatusCode.BadRequest, "Invalid password (at least 8 chars required)"));
}
// MUST FIND COMPANY BY EMAIL
// CREATE a MULTISHARD COMMAND
// SEARCH BY EMAIL
mpbdmContext <Guid> context = null;
Guid shardKey;
using (MultiShardConnection conn = new MultiShardConnection(WebApiConfig.ShardingObj.ShardMap.GetShards(), WebApiConfig.ShardingObj.connstring))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
// Get emailDomain
char[] papaki = new char[1];
papaki[0] = '@';
// SQL INJECTION SECURITY ISSUE
string emailDomain = registrationRequest.email.Split(papaki).Last();
// CHECK SCHEMA
cmd.CommandText = "SELECT Id FROM [mpbdm].[Companies] WHERE Email LIKE '%" + emailDomain + "'";
cmd.CommandType = CommandType.Text;
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
bool res = sdr.Read();
if (res != false)
{
shardKey = new Guid(sdr.GetString(0));
}
else
{
if (registrationRequest.CompanyName == null || registrationRequest.CompanyAddress == null)
{
return(this.Request.CreateResponse(HttpStatusCode.Forbidden, "Company under this email domain doesn't exist! To create a company with your registration please provide CompanyName and CompanyAddress parameters"));
}
Companies comp = new Companies();
comp.Id = Guid.NewGuid().ToString();
comp.Name = registrationRequest.CompanyName;
comp.Address = registrationRequest.CompanyAddress;
comp.Email = registrationRequest.email;
comp.Deleted = false;
// SHARDING Find where to save the new company
Shard shard = WebApiConfig.ShardingObj.FindRoomForCompany();
WebApiConfig.ShardingObj.RegisterNewShard(shard.Location.Database, comp.Id);
//Connect to the db registered above
shardKey = new Guid(comp.Id);
context = new mpbdmContext <Guid>(WebApiConfig.ShardingObj.ShardMap, shardKey, WebApiConfig.ShardingObj.connstring);
// Add to the db
context.Companies.Add(comp);
context.SaveChanges();
}
}
}
}
//////////////////////////////////////////////////////////////////////
// MUST RECHECK CORRECT DB!!!!!!!!!!!
if (context == null)
{
context = new mpbdmContext <Guid>(WebApiConfig.ShardingObj.ShardMap, shardKey, WebApiConfig.ShardingObj.connstring);
}
Account account = null;
var aa = context.Set <Account>();
var bb = aa.Where(a => a.User.Email == registrationRequest.email);
account = bb.FirstOrDefault();
if (account != null)
{
return(this.Request.CreateResponse(HttpStatusCode.BadRequest, "Email already exists"));
}
else
{
byte[] salt = CustomLoginProviderUtils.generateSalt();
string compId = shardKey.ToString();
Users newUser = new Users
{
Id = CustomLoginProvider.ProviderName + ":" + registrationRequest.email,
CompaniesID = compId,
FirstName = registrationRequest.firstName,
LastName = registrationRequest.lastName,
Email = registrationRequest.email
};
Account newAccount = new Account
{
Id = Guid.NewGuid().ToString(),
//Username = registrationRequest.username,
Salt = salt,
SaltedAndHashedPassword = CustomLoginProviderUtils.hash(registrationRequest.password, salt),
User = newUser
};
context.Users.Add(newUser);
context.Accounts.Add(newAccount);
try
{
context.SaveChanges();
}
catch (Exception ex)
{
var a = ex.InnerException;
}
return(this.Request.CreateResponse(HttpStatusCode.Created));
}
}
public void TestQueryShardsTvpParam()
{
try
{
// Install schema
string createTbl =
@"
CREATE TABLE dbo.PageView
(
PageViewID BIGINT NOT NULL,
PageViewCount BIGINT NOT NULL
);
CREATE TYPE dbo.PageViewTableType AS TABLE
(
PageViewID BIGINT NOT NULL
);";
string createProc =
@"CREATE PROCEDURE dbo.procMergePageView
@Display dbo.PageViewTableType READONLY
AS
BEGIN
MERGE INTO dbo.PageView AS T
USING @Display AS S
ON T.PageViewID = S.PageViewID
WHEN MATCHED THEN UPDATE SET T.PageViewCount = T.PageViewCount + 1
WHEN NOT MATCHED THEN INSERT VALUES(S.PageViewID, 1);
END";
using (var cmd = _shardConnection.CreateCommand())
{
cmd.CommandText = createTbl;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
cmd.ExecuteReader();
cmd.CommandText = createProc;
cmd.ExecuteNonQueryAsync(CancellationToken.None, MultiShardExecutionPolicy.PartialResults).Wait();
}
Logger.Log("Schema installed..");
// Create the data table
DataTable table = new DataTable();
table.Columns.Add("PageViewID", typeof(long));
int idCount = 3;
for (int i = 0; i < idCount; i++)
{
table.Rows.Add(i);
}
// Execute the command
using (var cmd = _shardConnection.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.procMergePageView";
var param = new SqlParameter("@Display", table);
param.TypeName = "dbo.PageViewTableType";
param.SqlDbType = SqlDbType.Structured;
cmd.Parameters.Add(param);
cmd.ExecuteNonQueryAsync(CancellationToken.None, MultiShardExecutionPolicy.PartialResults).Wait();
cmd.ExecuteNonQueryAsync(CancellationToken.None, MultiShardExecutionPolicy.PartialResults).Wait();
}
Logger.Log("Command executed..");
using (var cmd = _shardConnection.CreateCommand())
{
// Validate that the pageviewcount was updated
cmd.CommandText = "select PageViewCount from PageView";
cmd.CommandType = CommandType.Text;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
using (var sdr = cmd.ExecuteReader(CommandBehavior.Default))
{
while (sdr.Read())
{
long pageCount = (long)sdr["PageViewCount"];
Logger.Log("Page view count: {0} obtained from shard: {1}", pageCount, sdr.GetFieldValue <string>(1));
Assert.AreEqual(2, pageCount);
}
}
}
}
catch (Exception ex)
{
if (ex is AggregateException)
{
var aex = (AggregateException)ex;
Logger.Log("Exception encountered: {0}", aex.InnerException.ToString());
}
else
{
Logger.Log(ex.Message);
}
throw;
}
finally
{
string dropSchema =
@"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procMergePageView]') and objectproperty(id, N'IsProcedure') = 1)
begin
drop procedure dbo.procMergePageView
end
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pageview]'))
begin
drop table dbo.Pageview
end
if exists (select * from sys.types where name = 'PageViewTableType')
begin
drop type dbo.PageViewTableType
end";
using (var cmd = _shardConnection.CreateCommand())
{
cmd.CommandText = dropSchema;
cmd.ExecuteNonQueryAsync(CancellationToken.None, MultiShardExecutionPolicy.PartialResults).Wait();
}
}
}
public void TestSelectNoRows(string commandText, MultiShardExecutionPolicy policy)
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
cmd.ExecutionPolicy = policy;
// Read first
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
Assert.AreEqual(0, sdr.MultiShardExceptions.Count);
while (sdr.Read())
{
Assert.Fail("Should not have gotten any records.");
}
Assert.IsFalse(sdr.HasRows);
}
// HasRows first
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
Assert.AreEqual(0, sdr.MultiShardExceptions.Count);
Assert.IsFalse(sdr.HasRows);
while (sdr.Read())
{
Assert.Fail("Should not have gotten any records.");
}
}
}
}
}
public void TestShardResultFailures()
{
ProxyServer proxyServer = GetProxyServer();
try
{
// Start up the proxy server. Do it in a try so we can shut it down in the finally.
// Also, we have to generate the proxyShardconnections *AFTER* we start up the server
// so that we know what port the proxy is listening on. More on the placement
// of the connection generation below.
//
proxyServer.Start();
// PreKillReads is the number of successful reads to perform before killing
// all the connections. We start at 0 to test the no failure case as well.
//
for (int preKillReads = 0; preKillReads <= 10; preKillReads++)
{
// Additionally, since we are running inside a loop, we need to regenerate the proxy shard connections each time
// so that we don't re-use dead connections. If we do that we will end up hung in the read call.
//
List <Tuple <ShardLocation, DbConnection> > proxyShardConnections = GetProxyShardConnections(proxyServer);
using (MultiShardConnection conn = new MultiShardConnection(proxyShardConnections))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT db_name() as dbName1, REPLICATE(db_name(), 1000) as longExpr, db_name() as dbName2 FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
using (MultiShardDataReader sdr = cmd.ExecuteReader(CommandBehavior.Default))
{
int tuplesRead = 0;
while (sdr.Read())
{
// Read part of the tuple first before killing the connections and
// then attempting to read the rest of the tuple.
//
tuplesRead++;
try
{
// The longExpr should contain the first dbName field multiple times.
//
string dbName1 = sdr.GetString(0);
string longExpr = sdr.GetString(1);
Assert.IsTrue(longExpr.Contains(dbName1));
if (tuplesRead == preKillReads)
{
proxyServer.KillAllConnections();
}
// The second dbName field should be the same as the first dbName field.
//
string dbName2 = sdr.GetString(2);
Assert.AreEqual(dbName1, dbName2);
// The shardId should contain both the first and the second dbName fields.
//
string shardId = sdr.GetString(3);
Assert.IsTrue(shardId.Contains(dbName1));
Assert.IsTrue(shardId.Contains(dbName2));
}
catch (Exception ex)
{
// We've seen some failures here due to an attempt to access a socket after it has
// been disposed. The only place where we are attempting to access the socket
// is in the call to proxyServer.KillAllConnections. Unfortunately, it's not clear
// what is causing that problem since it only appears to repro in the lab.
// I (errobins) would rather not blindly start changing things in the code (either
// our code above, our exception handling code here, or the proxyServer code) until
// we know which socket we are trying to access when we hit this problem.
// So, the first step I will take is to pull additional exception information
// so that we can see some more information about what went wrong the next time it repros.
//
Assert.Fail("Unexpected exception, rethrowing. Here is some info: \n Message: {0} \n Source: {1} \n StackTrace: {2}",
ex.Message, ex.Source, ex.StackTrace);
throw;
}
}
Assert.IsTrue((tuplesRead <= preKillReads) || (0 == preKillReads),
String.Format("Tuples read was {0}, Pre-kill reads was {1}", tuplesRead, preKillReads));
}
}
}
}
}
finally
{
// Be sure to shut down the proxy server.
//
string proxyLog = proxyServer.EventLog.ToString();
Logger.Log(proxyLog);
proxyServer.Stop();
}
}
public void TestSimpleSelect(MultiShardExecutionPolicy policy)
{
// What we're doing:
// Grab all rows from each test database.
// Load them into a MultiShardDataReader.
// Iterate through the rows and make sure that we have 9 total.
//
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable";
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
cmd.ExecutionPolicy = policy;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
int recordsRetrieved = 0;
Logger.Log("Starting to get records");
while (sdr.Read())
{
recordsRetrieved++;
string dbNameField = sdr.GetString(0);
int testIntField = sdr.GetFieldValue<int>(1);
Int64 testBigIntField = sdr.GetFieldValue<Int64>(2);
string shardIdPseudoColumn = sdr.GetFieldValue<string>(3);
string logRecord =
string.Format(
"RecordRetrieved: dbNameField: {0}, TestIntField: {1}, TestBigIntField: {2}, shardIdPseudoColumnField: {3}, RecordCount: {4}",
dbNameField, testIntField, testBigIntField, shardIdPseudoColumn, recordsRetrieved);
Logger.Log(logRecord);
Debug.WriteLine(logRecord);
}
sdr.Close();
Assert.AreEqual(recordsRetrieved, 9);
}
}
}
}
public void TestFailedCommandWithConnectionCloseCmdBehavior()
{
Parallel.For(0, 100, i =>
{
try
{
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from table_does_not_exist";
cmd.CommandType = CommandType.Text;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Encountered exception: {0} in iteration: {1}",
ex.ToString(), i);
}
finally
{
Console.WriteLine("Completed execution of iteration: {0}", i);
}
});
}
public void TestQueryShardsAsync()
{
// Create new sharded connection so we can test the OpenAsync call as well.
//
using (MultiShardConnection conn = new MultiShardConnection(_shardMap.GetShards(), MultiShardTestUtils.ShardConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
using (MultiShardDataReader sdr = ExecAsync(conn, cmd).Result)
{
int recordsRetrieved = 0;
while (sdr.Read())
{
recordsRetrieved++;
var dbNameField = sdr.GetString(0);
var testIntField = sdr.GetFieldValue<int>(1);
var testBigIntField = sdr.GetFieldValue<Int64>(2);
Logger.Log("RecordRetrieved: dbNameField: {0}, TestIntField: {1}, TestBigIntField: {2}, RecordCount: {3}",
dbNameField, testIntField, testBigIntField, recordsRetrieved);
}
Assert.AreEqual(recordsRetrieved, 9);
}
}
}
}
public List <string[]> ExecuteMultiShardQuery(ListShardMap <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,
o.OrderID AS OrderCount
FROM
dbo.Customers AS c INNER JOIN
dbo.Orders AS o
ON c.CustomerID = o.CustomerID
ORDER BY
1";
// 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);
return(formatter._rows);
}
}
}
}
public void TestShardResultFailures()
{
ProxyServer proxyServer = GetProxyServer();
try
{
// Start up the proxy server. Do it in a try so we can shut it down in the finally.
// Also, we have to generate the proxyShardconnections *AFTER* we start up the server
// so that we know what port the proxy is listening on. More on the placement
// of the connection generation below.
//
proxyServer.Start();
// PreKillReads is the number of successful reads to perform before killing
// all the connections. We start at 0 to test the no failure case as well.
//
for (int preKillReads = 0; preKillReads <= 10; preKillReads++)
{
// Additionally, since we are running inside a loop, we need to regenerate the proxy shard connections each time
// so that we don't re-use dead connections. If we do that we will end up hung in the read call.
//
List<Tuple<ShardLocation, DbConnection>> proxyShardConnections = GetProxyShardConnections(proxyServer);
using (MultiShardConnection conn = new MultiShardConnection(proxyShardConnections))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT db_name() as dbName1, REPLICATE(db_name(), 1000) as longExpr, db_name() as dbName2 FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
using (MultiShardDataReader sdr = cmd.ExecuteReader(CommandBehavior.Default))
{
int tuplesRead = 0;
while (sdr.Read())
{
// Read part of the tuple first before killing the connections and
// then attempting to read the rest of the tuple.
//
tuplesRead++;
try
{
// The longExpr should contain the first dbName field multiple times.
//
string dbName1 = sdr.GetString(0);
string longExpr = sdr.GetString(1);
Assert.IsTrue(longExpr.Contains(dbName1));
if (tuplesRead == preKillReads)
{
proxyServer.KillAllConnections();
}
// The second dbName field should be the same as the first dbName field.
//
string dbName2 = sdr.GetString(2);
Assert.AreEqual(dbName1, dbName2);
// The shardId should contain both the first and the second dbName fields.
//
string shardId = sdr.GetString(3);
Assert.IsTrue(shardId.Contains(dbName1));
Assert.IsTrue(shardId.Contains(dbName2));
}
catch (Exception ex)
{
// We've seen some failures here due to an attempt to access a socket after it has
// been disposed. The only place where we are attempting to access the socket
// is in the call to proxyServer.KillAllConnections. Unfortunately, it's not clear
// what is causing that problem since it only appears to repro in the lab.
// I (errobins) would rather not blindly start changing things in the code (either
// our code above, our exception handling code here, or the proxyServer code) until
// we know which socket we are trying to access when we hit this problem.
// So, the first step I will take is to pull additional exception information
// so that we can see some more information about what went wrong the next time it repros.
//
Assert.Fail("Unexpected exception, rethrowing. Here is some info: \n Message: {0} \n Source: {1} \n StackTrace: {2}",
ex.Message, ex.Source, ex.StackTrace);
throw;
}
}
Assert.IsTrue((tuplesRead <= preKillReads) || (0 == preKillReads),
String.Format("Tuples read was {0}, Pre-kill reads was {1}", tuplesRead, preKillReads));
}
}
}
}
}
finally
{
// Be sure to shut down the proxy server.
//
string proxyLog = proxyServer.EventLog.ToString();
Logger.Log(proxyLog);
proxyServer.Stop();
}
}
