Forum schema

I’ve been working slowly, but steadily, on the forum lately and thought I’d put up the schema I’ve been testing against for the past few weeks. This is for SQLite since it’s very simple to setup, change things and if necessary, completely scrap and start over without having to mess with a DB server.

I’ve been using the SQLite Manager plugin for Firefox to make quick changes to the schema on the go. I’ll post snippets of the rest of the script in increments when I have time to clean up and maybe I’ll post the whole thing on GitHub for the alpha release.

Maybe this will serve as a good starting point for someone else looking for ideas as well.

DROP TABLE IF EXISTS users;
CREATE TABLE users (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	username VARCHAR NOT NULL UNIQUE, 
	password TEXT NOT NULL, 
	email VARCHAR NOT NULL UNIQUE, 
	token VARCHAR NOT NULL,
	bio TEXT, 
	pubkey TEXT, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	updated_at DATETIME NOT NULL , 
	status INTEGER NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX users_name_idx ON users (username, email);
CREATE INDEX users_activity_idx ON users (created_at, updated_at);
CREATE INDEX users_status_idx ON users (status);



DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	title VARCHAR,
	url VARCHAR,
	author VARCHAR NOT NULL, 
	user_id INTEGER NOT NULL DEFAULT 0, 
	summary TEXT NOT NULL, 
	body TEXT NULL, 
	reply_count INTEGER NOT NULL DEFAULT 0, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	updated_at DATETIME NOT NULL, 
	lastreply_at DATETIME NOT NULL, 
	status INTEGER NOT NULL  DEFAULT 0
);

CREATE INDEX posts_activity_idx ON posts (created_at, updated_at, lastreply_at);
CREATE INDEX posts_author_idx ON posts (author, user_id);
CREATE INDEX posts_url_idx ON posts (url) WHERE url IS NOT NULL;
CREATE INDEX posts_status_idx ON posts (status);



DROP TABLE IF EXISTS posts_family;
CREATE TABLE posts_family(
	parent_id INTEGER NOT NULL, 
	child_id INTEGER NOT NULL, 
	last_author VARCHAR NOT NULL, 
	last_id INTEGER NOT NULL, 
	lastreply_at DATETIME NOT NULL
);

CREATE INDEX posts_family_idx ON posts_family (parent_id, child_id);
CREATE INDEX posts_family_activity_idx ON posts_family 
	(last_author, last_id, lastreply_at);



DROP TABLE IF EXISTS posts_flags;
CREATE TABLE posts_flags(
	post_id INTEGER NOT NULL, 
	user_id INTEGER NOT NULL, 
	reason VARCHAR NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	status INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX posts_flags_idx ON posts_flags (post_id, user_id);
CREATE INDEX posts_flags_created_at_idx ON posts_flags (created_at);
CREATE INDEX posts_flags_status_idx ON posts_flags (status);



DROP TABLE IF EXISTS taxonomy;
CREATE TABLE taxonomy (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	label VARCHAR,
	term VARCHAR,
	post_count INTEGER NOT NULL DEFAULT 0, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	body TEXT NULL
);

CREATE INDEX taxonomy_created_at_idx ON taxonomy (created_at);
CREATE UNIQUE INDEX taxonomy_term_idx ON taxonomy (label, term);
CREATE INDEX taxonomy_count_idx ON taxonomy (post_count);



DROP TABLE IF EXISTS taxonomy_posts;
CREATE TABLE taxonomy_posts(
	taxonomy_id INTEGER NOT NULL, 
	post_id INTEGER NOT NULL
);

CREATE UNIQUE INDEX taxonomy_posts_idx ON taxonomy_posts (taxonomy_id, post_id);



DROP TABLE IF EXISTS pms;
CREATE TABLE pms (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	title VARCHAR,
	sender_id INTEGER NOT NULL, 
	receiver_id INTEGER NOT NULL, 
	body TEXT NULL, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	status INTEGER NOT NULL  DEFAULT 0
);

CREATE INDEX pms_contact_idx ON pms (sender_id, receiver_id);
CREATE INDEX pms_created_idx ON pms (created_at);
CREATE INDEX pms_status_idx ON pms (status);



DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions(
	id VARCHAR PRIMARY KEY, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	updated_at DATETIME NOT NULL,
	skey VARCHAR NOT NULL,
	data TEXT NOT NULL
);

CREATE INDEX sessions_activity_idx ON sessions (created_at, updated_at);



DROP TABLE IF EXISTS firewall;
CREATE TABLE firewall(
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	label VARCHAR,
	response VARCHAR,
	ip VARCHAR,
	ua VARCHAR,
	protocol VARCHAR,
	method VARCHAR,
	uri VARCHAR,
	headers TEXT NOT NULL, 
	request_at DATETIME NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX firewall_ip_idx ON firewall (ip, label);
CREATE INDEX firewall_response_idx ON firewall (response);
CREATE INDEX firewall_activity_idx ON firewall (request_at, created_at);



DROP TABLE IF EXISTS blocklist;
CREATE TABLE blocklist(
	ip VARCHAR PRIMARY KEY, 
	label VARCHAR,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	expires_at DATETIME NOT NULL
);

CREATE INDEX blocklist_label_idx ON blocklist (label);
CREATE INDEX blocklist_dates_idx ON blocklist (created_at, expires_at);



DROP TABLE IF EXISTS settings;
CREATE TABLE settings(
	id VARCHAR PRIMARY KEY, 
	data TEXT NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX settings_created_idx ON settings (created_at);



DROP TABLE IF EXISTS history;
CREATE TABLE history(
	post_id INTEGER NOT NULL,
	user_id INTEGER NOT NULL  DEFAULT 0, 
	anon_id VARCHAR, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX history_idx ON history (post_id, user_id, anon_id);
CREATE INDEX history_created_idx ON history (created_at);



DROP TABLE IF EXISTS actions;
CREATE TABLE actions(
	post_id INTEGER NOT NULL,
	user_id INTEGER NOT NULL  DEFAULT 0, 
	anon_id VARCHAR, 
	command VARCHAR, 
	auth_key VARCHAR, 
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	expires_at DATETIME NOT NULL
);

CREATE UNIQUE INDEX actions_idx ON actions (post_id, user_id, anon_id);
CREATE INDEX actions_auth_key_idx ON actions (auth_key);
CREATE INDEX actions_activity_idx ON actions (created_at, expires_at);

LINQ to SQL Role Provider

This is following my previous post on the Membership Provider. I decided I might as well add the RoleProvider to go along with it.

/**
 * THIS SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
 * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
 * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
 * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
 * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
 * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
 * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
 */

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Configuration;
using System.Configuration.Provider;
using Osiris.Models;

namespace Osiris.Providers
{
	public sealed class OsirisRoleProvider : RoleProvider
	{

		/*************************************************************************
		 * General settings
		 *************************************************************************/

		private string _applicationName;
		public override string ApplicationName
		{
			get { return _applicationName; }
			set { _applicationName = value; }
		}


		/*************************************************************************
		 * Retrieval methods
		 *************************************************************************/

		/// <summary>
		/// Gets all available user roles
		/// </summary>
		/// <returns>Array of all available roles</returns>
		public override string[] GetAllRoles()
		{
			string[] roles = null;
			using (OsirisDataContext db = new OsirisDataContext())
			{
				roles = (from groups in db.Groups
						 select groups.Title).ToArray();
			}
			return roles;
		}

		/// <summary>
		/// Gets the assigned roles for a particular user.
		/// </summary>
		/// <param name="username">Matching username</param>
		/// <returns>Array of assigned roles</returns>
		public override string[] GetRolesForUser(string username)
		{
			string[] roles = null;
			using (OsirisDataContext db = new OsirisDataContext())
			{
				roles = (from mg in db.MembersInGroups
								  where mg.Member.Username == username
								  select mg.Group.Title).ToArray();
			}
			return roles;
		}

		/// <summary>
		/// Gets all the users in a particular role
		/// </summary>
		public override string[] GetUsersInRole(string roleName)
		{
			// Without paging, this function seems pointless to me,
			// so I didn't implement it. Should be simple enough using the previous code though.
			throw new NotImplementedException();
		}


		/*************************************************************************
		 * Create and Delete methods
		 *************************************************************************/

		/// <summary>
		/// Creates a new role
		/// </summary>
		public override void CreateRole(string roleName)
		{
			// No need to add if it already exists
			if (!RoleExists(roleName))
			{
				Group g = new Group();
				g.Title = roleName;
				g.Signup = false;

				using (OsirisDataContext db = new OsirisDataContext())
				{
					db.Groups.InsertOnSubmit(g);
					db.SubmitChanges();
				}
			}
		}

		/// <summary>
		/// Deletes a given role
		/// </summary>
		/// <param name="roleName">Role name to delete</param>
		/// <param name="throwOnPopulatedRole">Specifies whether the function should throw 
		/// if there are assigned users to this role</param>
		/// <returns>True if successful. Defaults to false</returns>
		public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
		{
			// Return status. Defaults to false.
			bool ret = false;

			// You can only delete an existing role
			if (RoleExists(roleName))
			{
				try
				{
					using (OsirisDataContext db = new OsirisDataContext())
					{
						if (throwOnPopulatedRole)
						{
							int[] users = (from mg in db.MembersInGroups
										   where mg.Group.Title == roleName
										   select mg.Member.MemberId).ToArray();

							if (users.Count() > 0)
								throw new ProviderException("Cannot delete roles with users assigned to them");
						}

						Group g = (from groups in db.Groups
								   where groups.Title == roleName
								   select groups).FirstOrDefault();

						db.Groups.DeleteOnSubmit(g);
						db.SubmitChanges();

						ret = true;
					}
				}
				catch { }
			}
			
			return ret;
		}


		/*************************************************************************
		 * Assign/Remove methods
		 *************************************************************************/
		
		/// <summary>
		/// Adds a collection of users to a collection of corresponding roles
		/// </summary>
		public override void AddUsersToRoles(string[] usernames, string[] roleNames)
		{
			// Get the actual available roles
			string[] allRoles = GetAllRoles();

			// See if any of the given roles match the available roles
			IEnumerable<string> roles = allRoles.Intersect(roleNames);

			// There were some roles left after removing non-existent ones
			if (roles.Count() > 0)
			{
				// Cleanup duplicates first
				RemoveUsersFromRoles(usernames, roleNames);

				using (OsirisDataContext db = new OsirisDataContext())
				{
					// Get the user IDs
					List<int> mlist = (from members in db.Members
									   where usernames.Contains(members.Username)
									   select members.MemberId).ToList();

					// Get the group IDs
					List<int> glist = (from groups in db.Groups
									   where roleNames.Contains(groups.Title)
									   select groups.GroupId).ToList();

					// Fresh list of user-role assignments
					List<MembersInGroup> mglist = new List<MembersInGroup>();
					foreach (int m in mlist)
					{
						foreach (int g in glist)
						{
							MembersInGroup mg = new MembersInGroup();
							mg.MemberId = m;
							mg.GroupId = g;
							mglist.Add(mg);
						}
					}

					db.MembersInGroups.InsertAllOnSubmit(mglist);
					db.SubmitChanges();
				}
			}
		}

		/// <summary>
		/// Remove a collection of users from a collection of corresponding roles
		/// </summary>
		public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
		{
			// Get the actual available roles
			string[] allRoles = GetAllRoles();

			// See if any of the given roles match the available roles
			IEnumerable<string> roles = allRoles.Intersect(roleNames);

			// There were some roles left after removing non-existent ones
			if (roles.Count() > 0)
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					List<MembersInGroup> mg = (from members in db.MembersInGroups
											   where usernames.Contains(members.Member.Username) &&
											   roleNames.Contains(members.Group.Title)
											   select members).ToList();

					db.MembersInGroups.DeleteAllOnSubmit(mg);
					db.SubmitChanges();
				}
			}
		}


		/*************************************************************************
		 * Searching methods
		 *************************************************************************/

		/// <summary>
		/// Checks if a given username is in a particular role
		/// </summary>
		public override bool IsUserInRole(string username, string roleName)
		{
			// Return status defaults to false
			bool ret = false;

			if (RoleExists(roleName))
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					int c = (from m in db.MembersInGroups
							 where m.Member.Username == username &&
							 m.Group.Title == roleName
							 select m).Count();

					if (c > 0)
						ret = true;
				}
			}

			return ret;
		}

		/// <summary>
		/// Finds a set of users in a given role
		/// </summary>
		public override string[] FindUsersInRole(string roleName, string usernameToMatch)
		{
			// Here's another function that doesn't make sense without paging
			throw new NotImplementedException();
		}

		/// <summary>
		/// Checks if a given role already exists in the database
		/// </summary>
		/// <param name="roleName">Role name to search</param>
		/// <returns>True if the role exists. Defaults to false.</returns>
		public override bool RoleExists(string roleName)
		{
			bool ret = false;

			// If the specified role doesn't exist
			if (!GetAllRoles().Contains(roleName))
				ret = true;
			
			return ret;
		}

		/*************************************************************************
		 * Initialization
		 *************************************************************************/

		/// <summary>
		/// Initialize the RoleProvider
		/// </summary>
		public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
		{
			if (config == null)
				throw new ArgumentNullException("config");

			if (name == null || name.Length == 0)
				name = "OsirisRoleProvider";

			if (String.IsNullOrEmpty(config["description"]))
			{
				config.Remove("description");
				config.Add("description", "Osiris Role Provider");
			}

			// Initialize base class
			base.Initialize(name, config);

			_applicationName = GetConfigValue(config["applicationName"],
				System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);

		}


		/*************************************************************************
		 * Private helper methods
		 *************************************************************************/

		private string GetConfigValue(string configValue, string defaultValue)
		{
			if (String.IsNullOrEmpty(configValue))
				return defaultValue;

			return configValue;
		}
	}
}

LINQ to SQL Membership Provider

It’s been a long time since I posted something programming related here, but luckily, I found some precious little spare time this weekend.

I used to run a portal a while back and a portion of it (namely the login system) was converted to the Provider Model back in the early 2000’s. And that’s where it stood for the better part of a decade. Even after the portal became defunct, I thought about dusting off the old code and reusing it in something. Yesterday, I decided to rewrite the Membership Provider into a LINQ to SQL model.

The following is the end result of an afternoon of conversions. It still hasn’t been fully tested and may contain bugs, errors or other terrible things. And of course, there is plenty of room for improvement.

The LINQ to SQL Dbml follows the following schema :

Database Schema part

As you can see there are parts that are missing, but the protions that pertain to the code below are included.

The MemberContentPrivilege class is based on individual privilege flags and there’s a matching GroupContentPrivilege class as well (not pictured). I created my CMS (called Osiris) originally with ASP in mind. But then with the arrival ASP.Net 2.0, I had the chance to use the existing role based authentication. With updates to the provider model, I kept changing the database until each role and a small number of users had a set of privilege flags with matching content IDs. It was far more granular for my purposes than the default RBAC in the Provider Model.

You can, of course, delete those role specific code sections. I’ve marked them with a link to this post.

WARNING:
I’m still not too comfortable with the WordPress source code formatter, so I’d be careful when copying.

Update

Well that was quick. ;)
I decided to scrap the global DataContext object and went with the “using” method instead. I think this will prevent memory issues.

/**
 * THIS SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
 * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
 * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
 * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
 * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
 * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
 * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
 */

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web.Mvc;
using System.Web.Security;
using System.Web.Configuration;
using System.Security.Cryptography;
using System.Configuration;
using System.Configuration.Provider;
using System.Text;
using Osiris.Models;

namespace Osiris.Providers
{
	public sealed class OsirisMembershipProvider : MembershipProvider
	{
		private MachineKeySection machineKey;

		/*************************************************************************
		 * General settings
		 *************************************************************************/

		private string _applicationName;
		public override string ApplicationName
		{
			get { return _applicationName; }
			set { _applicationName = value; }
		}

		private bool _requiresUniqeEmail;
		public override bool RequiresUniqueEmail
		{
			get { return _requiresUniqeEmail; }
		}


		/*************************************************************************
		 * Private settings
		 *************************************************************************/

		private string _providerName;
		public string ProviderName
		{
			get { return _providerName; }
		}

		private TimeSpan _userIsOnlineTimeWindow;
		public TimeSpan UserIsOnlineTimeWindow
		{
			get { return _userIsOnlineTimeWindow; }
		}



		/*************************************************************************
		 * Password settings
		 *************************************************************************/

		private int _minRequiredNonAlphanumericCharacters;
		public override int MinRequiredNonAlphanumericCharacters
		{
			get { return _minRequiredNonAlphanumericCharacters; }
		}

		private int _minRequiredPasswordLength;
		public override int MinRequiredPasswordLength
		{
			get { return _minRequiredPasswordLength; }
		}

		private bool _enablePasswordReset;
		public override bool EnablePasswordReset
		{
			get { return _enablePasswordReset; }
		}

		private bool _enablePasswordRetrieval;
		public override bool EnablePasswordRetrieval
		{
			get { return _enablePasswordRetrieval; }
		}

		private int _passwordAttemptWindow;
		public override int PasswordAttemptWindow
		{
			get { return _passwordAttemptWindow; }
		}

		private string _passwordStrengthRegularExpression;
		public override string PasswordStrengthRegularExpression
		{
			get { return _passwordStrengthRegularExpression; }
		}

		private MembershipPasswordFormat _passwordFormat;
		public override MembershipPasswordFormat PasswordFormat
		{
			get { return _passwordFormat; }
		}

		private int _maxInvalidPasswordAttempts;
		public override int MaxInvalidPasswordAttempts
		{
			get { return _maxInvalidPasswordAttempts; }
		}

		private bool _requiresQuestionAndAnswer;
		public override bool RequiresQuestionAndAnswer
		{
			get { return _requiresQuestionAndAnswer; }
		}


		/*************************************************************************
		 * User related methods : create, update, unlock, delete methods.
		 *************************************************************************/

		/// <summary>
		/// Creates a new user with a given set of default values
		/// </summary>
		public override MembershipUser CreateUser(string username, string password, string email,
			string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey,
			out MembershipCreateStatus status)
		{
			ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);

			OnValidatingPassword(args);

			if (args.Cancel)
			{
				status = MembershipCreateStatus.InvalidPassword;
				return null;
			}

			if (RequiresUniqueEmail && GetUserNameByEmail(email) != "")
			{
				status = MembershipCreateStatus.DuplicateEmail;
				return null;
			}

			// If no user with this name already exists
			if (GetUser(username, false) == null)
			{
				DateTime createdDate = DateTime.Now;

				string salt = "";
				if (PasswordFormat == MembershipPasswordFormat.Hashed)
				{
					salt = GenerateSalt();
					password = password + salt;
				}

				Member m = new Member();
				m.Username = username;
				m.Password = EncodePassword(password);
				m.PasswordSalt = salt;
				m.Email = email;

				// Set the password retrieval question and answer if they are required
				if (RequiresQuestionAndAnswer)
				{
					m.PasswordQuestion = passwordQuestion;
					m.PasswordAnswer = EncodePassword(passwordAnswer);
				}

				m.IsApproved = isApproved;
				m.IsLockedOut = false;
				m.Comment = "";
				m.CreatedDate = createdDate;
				m.LastLockoutDate = createdDate;
				m.LastLoginDate = createdDate;
				m.LastActivityDate = createdDate;
				m.LastPasswordChangedDate = createdDate;
				m.FailedPasswordAttemptCount = 0;
				m.FailedPasswordAttemptWindowStart = createdDate;
				m.FailedPasswordAnswerAttemptCount = 0;
				m.FailedPasswordAnswerAttemptWindowStart = createdDate;

				try
				{
					using (OsirisDataContext db = new OsirisDataContext())
					{
						// Add the new user to the database
						db.Members.InsertOnSubmit(m);

						// Add the user to a the signup group
						// See the blog post for more details : 
						// https://eksith.wordpress.com/2010/04/04/linq-to-sql-membership
						Group g = (from groups in db.Groups
								   where groups.Signup == true
								   select groups).Single();

						if (g != null)
						{
							MembersInGroup mg = new MembersInGroup();
							mg.Group = g;
							mg.Member = m;
							db.MembersInGroups.InsertOnSubmit(mg);
						}

						// Save changes
						db.SubmitChanges();
					}

					// User creation was a success
					status = MembershipCreateStatus.Success;

					// Return the newly craeted user
					return GetUserFromMember(m);
				}
				catch
				{
					// Something was wrong and the user was rejected
					status = MembershipCreateStatus.UserRejected;
				}
			}
			else
			{
				// There is already a user with this name
				status = MembershipCreateStatus.DuplicateUserName;
			}

			// Something went wrong if we got this far without some sort of status or retun
			if (status != MembershipCreateStatus.UserRejected && status != MembershipCreateStatus.DuplicateUserName)
				status = MembershipCreateStatus.ProviderError;
				
			return null;
		}

		/// <summary>
		/// Updates an existing user with new settings
		/// </summary>
		/// <param name="user">MembershipUser object to modify</param>
		public override void UpdateUser(MembershipUser user)
		{
			using (OsirisDataContext db = new OsirisDataContext())
			{
				Member m = (from members in db.Members
							where members.Username == user.UserName
							select members).Single();

				m.Comment = user.Comment;
				m.Email = user.Email;
				m.IsApproved = user.IsApproved;

				db.SubmitChanges();
			}
		}

		/// <summary>
		/// Unlocks a user (after too many login attempts perhaps)
		/// </summary>
		/// <param name="userName">Username to unlock</param>
		/// <returns>True if successful. Defaults to false.</returns>
		public override bool UnlockUser(string userName)
		{
			// Return status defaults to false
			bool ret = false;
			try
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					Member m = (from members in db.Members
								where members.Username == userName
								select members).Single();

					m.IsLockedOut = false;

					// Save changes in the database
					db.SubmitChanges();
				}

				// A user was found and nothing was thrown
				ret = true;
			}
			catch
			{
				// Couldn't find the user or there was an error
				ret = false;
			}

			return ret;
		}

		/// <summary>
		/// Permanently deletes a user from the database
		/// </summary>
		/// <param name="username">Username to delete</param>
		/// <param name="deleteAllRelatedData">Should or shouldn't delete related user data</param>
		/// <returns>True if successful. Defaults to false.</returns>
		public override bool DeleteUser(string username, bool deleteAllRelatedData)
		{
			// Return status defaults to false.
			// When in doubt, always say "NO".
			bool ret = false;

			try
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					Member m = (from members in db.Members
								where members.Username == username
								select members).Single();

					db.Members.DeleteOnSubmit(m);

					// Delete all releated group/role/profile data
					// See the blog post for more details : 
					// https://eksith.wordpress.com/2010/04/04/linq-to-sql-membership
					if (deleteAllRelatedData)
					{
						List<MembersInGroup> g = (from mg in db.MembersInGroups
												  where mg.Member.Username == username
												  select mg).ToList();

						List<MemberContentPrivilege> mc = (from mp in db.MemberContentPrivileges
														   where mp.Member.Username == username
														   select mp).ToList();

						db.MembersInGroups.DeleteAllOnSubmit(g);
						db.MemberContentPrivileges.DeleteAllOnSubmit(mc);
					}

					// Save changes in the database
					db.SubmitChanges();
				}
				// Nothing was thrown, so go ahead and return true
				ret = true;
			}
			catch
			{
				// Couldn't find the user or was not able to delete
				ret = false;
			}

			return ret;
		}

		/*************************************************************************
		 * User authentication methods
		 *************************************************************************/

		/// <summary>
		/// Authenticates a user with the given username and password
		/// </summary>
		/// <param name="password">The login username</param>
		/// <param name="username">Login password</param>
		/// <returns>True if successful. Defaults to false.</returns>
		public override bool ValidateUser(string username, string password)
		{
			// Return status defaults to false.
			bool ret = false;

			try
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					Member m = (from members in db.Members
								where members.Username == username
								select members).Single();

					// We found a user by the username
					if (m != null)
					{
						// A user cannot login if not approved or locked out
						if ((!m.IsApproved) || m.IsLockedOut)
						{
							ret = false;
						}
						else
						{
							// Trigger period
							DateTime dt = DateTime.Now;

							// Check the given password and the one stored (and salt, if it exists)
							if (CheckPassword(password, m.Password, m.PasswordSalt))
							{
								m.LastLoginDate = dt;
								m.LastActivityDate = dt;

								// Reset past failures
								ResetAuthenticationFailures(ref m, dt);

								ret = true;
							}
							else
							{
								// The login failed... Increment the login attempt count
								m.FailedPasswordAttemptCount = (int)m.FailedPasswordAttemptCount + 1;

								if (m.FailedPasswordAttemptCount >= MaxInvalidPasswordAttempts)
									m.IsLockedOut = true;

								m.FailedPasswordAttemptWindowStart = dt;

							}
						}

						// Save changes
						db.SubmitChanges();
					}
				}
			}
			catch
			{
				// Nothing was thrown, so go ahead and return true
				ret = false;
			}

			return ret;
		}

		/// <summary>
		/// Gets the current password of a user (provided it isn't hashed)
		/// </summary>
		/// <param name="username">User the password is being retrieved for</param>
		/// <param name="answer">Password retrieval answer</param>
		/// <returns>User's passsword</returns>
		public override string GetPassword(string username, string answer)
		{
			// Default password is empty
			string password = String.Empty;

			if (PasswordFormat == MembershipPasswordFormat.Hashed)
			{
				throw new ProviderException("Hashed passwords cannot be retrieved. They must be reset.");
			}
			else
			{
				try
				{
					using (OsirisDataContext db = new OsirisDataContext())
					{
						Member m = (from members in db.Members
									where members.Username == username
									select members).Single();

						password = UnEncodePassword(m.Password);
					}
				}
				catch { }
			}
			return password;
		}

		/// <summary>
		/// Resets the passwords with a generated value
		/// </summary>
		/// <param name="username">User the password is being reset for</param>
		/// <param name="answer">Password retrieval answer</param>
		/// <returns>Newly generated password</returns>
		public override string ResetPassword(string username, string answer)
		{
			// Default password is empty
			string pass = String.Empty;

			try
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					Member m = (from members in db.Members
								where members.Username == username
								select members).Single();

					// We found a user by that name
					if (m != null)
					{
						// Check if the returned password answer matches
						if (EncodePassword(answer) == m.PasswordAnswer)
						{
							// Create a new password with the minimum number of characters
							pass = GeneratePassword(MinRequiredPasswordLength);

							// If the password format is hashed, there must be a salt added
							string salt = "";
							if (PasswordFormat == MembershipPasswordFormat.Hashed)
							{
								salt = GenerateSalt();
								pass = pass + salt;
							}

							m.Password = EncodePassword(pass);
							m.PasswordSalt = salt;

							// Reset everyting
							ResetAuthenticationFailures(ref m, DateTime.Now);

							db.SubmitChanges();
						}
					}
				}
			}
			catch
			{
			}
			return pass;
		}

		/// <summary>
		/// Change the current password for a new one. Note: Both are required.
		/// </summary>
		/// <param name="username">Username the password is being changed for</param>
		/// <param name="oldPassword">Old password to verify owner</param>
		/// <param name="newPassword">New password</param>
		/// <returns>True if successful. Defaults to false.</returns>
		public override bool ChangePassword(string username, string oldPassword, string newPassword)
		{
			if (!ValidateUser(username, oldPassword))
				return false;

			ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, false);

			OnValidatingPassword(args);

			if (args.Cancel)
				if (args.FailureInformation != null)
					throw args.FailureInformation;
				else
					throw new MembershipPasswordException("Password change has been cancelled due to a validation failure.");

			bool ret = false;
			try
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					Member m = (from members in db.Members
								where members.Username == username
								select members).Single();

					string salt = "";
					if (PasswordFormat == MembershipPasswordFormat.Hashed)
					{
						salt = GenerateSalt();
						newPassword = newPassword + salt;
					}

					m.Password = EncodePassword(newPassword);
					m.PasswordSalt = salt;

					// Reset everything
					ResetAuthenticationFailures(ref m, DateTime.Now);

					db.SubmitChanges();
				}
				ret = true;
			}
			catch
			{
				ret = false;
			}

			return ret;
		}

		/// <summary>
		/// Change the password retreival/reset question and answer pair
		/// </summary>
		/// <param name="username">Username the question and answer are being changed for</param>
		/// <param name="password">Current password</param>
		/// <param name="newPasswordQuestion">New password question</param>
		/// <param name="newPasswordAnswer">New password answer (will also be encrypted)</param>
		/// <returns>True if successful. Defaults to false.</returns>
		public override bool ChangePasswordQuestionAndAnswer(string username, string password,
			string newPasswordQuestion, string newPasswordAnswer)
		{
			if (!ValidateUser(username, password))
				return false;

			bool ret = false;
			try
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					Member m = (from members in db.Members
								where members.Username == username
								select members).Single();

					m.PasswordQuestion = newPasswordQuestion;
					m.PasswordAnswer = EncodePassword(newPasswordAnswer);

					db.SubmitChanges();
				}
				ret = true;
			}
			catch
			{
				ret = false;
			}
			return ret;
		}


		/*************************************************************************
		 * User information retreival methods
		 *************************************************************************/

		/// <summary>
		/// Gets the username by a given matching email address
		/// </summary>
		public override string GetUserNameByEmail(string email)
		{
			string username = String.Empty;

			try
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					username = (from members in db.Members
								where members.Email == email
								select members.Username).Single();
				}
			}
			catch
			{
			}
			return username;
		}

		/// <summary>
		/// Gets a MembershipUser object with a given key
		/// </summary>
		public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
		{
			MembershipUser u = null;
			try
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					Member m = (from members in db.Members
								where members.MemberId == Convert.ToInt32(providerUserKey)
								select members).Single();

					if (m != null)
						u = GetUserFromMember(m);
				}
			}
			catch
			{ }

			return u;
		}

		/// <summary>
		/// Gets a MembershipUser object with a given username
		/// </summary>
		public override MembershipUser GetUser(string username, bool userIsOnline)
		{
			MembershipUser u = null;

			try
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					Member m = (from members in db.Members
								where members.Username == username
								select members).Single();

					if (m != null)
						u = GetUserFromMember(m);
				}
			}
			catch
			{ }

			return u;
		}

		/// <summary>
		/// Gets all the users in the database
		/// </summary>
		/// <param name="pageIndex">Current page index</param>
		/// <param name="pageSize">Number of results per page</param>
		/// <param name="totalRecords">Total number of users returned</param>
		/// <returns>MembershpUserCollection object with a list of users on the page</returns>
		public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize,
			out int totalRecords)
		{
			MembershipUserCollection users = new MembershipUserCollection();
			totalRecords = 0;

			try
			{
				int start = pageSize * pageIndex;
				int end = start + pageSize;

				using (OsirisDataContext db = new OsirisDataContext())
				{
					totalRecords = (from members in db.Members
									select members).Count();

					List<Member> mlist = (from members in db.Members
										  select members).Skip(start).Take(pageSize).ToList();

					foreach (Member m in mlist)
						users.Add(GetUserFromMember(m));
				}
			}
			catch { }

			return users;
		}

		/// <summary>
		/// Gets the total number of users that are currently online.
		/// </summary>
		/// <returns>Returns user count (within UserIsOnlineTimeWindow minutes)</returns>
		public override int GetNumberOfUsersOnline()
		{
			int c = 0;
			try
			{
				using (OsirisDataContext db = new OsirisDataContext())
				{
					c = (from members in db.Members
						 where members.LastActivityDate.Add(UserIsOnlineTimeWindow) >= DateTime.Now
						 select members).Count();
				}
			}
			catch { }

			return c;
		}

		/// <summary>
		/// Finds a list of users with a matching email address
		/// </summary>
		/// <param name="emailToMatch">Given email to search</param>
		/// <param name="pageIndex">Current page index</param>
		/// <param name="pageSize">Number of results per page</param>
		/// <param name="totalRecords">Total number of users returned</param>
		/// <returns>MembershpUserCollection object with a list of users on the page</returns>
		public override MembershipUserCollection FindUsersByEmail(string emailToMatch,
			int pageIndex, int pageSize, out int totalRecords)
		{
			MembershipUserCollection users = new MembershipUserCollection();
			totalRecords = 0;

			try
			{
				int start = pageSize * pageIndex;
				int end = start + pageSize;

				using (OsirisDataContext db = new OsirisDataContext())
				{
					totalRecords = (from members in db.Members
									where members.Email.Contains(emailToMatch)
									select members).Count();

					List<Member> mlist = (from members in db.Members
										  where members.Email.Contains(emailToMatch)
										  select members).Skip(start).Take(pageSize).ToList();

					foreach (Member m in mlist)
						users.Add(GetUserFromMember(m));
				}
			}
			catch { }

			return users;
		}

		/// <summary>
		/// Gets a list of users with a matching username
		/// </summary>
		/// <param name="usernameToMatch">Username to search for</param>
		/// <param name="pageIndex">Current page index</param>
		/// <param name="pageSize">Number of results per page</param>
		/// <param name="totalRecords">Total number of users returned</param>
		/// <returns>MembershpUserCollection object with a list of users on the page</returns>
		public override MembershipUserCollection FindUsersByName(string usernameToMatch,
			int pageIndex, int pageSize, out int totalRecords)
		{
			MembershipUserCollection users = new MembershipUserCollection();
			totalRecords = 0;

			try
			{
				int start = pageSize * pageIndex;
				int end = start + pageSize;

				using (OsirisDataContext db = new OsirisDataContext())
				{
					totalRecords = (from members in db.Members
									select members).Count();

					List<Member> mlist = (from members in db.Members
										  where members.Username.Contains(usernameToMatch)
										  select members).Skip(start).Take(pageSize).ToList();

					foreach (Member m in mlist)
						users.Add(GetUserFromMember(m));
				}
			}
			catch { }

			return users;
		}


		/*************************************************************************
		 * Class initialization
		 *************************************************************************/

		public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
		{
			if (config == null)
				throw new ArgumentNullException("config");


			if (name == null || name.Length == 0)
				name = "OsirisMembershipProvider";

			if (String.IsNullOrEmpty(config["description"]))
			{
				config.Remove("description");
				config.Add("description", "Osiris Membership Provider");
			}

			// Initialize base class
			base.Initialize(name, config);

			_applicationName = GetConfigValue(config["applicationName"],
				System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);

			// This is a non-standard helper setting.
			_providerName = GetConfigValue(config["providerName"], name);
			
			
			// Sets the default parameters for all the Membership Provider settings

			_requiresUniqeEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
			_requiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "true"));
			_minRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "5"));
			_minRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"],
				"0"));
			_enablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
			_enablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "false"));
			_passwordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
			_passwordStrengthRegularExpression = GetConfigValue(config["passwordStrengthRegularExpression"], "");
			_maxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"],

				"5"));

			string passFormat = config["passwordFormat"];

			// If no format is specified, the default format will be hashed.
			if (passFormat == null)
				passFormat = "hashed";

			switch (passFormat.ToLower())
			{
				case "hashed":
					_passwordFormat = MembershipPasswordFormat.Hashed;
					break;
				case "encrypted":
					_passwordFormat = MembershipPasswordFormat.Hashed;
					break;
				case "clear":
					_passwordFormat = MembershipPasswordFormat.Clear;
					break;
				default:
					throw new ProviderException("Password format '" + passFormat + "' is not supported. Check your web.config file.");
			}

			Configuration cfg = WebConfigurationManager.OpenWebConfiguration(
				System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);

			machineKey = (MachineKeySection)cfg.GetSection("system.web/machineKey");

			if (machineKey.ValidationKey.Contains("AutoGenerate"))
				if (PasswordFormat != MembershipPasswordFormat.Clear)
					throw new ProviderException("Hashed or Encrypted passwords cannot be used with auto-generated keys.");

			MembershipSection membership = (MembershipSection)cfg.GetSection("system.web/membership");
			_userIsOnlineTimeWindow = membership.UserIsOnlineTimeWindow;
		}


		/*************************************************************************
		 * Private password helper methods
		 *************************************************************************/
		
		/// <summary>
		/// Compares a given password with one stored in the database and an optional salt
		/// </summary>
		private bool CheckPassword(string password, string dbpassword, string dbsalt)
		{
			string pass1 = password;
			string pass2 = dbpassword;
			bool ret = false;

			switch (PasswordFormat)
			{
				case MembershipPasswordFormat.Encrypted:
					pass2 = UnEncodePassword(dbpassword);
					break;
				case MembershipPasswordFormat.Hashed:
					pass1 = EncodePassword(password + dbsalt);
					break;
				default:
					break;
			}

			if (pass1 == pass2)
				ret = true;

			return ret;
		}

		/// <summary>
		/// Encodes a given password using the default MembershipPasswordFormat setting
		/// </summary>
		/// <param name="password">Password (plus salt as per above functions if necessary)</param>
		/// <returns>Clear form, Encrypted or Hashed password.</returns>
		private string EncodePassword(string password)
		{
			string encodedPassword = password;

			switch (PasswordFormat)
			{
				case MembershipPasswordFormat.Clear:
					break;
				case MembershipPasswordFormat.Encrypted:
					encodedPassword =
					  Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
					break;
				case MembershipPasswordFormat.Hashed:
					HMACSHA1 hash = new HMACSHA1();
					hash.Key = HexToByte(machineKey.ValidationKey);
					encodedPassword =
					  Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
					break;
				default:
					throw new ProviderException("Unsupported password format.");
			}

			return encodedPassword;
		}

		/// <summary>
		/// Decodes a given stored password into a cleartype or unencrypted form. Provided it isn't hashed.
		/// </summary>
		/// <param name="encodedPassword">Stored, encrypted password</param>
		/// <returns>Unecncrypted password</returns>
		private string UnEncodePassword(string encodedPassword)
		{
			string password = encodedPassword;

			switch (PasswordFormat)
			{
				case MembershipPasswordFormat.Clear:
					break;
				case MembershipPasswordFormat.Encrypted:
					password =
					  Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
					break;
				case MembershipPasswordFormat.Hashed:
					throw new ProviderException("Cannot decode hashed passwords.");
				default:
					throw new ProviderException("Unsupported password format.");
			}

			return password;
		}

		/// <summary>
		/// Converts a string into a byte array
		/// </summary>
		private byte[] HexToByte(string hexString)
		{
			byte[] returnBytes = new byte[hexString.Length / 2];
			for (int i = 0; i < returnBytes.Length; i++)
				returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
			return returnBytes;
		}

		/// <summary>
		/// Salt generation helper (this is essentially the same as the one in SqlMembershipProviders
		/// </summary>
		private string GenerateSalt()
		{
			byte[] buf = new byte[16];
			(new RNGCryptoServiceProvider()).GetBytes(buf);
			return Convert.ToBase64String(buf);
		}

		/// <summary>
		/// Generates a random password of given length (MinRequiredPasswordLength)
		/// </summary>
		private string GeneratePassword(int passLength)
		{
			string _range = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
			Byte[] _bytes = new Byte[passLength];
			char[] _chars = new char[passLength];

			RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();

			rng.GetBytes(_bytes);

			for (int i = 0; i < passLength; i++)
				_chars[i] = _range[_bytes[i] % _range.Length];

			return new string(_chars);
		}

		/*************************************************************************
		 * Private helper methods
		 *************************************************************************/

		/// <summary>
		/// Used in the initializtion, key in web.config or the default setting if null.
		/// </summary>
		private string GetConfigValue(string configValue, string defaultValue)
		{
			if (String.IsNullOrEmpty(configValue))
				return defaultValue;

			return configValue;
		}

		/// <summary>
		/// Upon a successful login or password reset, this changes all the previous failure markers to defaults.
		/// </summary>
		private static void ResetAuthenticationFailures(ref Member m, DateTime dt)
		{
			m.LastPasswordChangedDate = dt;
			m.FailedPasswordAttemptCount = 0;
			m.FailedPasswordAttemptWindowStart = dt;
			m.FailedPasswordAnswerAttemptCount = 0;
			m.FailedPasswordAnswerAttemptWindowStart = dt;
		}

		/// <summary>
		/// Converts a Member object into a MembershipUser object using its assigned settings
		/// </summary>
		private MembershipUser GetUserFromMember(Member m)
		{
			return new MembershipUser(this.ProviderName,
						m.Username,
						m.MemberId,
						m.Email,
						m.PasswordQuestion,
						m.Comment,
						m.IsApproved,
						m.IsLockedOut,
						m.CreatedDate,
						m.LastLoginDate,
						m.LastLoginDate,
						m.LastPasswordChangedDate,
						m.LastLockoutDate);
		}
	}
}

Enjoy!