How to setup a solution that has conditional objects?

Question

I have a deployment scenario that I am not sure is possible or if so, how to setup as a VSTS DB Edition project.

I want to deploy a database that, depending on a variable (.SQLCMDVARS) may or may not deploy certain objects, or have a different definition.

 

A sample scenario might be:

 

Regardless of the condition, 10 base tables will always be deployed.

 

If Condition1 = true, then deploy additional tables tableA and tableB with 5 columns

If Condition1 = false, then deploy additional tables tableC and tableB with 4 columns

(B being deployed whether true or false but with a different definition)

I initially thought I should:

1) create one database project with the 10 base tables,

2) create another database project with stuff for Condition1 = true

3) create another database project with stuff for Condition1 = false

 

I thought that projects 2 and 3 would be a conditional reference, something I am not sure is possible.

 

The final thought was that if the variable were set true I could do a schema compare against a database that included tableA and and tableB with 5 columns and see not deltas.

 

Is this possible?

Thanks for reading,

Robert Towne

 

---

Robert

Answer 1

The way I read it there are two aspects to your question:

1) Conditionally include an objects as-is
2) Conditionally change the shape of an object

You can included objects in to your projects in 3 ways:
* include files
* partial projects (which is nothing more then a way to group file includes through a file that describes the files to include)
* composite projects

The main difference between partial and composite is that partial you end up with a single deployment, since you are including the source code representation, with composite projects you are including the shape definition only, so you end up with 2 deployments, first the base objects and secondly with the additional objects

Making this conditional is tricky and has to be done at the MSBuild level, and cannot be achieved through SQLCMD variables. SQLCMD variables are nothing more then #define statements that are replaced with the literal value at deployment time. That means they cannnot be used to change the shape or conditionally determine if an object is part of the deployment.

The fact if something is part of the deployment is deteremined by the fact if something is in the build or not. This can be influenced through the "Build Action" on a file, or if you understand MSBuild well enough through conditions which can then be tied to a configuration. All this is manual labor.

If you have two shapes you would have to arrange for this as well at the MSBuild level, where you conditionally include version 1 or 2 of the shape at the file level.

I hope this makes some sense, happy to provide more details.

---

GertD @ www.DBProj.com

Answer 2

Gert, I think I understand - I'll need to play with a project and can translate your suggestions into something concrete.  If I have more questions I'll reply, and when I knock it out I'll try to post details.

Thank you very much,
Robert

---

Robert