PageRenderTime 24ms CodeModel.GetById 13ms app.highlight 5ms RepoModel.GetById 1ms app.codeStats 0ms

/BlogEngine/BlogEngine.NET/setup/SQLite/SQLiteUpgradeFrom2.0To2.5.txt

#
Plain Text | 438 lines | 360 code | 78 blank | 0 comment | 0 complexity | 61cb4c5be3fcbadebcc4b67743574509 MD5 | raw file
  1--
  2-- Add be_Blogs
  3--
  4CREATE TABLE be_Blogs (
  5[BlogRowId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  6[BlogId] VARCHAR(36) NOT NULL,
  7[BlogName] VARCHAR(255) NOT NULL,
  8[Hostname] VARCHAR(255) NOT NULL,
  9[IsAnyTextBeforeHostnameAccepted] BOOLEAN NOT NULL,
 10[StorageContainerName] VARCHAR(255) NOT NULL,
 11[VirtualPath] VARCHAR(255) NOT NULL,
 12[IsPrimary] BOOLEAN NOT NULL DEFAULT 0,
 13[IsActive] BOOLEAN NOT NULL DEFAULT 1);
 14
 15INSERT INTO be_Blogs (BlogId, BlogName, Hostname, IsAnyTextBeforeHostnameAccepted, StorageContainerName, VirtualPath, IsPrimary, IsActive)
 16VALUES ('27604f05-86ad-47ef-9e05-950bb762570c', 'Primary', '', 0, '', '~/', 1, 1);
 17
 18--
 19-- Update be_Users
 20--
 21ALTER TABLE be_Users
 22ADD BlogID VARCHAR(36) NULL;
 23
 24UPDATE be_Users SET BlogID = '27604f05-86ad-47ef-9e05-950bb762570c';
 25
 26CREATE INDEX idx_be_Users_BlogId_UserName
 27ON be_Users (BlogID, UserName);
 28
 29--
 30-- Update be_UserRoles
 31--
 32CREATE TABLE be_UserRolesNew (
 33[UserRoleID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
 34[BlogID] VARCHAR(36) NOT NULL,
 35[UserName] VARCHAR(100) NOT NULL,
 36[Role] VARCHAR(100) NOT NULL);
 37
 38INSERT INTO be_UserRolesNew
 39SELECT ur.UserRoleID, '27604f05-86ad-47ef-9e05-950bb762570c', u.UserName, r.Role
 40FROM be_UserRoles ur
 41INNER JOIN be_Users u ON ur.UserID = u.UserID
 42INNER JOIN be_Roles r ON ur.RoleID = r.RoleID;
 43
 44DROP TABLE be_UserRoles;
 45ALTER TABLE be_UserRolesNew RENAME TO be_UserRoles;
 46
 47CREATE INDEX idx_be_UserRoles_BlogId
 48ON be_UserRoles (BlogID);
 49
 50--
 51-- Update be_StopWords
 52--
 53CREATE TABLE be_StopWordsNew (
 54[StopWordsRowId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 55[BlogId] VARCHAR(36) NOT NULL,
 56[StopWord] VARCHAR(50) NOT NULL);
 57
 58INSERT INTO be_StopWordsNew (BlogId, StopWord)
 59SELECT '27604f05-86ad-47ef-9e05-950bb762570c', o.StopWord
 60FROM be_StopWords o;
 61
 62DROP TABLE be_StopWords;
 63ALTER TABLE be_StopWordsNew RENAME TO be_StopWords;
 64
 65CREATE INDEX idx_be_StopWords_BlogId
 66ON be_StopWords (BlogId);
 67
 68--
 69-- Update be_Settings
 70--
 71CREATE TABLE be_SettingsNew (
 72[SettingsRowId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 73[BlogId] VARCHAR(36) NOT NULL,
 74[SettingName] VARCHAR(50),
 75[SettingValue] TEXT NULL);
 76
 77INSERT INTO be_SettingsNew (BlogId, SettingName, SettingValue)
 78SELECT '27604f05-86ad-47ef-9e05-950bb762570c', s.SettingName, s.SettingValue
 79FROM be_Settings s;
 80
 81DROP TABLE be_Settings;
 82ALTER TABLE be_SettingsNew RENAME TO be_Settings;
 83
 84CREATE INDEX idx_be_Settings_BlogId
 85ON be_Settings (BlogId);
 86
 87--
 88-- Update be_Roles
 89--
 90ALTER TABLE be_Roles
 91ADD BlogID VARCHAR(36) NULL;
 92
 93UPDATE be_Roles SET BlogID = '27604f05-86ad-47ef-9e05-950bb762570c';
 94
 95CREATE INDEX idx_be_Roles_BlogId_Role
 96ON be_Roles (BlogID, Role);
 97
 98--
 99-- Update be_Rights
100--
101CREATE TABLE be_RightsNew (
102[RightRowId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
103[BlogId] VARCHAR(36) NOT NULL,
104[RightName] VARCHAR(100));
105
106INSERT INTO be_RightsNew (BlogId, RightName)
107SELECT '27604f05-86ad-47ef-9e05-950bb762570c', r.RightName
108FROM be_Rights r;
109
110DROP TABLE be_Rights;
111ALTER TABLE be_RightsNew RENAME TO be_Rights;
112
113CREATE INDEX idx_be_Rights_BlogId
114ON be_Rights (BlogId);
115
116--
117-- Update be_RightRoles
118--
119CREATE TABLE be_RightRolesNew (
120[RightRoleRowId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
121[BlogId] VARCHAR(36) NOT NULL,
122[RightName] VARCHAR(100),
123[Role] VARCHAR(100));
124
125INSERT INTO be_RightRolesNew (BlogId, RightName, Role)
126SELECT '27604f05-86ad-47ef-9e05-950bb762570c', r.RightName, r.Role
127FROM be_RightRoles r;
128
129DROP TABLE be_RightRoles;
130ALTER TABLE be_RightRolesNew RENAME TO be_RightRoles;
131
132CREATE INDEX idx_be_RightRoles_BlogId
133ON be_RightRoles (BlogId);
134
135--
136-- Update be_BlogRollItems
137--
138CREATE TABLE be_BlogRollItemsNew (
139[BlogRollRowId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
140[BlogId] VARCHAR(36) NOT NULL,
141[BlogRollId] VARCHAR(36) NOT NULL,
142[Title] VARCHAR(255) NULL,
143[Description] TEXT NULL,
144[BlogUrl] VARCHAR(255) NOT NULL,
145[FeedUrl] VARCHAR(255) NULL,
146[Xfn] VARCHAR(255) NULL,
147[SortIndex] INTEGER NOT NULL
148);
149
150INSERT INTO be_BlogRollItemsNew (BlogId, BlogRollId, Title, Description, BlogUrl, FeedUrl, Xfn, SortIndex)
151SELECT '27604f05-86ad-47ef-9e05-950bb762570c', b.BlogRollId, b.Title, b.Description, b.BlogUrl, b.FeedUrl, b.Xfn, b.SortIndex
152FROM be_BlogRollItems b;
153
154DROP TABLE be_BlogRollItems;
155ALTER TABLE be_BlogRollItemsNew RENAME TO be_BlogRollItems;
156
157CREATE INDEX idx_be_BlogRollItems_BlogId
158ON be_BlogRollItems (BlogId);
159
160--
161-- Update be_DataStoreSettings
162--
163CREATE TABLE be_DataStoreSettingsNew (
164[DataStoreSettingRowId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
165[BlogId] VARCHAR(36) NOT NULL,
166[ExtensionType] VARCHAR(50) NOT NULL,
167[ExtensionId] VARCHAR(100) NULL,
168[Settings] TEXT NULL
169);
170
171INSERT INTO be_DataStoreSettingsNew (BlogId, ExtensionType, ExtensionId, Settings)
172SELECT '27604f05-86ad-47ef-9e05-950bb762570c', b.ExtensionType, b.ExtensionId, b.Settings
173FROM be_DataStoreSettings b;
174
175DROP TABLE be_DataStoreSettings;
176ALTER TABLE be_DataStoreSettingsNew RENAME TO be_DataStoreSettings;
177
178CREATE INDEX idx_be_DataStoreSettings_BlogId
179ON be_DataStoreSettings (BlogId);
180
181--
182-- Update be_PingService
183--
184CREATE TABLE be_PingServiceNew (
185[PingServiceID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
186[BlogID] VARCHAR(36) NOT NULL,
187[Link] VARCHAR(255) NOT NULL
188);
189
190INSERT INTO be_PingServiceNew (BlogID, Link)
191SELECT '27604f05-86ad-47ef-9e05-950bb762570c', p.Link
192FROM be_PingService p;
193
194DROP TABLE be_PingService;
195ALTER TABLE be_PingServiceNew RENAME TO be_PingService;
196
197CREATE INDEX idx_be_PingService_BlogID
198ON be_PingService (BlogID);
199
200--
201-- Update be_Referrers
202--
203CREATE TABLE be_ReferrersNew (
204[ReferrerRowId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
205[BlogId] VARCHAR(36) NOT NULL,
206[ReferrerId] VARCHAR(36) NOT NULL,
207[ReferralDay] DATE DEFAULT CURRENT_DATE NOT NULL,
208[ReferrerUrl] VARCHAR(255) NOT NULL,
209[ReferralCount] INTEGER NOT NULL,
210[Url] VARCHAR(255) NULL,
211[IsSpam] BOOLEAN NULL
212);
213
214INSERT INTO be_ReferrersNew (BlogId, ReferrerId, ReferralDay, ReferrerUrl, ReferralCount, Url, IsSpam)
215SELECT '27604f05-86ad-47ef-9e05-950bb762570c', r.ReferrerId, r.ReferralDay, r.ReferrerUrl, r.ReferralCount, r.Url, r.IsSpam
216FROM be_Referrers r;
217
218DROP TABLE be_Referrers;
219ALTER TABLE be_ReferrersNew RENAME TO be_Referrers;
220
221CREATE INDEX idx_be_Referrers_BlogId
222ON be_Referrers (BlogId);
223
224--
225-- Update be_Profiles
226--
227CREATE TABLE be_ProfilesNew (
228[ProfileID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
229[BlogID] VARCHAR(36) NOT NULL,
230[UserName] VARCHAR(100) NOT NULL,
231[SettingName] VARCHAR(200) NOT NULL,
232[SettingValue] TEXT NOT NULL
233);
234
235INSERT INTO be_ProfilesNew (BlogID, UserName, SettingName, SettingValue)
236SELECT '27604f05-86ad-47ef-9e05-950bb762570c', p.UserName, p.SettingName, p.SettingValue
237FROM be_Profiles p;
238
239DROP TABLE be_Profiles;
240ALTER TABLE be_ProfilesNew RENAME TO be_Profiles;
241
242CREATE INDEX idx_be_Profiles_BlogID
243ON be_Profiles (BlogID);
244
245--
246-- Update be_Pages
247--
248CREATE TABLE be_PagesNew (
249[PageRowID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
250[BlogID] VARCHAR(36) NOT NULL,
251[PageID] VARCHAR(36) NOT NULL,
252[Title] VARCHAR(255) NOT NULL,
253[Description] TEXT NOT NULL,
254[PageContent] TEXT NOT NULL,
255[Keywords] TEXT NOT NULL,
256[DateCreated] DATETIME NOT NULL,
257[DateModified] DATETIME NOT NULL,
258[IsPublished] BOOLEAN NOT NULL,
259[IsFrontPage] BOOLEAN NOT NULL,
260[Parent] VARCHAR(36) NOT NULL,
261[ShowInList] BOOLEAN NOT NULL,
262[Slug] VARCHAR(255) NOT NULL,
263[IsDeleted] BOOLEAN NOT NULL
264);
265
266INSERT INTO be_PagesNew
267	(BlogID, PageID, Title, Description, PageContent, Keywords,
268	 DateCreated, DateModified, IsPublished, IsFrontPage, Parent,
269	 ShowInList, Slug, IsDeleted)
270SELECT
271	'27604f05-86ad-47ef-9e05-950bb762570c', p.PageID, p.Title, p.Description, p.PageContent, p.Keywords,
272	 p.DateCreated, p.DateModified, p.IsPublished, p.IsFrontPage, p.Parent,
273	 p.ShowInList, coalesce(p.Slug, ''), p.IsDeleted
274FROM be_Pages p;
275
276DROP TABLE be_Pages;
277ALTER TABLE be_PagesNew RENAME TO be_Pages;
278
279CREATE INDEX idx_be_Pages_BlogID
280ON be_Pages (BlogID);
281
282--
283-- Update be_Categories
284--
285CREATE TABLE be_CategoriesNew (
286[CategoryRowID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
287[BlogID] VARCHAR(36) NOT NULL,
288[CategoryID] VARCHAR(36) NOT NULL,
289[CategoryName] VARCHAR(50) NOT NULL,
290[Description] VARCHAR(200) NOT NULL,
291[ParentID] VARCHAR(36) NULL
292);
293
294INSERT INTO be_CategoriesNew (BlogID, CategoryID, CategoryName, Description, ParentID)
295SELECT '27604f05-86ad-47ef-9e05-950bb762570c', c.CategoryID, c.CategoryName, coalesce(c.Description, ''), c.ParentID
296FROM be_Categories c;
297
298DROP TABLE be_Categories;
299ALTER TABLE be_CategoriesNew RENAME TO be_Categories;
300
301CREATE INDEX idx_be_Categories_BlogID
302ON be_Categories (BlogID);
303
304--
305-- Update be_PostCategory
306--
307CREATE TABLE be_PostCategoryNew (
308[PostCategoryID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
309[BlogID] VARCHAR(36) NOT NULL,
310[PostID] VARCHAR(36) NOT NULL,
311[CategoryID] VARCHAR(36) NOT NULL
312);
313
314INSERT INTO be_PostCategoryNew (BlogID, PostID, CategoryID)
315SELECT '27604f05-86ad-47ef-9e05-950bb762570c', p.PostID, p.CategoryID
316FROM be_PostCategory p;
317
318DROP TABLE be_PostCategory;
319ALTER TABLE be_PostCategoryNew RENAME TO be_PostCategory;
320
321CREATE INDEX idx_be_PostCategory_BlogID_PostID
322ON be_PostCategory (BlogID, PostID);
323
324--
325-- Update be_PostNotify
326--
327CREATE TABLE be_PostNotifyNew (
328[PostNotifyID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
329[BlogID] VARCHAR(36) NOT NULL,
330[PostID] VARCHAR(36) NOT NULL,
331[NotifyAddress] VARCHAR(255) NOT NULL
332);
333
334INSERT INTO be_PostNotifyNew (BlogID, PostID, NotifyAddress)
335SELECT '27604f05-86ad-47ef-9e05-950bb762570c', p.PostID, p.NotifyAddress
336FROM be_PostNotify p;
337
338DROP TABLE be_PostNotify;
339ALTER TABLE be_PostNotifyNew RENAME TO be_PostNotify;
340
341CREATE INDEX idx_be_PostNotify_BlogID_PostID
342ON be_PostNotify (BlogID, PostID);
343
344--
345-- Update be_PostTag
346--
347CREATE TABLE be_PostTagNew (
348[PostTagID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
349[BlogID] VARCHAR(36) NOT NULL,
350[PostID] VARCHAR(36) NOT NULL,
351[Tag] VARCHAR(50) NOT NULL
352);
353
354INSERT INTO be_PostTagNew (BlogID, PostID, Tag)
355SELECT '27604f05-86ad-47ef-9e05-950bb762570c', p.PostID, p.Tag
356FROM be_PostTag p;
357
358DROP TABLE be_PostTag;
359ALTER TABLE be_PostTagNew RENAME TO be_PostTag;
360
361CREATE INDEX idx_be_PostTag_BlogID_PostID
362ON be_PostTag (BlogID, PostID);
363
364--
365-- Update be_PostComment
366--
367CREATE TABLE be_PostCommentNew (
368[PostCommentRowID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
369[BlogID] VARCHAR(36) NOT NULL,
370[PostCommentID] VARCHAR(36) NOT NULL,
371[PostID] VARCHAR(36) NOT NULL,
372[ParentCommentID] VARCHAR(36) NOT NULL,
373[CommentDate] DATETIME NOT NULL,
374[Author] VARCHAR(255) NOT NULL,
375[Email] VARCHAR(255) NOT NULL,
376[Website] VARCHAR(255) NOT NULL,
377[Comment] TEXT NOT NULL,
378[Country] VARCHAR(255) NOT NULL,
379[Ip] VARCHAR(50) NOT NULL,
380[IsApproved] BOOLEAN NOT NULL,
381[ModeratedBy] VARCHAR(100) NOT NULL,
382[Avatar] VARCHAR(255) NOT NULL,
383[IsSpam] BOOLEAN NOT NULL,
384[IsDeleted] BOOLEAN NOT NULL
385);
386
387INSERT INTO be_PostCommentNew
388	(BlogID, PostCommentID, PostID, ParentCommentID, CommentDate, Author,
389	 Email, Website, Comment, Country, Ip, IsApproved, ModeratedBy, Avatar,
390	 IsSpam, IsDeleted)
391SELECT '27604f05-86ad-47ef-9e05-950bb762570c', p.PostCommentID, p.PostID, p.ParentCommentID, p.CommentDate, p.Author,
392	 p.Email, p.Website, p.Comment, p.Country, p.Ip, p.IsApproved, coalesce(p.ModeratedBy,''),coalesce(p.Avatar, ''), p.IsSpam, p.IsDeleted
393FROM be_PostComment p;
394
395DROP TABLE be_PostComment;
396ALTER TABLE be_PostCommentNew RENAME TO be_PostComment;
397
398CREATE INDEX idx_be_PostComment_BlogID_PostID
399ON be_PostComment (BlogID, PostID);
400
401--
402-- Update be_Posts
403--
404CREATE TABLE be_PostsNew (
405[PostRowID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
406[BlogID] VARCHAR(36) NOT NULL,
407[PostID] VARCHAR(36) NOT NULL,
408[Title] VARCHAR(255) NOT NULL,
409[Description] TEXT NOT NULL,
410[PostContent] TEXT NOT NULL,
411[DateCreated] DATETIME NOT NULL,
412[DateModified] DATETIME NOT NULL,
413[Author] VARCHAR(50) NOT NULL,
414[IsPublished] BOOLEAN NOT NULL,
415[IsCommentEnabled] BOOLEAN NOT NULL,
416[Raters] INTEGER NOT NULL,
417[Rating] REAL NOT NULL,
418[Slug] VARCHAR(255) NOT NULL,
419[IsDeleted] BOOLEAN NOT NULL
420);
421
422INSERT INTO be_PostsNew
423	(BlogID, PostID, Title, Description, PostContent, DateCreated,
424	 DateModified, Author, IsPublished, IsCommentEnabled, Raters,
425	 Rating, Slug, IsDeleted)
426SELECT '27604f05-86ad-47ef-9e05-950bb762570c', p.PostID, p.Title, p.Description, p.PostContent, p.DateCreated,
427	 p.DateModified, p.Author, p.IsPublished, p.IsCommentEnabled, p.Raters,
428	 p.Rating, p.Slug, p.IsDeleted
429FROM be_Posts p;
430
431DROP TABLE be_Posts;
432ALTER TABLE be_PostsNew RENAME TO be_Posts;
433
434CREATE INDEX idx_be_Posts_BlogID_PostID
435ON be_Posts (BlogID, PostID);
436
437
438