Today, I am talking about how VS2010 Database Development Tools resolved a potential "Catch22" dependency resolution. To start with, here is the problem:
I have a database that has dependencies on another database. I will call then "Source" and "Main" database. "Main" database being the one that has dependencies on the "Source" database. Since I did not like to have explicit reference to "Source" database in my "Main' database, I created synonyms for all objects that needed to be referred into the source database.
So, I have synonyms that go like this:
CREATE SYNONYM [Main].[DimCustomer] FOR [$(AdvWorkLt)].[DW].[DimCustomer];
CREATE SYNONYM [Main].[DimProduct] FOR [$(AdvWorkLt)].[DW].[DimProduct];
…
The [$(AdvWorkLt)] is a SQLCMD variable in my database project that points to my Source database:
Next, I created views on top of these synonyms:
CREATE VIEW [dbo].[DimCustomer]
AS SELECT * from [Main].[DimCustomer]
Since I did not like to do a "Select *" in my view, I used the refactoring capabilities of Database Development Tools to expand the wildcard:
The refactoring action gave me the following SQL:
CREATE VIEW [dbo].[DimCustomer]
AS SELECT [Main].[DimCustomer].[IDCustomer],
[Main].[DimCustomer].[CustomerID],
[Main].[DimCustomer].[Title],
[Main].[DimCustomer].[FirstName],
[Main].[DimCustomer].[MiddleName],
[Main].[DimCustomer].[LastName],
[Main].[DimCustomer].[Suffix],
[Main].[DimCustomer].[CompanyName],
[Main].[DimCustomer].[ADWSalesPerson],
[Main].[DimCustomer].[EmailAddress],
[Main].[DimCustomer].[Phone],
[Main].[DimCustomer].[CustomerEffDate],
[Main].[DimCustomer].[CustomerEndDate],
[Main].[DimCustomer].[CustomerCurrent],
[Main].[DimCustomer].[LoadID]
FROM [Main].[DimCustomer]
Now, I questioned how the project would looks like when it would create a deployment script. To be honest, I had some doubts on how Database Development Tools would be able to render the dependencies between the synonyms and the views. I was in for a pleasant surprise because the dependencies has been resolved perfectly in the resulting deployment script:
PRINT N'Creating [Main].[DimCustomer]...';
GO
CREATE SYNONYM [Main].[DimCustomer] FOR [$(AdvWorkLt)].[DW].[DimCustomer];
GO
PRINT N'Creating [dbo].[DimCustomer]...';
GO
CREATE VIEW [dbo].[DimCustomer]
AS SELECT [Main].[DimCustomer].[IDCustomer],
[Main].[DimCustomer].[CustomerID],
[Main].[DimCustomer].[Title],
[Main].[DimCustomer].[FirstName],
[Main].[DimCustomer].[MiddleName],
[Main].[DimCustomer].[LastName],
[Main].[DimCustomer].[Suffix],
[Main].[DimCustomer].[CompanyName],
[Main].[DimCustomer].[ADWSalesPerson],
[Main].[DimCustomer].[EmailAddress],
[Main].[DimCustomer].[Phone],
[Main].[DimCustomer].[CustomerEffDate],
[Main].[DimCustomer].[CustomerEndDate],
[Main].[DimCustomer].[CustomerCurrent],
[Main].[DimCustomer].[LoadID]
FROM [Main].[DimCustomer]
GO
In conclusion, VS2010 Database Deployment Tools is able to resolve object dependencies gracefully. I was a bit skeptical about it but now, I am convinced more than ever that this is the best tool to manage databases.
Today I will talk about how you can manage Oracle with Toad Extension plugin for Visual Studio 2010 database development tools. As mentioned in a previous post, this plugin had two flavors; freeware and commercial. The following table shows the differences between both versions.
Features | Commercial | Freeware |
Create Project | X | X |
New Item templates | X | X |
Import Schema | X | X |
Import Script | X |
|
Deploy to Database | X | X |
Deploy to Script | X |
|
Schema Compare | X |
|
Data Generation | X |
|
Unit Testing (v1.1) | X |
|
Refactor | X |
|
Table Editor | X |
|
Property Grid | X | X |
Schema Viewer | X | X |
Dependency Viewer | X |
|
TFS Headless Build | X |
|
Source Control Integration | X | X |
The extension is easy to setup and its usage is much like SQL Server counterpart. Once installed, there is a new menu item in VS2010:
Like SQL Server, there is a wizard that helps you setting up a database project and optionally imports an existing schema (User) structure.
Once the project has been created, you can reverse engineer an existing schema. Even though you used the project wizard, you can import several schemas to the project. And, one thing you need to be aware of is that the current version of the extensions will not prevent you to import one schema twice. The team knows about this and it should be corrected pretty soon.
The project properties are very simple in a sense that they are limited compared to what we have with SQL Server. This can be very easy to explain since we are at V1.0 of the product. One thing I would like to have is the option to drop or not existing objects in the database if they do not exist in the project.
One thing I like a lot is the comment folder in the solution explorer. This folder contains all comments that the data architect, the database administrator or the developer added to a table or its columns. Also, all sql files in solution explorer have the ".osql" extension that presumably stands for "Oracle SQL".
Files are organized by object types. As I told my Quest contact, it would have been nice to have the option to organize files by schema, which is the default behavior with SQL Server.
The schema view is about the same as its SQL Server counterpart. Rename refactoring is fully supported as well as dependency viewer which I found very powerful and well implemented within Oracle projects. In Oracle, a schema is not the same as SQL Server schema. It's a user:
When we open the HR schema's file, it displays the following script:
The reason I am bringing this is because it explains why "Move to schema" refactoring has not been implemented. A schema in Oracle is a user, not the same abstraction layer as found in SQL server.
In my next articles on the topic, I will talk about data and schema comparison as well as schema deployment. Stay tuned J!
