SQLiteGenericTableHandler.cs 8.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253
  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 Mono.Data.SqliteClient;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. using OpenSim.Region.Framework.Interfaces;
  36. namespace OpenSim.Data.SQLite
  37. {
  38. public class SQLiteGenericTableHandler<T> : SQLiteFramework where T: class, new()
  39. {
  40. private static readonly ILog m_log =
  41. LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  42. protected Dictionary<string, FieldInfo> m_Fields =
  43. new Dictionary<string, FieldInfo>();
  44. protected List<string> m_ColumnNames = null;
  45. protected string m_Realm;
  46. protected FieldInfo m_DataField = null;
  47. public SQLiteGenericTableHandler(string connectionString,
  48. string realm, string storeName) : base(connectionString)
  49. {
  50. m_Realm = realm;
  51. if (storeName != String.Empty)
  52. {
  53. Assembly assem = GetType().Assembly;
  54. Migration m = new Migration(m_Connection, assem, storeName);
  55. m.Update();
  56. }
  57. Type t = typeof(T);
  58. FieldInfo[] fields = t.GetFields(BindingFlags.Public |
  59. BindingFlags.Instance |
  60. BindingFlags.DeclaredOnly);
  61. if (fields.Length == 0)
  62. return;
  63. foreach (FieldInfo f in fields)
  64. {
  65. if (f.Name != "Data")
  66. m_Fields[f.Name] = f;
  67. else
  68. m_DataField = f;
  69. }
  70. }
  71. private void CheckColumnNames(IDataReader reader)
  72. {
  73. if (m_ColumnNames != null)
  74. return;
  75. m_ColumnNames = new List<string>();
  76. DataTable schemaTable = reader.GetSchemaTable();
  77. foreach (DataRow row in schemaTable.Rows)
  78. {
  79. if (row["ColumnName"] != null &&
  80. (!m_Fields.ContainsKey(row["ColumnName"].ToString())))
  81. m_ColumnNames.Add(row["ColumnName"].ToString());
  82. }
  83. }
  84. public T[] Get(string field, string key)
  85. {
  86. return Get(new string[] { field }, new string[] { key });
  87. }
  88. public T[] Get(string[] fields, string[] keys)
  89. {
  90. if (fields.Length != keys.Length)
  91. return new T[0];
  92. List<string> terms = new List<string>();
  93. SqliteCommand cmd = new SqliteCommand();
  94. for (int i = 0 ; i < fields.Length ; i++)
  95. {
  96. cmd.Parameters.Add(new SqliteParameter(":" + fields[i], keys[i]));
  97. terms.Add("`" + fields[i] + "` = :" + fields[i]);
  98. }
  99. string where = String.Join(" and ", terms.ToArray());
  100. string query = String.Format("select * from {0} where {1}",
  101. m_Realm, where);
  102. cmd.CommandText = query;
  103. return DoQuery(cmd);
  104. }
  105. protected T[] DoQuery(SqliteCommand cmd)
  106. {
  107. IDataReader reader = ExecuteReader(cmd);
  108. if (reader == null)
  109. return new T[0];
  110. CheckColumnNames(reader);
  111. List<T> result = new List<T>();
  112. while (reader.Read())
  113. {
  114. T row = new T();
  115. foreach (string name in m_Fields.Keys)
  116. {
  117. if (m_Fields[name].GetValue(row) is bool)
  118. {
  119. int v = Convert.ToInt32(reader[name]);
  120. m_Fields[name].SetValue(row, v != 0 ? true : false);
  121. }
  122. else if (m_Fields[name].GetValue(row) is UUID)
  123. {
  124. UUID uuid = UUID.Zero;
  125. UUID.TryParse(reader[name].ToString(), out uuid);
  126. m_Fields[name].SetValue(row, uuid);
  127. }
  128. else if (m_Fields[name].GetValue(row) is int)
  129. {
  130. int v = Convert.ToInt32(reader[name]);
  131. m_Fields[name].SetValue(row, v);
  132. }
  133. else
  134. {
  135. m_Fields[name].SetValue(row, reader[name]);
  136. }
  137. }
  138. if (m_DataField != null)
  139. {
  140. Dictionary<string, string> data =
  141. new Dictionary<string, string>();
  142. foreach (string col in m_ColumnNames)
  143. {
  144. data[col] = reader[col].ToString();
  145. if (data[col] == null)
  146. data[col] = String.Empty;
  147. }
  148. m_DataField.SetValue(row, data);
  149. }
  150. result.Add(row);
  151. }
  152. CloseReaderCommand(cmd);
  153. return result.ToArray();
  154. }
  155. public T[] Get(string where)
  156. {
  157. SqliteCommand cmd = new SqliteCommand();
  158. string query = String.Format("select * from {0} where {1}",
  159. m_Realm, where);
  160. cmd.CommandText = query;
  161. return DoQuery(cmd);
  162. }
  163. public bool Store(T row)
  164. {
  165. SqliteCommand cmd = new SqliteCommand();
  166. string query = "";
  167. List<String> names = new List<String>();
  168. List<String> values = new List<String>();
  169. foreach (FieldInfo fi in m_Fields.Values)
  170. {
  171. names.Add(fi.Name);
  172. values.Add(":" + fi.Name);
  173. cmd.Parameters.Add(new SqliteParameter(":" + fi.Name, fi.GetValue(row).ToString()));
  174. }
  175. if (m_DataField != null)
  176. {
  177. Dictionary<string, string> data =
  178. (Dictionary<string, string>)m_DataField.GetValue(row);
  179. foreach (KeyValuePair<string, string> kvp in data)
  180. {
  181. names.Add(kvp.Key);
  182. values.Add(":" + kvp.Key);
  183. cmd.Parameters.Add(new SqliteParameter(":" + kvp.Key, kvp.Value));
  184. }
  185. }
  186. query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values (" + String.Join(",", values.ToArray()) + ")";
  187. cmd.CommandText = query;
  188. if (ExecuteNonQuery(cmd) > 0)
  189. return true;
  190. return false;
  191. }
  192. public bool Delete(string field, string val)
  193. {
  194. SqliteCommand cmd = new SqliteCommand();
  195. cmd.CommandText = String.Format("delete from {0} where `{1}` = :{1}", m_Realm, field);
  196. cmd.Parameters.Add(new SqliteParameter(field, val));
  197. if (ExecuteNonQuery(cmd) > 0)
  198. return true;
  199. return false;
  200. }
  201. }
  202. }