1
0

MySQLRegionData.cs 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418
  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 System.Reflection;
  32. using MySql.Data.MySqlClient;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. using OpenSim.Data;
  36. using RegionFlags = OpenSim.Framework.RegionFlags;
  37. namespace OpenSim.Data.MySQL
  38. {
  39. public class MySqlRegionData : MySqlFramework, IRegionData
  40. {
  41. private string m_Realm;
  42. private List<string> m_ColumnNames;
  43. //private string m_connectionString;
  44. protected virtual Assembly Assembly
  45. {
  46. get { return GetType().Assembly; }
  47. }
  48. public MySqlRegionData(string connectionString, string realm)
  49. : base(connectionString)
  50. {
  51. m_Realm = realm;
  52. m_connectionString = connectionString;
  53. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  54. {
  55. dbcon.Open();
  56. Migration m = new Migration(dbcon, Assembly, "GridStore");
  57. m.Update();
  58. }
  59. }
  60. public List<RegionData> Get(string regionName, UUID scopeID)
  61. {
  62. string command = "select * from `"+m_Realm+"` where regionName like ?regionName";
  63. if (scopeID != UUID.Zero)
  64. command += " and ScopeID = ?scopeID";
  65. command += " order by regionName";
  66. using (MySqlCommand cmd = new MySqlCommand(command))
  67. {
  68. cmd.Parameters.AddWithValue("?regionName", regionName);
  69. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  70. return RunCommand(cmd);
  71. }
  72. }
  73. public RegionData Get(int posX, int posY, UUID scopeID)
  74. {
  75. /* fixed size regions
  76. string command = "select * from `"+m_Realm+"` where locX = ?posX and locY = ?posY";
  77. if (scopeID != UUID.Zero)
  78. command += " and ScopeID = ?scopeID";
  79. using (MySqlCommand cmd = new MySqlCommand(command))
  80. {
  81. cmd.Parameters.AddWithValue("?posX", posX.ToString());
  82. cmd.Parameters.AddWithValue("?posY", posY.ToString());
  83. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  84. List<RegionData> ret = RunCommand(cmd);
  85. if (ret.Count == 0)
  86. return null;
  87. return ret[0];
  88. }
  89. */
  90. // extend database search for maximum region size area
  91. string command = "select * from `" + m_Realm + "` where locX between ?startX and ?endX and locY between ?startY and ?endY";
  92. if (scopeID != UUID.Zero)
  93. command += " and ScopeID = ?scopeID";
  94. int startX = posX - (int)Constants.MaximumRegionSize;
  95. int startY = posY - (int)Constants.MaximumRegionSize;
  96. int endX = posX;
  97. int endY = posY;
  98. List<RegionData> ret;
  99. using (MySqlCommand cmd = new MySqlCommand(command))
  100. {
  101. cmd.Parameters.AddWithValue("?startX", startX.ToString());
  102. cmd.Parameters.AddWithValue("?startY", startY.ToString());
  103. cmd.Parameters.AddWithValue("?endX", endX.ToString());
  104. cmd.Parameters.AddWithValue("?endY", endY.ToString());
  105. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  106. ret = RunCommand(cmd);
  107. }
  108. if (ret.Count == 0)
  109. return null;
  110. // find the first that contains pos
  111. RegionData rg = null;
  112. foreach (RegionData r in ret)
  113. {
  114. if (posX >= r.posX && posX < r.posX + r.sizeX
  115. && posY >= r.posY && posY < r.posY + r.sizeY)
  116. {
  117. rg = r;
  118. break;
  119. }
  120. }
  121. return rg;
  122. }
  123. public RegionData Get(UUID regionID, UUID scopeID)
  124. {
  125. string command = "select * from `"+m_Realm+"` where uuid = ?regionID";
  126. if (scopeID != UUID.Zero)
  127. command += " and ScopeID = ?scopeID";
  128. using (MySqlCommand cmd = new MySqlCommand(command))
  129. {
  130. cmd.Parameters.AddWithValue("?regionID", regionID.ToString());
  131. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  132. List<RegionData> ret = RunCommand(cmd);
  133. if (ret.Count == 0)
  134. return null;
  135. return ret[0];
  136. }
  137. }
  138. public List<RegionData> Get(int startX, int startY, int endX, int endY, UUID scopeID)
  139. {
  140. /* fix size regions
  141. string command = "select * from `"+m_Realm+"` where locX between ?startX and ?endX and locY between ?startY and ?endY";
  142. if (scopeID != UUID.Zero)
  143. command += " and ScopeID = ?scopeID";
  144. using (MySqlCommand cmd = new MySqlCommand(command))
  145. {
  146. cmd.Parameters.AddWithValue("?startX", startX.ToString());
  147. cmd.Parameters.AddWithValue("?startY", startY.ToString());
  148. cmd.Parameters.AddWithValue("?endX", endX.ToString());
  149. cmd.Parameters.AddWithValue("?endY", endY.ToString());
  150. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  151. return RunCommand(cmd);
  152. }
  153. */
  154. string command = "select * from `" + m_Realm + "` where locX between ?startX and ?endX and locY between ?startY and ?endY";
  155. if (scopeID != UUID.Zero)
  156. command += " and ScopeID = ?scopeID";
  157. int qstartX = startX - (int)Constants.MaximumRegionSize;
  158. int qstartY = startY - (int)Constants.MaximumRegionSize;
  159. List<RegionData> dbret;
  160. using (MySqlCommand cmd = new MySqlCommand(command))
  161. {
  162. cmd.Parameters.AddWithValue("?startX", qstartX.ToString());
  163. cmd.Parameters.AddWithValue("?startY", qstartY.ToString());
  164. cmd.Parameters.AddWithValue("?endX", endX.ToString());
  165. cmd.Parameters.AddWithValue("?endY", endY.ToString());
  166. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  167. dbret = RunCommand(cmd);
  168. }
  169. List<RegionData> ret = new List<RegionData>();
  170. if (dbret.Count == 0)
  171. return ret;
  172. foreach (RegionData r in dbret)
  173. {
  174. if (r.posX + r.sizeX > startX && r.posX <= endX
  175. && r.posY + r.sizeX > startY && r.posY <= endY)
  176. ret.Add(r);
  177. }
  178. return ret;
  179. }
  180. public List<RegionData> RunCommand(MySqlCommand cmd)
  181. {
  182. List<RegionData> retList = new List<RegionData>();
  183. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  184. {
  185. dbcon.Open();
  186. cmd.Connection = dbcon;
  187. using (IDataReader result = cmd.ExecuteReader())
  188. {
  189. while (result.Read())
  190. {
  191. RegionData ret = new RegionData();
  192. ret.Data = new Dictionary<string, object>();
  193. ret.RegionID = DBGuid.FromDB(result["uuid"]);
  194. ret.ScopeID = DBGuid.FromDB(result["ScopeID"]);
  195. ret.RegionName = result["regionName"].ToString();
  196. ret.posX = Convert.ToInt32(result["locX"]);
  197. ret.posY = Convert.ToInt32(result["locY"]);
  198. ret.sizeX = Convert.ToInt32(result["sizeX"]);
  199. ret.sizeY = Convert.ToInt32(result["sizeY"]);
  200. CheckColumnNames(result);
  201. foreach (string s in m_ColumnNames)
  202. {
  203. if (s == "uuid")
  204. continue;
  205. if (s == "ScopeID")
  206. continue;
  207. if (s == "regionName")
  208. continue;
  209. if (s == "locX")
  210. continue;
  211. if (s == "locY")
  212. continue;
  213. object value = result[s];
  214. if (value is DBNull)
  215. ret.Data[s] = null;
  216. else
  217. ret.Data[s] = result[s].ToString();
  218. }
  219. retList.Add(ret);
  220. }
  221. }
  222. }
  223. return retList;
  224. }
  225. private void CheckColumnNames(IDataReader result)
  226. {
  227. if (m_ColumnNames != null)
  228. return;
  229. List<string> columnNames = new List<string>();
  230. DataTable schemaTable = result.GetSchemaTable();
  231. foreach (DataRow row in schemaTable.Rows)
  232. {
  233. if (row["ColumnName"] != null)
  234. columnNames.Add(row["ColumnName"].ToString());
  235. }
  236. m_ColumnNames = columnNames;
  237. }
  238. public bool Store(RegionData data)
  239. {
  240. if (data.Data.ContainsKey("uuid"))
  241. data.Data.Remove("uuid");
  242. if (data.Data.ContainsKey("ScopeID"))
  243. data.Data.Remove("ScopeID");
  244. if (data.Data.ContainsKey("regionName"))
  245. data.Data.Remove("regionName");
  246. if (data.Data.ContainsKey("posX"))
  247. data.Data.Remove("posX");
  248. if (data.Data.ContainsKey("posY"))
  249. data.Data.Remove("posY");
  250. if (data.Data.ContainsKey("sizeX"))
  251. data.Data.Remove("sizeX");
  252. if (data.Data.ContainsKey("sizeY"))
  253. data.Data.Remove("sizeY");
  254. if (data.Data.ContainsKey("locX"))
  255. data.Data.Remove("locX");
  256. if (data.Data.ContainsKey("locY"))
  257. data.Data.Remove("locY");
  258. if (data.RegionName.Length > 128)
  259. data.RegionName = data.RegionName.Substring(0, 128);
  260. string[] fields = new List<string>(data.Data.Keys).ToArray();
  261. using (MySqlCommand cmd = new MySqlCommand())
  262. {
  263. string update = "update `" + m_Realm + "` set locX=?posX, locY=?posY, sizeX=?sizeX, sizeY=?sizeY";
  264. foreach (string field in fields)
  265. {
  266. update += ", ";
  267. update += "`" + field + "` = ?" + field;
  268. cmd.Parameters.AddWithValue("?" + field, data.Data[field]);
  269. }
  270. update += " where uuid = ?regionID";
  271. if (data.ScopeID != UUID.Zero)
  272. update += " and ScopeID = ?scopeID";
  273. cmd.CommandText = update;
  274. cmd.Parameters.AddWithValue("?regionID", data.RegionID.ToString());
  275. cmd.Parameters.AddWithValue("?regionName", data.RegionName);
  276. cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString());
  277. cmd.Parameters.AddWithValue("?posX", data.posX.ToString());
  278. cmd.Parameters.AddWithValue("?posY", data.posY.ToString());
  279. cmd.Parameters.AddWithValue("?sizeX", data.sizeX.ToString());
  280. cmd.Parameters.AddWithValue("?sizeY", data.sizeY.ToString());
  281. if (ExecuteNonQuery(cmd) < 1)
  282. {
  283. string insert = "insert into `" + m_Realm + "` (`uuid`, `ScopeID`, `locX`, `locY`, `sizeX`, `sizeY`, `regionName`, `" +
  284. String.Join("`, `", fields) +
  285. "`) values ( ?regionID, ?scopeID, ?posX, ?posY, ?sizeX, ?sizeY, ?regionName, ?" + String.Join(", ?", fields) + ")";
  286. cmd.CommandText = insert;
  287. if (ExecuteNonQuery(cmd) < 1)
  288. {
  289. return false;
  290. }
  291. }
  292. }
  293. return true;
  294. }
  295. public bool SetDataItem(UUID regionID, string item, string value)
  296. {
  297. using (MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + "` set `" + item + "` = ?" + item + " where uuid = ?UUID"))
  298. {
  299. cmd.Parameters.AddWithValue("?" + item, value);
  300. cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
  301. if (ExecuteNonQuery(cmd) > 0)
  302. return true;
  303. }
  304. return false;
  305. }
  306. public bool Delete(UUID regionID)
  307. {
  308. using (MySqlCommand cmd = new MySqlCommand("delete from `" + m_Realm + "` where uuid = ?UUID"))
  309. {
  310. cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
  311. if (ExecuteNonQuery(cmd) > 0)
  312. return true;
  313. }
  314. return false;
  315. }
  316. public List<RegionData> GetDefaultRegions(UUID scopeID)
  317. {
  318. return Get((int)RegionFlags.DefaultRegion, scopeID);
  319. }
  320. public List<RegionData> GetDefaultHypergridRegions(UUID scopeID)
  321. {
  322. return Get((int)RegionFlags.DefaultHGRegion, scopeID);
  323. }
  324. public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y)
  325. {
  326. List<RegionData> regions = Get((int)RegionFlags.FallbackRegion, scopeID);
  327. RegionDataDistanceCompare distanceComparer = new RegionDataDistanceCompare(x, y);
  328. regions.Sort(distanceComparer);
  329. return regions;
  330. }
  331. public List<RegionData> GetHyperlinks(UUID scopeID)
  332. {
  333. return Get((int)RegionFlags.Hyperlink, scopeID);
  334. }
  335. private List<RegionData> Get(int regionFlags, UUID scopeID)
  336. {
  337. string command = "select * from `" + m_Realm + "` where (flags & " + regionFlags.ToString() + ") <> 0";
  338. if (scopeID != UUID.Zero)
  339. command += " and ScopeID = ?scopeID";
  340. using (MySqlCommand cmd = new MySqlCommand(command))
  341. {
  342. cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
  343. return RunCommand(cmd);
  344. }
  345. }
  346. }
  347. }