clockwerk-opensim-stable – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 vero 1 :VERSION 1
2  
3 BEGIN TRANSACTION
4  
5 CREATE TABLE [inventoryfolders] (
6 [folderID] [varchar](36) NOT NULL default '',
7 [agentID] [varchar](36) default NULL,
8 [parentFolderID] [varchar](36) default NULL,
9 [folderName] [varchar](64) default NULL,
10 [type] [smallint] NOT NULL default 0,
11 [version] [int] NOT NULL default 0,
12 PRIMARY KEY CLUSTERED
13 (
14 [folderID] ASC
15 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
16 ) ON [PRIMARY]
17  
18 CREATE NONCLUSTERED INDEX [owner] ON [inventoryfolders]
19 (
20 [agentID] ASC
21 )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]
22  
23 CREATE NONCLUSTERED INDEX [parent] ON [inventoryfolders]
24 (
25 [parentFolderID] ASC
26 )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]
27  
28  
29 CREATE TABLE [inventoryitems] (
30 [inventoryID] [varchar](36) NOT NULL default '',
31 [assetID] [varchar](36) default NULL,
32 [assetType] [int] default NULL,
33 [parentFolderID] [varchar](36) default NULL,
34 [avatarID] [varchar](36) default NULL,
35 [inventoryName] [varchar](64) default NULL,
36 [inventoryDescription] [varchar](128) default NULL,
37 [inventoryNextPermissions] [int] default NULL,
38 [inventoryCurrentPermissions] [int] default NULL,
39 [invType] [int] default NULL,
40 [creatorID] [varchar](36) default NULL,
41 [inventoryBasePermissions] [int] NOT NULL default 0,
42 [inventoryEveryOnePermissions] [int] NOT NULL default 0,
43 [salePrice] [int] default NULL,
44 [saleType] [tinyint] default NULL,
45 [creationDate] [int] default NULL,
46 [groupID] [varchar](36) default NULL,
47 [groupOwned] [bit] default NULL,
48 [flags] [int] default NULL,
49 PRIMARY KEY CLUSTERED
50 (
51 [inventoryID] ASC
52 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
53 ) ON [PRIMARY]
54  
55  
56 CREATE NONCLUSTERED INDEX [owner] ON [inventoryitems]
57 (
58 [avatarID] ASC
59 )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]
60  
61 CREATE NONCLUSTERED INDEX [folder] ON [inventoryitems]
62 (
63 [parentFolderID] ASC
64 )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]
65  
66 COMMIT
67  
68  
69 :VERSION 2
70  
71 BEGIN TRANSACTION
72  
73 ALTER TABLE inventoryitems ADD inventoryGroupPermissions INTEGER NOT NULL default 0
74  
75 COMMIT
76  
77 :VERSION 3
78  
79 /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
80 BEGIN TRANSACTION
81  
82 CREATE TABLE dbo.Tmp_inventoryfolders
83 (
84 folderID uniqueidentifier NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
85 agentID uniqueidentifier NULL DEFAULT (NULL),
86 parentFolderID uniqueidentifier NULL DEFAULT (NULL),
87 folderName varchar(64) NULL DEFAULT (NULL),
88 type smallint NOT NULL DEFAULT ((0)),
89 version int NOT NULL DEFAULT ((0))
90 ) ON [PRIMARY]
91  
92 IF EXISTS(SELECT * FROM dbo.inventoryfolders)
93 EXEC('INSERT INTO dbo.Tmp_inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version)
94 SELECT CONVERT(uniqueidentifier, folderID), CONVERT(uniqueidentifier, agentID), CONVERT(uniqueidentifier, parentFolderID), folderName, type, version FROM dbo.inventoryfolders WITH (HOLDLOCK TABLOCKX)')
95  
96 DROP TABLE dbo.inventoryfolders
97  
98 EXECUTE sp_rename N'dbo.Tmp_inventoryfolders', N'inventoryfolders', 'OBJECT'
99  
100 ALTER TABLE dbo.inventoryfolders ADD CONSTRAINT
101 PK__inventor__C2FABFB3173876EA PRIMARY KEY CLUSTERED
102 (
103 folderID
104 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
105  
106 CREATE NONCLUSTERED INDEX owner ON dbo.inventoryfolders
107 (
108 agentID
109 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
110  
111 CREATE NONCLUSTERED INDEX parent ON dbo.inventoryfolders
112 (
113 parentFolderID
114 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
115  
116 COMMIT
117  
118  
119 :VERSION 4
120  
121 BEGIN TRANSACTION
122  
123 CREATE TABLE dbo.Tmp_inventoryitems
124 (
125 inventoryID uniqueidentifier NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
126 assetID uniqueidentifier NULL DEFAULT (NULL),
127 assetType int NULL DEFAULT (NULL),
128 parentFolderID uniqueidentifier NULL DEFAULT (NULL),
129 avatarID uniqueidentifier NULL DEFAULT (NULL),
130 inventoryName varchar(64) NULL DEFAULT (NULL),
131 inventoryDescription varchar(128) NULL DEFAULT (NULL),
132 inventoryNextPermissions int NULL DEFAULT (NULL),
133 inventoryCurrentPermissions int NULL DEFAULT (NULL),
134 invType int NULL DEFAULT (NULL),
135 creatorID uniqueidentifier NULL DEFAULT (NULL),
136 inventoryBasePermissions int NOT NULL DEFAULT ((0)),
137 inventoryEveryOnePermissions int NOT NULL DEFAULT ((0)),
138 salePrice int NULL DEFAULT (NULL),
139 saleType tinyint NULL DEFAULT (NULL),
140 creationDate int NULL DEFAULT (NULL),
141 groupID uniqueidentifier NULL DEFAULT (NULL),
142 groupOwned bit NULL DEFAULT (NULL),
143 flags int NULL DEFAULT (NULL),
144 inventoryGroupPermissions int NOT NULL DEFAULT ((0))
145 ) ON [PRIMARY]
146  
147 IF EXISTS(SELECT * FROM dbo.inventoryitems)
148 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)
149 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)')
150  
151 DROP TABLE dbo.inventoryitems
152  
153 EXECUTE sp_rename N'dbo.Tmp_inventoryitems', N'inventoryitems', 'OBJECT'
154  
155 ALTER TABLE dbo.inventoryitems ADD CONSTRAINT
156 PK__inventor__C4B7BC2220C1E124 PRIMARY KEY CLUSTERED
157 (
158 inventoryID
159 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
160  
161  
162 CREATE NONCLUSTERED INDEX owner ON dbo.inventoryitems
163 (
164 avatarID
165 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
166  
167 CREATE NONCLUSTERED INDEX folder ON dbo.inventoryitems
168 (
169 parentFolderID
170 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
171  
172 COMMIT
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  
186 BEGIN TRANSACTION
187 DECLARE @nm varchar(80);
188 DECLARE x CURSOR LOCAL FORWARD_ONLY READ_ONLY
189 FOR SELECT name FROM sys.default_constraints where parent_object_id = OBJECT_ID('inventoryitems');
190 OPEN x;
191 FETCH NEXT FROM x INTO @nm;
192 WHILE @@FETCH_STATUS = 0
193 BEGIN
194 EXEC('alter table inventoryitems drop ' + @nm);
195 FETCH NEXT FROM x INTO @nm;
196 END
197 CLOSE x
198 DEALLOCATE x
199 COMMIT
200  
201 # all DEFAULTs dropped!
202  
203 :GO
204  
205 BEGIN 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  
210 alter table inventoryitems
211 add constraint def_baseperm default 0 for inventoryBasePermissions
212 alter table inventoryitems
213 add constraint def_allperm default 0 for inventoryEveryOnePermissions
214 alter table inventoryitems
215 add constraint def_grpperm default 0 for inventoryGroupPermissions
216  
217 COMMIT
218  
219 :VERSION 7
220  
221 BEGIN TRANSACTION
222  
223 # CreatorID goes back to VARCHAR(36) (???)
224  
225 exec sp_rename 'inventoryitems.CreatorID', 'cr_old', 'COLUMN'
226  
227 :GO
228  
229 alter table inventoryitems
230 add creatorID varchar(36) NULL
231  
232 :GO
233  
234 update inventoryitems set creatorID = CONVERT(VARCHAR(36), cr_old)
235  
236 alter table inventoryitems
237 drop column cr_old
238  
239 COMMIT
240  
241 :VERSION 8
242  
243 ALTER TABLE inventoryitems
244 ADD CONSTRAINT DF_inventoryitems_creatorID
245 DEFAULT '00000000-0000-0000-0000-000000000000' FOR creatorID
246  
247 :GO
248  
249 :VERSION 9
250  
251 BEGIN TRANSACTION
252  
253 # CreatorID goes up to VARCHAR(255)
254  
255 exec sp_rename 'inventoryitems.CreatorID', 'cr_old', 'COLUMN'
256  
257 :GO
258  
259 alter table inventoryitems
260 add creatorID varchar(255) NULL
261  
262 :GO
263  
264 update inventoryitems set creatorID = cr_old
265  
266 alter table inventoryitems
267 drop CONSTRAINT DF_inventoryitems_creatorID
268 :GO
269  
270 alter table inventoryitems
271 drop column cr_old
272 :GO
273 COMMIT
274  
275 ALTER TABLE inventoryitems
276 ADD CONSTRAINT DF_inventoryitems_creatorID
277 DEFAULT '00000000-0000-0000-0000-000000000000' FOR creatorID
278  
279 :GO