Partial Projects

Artikel
10/05/2008

The Visual Studio Team System 2008 Database Edition GDR release introduces two new ways on how to share information between projects, partial projects and composite projects. This blog post will go in to how to use partial projects, composite projects will be discussed in a later post.

When we started talking to existing users of the current database project system and the use cases involved it became clear that many had a need to share actual implementation between different database projects. One common scenario we were confronted was is what I will call the "baseline" project. All projects derive from a common set of objects inside a base project. Some users solved this by copying the implementation files; some used clever source code control mapping tricks, none provided an easy answer to the problem, hence the idea of "Partial Projects" was born.

Partial projects allow code sharing between projects by including files from different project. The "base" project exports a list of files that can be included by the "derived" project. This list is create by the "base" project and materialized as a .files file.

The "derived" project includes the .files file. The result is that the code exported by the base project is included as-is in the derived project. The source code control ownership remains with the parent (base) project. So the files are included in a read-only fashion. This new feature promotes code re-use while maintaining single sourcing of artifacts and resulting in a single deployment where the derived projects includes the base project artifacts.

Partial Projects Example

Lets walk through a simple but typical example. We will create a new Visual Studio Solution, named PartialDBProj, to which we will add two new Database Projects, BaseDB and DerivedDB.

Step 1: Create PartialDBProh solution file

File->New Project->Other Project Types->Visual Studio Solutions
Name: PartialDBProj

Step 2: Add Database Projects to Solution

File->Add New Project->Database Projects->SQL Server 2008->SQL Server 2008 Database Project
Name: BaseDB
File->Add New Project->Database Projects->SQL Server 2008->SQL Server 2008 Database Project
Name: DerivedDB

The end result should like something like this:

Step 3: Add Tables to BaseDB project

Add 3 tables to the BaseDB project, either using Add Item in Solution Explorer or through the Schema View, just use the default names and definition [dbo].[Table1], [dbo].[Table2] and [dbo].[Table3]

Step 4: Export two table from the BaseDB project

In Solution Explorer, select the files you want to share/export. In this example select Table1 and Table3, right click and choose the "Export As Partial Project..." option

You will be asked where to save the .files file, the default location is inside the root of the exporting project. You will also be asked if you want to add the .files file to the exporting project file, if you choose to do so your Solution Explorer looks something like this:

Step 5: Open the .files file

Right click on the BaseDB.files files and choose Open. This will open the file and will show the XML contents of the file.

    1: <?xml version="1.0" encoding="utf-16"?>

    2: <Project xmlns="https://schemas.microsoft.com/developer/msbuild/2003">

    3:   <!--

    4:       Before you import this file, you must define the 'BaseDBBasePath_5_10_2008_05_12_18' property to point to the directory where this file is located.

    5:       Failure to define this property will result in a build error.

    6: -->

    7:   <ItemGroup>

    8:     <Build Include="$(BaseDBBasePath_5_10_2008_05_12_18)\Schema Objects\Schemas\dbo\Tables\Table1.table.sql">

    9:       <Link>BaseDBBasePath_5_10_2008_05_12_18\Table1.table.sql</Link>

   10:       <SubType>Code</SubType>

   11:     </Build>

   12:     <Build Include="$(BaseDBBasePath_5_10_2008_05_12_18)\Schema Objects\Schemas\dbo\Tables\Table3.table.sql">

   13:       <Link>BaseDBBasePath_5_10_2008_05_12_18\Table3.table.sql</Link>

   14:       <SubType>Code</SubType>

   15:     </Build>

   16:   </ItemGroup>

   17: </Project>

As you can see the .files file contains MSBuild links.

Step 6: Add Views to DerivedDB project

Add 2 views to the DerivedDB project, either using Add Item in Solution Explorer or through the Schema View.

Add [dbo].[View1] using the following definition:

    1: CREATE VIEW [dbo].[View1]

    2: AS

    3: SELECT  [T1].[column_1],

    4:         [T1].[column_2]

    5: FROM    [dbo].[Table1] AS [T1]

Add [dbo].[View3] using the following definition:

    1: CREATE VIEW [dbo].[View3]

    2: AS

    3: SELECT  [T3].[column_1],

    4:         [T3].[column_2]

    5: FROM    [dbo].[Table3] AS [T3]

Now that you added the views to the DerivedDB project, you will notice these views are in an error state

Step 7: Import the Partial Project

Now it is time to import the partial project in to the DerivedDB project. In Solution Explorer, select the DerivedDB project node, go to the Project menu and choose "Import Partial Project...". You can also right click on the project node and choose "Import Partial Project.
You first get a notification that adding or removing partial project will cause project unload - load to occur, to add the MSBuild includes to the project
If you answered Yes to the previous question you will get a file selection dialog, which you use to select the BaseDB.files file
When you select the file, the DerivedDB project will be unloaded, we add the link to the BaseDB.files file and reload the DerivedDB project. After the reload is completed you will see the included objects in Solution Explorer. The icon with the arrow indicates the is a linked object.
Inside "Schema View" you will now find Table1 and Table3, just as if you added them to the project yourself.

Step 8: Build the DerivedDB project

If you now Build and Deploy the DerivedDB project you will find that Table1 and Table3 are part of the build output (DerivedDB.dbschema) and therefore included in the deployment.

Including objects through a partial project have the same effect as adding the objects to the project directly. Partial projects are effectively includes of sets of files, where a set can contain 1 to N number of files.

Step 9: Remove the partial project

The last step is to remove the partial project from the DeviredDB project, by selecting the project node inside Solution Explorer and choosing the "Remove Partial Project..." option from the Project menu or by right clicking on the project node inside Solution Explorer and selecting the "Remove Partial Project..." options.
You will receive the same question when you added the project project, asking you to confirm if it is OK to unload and reload the project file.
Since you can have more then one Partial Project imported you need to select which one to unload, by selecting the ones to unload.
When you select OK, the DerivedDB project will be unloaded, we remove the link and reload the project, after which the Table1 and Table3 objects are no longer part of the DerivedDB project and the views will be in an error state again.

That concludes the example. We have seen how to:

Export a Partial Project
Import a Partial Project
Remove a Partial Project

Conclusion

Partial Projects are a simple mechanism to share implementation between project, without having to transfer the source code control ownership between the projects and resulting in a single deployment unit from Build. Conceptually "Partial Projects" are comparable with C/C++ #include files.

I hope this post gives you enough information to start using this great new feature.

-GertD

Comments

Anonymous
October 05, 2008
PingBack from http://www.easycoded.com/partial-projects/
Anonymous
October 06, 2008
we use lots of synonyms and cross-db references. I'm curious if there is a way to resolve synonyms (SR0029 error) without manually specifying DB/cmd variables...
Anonymous
October 14, 2008
GertD on Partial Projects Shai Raiten on Fixing Web Test Recorder Bar and How To: Create Generic Work...
Anonymous
November 25, 2008
Finally the moment is there, the final version of the Visual Studio Team System 2008 Database Edition
Anonymous
November 25, 2008
The VS Database edition team has just released the golden version of the Visual Studio Team System 2008
Anonymous
November 25, 2008
The VS Database edition team has just released the golden version of the Visual Studio Team System 2008
Anonymous
November 25, 2008
The comment has been removed
Anonymous
December 04, 2008
Visual Studio Team System 2008 Database Edition GDR - RTM - очень полезная новость, для тех кто разрабатывает...
Anonymous
December 08, 2008
This news is about two weeks old but better late than never :) I'm trying to put together a VSTS FireStarter
Anonymous
February 07, 2009
[ Nacsa Sándor , 2009. február 8.] Ez a Team System változat az adatbázis változások kezeléséhez és teszteléséhez
Anonymous
March 09, 2009
The comment has been removed
Anonymous
April 28, 2009
Still looking for the Composite Projects blog post that you mentioned would be forthcoming at the begin of this post.
Anonymous
February 20, 2011
I have exported and imported a partial project and it is working great! But now I want to add a new object to my Base Project. How can I reflect this change also in the Derived Project?
Anonymous
May 03, 2012
I don't get it. BaseDB and DerivedDB are different db's right? So why are you not using a three part name in the view such as '......FROM [basedb].[dbo].[Table1]'

Freigeben über