public void TestQueryShardsInvalidShardStateSync()
{
// Get a shard and close it's connection
var shardSqlConnections = _shardConnection.GetShardConnections();
shardSqlConnections[1].Item2.Close();
try
{
// Execute
using (MultiShardCommand cmd = _shardConnection.CreateCommand())
{
cmd.CommandText = "SELECT dbNameField, Test_int_Field, Test_bigint_Field FROM ConsistentShardedTable";
cmd.CommandType = CommandType.Text;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
}
}
}
catch (Exception ex)
{
if (ex is AggregateException)
{
var aex = (AggregateException)ex;
Logger.Log("Exception encountered: " + ex.Message);
throw aex.InnerExceptions.FirstOrDefault((e) => e is InvalidOperationException);
}
throw;
}
}
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 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);
}
}
}
}
/// <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 TestShardCommandRetryExhaustion()
{
var retryPolicy = new RetryPolicy(2, TimeSpan.FromMilliseconds(100), TimeSpan.FromSeconds(10), TimeSpan.FromMilliseconds(100));
var shardConnections = new List <Tuple <ShardLocation, DbConnection> >();
// Create ten mocked connections, half of them will throw exceptions on Open
for (int i = 0; i < 10; i++)
{
string database = string.Format("Shard{0}", i);
int j = i;
Action executeOnOpen = () =>
{
if (j < 5)
{
throw new TimeoutException();
}
};
var mockCon = new MockSqlConnection(database, executeOnOpen);
shardConnections.Add(new Tuple <ShardLocation, DbConnection>(new ShardLocation("test", database), mockCon));
}
var mockCmd = new MockSqlCommand();
mockCmd.ExecuteReaderFunc = (t, c) => new MockSqlDataReader();
mockCmd.CommandText = "select 1";
using (var conn = new MultiShardConnection(shardConnections))
{
using (var cmd = MultiShardCommand.Create(conn, mockCmd, 300))
{
cmd.ExecutionOptions = MultiShardExecutionOptions.None;
cmd.RetryPolicy = retryPolicy;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
MultiShardDataReader rdr = cmd.ExecuteReader(CommandBehavior.Default);
// Validate the right exception is re-thrown
Assert.IsTrue(rdr.MultiShardExceptions.Count == 5, "Expected MultiShardExceptions!");
foreach (MultiShardException ex in rdr.MultiShardExceptions)
{
Assert.IsTrue(ex.InnerException is TimeoutException, "Expected TimeoutException!");
}
// Validate that the connections for the faulted readers are closed
for (int i = 0; i < 5; i++)
{
Assert.IsTrue(shardConnections[i].Item2.State == ConnectionState.Closed,
"Expected Connection to be Closed!");
}
}
}
}
public DataSet MultiShardQuery(string commandText)
{
DataSet _ds = new DataSet();
try
{
RetryPolicy.DefaultExponential.ExecuteAction(() =>
{
using (MultiShardCommand cmd = new MultiShardConnection(lsm.GetShards(), shardConnectionString).CreateCommand())
{
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
cmd.ExecutionOptions = MultiShardExecutionOptions.None;
//04/23/2015 by Mark Berman - switched to Partial results versus CompleteResults as timeout exception was being thrown
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
if (sdr.Read())
{
// the multi-shard query does not return a dataset or datarow.
// we have to manually re-create a table structure, then fill it with data.
object[] sqlValues = new object[sdr.FieldCount];
sdr.GetSqlValues(sqlValues);
DataTable dtValues = new DataTable();
foreach (var column in sqlValues)
{
dtValues.Columns.Add(new DataColumn {
DataType = column.GetType()
});
}
_ds.Tables.Add(dtValues);
_ds.Tables[0].Rows.Add(sqlValues);
while (sdr.Read())
{
sdr.GetSqlValues(sqlValues);
_ds.Tables[0].Rows.Add(sqlValues);
}
}
}
}
});
}
catch { _ds = null; }
return(_ds);
}
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 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 void TestAddDataReaderWithNullSchema()
{
// Creates a MultiShardDataReader and verifies that the right exception is thrown
Func <LabeledDbDataReader[], bool> createMultiShardReader = (readers) =>
{
bool hitNullSchemaException = false;
try
{
var mockMultiShardCmd = MultiShardCommand.Create(null, "test");
var multiShardDataReader = new MultiShardDataReader(mockMultiShardCmd, readers,
MultiShardExecutionPolicy.PartialResults, false, readers.Length);
}
catch (MultiShardDataReaderInternalException ex)
{
hitNullSchemaException = ex.Message.Contains("null schema");
}
return(hitNullSchemaException);
};
var labeledDataReaders = new LabeledDbDataReader[10];
// Create a few mock readers. All with a null schema
for (int i = 0; i < labeledDataReaders.Length; i++)
{
var mockReader = new MockSqlDataReader(string.Format("Reader{0}", i), null /* Null schema */);
labeledDataReaders[i] = new LabeledDbDataReader(mockReader,
new ShardLocation("test", string.Format("Shard{0}", i)), new MockSqlCommand()
{
Connection = new MockSqlConnection("", () => { })
});
}
// Case #1
bool hitException = createMultiShardReader(labeledDataReaders);
Assert.IsFalse(hitException, "Unexpected exception! All readers have a null schema.");
// Case #2
for (int i = 0; i < labeledDataReaders.Length; i++)
{
MockSqlDataReader mockReader = (MockSqlDataReader)labeledDataReaders[i].DbDataReader;
mockReader.Open();
if (i > labeledDataReaders.Length / 2)
{
mockReader.DataTable = new DataTable();
}
}
hitException = createMultiShardReader(labeledDataReaders);
Assert.IsTrue(hitException, "Exception not hit! Second half of readers don't have a null schema!");
// Case #3
for (int i = 0; i < labeledDataReaders.Length; i++)
{
MockSqlDataReader mockReader = (MockSqlDataReader)labeledDataReaders[i].DbDataReader;
mockReader.Open();
if (i < labeledDataReaders.Length / 2)
{
mockReader.DataTable = new DataTable();
}
else
{
mockReader.DataTable = null;
}
}
hitException = createMultiShardReader(labeledDataReaders);
Assert.IsTrue(hitException, "Exception not hit! First half of readers don't have a null schema!");
}
public void TestDataReaderReadException()
{
// Setup two data readers from shards
var mockReader1 = new MockSqlDataReader("Reader1");
var mockReader2 = new MockSqlDataReader("Reader2");
bool movedOnToNextReader = false;
int invokeCount = 1;
Func <MockSqlDataReader, Task <bool> > ExecuteOnReadAsync = (r) =>
{
return(Task.Run <bool>(() =>
{
// First reader throws an exception when Read
if (r.Name == "Reader1")
{
if (invokeCount == 2)
{
throw new InvalidOperationException();
}
}
else
{
movedOnToNextReader = true;
}
return true;
}));
};
Action <MockSqlDataReader> ExecuteOnGetColumn = (r) =>
{
if (r.Name == "Reader1")
{
throw new InvalidOperationException();
}
};
mockReader1.ExecuteOnReadAsync = ExecuteOnReadAsync;
mockReader1.ExecuteOnGetColumn = ExecuteOnGetColumn;
mockReader2.ExecuteOnReadAsync = ExecuteOnReadAsync;
var labeledDataReaders = new LabeledDbDataReader[2];
labeledDataReaders[0] = new LabeledDbDataReader(mockReader1, new ShardLocation("test", "Shard1"),
new MockSqlCommand()
{
Connection = new MockSqlConnection("", () => { })
});
labeledDataReaders[1] = new LabeledDbDataReader(mockReader2, new ShardLocation("test", "Shard2"),
new MockSqlCommand()
{
Connection = new MockSqlConnection("", () => { })
});
// Create the MultiShardDataReader
var mockMultiShardCmd = MultiShardCommand.Create(null, "test");
var multiShardDataReader = new MultiShardDataReader(mockMultiShardCmd, labeledDataReaders,
MultiShardExecutionPolicy.PartialResults, false);
// Validate that if an exception is thrown when reading a column,
// it is propagated back to the user
try
{
multiShardDataReader.Read();
invokeCount++;
multiShardDataReader.GetInt32(0);
}
catch (Exception ex)
{
Assert.IsTrue(ex is InvalidOperationException, "Expected InvalidOperationException!");
}
// Validate that we didn't automatically move on to the next reader when we
// hit an exception whilst reading the column and that
// an exception from a second Read() call is stored and the reader is closed
multiShardDataReader.Read();
Assert.AreEqual(multiShardDataReader.MultiShardExceptions.Count, 1, "Expected exception to be recorded");
Assert.IsTrue(mockReader1.IsClosed, "Expected reader to be closed!");
// Validate we immediately moved on to the next reader
multiShardDataReader.Read();
Assert.IsTrue(movedOnToNextReader, "Should've moved on to next reader");
}
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();
}
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();
}
}
// 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));
}
}
/// <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);
}
/// <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 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 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);
}
}
}
}
}
