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); } } } }
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); }
/// <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 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 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()) { } } } } }
// 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 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 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 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 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(); } }
/// <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 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 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 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); }