Migrate DB from MsSql to Sqlite

The Page describes the steps to migrate an existing YAF.NET Database (v3.x.x) from MS SQL to Sqlite.

1. Upgrade first to the latest version of YAF.NET
2. Download the Converter from https://github.com/w8tcha/mssql2sqlite/releases
3. Convert the db
4. create the missing views

-- yaf_vaccess source

CREATE VIEW yaf_vaccess as  select a.UserID,x_1.ForumID,MAX(b.Flags & 1) AS IsAdmin,MAX(b.Flags & 2) AS IsGuest,MAX(b.Flags & 8) AS IsForumModerator,(SELECT COUNT(1) AS Expr1 FROM yaf_UserGroup AS v INNER JOIN yaf_Group AS w ON v.GroupID = w.GroupID CROSS JOIN  yaf_ForumAccess AS x CROSS JOIN  yaf_AccessMask AS y WHERE(v.UserID = a.UserID) AND(x.GroupID = w.GroupID) AND(y.AccessMaskID = x.AccessMaskID) AND(y.Flags & 64 <> 0)) AS IsModerator,MAX(x_1.ReadAccess) AS ReadAccess,MAX(x_1.PostAccess) AS PostAccess,MAX(x_1.ReplyAccess) AS ReplyAccess,MAX(x_1.PriorityAccess) AS PriorityAccess,MAX(x_1.PollAccess) AS PollAccess,MAX(x_1.VoteAccess) AS VoteAccess,MAX(x_1.ModeratorAccess) AS ModeratorAccess,MAX(x_1.EditAccess) AS EditAccess,MAX(x_1.DeleteAccess) AS DeleteAccess,MAX(x_1.UploadAccess) AS UploadAccess,MAX(x_1.DownloadAccess) AS DownloadAccess  FROM yaf_vaccessfull x_1  INNER JOIN  yaf_UserGroup AS a ON a.UserID = x_1.UserID  INNER JOIN yaf_Group AS b ON b.GroupID = a.GroupID  GROUP BY a.UserID, x_1.ForumID;

-- yaf_vaccess_group source

CREATE VIEW yaf_vaccess_group as  select e.BoardID,b.UserID,c.ForumID,d.AccessMaskID,b.GroupID,d.Flags & 1 AS ReadAccess,d.Flags & 2 AS PostAccess,d.Flags & 4 AS ReplyAccess,d.Flags & 8 AS PriorityAccess,d.Flags & 16 AS PollAccess,d.Flags & 32 AS VoteAccess,d.Flags & 64 AS ModeratorAccess,d.Flags & 128 AS EditAccess,d.Flags & 256 AS DeleteAccess,d.Flags & 512 AS UploadAccess,d.Flags & 1024 AS DownloadAccess,e.Flags & 1 AS AdminGroup from yaf_UserGroup AS b INNER JOIN yaf_ForumAccess AS c on c.GroupID=b.GroupID INNER JOIN yaf_AccessMask AS d on d.AccessMaskID=c.AccessMaskID INNER JOIN yaf_Group AS e on e.GroupID=b.GroupID;

-- yaf_vaccess_null source

CREATE VIEW yaf_vaccess_null as  select a.UserID,0 AS ForumID,0 AS ReadAccess,0 AS PostAccess,0 AS ReplyAccess,0 AS PriorityAccess,0 AS PollAccess,0 AS VoteAccess,0 AS ModeratorAccess,0 AS EditAccess,0 AS DeleteAccess,0 AS UploadAccess,0 AS DownloadAccess,0 AS AdminGroup  from "yaf_User" AS a;

-- yaf_vaccess_user source

CREATE VIEW yaf_vaccess_user as  select b.UserID,b.ForumID,c.AccessMaskID,c.Flags & 1 AS ReadAccess,c.Flags & 2 AS PostAccess,c.Flags & 4 AS ReplyAccess,c.Flags & 8 AS PriorityAccess,c.Flags & 16 AS PollAccess,c.Flags & 32 AS VoteAccess,c.Flags & 64 AS ModeratorAccess,c.Flags & 128 AS EditAccess,c.Flags & 256 AS DeleteAccess,c.Flags & 512 AS UploadAccess,c.Flags & 1024 AS DownloadAccess from yaf_UserForum AS b INNER JOIN yaf_AccessMask AS c on c.AccessMaskID=b.AccessMaskID;

-- yaf_vaccessfull source

CREATE VIEW yaf_vaccessfull as  select b.UserID,b.ForumID,c.Flags & 1 AS ReadAccess, c.Flags & 2 AS PostAccess,c.Flags & 4 AS ReplyAccess,c.Flags & 8 AS PriorityAccess,c.Flags & 16 AS PollAccess,c.Flags & 32 AS VoteAccess,c.Flags & 64 AS ModeratorAccess,c.Flags & 128 AS EditAccess,c.Flags & 256 AS DeleteAccess,c.Flags & 512 AS UploadAccess,c.Flags & 1024 AS DownloadAccess,0 AS AdminGroup FROM yaf_UserForum AS b INNER JOIN yaf_AccessMask AS c ON c.AccessMaskID = b.AccessMaskID UNION ALL  SELECT b.UserID,c.ForumID,d.Flags & 1 AS ReadAccess,d.Flags & 2 AS PostAccess,d.Flags & 4 AS ReplyAccess,d.Flags & 8 AS PriorityAccess,d.Flags & 16 AS PollAccess,d.Flags & 32 AS VoteAccess,d.Flags & 64 AS ModeratorAccess,d.Flags & 128 AS EditAccess,d.Flags & 256 AS DeleteAccess,d.Flags & 512 AS UploadAccess,d.Flags & 1024 AS DownloadAccess,e.Flags & 1 AS AdminGroup FROM yaf_UserGroup AS b INNER JOIN yaf_ForumAccess AS c ON c.GroupID = b.GroupID  INNER JOIN yaf_AccessMask AS d ON d.AccessMaskID = c.AccessMaskID  INNER JOIN yaf_Group e ON e.GroupID = b.GroupID  UNION ALL  SELECT UserID,0 AS ForumID,0 AS ReadAccess,0 AS PostAccess,0 AS ReplyAccess,0 AS PriorityAccess,0 AS PollAccess,0 AS VoteAccess,0 AS ModeratorAccess,0 AS EditAccess,0 AS DeleteAccess, 0 AS UploadAccess,0 AS DownloadAccess,0 AS AdminGroup FROM "yaf_User" AS a;

5. Download the sqlite upgrade package...
6. Update the connection string in the web.config (db.config)