|
In a previous position, we used the following DB script pattern:
1) For each major release, have a set of "Create" scripts.
a) Create Table_1.0.0, Create_Index_1.0.0, Create_Sysdata_1.0.0 (or something like that)
2) Each time there is an upgrade, create a set of scripts
a) Upgrade_Table_1.0.0_1.0.01, Upgrade_index_1.0.0_1.0.1 (and so on)
Make sure you are using a source code control system where you would be checking in not only your source code, but your SQL scripts.
When you have a "build" you will also pickup the correct version of database scripts.
Also, you should have a Build / Deploy box where you can Create or Upgrade databases anytime for QA testing. It is always a good test to be able to take a backup of your client's database and run the upgrade scripts to prove them out.
The only version control system I've used is Subversion. If you are not familiar with them, check them out and setup a proper team development environment, you will save yourself lots of headaches.
Just my 2 cents.
David
|
|
|
|
|
General concept
- Database itself has a version
- Wrap the changes into an application that applies those to the database
- Use a table in the database that tracks the current version.
An existing tool for this is liquibase. Seemed decent when I used it.
You can roll your own as well.
And additional feature that I consider essential but it adds complexity is that the application also tracks the version of the database it expects. If the database is the wrong version (table above) then the application will exit on start up.
|
|
|
|
|
We use Red-Gate SQL tools against the UAT server. We also make a strong use of schemas, common objects use dbo, specific object use a different schema, a developer generally works on one schema at a time and dbo changes are discussed before implementing.
Scripts are run against the UAT server using SQL Compare. Master table data is synched via SQL Data Compare.
PS I do not work for Red-Gate but have been using their paid for tool set well over a decade.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This is how I've done it for years....right after login, the application compares db version to app version, applies changes (logic in the app itself) if needed. If the version changed, the db version is updated to reflect that. In addition, I also use a special database table that records schema changes as they are made.
If it happens that the database version is higher than the app, the app prompts for an update. If the user declines, the app complains and exits.
btw, comparing version numbers is much easier if you convert them to long ints using something like this:
intAppVersion = (major * 100000) + (minor * 1000) + revision
This also makes it easier to run a loop through db version checks until you hit the current app version.
"Go forth into the source" - Neal Morse
|
|
|
|
|
That sounds very similar to Entity Framework Code First Migrations[^]. 
However, I don't think that's a good approach. Apart from your application needing to check the database version every time it connects, it also has to connect as a user which has permission to modify the structure of the database. That always seems like a violation of the principle of least privilege[^] to me. 99% of the time, your application doesn't need to modify the database structure, so it shouldn't have permission to do so.
You might be able to work around that by using a second login with the elevated permissions. But in most cases, I think it's easier to move the database upgrade code outside of the application, and manage it as part of the upgrade process.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: That sounds very similar to Entity Framework Code First Migrations
Maybe they got it from me?  I've been using this approach since about Y2K.
You do have some good points regarding principle of least privilege, and I wholeheartedly agree with that philosophy/architecture for some applications...most of the ones I deal with demand simplicity and the ability for an end user to install a module without help from IT, hence dbo.
"Go forth into the source" - Neal Morse
|
|
|
|
|
I have the following tables and values:
t_cars
nCars_ID sName sModel sIdentifier
1 BMW 3 series D-78-JHG
2 Volvo C30 B-56-KHT
3 Fiat Doblo H-72-ABN
4 Volvo C40 J-78-YTR
t_feature
nFeature_ID sName
1 CMC
2 Doors
3 Color
4 Type
5 Weight
6 Engine
7 Power
t_cars_feature
nCarsFeature_ID nCars_ID nFeature_ID sValue
1 2 1 2500
2 2 2 5
3 2 4 Diesel
4 2 3 Green
5 3 1 1900
6 3 2 3
7 3 4 Otto
8 3 5 2300 KG
9 1 1 1900
10 1 3 Blue
11 1 4 Diesel
12 1 5 2100 KG
I need to retrieve from DB the cars that has CMC feature, has Color feature, AND CMC = 1900 AND Color = 'Blue' ONLY
I have tried:
SELECT t_cars.sName, t_cars.sModel, t_cars.sIdentifier
FROM t_cars, t_feature, t_cars_feature
WHERE t_feature.nFeature_ID = t_cars_feature.nFeature_ID
AND t_cars.nCars_ID = t_cars_feature.nCars_ID
AND [/*condition that get me cars that has CMC feature, has Color feature, AND CMC = 1900 AND Color = 'Blue' ONLY*/]
I have tried the condition like that:
Trial 1:
AND t_feature.sName = 'CMC'
AND t_feature.sName = 'Color'
AND t_cars_feature.sValue = '1900'
AND t_cars_feature.sValue = 'Blue'
and get me nothing
I have also tried:
Trial 2:
AND t_feature.sName IN ('CMC','Color')
AND t_cars_feature.sValue IN ('1900','Blue')
and get me all records that has CMC 1900 OR color 'Blue' (probably I got here cartesian product)
In real situation I could have several t_feature.sName values, and several t_cars_feature.sValue values, that is why trial 1 are not suitable for me ...
Can you help me ? Thank you.
|
|
|
|
|
You need to select on the cars_features table more than once, so:
SELECT C.sName, C.sModel, C.sIdentifier
FROM t_cars AS C INNER JOIN t_cars_feature AS CF1 ON CF1.nCars_ID = C.nCars_ID
INNER JOIN t_cars_feature AS CF2 ON CF2.nCars_ID = C.nCars_ID
WHERE (CF1.nFeature_ID = 1 AND CF1.sValue = 1900)
AND (CF2.nFeature_ID = 3 AND CF2.sValue = 'Blue')
sName | sModel | sIdentifier
----------------------------
BMW | 3 series | D-78-JHG
|
|
|
|
|
First crate a query that selects the link records (t_cars_feature) you expect to see.
Select * from t_cars_feature where (FeatureID = 1 and value = 'CMC') and (FeatureID = 2 and value in (3,5)) ...
Then change the query to only get the CarID
Select CarID from t_cars_feature where (FeatureID = 1 and value = 'CMC') and (FeatureID = 2 and value in (3,5)) ...
Once you are happy with the results wrap the first query to select the cars
Select * from Cars where CarID in (insert query 1)
What you are missing is the heavy use of brackets. It is still an ugly database design.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
It shows both the Jet and Ace drivers on a Server machine, and user the Sql Agent Job is running with, doesn't have admin privileges. The Packages that are running with Jet drivers are working fine on that Server where as the Packages that are using Ace drivers are not running Properly. There are various questions and eye brows that are raising here
- There are Ace 14.0 drivers there on that machine, were the Ace 12.0 drivers overwritten by Ace 14.0 when we installed newer version of Ace or office pack
- Don't the Ace 14.0 drivers which are advanced than the Ace 12.0 have backward compatibility, why is it? When we try Ace 12.0 drivers with the Packages that are using csv or flat files are working fine where as only the Excel files both (xls and xlsx) are not working properly why?
a. Are the Ace 14.0 drivers backward compatible with the csv and flat files or do the Ace 12.0 drivers can import from the csv and flat files without Admin
privileges Many questions are raising with this odd behavior. Because the csv and flat files are getting imported without admin privileges too
b. Are the Ace 12.0 drivers not available only 14.0 are running all this but because of the registry entry or some uncleaned stuff from the installation is
showing the Ace 12.0 drivers in the drivers list? Many many questions are raising. - As the Packages with the Jet drivers are working fine I am not understanding what is missing or messing up here, is it the unavailability of Ace drivers or do the Ace drivers need Admin privileges and Jet drivers don't? If unavailability is the case then why Ace 12.0 drivers are fine with csv and flat files?
The problem for us in continuing with the Jet drivers is, we have some packages with xls which is fine with Jet drivers but we have some Packages with the xlsx files which are not going to work with the Jet drivers.
Is there any buddy who can answer me these questions? Any help would be greatly appreciated my friends. I am also researching doing lot of studies about behavior of these drivers.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Are you talking about SSIS packages?
I recommend using only ACE, but you may need to run your packages as 32-bit.
|
|
|
|
|
I did, but still Ace drivers are not working but Jet drivers are working on my machine, but Ace drivers are showing in the drivers list, with Admin rights Ace drivers are also Working, when user is not Admin Jet drivers are working fine, how is it different for different drivers when the user is same?
I have Ace 14.0 drivers but Packages by default taking only Ace 12.0 drivers, when I check in the drivers list Ace 12.0 drivers are showing in the list are the Ace 12.0 drivers cheating that they show-up from registry entries but don't exist?
- Why SSIS Packages only take Ace 12.0 drivers, why isn't there backward compatibility?
- why don't they take Ace 14.0 drivers?
- Why Jet drivers can read the Excel files without the user that doesn't have Admin privileges but Ace 12.0 drivers can't?
Any help would be greatly helpful friends - thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: with Admin rights Ace drivers are also Working,
So rephrasing to make it very clear what the problem is.
1. When you run the application ON the target machine with admin privileges it both drivers work.
2. When you run the application ON the target machine with non-admin privileges, doing exactly the same as above for 1, then only one driver works. The other fails.
If so your problem is with the non-admin user and/or the access rights for the driver that does not work.
|
|
|
|
|
Hi all,
I am passing column names in dynamic sql like 'col1, col2, col3' + ', ''' + @reportId + '''', up to col3 all the columns are giving correct values, but the @reportId is a string which has value as '1-085' that's being converted into integers and giving me the value '-084'. I mean may its calculating the expression and returning the resultant value.
But I want that string to be as it is, means I want that value as '1-085' instead of a calculated value ie -84, is there anyway to do it. Any help would be greatly helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
If it is a string then it will be saved as a string. Your code must be doing something strange.
|
|
|
|
|
No I am passing it as a string only from an ssis package variable which is of type string, I am appending both these strings as 'col1, col2, col3' + ', ''' + @reportId + '''' and passing it as nvarchar parameter into a stored procedure
Exec [ETL].[PopulateStageTable] ?, ?, ?, ?, ?, ?, ?
There are other Parameters but they are not giving any problem excel this, I checked even the execute sql too has this parameter defined as nvarchar and same thing in the Stored procedure too, nothing is converted to number or anything like that
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Rephrasing what the previous response was suggesting (I believe.)
You posted a line where @reportId is used. Nothing in that line would change the value of that.
That line has nothing to do with the value in @reportId. At that point it already has the value you are seeing.
Consequently you are doing something before that which causes it to have that value. You need to find that and correct it.
|
|
|
|
|
To rectify you, this is not something you believe, but deduced. It sounds correct, hence the upvote.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I didn't quite get your answer, but some how its deducting the value and putting it there. If you can answer that's fine, but you are not able that's fine I just added one more step in Control flow which updates that Column with the correct value. But if you know it would be helpful for me.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: but some how its deducting the value
Computers are deterministic. They do what they do because some code explicitly to do it that way.
Not sure how I can make it clearer. If you go to the store and open your wallet and expect it to have $20 and it doesn't do you continue to look at the wallet expecting the $20 to show up. Or do you think back where you were before the store to figure out where that money went?
Same thing it true for this code.
You posted a line where you use the reportId variable. At that point it already had a value. The computer doesn't give it a value at that point, because it already had one.
There is code before that line that gives it a value. You must find that code, not the code that you are looking at (again the code you posted) and then figure out from that why it has the value that it does.
|
|
|
|
|
Ok Got it my friend thank you, I put an another sql task that just updates that Column value, so I think we are good now my friend.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
The AspNetUsers table uses a GUID for the Id. I'm adding a dozen tables or so, and was interested in what is considered "best practice" where Id columns in my own tables are concerned. I understand it's almost impossible to infer other IDs if they're GUIDs, and there's a possibility that even ids from my tables could show up in cookies or query strings, so it sounds to me like the best way forward is to also use GUIDs for my IDs as well.
Are there any hidden/subtle down-sides to using GUIDs for identity columns, or overriding benefits for using longs instead of GUIDs?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Despite of the small amount of extra space needed for GUID it has multiple benefits. For example:
- It's unique even if you run multiple SQL Server instances
- It can be used as a key in replicated environment
- Reseeding a table does not produce duplicates
- If you use newsequentialid the values are 'close to each other' thus behaving more nicely with clustered keys, just like identity values
|
|
|
|
|
John Simmons / outlaw programmer wrote: Are there any hidden/subtle down-sides to using GUIDs for identity columns, or overriding benefits for using longs instead of GUIDs?
GUIDs are 'probably' unique. So very, very likely unique.
Longs are sequential. Always (unless you do something weird.) So they implicitly define insertion order even in cases when a database reuses slots. Sometimes GUIDs do that but most do not. Normally I insertion order a significant factor when I need to debug subtle production errors. Other than that they are convenient alternative which can only otherwise be solved (sort of) by adding a 'creation time' column to each table.
Small extra space that they use hasn't been a concern of mine for a very long time. But your business model might define something different. If so then you might even want to consider an int rather than long (again business model drives that.)
As you noted GUIDs have an obscurity advantage when exposed. Of course that is easy to do the same thing by creating the client to server interface such that it creates ids specific to that (so map it.)
Syncing two disparate databases (perhaps offline to main) is at least somewhat easier with GUIDs because one can ignore the id overlap that would exist with longs.
I would say that these days although I still like the sequential nature of longs, I would probably start with GUIDs unless I knew of something specific that I needed to support. But I would also add a 'creation time' regardless.
|
|
|
|
|
Quote: Other than that they are convenient alternative which can only otherwise be solved (sort of) by adding a 'creation time' column to each table.
GUID Version 1 includes a timestamp to within 100 nano-seconds, and a "globally unique node identifier", which would normally be your MAC address. If you need insertion order, then a V1 GUID might meet your needs. Since you also get the node identifier, then you not only know when, but where the data comes from. Whether the risks with that are acceptable, is up to you, of course.
See How to make a GUID
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
