~grubng-dev/grubng/tools-urlsdb

« back to all changes in this revision

Viewing changes to Database.cs

  • Committer: thindil
  • Date: 2011-01-11 16:02:10 UTC
  • Revision ID: thindil2@gmail.com-20110111160210-s0kc5jjcxw2h02g3
probably fixed memory leaks during connection to database

Show diffs side-by-side

added added

removed removed

Lines of Context:
1
1
//  
2
 
//  Copyright (C) 2009,2010 Bartek thindil Jasicki
 
2
//  Copyright (C) 2009,2010,2011 Bartek thindil Jasicki
3
3
// 
4
4
//  This file is part of Grubng
5
5
// 
27
27
        /// <summary>
28
28
        /// Provide functions for create and manipulate database
29
29
        /// </summary>
30
 
        internal sealed class Database : IDisposable
 
30
        internal sealed class Database
31
31
        {       
32
32
                /// <summary>
33
 
                /// IDbConnection class object.
34
 
                /// </summary>
35
 
                IDbConnection dbcon;
36
 
                /// <summary>
37
33
                /// Connection string to database.
38
34
                /// </summary>
39
35
                string connectionString;
51
47
                }
52
48
                
53
49
                /// <summary>
54
 
                /// Function open connection with database
55
 
                /// </summary>
56
 
                public void Open()
57
 
                {
58
 
                        this.dbcon = new MySqlConnection(this.connectionString);
59
 
                        this.dbcon.Open();
60
 
                }
61
 
                
62
 
                /// <summary>
63
 
                /// Function close connection with database
64
 
                /// </summary>
65
 
                public void Close()
66
 
                {
67
 
                        this.dbcon.Close();
68
 
                }
69
 
                
70
 
                /// <summary>
71
50
                /// Create database.
72
51
                /// </summary>
73
52
                public static void CreateDB()
100
79
                /// </summary>
101
80
                public void UpdateDB()
102
81
                {
103
 
                        using (IDbCommand dbcmd = this.dbcon.CreateCommand())
 
82
                        using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
104
83
                        {
105
 
                                dbcmd.CommandText = "ALTER TABLE `urlslist` ADD `robot_result1` smallint, ADD `robot_result2` smallint, " +
106
 
                                        "ADD `robot_result3` smallint;";
107
 
                                dbcmd.CommandTimeout = 500;
108
 
                                int tmp = dbcmd.ExecuteNonQuery();
109
 
                                tmp ++;
 
84
                                dbcon.Open();
 
85
                                using (IDbCommand dbcmd = dbcon.CreateCommand())
 
86
                                {
 
87
                                        dbcmd.CommandText = "ALTER TABLE `urlslist` ADD `robot_result1` smallint, ADD `robot_result2` smallint, " +
 
88
                                                "ADD `robot_result3` smallint;";
 
89
                                        dbcmd.CommandTimeout = 500;
 
90
                                        int tmp = dbcmd.ExecuteNonQuery();
 
91
                                        tmp ++;
 
92
                                }
 
93
                                dbcon.Close();
110
94
                        }
111
95
                }
112
96
                
128
112
                public int InstertURL(string urlhash, string url, string secondhash)
129
113
                {
130
114
                        int amount;
131
 
                        using (IDbCommand dbcmd = this.dbcon.CreateCommand())
 
115
                        using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
132
116
                        {
133
 
                                dbcmd.CommandTimeout = 500;
134
 
                                dbcmd.CommandText = "SELECT count(`url_hash`) FROM `urlslist` WHERE `url_hash`='" + urlhash + "';";
135
 
                                IDataReader reader = dbcmd.ExecuteReader();
136
 
                                reader.Read();
137
 
                                amount = Convert.ToInt32(reader.GetValue(0));
138
 
                                reader.Close();
139
 
                                if (amount != 0)
140
 
                                {
141
 
                                        reader.Dispose();
142
 
                                        dbcmd.Dispose();
143
 
                                        return 0;
144
 
                                }
145
 
                                dbcmd.CommandText = "SELECT count(`url_hash`) FROM `urlslist` WHERE `url_hash`='" + secondhash + "';";
146
 
                                reader = dbcmd.ExecuteReader();
147
 
                                reader.Read();
148
 
                                amount = Convert.ToInt32(reader.GetValue(0));
149
 
                                reader.Close();
150
 
                                if (amount != 0)
151
 
                                {
152
 
                                        reader.Dispose();
153
 
                                        dbcmd.Dispose();
154
 
                                        return 0;
155
 
                                }
156
 
                                reader.Dispose();
157
 
                                dbcmd.CommandText = "INSERT INTO `urlslist` (`url_hash`, `url`, `last_visited`, " +
158
 
                                        "`http_code1`, `http_code2`, `http_code3`, `robot_check`, " +
159
 
                                                "`robot_result1`, `robot_result2`, `robot_result3`," +
160
 
                                                "`user_crawl`, `user_robots`)VALUES (\"" + urlhash + "\", \"" + url + 
161
 
                                                "\", 0, 0, 0, 0, 0, 0, 0, 0, \"\", \"\");";
162
 
                                try
163
 
                                {
164
 
                                        amount = dbcmd.ExecuteNonQuery();
165
 
                                }
166
 
                                catch (MySqlException)
167
 
                                {
168
 
                                        amount = 0;
169
 
                                }
 
117
                                dbcon.Open();
 
118
                                using (IDbCommand dbcmd = dbcon.CreateCommand())
 
119
                                {
 
120
                                        dbcmd.CommandTimeout = 500;
 
121
                                        dbcmd.CommandText = "SELECT count(`url_hash`) FROM `urlslist` WHERE `url_hash`='" + urlhash + "';";
 
122
                                        IDataReader reader = dbcmd.ExecuteReader();
 
123
                                        reader.Read();
 
124
                                        amount = Convert.ToInt32(reader.GetValue(0));
 
125
                                        reader.Close();
 
126
                                        if (amount != 0)
 
127
                                        {
 
128
                                                reader.Dispose();
 
129
                                                dbcmd.Dispose();
 
130
                                                return 0;
 
131
                                        }
 
132
                                        dbcmd.CommandText = "SELECT count(`url_hash`) FROM `urlslist` WHERE `url_hash`='" + secondhash + "';";
 
133
                                        reader = dbcmd.ExecuteReader();
 
134
                                        reader.Read();
 
135
                                        amount = Convert.ToInt32(reader.GetValue(0));
 
136
                                        reader.Close();
 
137
                                        if (amount != 0)
 
138
                                        {
 
139
                                                reader.Dispose();
 
140
                                                dbcmd.Dispose();
 
141
                                                return 0;
 
142
                                        }
 
143
                                        reader.Dispose();
 
144
                                        dbcmd.CommandText = "INSERT INTO `urlslist` (`url_hash`, `url`, `last_visited`, " +
 
145
                                                "`http_code1`, `http_code2`, `http_code3`, `robot_check`, " +
 
146
                                                        "`robot_result1`, `robot_result2`, `robot_result3`," +
 
147
                                                        "`user_crawl`, `user_robots`)VALUES (\"" + urlhash + "\", \"" + url + 
 
148
                                                        "\", 0, 0, 0, 0, 0, 0, 0, 0, \"\", \"\");";
 
149
                                        try
 
150
                                        {
 
151
                                                amount = dbcmd.ExecuteNonQuery();
 
152
                                        }
 
153
                                        catch (MySqlException)
 
154
                                        {
 
155
                                                amount = 0;
 
156
                                        }
 
157
                                }
 
158
                                dbcon.Close();
170
159
                        }
171
160
                        return amount;
172
161
                }
183
172
                public int DeleteURL(string urlhash)
184
173
                {
185
174
                        int amount;
186
 
                        using (IDbCommand dbcmd = this.dbcon.CreateCommand())
 
175
                        using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
187
176
                        {
188
 
                                dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `url_hash`='" + urlhash + "';";
189
 
                                dbcmd.CommandTimeout = 500;
190
 
                                try
191
 
                                {
192
 
                                        amount = dbcmd.ExecuteNonQuery();
193
 
                                }
194
 
                                catch (MySqlException)
195
 
                                {
196
 
                                        amount = 0;
197
 
                                }
 
177
                                dbcon.Open();
 
178
                                using (IDbCommand dbcmd = dbcon.CreateCommand())
 
179
                                {
 
180
                                        dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `url_hash`='" + urlhash + "';";
 
181
                                        dbcmd.CommandTimeout = 500;
 
182
                                        try
 
183
                                        {
 
184
                                                amount = dbcmd.ExecuteNonQuery();
 
185
                                        }
 
186
                                        catch (MySqlException)
 
187
                                        {
 
188
                                                amount = 0;
 
189
                                        }
 
190
                                }
 
191
                                dbcon.Close();
198
192
                        }
199
193
                        return amount;
200
194
                }
212
206
                public string SelectURLs(int offset)
213
207
                {
214
208
                        System.Text.StringBuilder returnvalue = new System.Text.StringBuilder();
215
 
                        using (IDbCommand dbcmd = this.dbcon.CreateCommand())
 
209
                        using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
216
210
                        {
217
 
                                dbcmd.CommandTimeout = 500;
218
 
                                dbcmd.CommandText = "SET session sort_buffer_size=100000000;";
219
 
                                dbcmd.ExecuteNonQuery();
220
 
                                dbcmd.CommandText = "SET read_rnd_buffer_size=100000000;";
221
 
                                dbcmd.ExecuteNonQuery();
222
 
                                if (offset == -1)
223
 
                                {
224
 
                                        dbcmd.CommandText = "SELECT SQL_NO_CACHE `url`, `last_visited` FROM `urlslist` ORDER BY `last_visited` ASC LIMIT 250000;";
225
 
                                }
226
 
                                else
227
 
                                {
228
 
                                        dbcmd.CommandText = "SELECT SQL_NO_CACHE `url` FROM `urlslist` LIMIT 250000 OFFSET " + offset.ToString() + ";";
229
 
                                }
230
 
                                IDataReader reader = dbcmd.ExecuteReader();
231
 
                                while (reader.Read())
232
 
                                {
233
 
                                        returnvalue.Append(Convert.ToString(reader.GetValue(0)));
 
211
                                dbcon.Open();
 
212
                                using (IDbCommand dbcmd = dbcon.CreateCommand())
 
213
                                {
 
214
                                        dbcmd.CommandTimeout = 500;
 
215
                                        dbcmd.CommandText = "SET session sort_buffer_size=100000000;";
 
216
                                        dbcmd.ExecuteNonQuery();
 
217
                                        dbcmd.CommandText = "SET read_rnd_buffer_size=100000000;";
 
218
                                        dbcmd.ExecuteNonQuery();
234
219
                                        if (offset == -1)
235
220
                                        {
236
 
                                                returnvalue.Append(' ');
237
 
                                                returnvalue.Append(Convert.ToString(reader.GetValue(1)));
238
 
                                        }
239
 
                                        returnvalue.Append("\n");
 
221
                                                dbcmd.CommandText = "SELECT SQL_NO_CACHE `url`, `last_visited` FROM `urlslist` ORDER BY `last_visited` ASC LIMIT 250000;";
 
222
                                        }
 
223
                                        else
 
224
                                        {
 
225
                                                dbcmd.CommandText = "SELECT SQL_NO_CACHE `url` FROM `urlslist` LIMIT 250000 OFFSET " + offset.ToString() + ";";
 
226
                                        }
 
227
                                        IDataReader reader = dbcmd.ExecuteReader();
 
228
                                        while (reader.Read())
 
229
                                        {
 
230
                                                returnvalue.Append(Convert.ToString(reader.GetValue(0)));
 
231
                                                if (offset == -1)
 
232
                                                {
 
233
                                                        returnvalue.Append(' ');
 
234
                                                        returnvalue.Append(Convert.ToString(reader.GetValue(1)));
 
235
                                                }
 
236
                                                returnvalue.Append("\n");
 
237
                                        }
 
238
                                        reader.Close();
 
239
                                        reader.Dispose();
 
240
                                        dbcmd.CommandText = "SET sort_buffer_size=DEFAULT;";
 
241
                                        dbcmd.ExecuteNonQuery();
 
242
                                        dbcmd.CommandText = "SET read_rnd_buffer_size=DEFAULT;";
 
243
                                        dbcmd.ExecuteNonQuery();
240
244
                                }
241
 
                                reader.Close();
242
 
                                reader.Dispose();
243
 
                                dbcmd.CommandText = "SET sort_buffer_size=DEFAULT;";
244
 
                                dbcmd.ExecuteNonQuery();
245
 
                                dbcmd.CommandText = "SET read_rnd_buffer_size=DEFAULT;";
246
 
                                dbcmd.ExecuteNonQuery();
 
245
                                dbcon.Close();
247
246
                        }
248
247
                        return returnvalue.ToString().TrimEnd(new char[] {' ', '\n'});
249
248
                }
263
262
                public int CleanURLs(string status, bool solrenabled)
264
263
                {
265
264
                        int amount = 0;
266
 
                        using (IDbCommand dbcmd = this.dbcon.CreateCommand())
 
265
                        using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
267
266
                        {
268
 
                                dbcmd.CommandTimeout = 1000;
269
 
                                //Delete this same URL's from Solr too
270
 
                                if (solrenabled)
 
267
                                dbcon.Open();
 
268
                                using (IDbCommand dbcmd = dbcon.CreateCommand())
271
269
                                {
272
 
                                        dbcmd.CommandText = "SELECT SQL_NO_CACHE `url` FROM `urlslist` WHERE `http_code1`=" + status + " AND `http_code2`=" + 
273
 
                                                status + " AND `http_code3`=" + status + ";";
274
 
                                        IDataReader reader = dbcmd.ExecuteReader();
275
 
                                        System.Text.StringBuilder solrcommand = new System.Text.StringBuilder();
276
 
                                        solrcommand.Append("<delete>");
277
 
                                        string scommand = String.Empty;
278
 
                                        while (reader.Read())
 
270
                                        dbcmd.CommandTimeout = 1000;
 
271
                                        //Delete this same URL's from Solr too
 
272
                                        if (solrenabled)
279
273
                                        {
280
 
                                                solrcommand.Append("<id>http://");
281
 
                                                solrcommand.Append(System.Security.SecurityElement.Escape(reader.GetString(0)));
282
 
                                                solrcommand.Append("</id>");
283
 
                                                amount ++;
284
 
                                                if (amount == 2000)
 
274
                                                dbcmd.CommandText = "SELECT SQL_NO_CACHE `url` FROM `urlslist` WHERE `http_code1`=" + status + " AND `http_code2`=" + 
 
275
                                                        status + " AND `http_code3`=" + status + ";";
 
276
                                                IDataReader reader = dbcmd.ExecuteReader();
 
277
                                                System.Text.StringBuilder solrcommand = new System.Text.StringBuilder();
 
278
                                                solrcommand.Append("<delete>");
 
279
                                                string scommand = String.Empty;
 
280
                                                while (reader.Read())
285
281
                                                {
286
 
                                                        solrcommand.Append("</delete>");
287
 
                                                        scommand = System.Text.RegularExpressions.Regex.Replace(solrcommand.ToString(), @"[\p{IsC}]", String.Empty);
288
 
                                                        MainClass.SendToSolr(scommand);
289
 
                                                        MainClass.SendToSolr("<commit/>");
290
 
                                                        solrcommand.Remove(0, solrcommand.Length);
291
 
                                                        solrcommand.Append("<delete>");
292
 
                                                        amount = 0;
 
282
                                                        solrcommand.Append("<id>http://");
 
283
                                                        solrcommand.Append(System.Security.SecurityElement.Escape(reader.GetString(0)));
 
284
                                                        solrcommand.Append("</id>");
 
285
                                                        amount ++;
 
286
                                                        if (amount == 2000)
 
287
                                                        {
 
288
                                                                solrcommand.Append("</delete>");
 
289
                                                                scommand = System.Text.RegularExpressions.Regex.Replace(solrcommand.ToString(), @"[\p{IsC}]", String.Empty);
 
290
                                                                MainClass.SendToSolr(scommand);
 
291
                                                                MainClass.SendToSolr("<commit/>");
 
292
                                                                solrcommand.Remove(0, solrcommand.Length);
 
293
                                                                solrcommand.Append("<delete>");
 
294
                                                                amount = 0;
 
295
                                                        }
293
296
                                                }
294
 
                                        }
295
 
                                        reader.Close();
296
 
                                        reader.Dispose();
297
 
                                        solrcommand.Append("</delete>");
298
 
                                        scommand = System.Text.RegularExpressions.Regex.Replace(solrcommand.ToString(), @"[\p{IsC}]", String.Empty);
299
 
                                        MainClass.SendToSolr(scommand);
300
 
                                        MainClass.SendToSolr("<commit/>");
301
 
                                }
302
 
                                dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `http_code1`=" + status + " AND `http_code2`=" + status + 
303
 
                                        " AND `http_code3`=" + status + ";";
304
 
                                try
305
 
                                {
306
 
                                        amount = dbcmd.ExecuteNonQuery();
307
 
                                }
308
 
                                catch (MySqlException)
309
 
                                {
310
 
                                        amount = 0;
311
 
                                }
 
297
                                                reader.Close();
 
298
                                                reader.Dispose();
 
299
                                                solrcommand.Append("</delete>");
 
300
                                                scommand = System.Text.RegularExpressions.Regex.Replace(solrcommand.ToString(), @"[\p{IsC}]", String.Empty);
 
301
                                                MainClass.SendToSolr(scommand);
 
302
                                                MainClass.SendToSolr("<commit/>");
 
303
                                        }
 
304
                                        dbcmd.CommandText = "DELETE FROM `urlslist` WHERE `http_code1`=" + status + " AND `http_code2`=" + status + 
 
305
                                                " AND `http_code3`=" + status + ";";
 
306
                                        try
 
307
                                        {
 
308
                                                amount = dbcmd.ExecuteNonQuery();
 
309
                                        }
 
310
                                        catch (MySqlException)
 
311
                                        {
 
312
                                                amount = 0;
 
313
                                        }
 
314
                                }
 
315
                                dbcon.Close();
312
316
                        }
313
317
                        return amount;
314
318
                }
318
322
                /// </summary>
319
323
                public void OptimizeDB()
320
324
                {
321
 
                        using (IDbCommand dbcmd = this.dbcon.CreateCommand())
 
325
                        using (IDbConnection dbcon = new MySqlConnection(this.connectionString))
322
326
                        {
323
 
                                dbcmd.CommandText = "OPTIMIZE TABLE `urlslist`, `weekly`, `monthly`, `yearly`;";
324
 
                                dbcmd.CommandTimeout = 5000;
325
 
                                int amount = 0;
326
 
                                try
327
 
                                {
328
 
                                        amount = dbcmd.ExecuteNonQuery();
329
 
                                }
330
 
                                catch (MySqlException)
331
 
                                {
332
 
                                }
333
 
                                dbcmd.CommandText = "FLUSH TABLES;";
334
 
                                try
335
 
                                {
336
 
                                        amount = dbcmd.ExecuteNonQuery();
337
 
                                }
338
 
                                catch (MySqlException)
339
 
                                {
340
 
                                }
341
 
                                amount ++;
 
327
                                dbcon.Open();
 
328
                                using (IDbCommand dbcmd = dbcon.CreateCommand())
 
329
                                {
 
330
                                        dbcmd.CommandText = "OPTIMIZE TABLE `urlslist`, `weekly`, `monthly`, `yearly`;";
 
331
                                        dbcmd.CommandTimeout = 5000;
 
332
                                        int amount = 0;
 
333
                                        try
 
334
                                        {
 
335
                                                amount = dbcmd.ExecuteNonQuery();
 
336
                                        }
 
337
                                        catch (MySqlException)
 
338
                                        {
 
339
                                        }
 
340
                                        dbcmd.CommandText = "FLUSH TABLES;";
 
341
                                        try
 
342
                                        {
 
343
                                                amount = dbcmd.ExecuteNonQuery();
 
344
                                        }
 
345
                                        catch (MySqlException)
 
346
                                        {
 
347
                                        }
 
348
                                        amount ++;
 
349
                                }
 
350
                                dbcon.Close();
342
351
                        }
343
352
                }
344
 
                
345
 
                /// <summary>
346
 
                /// Function dispose unmanaged resources.
347
 
                /// </summary>
348
 
                public void Dispose()
349
 
                {
350
 
                        this.dbcon.Dispose();
351
 
                }
352
353
        }
353
354
}