1
0

SQLiteEstateData.cs 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340
  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 SQLiteEstateStore : IEstateDataStore
  39. {
  40. private static readonly ILog m_log =
  41. LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  42. private SqliteConnection m_connection;
  43. private string m_connectionString;
  44. private FieldInfo[] m_Fields;
  45. private Dictionary<string, FieldInfo> m_FieldMap =
  46. new Dictionary<string, FieldInfo>();
  47. public void Initialise(string connectionString)
  48. {
  49. m_connectionString = connectionString;
  50. m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString);
  51. m_connection = new SqliteConnection(m_connectionString);
  52. m_connection.Open();
  53. Assembly assem = GetType().Assembly;
  54. Migration m = new Migration(m_connection, assem, "EstateStore");
  55. m.Update();
  56. m_connection.Close();
  57. m_connection.Open();
  58. Type t = typeof(EstateSettings);
  59. m_Fields = t.GetFields(BindingFlags.NonPublic |
  60. BindingFlags.Instance |
  61. BindingFlags.DeclaredOnly);
  62. foreach (FieldInfo f in m_Fields)
  63. if (f.Name.Substring(0, 2) == "m_")
  64. m_FieldMap[f.Name.Substring(2)] = f;
  65. }
  66. private string[] FieldList
  67. {
  68. get { return new List<string>(m_FieldMap.Keys).ToArray(); }
  69. }
  70. public EstateSettings LoadEstateSettings(UUID regionID)
  71. {
  72. EstateSettings es = new EstateSettings();
  73. es.OnSave += StoreEstateSettings;
  74. string sql = "select estate_settings."+String.Join(",estate_settings.", FieldList)+" from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = :RegionID";
  75. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  76. cmd.CommandText = sql;
  77. cmd.Parameters.Add(":RegionID", regionID.ToString());
  78. IDataReader r = cmd.ExecuteReader();
  79. if (r.Read())
  80. {
  81. foreach (string name in FieldList)
  82. {
  83. if (m_FieldMap[name].GetValue(es) is bool)
  84. {
  85. int v = Convert.ToInt32(r[name]);
  86. if (v != 0)
  87. m_FieldMap[name].SetValue(es, true);
  88. else
  89. m_FieldMap[name].SetValue(es, false);
  90. }
  91. else if (m_FieldMap[name].GetValue(es) is UUID)
  92. {
  93. UUID uuid = UUID.Zero;
  94. UUID.TryParse(r[name].ToString(), out uuid);
  95. m_FieldMap[name].SetValue(es, uuid);
  96. }
  97. else
  98. {
  99. m_FieldMap[name].SetValue(es, Convert.ChangeType(r[name], m_FieldMap[name].FieldType));
  100. }
  101. }
  102. r.Close();
  103. }
  104. else
  105. {
  106. // Migration case
  107. //
  108. r.Close();
  109. List<string> names = new List<string>(FieldList);
  110. names.Remove("EstateID");
  111. sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( :"+String.Join(", :", names.ToArray())+")";
  112. cmd.CommandText = sql;
  113. cmd.Parameters.Clear();
  114. foreach (string name in FieldList)
  115. {
  116. if (m_FieldMap[name].GetValue(es) is bool)
  117. {
  118. if ((bool)m_FieldMap[name].GetValue(es))
  119. cmd.Parameters.Add(":"+name, "1");
  120. else
  121. cmd.Parameters.Add(":"+name, "0");
  122. }
  123. else
  124. {
  125. cmd.Parameters.Add(":"+name, m_FieldMap[name].GetValue(es).ToString());
  126. }
  127. }
  128. cmd.ExecuteNonQuery();
  129. cmd.CommandText = "select LAST_INSERT_ROWID() as id";
  130. cmd.Parameters.Clear();
  131. r = cmd.ExecuteReader();
  132. r.Read();
  133. es.EstateID = Convert.ToUInt32(r["id"]);
  134. r.Close();
  135. cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
  136. cmd.Parameters.Add(":RegionID", regionID.ToString());
  137. cmd.Parameters.Add(":EstateID", es.EstateID.ToString());
  138. // This will throw on dupe key
  139. try
  140. {
  141. cmd.ExecuteNonQuery();
  142. }
  143. catch (Exception)
  144. {
  145. }
  146. // Munge and transfer the ban list
  147. //
  148. cmd.Parameters.Clear();
  149. cmd.CommandText = "insert into estateban select "+es.EstateID.ToString()+", bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = :UUID";
  150. cmd.Parameters.Add(":UUID", regionID.ToString());
  151. try
  152. {
  153. cmd.ExecuteNonQuery();
  154. }
  155. catch (Exception)
  156. {
  157. }
  158. es.Save();
  159. }
  160. LoadBanList(es);
  161. es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
  162. es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
  163. es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
  164. return es;
  165. }
  166. public void StoreEstateSettings(EstateSettings es)
  167. {
  168. List<string> fields = new List<string>(FieldList);
  169. fields.Remove("EstateID");
  170. List<string> terms = new List<string>();
  171. foreach (string f in fields)
  172. terms.Add(f+" = :"+f);
  173. string sql = "update estate_settings set "+String.Join(", ", terms.ToArray())+" where EstateID = :EstateID";
  174. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  175. cmd.CommandText = sql;
  176. foreach (string name in FieldList)
  177. {
  178. if (m_FieldMap[name].GetValue(es) is bool)
  179. {
  180. if ((bool)m_FieldMap[name].GetValue(es))
  181. cmd.Parameters.Add(":"+name, "1");
  182. else
  183. cmd.Parameters.Add(":"+name, "0");
  184. }
  185. else
  186. {
  187. cmd.Parameters.Add(":"+name, m_FieldMap[name].GetValue(es).ToString());
  188. }
  189. }
  190. cmd.ExecuteNonQuery();
  191. SaveBanList(es);
  192. SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
  193. SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
  194. SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
  195. }
  196. private void LoadBanList(EstateSettings es)
  197. {
  198. es.ClearBans();
  199. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  200. cmd.CommandText = "select bannedUUID from estateban where EstateID = :EstateID";
  201. cmd.Parameters.Add(":EstateID", es.EstateID);
  202. IDataReader r = cmd.ExecuteReader();
  203. while (r.Read())
  204. {
  205. EstateBan eb = new EstateBan();
  206. UUID uuid = new UUID();
  207. UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
  208. eb.BannedUserID = uuid;
  209. eb.BannedHostAddress = "0.0.0.0";
  210. eb.BannedHostIPMask = "0.0.0.0";
  211. es.AddBan(eb);
  212. }
  213. r.Close();
  214. }
  215. private void SaveBanList(EstateSettings es)
  216. {
  217. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  218. cmd.CommandText = "delete from estateban where EstateID = :EstateID";
  219. cmd.Parameters.Add(":EstateID", es.EstateID.ToString());
  220. cmd.ExecuteNonQuery();
  221. cmd.Parameters.Clear();
  222. cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( :EstateID, :bannedUUID, '', '', '' )";
  223. foreach (EstateBan b in es.EstateBans)
  224. {
  225. cmd.Parameters.Add(":EstateID", es.EstateID.ToString());
  226. cmd.Parameters.Add(":bannedUUID", b.BannedUserID.ToString());
  227. cmd.ExecuteNonQuery();
  228. cmd.Parameters.Clear();
  229. }
  230. }
  231. void SaveUUIDList(uint EstateID, string table, UUID[] data)
  232. {
  233. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  234. cmd.CommandText = "delete from "+table+" where EstateID = :EstateID";
  235. cmd.Parameters.Add(":EstateID", EstateID.ToString());
  236. cmd.ExecuteNonQuery();
  237. cmd.Parameters.Clear();
  238. cmd.CommandText = "insert into "+table+" (EstateID, uuid) values ( :EstateID, :uuid )";
  239. foreach (UUID uuid in data)
  240. {
  241. cmd.Parameters.Add(":EstateID", EstateID.ToString());
  242. cmd.Parameters.Add(":uuid", uuid.ToString());
  243. cmd.ExecuteNonQuery();
  244. cmd.Parameters.Clear();
  245. }
  246. }
  247. UUID[] LoadUUIDList(uint EstateID, string table)
  248. {
  249. List<UUID> uuids = new List<UUID>();
  250. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  251. cmd.CommandText = "select uuid from "+table+" where EstateID = :EstateID";
  252. cmd.Parameters.Add(":EstateID", EstateID);
  253. IDataReader r = cmd.ExecuteReader();
  254. while (r.Read())
  255. {
  256. // EstateBan eb = new EstateBan();
  257. UUID uuid = new UUID();
  258. UUID.TryParse(r["uuid"].ToString(), out uuid);
  259. uuids.Add(uuid);
  260. }
  261. r.Close();
  262. return uuids.ToArray();
  263. }
  264. }
  265. }