InventoryStore.migrations 9.0 KB

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