MSSQLRegionData.cs 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352
  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.Data.SqlClient;
  31. using System.Drawing;
  32. using System.IO;
  33. using System.Reflection;
  34. using log4net;
  35. using OpenMetaverse;
  36. using OpenSim.Framework;
  37. using OpenSim.Region.Framework.Interfaces;
  38. using OpenSim.Region.Framework.Scenes;
  39. using RegionFlags = OpenSim.Framework.RegionFlags;
  40. namespace OpenSim.Data.MSSQL
  41. {
  42. /// <summary>
  43. /// A MSSQL Interface for the Region Server.
  44. /// </summary>
  45. public class MSSQLRegionData : IRegionData
  46. {
  47. private string m_Realm;
  48. private List<string> m_ColumnNames = null;
  49. private string m_ConnectionString;
  50. private MSSQLManager m_database;
  51. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  52. public MSSQLRegionData(string connectionString, string realm)
  53. {
  54. m_Realm = realm;
  55. m_ConnectionString = connectionString;
  56. m_database = new MSSQLManager(connectionString);
  57. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  58. {
  59. conn.Open();
  60. Migration m = new Migration(conn, GetType().Assembly, "GridStore");
  61. m.Update();
  62. }
  63. }
  64. public List<RegionData> Get(string regionName, UUID scopeID)
  65. {
  66. string sql = "select * from ["+m_Realm+"] where regionName like @regionName";
  67. if (scopeID != UUID.Zero)
  68. sql += " and ScopeID = @scopeID";
  69. sql += " order by regionName";
  70. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  71. using (SqlCommand cmd = new SqlCommand(sql, conn))
  72. {
  73. cmd.Parameters.Add(m_database.CreateParameter("@regionName", regionName));
  74. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  75. conn.Open();
  76. return RunCommand(cmd);
  77. }
  78. }
  79. public RegionData Get(int posX, int posY, UUID scopeID)
  80. {
  81. string sql = "select * from ["+m_Realm+"] where locX = @posX and locY = @posY";
  82. if (scopeID != UUID.Zero)
  83. sql += " and ScopeID = @scopeID";
  84. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  85. using (SqlCommand cmd = new SqlCommand(sql, conn))
  86. {
  87. cmd.Parameters.Add(m_database.CreateParameter("@posX", posX.ToString()));
  88. cmd.Parameters.Add(m_database.CreateParameter("@posY", posY.ToString()));
  89. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  90. conn.Open();
  91. List<RegionData> ret = RunCommand(cmd);
  92. if (ret.Count == 0)
  93. return null;
  94. return ret[0];
  95. }
  96. }
  97. public RegionData Get(UUID regionID, UUID scopeID)
  98. {
  99. string sql = "select * from ["+m_Realm+"] where uuid = @regionID";
  100. if (scopeID != UUID.Zero)
  101. sql += " and ScopeID = @scopeID";
  102. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  103. using (SqlCommand cmd = new SqlCommand(sql, conn))
  104. {
  105. cmd.Parameters.Add(m_database.CreateParameter("@regionID", regionID));
  106. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  107. conn.Open();
  108. List<RegionData> ret = RunCommand(cmd);
  109. if (ret.Count == 0)
  110. return null;
  111. return ret[0];
  112. }
  113. }
  114. public List<RegionData> Get(int startX, int startY, int endX, int endY, UUID scopeID)
  115. {
  116. string sql = "select * from ["+m_Realm+"] where locX between @startX and @endX and locY between @startY and @endY";
  117. if (scopeID != UUID.Zero)
  118. sql += " and ScopeID = @scopeID";
  119. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  120. using (SqlCommand cmd = new SqlCommand(sql, conn))
  121. {
  122. cmd.Parameters.Add(m_database.CreateParameter("@startX", startX));
  123. cmd.Parameters.Add(m_database.CreateParameter("@startY", startY));
  124. cmd.Parameters.Add(m_database.CreateParameter("@endX", endX));
  125. cmd.Parameters.Add(m_database.CreateParameter("@endY", endY));
  126. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  127. conn.Open();
  128. return RunCommand(cmd);
  129. }
  130. }
  131. public List<RegionData> RunCommand(SqlCommand cmd)
  132. {
  133. List<RegionData> retList = new List<RegionData>();
  134. SqlDataReader result = cmd.ExecuteReader();
  135. while (result.Read())
  136. {
  137. RegionData ret = new RegionData();
  138. ret.Data = new Dictionary<string, object>();
  139. UUID regionID;
  140. UUID.TryParse(result["uuid"].ToString(), out regionID);
  141. ret.RegionID = regionID;
  142. UUID scope;
  143. UUID.TryParse(result["ScopeID"].ToString(), out scope);
  144. ret.ScopeID = scope;
  145. ret.RegionName = result["regionName"].ToString();
  146. ret.posX = Convert.ToInt32(result["locX"]);
  147. ret.posY = Convert.ToInt32(result["locY"]);
  148. ret.sizeX = Convert.ToInt32(result["sizeX"]);
  149. ret.sizeY = Convert.ToInt32(result["sizeY"]);
  150. if (m_ColumnNames == null)
  151. {
  152. m_ColumnNames = new List<string>();
  153. DataTable schemaTable = result.GetSchemaTable();
  154. foreach (DataRow row in schemaTable.Rows)
  155. m_ColumnNames.Add(row["ColumnName"].ToString());
  156. }
  157. foreach (string s in m_ColumnNames)
  158. {
  159. if (s == "uuid")
  160. continue;
  161. if (s == "ScopeID")
  162. continue;
  163. if (s == "regionName")
  164. continue;
  165. if (s == "locX")
  166. continue;
  167. if (s == "locY")
  168. continue;
  169. ret.Data[s] = result[s].ToString();
  170. }
  171. retList.Add(ret);
  172. }
  173. return retList;
  174. }
  175. public bool Store(RegionData data)
  176. {
  177. if (data.Data.ContainsKey("uuid"))
  178. data.Data.Remove("uuid");
  179. if (data.Data.ContainsKey("ScopeID"))
  180. data.Data.Remove("ScopeID");
  181. if (data.Data.ContainsKey("regionName"))
  182. data.Data.Remove("regionName");
  183. if (data.Data.ContainsKey("posX"))
  184. data.Data.Remove("posX");
  185. if (data.Data.ContainsKey("posY"))
  186. data.Data.Remove("posY");
  187. if (data.Data.ContainsKey("sizeX"))
  188. data.Data.Remove("sizeX");
  189. if (data.Data.ContainsKey("sizeY"))
  190. data.Data.Remove("sizeY");
  191. if (data.Data.ContainsKey("locX"))
  192. data.Data.Remove("locX");
  193. if (data.Data.ContainsKey("locY"))
  194. data.Data.Remove("locY");
  195. string[] fields = new List<string>(data.Data.Keys).ToArray();
  196. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  197. using (SqlCommand cmd = new SqlCommand())
  198. {
  199. string update = "update [" + m_Realm + "] set locX=@posX, locY=@posY, sizeX=@sizeX, sizeY=@sizeY ";
  200. foreach (string field in fields)
  201. {
  202. update += ", ";
  203. update += "[" + field + "] = @" + field;
  204. cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field]));
  205. }
  206. update += " where uuid = @regionID";
  207. if (data.ScopeID != UUID.Zero)
  208. update += " and ScopeID = @scopeID";
  209. cmd.CommandText = update;
  210. cmd.Connection = conn;
  211. cmd.Parameters.Add(m_database.CreateParameter("@regionID", data.RegionID));
  212. cmd.Parameters.Add(m_database.CreateParameter("@regionName", data.RegionName));
  213. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", data.ScopeID));
  214. cmd.Parameters.Add(m_database.CreateParameter("@posX", data.posX));
  215. cmd.Parameters.Add(m_database.CreateParameter("@posY", data.posY));
  216. cmd.Parameters.Add(m_database.CreateParameter("@sizeX", data.sizeX));
  217. cmd.Parameters.Add(m_database.CreateParameter("@sizeY", data.sizeY));
  218. conn.Open();
  219. try
  220. {
  221. if (cmd.ExecuteNonQuery() < 1)
  222. {
  223. string insert = "insert into [" + m_Realm + "] ([uuid], [ScopeID], [locX], [locY], [sizeX], [sizeY], [regionName], [" +
  224. String.Join("], [", fields) +
  225. "]) values (@regionID, @scopeID, @posX, @posY, @sizeX, @sizeY, @regionName, @" + String.Join(", @", fields) + ")";
  226. cmd.CommandText = insert;
  227. try
  228. {
  229. if (cmd.ExecuteNonQuery() < 1)
  230. {
  231. return false;
  232. }
  233. }
  234. catch (Exception ex)
  235. {
  236. m_log.Warn("[MSSQL Grid]: Error inserting into Regions table: " + ex.Message + ", INSERT sql: " + insert);
  237. }
  238. }
  239. }
  240. catch (Exception ex)
  241. {
  242. m_log.Warn("[MSSQL Grid]: Error updating Regions table: " + ex.Message + ", UPDATE sql: " + update);
  243. }
  244. }
  245. return true;
  246. }
  247. public bool SetDataItem(UUID regionID, string item, string value)
  248. {
  249. string sql = "update [" + m_Realm +
  250. "] set [" + item + "] = @" + item + " where uuid = @UUID";
  251. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  252. using (SqlCommand cmd = new SqlCommand(sql, conn))
  253. {
  254. cmd.Parameters.Add(m_database.CreateParameter("@" + item, value));
  255. cmd.Parameters.Add(m_database.CreateParameter("@UUID", regionID));
  256. conn.Open();
  257. if (cmd.ExecuteNonQuery() > 0)
  258. return true;
  259. }
  260. return false;
  261. }
  262. public bool Delete(UUID regionID)
  263. {
  264. string sql = "delete from [" + m_Realm +
  265. "] where uuid = @UUID";
  266. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  267. using (SqlCommand cmd = new SqlCommand(sql, conn))
  268. {
  269. cmd.Parameters.Add(m_database.CreateParameter("@UUID", regionID));
  270. conn.Open();
  271. if (cmd.ExecuteNonQuery() > 0)
  272. return true;
  273. }
  274. return false;
  275. }
  276. public List<RegionData> GetDefaultRegions(UUID scopeID)
  277. {
  278. return Get((int)RegionFlags.DefaultRegion, scopeID);
  279. }
  280. public List<RegionData> GetDefaultHypergridRegions(UUID scopeID)
  281. {
  282. return Get((int)RegionFlags.DefaultHGRegion, scopeID);
  283. }
  284. public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y)
  285. {
  286. List<RegionData> regions = Get((int)RegionFlags.FallbackRegion, scopeID);
  287. RegionDataDistanceCompare distanceComparer = new RegionDataDistanceCompare(x, y);
  288. regions.Sort(distanceComparer);
  289. return regions;
  290. }
  291. public List<RegionData> GetHyperlinks(UUID scopeID)
  292. {
  293. return Get((int)RegionFlags.Hyperlink, scopeID);
  294. }
  295. private List<RegionData> Get(int regionFlags, UUID scopeID)
  296. {
  297. string sql = "SELECT * FROM [" + m_Realm + "] WHERE (flags & " + regionFlags.ToString() + ") <> 0";
  298. if (scopeID != UUID.Zero)
  299. sql += " AND ScopeID = @scopeID";
  300. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  301. using (SqlCommand cmd = new SqlCommand(sql, conn))
  302. {
  303. cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
  304. conn.Open();
  305. return RunCommand(cmd);
  306. }
  307. }
  308. }
  309. }