SQLiteEstateData.cs 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387
  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.SQLiteLegacy
  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, bool create)
  71. {
  72. 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";
  73. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  74. cmd.CommandText = sql;
  75. cmd.Parameters.Add(":RegionID", regionID.ToString());
  76. return DoLoad(cmd, regionID, create);
  77. }
  78. private EstateSettings DoLoad(SqliteCommand cmd, UUID regionID, bool create)
  79. {
  80. EstateSettings es = new EstateSettings();
  81. es.OnSave += StoreEstateSettings;
  82. IDataReader r = cmd.ExecuteReader();
  83. if (r.Read())
  84. {
  85. foreach (string name in FieldList)
  86. {
  87. if (m_FieldMap[name].GetValue(es) is bool)
  88. {
  89. int v = Convert.ToInt32(r[name]);
  90. if (v != 0)
  91. m_FieldMap[name].SetValue(es, true);
  92. else
  93. m_FieldMap[name].SetValue(es, false);
  94. }
  95. else if (m_FieldMap[name].GetValue(es) is UUID)
  96. {
  97. UUID uuid = UUID.Zero;
  98. UUID.TryParse(r[name].ToString(), out uuid);
  99. m_FieldMap[name].SetValue(es, uuid);
  100. }
  101. else
  102. {
  103. m_FieldMap[name].SetValue(es, Convert.ChangeType(r[name], m_FieldMap[name].FieldType));
  104. }
  105. }
  106. r.Close();
  107. }
  108. else if (create)
  109. {
  110. r.Close();
  111. List<string> names = new List<string>(FieldList);
  112. names.Remove("EstateID");
  113. string sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( :"+String.Join(", :", names.ToArray())+")";
  114. cmd.CommandText = sql;
  115. cmd.Parameters.Clear();
  116. foreach (string name in FieldList)
  117. {
  118. if (m_FieldMap[name].GetValue(es) is bool)
  119. {
  120. if ((bool)m_FieldMap[name].GetValue(es))
  121. cmd.Parameters.Add(":"+name, "1");
  122. else
  123. cmd.Parameters.Add(":"+name, "0");
  124. }
  125. else
  126. {
  127. cmd.Parameters.Add(":"+name, m_FieldMap[name].GetValue(es).ToString());
  128. }
  129. }
  130. cmd.ExecuteNonQuery();
  131. cmd.CommandText = "select LAST_INSERT_ROWID() as id";
  132. cmd.Parameters.Clear();
  133. r = cmd.ExecuteReader();
  134. r.Read();
  135. es.EstateID = Convert.ToUInt32(r["id"]);
  136. r.Close();
  137. cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
  138. cmd.Parameters.Add(":RegionID", regionID.ToString());
  139. cmd.Parameters.Add(":EstateID", es.EstateID.ToString());
  140. // This will throw on dupe key
  141. try
  142. {
  143. cmd.ExecuteNonQuery();
  144. }
  145. catch (Exception)
  146. {
  147. }
  148. es.Save();
  149. }
  150. LoadBanList(es);
  151. es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
  152. es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
  153. es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
  154. return es;
  155. }
  156. public void StoreEstateSettings(EstateSettings es)
  157. {
  158. List<string> fields = new List<string>(FieldList);
  159. fields.Remove("EstateID");
  160. List<string> terms = new List<string>();
  161. foreach (string f in fields)
  162. terms.Add(f+" = :"+f);
  163. string sql = "update estate_settings set "+String.Join(", ", terms.ToArray())+" where EstateID = :EstateID";
  164. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  165. cmd.CommandText = sql;
  166. foreach (string name in FieldList)
  167. {
  168. if (m_FieldMap[name].GetValue(es) is bool)
  169. {
  170. if ((bool)m_FieldMap[name].GetValue(es))
  171. cmd.Parameters.Add(":"+name, "1");
  172. else
  173. cmd.Parameters.Add(":"+name, "0");
  174. }
  175. else
  176. {
  177. cmd.Parameters.Add(":"+name, m_FieldMap[name].GetValue(es).ToString());
  178. }
  179. }
  180. cmd.ExecuteNonQuery();
  181. SaveBanList(es);
  182. SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
  183. SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
  184. SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
  185. }
  186. private void LoadBanList(EstateSettings es)
  187. {
  188. es.ClearBans();
  189. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  190. cmd.CommandText = "select bannedUUID from estateban where EstateID = :EstateID";
  191. cmd.Parameters.Add(":EstateID", es.EstateID);
  192. IDataReader r = cmd.ExecuteReader();
  193. while (r.Read())
  194. {
  195. EstateBan eb = new EstateBan();
  196. UUID uuid = new UUID();
  197. UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
  198. eb.BannedUserID = uuid;
  199. eb.BannedHostAddress = "0.0.0.0";
  200. eb.BannedHostIPMask = "0.0.0.0";
  201. es.AddBan(eb);
  202. }
  203. r.Close();
  204. }
  205. private void SaveBanList(EstateSettings es)
  206. {
  207. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  208. cmd.CommandText = "delete from estateban where EstateID = :EstateID";
  209. cmd.Parameters.Add(":EstateID", es.EstateID.ToString());
  210. cmd.ExecuteNonQuery();
  211. cmd.Parameters.Clear();
  212. cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( :EstateID, :bannedUUID, '', '', '' )";
  213. foreach (EstateBan b in es.EstateBans)
  214. {
  215. cmd.Parameters.Add(":EstateID", es.EstateID.ToString());
  216. cmd.Parameters.Add(":bannedUUID", b.BannedUserID.ToString());
  217. cmd.ExecuteNonQuery();
  218. cmd.Parameters.Clear();
  219. }
  220. }
  221. void SaveUUIDList(uint EstateID, string table, UUID[] data)
  222. {
  223. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  224. cmd.CommandText = "delete from "+table+" where EstateID = :EstateID";
  225. cmd.Parameters.Add(":EstateID", EstateID.ToString());
  226. cmd.ExecuteNonQuery();
  227. cmd.Parameters.Clear();
  228. cmd.CommandText = "insert into "+table+" (EstateID, uuid) values ( :EstateID, :uuid )";
  229. foreach (UUID uuid in data)
  230. {
  231. cmd.Parameters.Add(":EstateID", EstateID.ToString());
  232. cmd.Parameters.Add(":uuid", uuid.ToString());
  233. cmd.ExecuteNonQuery();
  234. cmd.Parameters.Clear();
  235. }
  236. }
  237. UUID[] LoadUUIDList(uint EstateID, string table)
  238. {
  239. List<UUID> uuids = new List<UUID>();
  240. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  241. cmd.CommandText = "select uuid from "+table+" where EstateID = :EstateID";
  242. cmd.Parameters.Add(":EstateID", EstateID);
  243. IDataReader r = cmd.ExecuteReader();
  244. while (r.Read())
  245. {
  246. // EstateBan eb = new EstateBan();
  247. UUID uuid = new UUID();
  248. UUID.TryParse(r["uuid"].ToString(), out uuid);
  249. uuids.Add(uuid);
  250. }
  251. r.Close();
  252. return uuids.ToArray();
  253. }
  254. public EstateSettings LoadEstateSettings(int estateID)
  255. {
  256. string sql = "select estate_settings."+String.Join(",estate_settings.", FieldList)+" from estate_settings where estate_settings.EstateID = :EstateID";
  257. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  258. cmd.CommandText = sql;
  259. cmd.Parameters.Add(":EstateID", estateID.ToString());
  260. return DoLoad(cmd, UUID.Zero, false);
  261. }
  262. public List<int> GetEstates(string search)
  263. {
  264. List<int> result = new List<int>();
  265. string sql = "select EstateID from estate_settings where estate_settings.EstateName = :EstateName";
  266. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  267. cmd.CommandText = sql;
  268. cmd.Parameters.Add(":EstateName", search);
  269. IDataReader r = cmd.ExecuteReader();
  270. while (r.Read())
  271. {
  272. result.Add(Convert.ToInt32(r["EstateID"]));
  273. }
  274. r.Close();
  275. return result;
  276. }
  277. public bool LinkRegion(UUID regionID, int estateID)
  278. {
  279. SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
  280. cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
  281. cmd.Parameters.Add(":RegionID", regionID.ToString());
  282. cmd.Parameters.Add(":EstateID", estateID.ToString());
  283. if (cmd.ExecuteNonQuery() == 0)
  284. return false;
  285. return true;
  286. }
  287. public List<UUID> GetRegions(int estateID)
  288. {
  289. return new List<UUID>();
  290. }
  291. public bool DeleteEstate(int estateID)
  292. {
  293. return false;
  294. }
  295. }
  296. }