~grubng-dev/grubng/tools-urlsdb

//  
//  Copyright (C) 2009,2010,2011 Bartek thindil Jasicki
// 
//  This file is part of Grubng
// 
//  Grubng is free software: you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation, either version 3 of the License, or
//  (at your option) any later version.
// 
//  This program is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//  GNU General Public License for more details.
// 
//  You should have received a copy of the GNU General Public License
//  along with this program.  If not, see <http://www.gnu.org/licenses/>.
// 

using System;
using System.Data;
using System.Text;
using MySql.Data.MySqlClient;

namespace urlsdb
{
	
	/// <summary>
	/// Provide functions for create and manipulate database
	/// </summary>
	internal sealed class Database
	{	
		/// <summary>
		/// Connection string to database.
		/// </summary>
		string connectionString;
		
		/// <summary>
		/// Standard class constructor. Read connection parameters from urlsdb.conf
		/// </summary>
		public Database()
		{
			this.connectionString = "Server=" + Config.ReadConfig("/configuration/mysqlhost") + ";" +
				"Database=" + Config.ReadConfig("/configuration/mysqldb") + ";" +
					"User ID=" + Config.ReadConfig("/configuration/mysqluser") + ";" +
					"Password=" + Config.ReadConfig("/configuration/mysqlpassword") + ";" +
					"Pooling=false";
		}
		
		/// <summary>
		/// Create database.
		/// </summary>
		public static void CreateDB()
		{
			string connectionString = "Server=" + Config.ReadConfig("/configuration/mysqlhost") + ";" +
				"Database=" + Config.ReadConfig("/configuration/mysqldb") + ";" +
					"User ID=" + Config.ReadConfig("/configuration/mysqluser") + ";" +
					"Password=" + Config.ReadConfig("/configuration/mysqlpassword") + ";" +
					"Pooling=false";
			using (IDbConnection dbcon = new MySqlConnection(connectionString))
			{
				dbcon.Open();
				using (IDbCommand dbcmd = dbcon.CreateCommand())
				{
					dbcmd.CommandText = "CREATE TABLE `urlslist` (`url_hash` varchar(50), `url` TEXT, `last_visited` bigint, " +
						"`http_code1` smallint, `http_code2` smallint, `http_code3` smallint, `robot_check` bigint, " +
							"`robot_result1` smallint, `robot_result2` smallint, `robot_result3` smallint," +
							"`user_crawl` varchar(255), `user_robots` varchar(255));" +
							"CREATE UNIQUE INDEX `url_hash` ON urlslist(`url_hash`);" +
							"CREATE INDEX `last_visited` ON urlslist(`last_visited`);";
					int tmp = dbcmd.ExecuteNonQuery();
					tmp ++;
				}
				dbcon.Close();
			}
		}
		
		/// <summary>
		/// Update database
		/// </summary>
		public void UpdateDB()
		{
			using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
			{
				dbcon.Open();
				using (IDbCommand dbcmd = dbcon.CreateCommand())
				{
					dbcmd.CommandText = "ALTER TABLE `urlslist` ADD `robot_result1` smallint, ADD `robot_result2` smallint, " +
						"ADD `robot_result3` smallint;";
					dbcmd.CommandTimeout = 500;
					int tmp = dbcmd.ExecuteNonQuery();
					tmp ++;
				}
				dbcon.Close();
			}
		}
		
		/// <summary>
		/// Bulk insert new URL's to database.
		/// </summary>
		/// <param name="dict">
		/// A <see cref="System.Collections.Generic.Dictionary<System.String, System.String[]>"/>
		/// dictionary with URL's to insert. Keys are URL's, value are array with main SHA1 hash of
		/// url and second SHA1 hash.
		/// </param>
		/// <param name="fast">
		/// A <see cref="System.Boolean"/> if true, make fast insert URL's to database (without checking
		/// for existing entries)
		/// </param>
		/// <returns>
		/// A <see cref="System.Int32"/> amount of URL's inserted to database.
		/// </returns>
		public int InsertURLs(System.Collections.Generic.Dictionary<string, string[]> dict, bool fast)
		{
			int amount = 0;
			bool any = false;
			StringBuilder insertcmd = new StringBuilder();
			insertcmd.Append("INSERT INTO `urlslist` (`url_hash`, `url`, `last_visited`, " +
			                 "`http_code1`, `http_code2`, `http_code3`, `robot_check`, " +
			                 "`robot_result1`, `robot_result2`, `robot_result3`," +
			                 "`user_crawl`, `user_robots`) VALUES");
			if (fast)
			{
				foreach(string url in dict.Keys)
				{
					insertcmd.Append("(\"");
					insertcmd.Append(dict[url][0]);
					insertcmd.Append("\", \"");
					insertcmd.Append(url);
					insertcmd.Append("\", 0, 0, 0, 0, 0, 0, 0, 0, \"\", \"\"),");
				}
				any = true;
			}
			using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
			{
				dbcon.Open();
				using (IDbCommand dbcmd = dbcon.CreateCommand())
				{
					dbcmd.CommandTimeout = 500;
					if (!fast)
					{
						System.Collections.Generic.List<string> commands = new System.Collections.Generic.List<string>();
						System.Collections.Generic.List<string> keys = new System.Collections.Generic.List<string>();
						foreach(string url in dict.Keys)
						{
							commands.Add("SELECT `url_hash` FROM `urlslist` WHERE `url_hash`='" + dict[url][0] +
							             "' OR `url_hash`='" + dict[url][1] + "';");
							keys.Add(url);
						}
						for (int i = 0; i < commands.Count; i++)
						{
							dbcmd.CommandText = commands[i];
							using (IDataReader reader = dbcmd.ExecuteReader())
							{
								if (!reader.Read())
								{	
									insertcmd.Append("(\"");
									insertcmd.Append(dict[keys[i]][0]);
									insertcmd.Append("\", \"");
									insertcmd.Append(keys[i]);
									insertcmd.Append("\", 0, 0, 0, 0, 0, 0, 0, 0, \"\", \"\"),");
									any = true;
								}
								reader.Close();
							}
						}
					}
					if (any)
					{
						dbcmd.CommandText = insertcmd.ToString().TrimEnd(new char[] {','}) + ";";
						amount = dbcmd.ExecuteNonQuery();
					}
				}
				dbcon.Close();
			}
			return amount;
		}
		
		/// <summary>
		/// Insert new URL to database
		/// </summary>
		/// <param name="urlhash">
		/// A <see cref="System.String"/> SHA1 hash of URL to insert to database.
		/// </param>
		/// <param name="url">
		/// A <see cref="System.String"/> URL to insert to database.
		/// </param>
		/// <param name="secondhash">
		/// A <see cref="System.String"/> with second SHA1 hash of URL
		/// </param>
		/// <returns>
		/// A <see cref="System.Int32"/> 1 - for inserted url to database, otherwise 0
		/// </returns>
		public int InstertURL(string urlhash, string url, string secondhash)
		{
			int amount;
			using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
			{
				dbcon.Open();
				using (IDbCommand dbcmd = dbcon.CreateCommand())
				{
					dbcmd.CommandTimeout = 500;
					dbcmd.CommandText = "SELECT count(`url_hash`) FROM `urlslist` WHERE `url_hash`='" + urlhash + 
						"' OR `url_hash`='" + secondhash + "';";
					using (IDataReader reader = dbcmd.ExecuteReader())
					{
						reader.Read();
						amount = Convert.ToInt32(reader.GetValue(0));
						reader.Close();
						if (amount != 0)
						{
							reader.Dispose();
							dbcmd.Dispose();
							return 0;
						}
					}
					dbcmd.CommandText = "INSERT INTO `urlslist` (`url_hash`, `url`, `last_visited`, " +
						"`http_code1`, `http_code2`, `http_code3`, `robot_check`, " +
							"`robot_result1`, `robot_result2`, `robot_result3`," +
							"`user_crawl`, `user_robots`)VALUES (\"" + urlhash + "\", \"" + url + 
							"\", 0, 0, 0, 0, 0, 0, 0, 0, \"\", \"\");";
					try
					{
						amount = dbcmd.ExecuteNonQuery();
					}
					catch (MySqlException)
					{
						amount = 0;
					}
				}
				dbcon.Close();
			}
			return amount;
		}
		
		/// <summary>
		/// Delete URL's from database.
		/// </summary>
		/// <param name="urls">
		/// A <see cref="System.Collections.Generic.List<System.String>"/> URL's selected to delete 
		/// from database.
		/// </param>
		/// <returns>
		/// A <see cref="System.Int32"/> amount of deleted URL's.
		/// </returns>
		public int DeleteURLs(System.Collections.Generic.List<string> urls)
		{
			int amount = 0;
			System.Collections.Generic.List<string> delcmds = new System.Collections.Generic.List<string>();
			using (ParseURLs parseulrs = new ParseURLs())
			{
				foreach (string url in urls)
				{
					delcmds.Add("DELETE FROM `urlslist` WHERE `url_hash`='" + parseulrs.GetHash(url) + "';");
				}
			}
			using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
			{
				dbcon.Open();
				using (IDbCommand dbcmd = dbcon.CreateCommand())
				{
					dbcmd.CommandTimeout = 500;
					foreach (string cmd in delcmds)
					{
						dbcmd.CommandText = cmd;
						try
						{
							amount += dbcmd.ExecuteNonQuery();
						}
						catch (MySqlException)
						{
						}
					}
				}
				dbcon.Close();
			}
			return amount;
		}
		
		/// <summary>
		/// Delete URL from database
		/// </summary>
		/// <param name="urlhash">
		/// A <see cref="System.String"/> SHA1 hash of URL to delete from database.
		/// </param>
		/// <returns>
		/// A <see cref="System.Int32"/> 1 - if URL was deleted, otherwise 0
		/// </returns>
		public int DeleteURL(string urlhash)
		{
			int amount;
			using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
			{
				dbcon.Open();
				using (IDbCommand dbcmd = dbcon.CreateCommand())
				{
					dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `url_hash`='" + urlhash + "';";
					dbcmd.CommandTimeout = 500;
					try
					{
						amount = dbcmd.ExecuteNonQuery();
					}
					catch (MySqlException)
					{
						amount = 0;
					}
				}
				dbcon.Close();
			}
			return amount;
		}
			
		/// <summary>
		/// Select URL's from database. Start from selected position.
		/// </summary>
		/// <param name="offset">
		/// A <see cref="System.Int32"/> position from which start fetch URL's from database. If offset = -1 select only first 250000
		/// URL's from database
		/// </param>
		/// <returns>
		/// A <see cref="System.String"/> list of URL's from database, separated by space
		/// </returns>
		public string SelectURLs(int offset)
		{
			StringBuilder returnvalue = new StringBuilder();
			using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
			{
				dbcon.Open();
				using (IDbCommand dbcmd = dbcon.CreateCommand())
				{
					dbcmd.CommandTimeout = 5000;
					dbcmd.CommandText = "SET session sort_buffer_size=100000000;";
					dbcmd.ExecuteNonQuery();
					dbcmd.CommandText = "SET read_rnd_buffer_size=100000000;";
					dbcmd.ExecuteNonQuery();
					if (offset == -1)
					{
						dbcmd.CommandText = "SELECT SQL_NO_CACHE `url`, `last_visited` FROM `urlslist` ORDER BY `last_visited` ASC LIMIT 250000;";
					}
					else
					{
						dbcmd.CommandText = "SELECT SQL_NO_CACHE `url` FROM `urlslist` LIMIT 100000 OFFSET " + offset.ToString() + ";";
					}
					using (IDataReader reader = dbcmd.ExecuteReader())
					{
						while (reader.Read())
						{
							returnvalue.Append(reader.GetString(0));
							if (offset == -1)
							{
								returnvalue.Append(' ');
								returnvalue.Append(reader.GetString(1));
							}
							returnvalue.Append("\n");
						}
						reader.Close();
					}
					dbcmd.CommandText = "SET sort_buffer_size=DEFAULT;";
					dbcmd.ExecuteNonQuery();
					dbcmd.CommandText = "SET read_rnd_buffer_size=DEFAULT;";
					dbcmd.ExecuteNonQuery();
				}
				dbcon.Close();
			}
			return returnvalue.ToString().TrimEnd(new char[] {' ', '\n'});
		}
		
		/// <summary>
		/// Function delete URL's from database with selected HTTP status codes
		/// </summary>
		/// <param name="status">
		/// A <see cref="System.String"/> HTTP status code
		/// </param>
		/// <param name="solrenabled">
		/// A <see cref="System.Boolean"/> if true, delete URL's from Solr too, otherwise false
		/// </param>
		/// <returns>
		/// A <see cref="System.Int32"/> amount of URL's deleted from database
		/// </returns>
		public int CleanURLs(string status, bool solrenabled)
		{
			int amount = 0;
			using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
			{
				dbcon.Open();
				using (IDbCommand dbcmd = dbcon.CreateCommand())
				{
					dbcmd.CommandTimeout = 10000;
					//Delete this same URL's from Solr too
					if (solrenabled)
					{
						dbcmd.CommandText = "SELECT SQL_NO_CACHE `url` FROM `urlslist` WHERE `http_code1`=" + status + " AND `http_code2`=" + 
							status + " AND `http_code3`=" + status + ";";
						using (IDataReader reader = dbcmd.ExecuteReader())
						{
							StringBuilder solrcommand = new StringBuilder();
							solrcommand.Append("<delete>");
							string scommand = String.Empty;
							while (reader.Read())
							{
								solrcommand.Append("<id>http://");
								solrcommand.Append(System.Security.SecurityElement.Escape(reader.GetString(0)));
								solrcommand.Append("</id>");
								amount ++;
								if (amount == 2000)
								{
									solrcommand.Append("</delete>");
									scommand = System.Text.RegularExpressions.Regex.Replace(solrcommand.ToString(), @"[\p{IsC}]", String.Empty);
									MainClass.SendCommand(scommand, "Solr");
									MainClass.SendCommand("<commit/>", "Solr");
									solrcommand.Remove(0, solrcommand.Length);
									solrcommand.Append("<delete>");
									amount = 0;
								}
							}
							solrcommand.Append("</delete>");
							scommand = System.Text.RegularExpressions.Regex.Replace(solrcommand.ToString(), @"[\p{IsC}]", String.Empty);
							MainClass.SendCommand(scommand, "Solr");
							MainClass.SendCommand("<commit/>", "Solr");
							reader.Close();
						}
					}
					dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `http_code1`=" + status + " AND `http_code2`=" + status + 
						" AND `http_code3`=" + status + ";";
					try
					{
						amount = dbcmd.ExecuteNonQuery();
					}
					catch (MySqlException)
					{
						amount = 0;
					}
				}
				dbcon.Close();
			}
			return amount;
		}
		
		/// <summary>
		/// Function optimize tables in database
		/// </summary>
		public void OptimizeDB()
		{
			using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
			{
				dbcon.Open();
				using (IDbCommand dbcmd = dbcon.CreateCommand())
				{
					string[] commands = new string[3] {"OPTIMIZE TABLE `weekly`;",
						"OPTIMIZE TABLE `monthly`;",
						"OPTIMIZE TABLE `yearly`;"
					};
					dbcmd.CommandTimeout = 50000;
					for (int i = 0; i < commands.Length; i++)
					{
						try
						{
							dbcmd.CommandText = commands[i];
							dbcmd.ExecuteNonQuery();
						}
						catch (MySqlException e)
						{
							Utils.WriteLog(e.Message);
						}
					}
				}
				dbcon.Close();
			}
		}
	}
}