1
0

MSSQLRegionData.cs 14 KB

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