2
// Copyright (C) 2009 Bartek thindil Jasicki
4
// This file is part of Grubng
6
// Grubng is free software: you can redistribute it and/or modify
7
// it under the terms of the GNU General Public License as published by
8
// the Free Software Foundation, either version 3 of the License, or
9
// (at your option) any later version.
11
// This program is distributed in the hope that it will be useful,
12
// but WITHOUT ANY WARRANTY; without even the implied warranty of
13
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14
// GNU General Public License for more details.
16
// You should have received a copy of the GNU General Public License
17
// along with this program. If not, see <http://www.gnu.org/licenses/>.
22
using MySql.Data.MySqlClient;
28
/// Provide functions for create and manipulate database
30
internal sealed class Database
33
/// IDbConnection class object.
37
/// Connection string to database.
39
string connectionString;
42
/// Standard class constructor. Read connection parameters from urlsdb.conf
46
System.IO.StreamReader reader = new System.IO.StreamReader("urlsdb.conf");
47
this.connectionString = reader.ReadLine();
53
/// Function open connection with database
57
this.dbcon = new MySqlConnection(this.connectionString);
62
/// Function close connection with database
73
public static void CreateDB()
75
System.IO.StreamReader reader = new System.IO.StreamReader("urlsdb.conf");
76
string connectionString = reader.ReadLine();
79
System.Data.IDbConnection dbcon = new MySqlConnection(connectionString);
81
IDbCommand dbcmd = dbcon.CreateCommand();
82
dbcmd.CommandText = "CREATE TABLE urlslist (url_hash varchar(50), url TEXT, last_visited bigint, " +
83
"http_code1 smallint, http_code2 smallint, http_code3 smallint, robot_check bigint, " +
84
"user_crawl varchar(255), user_robots varchar(255));" +
85
"CREATE UNIQUE INDEX url_hash ON urlslist(url_hash);" +
86
"CREATE INDEX last_visited ON urlslist(last_visited);";
87
int tmp = dbcmd.ExecuteNonQuery();
94
/// Insert new URL to database
96
/// <param name="urlhash">
97
/// A <see cref="System.String"/> SHA1 hash of URL to insert to database.
99
/// <param name="url">
100
/// A <see cref="System.String"/> URL to insert to database.
103
/// A <see cref="System.Int32"/> 1 - for inserted url to database, otherwise 0
105
public int InstertURL(string urlhash, string url)
107
IDbCommand dbcmd = this.dbcon.CreateCommand();
108
dbcmd.CommandText = "INSERT INTO `urlslist` VALUES (\"" + urlhash + "\", \"" + url +
109
"\", 0, 0, 0, 0, 0, \"\", \"\");";
110
dbcmd.CommandTimeout = 500;
114
amount = dbcmd.ExecuteNonQuery();
116
catch (MySqlException)
125
/// Delete URL from database
127
/// <param name="urlhash">
128
/// A <see cref="System.String"/> SHA1 hash of URL to delete from database.
131
/// A <see cref="System.Int32"/> 1 - if URL was deleted, otherwise 0
133
public int DeleteURL(string urlhash)
135
IDbCommand dbcmd = this.dbcon.CreateCommand();
136
dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `url_hash`='" + urlhash + "';";
137
dbcmd.CommandTimeout = 500;
141
amount = dbcmd.ExecuteNonQuery();
143
catch (MySqlException)
152
/// Select URL's from database. Start from selected position.
154
/// <param name="offset">
155
/// A <see cref="System.Int32"/> position from which start fetch URL's from database. If offset = -1 select only first 250000
156
/// URL's from database
159
/// A <see cref="System.String"/> list of URL's from database, separated by space
161
public string SelectURLs(int offset)
163
System.Text.StringBuilder returnvalue = new System.Text.StringBuilder();
164
IDbCommand dbcmd = this.dbcon.CreateCommand();
165
dbcmd.CommandTimeout = 500;
166
dbcmd.CommandText = "SET session sort_buffer_size=100000000;";
167
dbcmd.ExecuteNonQuery();
168
dbcmd.CommandText = "SET read_rnd_buffer_size=100000000;";
169
dbcmd.ExecuteNonQuery();
172
dbcmd.CommandText = "SELECT SQL_NO_CACHE `url`, `last_visited` FROM `urlslist` ORDER BY `last_visited` ASC LIMIT 250000;";
176
dbcmd.CommandText = "SELECT SQL_NO_CACHE `url` FROM `urlslist` LIMIT 1000000 OFFSET " + offset.ToString() + ";";
178
IDataReader reader = dbcmd.ExecuteReader();
179
while (reader.Read())
181
returnvalue.Append(Convert.ToString(reader.GetValue(0)));
184
returnvalue.Append(' ');
185
returnvalue.Append(Convert.ToString(reader.GetValue(1)));
187
returnvalue.Append("\n");
191
dbcmd.CommandText = "SET sort_buffer_size=DEFAULT;";
192
dbcmd.ExecuteNonQuery();
193
dbcmd.CommandText = "SET read_rnd_buffer_size=DEFAULT;";
195
return returnvalue.ToString().TrimEnd(new char[] {' ', '\n'});
199
/// Function delete URL's from database with selected HTTP status codes
201
/// <param name="status">
202
/// A <see cref="System.String"/> HTTP status code
205
/// A <see cref="System.Int32"/> amount of URL's deleted from database
207
public int CleanURLs(string status)
209
IDbCommand dbcmd = this.dbcon.CreateCommand();
210
dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `http_code1`=" + status + " AND `http_code2`=" + status +
211
" AND `http_code3`=" + status + ";";
212
dbcmd.CommandTimeout = 1000;
216
amount = dbcmd.ExecuteNonQuery();
218
catch (MySqlException)
227
/// Function optimize tables in database
229
public void OptimizeDB()
231
IDbCommand dbcmd = this.dbcon.CreateCommand();
232
dbcmd.CommandText = "OPTIMIZE TABLE `urlslist`, `weekly`, `monthly`, `yearly`;";
233
dbcmd.CommandTimeout = 500;
237
amount = dbcmd.ExecuteNonQuery();
239
catch (MySqlException)
242
dbcmd.CommandText = "FLUSH TABLES;";
243
amount = dbcmd.ExecuteNonQuery();