MySQLRegionData.cs 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319
  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;
  29. using System.Collections.Generic;
  30. using System.Data;
  31. using OpenMetaverse;
  32. using OpenSim.Framework;
  33. using OpenSim.Data;
  34. using MySql.Data.MySqlClient;
  35. namespace OpenSim.Data.MySQL
  36. {
  37. public class MySqlRegionData : MySqlFramework, IRegionData
  38. {
  39. private string m_Realm;
  40. private List<string> m_ColumnNames;
  41. //private string m_connectionString;
  42. public MySqlRegionData(string connectionString, string realm)
  43. : base(connectionString)
  44. {
  45. m_Realm = realm;
  46. m_connectionString = connectionString;
  47. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  48. {
  49. dbcon.Open();
  50. Migration m = new Migration(dbcon, GetType().Assembly, "GridStore");
  51. m.Update();
  52. }
  53. }
  54. public List<RegionData> Get(string regionName, UUID scopeID)
  55. {
  56. string command = "select * from `"+m_Realm+"` where regionName like ?regionName";
  57. if (scopeID != UUID.Zero)
  58. command += " and ScopeID = ?scopeID";
  59. using (MySqlCommand cmd = new MySqlCommand(command))
  60. {
  61. cmd.Parameters.AddWithValue("?regionName", regionName);
  62. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  63. return RunCommand(cmd);
  64. }
  65. }
  66. public RegionData Get(int posX, int posY, UUID scopeID)
  67. {
  68. string command = "select * from `"+m_Realm+"` where locX = ?posX and locY = ?posY";
  69. if (scopeID != UUID.Zero)
  70. command += " and ScopeID = ?scopeID";
  71. using (MySqlCommand cmd = new MySqlCommand(command))
  72. {
  73. cmd.Parameters.AddWithValue("?posX", posX.ToString());
  74. cmd.Parameters.AddWithValue("?posY", posY.ToString());
  75. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  76. List<RegionData> ret = RunCommand(cmd);
  77. if (ret.Count == 0)
  78. return null;
  79. return ret[0];
  80. }
  81. }
  82. public RegionData Get(UUID regionID, UUID scopeID)
  83. {
  84. string command = "select * from `"+m_Realm+"` where uuid = ?regionID";
  85. if (scopeID != UUID.Zero)
  86. command += " and ScopeID = ?scopeID";
  87. using (MySqlCommand cmd = new MySqlCommand(command))
  88. {
  89. cmd.Parameters.AddWithValue("?regionID", regionID.ToString());
  90. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  91. List<RegionData> ret = RunCommand(cmd);
  92. if (ret.Count == 0)
  93. return null;
  94. return ret[0];
  95. }
  96. }
  97. public List<RegionData> Get(int startX, int startY, int endX, int endY, UUID scopeID)
  98. {
  99. string command = "select * from `"+m_Realm+"` where locX between ?startX and ?endX and locY between ?startY and ?endY";
  100. if (scopeID != UUID.Zero)
  101. command += " and ScopeID = ?scopeID";
  102. using (MySqlCommand cmd = new MySqlCommand(command))
  103. {
  104. cmd.Parameters.AddWithValue("?startX", startX.ToString());
  105. cmd.Parameters.AddWithValue("?startY", startY.ToString());
  106. cmd.Parameters.AddWithValue("?endX", endX.ToString());
  107. cmd.Parameters.AddWithValue("?endY", endY.ToString());
  108. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  109. return RunCommand(cmd);
  110. }
  111. }
  112. public List<RegionData> RunCommand(MySqlCommand cmd)
  113. {
  114. List<RegionData> retList = new List<RegionData>();
  115. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  116. {
  117. dbcon.Open();
  118. cmd.Connection = dbcon;
  119. using (IDataReader result = cmd.ExecuteReader())
  120. {
  121. while (result.Read())
  122. {
  123. RegionData ret = new RegionData();
  124. ret.Data = new Dictionary<string, object>();
  125. ret.RegionID = DBGuid.FromDB(result["uuid"]);
  126. ret.ScopeID = DBGuid.FromDB(result["ScopeID"]);
  127. ret.RegionName = result["regionName"].ToString();
  128. ret.posX = Convert.ToInt32(result["locX"]);
  129. ret.posY = Convert.ToInt32(result["locY"]);
  130. ret.sizeX = Convert.ToInt32(result["sizeX"]);
  131. ret.sizeY = Convert.ToInt32(result["sizeY"]);
  132. if (m_ColumnNames == null)
  133. {
  134. m_ColumnNames = new List<string>();
  135. DataTable schemaTable = result.GetSchemaTable();
  136. foreach (DataRow row in schemaTable.Rows)
  137. {
  138. if (row["ColumnName"] != null)
  139. m_ColumnNames.Add(row["ColumnName"].ToString());
  140. }
  141. }
  142. foreach (string s in m_ColumnNames)
  143. {
  144. if (s == "uuid")
  145. continue;
  146. if (s == "ScopeID")
  147. continue;
  148. if (s == "regionName")
  149. continue;
  150. if (s == "locX")
  151. continue;
  152. if (s == "locY")
  153. continue;
  154. ret.Data[s] = result[s].ToString();
  155. }
  156. retList.Add(ret);
  157. }
  158. }
  159. }
  160. return retList;
  161. }
  162. public bool Store(RegionData data)
  163. {
  164. if (data.Data.ContainsKey("uuid"))
  165. data.Data.Remove("uuid");
  166. if (data.Data.ContainsKey("ScopeID"))
  167. data.Data.Remove("ScopeID");
  168. if (data.Data.ContainsKey("regionName"))
  169. data.Data.Remove("regionName");
  170. if (data.Data.ContainsKey("posX"))
  171. data.Data.Remove("posX");
  172. if (data.Data.ContainsKey("posY"))
  173. data.Data.Remove("posY");
  174. if (data.Data.ContainsKey("sizeX"))
  175. data.Data.Remove("sizeX");
  176. if (data.Data.ContainsKey("sizeY"))
  177. data.Data.Remove("sizeY");
  178. if (data.Data.ContainsKey("locX"))
  179. data.Data.Remove("locX");
  180. if (data.Data.ContainsKey("locY"))
  181. data.Data.Remove("locY");
  182. if (data.RegionName.Length > 32)
  183. data.RegionName = data.RegionName.Substring(0, 32);
  184. string[] fields = new List<string>(data.Data.Keys).ToArray();
  185. using (MySqlCommand cmd = new MySqlCommand())
  186. {
  187. string update = "update `" + m_Realm + "` set locX=?posX, locY=?posY, sizeX=?sizeX, sizeY=?sizeY";
  188. foreach (string field in fields)
  189. {
  190. update += ", ";
  191. update += "`" + field + "` = ?" + field;
  192. cmd.Parameters.AddWithValue("?" + field, data.Data[field]);
  193. }
  194. update += " where uuid = ?regionID";
  195. if (data.ScopeID != UUID.Zero)
  196. update += " and ScopeID = ?scopeID";
  197. cmd.CommandText = update;
  198. cmd.Parameters.AddWithValue("?regionID", data.RegionID.ToString());
  199. cmd.Parameters.AddWithValue("?regionName", data.RegionName);
  200. cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString());
  201. cmd.Parameters.AddWithValue("?posX", data.posX.ToString());
  202. cmd.Parameters.AddWithValue("?posY", data.posY.ToString());
  203. cmd.Parameters.AddWithValue("?sizeX", data.sizeX.ToString());
  204. cmd.Parameters.AddWithValue("?sizeY", data.sizeY.ToString());
  205. if (ExecuteNonQuery(cmd) < 1)
  206. {
  207. string insert = "insert into `" + m_Realm + "` (`uuid`, `ScopeID`, `locX`, `locY`, `sizeX`, `sizeY`, `regionName`, `" +
  208. String.Join("`, `", fields) +
  209. "`) values ( ?regionID, ?scopeID, ?posX, ?posY, ?sizeX, ?sizeY, ?regionName, ?" + String.Join(", ?", fields) + ")";
  210. cmd.CommandText = insert;
  211. if (ExecuteNonQuery(cmd) < 1)
  212. {
  213. return false;
  214. }
  215. }
  216. }
  217. return true;
  218. }
  219. public bool SetDataItem(UUID regionID, string item, string value)
  220. {
  221. using (MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + "` set `" + item + "` = ?" + item + " where uuid = ?UUID"))
  222. {
  223. cmd.Parameters.AddWithValue("?" + item, value);
  224. cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
  225. if (ExecuteNonQuery(cmd) > 0)
  226. return true;
  227. }
  228. return false;
  229. }
  230. public bool Delete(UUID regionID)
  231. {
  232. using (MySqlCommand cmd = new MySqlCommand("delete from `" + m_Realm + "` where uuid = ?UUID"))
  233. {
  234. cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
  235. if (ExecuteNonQuery(cmd) > 0)
  236. return true;
  237. }
  238. return false;
  239. }
  240. public List<RegionData> GetDefaultRegions(UUID scopeID)
  241. {
  242. return Get((int)RegionFlags.DefaultRegion, scopeID);
  243. }
  244. public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y)
  245. {
  246. List<RegionData> regions = Get((int)RegionFlags.FallbackRegion, scopeID);
  247. RegionDataDistanceCompare distanceComparer = new RegionDataDistanceCompare(x, y);
  248. regions.Sort(distanceComparer);
  249. return regions;
  250. }
  251. public List<RegionData> GetHyperlinks(UUID scopeID)
  252. {
  253. return Get((int)RegionFlags.Hyperlink, scopeID);
  254. }
  255. private List<RegionData> Get(int regionFlags, UUID scopeID)
  256. {
  257. string command = "select * from `" + m_Realm + "` where (flags & " + regionFlags.ToString() + ") <> 0";
  258. if (scopeID != UUID.Zero)
  259. command += " and ScopeID = ?scopeID";
  260. MySqlCommand cmd = new MySqlCommand(command);
  261. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  262. return RunCommand(cmd);
  263. }
  264. }
  265. }