The Data Development Lifecycle


Like your conventional software developer, the database developer can now enjoy support for each stage of the data development lifecycle. Let's look at the tools and functionality in detail. To do that, you are going to walk through each stage of the lifecycle using the Northwind database (one of the default databases in SQL Server) as an example. Along the way, you will learn the basics of how to use Team Edition for Database Professionals, and will see some of how it integrates into Team Foundation Server.

Implementation

The implementation phase consists of defining your database and building all the objects in it, such as tables, views, and stored procedures. Team Edition for Database Professionals helps you do this by storing your schema in a new Visual Studio Project, called a database project. It stores all the information about your database as SQL files, making them easy for database professionals to understand and modify.

Important

Team Edition for Database Professionals does not support the use of graphical designers at this time. You must edit the raw SQL code to make all database changes. Support for graphical designers will be added in a future release.

For this example, you are going to see how to create a new database project, and then import the schema of an existing database into that project. We then cover how you can use the database project to manage your schema information, including how to use version control and work item tracking. Finally, you'll see how you can modify your database schema by adding a new table to your database.

Creating a Database Project

First thing you need to do is create a new database project.

  1. Open Visual Studio, and select FileNewProject. This opens the New Project window, as shown in Figure 8-2.

    As you can see, there is a new section under Project types called Database Projects, and a section under that entitled Microsoft SQL Server. Team Edition for Database Professionals installs four templates for you to use, depending on whether you want to access SQL Server 2000 or SQL Server 2005.

  2. In this case, you want to access the Northwind database on SQL Server 2005, so you select the SQL Server 2005 project template. Name your project MyNorthWindDB, leave everything else as the default, and click OK. This creates your database project., which you can see in the Solution Explorer.

image from book
Figure 8-2

Once you have created your database project, you can begin creating new database objects, or you can import the schema of an existing database. You are going to do the latter.

Importing Your Database Schema

Next, you want to import the schema of the Northwind database.

  1. To do this, right-click the MyNorthWindDB project in the Solution Explorer, and select Import Database Schema. Figure 8-3 shows the Import Database Schema into Database Project window that opens.

  2. In this window, you define the database from which you want to import the schema, and the database project where you wish to put that schema. You can click the New Connection button to set up a connection to your source database. In this instance, you are connecting to the Northwind database, and are going to store the database schema in the MyNorthWindDB database project. Click OK, and the process will proceed. This could take anywhere from a few seconds to a few minutes, depending on how complex your database is, so be patient.

image from book
Figure 8-3

As you can see from Figure 8-4, all the database information, including tables, views, and stored procedures, has been added to your database project. Also in this figure you can see the SQL statement representation of the Categories table from the Northwind database.

image from book
Figure 8-4

The cool thing is, all this database schema information is stored simply as multiple SQL statements in files. This makes it easy for a database professional to understand and modify information as needed.

Managing Your Database Schema

At this point, you have a database project that represents your Northwind database and contains the latest database schema of that database. Now you can begin making the database changes that are necessary for your project. However, before you do that, let's look at some of the ways you can manage your schema using the database project.

  1. The first thing you will probably want to do is add your database project into the version control system. This allows you to track all changes made to any of your database objects. All you have to do is right-click the MyNorthWindDB project in Solution Explorer and select Add Project to Source Control. After all, the database project is just like any other project in Visual Studio, a collection of files. This opens the Add Solution to Source Control window, where you can select the Team Project you want to add your database project to, its location in the version control system, and the associated workspace.

  2. Once you have added the files to the version control system, you need to check-in for the first time. Open the Pending Changes window and click the Check In button, to finish adding the files to the version control system. Your database schema is now under version control, and any changes you make to the schema will be tracked.

As you would suspect, you have access to all the version control features for this project, including branching, shelving, and associating database schema changes with work items. The database project is just another project in Visual Studio, so you access all these features the same way you always would.

There are three important folders in your database project. The Data Generation Plans folder contains any information related to data generation, which is covered later in this chapter. The Schema Objects folder holds all the objects that make up your database, such as tables and stored procedures. To add a new object, simply right-click the appropriate folder and select Add. To edit an existing object, double-click the object and it will open in the editor, where you can modify the SQL code. The Scripts folder contains SQL scripts that can run before or after you build your database project.

Modifying Your Database Schema

Now that you have imported your Northwind database, you need to add a new table to the database project.

  1. In the solution explorer, under the MyNorthWindDB project, right-click the Tables folder located in the Schema Objects folder, and select AddTable. This opens the Add New Item window, where you can select the Table template, and give your table a name.

  2. Go ahead and name your table CreditCard. The table is added in the Tables folder, and an editor window opens in Visual Studio with a default table setup.

  3. Modify the table to reflect Figure 8-5.

    As you can see, you are creating a table with a single column in it, called cc_number. This column will contain credit card numbers. Later in this chapter, you are going to learn how to create a custom data generator,to generate credit card numbers for testing. And yes, while this is a rather simple and incomplete example, it gives you a great jumping off point to delve into this more.

  4. Once you have modified your table, click the save button. This new table has now been saved into your database project. If you have added your database project to your version control system, don't forget to check in your new table. Remember, you can also associate this check-in with any work items that are relevant. This table currently exists only on your local machine in your database project. Later in this chapter, you learn how to deploy your new database changes to either a test or a production server.

  5. Let's also make a new stored procedure that will retrieve all the credit card numbers from our CreditCard table. Right-click the Stored Procedures folder in Solution Explorer, and select AddStored Procedure. Name the stored procedure CCSelectAll. Figure 8-6 shows the code for the stored procedure.

image from book
Figure 8-5

image from book
Figure 8-6

This stored procedure will retrieve all the rows from the cc_number column of the CreditCard table. Don't forget to save this stored procedure, and check it back into the version control system.

Building and Deploying

Before you can deploy your new database schema or update your existing database with your changes, you need to build the database project. This creates all the SQL scripts necessary to create your database schema, and verifies all the objects are valid. Once the database project has been successfully built, you can then deploy it to your test server or production server, as the case may be.

Building/Deploying Your Database Project

Before you build your database project, you need to define some project settings.

  1. Right-click your database project in Solution Explorer, and select Properties to open the project properties window, shown in Figure 8-7.

  2. On the Build tab, you specify the target database you want to deploy to. In this case, you are specifying a new database called NorthWindTestDB. When this database project is compiled, the SQL scripts will reflect that the database is to be deployed to the test server into a new database named NorthWindTestDB.

  3. Save your changes and close this window.

  4. To actually build your database project, right-click the database project and select Build MyNorthWindDB. The output window shows the success or failure of the build. For your test project, it should succeed. Once the project has been built, you can deploy it by right-clicking on the database project and selecting Deploy Selection. Go ahead and do this for the MyNorthWindDB project.

    If the deployment is successful, you will now see a new database on your SQL Server, named NorthWindTestDB, which contains your CreditCardNumber table. You have now created a test database for you to use with your unit testing.

image from book
Figure 8-7

One thing to consider is how you are going to deploy to an existing database. Some changes you make may require tables to be dropped and recreated, which can lead to a loss of data from that table. Team Edition for Database Developers is smart enough to realize when that might happen, and will warn you appropriately, but you should take care when deploying your updated schema, to ensure there is no data loss.

Team Edition for Database Developers allows for scripts that can run before your build script deploys, and after your build script has deployed. These scripts are not part of your database schema, but can be used to execute certain functionality during the deployment process, as necessary.

Important

James Manning has blogged about how to script and monitor Team Foundation Server database jobs using PowerShell and SQL Server Management Objects (SMO). You can learn more by reading his blog post: http://blogs.msdn.com/jmanning/archive/2006/08/29/730482.aspx

Comparing Schemas

Almost everyone has run into the situation where they have made changes to their local copy of the database while doing development, but can't remember what all their changes were. Team Edition for Database Developers also includes a nice feature called Schema Compare that helps solve this problem.

  1. To get started, select DateSchema CompareNew Schema Comparison. This opens the New Schema Comparison window, shown in Figure 8-8.

  2. In this dialog, you can select your source schema and your target schema. Your source schema is usually your database project, though you could select another database if you wanted to compare the two. In the above example, you have selected your database project as the source schema, and the original production database as the target. Clicking OK begins the comparison. This may take anywhere from a few seconds to a few minutes, depending on the number of changes you have made, and the size of your database.

    Once the comparison is complete, the results will be displayed as shown in Figure 8-9.

    As you can see, there is a new table, CreditCardNumber, that does not exist in the target database, so the system is going to create this item for us. As well, it will create the stored procedure you created earlier as well. By selecting particular objects, you can view the differences between the source and target schemas. As well, at the bottom of the window you can view the SQL script that is being created dynamically for you, to make these changes in the target schema.

  3. When you are satisfied, click Write Updates to make these changes to your target data source. You can also click Export to Editor to export this script to an editor, allowing you to modify it manually.

image from book
Figure 8-8

image from book
Figure 8-9

Testing

Team Edition for Database Professionals has support for unit testing built into it, allowing you to build detailed tests to verify the correctness of your database. It also supports refactoring, allowing you to change the name of a database column or table, and propagate that change throughout all the stored procedures and unit tests. Finally, a feature that will probably get a good bit of use is the data generation capabilities. Team Edition for Database Professionals will automatically generate test data for your database, freeing you to focus on more important testing matters.

Refactoring

Refactoring allows you to change a table name, and have that change automatically propagated throughout the entire database project, including your unit tests. This helps prevent errors from cropping up later in your application, due to a database change. For this example, let's change the name of the table you just created, CreditCard, to be CreditCardNumbers.

  1. Right-click the CreditCard table and select Rename. This opens the Rename dialog box, as shown in Figure 8-10.

  2. In this window, you enter the new name of the table, in this case CreditCardNumber. If you uncheck the Preview Changes check box, then the changes will be automatically propagated throughout your project. If you leave the box checked, then you will see all the changes that will be made and where they will be made. It is recommended that you leave this box checked, so you can preview the changes. Click OK to continue. Figure 8-11 shows the Preview Changes window that opens.

  3. This window shows you all the different database objects that will be affected by the rename. In this case, the Create Table statement for the table and the CCSelectAll stored procedure will be changed. If you decide you do not want to make this change, you can click Cancel. If everything looks good, and you want to proceed, simply click Apply. This propagates the change throughout all the objects shown in the preview window.

image from book
Figure 8-10

image from book
Figure 8-11

While not exactly refactoring, so to speak, Team Edition from Database Professionals will warn you if you change a column in a table, and if that change will affect other objects in the schema. Double-click the CreditCardNumber table to open it in the editor. Change the column cc_number to be credit_card_number. When you save this change, the error list window opens, showing that the CCSelectAll stored procedure now contains an invalid column name, as shown in Figure 8-12.

image from book
Figure 8-12

To fix this, open the CCSelectAll stored procedure, and change the column name to be credit_card_number. When you save your changes to the stored procedure, the error will disappear from the error list.

Data Generation

Data generation is probably the coolest and definitely one of the most useful features of Team Edition for Database Professionals. Data generation allows you to fill your database with test data. This data is based off the different column types in each of your tables, but it is gibberish, meaning that you can get meaningful results from the data, to verify your application is working, without having to worry about using sensitive data. No more siphoning data from your production database, or spending hours entering test data into your test database. By using data generation, with a few simple clicks, you can be up and running with test data.

Using the Data Generator, you can create either random or nonrandom data. You can run the data generator by itself, to populate your database, or you can tie it together with your unit tests, setting up the data in the database appropriately before the tests are run. The Data Generator supports the use of regular expressions, allowing you to format the data as you would like it. And finally, the Data Generator is fully extensible, allowing you to build your own data generator to create test data as you see fit.

Data generation starts with a data generation plan.

  1. Right-click the Data Generation Plans folder in your database project, and select AddData Generation Plan.

  2. In the Add New Item window, enter a name for your plan, or accept the default. For this example, change the name to be TestDataPlan, and click Add. The data generation plan is created and opens in Visual Studio, as shown in Figure 8-13.

    This window contains a lot of information. The top portion of the window shows all the tables in your database schema. It also shows how many rows will be added to this table. If you have a column in a table that is a foreign key in another table, the Related Table column allows you to tie those two tables together, making sure the foreign key matches correctly. Moreover, the Ratio to Related Table column allows you to specify the number of rows in the primary and any related tables, such as a one-to-one correspondence. You can select which tables to generate data for by selecting or deselecting them in the check box to the left. If the table you uncheck has a foreign key relationship to another table, you will not be able to completely uncheck the table until the related table is also unchecked. That is a safeguard to make sure there is data in all the required tables.

    As you can see in Figure 8-13, the Categories table is selected in the top of the window. At the bottom of the window, you can see all the columns in the Categories table. You can select which columns you want to generate data for. You can see the data type of the column, and can specify the generator used to generate the data for that column. Team Edition for Database Professionals includes several data generators out of the box. Some of the included generators are ones for creating integers, floating point numbers, and strings, just to name a few. If you select a column, you can view the property information for the data generator selected for that column.

  3. Select the CategoryName column of the Categories database, and view the Property window, as shown in Figure 8-14.

    The property window allows you to configure the different aspects of the data generator for that column. For the above column, you can set the minimum and maximum length, and whether the column is unique or not. As well, you can specify how many of the column entries should be null, and can set the seed value that helps to generate the random values.

  4. Uncheck all the tables in this data generation plan, and then check the CreditCardNumber table, so it is the only one selected. Select the CreditCardNumber table you created earlier. Select the credit_card_number column, and change the Generator value to be RegularExpression.

  5. You can use regular expressions to create your random data in the database, allowing you more control over the data. For this example, you want numbers that are 16 digits, so you are going to use a regular expression to accomplish this. Go to the Properties window for this column, and set the Expression value to [0–9]{16}. This will generate a 16-digit number, which is what you want.

    But how can you be sure the data will look like you want it to? If you look at Figure 8-13, you'll notice a tab at the bottom called Data Generation Preview. This tab shows you what the generated data will look like, before it is actually created, enabling you to verify it is how you wanted it. Figure 8-15 shows an example of this tab for the regular expression you just created.

  6. Notice the data are 16-digit numbers, similar to credit card numbers. However, some of the numbers are invalid, as credit cards do not normally start with a one or a two. You will address this later in the chapter when you develop a custom data generator for credit card numbers. For the time being, accept this limitation.

image from book
Figure 8-13

image from book
Figure 8-14

image from book
Figure 8-15

You can create a data generation plan that contains all the tables in the database, a plan for each individual table, or any combination in between. Use data generation in a way that makes the most sense to you. Remember, if you have added your database project to the version control system, your data generation plans are automatically version controlled as well, so make sure you check them in regularly.

At this point, you have the data generation plan, but you have not actually created or stored any data in a database.

  1. To actually create the test data, open the data generation plan, then select DataData GeneratorGenerate Data from the menu. You will be prompted to select the database you wish to generate data for.

    Important

    Whatever you do, do not select your production database. If you do, you could overwrite all your production data with your generated data. That would be a bad thing.

  2. Select the NorthWindTestDB database that you created earlier as the target for your data generation, and click OK. You are asked if you want to clear the contents of the selected tables before continuing. Select Yes. The data generation plan thinks for a few seconds, and then displays the success or failure of the generation.

If you now open a SQL Query window and select the rows from the CreditCardNumber table, you will see the randomly generated data that was added to the table when you ran the plan.

Unit Testing Your Schema

Unit testing in Team Edition for Database Professionals works similarly to unit testing in the other versions of Visual Studio Team System. Let's create a unit test for the CCSelectAll stored procedure you created earlier.

  1. In the Solution Explorer window, right-click the CCSelectAll stored procedure, and select Create Unit Tests. This opens the Create Unit Tests window, shown in Figure 8-16.

    You can select multiple stored procedures if you want.

  2. For this example, name the test project MyNorthwindTestProject, make sure the CCSelectAll button is selected, and click OK. This starts the creation of the test project. You are prompted to select a database to execute your unit tests against, as shown in Figure 8-17.

  3. Select the NorthwindTest database you created earlier. You can also choose to have your database schema deployed for you before the tests are run, and you can choose to have your data generation plan run before the tests are run. For your example, leave these unselected. Click OK to generate your test project.

    The unit test you have created will open for you automatically, as shown in Figure 8-18.

    As you can see, the unit testing for stored procedures is slightly different than unit testing regular code. Initially it provides a nicer, high-level way to look at the testing, but don't worry, you can delve down into the C# code to create some detailed tests. For this example, you are going to stick with the high-level view. This unit test is going to call the CCSelectAll stored procedure. Pretty simple really. Notice that you can declare some test conditions for which you want to test. Team Edition for Database Professionals ships with several default test conditions, and you can add more if you want.

    For this example, you are expecting a nonempty result set to be returned from the call to this stored procedure. You can easily add multiple test conditions by selecting them from the dropdown list box and clicking the plus sign.

  4. To run this test, simply open the Test View window by selecting TestWindowsTest View. Right-click the dbo_CCSelectAllTest and click Run Selection, just as you would any other unit test. As Figure 8-19 shows, the test has run successfully.

image from book
Figure 8-16

image from book
Figure 8-17

image from book
Figure 8-18

image from book
Figure 8-19

At this point, you can do anything else you would normally do, including publishing the test results to Team Foundation Server.



Professional Team Foundation Server
Professional Team Foundation Server
ISBN: 0471919306
EAN: 2147483647
Year: 2004
Pages: 168

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net