1
0

MySQLRegionData.cs 16 KB

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