Support for SSDT projects: new fork

Topics: General
Jul 19, 2012 at 1:30 PM

I've created a new Issue and corresponding Fork for adding NuGet support for SSDT projects, and the contributors docs say to start a new discussion too, so here it is!

I think the text in the Issue says it all as for why I want this: for me, it's more about using NuGet locally than getting database packages from the central repo., although I do like the idea of being able to include a copy of the SqlMembershipProvider schema in my SSDT project just by hitting up NuGet.

So is this something that anyone else would like to see? What about NuGet maintainers: if this fork ends up producing something worthwhile, would it be considered for inclusion in the main distro?

Thanks,
Tom

Jul 20, 2012 at 2:15 PM
Edited Jul 20, 2012 at 2:15 PM

Do you have an estimate of how big or small the changeset would be to support SSDT? If it's small, we'll take it.

Aug 6, 2012 at 9:41 AM

I'd be definately interested in this.

I would like this to allow

  1. Create base folder structures as nuget packages
  2. Package up Sqlcmd script packages (for pre/post deploy use)
  3. Package up utility dacpacs for use the wider team (as per the issue 2439)

I'd be happy to test any progression on this

 

Nov 28, 2012 at 11:36 PM

I'm very interested in this - I was going to start digging into how to make this work.  I'm happy to contribute to development and testing.

Nov 29, 2012 at 8:32 AM

Hi, all. I've actually made some headway with this, but unfortunately other things have taken priority and I haven't done much on it recently. I'll try to upload what I have today (it's not very stable, but there's enough there to get started) and I'll post here when I've done so.

John, that'd be great if you'd like to help out. If you're already familiar with the guts of NuGet, great, you'll probably be better at this than me. If not, it might be a good idea to take a look at what I've already checked in to see where I'm going with this. The main problem is that SSDT projects do not (at this time) expose an API that we can use to add references, which is pretty fundamental, really! The best solution I've found so far (and this is also what the SSDT developers suggested) is to modify the .sqlproj file in the background and force a reload. Modifying the project has been fine, but getting Visual Studio to reliably refresh it has been difficult, and I'm still not sure it works very well.

Anyway, please do grab a copy of what I've got, and see what you can get going!

Cheers,
Tom

Dec 14, 2012 at 11:23 AM

Okay, please ignore most of what I wrote in the last post! I've now pushed my code to the fork, and it's working pretty well, without having to resort to modifying the project file and reloading. Thanks for that go to a colleague of mine who explained how to use reflection to get into some of the guts of the SSDT objects.

There's still some work to be done, but it now passes the three tests I've laid out; to run them, compile the fork, start it running in an experimental instance of Visual Studio, open the NuGet console and type "Run-Test Ssdt" and hit tab to show the names of the tests.

Right now what I need is some advice on architecture. One is quite a little one: in Core/Packages/Constants.cs, should ".dacpac" be one of the AssemblyReferencesExtensions? There's only one place I use that collection in my own code, and in that case I specifically exclude the ".dacpac" case! I'm just not quite sure what AssemblyReferencesExtensions gets used for in other places, so I don't know whether .dacpac should be there or not.

The second, bigger and more important one is this: the SSDT project object does not expose a "References" property, so all instances of "Project.Object.References" just fail. There are two ways I've thought of getting around this: first, replace all occurrences of Object.References with a reference to the actual references object. That's fine from within my own code, and indeed that's what I've done. However, it falls down for other places, specifically the end-to-end integration tests. The second option is to create our own project type, which wraps the existing one and just passes method calls through, except for "Object", for which we provide our own object which has a References property. This is what I've done, and it involves a nasty little hack in the core NuGet code to make it work; however, now it does work, it works quite well.

So what do people think? Now I've got past some of the XML-manipulation nastiness the code changes are relatively simple, so you should be able to understand the changes I've made quite quickly, especially if you already understand how NuGet works. If anyone tests this, please leave a note here so that I know what your experience was, positive or negative.

Thanks a lot,
Tom 

Sep 20, 2013 at 11:07 PM
This have any chance of making it into the main distro? What do we need to move this forward?
Sep 23, 2013 at 9:26 AM
I'm not entirely sure, to be honest. From my point of view, I need a bit of advice from someone more familiar with the NuGet codebase (see my long post above!), but mostly I need people to actually try it out and test it! I've put in the features that I thought were important, but without people using it it's difficult to tell what needs doing before it's ready.

As for getting it into the main distribution, I think the chances are reasonable as long as someone wants it. :-) I'll give it another push, and see if someone can take a look. I'll get back to you if there's news!

Cheers,
Tom
Sep 24, 2013 at 12:02 AM
Hi,

First of all, thanks for your effort in contributing to NuGet.

To answer your questions above, I need to know more about the .dacpac files. What are they for? Also, does SSDT projects have the concept of assembly references?
Sep 24, 2013 at 8:37 PM
Edited Sep 24, 2013 at 8:38 PM
from the Data-tier Applications overview doc
A DACPAC is a Windows file with a .dacpac extension. The file supports an open format consisting of multiple XML sections representing details of the DACPAC origin, the objects in the database, and other characteristics. An advanced user can unpack the file using the DacUnpack.exe utility that ships with the product to inspect each section more closely.
A dacpac is in effect the container for the DDL for a database. It can be (with SqlPackage.exe) used to bring up a new instance of the database or it can be applied to an existing database to have just the deltas between a database's current DDL and the DDL defined in the dacpac applied.

Yes, the SSDT project has assembly references. It also has Database References. These references allow you to reference other database dacpac files, almost as if they were dlls, so that you can then reference the database objects contained in the referenced dacpacs. Here is an example of a database reference in a .sqlproj file that is referencing a manually created and installed NuGet package that contains my CoreDB dacpac.
  <ItemGroup>
    <ArtifactReference Include="..\packages\CoreDB.1.0.0\lib\net40\MyApp.Database.Core.dacpac">
      <HintPath>..\packages\CoreDB.1.0.0\lib\net40\MyApp.Database.Core.dacpac</HintPath>
      <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
    </ArtifactReference>
  </ItemGroup>
Sep 25, 2013 at 9:29 AM
Edited Sep 25, 2013 at 9:30 AM
RobRolls gives a decent summary, and I wrote this message yesterday, so my apologies if it repeats some of what he said; in the process of writing it I realised I'd make a small mistake in my code, and I wanted to fix that first. I also took the opportunity to merge the upstream changes, so the fork is pretty up-to-date now.

A .dacpac file is the output of an SSDT project, similar to a .dll; it's essentially a compiled version of the database project. It can then be "installed" into an SQL Server instance, or have a number of other things done with it (e.g. incremental updates to database schemas).

SSDT projects can reference other .dacpac files, which gives access to their tables, stored procedures, and so on. They can also reference .dll assemblies, which allows the SQL Server instance to perform tasks that .NET code can do, but is difficult or impossible in T-SQL. For this reason, SSDT projects are able to reference .dacpac files, and also .dll assemblies.

There is however a major difference between referencing DACPACs and DLLs: when adding a reference to a DACPAC, the user is given the option to include it as a part of the current database, as a different database on the same server, or on a different server entirely. For now, in my code, I have simply ignored this issue, and add database references in the default way, which is as part of the same database. If this is not what users need / want / expect, I'll have to find some way to change this.

There are also a couple of "standard" databases that are analogous to GAC references; these are added from the GUI in a slightly different way, but the API seems to treat them normally, so that doesn't seem to be a problem.

I hope that answers your questions, and gives a little bit of background on SSDT. :-)

Thanks and best wishes,
Tom
Sep 25, 2013 at 4:18 PM
Tom,

Nice summary. I am glad to hear about how you handled the database reference "type" issue. This is exactly how we are using them, as composite projects.

Thanks,
Rob
Oct 25, 2013 at 6:34 AM
This seems like an interesting scenario for NuGet. It would be a while before we could commit to accepting this into the product, as there's a lot of testing ramp-up to be able to sign off on the quality of the feature.

Do you think the implementation in a fork could be done such that you could keep the fork up to date and use the fork for your own needs for some time to try the feature out as the first pass of testing?
Oct 25, 2013 at 1:20 PM
Hi, Jeff. Yes, I think that's possible, although I'll probably have to talk to my line manager about the potential time commitment. Also, in keeping the fork up-to-date, should it be based on the master branch (as currently), or a stable branch?

However, before we get there at all there are a couple of lingering issues that need to be taken care of, and I need some advice from you (or one of your colleagues) before I can call it done. The questions are there in more detail in my post from December 14th, but to briefly recap:
  1. Should .dacpac be one of the AssemblyReferencesExtensions in src/Core/Packages/Constants.cs? I'm not sure how this is used (aside from within my own code, of course).
  2. I'm currently using a bit of a hack to make Project.Object.References work for SSDT projects, but the NuGet project support itself could work without it. The cost of removing the hack would be any reference to Object.References failing to work -- in particular, the EndToEnd tests. Are the tests the only place that directly reference Project.Object, or does it come up elsewhere?
Both questions are really to do with familiarity with the rest of the NuGet codebase. If you could shed any light on them, that'd be much appreciated and go a long way.

Cheers,
Tom