| In the Visual Studio created the project "Database" in which he described the structure of the database, in particular the table |
( table ( id ] INT NOT NULL , [ id ] INT NOT NULL , value ] NVARCHAR ( 255 ) NOT NULL , [ value ] NVARCHAR ( 255 ) NOT NULL , KEY ([ id ] ASC ), PRIMARY KEY ([ id ] ASC ), ([ name ] ASC ) UNIQUE ([ name ] ASC ) );Collected the DACPAC file and deployed it to the clients. Everything was good, until the decision came “from above” to add to this table another unique non-empty field value2 with values [value] + '' + CAST ([id] as nvarchar)
And here I had a stupor: Make it a script, of course, does not cost anything, but how to do it kosher using MSVS?
I tried to use predeploy or postdeploy scripts, get either an error, such as field [value2] is defined twice, or in the description script I see the field value2 nvarchar (max) NULL, which is already filled in the post-deployment script and NOT NULL UNIQUE, which in my opinion - not very well.
People, tell me, please, how to solve the problem correctly?
And the bugs were crawling and brazenly moving their whiskers ... (c) Not mine.
Manually, by the script, you would do the same.
It is only strange that nvarchar ( max ), this type cannot be made unique. You must have been sealed?
And I do not see the field [name], which made UNIQUE
|December 20, 18, 09:39 [ 21769171 ] Reply | Quote Report to moderator|
| alexeyvg, Yes. A typo. NVARCHAR (255) field [value], instead of [name] While writing the question, I was distracted to another base, so the errors went. I apologize. |
TABLE [ table ] ( CREATE TABLE [ table ] ( id ] INT NOT NULL , [ id ] INT NOT NULL , value ] NVARCHAR ( 255 ) NOT NULL , [ value ] NVARCHAR ( 255 ) NOT NULL , KEY ([ id ] ASC ), PRIMARY KEY ([ id ] ASC ), ([ value ] ASC ) UNIQUE ([ value ] ASC ) );
Because in the original table creation script, the [value2] field will be NULL and NOT UNIQUE. What makes documenting difficult.
|Another example of why "not entirely successful" refers to the subsequent updating of the structure. The updated structure will contain the [value2] NOT NULL UNIQUE field, the structure update will initially return it to NULL and NOT UNIQUE, and then the post-installation script will return it to NOT NULL and UNIQUE again. And can this masturbation be somehow banned? For example, to check that if there is a field, then not to change it?|
Do not you write the next update script yourself?
| Harlan, |
so set the default value to [value] + '' + CAST ([id] as nvarchar) for the new column.
|iap, the use of DACPAC implies that it contains a description of the database structure, and the update process itself builds the scripts that will update the client database to the current one. As far as I could understand this mechanism, I can describe two scripts that will be executed either before deployment or after deployment. However, my update scripts are built before the execution of the PREDEPLOY script, so if I add a field in this script, then the deployment will fail with the error "There can not be two fields with the same name", and if I add a field change to the POSTDEPLOY script, then the main script first " will turn the "field to NULL NOT UNIQUE, and then the POSTDEPLOY script will return the attributes NOT NULL UNIQUE to this field.|
|Vladislav Kolosov, tried, does not roll. Message: "SQL70536: Name" [value] "is forbidden in this context. Valid expressions include constants, constant expressions, and (in some contexts) variables. Column names are not allowed"|
| Harlan, |
on 2 "deploy" break, all business ...
Although inconvenient, of course, crutches and all changes from other developers will ruin production. So in general, this option disappears.
I honestly thought that DACPAC will not add a field if it already exists.
And I did not expect that the operation to add a field seems to be the developers of Visual Studio with such an incredibly unique operation that they did not. Or maybe they did, but we do not know here what tick in the properties to click?
I did not use this technology when it appeared, I thought that if I finished it (which is unlikely for the epoch of the “after Bill” epoch, but it would be possible in a siqueline team), then I will.
But alas, not finished.
Tek that in a student lab - Wellcome, do not use in professional work.
Use just the base project, make all changes with scripts saved in the same project, from time to time use Compare, to compare the project and the base.
| Critic, great idea. |
But I can’t figure out what to write in the first section, but what about the second? Can you tell me?
| alexeyvg, I have several clients who update their software whenever they want. Many of these customers from the "Who's there? Sit down, I'll open it myself." Therefore, I would not like them to do "complex" instructions with sequential rolling packages. But apparently, there is no other option. |
DACPAC does not add a second field if it existed prior to the deployment. But if the deployment began, it first builds the update script based on what it is now, then it executes the predeploy script. And if in this script there is a code that added this field to the table, then the main dakpack code will crash with an error. From here an output: the predeploy script to be taken out of limits of dacpac. Those. first execute it, and then deploy dacpac.