~grubng-dev/grubng/tools-urlsdb

« back to all changes in this revision

Viewing changes to Database.cs

  • Committer: thindil
  • Date: 2009-09-30 07:20:04 UTC
  • Revision ID: thindil2@gmail.com-20090930072004-mynp288lh4hb56o7
Initial import

Show diffs side-by-side

added added

removed removed

Lines of Context:
 
1
//  
 
2
//  Copyright (C) 2009 Bartek thindil Jasicki
 
3
// 
 
4
//  This file is part of Grubng
 
5
// 
 
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.
 
10
// 
 
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.
 
15
// 
 
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/>.
 
18
// 
 
19
 
 
20
using System;
 
21
using System.Data;
 
22
using MySql.Data.MySqlClient;
 
23
 
 
24
namespace urlsdb
 
25
{
 
26
        
 
27
        /// <summary>
 
28
        /// Provide functions for create and manipulate database
 
29
        /// </summary>
 
30
        internal sealed class Database
 
31
        {       
 
32
                /// <summary>
 
33
                /// IDbConnection class object.
 
34
                /// </summary>
 
35
                IDbConnection dbcon;
 
36
                /// <summary>
 
37
                /// Connection string to database.
 
38
                /// </summary>
 
39
                string connectionString;
 
40
                
 
41
                /// <summary>
 
42
                /// Standard class constructor. Read connection parameters from urlsdb.conf
 
43
                /// </summary>
 
44
                public Database()
 
45
                {
 
46
                        System.IO.StreamReader reader = new System.IO.StreamReader("urlsdb.conf");
 
47
                        this.connectionString = reader.ReadLine();
 
48
                        reader.Close();
 
49
                        reader.Dispose();
 
50
                }
 
51
                
 
52
                /// <summary>
 
53
                /// Function open connection with database
 
54
                /// </summary>
 
55
                public void Open()
 
56
                {
 
57
                        this.dbcon = new MySqlConnection(this.connectionString);
 
58
                        this.dbcon.Open();
 
59
                }
 
60
                
 
61
                /// <summary>
 
62
                /// Function close connection with database
 
63
                /// </summary>
 
64
                public void Close()
 
65
                {
 
66
                        this.dbcon.Close();
 
67
                        this.dbcon.Dispose();
 
68
                }
 
69
                
 
70
                /// <summary>
 
71
                /// Create database.
 
72
                /// </summary>
 
73
                public static void CreateDB()
 
74
                {
 
75
                        System.IO.StreamReader reader = new System.IO.StreamReader("urlsdb.conf");
 
76
                        string connectionString = reader.ReadLine();
 
77
                        reader.Close();
 
78
                        reader.Dispose();
 
79
                        System.Data.IDbConnection dbcon = new MySqlConnection(connectionString);
 
80
                        dbcon.Open();
 
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();
 
88
                        tmp ++;
 
89
                        dbcmd.Dispose();
 
90
                        dbcon.Close();
 
91
                }
 
92
                
 
93
                /// <summary>
 
94
                /// Insert new URL to database
 
95
                /// </summary>
 
96
                /// <param name="urlhash">
 
97
                /// A <see cref="System.String"/> SHA1 hash of URL to insert to database.
 
98
                /// </param>
 
99
                /// <param name="url">
 
100
                /// A <see cref="System.String"/> URL to insert to database.
 
101
                /// </param>
 
102
                /// <returns>
 
103
                /// A <see cref="System.Int32"/> 1 - for inserted url to database, otherwise 0
 
104
                /// </returns>
 
105
                public int InstertURL(string urlhash, string url)
 
106
                {
 
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;
 
111
                        int amount;
 
112
                        try
 
113
                        {
 
114
                                amount = dbcmd.ExecuteNonQuery();
 
115
                        }
 
116
                        catch (MySqlException)
 
117
                        {
 
118
                                amount = 0;
 
119
                        }
 
120
                        dbcmd.Dispose();
 
121
                        return amount;
 
122
                }
 
123
                
 
124
                /// <summary>
 
125
                /// Delete URL from database
 
126
                /// </summary>
 
127
                /// <param name="urlhash">
 
128
                /// A <see cref="System.String"/> SHA1 hash of URL to delete from database.
 
129
                /// </param>
 
130
                /// <returns>
 
131
                /// A <see cref="System.Int32"/> 1 - if URL was deleted, otherwise 0
 
132
                /// </returns>
 
133
                public int DeleteURL(string urlhash)
 
134
                {
 
135
                        IDbCommand dbcmd = this.dbcon.CreateCommand();
 
136
                        dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `url_hash`='" + urlhash + "';";
 
137
                        dbcmd.CommandTimeout = 500;
 
138
                        int amount;
 
139
                        try
 
140
                        {
 
141
                                amount = dbcmd.ExecuteNonQuery();
 
142
                        }
 
143
                        catch (MySqlException)
 
144
                        {
 
145
                                amount = 0;
 
146
                        }
 
147
                        dbcmd.Dispose();
 
148
                        return amount;
 
149
                }
 
150
                        
 
151
                /// <summary>
 
152
                /// Select URL's from database. Start from selected position.
 
153
                /// </summary>
 
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
 
157
                /// </param>
 
158
                /// <returns>
 
159
                /// A <see cref="System.String"/> list of URL's from database, separated by space
 
160
                /// </returns>
 
161
                public string SelectURLs(int offset)
 
162
                {
 
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();
 
170
                        if (offset == -1)
 
171
                        {
 
172
                                dbcmd.CommandText = "SELECT SQL_NO_CACHE `url`, `last_visited` FROM `urlslist` ORDER BY `last_visited` ASC LIMIT 250000;";
 
173
                        }
 
174
                        else
 
175
                        {
 
176
                                dbcmd.CommandText = "SELECT SQL_NO_CACHE `url` FROM `urlslist` LIMIT 1000000 OFFSET " + offset.ToString() + ";";
 
177
                        }
 
178
                        IDataReader reader = dbcmd.ExecuteReader();
 
179
                        while (reader.Read())
 
180
                        {
 
181
                                returnvalue.Append(Convert.ToString(reader.GetValue(0)));
 
182
                                if (offset == -1)
 
183
                                {
 
184
                                        returnvalue.Append(' ');
 
185
                                        returnvalue.Append(Convert.ToString(reader.GetValue(1)));
 
186
                                }
 
187
                                returnvalue.Append("\n");
 
188
                        }
 
189
                        reader.Close();
 
190
                        reader.Dispose();
 
191
                        dbcmd.CommandText = "SET sort_buffer_size=DEFAULT;";
 
192
                        dbcmd.ExecuteNonQuery();
 
193
                        dbcmd.CommandText = "SET read_rnd_buffer_size=DEFAULT;";
 
194
                        dbcmd.Dispose();
 
195
                        return returnvalue.ToString().TrimEnd(new char[] {' ', '\n'});
 
196
                }
 
197
                
 
198
                /// <summary>
 
199
                /// Function delete URL's from database with selected HTTP status codes
 
200
                /// </summary>
 
201
                /// <param name="status">
 
202
                /// A <see cref="System.String"/> HTTP status code
 
203
                /// </param>
 
204
                /// <returns>
 
205
                /// A <see cref="System.Int32"/> amount of URL's deleted from database
 
206
                /// </returns>
 
207
                public int CleanURLs(string status)
 
208
                {
 
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;
 
213
                        int amount;
 
214
                        try
 
215
                        {
 
216
                                amount = dbcmd.ExecuteNonQuery();
 
217
                        }
 
218
                        catch (MySqlException)
 
219
                        {
 
220
                                amount = 0;
 
221
                        }
 
222
                        dbcmd.Dispose();
 
223
                        return amount;
 
224
                }
 
225
                
 
226
                /// <summary>
 
227
                /// Function optimize tables in database
 
228
                /// </summary>
 
229
                public void OptimizeDB()
 
230
                {
 
231
                        IDbCommand dbcmd = this.dbcon.CreateCommand();
 
232
                        dbcmd.CommandText = "OPTIMIZE TABLE `urlslist`, `weekly`, `monthly`, `yearly`;";
 
233
                        dbcmd.CommandTimeout = 500;
 
234
                        int amount = 0;
 
235
                        try
 
236
                        {
 
237
                                amount = dbcmd.ExecuteNonQuery();
 
238
                        }
 
239
                        catch (MySqlException)
 
240
                        {
 
241
                        }
 
242
                        dbcmd.CommandText = "FLUSH TABLES;";
 
243
                        amount = dbcmd.ExecuteNonQuery();
 
244
                        amount ++;
 
245
                        dbcmd.Dispose();
 
246
                }
 
247
        }
 
248
}