mysqlusersstorageprovider.cs

YetAnotherForum.Net+ScrewTurnWiki中文完美汉化增强版

using System;
using System.Collections.Generic;
using System.Text;
using ScrewTurn.Wiki.PluginFramework;
using System.Security.Cryptography;
using MySql.Data.MySqlClient;

namespace ScrewTurn.Wiki.PluginPack {

	/// <summary>
	/// Implements a Users Storage Provider against MySQL.
	/// </summary>
	public class MySqlUsersStorageProvider : MySqlStorageProviderBase, IUsersStorageProvider {

		private ComponentInformation info = new ComponentInformation("MySQL Users Storage Provider " + CurrentVersion, "ScrewTurn Software", "http://www.screwturn.eu");
		private const string CurrentVersion = "1.1";

		protected override bool ValidateConfig() {
			// Config must be a valid Connection String
			// Open a connection and perform a test query
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT count(*) FROM User";
			object c = null;
			try {
				c = ExecuteScalar(cmd);
			}
			catch { }
			if(c == null) {
				return GenerateDatabase();
			}
			else return true;
		}

		private bool GenerateDatabase() {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = @"CREATE TABLE UsersProviderVersion (
								Version varchar(12) PRIMARY KEY
							);
							INSERT INTO UsersProviderVersion VALUES ('" + CurrentVersion + @"');
							CREATE TABLE User (
								Username varchar(128) PRIMARY KEY,
								PasswordHash varchar(128) NOT NULL,
								Email varchar(128) NOT NULL,
								DateTime datetime NOT NULL,
								Active bit NOT NULL DEFAULT 0,
								Admin bit NOT NULL DEFAULT 0
							);";
			return ExecuteNonQuery(cmd) > 0;
		}

		protected override bool IsDatabaseUpToDate() {
			// Try to retrieve the version number
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT * FROM UsersProviderVersion";
			string ver = null;
			try {
				ver = (string)ExecuteScalar(cmd);
			}
			catch { }
			if(ver == null) {
				// Database has no Version, create table Version (v1.0) and Update Database
				cmd = GetCommand();
				cmd.CommandText = "CREATE TABLE UsersProviderVersion (Version varchar(12) PRIMARY KEY); INSERT INTO UsersProviderVersion VALUES ('1.0');";
				ExecuteNonQuery(cmd);
				return false;
			}
			else if(ver.Equals(CurrentVersion)) return true;
			else return false;
		}

		protected override bool UpdateDatabase() {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT * FROM UsersProviderVersion";
			string ver = (string)ExecuteScalar(cmd);
			return UpdateDatabaseInternal(ver, CurrentVersion);
		}

		private bool UpdateDatabaseInternal(string fromVersion, string toVersion) {
			// No updates needed for now
			switch(fromVersion) {
				case "1.0":
					switch(toVersion) {
						case "1.1":
							return UpdateFrom10To11();
					}
					break;
			}
			return false;
		}

		private bool UpdateFrom10To11() {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE UsersProviderVersion SET Version = '1.1' WHERE 1 = 1";
			return ExecuteNonQuery(cmd) == 1;
		}

		public ComponentInformation Information {
			get { return info; }
		}

		public bool ReadOnly {
			get { return false; }
		}

		public bool TestAccount(UserInfo user, string password) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT * FROM User WHERE Username = ?Username AND PasswordHash = ?PasswordHash";
			cmd.Parameters.Add(new MySqlParameter("?Username", user.Username));
			cmd.Parameters.Add(new MySqlParameter("?PasswordHash", ComputeHash(password)));
			return ExecuteScalar(cmd) != null;
		}

		public UserInfo[] AllUsers {
			get {
				MySqlCommand cmd = GetCommand();
				cmd.CommandText = "SELECT * FROM User";
				MySqlDataReader reader = ExecuteReader(cmd);
				List<UserInfo> result = new List<UserInfo>();
				while(reader != null && reader.Read()) {
					result.Add(new UserInfo(reader.GetString(0), reader.GetString(2), reader.GetBoolean(4), reader.GetDateTime(3), reader.GetBoolean(5), this));
				}
				Close(cmd);
				return result.ToArray();
			}
		}

		public UserInfo AddUser(string username, string password, string email, bool active, DateTime dateTime, bool admin) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO User VALUES (?Username, ?PasswordHash, ?Email, ?DateTime, ?Active, ?Admin)";
			cmd.Parameters.Add(new MySqlParameter("?Username", username));
			cmd.Parameters.Add(new MySqlParameter("?PasswordHash", ComputeHash(password)));
			cmd.Parameters.Add(new MySqlParameter("?Email", email));
			cmd.Parameters.Add(new MySqlParameter("?DateTime", dateTime));
			cmd.Parameters.Add(new MySqlParameter("?Active", active));
			cmd.Parameters.Add(new MySqlParameter("?Admin", admin));
			if(ExecuteNonQuery(cmd) == 1) {
				return new UserInfo(username, email, active, dateTime, admin, this);
			}
			else return null;
		}

		public UserInfo SetUserActivationStatus(UserInfo user, bool active) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE User SET Active = ?Active WHERE Username = ?Username";
			cmd.Parameters.Add(new MySqlParameter("?Active", active));
			cmd.Parameters.Add(new MySqlParameter("?Username", user.Username));
			if(ExecuteNonQuery(cmd) == 1) {
				return new UserInfo(user.Username, user.Email, active, user.DateTime, user.Admin, this);
			}
			else return null;
		}

		public UserInfo SetUserAdministrationStatus(UserInfo user, bool admin) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE User SET Admin = ?Admin WHERE Username = ?Username";
			cmd.Parameters.Add(new MySqlParameter("?Admin", admin));
			cmd.Parameters.Add(new MySqlParameter("?Username", user.Username));
			if(ExecuteNonQuery(cmd) == 1) {
				return new UserInfo(user.Username, user.Email, user.Active, user.DateTime, admin, this);
			}
			else return null;
		}

		public bool RemoveUser(UserInfo user) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM User WHERE Username = ?Username";
			cmd.Parameters.Add(new MySqlParameter("?Username", user.Username));
			return ExecuteNonQuery(cmd) == 1;
		}

		public UserInfo ChangeEmail(UserInfo user, string newEmail) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE User SET Email = ?Email WHERE Username = ?Username";
			cmd.Parameters.Add(new MySqlParameter("?Email", newEmail));
			cmd.Parameters.Add(new MySqlParameter("?Username", user.Username));
			if(ExecuteNonQuery(cmd) == 1) {
				return new UserInfo(user.Username, newEmail, user.Active, user.DateTime, user.Admin, this);
			}
			else return null;
		}

		public UserInfo ChangePassword(UserInfo user, string newPassword) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE User SET PasswordHash = ?PasswordHash WHERE Username = ?Username";
			cmd.Parameters.Add(new MySqlParameter("?PasswordHash", ComputeHash(newPassword)));
			cmd.Parameters.Add(new MySqlParameter("?Username", user.Username));
			if(ExecuteNonQuery(cmd) == 1) {
				return new UserInfo(user.Username, user.Email, user.Active, user.DateTime, user.Admin, this);
			}
			else return null;
		}

		/// <summary>
		/// Computes the Hash code of a string.
		/// </summary>
		/// <param name="input">The string.</param>
		/// <returns>The Hash code.</returns>
		private byte[] ComputeHashBytes(string input) {
			SHA1 sha1 = SHA1CryptoServiceProvider.Create();
			return sha1.ComputeHash(Encoding.ASCII.GetBytes(input));
		}

		/// <summary>
		/// Computes the Hash code of a string and converts it into a Hex string.
		/// </summary>
		/// <param name="input">The string.</param>
		/// <returns>The Hash code, converted into a Hex string.</returns>
		private string ComputeHash(string input) {
			byte[] bytes = ComputeHashBytes(input);
			string result = "";
			for(int i = 0; i < bytes.Length; i++) {
				result += string.Format("{0:X2}", bytes[i]);
			}
			return result;
		}

	}

}