MySQLGenericTableHandler.cs 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365
  1. /*
  2. * Copyright (c) Contributors, http://opensimulator.org/
  3. * See CONTRIBUTORS.TXT for a full list of copyright holders.
  4. *
  5. * Redistribution and use in source and binary forms, with or without
  6. * modification, are permitted provided that the following conditions are met:
  7. * * Redistributions of source code must retain the above copyright
  8. * notice, this list of conditions and the following disclaimer.
  9. * * Redistributions in binary form must reproduce the above copyright
  10. * notice, this list of conditions and the following disclaimer in the
  11. * documentation and/or other materials provided with the distribution.
  12. * * Neither the name of the OpenSimulator Project nor the
  13. * names of its contributors may be used to endorse or promote products
  14. * derived from this software without specific prior written permission.
  15. *
  16. * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
  17. * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  18. * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  19. * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
  20. * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
  21. * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
  22. * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  23. * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  24. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
  25. * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  26. */
  27. using System;
  28. using System.Collections.Generic;
  29. using System.Data;
  30. using System.Reflection;
  31. using log4net;
  32. using MySql.Data.MySqlClient;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. using OpenSim.Region.Framework.Interfaces;
  36. namespace OpenSim.Data.MySQL
  37. {
  38. public class MySQLGenericTableHandler<T> : MySqlFramework where T: class, new()
  39. {
  40. // private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  41. protected Dictionary<string, FieldInfo> m_Fields =
  42. new Dictionary<string, FieldInfo>();
  43. protected List<string> m_ColumnNames = null;
  44. protected string m_Realm;
  45. protected FieldInfo m_DataField = null;
  46. protected virtual Assembly Assembly
  47. {
  48. get { return GetType().Assembly; }
  49. }
  50. public MySQLGenericTableHandler(string connectionString,
  51. string realm, string storeName) : base(connectionString)
  52. {
  53. m_Realm = realm;
  54. m_connectionString = connectionString;
  55. if (storeName != String.Empty)
  56. {
  57. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  58. {
  59. dbcon.Open();
  60. Migration m = new Migration(dbcon, Assembly, storeName);
  61. m.Update();
  62. }
  63. }
  64. Type t = typeof(T);
  65. FieldInfo[] fields = t.GetFields(BindingFlags.Public |
  66. BindingFlags.Instance |
  67. BindingFlags.DeclaredOnly);
  68. if (fields.Length == 0)
  69. return;
  70. foreach (FieldInfo f in fields)
  71. {
  72. if (f.Name != "Data")
  73. m_Fields[f.Name] = f;
  74. else
  75. m_DataField = f;
  76. }
  77. }
  78. private void CheckColumnNames(IDataReader reader)
  79. {
  80. if (m_ColumnNames != null)
  81. return;
  82. List<string> columnNames = new List<string>();
  83. DataTable schemaTable = reader.GetSchemaTable();
  84. foreach (DataRow row in schemaTable.Rows)
  85. {
  86. if (row["ColumnName"] != null &&
  87. (!m_Fields.ContainsKey(row["ColumnName"].ToString())))
  88. columnNames.Add(row["ColumnName"].ToString());
  89. }
  90. m_ColumnNames = columnNames;
  91. }
  92. public virtual T[] Get(string field, string key)
  93. {
  94. return Get(new string[] { field }, new string[] { key });
  95. }
  96. public virtual T[] Get(string[] fields, string[] keys)
  97. {
  98. if (fields.Length != keys.Length)
  99. return new T[0];
  100. List<string> terms = new List<string>();
  101. using (MySqlCommand cmd = new MySqlCommand())
  102. {
  103. for (int i = 0 ; i < fields.Length ; i++)
  104. {
  105. cmd.Parameters.AddWithValue(fields[i], keys[i]);
  106. terms.Add("`" + fields[i] + "` = ?" + fields[i]);
  107. }
  108. string where = String.Join(" and ", terms.ToArray());
  109. string query = String.Format("select * from {0} where {1}",
  110. m_Realm, where);
  111. cmd.CommandText = query;
  112. return DoQuery(cmd);
  113. }
  114. }
  115. protected T[] DoQuery(MySqlCommand cmd)
  116. {
  117. List<T> result = new List<T>();
  118. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  119. {
  120. dbcon.Open();
  121. cmd.Connection = dbcon;
  122. using (IDataReader reader = cmd.ExecuteReader())
  123. {
  124. if (reader == null)
  125. return new T[0];
  126. CheckColumnNames(reader);
  127. while (reader.Read())
  128. {
  129. T row = new T();
  130. foreach (string name in m_Fields.Keys)
  131. {
  132. if (reader[name] is DBNull)
  133. {
  134. continue;
  135. }
  136. if (m_Fields[name].FieldType == typeof(bool))
  137. {
  138. int v = Convert.ToInt32(reader[name]);
  139. m_Fields[name].SetValue(row, v != 0 ? true : false);
  140. }
  141. else if (m_Fields[name].FieldType == typeof(UUID))
  142. {
  143. m_Fields[name].SetValue(row, DBGuid.FromDB(reader[name]));
  144. }
  145. else if (m_Fields[name].FieldType == typeof(int))
  146. {
  147. int v = Convert.ToInt32(reader[name]);
  148. m_Fields[name].SetValue(row, v);
  149. }
  150. else
  151. {
  152. m_Fields[name].SetValue(row, reader[name]);
  153. }
  154. }
  155. if (m_DataField != null)
  156. {
  157. Dictionary<string, string> data =
  158. new Dictionary<string, string>();
  159. foreach (string col in m_ColumnNames)
  160. {
  161. data[col] = reader[col].ToString();
  162. if (data[col] == null)
  163. data[col] = String.Empty;
  164. }
  165. m_DataField.SetValue(row, data);
  166. }
  167. result.Add(row);
  168. }
  169. }
  170. }
  171. return result.ToArray();
  172. }
  173. public virtual T[] Get(string where)
  174. {
  175. using (MySqlCommand cmd = new MySqlCommand())
  176. {
  177. string query = String.Format("select * from {0} where {1}",
  178. m_Realm, where);
  179. cmd.CommandText = query;
  180. return DoQuery(cmd);
  181. }
  182. }
  183. public virtual bool Store(T row)
  184. {
  185. // m_log.DebugFormat("[MYSQL GENERIC TABLE HANDLER]: Store(T row) invoked");
  186. using (MySqlCommand cmd = new MySqlCommand())
  187. {
  188. string query = "";
  189. List<String> names = new List<String>();
  190. List<String> values = new List<String>();
  191. foreach (FieldInfo fi in m_Fields.Values)
  192. {
  193. names.Add(fi.Name);
  194. values.Add("?" + fi.Name);
  195. // Temporarily return more information about what field is unexpectedly null for
  196. // http://opensimulator.org/mantis/view.php?id=5403. This might be due to a bug in the
  197. // InventoryTransferModule or we may be required to substitute a DBNull here.
  198. if (fi.GetValue(row) == null)
  199. throw new NullReferenceException(
  200. string.Format(
  201. "[MYSQL GENERIC TABLE HANDLER]: Trying to store field {0} for {1} which is unexpectedly null",
  202. fi.Name, row));
  203. cmd.Parameters.AddWithValue(fi.Name, fi.GetValue(row).ToString());
  204. }
  205. if (m_DataField != null)
  206. {
  207. Dictionary<string, string> data =
  208. (Dictionary<string, string>)m_DataField.GetValue(row);
  209. foreach (KeyValuePair<string, string> kvp in data)
  210. {
  211. names.Add(kvp.Key);
  212. values.Add("?" + kvp.Key);
  213. cmd.Parameters.AddWithValue("?" + kvp.Key, kvp.Value);
  214. }
  215. }
  216. query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values (" + String.Join(",", values.ToArray()) + ")";
  217. cmd.CommandText = query;
  218. if (ExecuteNonQuery(cmd) > 0)
  219. return true;
  220. return false;
  221. }
  222. }
  223. public virtual bool Delete(string field, string key)
  224. {
  225. return Delete(new string[] { field }, new string[] { key });
  226. }
  227. public virtual bool Delete(string[] fields, string[] keys)
  228. {
  229. // m_log.DebugFormat(
  230. // "[MYSQL GENERIC TABLE HANDLER]: Delete(string[] fields, string[] keys) invoked with {0}:{1}",
  231. // string.Join(",", fields), string.Join(",", keys));
  232. if (fields.Length != keys.Length)
  233. return false;
  234. List<string> terms = new List<string>();
  235. using (MySqlCommand cmd = new MySqlCommand())
  236. {
  237. for (int i = 0 ; i < fields.Length ; i++)
  238. {
  239. cmd.Parameters.AddWithValue(fields[i], keys[i]);
  240. terms.Add("`" + fields[i] + "` = ?" + fields[i]);
  241. }
  242. string where = String.Join(" and ", terms.ToArray());
  243. string query = String.Format("delete from {0} where {1}", m_Realm, where);
  244. cmd.CommandText = query;
  245. return ExecuteNonQuery(cmd) > 0;
  246. }
  247. }
  248. public long GetCount(string field, string key)
  249. {
  250. return GetCount(new string[] { field }, new string[] { key });
  251. }
  252. public long GetCount(string[] fields, string[] keys)
  253. {
  254. if (fields.Length != keys.Length)
  255. return 0;
  256. List<string> terms = new List<string>();
  257. using (MySqlCommand cmd = new MySqlCommand())
  258. {
  259. for (int i = 0; i < fields.Length; i++)
  260. {
  261. cmd.Parameters.AddWithValue(fields[i], keys[i]);
  262. terms.Add("`" + fields[i] + "` = ?" + fields[i]);
  263. }
  264. string where = String.Join(" and ", terms.ToArray());
  265. string query = String.Format("select count(*) from {0} where {1}",
  266. m_Realm, where);
  267. cmd.CommandText = query;
  268. Object result = DoQueryScalar(cmd);
  269. return Convert.ToInt64(result);
  270. }
  271. }
  272. public long GetCount(string where)
  273. {
  274. using (MySqlCommand cmd = new MySqlCommand())
  275. {
  276. string query = String.Format("select count(*) from {0} where {1}",
  277. m_Realm, where);
  278. cmd.CommandText = query;
  279. object result = DoQueryScalar(cmd);
  280. return Convert.ToInt64(result);
  281. }
  282. }
  283. public object DoQueryScalar(MySqlCommand cmd)
  284. {
  285. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  286. {
  287. dbcon.Open();
  288. cmd.Connection = dbcon;
  289. return cmd.ExecuteScalar();
  290. }
  291. }
  292. }
  293. }