Subtext database administration page

DatabaseMaintenance.aspx

<%@ Page Language="C#" Debug="true" EnableTheming="false" Title="Subtext Admin - Database Maintenance" MasterPageFile="~/aspx/Admin/WebUI/AdminPageTemplate.Master" AutoEventWireup="True" Inherits="Subtext.Web.Admin.Pages.AdminPage" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="Microsoft.ApplicationBlocks.Data" %>
<%@ Import Namespace="Subtext.Framework.Providers" %>

<script runat="server">
  // Timeout has to be longer than the default because these ops can take a while.
  // Add this to the end of your connection string:
  // ";Connection Timeout=120"

	protected void Page_Load(object sender, EventArgs e)
	{
		base.TabSectionId = "Stats";
		this.Messages.Message = null;
	}

	protected void ClearErrorLog_Click(object sender, EventArgs e)
	{
		try
		{
			SqlHelper.ExecuteNonQuery(Subtext.Framework.Configuration.Config.ConnectionString, CommandType.StoredProcedure, "subtext_LogClear");
			// Subtext.Framework.Logging.LoggingProvider.Instance().ClearLog();
			this.Messages.ShowMessage("Cleared the error log.", false);
		}
		catch (SqlException err)
		{
			this.Messages.ShowError(String.Format("Unable to clear the error log: {0}", err.Message), false);
		}
	}
	
	protected void ReindexDatabase_Click(object sender, EventArgs e)
	{
		try
		{
			SqlHelper.ExecuteNonQuery(Subtext.Framework.Configuration.Config.ConnectionString, CommandType.Text, "DBCC DBREINDEX (subtext_URLs)");
			this.Messages.ShowMessage("Reindexed URL table.", false);
		}
		catch (SqlException err)
		{
			this.Messages.ShowError(String.Format("Unable to reindex URL table: {0}", err.Message), false);
		}
		try
		{
			SqlHelper.ExecuteNonQuery(Subtext.Framework.Configuration.Config.ConnectionString, CommandType.Text, "DBCC DBREINDEX (subtext_Referrals)");
			this.Messages.ShowMessage("Reindexed referral table.", false);
		}
		catch (SqlException err)
		{
			this.Messages.ShowError(String.Format("Unable to reindex referral table: {0}", err.Message), false);
		}
	}
	
	protected void ShrinkDatabase_Click(object sender, EventArgs e)
	{
		try
		{
			SqlHelper.ExecuteNonQuery(Subtext.Framework.Configuration.Config.ConnectionString, CommandType.Text, "DBCC SHRINKDATABASE (0)");
			this.Messages.ShowMessage("Shrunk database.", false);
		}
		catch (SqlException err)
		{
			this.Messages.ShowError(String.Format("Unable to shrink database: {0}", err.Message), false);
		}
	}
	
	protected override void OnPreRender(EventArgs e)
	{
		base.OnPreRender(e);
		try
		{
			this.numErrorLogEntries.Text = SqlHelper.ExecuteScalar(Subtext.Framework.Configuration.Config.ConnectionString, CommandType.Text, "SELECT COUNT(*) FROM [dbo].[subtext_Log]").ToString();
		}
		catch (SqlException err)
		{
			this.Messages.ShowError(String.Format("Unable to get count of error log entries: {0}", err.Message), false);
		}
		try
		{
			this.numTotalReferrals.Text = SqlHelper.ExecuteScalar(Subtext.Framework.Configuration.Config.ConnectionString, CommandType.Text, "SELECT COUNT(*) FROM [dbo].[subtext_Referrals]").ToString();
		}
		catch (SqlException err)
		{
			this.Messages.ShowError(String.Format("Unable to get count of referrals: {0}", err.Message), false);
		}
		try
		{
			using (SqlDataReader spaceUsedData = SqlHelper.ExecuteReader(Subtext.Framework.Configuration.Config.ConnectionString, "sp_spaceused"))
			{
				this.databaseStats.DataSource = spaceUsedData;
				this.databaseStats.DataBind();
			}
			using(SqlDataReader tableSpaceUsed = SqlHelper.ExecuteReader(Subtext.Framework.Configuration.Config.ConnectionString, CommandType.StoredProcedure, "sp_MSforeachtable", new SqlParameter("command1", "EXEC sp_spaceused '?'")))
			{
				DataTable table = new DataTable();
				do
				{
					table.Load(tableSpaceUsed);
				} while(tableSpaceUsed.NextResult());
				tableStats.DataSource = table;
				tableStats.DataBind();
			}
		}
		catch (SqlException err)
		{
			this.Messages.ShowError(String.Format("Unable to get database size statistics: {0}", err.Message), false);
		}
	}
</script>

<asp:Content ID="actions" ContentPlaceHolderID="actionsHeading" runat="server">
</asp:Content>

<asp:Content ID="categoryListTitle" ContentPlaceHolderID="categoryListHeading" runat="server">
</asp:Content>

<asp:Content ID="categoriesLinkListing" ContentPlaceHolderID="categoryListLinks" runat="server">
</asp:Content>

<asp:Content ID="maintenanceContent" ContentPlaceHolderID="pageContent" runat="server">
<script type="text/javascript">
	var allSearchEngineUrls = [
			"<%= Request.Url.GetLeftPart(UriPartial.Authority) %>%",
			"http://altavista.%",
			"http://%.altavista.%",
			"http://ask.%",
			"http://%.ask.%",
			"http://bloglines.%",
			"http://%carinsurance.%",
			"http://dogpile.%",
			"http://%.dogpile.%",
			"http://com/%",
			"http://fastbrowsersearch.%",
			"http://%.fastbrowsersearch.%",
			"http://google.%",
			"http://%.google.%",
			"http://bing.%",
			"http://%.bing.%",
			"http://localhost%",
			"http://localsearch.live.com/%",
			"http://%onlinedegree.%",
			"http://%payday%loan%",
			"http://search.%",
			"http://%.search.%",
			"http://yahoo.%",
			"http://%.yahoo.%",
			"http://twitter.%",
			"http://%.twitter.%",
			"http://image.yodao.com%",
			"http://%.%.%.%:%?%",
			"http://searchservice.myspace.com%",
			"http://www.metacrawler.com%",
			"http://www.webcrawler.com%",
			"http://%.chacha.%",
			"http://%.yandex.%",
			"http://yandex.%",
			"http://%.tattoodle.%",
			"http://%.startpagina.%",
			"http://%.seznam.cz%",
			"http://%.swagbucks.%",
			"http://swagbucks.%",
			"%/Search/%",
			"%/Search?%",
			"%/Search.aspx?%",
			"%/Search.php?%",
			"%casino%",
			"%cialis%",
			"%hydrocodone%",
			"%insurance%",
			"%levitra%",
			"%lorazipam%",
			"%phentermine%",
			"%poker%",
			"%refinance%",
			"%sex%",
			"%valium%",
			"%viagra%",
			"%vioxx%",
			"%xanax%",
			"%xxx%"];

$(document).ready(function()
{
	var list = $("#search-engine-url-list");
	for(var i = 0; i < allSearchEngineUrls.length; i++)
	{
		list.append("<li>" + allSearchEngineUrls[i] + "</li>");
	}
});

function startReferralRemoval()
{
	$("#delete-processing").html("<p>Starting referral processing...</p>");
	removeReferrals(0);
}

function removeReferrals(patternIndex)
{
	var postData = {
		action: "delete-referral",
		urlPattern: allSearchEngineUrls[patternIndex]
	};
	$.ajax({
		url: "DatabaseMaintenanceService.aspx",
		data: postData,
		success: function(data, textStatus, jqXHR){
			$("#delete-processing").html("<p>Processed: " + data.urlPattern + " [" + data.remaining + "]</p>");
			if(data.remaining > 0)
			{
				removeReferrals(patternIndex);
				return;
			}
			patternIndex++;
			if(patternIndex <= allSearchEngineUrls.length - 1)
			{
				removeReferrals(patternIndex);
				return;
			}
			$("#delete-processing").html("<p>Done. All spam referrals removed.</p>");
		},
		error: function(jqXHR, textStatus, errorThrown){
			$("#delete-processing").append("<p><strong>An error occurred while processing (" + textStatus + "): " + errorThrown + "</strong></p>");
		},
		type: "POST",
		dataType: "json"
	});
}

function currentReferralsState()
{
	var postData = {
		action: "current-referrals-state"
	};
	$.ajax({
		url: "DatabaseMaintenanceService.aspx",
		data: postData,
		success: function(data, textStatus, jqXHR){
			$("#referral-proc").html("<p>Current entry tracking proc text:</p><pre>" + data.proc + "</pre>");
		},
		error: function(jqXHR, textStatus, errorThrown){
			$("#referral-proc").html("<p><strong>An error occurred while retrieving referral proc: " + errorThrown + "</strong></p>");
		},
		type: "POST",
		dataType: "json"
	});
}

function disableReferrals()
{
	var postData = {
		action: "disable-referrals"
	};
	$.ajax({
		url: "DatabaseMaintenanceService.aspx",
		data: postData,
		success: function(data, textStatus, jqXHR){
			$("#referral-proc").html("<p>Stored procedure updated - referrals disabled. New proc text:</p><pre>" + data.proc + "</pre>");
		},
		error: function(jqXHR, textStatus, errorThrown){
			$("#referral-proc").html("<p><strong>An error occurred while disabling referrals: " + errorThrown + "</strong></p>");
		},
		type: "POST",
		dataType: "json"
	});
}

function enableReferrals()
{
	var postData = {
		action: "enable-referrals"
	};
	$.ajax({
		url: "DatabaseMaintenanceService.aspx",
		data: postData,
		success: function(data, textStatus, jqXHR){
			$("#referral-proc").html("<p>Stored procedure updated - referrals enabled. New proc text:</p><pre>" + data.proc + "</pre>");
		},
		error: function(jqXHR, textStatus, errorThrown){
			$("#referral-proc").html("<p><strong>An error occurred while enabling referrals: " + errorThrown + "</strong></p>");
		},
		type: "POST",
		dataType: "json"
	});
}
</script>

	<st:MessagePanel id="Messages" runat="server"></st:MessagePanel>
	<st:AdvancedPanel id="MaintenancePanel" runat="server" Collapsible="False" HeaderText="Maintenance Items" HeaderCssClass="CollapsibleHeader" DisplayHeader="true">
	<p><strong>Error log entries</strong>: <asp:Literal ID="numErrorLogEntries" runat="server" /> [<asp:LinkButton ID="clearErrorLog" runat="server" Text="Clear Error Log" OnClick="ClearErrorLog_Click" />]</p>
	</st:AdvancedPanel>
	<st:AdvancedPanel id="DatabaseStatsPanel" runat="server" Collapsible="False" HeaderText="Database Statistics" HeaderCssClass="CollapsibleHeader" DisplayHeader="true" LinkStyle="Image" LinkBeforeHeader="True">
	<asp:DataGrid ID="databaseStats" runat="server" AutoGenerateColumns="true" CssClass="Listing highlightTable"></asp:DataGrid>
	<asp:DataGrid ID="tableStats" runat="server" AutoGenerateColumns="true" CssClass="Listing highlightTable"></asp:DataGrid>
	</st:AdvancedPanel>
	<st:AdvancedPanel id="SearchEngineUrlPanel" runat="server" Collapsible="True" HeaderText="Search Engine URLs" HeaderCssClass="CollapsibleHeader" DisplayHeader="true" LinkStyle="Image" LinkBeforeHeader="True">
	<p><strong>Total referrals</strong>: <asp:Literal ID="numTotalReferrals" runat="server" /></p>
	<p>
	<input type="button" value="Remove Search Engine Referrals" onclick="startReferralRemoval();" class="button" />&nbsp;
	<asp:Button ID="reindexReferrals" runat="server" Text="Reindex Referrals" OnClick="ReindexDatabase_Click" />&nbsp;
	<asp:Button ID="shrinkDatabase" runat="server" Text="Shrink Database" OnClick="ShrinkDatabase_Click" />
	</p>
	<div id="delete-processing"></div>
	<hr />
	<p>A URL is considered a "search engine referral" if it matches one of the following:</p>
	<ul id="search-engine-url-list"></ul>
	<p>Referral cleanup logic based on <a href="http://haacked.com/archive/2006/02/06/databasemaintenanceofyourblog.aspx" target="_blank">database maintenance details here</a>.</p>
	</st:AdvancedPanel>
	<st:AdvancedPanel id="DisableReferralsPanel" runat="server" Collapsible="True" HeaderText="Enable/Disable Referrals" HeaderCssClass="CollapsibleHeader" DisplayHeader="true" LinkStyle="Image" LinkBeforeHeader="True">
	<p>These buttons allow you to enable/disable tracking of referrals by modifying the Subtext stored procedure for entries to track (or not).</p>
	<p><strong>These modify your stored procedures.</strong> If you're not comfortable with that, or if you've got things locked down or customized, this may not be for you.</p>
	<p><strong>You have been warned.</strong></p>
	<p>
	<input type="button" value="View Current Referrals Proc" onclick="currentReferralsState();" class="button" />&nbsp;
	<input type="button" value="Disable Referrals" onclick="disableReferrals();" class="button" />&nbsp;
	<input type="button" value="Enable Referrals" onclick="enableReferrals();" class="button" />
	</p>
	<div id="referral-proc"></div>
	</st:AdvancedPanel>
</asp:Content>

DatabaseMaintenanceService.aspx

<%@ Page Language="C#" Debug="true" EnableTheming="false" Title="Subtext Admin - Database Maintenance AJAX Service" AutoEventWireup="True" Inherits="Subtext.Web.Admin.Pages.AdminPage" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="Microsoft.ApplicationBlocks.Data" %>
<%@ Import Namespace="Subtext.Framework.Providers" %>
<script runat="server">
	protected const int RecordLimit = 1000;

	
	private const string EnableReferralsProc = @"ALTER PROC [dbo].[subtext_TrackEntry]
(
	@EntryID int,
	@BlogId int,
	@Url nvarchar(255) = NULL,
	@IsWeb bit
)

AS

if(@Url is not NULL AND @IsWeb = 1)
BEGIN
	EXEC [dbo].[subtext_InsertReferral] @EntryID, @BlogId, @Url
END

EXEC [dbo].[subtext_InsertEntryViewCount] @EntryID, @BlogId, @IsWeb";


	private const string DisableReferralsProc = @"ALTER PROC [dbo].[subtext_TrackEntry]
(
	@EntryID int,
	@BlogId int,
	@Url nvarchar(255) = NULL,
	@IsWeb bit
)

AS

-- if(@Url is not NULL AND @IsWeb = 1)
-- BEGIN
-- 	EXEC [dbo].[subtext_InsertReferral] @EntryID, @BlogId, @Url
-- END

EXEC [dbo].[subtext_InsertEntryViewCount] @EntryID, @BlogId, @IsWeb";



	protected void Page_Load(object sender, EventArgs e)
	{
		string action = Request.Form["action"];
		switch(action)
		{
			case "delete-referral":
				this.DeleteReferral();
				break;
			case "disable-referrals":
				this.DisableReferrals();
				break;
			case "enable-referrals":
				this.EnableReferrals();
				break;
			case "current-referrals-state":
				this.CurrentReferralsState();
				break;
			default:
				throw new NotSupportedException("The action provided is not supported.");
		}
	}

	private void CurrentReferralsState()
	{
		var proc = this.GetStoredProcText("subtext_TrackEntry");
		Response.Write("{ ");
		WriteJsonPair("proc", proc);
		Response.Write(" }");
	}

	private void DisableReferrals()
	{
		using (SqlConnection connection = new SqlConnection(Subtext.Framework.Configuration.Config.ConnectionString))
		{
			connection.Open();
			SqlTransaction transaction = connection.BeginTransaction();
			try
			{
				SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, DisableReferralsProc);
				transaction.Commit();
			}
			catch
			{
				transaction.Rollback();
				throw;
			}
		}
		this.CurrentReferralsState();
	}
	
	private void EnableReferrals()
	{
		using (SqlConnection connection = new SqlConnection(Subtext.Framework.Configuration.Config.ConnectionString))
		{
			connection.Open();
			SqlTransaction transaction = connection.BeginTransaction();
			try
			{
				SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, EnableReferralsProc);
				transaction.Commit();
			}
			catch
			{
				transaction.Rollback();
				throw;
			}
		}
		this.CurrentReferralsState();
	}
	
	private void DeleteReferral()
	{
		string urlPattern = Request.Form["urlPattern"];
		if(String.IsNullOrEmpty(urlPattern))
		{
			throw new ArgumentException("Must supply a URL pattern.");
		}
		
		string fromClause = String.Format("FROM [dbo].[subtext_URLs] WHERE Url LIKE '{0}'", urlPattern);
		string sqlDeleteReferrals = String.Format("DELETE TOP ({0}) [dbo].[subtext_Referrals] WHERE UrlID IN (SELECT UrlID {1})", RecordLimit, fromClause);
		string sqlDeleteUrls = "DELETE FROM [dbo].[subtext_URLs] WHERE UrlID NOT IN (SELECT UrlID FROM [dbo].[subtext_Referrals])";
		
		using (SqlConnection connection = new SqlConnection(Subtext.Framework.Configuration.Config.ConnectionString))
		{
			connection.Open();
			SqlTransaction transaction = connection.BeginTransaction();
			try
			{
				SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlDeleteReferrals);
				SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlDeleteUrls);
				transaction.Commit();
			}
			catch
			{
				transaction.Rollback();
				throw;
			}
		}

		var numRemainingForPattern = GetRemainingReferrals(fromClause);
		
		Response.Write("{ ");
		WriteJsonPair("urlPattern", urlPattern);
		Response.Write(",");
		WriteJsonPair("remaining", numRemainingForPattern);
		Response.Write(" }");
	}
	
	private int GetRemainingReferrals(string fromClause)
	{
		string sqlRemainingReferrals = String.Format("SELECT COUNT(*) FROM [dbo].[subtext_Referrals] WHERE UrlID IN (SELECT UrlID {0})", fromClause);
		int numRemaining = Convert.ToInt32(SqlHelper.ExecuteScalar(Subtext.Framework.Configuration.Config.ConnectionString, CommandType.Text, sqlRemainingReferrals));
		return numRemaining;
	}
	
	private string GetStoredProcText(string procName)
	{
		string sqlGetProc = String.Format("SELECT text FROM syscomments WHERE id = (SELECT id FROM sysobjects WHERE name = 'subtext_TrackEntry') ORDER BY colid", procName);
		string text = "";
		using(var dr = SqlHelper.ExecuteReader(Subtext.Framework.Configuration.Config.ConnectionString, CommandType.Text, sqlGetProc))
		{
			while (dr.Read())
			{
				text += dr.GetString(0);
			}
		}
		return text;
	}
	
	private void WriteJsonPair(string key, object value)
	{
		Response.Write(String.Format("\"{0}\":\"{1}\"", key, value.ToString().Replace("\n", "\\n").Replace("\r", "\\r").Replace("\t", "\\t")));
	}
</script>