~grubng-dev/grubng/tools-urlsdb

//  
//  Copyright (C) 2009 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 MySql.Data.MySqlClient;

namespace urlsdb
{
	
	/// <summary>
	/// Provide functions for create and manipulate database
	/// </summary>
	internal sealed class Database
	{	
		/// <summary>
		/// IDbConnection class object.
		/// </summary>
		IDbConnection dbcon;
		/// <summary>
		/// Connection string to database.
		/// </summary>
		string connectionString;
		
		/// <summary>
		/// Standard class constructor. Read connection parameters from urlsdb.conf
		/// </summary>
		public Database()
		{
			System.IO.StreamReader reader = new System.IO.StreamReader("urlsdb.conf");
			this.connectionString = reader.ReadLine();
			reader.Close();
			reader.Dispose();
		}
		
		/// <summary>
		/// Function open connection with database
		/// </summary>
		public void Open()
		{
			this.dbcon = new MySqlConnection(this.connectionString);
			this.dbcon.Open();
		}
		
		/// <summary>
		/// Function close connection with database
		/// </summary>
		public void Close()
		{
			this.dbcon.Close();
			this.dbcon.Dispose();
		}
		
		/// <summary>
		/// Create database.
		/// </summary>
		public static void CreateDB()
		{
			System.IO.StreamReader reader = new System.IO.StreamReader("urlsdb.conf");
			string connectionString = reader.ReadLine();
			reader.Close();
			reader.Dispose();
			System.Data.IDbConnection dbcon = new MySqlConnection(connectionString);
			dbcon.Open();
			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, " +
					"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 ++;
			dbcmd.Dispose();
			dbcon.Close();
		}
		
		/// <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>
		/// <returns>
		/// A <see cref="System.Int32"/> 1 - for inserted url to database, otherwise 0
		/// </returns>
		public int InstertURL(string urlhash, string url)
		{
			IDbCommand dbcmd = this.dbcon.CreateCommand();
			dbcmd.CommandText = "INSERT INTO `urlslist` VALUES (\"" + urlhash + "\", \"" + url + 
				"\", 0, 0, 0, 0, 0, \"\", \"\");";
			dbcmd.CommandTimeout = 500;
			int amount;
			try
			{
				amount = dbcmd.ExecuteNonQuery();
			}
			catch (MySqlException)
			{
				amount = 0;
			}
			dbcmd.Dispose();
			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)
		{
			IDbCommand dbcmd = this.dbcon.CreateCommand();
			dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `url_hash`='" + urlhash + "';";
			dbcmd.CommandTimeout = 500;
			int amount;
			try
			{
				amount = dbcmd.ExecuteNonQuery();
			}
			catch (MySqlException)
			{
				amount = 0;
			}
			dbcmd.Dispose();
			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)
		{
			System.Text.StringBuilder returnvalue = new System.Text.StringBuilder();
			IDbCommand dbcmd = this.dbcon.CreateCommand();
			dbcmd.CommandTimeout = 500;
			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 1000000 OFFSET " + offset.ToString() + ";";
			}
			IDataReader reader = dbcmd.ExecuteReader();
			while (reader.Read())
			{
				returnvalue.Append(Convert.ToString(reader.GetValue(0)));
				if (offset == -1)
				{
					returnvalue.Append(' ');
					returnvalue.Append(Convert.ToString(reader.GetValue(1)));
				}
				returnvalue.Append("\n");
			}
			reader.Close();
			reader.Dispose();
			dbcmd.CommandText = "SET sort_buffer_size=DEFAULT;";
			dbcmd.ExecuteNonQuery();
			dbcmd.CommandText = "SET read_rnd_buffer_size=DEFAULT;";
			dbcmd.Dispose();
			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>
		/// <returns>
		/// A <see cref="System.Int32"/> amount of URL's deleted from database
		/// </returns>
		public int CleanURLs(string status)
		{
			IDbCommand dbcmd = this.dbcon.CreateCommand();
			dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `http_code1`=" + status + " AND `http_code2`=" + status + 
				" AND `http_code3`=" + status + ";";
			dbcmd.CommandTimeout = 1000;
			int amount;
			try
			{
				amount = dbcmd.ExecuteNonQuery();
			}
			catch (MySqlException)
			{
				amount = 0;
			}
			dbcmd.Dispose();
			return amount;
		}
		
		/// <summary>
		/// Function optimize tables in database
		/// </summary>
		public void OptimizeDB()
		{
			IDbCommand dbcmd = this.dbcon.CreateCommand();
			dbcmd.CommandText = "OPTIMIZE TABLE `urlslist`, `weekly`, `monthly`, `yearly`;";
			dbcmd.CommandTimeout = 500;
			int amount = 0;
			try
			{
				amount = dbcmd.ExecuteNonQuery();
			}
			catch (MySqlException)
			{
			}
			dbcmd.CommandText = "FLUSH TABLES;";
			amount = dbcmd.ExecuteNonQuery();
			amount ++;
			dbcmd.Dispose();
		}
	}
}