Elephanting Microsoft Sunshines rantOTD
-
I'm using a .sqlproj for my database. It lets you create tables using a semi-designer UI which is nice. However it tries to limit the amount of SQL it generates to reproduce the designer which can be a problem; in particular with default value constraints if you need to generate update scripts instead of always publishing to the DB in VS itself (eg because you're distributing a desktop app instead of a website). Eg this is what I get by default.
[IsDirty] bit NOT NULL DEFAULT 0
Looks harmless right? Except that when you publish, in SQL Server you get a generated constraint with a name like
DF__UserPermi__IsDir_49ACE3F2B. Even this could be livable, except that the number on the end is randomly generated and consequently multiple create scripts will result in different postfixes on the constraint names. This in turn results in databases that whenever a constraint is touched (and the sqlproj's love of dropping constraints while doing anything else to a table means this is more often than you might otherwise thing) you can't have a single universal update script. If MS had gone with full table and column names in its generated constraint names instead of truncating and affixing a random 8 digit hex number this wouldn't've mattered. If MS would've used randoms and truncation (presumably to maintain a max length) and then put the name in the SQL it generates to describe the table in the .sqlproj it wouldn't've mattered. Since they did neither of those things I just wasted 2.5 hours of my life modifying hundreds of lines of SQL to look like this instead:[IsDirty] bit NOT NULL CONSTRAINT [DF_UserPermissions_IsDirty] DEFAULT 0
ELEPHANTING SUNSHINES! And for more fun down the line, AIUI there's no way to automatically enforce explicit naming of new constraints (never mind actually have the project do the explicit naming for us). I shudder to think of what would happen if we ended up repeating the original problem with hundreds or thousands of systems deployed in the future instead of just a handful of beta testers.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, waging all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius Training a telescope on one’s own belly button will only reveal lint. You
-
I'm using a .sqlproj for my database. It lets you create tables using a semi-designer UI which is nice. However it tries to limit the amount of SQL it generates to reproduce the designer which can be a problem; in particular with default value constraints if you need to generate update scripts instead of always publishing to the DB in VS itself (eg because you're distributing a desktop app instead of a website). Eg this is what I get by default.
[IsDirty] bit NOT NULL DEFAULT 0
Looks harmless right? Except that when you publish, in SQL Server you get a generated constraint with a name like
DF__UserPermi__IsDir_49ACE3F2B. Even this could be livable, except that the number on the end is randomly generated and consequently multiple create scripts will result in different postfixes on the constraint names. This in turn results in databases that whenever a constraint is touched (and the sqlproj's love of dropping constraints while doing anything else to a table means this is more often than you might otherwise thing) you can't have a single universal update script. If MS had gone with full table and column names in its generated constraint names instead of truncating and affixing a random 8 digit hex number this wouldn't've mattered. If MS would've used randoms and truncation (presumably to maintain a max length) and then put the name in the SQL it generates to describe the table in the .sqlproj it wouldn't've mattered. Since they did neither of those things I just wasted 2.5 hours of my life modifying hundreds of lines of SQL to look like this instead:[IsDirty] bit NOT NULL CONSTRAINT [DF_UserPermissions_IsDirty] DEFAULT 0
ELEPHANTING SUNSHINES! And for more fun down the line, AIUI there's no way to automatically enforce explicit naming of new constraints (never mind actually have the project do the explicit naming for us). I shudder to think of what would happen if we ended up repeating the original problem with hundreds or thousands of systems deployed in the future instead of just a handful of beta testers.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, waging all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius Training a telescope on one’s own belly button will only reveal lint. You
do you have that many contraints that you need to adjust for updates every time? Do that many of your update scripts really need to violate [default value] constraints? (Sounds a bitreally bodgy) just hand script the few that matter (- there should be clear records of those few, if any, that matter) - if it is a huge number rethink the design because that is nothing to ever brag about.
Sin tack the any key okay
-
do you have that many contraints that you need to adjust for updates every time? Do that many of your update scripts really need to violate [default value] constraints? (Sounds a bitreally bodgy) just hand script the few that matter (- there should be clear records of those few, if any, that matter) - if it is a huge number rethink the design because that is nothing to ever brag about.
Sin tack the any key okay
In reality I suspect at least 99% of the time the constraints could be left in place (actually I can't think of any thing I've done since starting the app that would require removing one); but the update scripts that .sqlproj generates frequently decide to add/remove constraints before doing anything with the table. Once again Elephanting Microsoft Sunshines. Theoretically I could edit every update script it generates to remove the add/remove constraint elephantery, but that would end up being an even bigger cumulative waste of time.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, waging all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies. -- Sarah Hoyt
-
In reality I suspect at least 99% of the time the constraints could be left in place (actually I can't think of any thing I've done since starting the app that would require removing one); but the update scripts that .sqlproj generates frequently decide to add/remove constraints before doing anything with the table. Once again Elephanting Microsoft Sunshines. Theoretically I could edit every update script it generates to remove the add/remove constraint elephantery, but that would end up being an even bigger cumulative waste of time.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, waging all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies. -- Sarah Hoyt
Did sound odd to me to keep flipping constraints in and out, but if it's from a script builder then as you say sensibility is also removed. I guess I'm old fashioned and don't use auto generated scripts: if they work fine, but when they fail finding out where is somewhere on-par with looking at junior programmer multi threaded recursive class libraries in visual basic.
-
Did sound odd to me to keep flipping constraints in and out, but if it's from a script builder then as you say sensibility is also removed. I guess I'm old fashioned and don't use auto generated scripts: if they work fine, but when they fail finding out where is somewhere on-par with looking at junior programmer multi threaded recursive class libraries in visual basic.
I'm not sure this'd be my first choice project either; but my employer's decided to standardize on a single .net DB project type to minimize the differences in tooling between different projects and .sqlproj was the favorite of the senior dev who decided what we'd have to use. Funny how that works. :rolleyes:
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, waging all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies. -- Sarah Hoyt