We decided to use separate table, view, and stored-procedure scripts primarily because of the nature of objects involved and also based on the relative change frequency of each type of object. Note in Figure 2 that to keep production tables from being dropped, we didn't generate DROP <object> commands in the Formatting tab. Because we would be transferring changes into production, we had to be careful that the table-generation script didn't have the DROP TABLE option. Stored procedure changes were the most frequent, so we made them a separate category—both individually and as a group. When we had more than 150 stored procedures, dropping and recreating all stored procedures when just one of them had changed was a big pain, so we chose to create one file per stored procedure. However, when we were setting up a new environment, choosing to put all stored procedures in one file (as Figure 3 shows) made sense. We could run just that file to create all the stored procedures.
DatabaseSchemaChanges. This project folder contained scripts of changes made to objects in the base (1.00) version of the database schema. This folder contained ALTER TABLE commands for existing tables and CREATE TABLE commands for new tables. The ALTER TABLE statement was required because changes in schema would be promoted to the production environment, where live data exists, whenever the table structure changed due to client requests. We couldn't afford to drop and recreate production tables.
The DatabaseSchemaChanges folder also contained CREATE <object> commands for all the other types of objects we created in the base schema (e.g., defaults, indexes), with DROP <object> commands preceding the CREATE statement. These objects existed only in the metadata and any change to them was a complete replacement, not an alteration. After changes were captured as a script in this folder, recreation of the entire schema required running not only the scripts in the DatabaseCreation and DatabaseSchema folders but also the script in the DatabaseSchemaChanges folder.
DatabaseAllViews. This project folder contained one script for creating all the views in the database. To generate this script, we used Enterprise Manager's scripting facility, selecting the options as Figures 4, 5, and 6 show. Note that in the Formatting tab, you drop, then recreate views and stored procedures. You can't drop tables because they might contain live data.
DatabaseIndividualViews. This project folder contained one script file per view. This set of scripts was useful in transferring incremental changes. We created the script by selecting the same options on the General and Formatting tabs as in Figures 4 and 5 and selecting Create one file per object on the Options tab.
DatabaseAllStoredProcs. This project folder contained one script for all the stored procedures in the database. To generate this script, we again used Enterprise Manager's scripting ability. This script is useful either when you have to set up an environment from scratch or when you have a large number of changes to transfer from one environment to another. Being able to drop all stored procedures and recreate them by running just one script—rather than running a script for each stored procedure that was modified or added—is convenient.
DatabaseIndividualStoredProcs. This project folder contained one script file per stored procedure. Having one script per stored procedure is useful in transferring incremental changes from one environment to another. Handling stored procedures is much like handling views, except that instead of selecting All Views in the General tab of the Generate SQL Scripts dialog box, we selected All Stored Procedures.
DatabaseDataLoadingScripts. This project folder contained all the scripts, in the form of INSERT statements, for loading initial values into the database's lookup tables. You can generate these statements from an existing table by using database life-cycle-management tools such as Embarcadero Technologies' DBArtisan. You can maintain lookup values in an Excel spreadsheet and use Data Transformation Services (DTS) to transfer them to the database.
Besides these eight project folders, we created three others. The DatabaseDocumentation project folder contained the versions of all documentation pertaining to the database. In the DatabaseBackup project folder, we maintained a snapshot of the database, in the form of a compressed database backup file, for each milestone in the development life cycle. Finally, we moved all the scripts and files that became obsolete during the course of the project to the DatabaseObsolete project folder.
This file structure let us track the versions of every component of the database that we were interested in, along with the reasons for each change (which were annotated in the VSS file properties) for each version. The reason for a version change could be to correct a defect, add functionality, or fine-tune the system. We recorded the dates of these changes along with who made them in a separate database we discuss later.
Figure 7 shows in the VSS Explorer the project folders we created. In VSS terminology, each container of versioned objects is a project—each project can contain sub-projects in a tree-like structure. This naming system was somewhat annoying at first because we wanted to call our overall effort "the project" and the containers for different types of objects "folders" belonging to the project. However, after we got past this peculiar naming convention, VSS was straightforward and consistent to use.
Prev. page
1
[2]
3
next page 
