mysqlpagesstorageprovider.cs

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

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

namespace ScrewTurn.Wiki.PluginPack {

	/// <summary>
	/// Implements a Pages Storage Provider against MySQL Server.
	/// </summary>
	public class MySqlPagesStorageProvider : MySqlStorageProviderBase, IPagesStorageProvider {

		private ComponentInformation info = new ComponentInformation("MySQL Pages 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 Page";
			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 PagesProviderVersion (
								Version varchar(12) PRIMARY KEY
							);
							INSERT INTO PagesProviderVersion VALUES ('" + CurrentVersion + @"');
							create table Page (
								Name varchar(128) primary key,
								Status char not null default 'N',
								CreationDateTime datetime not null
							);

							create table PageContent (
								Page varchar(128) references Page.Name on update cascade on delete cascade,
								Revision int not null default -1,
								Title varchar(256) not null,
								DateTime datetime not null,
								Username varchar(64) not null,
								Content text not null,
								Comment varchar(128) not null,
								primary key (Page, Revision)
							);

							create table Category (
								Name varchar(128) primary key
							);

							create table CategoryBinding (
								Category varchar(128) references Category.Name on update cascade on delete cascade,
								Page varchar(128) references Page.Name on update cascade on delete cascade,
								primary key (Category, Page)
							);

							create table Message (
								ID int primary key auto_increment,
								Page varchar(128) references Page.Name on update cascade on delete cascade,
								Parent int not null,
								Username varchar(64) not null,
								DateTime datetime not null,
								Subject varchar(128) not null,
								Body text not null
							);

							create table Snippet (
								Name varchar(128) primary key,
								Content text not null
							);

							create table NavigationPath (
								Name varchar(128) not null primary key
							);

							create table NavigationPathBinding (
								NavigationPath varchar(128) not null references NavigationPath.Name on delete cascade,
								Page varchar(128) not null references Page.Name on update cascade on delete cascade,
								Number int not null,
								primary key (NavigationPath, Page, Number)
							);";
			return ExecuteNonQuery(cmd) > 0;
		}

		protected override bool IsDatabaseUpToDate() {
			// Try to retrieve the version number
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT * FROM PagesProviderVersion ";
			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  PagesProviderVersion  ( Version  varchar(12) PRIMARY KEY); INSERT INTO  PagesProviderVersion  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  PagesProviderVersion ";
			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  PagesProviderVersion  SET  Version  = '1.1' WHERE 1 = 1";
			return ExecuteNonQuery(cmd) == 1;
		}

		public ComponentInformation Information {
			get { return info; }
		}

		public bool ReadOnly {
			get { return false; }
		}

		public PageInfo[] AllPages {
			get {
				MySqlCommand cmd = GetCommand();
				cmd.CommandText = "SELECT * FROM  Page ";
				MySqlDataReader reader = ExecuteReader(cmd);
				List<PageInfo> result = new List<PageInfo>();
				while(reader != null && reader.Read()) {
					PageStatus s = PageStatus.Normal;
					switch(reader.GetString(1).ToUpper()) {
						case "N":
							s = PageStatus.Normal;
							break;
						case "L":
							s = PageStatus.Locked;
							break;
						case "P":
							s = PageStatus.Public;
							break;
						default:
							throw new Exception("Invalid Page Status.");
					}
					result.Add(new PageInfo(reader.GetString(0), this, s, reader.GetDateTime(2)));
				}
				Close(cmd);
				return result.ToArray();
			}
		}

		public CategoryInfo[] AllCategories {
			get {
				MySqlCommand cmd = GetCommand();
				cmd.CommandText = "SELECT * FROM  Category ";
				MySqlDataReader reader = ExecuteReader(cmd);
				List<CategoryInfo> result = new List<CategoryInfo>();
				while(reader != null && reader.Read()) {
					CategoryInfo ci = new CategoryInfo(reader.GetString(0), this);
					ci.Pages = GetPagesPerCategory(ci.Name);
					result.Add(ci);
				}
				return result.ToArray();
			}
		}

		private string[] GetPagesPerCategory(string category) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT  Page  FROM  CategoryBinding  WHERE  Category  = ?Category";
			cmd.Parameters.Add(new MySqlParameter("?Category", category));
			MySqlDataReader reader = ExecuteReader(cmd);
			List<string> result = new List<string>();
			while(reader != null && reader.Read()) {
				result.Add(reader.GetString(0));
			}
			return result.ToArray();
		}

		public CategoryInfo AddCategory(string name) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO  Category  VALUES (?Category)";
			cmd.Parameters.Add(new MySqlParameter("?Category", name));
			if(ExecuteNonQuery(cmd) == 1) {
				CategoryInfo ci = new CategoryInfo(name, this);
				ci.Pages = new string[0];
				return ci;
			}
			else return null;
		}

		public CategoryInfo RenameCategory(CategoryInfo category, string newName) {
			// No need to update table CategoryBinding because there is ON UPDADE/DELETE CASCADE
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE  Category  SET  Category  = ?NewCategory WHERE  Category  = ?Category";
			cmd.Parameters.Add(new MySqlParameter("?NewCategory", newName));
			cmd.Parameters.Add(new MySqlParameter("?Category", category.Name));
			if(ExecuteNonQuery(cmd) == 1) {
				CategoryInfo ci = new CategoryInfo(newName, this);
				ci.Pages = category.Pages;
				return ci;
			}
			else return null;
		}

		public bool RemoveCategory(CategoryInfo category) {
			// No need to update table CategoryBinding because there is ON UPDADE/DELETE CASCADE
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM  Category  WHERE  Name  = ?Category";
			cmd.Parameters.Add(new MySqlParameter("?Category", category.Name));
			return ExecuteNonQuery(cmd) == 1;
		}

		public CategoryInfo MergeCategories(CategoryInfo source, CategoryInfo destination) {
			// Delete pages that are common to both the categories in [CategoryBinding]
			// Rename source to destination in [CategoryBinding]
			// Delete destination in [Category]
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM  CategoryBinding  WHERE  Category  = ?Cat1 AND  Page  IN (SELECT  Page  FROM  CategoryBinding  WHERE  Category  = ?Cat2); UPDATE  CategoryBinding  SET  Category  = ?Cat2 WHERE  Category  = ?Cat1; DELETE FROM  Category  WHERE  Name  = ?Cat1";
			cmd.Parameters.Add(new MySqlParameter("?Cat1", source.Name));
			cmd.Parameters.Add(new MySqlParameter("?Cat2", destination.Name));
			if(ExecuteNonQuery(cmd) > 0) {
				List<string> p = new List<string>();
				p.AddRange(source.Pages);
				p.AddRange(destination.Pages);
				CategoryInfo ci = new CategoryInfo(destination.Name, this);
				ci.Pages = p.ToArray();
				return ci;
			}
			else return null;
		}

		public PageContent GetContent(PageInfo page) {
			return GetBackupContent(page, -1);
		}

		public List<int> GetBackups(PageInfo page) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT  Revision  FROM  PageContent  WHERE  Page  = ?Page AND NOT  Revision  = -1 ORDER BY  Revision ";
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			MySqlDataReader reader = ExecuteReader(cmd);
			List<int> result = new List<int>();
			while(reader != null && reader.Read()) {
				result.Add(reader.GetInt32(0));
			}
			Close(cmd);
			return result;
		}

		public PageContent GetBackupContent(PageInfo page, int revision) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT * FROM  PageContent  WHERE  Page  = ?Page AND  Revision  = ?Revision";
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			cmd.Parameters.Add(new MySqlParameter("?Revision", revision));
			MySqlDataReader reader = ExecuteReader(cmd);
			PageContent content = null;
			if(reader != null && reader.Read()) {
				content = new PageContent(page, reader.GetString(2), reader.GetString(4), reader.GetDateTime(3), reader.GetString(6), reader.GetString(5));
			}
			Close(cmd);
			return content;
		}

		public bool SetBackupContent(PageContent content, int revision) {
			MySqlCommand cmd;
			if(GetBackupContent(content.PageInfo, revision) == null) {
				cmd = GetCommand();
				// Insert a fake revision and update it
				cmd.CommandText = "INSERT INTO  PageContent  VALUES (?Page, ?Revision, '-', ?DateTime, '-', '-', '-')";
				cmd.Parameters.Add(new MySqlParameter("?Page", content.PageInfo.Name));
				cmd.Parameters.Add(new MySqlParameter("?Revision", revision));
				cmd.Parameters.Add(new MySqlParameter("?DateTime", DateTime.Now));
				if(ExecuteNonQuery(cmd) != 1) return false;
			}
			cmd = GetCommand();
			cmd.CommandText = "UPDATE  PageContent  SET  Title  = ?Title,  DateTime  = ?DateTime,  Username  = ?Username,  Comment  = ?Comment,  Content  = ?Content WHERE  Page  = ?Page AND  Revision  = ?Revision";
			cmd.Parameters.Add(new MySqlParameter("?Page", content.PageInfo.Name));
			cmd.Parameters.Add(new MySqlParameter("?Revision", revision));
			cmd.Parameters.Add(new MySqlParameter("?Title", content.Title));
			cmd.Parameters.Add(new MySqlParameter("?DateTime", content.LastModified));
			cmd.Parameters.Add(new MySqlParameter("?Username", content.User));
			cmd.Parameters.Add(new MySqlParameter("?Comment", content.Comment));
			cmd.Parameters.Add(new MySqlParameter("?Content", content.Content));
			return ExecuteNonQuery(cmd) == 1;
		}

		public bool Backup(PageInfo page) {
			List<int> b = GetBackups(page);
			int revision = b.Count > 0 ? b[b.Count - 1] + 1 : 0;
			PageContent content = GetContent(page);
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO  PageContent  VALUES (?Page, ?Revision, ?Title, ?DateTime, ?Username, ?Content, ?Comment)";
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));