mysqlpagesstorageprovider.cs

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

			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("?Content", content.Content));
			cmd.Parameters.Add(new MySqlParameter("?Comment", content.Comment));
			return ExecuteNonQuery(cmd) == 1;
		}

		public PageInfo AddPage(string name, DateTime creationDateTime) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO  Page  VALUES (?Name, 'N', ?DateTime); INSERT INTO  PageContent  VALUES (?Name, -1, '-', ?DateTime, '-', '-', '-')";
			cmd.Parameters.Add(new MySqlParameter("?Name", name));
			cmd.Parameters.Add(new MySqlParameter("?DateTime", creationDateTime));
			if(ExecuteNonQuery(cmd) == 2) {
				return new PageInfo(name, this, PageStatus.Normal, creationDateTime);
			}
			else return null;
		}

		public PageInfo RenamePage(PageInfo page, string newName) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE  Page  SET  Name  = ?NewPage WHERE  Name  = ?Page";
			cmd.Parameters.Add(new MySqlParameter("?NewPage", newName));
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			if(ExecuteNonQuery(cmd) == 1) {
				return new PageInfo(newName, this, page.Status, page.CreationDateTime);
			}
			else return null;
		}

		public PageInfo SetStatus(PageInfo page, PageStatus status) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE  Page  SET  Status  = ?Status WHERE  Name  = ?Page";
			string s = "N";
			switch(status) {
				case PageStatus.Normal:
					s = "N";
					break;
				case PageStatus.Locked:
					s = "L";
					break;
				case PageStatus.Public:
					s = "P";
					break;
			}
			cmd.Parameters.Add(new MySqlParameter("?Status", s));
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			if(ExecuteNonQuery(cmd) == 1) {
				return new PageInfo(page.Name, this, status, page.CreationDateTime);
			}
			else return null;
		}

		public bool ModifyPage(PageInfo page, string title, string username, DateTime dateTime, string comment, string content, bool backup) {
			if(backup) {
				if(!Backup(page)) return false;
			}

			PageContent c = new PageContent(page, title, username, dateTime, comment, content);
			return SetBackupContent(c, -1);
		}

		public bool RollbackPage(PageInfo page, int revision) {
			// Delete newer backups, update current revision
			PageContent content = GetBackupContent(page, revision);
			if(content == null) return false;
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE  PageContent  SET  Title  = ?Title,  DateTime  = ?DateTime,  Username  = ?Username,  Content  = ?Content,  Comment  = ?Comment WHERE  Page  = ?Page AND  Revision  = -1; DELETE FROM  PageContent  WHERE  Page  = ?Page AND NOT  Revision  = -1 AND  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("?Content", content.Content));
			cmd.Parameters.Add(new MySqlParameter("?Comment", content.Comment));
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			cmd.Parameters.Add(new MySqlParameter("?Revision", revision));
			return ExecuteNonQuery(cmd) > 1;
		}

		public bool DeleteBackups(PageInfo page, int revision) {
			// Delete older backups, re-number remaining backups
			if(revision == -1) {
				List<int> backups = GetBackups(page);
				if(backups.Count > 0) revision = backups[backups.Count - 1];
			}
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM  PageContent  WHERE  Page  = ?Page AND NOT  Revision  = -1 AND  Revision  <= ?Revision; UPDATE  PageContent  SET  Revision  =  Revision  - ?Revision - 1 WHERE  Page  = ?Page AND NOT  Revision  = -1";
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			cmd.Parameters.Add(new MySqlParameter("?Revision", revision));
			return ExecuteNonQuery(cmd) > 0;
		}

		public bool RemovePage(PageInfo page) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM  Page  WHERE  Name  = ?Page";
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			return ExecuteNonQuery(cmd) == 1;
		}

		public bool Rebind(PageInfo page, string[] categories) {
			// Delete old bindings, add new bindings
			MySqlCommand cmd = GetCommand();
			StringBuilder sb = new StringBuilder();
			sb.Append("DELETE FROM  CategoryBinding  WHERE  Page  = ?Page; ");
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			for(int i = 0; i < categories.Length; i++) {
				sb.Append("INSERT INTO  CategoryBinding  VALUES (?Cat");
				sb.Append(i.ToString());
				sb.Append(", ?Page); ");
				cmd.Parameters.Add(new MySqlParameter("?Cat" + i.ToString(), categories[i]));
			}
			cmd.CommandText = sb.ToString();
			return ExecuteNonQuery(cmd) > 0;
		}

		public Message[] GetMessages(PageInfo page) {
			// This method is implemented in a very raw way
			return GetReplies(page, -1).ToArray();
		}

		private List<Message> GetReplies(PageInfo page, int parent) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT * FROM  Message  WHERE  Page  = ?Page AND  Parent  = ?Parent";
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			cmd.Parameters.Add(new MySqlParameter("?Parent", parent));
			MySqlDataReader reader = ExecuteReader(cmd);
			List<Message> result = new List<Message>();
			while(reader != null && reader.Read()) {
				Message msg = new Message(reader.GetInt32(0), reader.GetString(3), reader.GetString(5), reader.GetDateTime(4), reader.GetString(6));
				// Too many connections with recursion?
				msg.Replies = GetReplies(page, msg.ID);
				result.Add(msg);
			}
			Close(cmd);
			return result;
		}

		public int GetMessageCount(PageInfo page) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT COUNT(*) FROM  Message  WHERE  Page  = ?Page";
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			return int.Parse(ExecuteScalar(cmd).ToString());
		}

		public bool AddMessage(PageInfo page, string username, string subject, DateTime dateTime, string body, int parent) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO  Message (Page,Parent,Username,DateTime,Subject,Body) VALUES (?Page, ?Parent, ?Username, ?DateTime, ?Subject, ?Body)";
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			cmd.Parameters.Add(new MySqlParameter("?Parent", parent));
			cmd.Parameters.Add(new MySqlParameter("?Username", username));
			cmd.Parameters.Add(new MySqlParameter("?DateTime", dateTime));
			cmd.Parameters.Add(new MySqlParameter("?Subject", subject));
			cmd.Parameters.Add(new MySqlParameter("?Body", body));
			return ExecuteNonQuery(cmd) == 1;
		}

		public bool RemoveMessage(PageInfo page, int id, bool removeReplies) {
			MySqlCommand cmd = GetCommand();
			if(removeReplies) {
				// Recursively remove all the replies
				cmd.CommandText = "SELECT  ID  FROM  Message  WHERE  Page  = ?Page AND  Parent  = ?ID";
				cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
				cmd.Parameters.Add(new MySqlParameter("?ID", id));
				MySqlDataReader reader = ExecuteReader(cmd);
				while(reader != null && reader.Read()) {
					RemoveMessage(page, reader.GetInt32(0), true);
				}
				Close(cmd);
				// Delete the message
				cmd = GetCommand();
				cmd.CommandText = "DELETE FROM  Message  WHERE  Page  = ?Page AND  ID  = ?ID";
				cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
				cmd.Parameters.Add(new MySqlParameter("?ID", id));
				return ExecuteNonQuery(cmd) == 1;
			}
			else {
				// Find parent
				cmd.CommandText = "SELECT  Parent  FROM  Message  WHERE  Page  = ?Page AND  ID  = ?ID";
				cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
				cmd.Parameters.Add(new MySqlParameter("?ID", id));
				int pid = (int)ExecuteScalar(cmd); // Can be -1
				// Set new parent, delete message
				cmd = GetCommand();
				cmd.CommandText = "UPDATE  Message  SET  Parent  = ?Parent WHERE  Page  = ?Page AND  Parent  = ?ID; DELETE FROM  Message  WHERE  Page  = ?Page AND  ID  = ?ID";
				cmd.Parameters.Add(new MySqlParameter("?Parent", pid));
				cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
				cmd.Parameters.Add(new MySqlParameter("?ID", id));
				return ExecuteNonQuery(cmd) > 0;
			}
		}

		public bool ModifyMessage(PageInfo page, int id, string username, string subject, DateTime dateTime, string body) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE  Message  SET  Username  = ?Username,  Subject  = ?Subject,  DateTime  = ?DateTime,  Body  = ?Body WHERE  Page  = ?Page AND  ID  = ?ID";
			cmd.Parameters.Add(new MySqlParameter("?Username", username));
			cmd.Parameters.Add(new MySqlParameter("?Subject", subject));
			cmd.Parameters.Add(new MySqlParameter("?DateTime", dateTime));
			cmd.Parameters.Add(new MySqlParameter("?Body", body));
			cmd.Parameters.Add(new MySqlParameter("?Page", page.Name));
			cmd.Parameters.Add(new MySqlParameter("?ID", id));
			return ExecuteNonQuery(cmd) == 1;
		}

		public NavigationPath[] AllNavigationPaths {
			get {
				MySqlCommand cmd = GetCommand();
				cmd.CommandText = "SELECT * FROM  NavigationPath ";
				MySqlDataReader reader = ExecuteReader(cmd);
				List<NavigationPath> result = new List<NavigationPath>();
				while(reader != null && reader.Read()) {
					NavigationPath n = new NavigationPath(reader.GetString(0), this);
					n.Pages.AddRange(GetPages(n.Name));
				}
				Close(cmd);
				return result.ToArray();
			}
		}

		private List<string> GetPages(string path) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT  Page  FROM  NavigationPathBinding  WHERE  NavigationPath  = ?Path ORDER BY  Number ";
			cmd.Parameters.Add(new MySqlParameter("?Path", path));
			MySqlDataReader reader = ExecuteReader(cmd);
			List<string> result = new List<string>();
			while(reader != null && reader.Read()) {
				result.Add(reader.GetString(0));
			}
			Close(cmd);
			return result;
		}

		public NavigationPath AddNavigationPath(string name, PageInfo[] pages) {
			MySqlCommand cmd = GetCommand();
			StringBuilder sb = new StringBuilder();
			sb.Append("INSERT INTO  NavigationPath  VALUES (?Path); ");
			cmd.Parameters.Add(new MySqlParameter("?Path", name));
			string[] pgs = new string[pages.Length];
			cmd.Parameters.Add(new MySqlParameter("?Path2", name));
			for(int i = 0; i < pages.Length; i++) {
				sb.Append("INSERT INTO  NavigationPathBinding  VALUES (?Path2, ?Page");
				sb.Append(i.ToString());
				sb.Append(", ?Number");
				sb.Append(i.ToString());
				sb.Append("); ");
				cmd.Parameters.Add(new MySqlParameter("?Page" + i.ToString(), pages[i].Name));
				cmd.Parameters.Add(new MySqlParameter("?Number" + i.ToString(), i));
				pgs[i] = pages[i].Name;
			}
			cmd.CommandText = sb.ToString();
			if(ExecuteNonQuery(cmd) == pages.Length + 1) {
				NavigationPath p = new NavigationPath(name, this);
				p.Pages.AddRange(pgs);
				return p;
			}
			else return null;
		}

		public NavigationPath ModifyNavigationPath(string name, PageInfo[] pages) {
			// Shortcut
			if(!RemoveNavigationPath(name)) return null;
			return AddNavigationPath(name, pages);
		}

		public bool RemoveNavigationPath(string name) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM  NavigationPath  WHERE  Name  = ?Path";
			cmd.Parameters.Add(new MySqlParameter("?Path", name));
			return ExecuteNonQuery(cmd) == 1;
		}

		public Snippet[] AllSnippets {
			get {
				MySqlCommand cmd = GetCommand();
				cmd.CommandText = "SELECT * FROM  Snippet ";
				MySqlDataReader reader = ExecuteReader(cmd);
				List<Snippet> result = new List<Snippet>();
				while(reader != null && reader.Read()) {
					result.Add(new Snippet(reader.GetString(0), reader.GetString(1), this));
				}
				Close(cmd);
				return result.ToArray();
			}
		}

		public Snippet AddSnippet(string name, string content) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO  Snippet  VALUES (?Name, ?Content)";
			cmd.Parameters.Add(new MySqlParameter("?Name", name));
			cmd.Parameters.Add(new MySqlParameter("?Content", content));
			if(ExecuteNonQuery(cmd) == 1) {
				return new Snippet(name, content, this);
			}
			else return null;
		}

		public Snippet ModifySnippet(string name, string content) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE  Snippet  SET  Content  = ?Content WHERE  Name  = ?Name";
			cmd.Parameters.Add(new MySqlParameter("?Content", content));
			cmd.Parameters.Add(new MySqlParameter("?Name", name));
			if(ExecuteNonQuery(cmd) == 1) {
				return new Snippet(name, content, this);
			}
			else return null;
		}

		public bool RemoveSnippet(string name) {
			MySqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM  Snippet  WHERE  Name  = ?Name";
			cmd.Parameters.Add(new MySqlParameter("?Name", name));
			return ExecuteNonQuery(cmd) == 1;
		}
	}

}