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 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/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 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 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 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
};
// 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.");
// our shard map manager db is ShardMgtDB
Sharding sharding = new Sharding(server, shardmapmgrdb, connStrBldr.ConnectionString);
sharding.RegisterNewShard(server, shard1, connStrBldr.ConnectionString, tenantId1);
sharding.RegisterNewShard(server, shard2, connStrBldr.ConnectionString, tenantId2);
// 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);
}
}
});
Console.WriteLine();
// 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);
}
}
});
// Multi shard query
Console.WriteLine("****** Query accross all shards for blog titles ***** ");
SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
{
using (MultiShardConnection conn = new MultiShardConnection(
sharding.ShardMap.GetShards(),
connStrBldr.ConnectionString))
{
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT Name FROM Blogs";
cmd.CommandType = CommandType.Text;
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
using (MultiShardDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
var title = sdr.GetString(0);
Console.WriteLine(title);
}
}
}
}
});
Console.WriteLine("Press any key to exit...");
Console.ReadLine();
}
// 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));
}
}
