Refactoring
Karen: Can you talk about refactoring?
Gert: You can start by building a database project and reverse engineer it to a database you already have. You can put multiple objects in the project and start changing the objects. Some changes are not just renaming; they're really refactoring.
For example, say you have a reference to table Sales in your stored procedure. Inside your view, you rename Sales to Orders. VSDB Pro will go through your schema and make changes so that everywhere you have that table, it reflects the rename of Orders instead of Sales. If it finds a stored procedure or function that uses those names, it will also recreate them.
Richard: We'll also do validation. If you make a constraint that's not valid, we'll give you a visual indicator in your project, identifying which things have been invalidated by your changes. So say you break a table—like you delete the contents of a table and five foreign keys referenced it. We'll visually indicate which tables' foreign keys the changes have broken.
Karen: What are the limitations on refactoring in this first version?
Gert: In this release, we're restricting refactoring to renames. We will not do, for example, type changes (e.g., I changed this column, which is bound to this parameter, so I'll change the Declare statement from small int to big int). That we couldn't bite off due to time constraints. We'll do that next time.
Unit Test
Karen: Can you explain what you're doing with unit testing?
Gert: We're also adding unit testing for database objects on top of the MSTEST infrastructure. So if you have a stored procedure, you can write a unit test. We have two flavors. First, you can take the SQL-only approach and write a T-SQL-based unit test, have validations, and make that part of your unit test list and have that be part of your build system. Second, since this is all integrated into the existing test infrastructure, app developers can take the code approach and use C# or VB.
Richard: Let me give you a concrete example of a unit test. I created a unit test for a stored procedure with Northwind. I said, "Get categories by count," and I added two test conditions: Make sure it returns a nonempty result set, and make sure the row count is nine. When I ran the test, it was able to go against a defined connection, get the row count back from the stored procedure execution, tell me about the success or failure of the test, and then generate a report of the test results. Because it's integrated with the rest of the unit tests, I could have hundreds of tests all together in the same project.
Karen: So this is moving toward the integration with the app developer that you're aiming for.
Richard: Yes. When we talk about integration with the app developer, each test is a different file in a project, so I could have a database unit test (or five or ten) and then I could have some C# unit tests or some VB unit tests, all in a project. And I could have the unit test project and the database project in the same solution file, check them in, and check them out together. So now I'm a database developer, I go in and make a change to a stored procedure. I deploy that stored procedure out to a test system that has some data and run my unit tests on it and know whether the change I just made broke it.
Gert: It gets better. Wouldn't it be great if you could unit test against real data? So we add another tool, the Data Generator, to generate meaningful data. Another advantage of our Data Generator: We can generate the same test set over and over. So this is great for unit testing.
Richard: And because we're mirroring the stats, we can even get similar if not identical plans, as you'd get in a production server.
So now I define my project in VS. I build and deploy that project to a test SQL Server. I use DGEN to populate it with data. Then I run unit test against the newly populated database. And I literally just take all the pieces of this project, flow them together, and now I have a repeatable test cycle of my version of the truth being deployed repeatedly as I make changes. Then I can measure specifically with every single stored procedure, every function, row counts, performance of my regular SQL queries because I can run those as scripts. And I can repeatedly deploy, make changes, deploy, test out, and have each run checked back into source control.
I can actually measure what change I made: What was the performance or data generation impact? Did queries slow down? Did I get more data than I expected? And because all this is checked in, I can repeat those experiments every time. Now we have data to back all those questions up.
Gert: To complete this, we have the ability to compare schemas. You can compare the project with what you have in production. You can compare a test implementation and a production implementation. The ability to compare schemas is really important to us.
The Right Track
Karen: What has been the response from customers who've used VSDB Pro?
Gert: The general feedback is that nobody provides an integrated solution to do this today. That's exactly what we're doing. VSDB Pro's refactoring is unique. The way we do data generation is unique. We're meeting the need by providing all these things together as an integrated solution working in an integrated process.
End of Article
Prev. page
1
2
[3]
next page -->
