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)); } }