PageRenderTime 39ms CodeModel.GetById 9ms app.highlight 27ms RepoModel.GetById 1ms app.codeStats 0ms

/BlogEngine/BlogEngine.NET/setup/SQL_CE/SQL_CE_UpgradeFrom2.0to2.5.sql

#
SQL | 276 lines | 271 code | 4 blank | 1 comment | 0 complexity | c480a249b872dbedbf1e7e85bebd8b7c MD5 | raw file
  1
  2
  3ALTER TABLE [be_PostCategory] DROP CONSTRAINT [FK_be_PostCategory_be_Categories]
  4GO
  5ALTER TABLE [be_PostCategory] DROP CONSTRAINT [FK_be_PostCategory_be_Posts]
  6GO
  7ALTER TABLE [be_PostComment] DROP CONSTRAINT [FK_be_PostComment_be_Posts]
  8GO
  9ALTER TABLE [be_PostNotify] DROP CONSTRAINT [FK_be_PostNotify_be_Posts]
 10GO
 11ALTER TABLE [be_PostTag] DROP CONSTRAINT [FK_be_PostTag_be_Posts]
 12GO
 13ALTER TABLE [be_UserRoles] DROP CONSTRAINT [FK_be_UserRoles_be_Roles]
 14GO
 15ALTER TABLE [be_UserRoles] DROP CONSTRAINT [FK_be_UserRoles_be_Users]
 16GO
 17
 18CREATE TABLE [be_Blogs] (
 19  [BlogRowId] int NOT NULL  IDENTITY (2,1)
 20, [BlogId] uniqueidentifier NOT NULL
 21, [BlogName] nvarchar(255) NOT NULL
 22, [Hostname] nvarchar(255) NOT NULL
 23, [IsAnyTextBeforeHostnameAccepted] bit NOT NULL
 24, [StorageContainerName] nvarchar(255) NOT NULL
 25, [VirtualPath] nvarchar(255) NOT NULL
 26, [IsPrimary] bit NOT NULL
 27, [IsActive] bit NOT NULL
 28);
 29GO
 30ALTER TABLE [be_Blogs] ADD CONSTRAINT [PK_be_Blogs_BlogRowId] PRIMARY KEY ([BlogRowId]);
 31GO
 32INSERT INTO [be_Blogs] ([BlogId], [BlogName], [Hostname], [IsAnyTextBeforeHostnameAccepted], [StorageContainerName], [VirtualPath], [IsPrimary], [IsActive]) VALUES (N'27604f05-86ad-47ef-9e05-950bb762570c', N'Primary', N'', 0, N'', N'~/', 1, 1)
 33GO
 34-- Adding as column with NOT NULL is not allowed, set a default value or allow NULL
 35ALTER TABLE [be_BlogRollItems] ADD [BlogRollRowId] int NOT NULL  IDENTITY (7,1)
 36GO
 37ALTER TABLE [be_BlogRollItems] ADD [BlogId] uniqueidentifier NULL  
 38GO
 39UPDATE [be_BlogRollItems] SET [BlogId] = '27604F05-86AD-47EF-9E05-950BB762570C'
 40GO
 41ALTER TABLE [be_BlogRollItems] ALTER COLUMN [BlogId] uniqueidentifier NOT NULL
 42GO
 43ALTER TABLE [be_BlogRollItems] DROP CONSTRAINT [PK_be_BlogRollItems_BlogRollId]
 44GO
 45ALTER TABLE [be_BlogRollItems] ADD CONSTRAINT [PK_be_BlogRollItems_BlogRollRowId] PRIMARY KEY ([BlogRollRowId]);
 46GO
 47CREATE INDEX [idx_be_BlogRollItems_BlogId] ON [be_BlogRollItems] ([BlogId] ASC);
 48GO
 49ALTER TABLE [be_Categories] ADD [CategoryRowID] int NOT NULL  IDENTITY (2,1)
 50GO
 51ALTER TABLE [be_Categories] ADD [BlogID] uniqueidentifier NULL  
 52GO
 53UPDATE [be_Categories] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
 54GO
 55ALTER TABLE [be_Categories] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
 56GO
 57ALTER TABLE [be_Categories] DROP CONSTRAINT [PK_be_Categories_CategoryID]
 58GO
 59ALTER TABLE [be_Categories] ADD CONSTRAINT [PK_be_Categories_CategoryRowID] PRIMARY KEY ([CategoryRowID]);
 60GO
 61CREATE UNIQUE INDEX [idx_be_Categories_BlogID_CategoryID] ON [be_Categories] ([BlogID] ASC,[CategoryID] ASC);
 62GO
 63ALTER TABLE [be_DataStoreSettings] ADD [DataStoreSettingRowId] int NOT NULL  IDENTITY (2,1)
 64GO
 65ALTER TABLE [be_DataStoreSettings] ADD [BlogId] uniqueidentifier NULL  
 66GO
 67UPDATE [be_DataStoreSettings] SET [BlogId] = '27604F05-86AD-47EF-9E05-950BB762570C'
 68GO
 69ALTER TABLE [be_DataStoreSettings] ALTER COLUMN [BlogId] uniqueidentifier NOT NULL
 70GO
 71ALTER TABLE [be_DataStoreSettings] ADD CONSTRAINT [PK_be_DataStoreSettings_DataStoreSettingRowId] PRIMARY KEY ([DataStoreSettingRowId]);
 72GO
 73CREATE INDEX [idx_be_DataStoreSettings_BlogId_ExtensionType_TypeID] ON [be_DataStoreSettings] ([BlogId] ASC,[ExtensionType] ASC,[ExtensionId] ASC);
 74GO
 75DROP INDEX [be_DataStoreSettings].[I_TypeID];
 76GO
 77ALTER TABLE [be_Pages] ADD [PageRowID] int NOT NULL  IDENTITY (1,1)
 78GO
 79ALTER TABLE [be_Pages] ADD [BlogID] uniqueidentifier NULL  
 80GO
 81UPDATE [be_Pages] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
 82GO
 83ALTER TABLE [be_Pages] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
 84GO
 85ALTER TABLE [be_Pages] DROP CONSTRAINT [PK_be_Pages_PageID]
 86GO
 87ALTER TABLE [be_Pages] ADD CONSTRAINT [PK_be_Pages_PageRowID] PRIMARY KEY ([PageRowID]);
 88GO
 89CREATE INDEX [idx_Pages_BlogId_PageId] ON [be_Pages] ([BlogID] ASC,[PageID] ASC);
 90GO
 91ALTER TABLE [be_PingService] ADD [BlogID] uniqueidentifier NULL  
 92GO
 93UPDATE [be_PingService] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
 94GO
 95ALTER TABLE [be_PingService] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
 96GO
 97CREATE INDEX [idx_be_PingService_BlogId] ON [be_PingService] ([BlogID] ASC);
 98GO
 99ALTER TABLE [be_PostCategory] ADD [BlogID] uniqueidentifier NULL  
100GO
101UPDATE [be_PostCategory] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
102GO
103ALTER TABLE [be_PostCategory] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
104GO
105CREATE INDEX [idx_be_PostCategory_BlogId_CategoryId] ON [be_PostCategory] ([BlogID] ASC,[CategoryID] ASC);
106GO
107CREATE INDEX [idx_be_PostCategory_BlogId_PostId] ON [be_PostCategory] ([BlogID] ASC,[PostID] ASC);
108GO
109DROP INDEX [be_PostCategory].[FK_CategoryID];
110GO
111DROP INDEX [be_PostCategory].[FK_PostID];
112GO
113ALTER TABLE [be_PostComment] ADD [PostCommentRowID] int NOT NULL  IDENTITY (1,1)
114GO
115ALTER TABLE [be_PostComment] ADD [BlogID] uniqueidentifier NULL  
116GO
117UPDATE [be_PostComment] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
118GO
119ALTER TABLE [be_PostComment] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
120GO
121ALTER TABLE [be_PostComment] DROP CONSTRAINT [PK_be_PostComment_PostCommentID]
122GO
123ALTER TABLE [be_PostComment] ADD CONSTRAINT [PK_be_PostComment_PostCommentRowID] PRIMARY KEY ([PostCommentRowID]);
124GO
125CREATE INDEX [idx_be_PostComment_BlogId_PostId] ON [be_PostComment] ([BlogID] ASC,[PostID] ASC);
126GO
127DROP INDEX [be_PostComment].[FK_PostID];
128GO
129ALTER TABLE [be_PostNotify] ADD [BlogID] uniqueidentifier NULL  
130GO
131UPDATE [be_PostNotify] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
132GO
133ALTER TABLE [be_PostNotify] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
134GO
135ALTER TABLE [be_Posts] ADD [PostRowID] int NOT NULL  IDENTITY (2,1)
136GO
137ALTER TABLE [be_Posts] ADD [BlogID] uniqueidentifier NULL  
138GO
139UPDATE [be_Posts] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
140GO
141ALTER TABLE [be_Posts] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
142GO
143ALTER TABLE [be_Posts] DROP CONSTRAINT [PK_be_Posts_PostID]
144GO
145ALTER TABLE [be_Posts] ADD CONSTRAINT [PK_be_Posts_PostRowID] PRIMARY KEY ([PostRowID]);
146GO
147CREATE UNIQUE INDEX [be_Posts_BlogID_PostID] ON [be_Posts] ([BlogID] ASC,[PostID] ASC);
148GO
149ALTER TABLE [be_PostTag] ADD [BlogID] uniqueidentifier NULL  
150GO
151UPDATE [be_PostTag] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
152GO
153ALTER TABLE [be_PostTag] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
154GO
155CREATE INDEX [idx_be_PostTag_BlogId_PostId] ON [be_PostTag] ([BlogID] ASC,[PostID] ASC);
156GO
157DROP INDEX [be_PostTag].[FK_PostID];
158GO
159ALTER TABLE [be_Profiles] ADD [BlogID] uniqueidentifier NULL  
160GO
161UPDATE [be_Profiles] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
162GO
163ALTER TABLE [be_Profiles] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
164GO
165CREATE INDEX [idx_be_Profiles_BlogId_UserName] ON [be_Profiles] ([BlogID] ASC,[UserName] ASC);
166GO
167DROP INDEX [be_Profiles].[I_UserName];
168GO
169ALTER TABLE [be_Referrers] ADD [ReferrerRowId] int NOT NULL  IDENTITY (1,1)
170GO
171ALTER TABLE [be_Referrers] ADD [BlogId] uniqueidentifier NULL  
172GO
173UPDATE [be_Referrers] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
174GO
175ALTER TABLE [be_Referrers] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
176GO
177ALTER TABLE [be_Referrers] DROP CONSTRAINT [PK_be_Referrers_ReferrerId]
178GO
179ALTER TABLE [be_Referrers] ADD CONSTRAINT [PK_be_Referrers_ReferrerRowId] PRIMARY KEY ([ReferrerRowId]);
180GO
181CREATE INDEX [idx_be_Referrers_BlogId] ON [be_Referrers] ([BlogId] ASC);
182GO
183ALTER TABLE [be_RightRoles] ADD [RightRoleRowId] int NOT NULL  IDENTITY (1,1)
184GO
185ALTER TABLE [be_RightRoles] ADD [BlogId] uniqueidentifier NULL  
186GO
187UPDATE [be_RightRoles] SET [BlogId] = '27604F05-86AD-47EF-9E05-950BB762570C'
188GO
189ALTER TABLE [be_RightRoles] ALTER COLUMN [BlogId] uniqueidentifier NOT NULL
190GO
191ALTER TABLE [be_RightRoles] DROP CONSTRAINT [PK_be_RightRoles_RightName_Role]
192GO
193ALTER TABLE [be_RightRoles] ADD CONSTRAINT [PK_be_RightRoles_RightRoleRowId] PRIMARY KEY ([RightRoleRowId]);
194GO
195CREATE INDEX [idx_be_RightRoles_BlogId] ON [be_RightRoles] ([BlogId] ASC);
196GO
197ALTER TABLE [be_Rights] ADD [RightRowId] int NOT NULL  IDENTITY (1,1)
198GO
199ALTER TABLE [be_Rights] ADD [BlogId] uniqueidentifier NULL  
200GO
201UPDATE [be_Rights] SET [BlogId] = '27604F05-86AD-47EF-9E05-950BB762570C'
202GO
203ALTER TABLE [be_Rights] ALTER COLUMN [BlogId] uniqueidentifier NOT NULL
204GO
205ALTER TABLE [be_Rights] DROP CONSTRAINT [PK_be_Rights_RightName]
206GO
207ALTER TABLE [be_Rights] ADD CONSTRAINT [PK_be_Rights_RightRowId] PRIMARY KEY ([RightRowId]);
208GO
209CREATE INDEX [idx_be_Rights_BlogId] ON [be_Rights] ([BlogId] ASC);
210GO
211ALTER TABLE [be_Roles] ADD [BlogID] uniqueidentifier NULL  
212GO
213UPDATE [be_Roles] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
214GO
215ALTER TABLE [be_Roles] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
216GO
217CREATE UNIQUE INDEX [idx_be_Roles_BlogID_Role] ON [be_Roles] ([BlogID] ASC,[Role] ASC);
218GO
219ALTER TABLE [be_Settings] ADD [SettingRowId] int NOT NULL  IDENTITY (63,1)
220GO
221ALTER TABLE [be_Settings] ADD [BlogId] uniqueidentifier NULL  
222GO
223UPDATE [be_Settings] SET [BlogId] = '27604F05-86AD-47EF-9E05-950BB762570C'
224GO
225ALTER TABLE [be_Settings] ALTER COLUMN [BlogId] uniqueidentifier NOT NULL
226GO
227ALTER TABLE [be_Settings] DROP CONSTRAINT [PK_be_Settings_SettingName]
228GO
229ALTER TABLE [be_Settings] ADD CONSTRAINT [PK_be_Settings_SettingRowId] PRIMARY KEY ([SettingRowId]);
230GO
231CREATE INDEX [idx_be_Settings_BlogId] ON [be_Settings] ([BlogId] ASC);
232GO
233ALTER TABLE [be_StopWords] ADD [StopWordRowId] int NOT NULL  IDENTITY (109,1)
234GO
235ALTER TABLE [be_StopWords] ADD [BlogId] uniqueidentifier NULL  
236GO
237UPDATE [be_StopWords] SET [BlogId] = '27604F05-86AD-47EF-9E05-950BB762570C'
238GO
239ALTER TABLE [be_StopWords] ALTER COLUMN [BlogId] uniqueidentifier NOT NULL
240GO
241ALTER TABLE [be_StopWords] DROP CONSTRAINT [PK_be_StopWords_StopWord]
242GO
243ALTER TABLE [be_StopWords] ADD CONSTRAINT [PK_be_StopWords_StopWordRowId] PRIMARY KEY ([StopWordRowId]);
244GO
245CREATE INDEX [idx_be_StopWords_BlogId] ON [be_StopWords] ([BlogId] ASC);
246GO
247CREATE TABLE [be_UserRoles_temp] (
248  [UserRoleID] int NOT NULL  IDENTITY (2,1)
249, [BlogID] uniqueidentifier NOT NULL
250, [UserName] nvarchar(100) NOT NULL
251, [Role] nvarchar(100) NOT NULL
252);
253GO
254INSERT INTO [be_UserRoles_temp] ([BlogID],[UserName],[Role])
255SELECT '27604F05-86AD-47EF-9E05-950BB762570C', u.[UserName], r.[Role]
256FROM [be_UserRoles] AS ur
257INNER JOIN [be_Users] AS u ON u.[UserID] = ur.[UserID]
258INNER JOIN [be_Roles] AS r ON r.[RoleID] = ur.[RoleID]
259GO
260DROP TABLE [be_UserRoles]
261GO
262SP_RENAME 'be_UserRoles_temp', 'be_UserRoles'
263GO
264ALTER TABLE [be_UserRoles] ADD CONSTRAINT [PK_be_UserRoles_UserRoleID] PRIMARY KEY ([UserRoleID]);
265GO
266CREATE INDEX [idx_be_UserRoles_BlogId] ON [be_UserRoles] ([BlogID] ASC);
267GO
268ALTER TABLE [be_Users] ADD [BlogID] uniqueidentifier NULL  
269GO
270UPDATE [be_Users] SET [BlogID] = '27604F05-86AD-47EF-9E05-950BB762570C'
271GO
272ALTER TABLE [be_Users] ALTER COLUMN [BlogID] uniqueidentifier NOT NULL
273GO
274CREATE INDEX [idx_be_Users_BlogId_UserName] ON [be_Users] ([BlogID] ASC,[UserName] ASC);
275GO
276