aboutsummaryrefslogtreecommitdiffstatshomepage
diff options
context:
space:
mode:
-rw-r--r--OpenSim/Data/MSSQL/Resources/InventoryStore.migrations70
1 files changed, 70 insertions, 0 deletions
diff --git a/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations b/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations
index cd5dfdc..e2a8d57 100644
--- a/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations
+++ b/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations
@@ -171,4 +171,74 @@ CREATE NONCLUSTERED INDEX folder ON dbo.inventoryitems
171 171
172COMMIT 172COMMIT
173 173
174:VERSION 5
175
176# It would be totally crazy to have to recreate the whole table just to change one column type,
177# just because MS SQL treats each DEFAULT as a constraint object that must be dropped
178# before anything can be done to the column. Since all defaults here are unnamed, there is
179# no easy way to drop them! The hairy piece of code below removes all DEFAULT constraints
180# from InventoryItems.
181
182# SO: anything that's NULLable is by default NULL, so please don't declare DEFAULT(NULL),
183# they do nothing but prevent changes to the columns. If you really
184# need to have DEFAULTs or other constraints, give them names so they can be dropped when needed!
185
186BEGIN TRANSACTION
187DECLARE @nm varchar(80);
188DECLARE x CURSOR LOCAL FORWARD_ONLY READ_ONLY
189 FOR SELECT name FROM sys.default_constraints where parent_object_id = OBJECT_ID('inventoryitems');
190OPEN x;
191FETCH NEXT FROM x INTO @nm;
192WHILE @@FETCH_STATUS = 0
193BEGIN
194 EXEC('alter table inventoryitems drop ' + @nm);
195 FETCH NEXT FROM x INTO @nm;
196END
197CLOSE x
198DEALLOCATE x
199COMMIT
200
201# all DEFAULTs dropped!
202
203:GO
204
205BEGIN TRANSACTION
206
207# Restoring defaults:
208# NOTE: [inventoryID] does NOT need one: it's NOT NULL PK and a unique Guid must be provided every time anyway!
209
210alter table inventoryitems
211 add constraint def_baseperm default 0 for inventoryBasePermissions
212alter table inventoryitems
213 add constraint def_allperm default 0 for inventoryEveryOnePermissions
214alter table inventoryitems
215 add constraint def_grpperm default 0 for inventoryGroupPermissions
216
217COMMIT
218
219:VERSION 7
220
221BEGIN TRANSACTION
222
223# CreatorID goes back to VARCHAR(36) (???)
224
225exec sp_rename 'inventoryitems.CreatorID', 'cr_old', 'COLUMN'
226
227:GO
228
229alter table inventoryitems
230 add creatorID varchar(36) NULL
231
232:GO
233
234update inventoryitems set creatorID = CONVERT(VARCHAR(36), cr_old)
235
236alter table inventoryitems
237 drop column cr_old
238
239COMMIT
240
241
242
243
174 244