1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
|
:VERSION 1
BEGIN TRANSACTION
CREATE TABLE [inventoryfolders] (
[folderID] [varchar](36) NOT NULL default '',
[agentID] [varchar](36) default NULL,
[parentFolderID] [varchar](36) default NULL,
[folderName] [varchar](64) default NULL,
[type] [smallint] NOT NULL default 0,
[version] [int] NOT NULL default 0,
PRIMARY KEY CLUSTERED
(
[folderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [owner] ON [inventoryfolders]
(
[agentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [parent] ON [inventoryfolders]
(
[parentFolderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE TABLE [inventoryitems] (
[inventoryID] [varchar](36) NOT NULL default '',
[assetID] [varchar](36) default NULL,
[assetType] [int] default NULL,
[parentFolderID] [varchar](36) default NULL,
[avatarID] [varchar](36) default NULL,
[inventoryName] [varchar](64) default NULL,
[inventoryDescription] [varchar](128) default NULL,
[inventoryNextPermissions] [int] default NULL,
[inventoryCurrentPermissions] [int] default NULL,
[invType] [int] default NULL,
[creatorID] [varchar](36) default NULL,
[inventoryBasePermissions] [int] NOT NULL default 0,
[inventoryEveryOnePermissions] [int] NOT NULL default 0,
[salePrice] [int] default NULL,
[saleType] [tinyint] default NULL,
[creationDate] [int] default NULL,
[groupID] [varchar](36) default NULL,
[groupOwned] [bit] default NULL,
[flags] [int] default NULL,
PRIMARY KEY CLUSTERED
(
[inventoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [owner] ON [inventoryitems]
(
[avatarID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [folder] ON [inventoryitems]
(
[parentFolderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
COMMIT
:VERSION 2
BEGIN TRANSACTION
ALTER TABLE inventoryitems ADD inventoryGroupPermissions INTEGER NOT NULL default 0
COMMIT
:VERSION 3
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_inventoryfolders
(
folderID uniqueidentifier NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
agentID uniqueidentifier NULL DEFAULT (NULL),
parentFolderID uniqueidentifier NULL DEFAULT (NULL),
folderName varchar(64) NULL DEFAULT (NULL),
type smallint NOT NULL DEFAULT ((0)),
version int NOT NULL DEFAULT ((0))
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.inventoryfolders)
EXEC('INSERT INTO dbo.Tmp_inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version)
SELECT CONVERT(uniqueidentifier, folderID), CONVERT(uniqueidentifier, agentID), CONVERT(uniqueidentifier, parentFolderID), folderName, type, version FROM dbo.inventoryfolders WITH (HOLDLOCK TABLOCKX)')
DROP TABLE dbo.inventoryfolders
EXECUTE sp_rename N'dbo.Tmp_inventoryfolders', N'inventoryfolders', 'OBJECT'
ALTER TABLE dbo.inventoryfolders ADD CONSTRAINT
PK__inventor__C2FABFB3173876EA PRIMARY KEY CLUSTERED
(
folderID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX owner ON dbo.inventoryfolders
(
agentID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX parent ON dbo.inventoryfolders
(
parentFolderID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
COMMIT
:VERSION 4
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_inventoryitems
(
inventoryID uniqueidentifier NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
assetID uniqueidentifier NULL DEFAULT (NULL),
assetType int NULL DEFAULT (NULL),
parentFolderID uniqueidentifier NULL DEFAULT (NULL),
avatarID uniqueidentifier NULL DEFAULT (NULL),
inventoryName varchar(64) NULL DEFAULT (NULL),
inventoryDescription varchar(128) NULL DEFAULT (NULL),
inventoryNextPermissions int NULL DEFAULT (NULL),
inventoryCurrentPermissions int NULL DEFAULT (NULL),
invType int NULL DEFAULT (NULL),
creatorID uniqueidentifier NULL DEFAULT (NULL),
inventoryBasePermissions int NOT NULL DEFAULT ((0)),
inventoryEveryOnePermissions int NOT NULL DEFAULT ((0)),
salePrice int NULL DEFAULT (NULL),
saleType tinyint NULL DEFAULT (NULL),
creationDate int NULL DEFAULT (NULL),
groupID uniqueidentifier NULL DEFAULT (NULL),
groupOwned bit NULL DEFAULT (NULL),
flags int NULL DEFAULT (NULL),
inventoryGroupPermissions int NOT NULL DEFAULT ((0))
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.inventoryitems)
EXEC('INSERT INTO dbo.Tmp_inventoryitems (inventoryID, assetID, assetType, parentFolderID, avatarID, inventoryName, inventoryDescription, inventoryNextPermissions, inventoryCurrentPermissions, invType, creatorID, inventoryBasePermissions, inventoryEveryOnePermissions, salePrice, saleType, creationDate, groupID, groupOwned, flags, inventoryGroupPermissions)
SELECT CONVERT(uniqueidentifier, inventoryID), CONVERT(uniqueidentifier, assetID), assetType, CONVERT(uniqueidentifier, parentFolderID), CONVERT(uniqueidentifier, avatarID), inventoryName, inventoryDescription, inventoryNextPermissions, inventoryCurrentPermissions, invType, CONVERT(uniqueidentifier, creatorID), inventoryBasePermissions, inventoryEveryOnePermissions, salePrice, saleType, creationDate, CONVERT(uniqueidentifier, groupID), groupOwned, flags, inventoryGroupPermissions FROM dbo.inventoryitems WITH (HOLDLOCK TABLOCKX)')
DROP TABLE dbo.inventoryitems
EXECUTE sp_rename N'dbo.Tmp_inventoryitems', N'inventoryitems', 'OBJECT'
ALTER TABLE dbo.inventoryitems ADD CONSTRAINT
PK__inventor__C4B7BC2220C1E124 PRIMARY KEY CLUSTERED
(
inventoryID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX owner ON dbo.inventoryitems
(
avatarID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX folder ON dbo.inventoryitems
(
parentFolderID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
COMMIT
:VERSION 5
# It would be totally crazy to have to recreate the whole table just to change one column type,
# just because MS SQL treats each DEFAULT as a constraint object that must be dropped
# before anything can be done to the column. Since all defaults here are unnamed, there is
# no easy way to drop them! The hairy piece of code below removes all DEFAULT constraints
# from InventoryItems.
# SO: anything that's NULLable is by default NULL, so please don't declare DEFAULT(NULL),
# they do nothing but prevent changes to the columns. If you really
# need to have DEFAULTs or other constraints, give them names so they can be dropped when needed!
BEGIN TRANSACTION
DECLARE @nm varchar(80);
DECLARE x CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR SELECT name FROM sys.default_constraints where parent_object_id = OBJECT_ID('inventoryitems');
OPEN x;
FETCH NEXT FROM x INTO @nm;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('alter table inventoryitems drop ' + @nm);
FETCH NEXT FROM x INTO @nm;
END
CLOSE x
DEALLOCATE x
COMMIT
# all DEFAULTs dropped!
:GO
BEGIN TRANSACTION
# Restoring defaults:
# NOTE: [inventoryID] does NOT need one: it's NOT NULL PK and a unique Guid must be provided every time anyway!
alter table inventoryitems
add constraint def_baseperm default 0 for inventoryBasePermissions
alter table inventoryitems
add constraint def_allperm default 0 for inventoryEveryOnePermissions
alter table inventoryitems
add constraint def_grpperm default 0 for inventoryGroupPermissions
COMMIT
:VERSION 7
BEGIN TRANSACTION
# CreatorID goes back to VARCHAR(36) (???)
exec sp_rename 'inventoryitems.CreatorID', 'cr_old', 'COLUMN'
:GO
alter table inventoryitems
add creatorID varchar(36) NULL
:GO
update inventoryitems set creatorID = CONVERT(VARCHAR(36), cr_old)
alter table inventoryitems
drop column cr_old
COMMIT
:VERSION 8
ALTER TABLE inventoryitems
ADD CONSTRAINT DF_inventoryitems_creatorID
DEFAULT '00000000-0000-0000-0000-000000000000' FOR creatorID
:GO
|
