sqlserverpagesstorageprovider.cs

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

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using ScrewTurn.Wiki.PluginFramework;

namespace ScrewTurn.Wiki.PluginPack {

	/// <summary>
	/// Implements a Pages Storage Provider against SQL Server.
	/// </summary>
	public class SqlServerPagesStorageProvider : SqlServerStorageProviderBase, IPagesStorageProvider {

		private ComponentInformation info = new ComponentInformation("MSSQL 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
			SqlCommand 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() {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = @"CREATE TABLE [PagesProviderVersion] (
								[Version] varchar(12) PRIMARY KEY
							);
							INSERT INTO [PagesProviderVersion] VALUES ('" + CurrentVersion + @"');
							create table [Page] (
								[Name] nvarchar(128) primary key,
								[Status] char not null default ('N'), -- (P)ublic, N(ormal), (L)ocked
								[CreationDateTime] datetime not null
							);

							create table [PageContent] (
								[Page] nvarchar(128) references [Page]([Name]) on update cascade on delete cascade,
								[Revision] int not null default ((-1)), -- -1 for Current Revision
								[Title] nvarchar(256) not null,
								[DateTime] datetime not null,
								[Username] nvarchar(64) not null,
								[Content] ntext not null,
								[Comment] nvarchar(128) not null,
								primary key ([Page], [Revision])
							);

							create table [Category] (
								[Name] nvarchar(128) primary key
							);

							create table [CategoryBinding] (
								[Category] nvarchar(128) references [Category]([Name]) on update cascade on delete cascade,
								[Page] nvarchar(128) references [Page]([Name]) on update cascade on delete cascade,
								primary key ([Category], [Page])
							);

							create table [Message] (
								[ID] int primary key identity,
								[Page] nvarchar(128) references [Page]([Name]) on update cascade on delete cascade,
								[Parent] int not null, -- -1 for no parent
								[Username] nvarchar(64) not null,
								[DateTime] datetime not null,
								[Subject] nvarchar(128) not null,
								[Body] ntext not null
							);

							create table [Snippet] (
								[Name] nvarchar(128) primary key,
								[Content] ntext not null
							);

							create table [NavigationPath] (
								[Name] nvarchar(128) not null primary key
							);

							create table [NavigationPathBinding] (
								[NavigationPath] nvarchar(128) not null references [NavigationPath]([Name]) on delete cascade,
								[Page] nvarchar(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
			SqlCommand 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() {
			SqlCommand 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() {
			SqlCommand 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 {
				SqlCommand cmd = GetCommand();
				cmd.CommandText = "SELECT * FROM [Page]";
				SqlDataReader 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 {
				SqlCommand cmd = GetCommand();
				cmd.CommandText = "SELECT * FROM [Category]";
				SqlDataReader 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) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT [Page] FROM [CategoryBinding] WHERE [Category] = @Category";
			cmd.Parameters.Add(new SqlParameter("Category", category));
			SqlDataReader 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) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO [Category] VALUES (@Category)";
			cmd.Parameters.Add(new SqlParameter("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
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE [Category] SET [Category] = @NewCategory WHERE [Category] = @Category";
			cmd.Parameters.Add(new SqlParameter("NewCategory", newName));
			cmd.Parameters.Add(new SqlParameter("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
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM [Category] WHERE [Name] = @Category";
			cmd.Parameters.Add(new SqlParameter("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]
			SqlCommand 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 SqlParameter("Cat1", source.Name));
			cmd.Parameters.Add(new SqlParameter("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) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT [Revision] FROM [PageContent] WHERE [Page] = @Page AND NOT [Revision] = -1 ORDER BY [Revision]";
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			SqlDataReader 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) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT * FROM [PageContent] WHERE [Page] = @Page AND [Revision] = @Revision";
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			cmd.Parameters.Add(new SqlParameter("Revision", revision));
			SqlDataReader 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) {
			SqlCommand 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 SqlParameter("Page", content.PageInfo.Name));
				cmd.Parameters.Add(new SqlParameter("Revision", revision));
				cmd.Parameters.Add(new SqlParameter("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 SqlParameter("Page", content.PageInfo.Name));
			cmd.Parameters.Add(new SqlParameter("Revision", revision));
			cmd.Parameters.Add(new SqlParameter("Title", content.Title));
			cmd.Parameters.Add(new SqlParameter("DateTime", content.LastModified));
			cmd.Parameters.Add(new SqlParameter("Username", content.User));
			cmd.Parameters.Add(new SqlParameter("Comment", content.Comment));
			cmd.Parameters.Add(new SqlParameter("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);
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO [PageContent] VALUES (@Page, @Revision, @Title, @DateTime, @Username, @Content, @Comment)";