· 7 years ago · Dec 14, 2018, 08:56 PM
1using System;
2using System.Collections;
3using System.Collections.Generic;
4using System.Linq;
5using System.Text;
6using MySql.Data;
7using MySql.Data.MySqlClient;
8
9namespace FreeAir
10{
11 class FreeAirDatabase
12 {
13 FreeAirConfig _config;
14
15 private string ConnectionString
16 {
17 get
18 {
19 return String.Format("server={0};user={1};port=3306;password={2};",
20 _config.DbUrl, _config.DbUsername, _config.DbPassword);
21 }
22 }
23
24 public FreeAirDatabase(FreeAirConfig Config)
25 {
26 if (Config != null)
27 {
28 _config = Config;
29 }
30 else
31 {
32 throw new ArgumentNullException("Need an instance of a configuration to use the database.");
33 }
34 }
35
36 /// <summary>
37 /// Creates a blank database.
38 /// </summary>
39 /// <param name="overwrite">If you would like to overwrite an existing database by the name provided, set to true.</param>
40 /// <returns>Success of the operation.</returns>
41 public bool CreateNewDatabase(bool overwrite, bool populate)
42 {
43 MySqlConnection conn = new MySqlConnection(ConnectionString);
44 string query = "";
45
46 if (overwrite)
47 {
48 query = String.Format("DROP DATABASE IF EXISTS {0}; CREATE DATABASE IF NOT EXISTS {0}", _config.DbDatabase);
49 }
50 else if (!overwrite)
51 {
52 query = "CREATE DATABASE IF NOT EXISTS " + _config.DbDatabase;
53 }
54
55 try
56 {
57 conn.Open();
58 }
59 catch
60 {
61 // Could not open the connection.
62 return false;
63 }
64
65 // Create database.
66 bool success = false;
67 if (overwrite)
68 {
69 success = ProcessMultiNonQuery(query, conn);
70 }
71 else if (!overwrite)
72 {
73 MySqlCommand cmd = new MySqlCommand(query, conn);
74 int rCode = cmd.ExecuteNonQuery();
75
76 if (rCode == -1)
77 success = true;
78 else
79 success = false;
80 }
81
82 // Populate the database if requested.
83 if (!success)
84 return false;
85 else if (success && populate)
86 success = PopulateDatabase(conn, overwrite);
87
88 conn.Close();
89 conn.Dispose();
90
91 return success;
92 }
93
94 /// <summary>
95 /// Populates the database with new tables.
96 /// </summary>
97 /// <param name="overwrite">If true, it will overwrite existing tables.</param>
98 /// <returns>Returns true if successful. Returns false if tables already exist or other failure condition occurs.</returns>
99 public bool PopulateDatabase(bool overwrite)
100 {
101 MySqlConnection conn = new MySqlConnection(ConnectionString);
102
103 try
104 {
105 conn.Open();
106 }
107 catch
108 {
109 return false;
110 }
111
112 bool success = PopulateDatabase(conn, overwrite);
113
114 conn.Close();
115 conn.Dispose();
116
117 return success;
118 }
119
120 /// <summary>
121 /// Populates the database with new tables.
122 /// </summary>
123 /// <param name="conn">Existing open connection to the server (not directed to any database).</param>
124 /// <param name="overwrite">If set to true, it will overwrite any existing tables.</param>
125 /// <returns></returns>
126 private bool PopulateDatabase(MySqlConnection conn, bool overwrite)
127 {
128 string query = "";
129
130 if (overwrite)
131 query += "USE " + _config.DbDatabase + "; DROP TABLE IF EXISTS categories;" +
132 "DROP TABLE IF EXISTS media; DROP TABLE IF EXISTS playlist;";
133
134 query += "CREATE TABLE categories (c_id INT NOT NULL AUTO_INCREMENT, " +
135 "c_name VARCHAR(10) NOT NULL, c_desc VARCHAR(100), PRIMARY KEY (c_id));" +
136 "CREATE TABLE media (m_id INT NOT NULL AUTO_INCREMENT, c_id INT NOT NULL, " +
137 "m_title VARCHAR(20), m_author VARCHAR(20), m_file VARCHAR(50), " +
138 "m_added DATETIME NOT NULL, m_expires DATETIME, PRIMARY KEY (m_id));" +
139 "CREATE TABLE playlist (p_id INT NOT NULL AUTO_INCREMENT, m_id INT NOT NULL, " +
140 "p_hour INT NOT NULL, p_start DATETIME NOT NULL, p_end DATETIME NOT NULL, " +
141 "p_order INT NOT NULL, p_crossfade INT, PRIMARY KEY (p_id))";
142
143 return ProcessMultiNonQuery(query, conn);
144 }
145
146 /// <summary>
147 /// Adds a category to the database.
148 /// </summary>
149 /// <param name="name">Name of the category. Up to 10 characters.</param>
150 /// <param name="description">Description of the category. Up to 100 characters.</param>
151 /// <returns>The ID of the category once added to the database. Returns -1 if the category could not be added.</returns>
152 public int AddCategory(string name, string description)
153 {
154 if (name.Length > 10 || description.Length > 100)
155 {
156 return -1;
157 }
158
159 MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
160 string query = String.Format("INSERT INTO `categories` (c_name, c_desc) VALUES ('{0}', '{1}');", name, description);
161 MySqlCommand cmd = new MySqlCommand(query, conn);
162
163 try
164 {
165 conn.Open();
166 }
167 catch
168 {
169 return -1;
170 }
171
172 int ind = GetLastInsertId(conn);
173 cmd.Connection.Close();
174 cmd.Dispose();
175 conn.Dispose();
176 return ind;
177 }
178
179 /// <summary>
180 /// Removes a category from the database.
181 /// </summary>
182 /// <param name="categoryId">ID of the category to remove.</param>
183 /// <param name="moveCategoryId">ID of the category to move media items to which might be in this category.</param>
184 /// <returns>If the operation was successful.</returns>
185 public bool RemoveCategory(int categoryId, int moveCategoryId)
186 {
187 MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
188 string query = String.Format("DELETE FROM `categories` WHERE c_id='{0}'", categoryId);
189 MySqlCommand cmd = new MySqlCommand(query, conn);
190 bool success = false;
191
192 try
193 {
194 conn.Open();
195 }
196 catch
197 {
198 return false;
199 }
200
201 if (cmd.ExecuteNonQuery() == 1)
202 {
203 cmd.CommandText = String.Format("UPDATE `media` SET c_id='{0}' WHERE c_id='{1}'", moveCategoryId, categoryId);
204 if (cmd.ExecuteNonQuery() == 1)
205 success = true;
206 }
207
208 cmd.Dispose();
209 conn.Close();
210 conn.Dispose();
211 return success;
212 }
213
214 /// <summary>
215 /// Gets an array of strings containing information about each category from the database.
216 /// </summary>
217 /// <returns></returns>
218 public string[] GetAllCategories()
219 {
220 MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
221 MySqlCommand cmd = new MySqlCommand("SELECT * FROM `categories`;", conn);
222 MySqlDataReader reader = null;
223 ArrayList rows = new ArrayList();
224
225 try
226 {
227 conn.Open();
228 }
229 catch
230 {
231 return null;
232 }
233
234 reader = cmd.ExecuteReader();
235
236 while (reader.Read())
237 {
238 string row = String.Format("{0},{1},{2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
239 rows.Add(row);
240 }
241
242 reader.Close();
243 reader.Dispose();
244 cmd.Dispose();
245 conn.Close();
246 conn.Dispose();
247 return (string[])rows.ToArray(typeof(string));
248 }
249
250 /// <summary>
251 /// Make modifications to a category already stored in the database.
252 /// </summary>
253 /// <param name="categoryId">The ID of the category to modify.</param>
254 /// <param name="categoryName">The new name of the category.</param>
255 /// <param name="categoryDescription">The new description of the category.</param>
256 /// <returns>If the query was successful or not.</returns>
257 public bool ModifyCategory(int categoryId, string categoryName, string categoryDescription)
258 {
259 MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
260 string query = String.Format("UPDATE `categories` SET c_name='{0}', c_desc='{1}' WHERE c_id='{2}';", categoryName, categoryDescription, categoryId);
261 MySqlCommand cmd = new MySqlCommand(query, conn);
262 bool success = false;
263
264 try
265 {
266 conn.Open();
267 }
268 catch
269 {
270 return false;
271 }
272
273 if (cmd.ExecuteNonQuery() == 1)
274 {
275 success = true;
276 }
277
278 cmd.Dispose();
279 conn.Close();
280 conn.Dispose();
281 return success;
282 }
283
284 /// <summary>
285 /// Adds a single media item to the database.
286 /// </summary>
287 /// <param name="categoryId">The category this media belongs to. Can't be null.</param>
288 /// <param name="title">The title of the media..</param>
289 /// <param name="author">Author of the media.</param>
290 /// <param name="fileName">The name of the file.</param>
291 /// <param name="expires"></param>
292 /// <returns></returns>
293 public bool AddMedia(int categoryId, string title, string author, string fileName, DateTime expires)
294 {
295 bool success = false;
296 MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
297 string query = String.Format("INSERT INTO `media` (c_id, m_title, m_author, m_file, m_added, m_expires) VALUES (" +
298 "'{0}', '{1}', '{2}', '{3}', '{4}', '{5}');", categoryId, title, author, fileName, GetMySqlDateTime(DateTime.Now), GetMySqlDateTime(expires));
299 MySqlCommand cmd = new MySqlCommand(query, conn);
300
301 try
302 {
303 conn.Open();
304 }
305 catch
306 {
307 return false;
308 }
309
310 if (cmd.ExecuteNonQuery() == 1)
311 {
312 success = true;
313 }
314
315 cmd.Dispose();
316 conn.Close();
317 conn.Dispose();
318 return success;
319 }
320
321 /// <summary>
322 /// Gets all media in a specified category from the database.
323 /// </summary>
324 /// <param name="categoryId"></param>
325 /// <returns></returns>
326 public string[] GetMediaByCategory(int categoryId)
327 {
328 ArrayList media = new ArrayList();
329 MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
330 string query = "SELECT * FROM `media` WHERE c_id = '" + Convert.ToString(categoryId) + "';";
331 MySqlCommand cmd = new MySqlCommand(query, conn);
332
333 try
334 {
335 conn.Open();
336 }
337 catch
338 {
339 return null;
340 }
341
342 MySqlDataReader reader = cmd.ExecuteReader();
343
344 while (reader.Read())
345 {
346 string row = String.Format("{0},{1},{2},{3},{4},{5},{6}", reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2),
347 reader.GetString(3), reader.GetString(4), GetMySqlDateTime(reader.GetDateTime(5)), GetMySqlDateTime(reader.GetDateTime(6)));
348 media.Add(row);
349 }
350
351 reader.Close();
352 reader.Dispose();
353 cmd.Dispose();
354 conn.Close();
355 conn.Dispose();
356 return (string[])media.ToArray(typeof(string));
357 }
358
359 /// <summary>
360 /// Gets a DateTime string to enter into a MySQL database from a .NET DateTime object.
361 /// </summary>
362 /// <param name="dt">The datetime to reference.</param>
363 /// <returns></returns>
364 private string GetMySqlDateTime(DateTime dt)
365 {
366 if (dt != null)
367 {
368 return String.Format("{0}-{1}-{2} {3}:{4}:{5}", dt.Year,
369 Convert.ToString(dt.Month).PadLeft(2, '0'), Convert.ToString(dt.Day).PadLeft(2, '0'),
370 Convert.ToString(dt.Hour).PadLeft(2, '0'), Convert.ToString(dt.Minute).PadLeft(2, '0'),
371 Convert.ToString(dt.Second).PadLeft(2, '0'));
372 }
373 else
374 {
375 return "NULL";
376 }
377 }
378
379 /// <summary>
380 /// Gets the last AUTO_INCREMENT row ID created from the MySQL database.
381 /// </summary>
382 /// <param name="conn">A live connection to MySQL.</param>
383 /// <returns>The ID of the last created row.</returns>
384 private int GetLastInsertId(MySqlConnection conn)
385 {
386 MySqlCommand cmd = new MySqlCommand("SELECT LAST_INSERT_ID();", conn);
387 MySqlDataReader reader = null;
388 int index = -1;
389
390 try
391 {
392 reader = cmd.ExecuteReader();
393 }
394 catch
395 {
396 return -1;
397 }
398
399 if (reader != null && reader.Read())
400 {
401 index = reader.GetInt32(0);
402 }
403 else
404 {
405 return -1;
406 }
407
408 reader.Close();
409 return index;
410 }
411
412 /// <summary>
413 /// Processes multi-non-query strings.
414 /// </summary>
415 /// <param name="queryString">Query string containing queries.</param>
416 /// <param name="conn">The connection to the database.</param>
417 /// <returns>A boolean value indicating total success or partial/full failure.</returns>
418 /// <remarks>This function is necessitated by the one query per execution rule most MySQL databases
419 /// adhere to religiously. What we do here to get around that is by looping through the non-queries
420 /// one at a time while making it appear as one swift instruction on the surface.</remarks>
421 private bool ProcessMultiNonQuery(string queryString, MySqlConnection conn)
422 {
423 ArrayList arrLst = new ArrayList();
424
425 foreach (string query in queryString.Split(';'))
426 {
427 if (query != "")
428 {
429 MySqlCommand cmd = new MySqlCommand(query.Trim(), conn);
430 int rCode = cmd.ExecuteNonQuery();
431 if (rCode == 1)
432 arrLst.Add(true);
433 else
434 arrLst.Add(false);
435 }
436 }
437
438 bool[] returns = (bool[])arrLst.ToArray(typeof(bool));
439 return returns.All(x => x);
440 }
441 }
442}